Productivity Reporting #3 – Back fill transactional data or not? Slowly Changing Dimension (SCD)?

Fact tables that involve employee productivity (which drives the Incentive program) are mostly transactional. The word transactional is not exactly accurate in our world. The granularity in many of data elements is per day, meaning that certain data elements will only store the last value from each day, rather than every changed values (transactional) throughout the day.

This “per day” approach also coincides with our batch ETL process, which is also per day.

However, business has constantly asked us to back fill those per-day transactional data with data that were entered many days later. Here are some scenarios:

1. Managers have forgotten to assign team members to a task until the tasks have been completed.

2. Team members have forgotten to check certain indicators until days after the decisions have been made.

As a developer, we are always happy to comply with business requirements, with a few occasional exceptions. I think this should be one of the exceptions.

Here is the reason. If the transactional data can be updated many days later, imagine what would happen to the record counts. Your record counts (for certain measures) on 5/20 will be slightly different when you count it again one 5/25, if you have back filled the transactional data between 5/20 and 5/25.

Does this issue sound familiar to you? What about the famous Slowly Changing Dimension (SCD) issue in star schema ETL process? Do they have anything in common?

I think this is the same issue as the SCD issue. I need to confess that when I read about the SCD issues, it really didn’t make a lot of sense to me until I have a real business case.

A real business case also helped me to realize that there is no right or wrong solution for the SCD issue, and the solution largely depends on what you can work out with the business.     

Dimension Role-Playing in Employee Comp Reporting

The employee hierarchy table is just one physical table, with a date range to keep employee’s employment history. This same employee hierarchy table can be used sometimes to show different business functional teams, for example, LAS VS. Underwriters on the same fact table. 

I can simply create two SQL views to handle this, or just use two sub-queries if I don’t want to create too many SQL views.

This is called dimension role-playing. Role-playing in a data warehouse occurs when a single dimension simultaneously appears several times in the same fact table. The underlying dimension exists as a single physical table, but each of the roles should be presented in a separated labeled view.  

Another common example in the employee productivity reporting, is the date dimension. We will just need one single date dimension table, but very often, we need to have a second date, such as application date, besides the primary decision date, on the same fact table. Again we only need to create two different SQL views to handle this, with only one underlying physical date dimension table.

Introduction to Employee Compensation and Productivity Reporting

I came back to my old job, but am not working on exactly same projects. I am very happy that I have new puzzles to solve now.

Obviously employee compensation and productivity reporting has become a hot topic in the business recently.

I am going to blog on this subject as I get to understand more about the business needs.

After completing several ETL processes and reports so far, I gradually start to put all the puzzles together.

In its simplistic form, I can envision a subset of the data warehouse bus matrix. This matrix will revolve as I understand more.

The dimensions will involve hierarchies. All the facts basically are from a transactional table. But as we put together all the fact tables for reporting, we will get all three types of fact tables, i.e. the transactional fact tables, snapshot fact tables, and also accumulating fact tables. The third type will be the most common.

The natural granularity for our business transactional fact table will be one row for each action a team member does for each account. For the purpose of employee compensation and productivity reporting, the facts associated with the business process typically just include the counts of the actions, such as the counts all “declines”, or the counts of all “approvals”.

In the next blog I’ll talk about whether I need to normalize my fact tables by fact types.