Figuring out the apply date in UKG Kronos Dimensions Datahub

A client has both UKG Pro and UKG Kronos Dimensions.  They want to see punch back up for each pay check.

The challenge is figuring out the apply date.  Which is clearly on the screen but not to be found in the Data Hub Database.    Here is what we did to figure out this key piece of information.

What is the apply date

The apply date tells you when a given span of hours will be paid.

Most of the time, this is the same as the date the person punched in.

In that case, the partitionDate in the vTimeCardWorkShiftSpan table will actually tie to the vTimeCardTotal table and then to the pay check.

There are two other situation you need to conser.

I’ve found two possible situations where you need a different date:

  1. When the person clocked in early for a shift the next day.
  2. When the clock time was rounded up to the next day.

Early start on a scheduled shift –

If a person is scheduled for a shift starting at midnight but clocks in early, the apply date will be the next day.

Here’s an example.

The person clocked in at 11:56 PM  even though the shift started as 12:00 AM

And the apply date is 12/2.

To find this apply date, we need to use the vTimecardWorkShiftScheduledShift view which links the vTimeCardWorkShiftSpan view with the vScheduledShift view (see the code below)

Early start just before midnight when not scheduled

Sometimes, a person works even though they were not scheduled.  If they start just before midnight, a rounding rule will push the apply date to the next day.

Here we can see in the time card that the person worked but was not scheduled:

If we choose Analyze:

What’s strange here is that the system moved the date forward even though the rule was to round to the nearest 15 minutes.  Which just shows that you need to check how the system rounded and not try to apply your own rounding logic.

This appears to be a very similar case to the previous one. However, the exception is stored in the vTimeCardException view.  

The Final Query

Here’s the final query which can show all times when the apply date will not be the partitionDate.

WITH ExceptionCTE AS (SELECT exc.workedShiftSpanId, MAX(exc.eventDate) AS eventdate FROM `nyfoundling_detail.vTimecardException` exc GROUP BY exc.workedShiftSpanId), shiftDatesCTE AS (SELECT wsss.workedShiftId, MIN(ss.partitionDate) AS partitionDate FROM `nyfoundling_detail.vTimecardWorkShiftScheduledShift` wsss JOIN `nyfoundling_detail.vScheduleShift` ss ON wsss.shiftId = ss.shiftId GROUP BY wsss.workedShiftId) SELECT twss.personId, ppl.lastName, ppl.firstName, ppl.personNumber, twss.partitionDate, COALESCE(exc.eventdate, sd.partitionDate, twss.partitionDate) AS applyDate, twss.workedShiftSpanId, twss.startDateTime AS TimeCardWorkShiftSpan_StartDateTime, exc.eventdate AS Exception_EventDate, sd.partitionDate AS TimecardWorkShiftScheduledShift_PartitionDate FROM `nyfoundling_detail.vTimecardWorkShiftSpan` twss JOIN `nyfoundling_detail.vPeople` ppl ON twss.personId = ppl.personId LEFT OUTER JOIN ExceptionCTE exc ON exc.workedShiftSpanId = twss.workedShiftSpanId LEFT OUTER JOIN shiftDatesCTE sd ON twss.workedShiftId = sd.workedShiftId WHERE COALESCE(exc.eventdate, sd.partitionDate, twss.partitionDate) <> twss.partitionDate ORDER BY twss.partitionDate, ppl.personNumber;
Code language: PHP (php)

Notes on the Code

  1. Both the vTimeCardException vand vTimecardWorkShiftScheduledShift  views can have multiple records that match the vTimeCardWorkShiftSpan.  This is why I have the individual common table expressions to avoid creating excess records.
  • We generally run this query and store the result for use in other queries.   This improves performance and makes other code easier to deal with. 

I hope this helps you with your Data Hub Reporting. And that one day, this will not be necessary.

Have you found a better solution?  Or do you have questions?  Please comment below or contact us.

Data Hub vs People Analytics for UKG (Kronos) Dimensions

Recently, UKG has started supporting Dimensions data in People analytics.   Our clients’ experience indicates that DataViews are cool for manager and employee self-service but don’t work well organization wide analysis.  As for the BIRT reporting tool – I’ll invoke the thumper rule.*

The People Analytics Dimensions data is based on Data Hub, another new product we’ve been working with.   Data Hub gives you even more detail data, available through Google Big Query.

Here are my basic observations:

  1. Whatever road you take, UKG Dimensions data and UKG pro data is still not integrated. Which means we’re still pulling both sets of data into SQL server for various analyses.   Speaking to multiple folks at the recent Aspire conference in Las Vegas, UKG knows that this has to change.   It’s unclear when this will happen.
  2. If you just need aggregated data and you’re happy with people Analytics, you probably want to stick with the People Analytics solution.  
  3. If you need detailed transactional data, you need to be in Data Hub.  I don’t know the full road map for People Analytics, but right now you can’t get the full punch level detail in People Analytics.
  4. If you want to use some tool other than People Analytics/Cognos BI, then Data Hub is the way to go.  Because you can connect directly to Google Big Query from other tools like Power BI.
  5. Data Hub is a fairly new product and still very much a work in progress.   There are data fields on the screen which aren’t easy to replicate out of the data base (apply date – I’m looking at you).  I’m planning on creating a technical post on this.  Some of my SQL queries scare me.

If you’d like to chat more about how we might help you with Data Hub and/or People Analytics, please contact us.

*If you don’t have anything nice to say, don’t say anything at all.

Before you train on UKG People Analytics

Before you start training in UKG People Analytics

You know you have a lot of data in your UKG Pro (and now UKG Dimensions) system which you can access with People Analytics.

However, it’s not exactly an obvious tool to use.  Indeed, if you’ve worked with any kind of enterprise system, you may agree that “your users can write their own reports” may be the greatest lie told overstatement made by software salespeople.

I’ve delivered lots of training for BI reporting and as I approach people analytics, I believe that my basic approach still applies.

You need to distinguish between querying data and presenting data.

Querying means getting the right data out of the system using relational database concepts.   In the Cognos/People Analytics context it means understanding things like cardinality and joins.  A few days of training can get you started.*  But you need to work with it every week or you’ll forget everything and wonder why you spent the money.

Presenting takes that good data and makes it easy to consume.  That means choosing the right color, the right font, the right chart, among other challenges.

There are very few people who do both things well.  For myself, I have always enjoyed the querying.  I’ve written thousands of lines of SQL code in my career. So, while I’d prefer writing SQL, I can deal with joins in People Analytics.

In contrast, I am not visual.  I like reading financial statements.  A colleague once said that if you want to watch me sweat, make me create a Visio diagram. 

If you’re like most of our clients, your staff will probably be better with the presentation and not the querying.  In any case, consider the distinction before investing your money.

* For example, these posts on SQL For Super Users.

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult