Monday, August 11, 2008

A Table Valued Function to Split Strings


-- the function
CREATE FUNCTION SplitString
(
      @TargetString NVARCHAR(MAX),
      @Delimeter NVARCHAR(MAX)
)
 
-- the part repository
RETURNS @Parts TABLE
(
      PartId INT IDENTITY(1, 1),
      Part VARCHAR(MAX)
)
AS BEGIN
 
      -- just some variables to keep track of things
      DECLARE
            @CurrentIndex INT,
            @DelimeterIndex INT,
            @PartLength INT;
 
      -- initialize the loop
      SELECT
            @CurrentIndex = 0,
            @DelimeterIndex =CHARINDEX(@Delimeter, @TargetString, 0),
            @PartLength = @DelimeterIndex - @CurrentIndex;
 
      -- if the delimeter exists, continue the loop
      WHILE (@DelimeterIndex > 0) BEGIN
 
            -- add the part to the part repository
            INSERT INTO @Parts VALUES (SUBSTRING(@TargetString, @CurrentIndex, @PartLength));
 
            -- update the indexing information
            SELECT
                  @CurrentIndex = @CurrentIndex + @PartLength + LEN(@Delimeter),
                  @DelimeterIndex = CHARINDEX(@Delimeter, @TargetString, @CurrentIndex),
                  @PartLength = @DelimeterIndex - @CurrentIndex;
 
      END
 
      -- add whatever comes after the last delimeter
      INSERT INTO @Parts VALUES (SUBSTRING(@TargetString, @CurrentIndex, LEN(@TargetString) - @CurrentIndex + 1))
 
      RETURN
END

Saturday, August 9, 2008

HR-XML Integration in Human Resources Software

Most of the programming and architecture work I do is in the human resources space. I write software for paperless onboarding and acculturation, personell change management, background checks and verification services, new employee requisitioning, and the like. As you can imagine, I spend a great deal of time and effort integrating with human resources software systems. I work with applicant tracking systems, payroll systems, human resources management systems, other onboarding systems, background check providers, and a number of other applications and services that all have to be able to talk to each other.

Some of the software, services, and systems I integrate with aren’t too bad. Some of them, however, are obscene. I work with one HRMS and payroll system, for example, that has a database with more than 1300 tables comprising an absurd 34,000 columns. Most of the tables have 8 to 10 character names; 3 of these characters are consumed by a 3 letter prefix which is carried over into the column names in the table. The tables may or may not have an identity field which is buried somewhere in the table structure, but you don’t need to know what the identity column is because it’s not used as a primary key. Instead, the primary key is derived from a time based calculation with a 1 second granularity. Furthermore, there are thousands of columns on most of the tables that start with UDF and end with a two digit number. In case you haven’t seen this abhorrent pattern before, UDF is short for “User Defined Field,” and it’s pretty darned tacky.

If you’re not yet convinced that this is indeed the ultimate in terrible database design, just go dig up E. F. Codd and you’ll most likely find him spinning in his grave. But, I’m not really writing this entry about bad databases; I mostly wanted to describe why it is so hard for multiple vendors to work together in the same environment. Well, to our rescue comes the HR-XML consortium, “ a non-profit organization dedicated to the development and promotion of a standard suite of XML specifications to enable e-business and the automation of human resources-related data exchanges.”

So, what does that really mean? Well, if your imports and your exports follow the HR-XML schema, you know you can always talk between systems. Our suite of human resources software is fully capable of communicating via HR-XML data. For us internally, it means that any piece of our system can be added or removed based on client needs. It also means that we fit in any stage of an HR process between applicant tracking and termination of an employee.

We can easily integrate with HRMS and Payroll systems, background check vendors, and even the Department of Homeland Security eVerify system. The only real problem is that HR-XML is not well accepted. Most other vendors don’t provide HR-XML integration support. In fact, some companies even lie about HR-XML integration to trick potential customers into believing they are getting an easily extensible system. I even know of one company that not only lies about its HR-XML integration, but also sits on the board of the HR-XML consortium. To me, this seems like the ultimate software insult.

So, if you work in HR or in HR software development, what can you do? Well, tell your vendors you are interested in seeing HR-XML support. Interact with the HR-XML consortium. Make your solutions HR-XML compatible. There are numerous ways to get involved and I hope this article whets your HR-XML appetite. I plan on posting a series of HR-XML articles on my blog so keep checking back. In the meantime, I’d love to hear about your integration nightmares. Please post a comment on this post if you have experienced similar issues.

© 2008, D. Patrick Caldwell, Vice President for Research and Development, Emerald Software Group, LLC


[digg=http://digg.com/programming/HR_XML_Integration_in_Human_Resources_Software]