April 14, 2017 at 7:01 am
Hello,
I am fairly new to data-warehousing but have read my fair share of books and online tutorials to gain an introductory understanding of the basic components that build up a data warehouse.
My end goal is to be able to do a headcount of employees at the end of the month, therefore I designed my fact table as a monthly snapshot where each end-of-month I will populate my fact table (one row per employee per month).
My dimension table includes a DimEmployee table which is an SCD2 (any change made to an employee's information causes a new row to be introduced in the data source, so I am keeping track of the most recent employee information via an is_current flag set to 'Y' or 'N').
My question is, do I need to update the Dimensions daily and only insert whatever data I have at end-of-month into the fact table? Or do I update the dimensions end-of-month as well?
Thank you in advance!
- Ali
April 18, 2017 at 7:21 am
Do you use your DimEmployee dimension linked to other Fact tables (other than your monthly snapshot)? Do you care about multiple changes that happen to an employee during the month? or only what the employee ended up as?
I'm guessing your business needs do want you to keep track of those employee changes daily. My guess is that you probably would want to know that an employee switched titles from a 'Cashier' to a 'Head Cashier' to a 'Manager' all within a single month (three records within your SCD). What about an employee that hires in during the beginning of the month and gets fired/sacked/canned before the month is completed?
Just some thoughts.
I hope this helps,
Rob
April 18, 2017 at 9:20 am
I agree that you'll probably want to update DimEmployee daily. I'd imagine that there are potentially other fact tables that would be able to take advantage of the data within this dimension, and their grain may be at a transactional level instead of a monthly rollup level. I follow the Kimball methodology for data warehouses, and one of their main principles is what's called the Bus Architecture, reusing the same dimension for multiple facts:
http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/kimball-data-warehouse-bus-architecture/
Here's some good resources about dimensional modeling:
http://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/
http://decisionworks.com/data-warehouse-business-intelligence-resources/kimball-techniques/
April 18, 2017 at 12:02 pm
Thank you so much guys! Yeah, it does make sense to update them daily as there might be "in-month" changes to the same employee and I would need to add a new row for said employee and update his current flag field.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply