Home > Uncategorized > Dimension Role-Playing in Employee Comp Reporting

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.

image

image

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.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: