Kronos Workforce Central to UKG Dimensions: Does SQL Still Matter?

You’ve supported Kronos Workforce Central for years with your SQL skills. Now, your company is moving to Dimensions. Are your skills still important?

They are, but they are not enough.

Let’s take a moment to share the pain. This challenge is not unique to this migration. SQL has been the Swiss army knife for application support for decades. Before SQL, we had other tools that allowed direct database access. (Anyone remember wrkqry on the AS400?)

But that’s not where we are today. While we’re pretty sure that databases still exist, they’re often hidden far away from user technical resources.

Dimensions has multiple tools for data work. But none of them completely replaces what you get with an on-premise database

Here’s a list of the tools we’re currently using with Dimensions. Listed by order of usefulness for the SQL Developer.

Data Hub is the closest you’ll get to a full view of data in SQL.

The good news:

• It uses Google Big Query which means you can use SQL.
• It has transaction level data.
• Google Big Query has standard APIs to extract the data.

The bad news:

• It doesn’t come free.
• It’s not real time. You schedule it to refresh and the refresh time will depend on your implementation. For reference, a client with 3000 employees, half of whom punch in and out, the refresh takes about 45 minutes.
• It doesn’t have all the data that’s actually in Dimensions.
• Your queries can get pretty complicated. Here’s a sample where we figure out how to find the applied date for a given punch in/punch out.

If your company also uses UKG Pro aka UltiPro, you should be familiar with People Analytics aka Cognos BI. UKG has brought Dimensions data into Cognos.

The good news:

• Knowing SQL will help you understand how to build queries based on the models provided.
• If you’ve used People Analytics previously, you already know the tool.
• You can use Reports as a Service to extract data. (Post coming soon!)

The bad news:

• There is a small cost associated with this.
• The data isn’t real time. You schedule the refresh.
• It does not have detail data.

Dimensions has the ability to extract data to a delimited file.

Before I had Data Hub, this is what I was using for basic analysis. It did have some quirks. For example, I found that once an employee left the company and was disabled in Dimensions, there data was excluded from some extracts. I didn’t track the problem down.

Dimensions has a large set of Rest APIs.

The good news:

• You can find data that you can’t get in any of the above options. And as you’re just running one API, you can refresh data pretty quickly.

The bad news:

• Again, you can find most, but not all of what you want. And as you many found with other Rest APIs (whether with Kronos, UltiPro or any other software), everything works per a standard. Except when it doesn’t.
• As with all APIs, you now have JSON and not a nice tabular layout. If you’re a SQL person like me, you probably don’t love JSON. But remember, it’s like XML in many ways. Which you probably didn’t like either.

Dimensions does have built in reporting tools which I’ve left for last. Dataviews are fine for manager self-service. But I find they are less effective when trying to do enterprise-wide analysis. You drill, and drill, and drill. You can export to Excel but the results aren’t always that useful.

As for BIRT, I’ll just invoke the thumper rule. I don’t have anything nice to say so I won’t say anything at all.

We’ll be updating this post in the future as we blog about more examples. Let us know what you’d like to know about next in the comments. Or Contact us if you want help moving from SQL Developer to Cloud Data Specialist.