January 18, 2012 at 8:51 am
I have a business requirement that I need to determine the difference between historic change of a SCD type 2 dimension attribute. At this time the dimension is tracking change to an order. What the managers want to know is when a customer service rep sets a promise date with a customer they have 2 working days (no weekends or holidays) to update that promise date depending on availability and other factors. The managers want to know the initial promise date and the final (any changes within the 2 work day limit) and compare the final promise date to the ship date of the order. I am recording the order throughout its lifecycle (bid, order processing, and invoicing) in the dimension. I plan on loading the fact table with the initial, final and ship dates from the dimension after the order is finally invoiced and there are no more changes to that order. I am new to data warehousing. My predecessor used only stored procedures to load the data warehouse. I am continuing that path until I learn SSIS. Does anyone have any thoughts on the most efficient way of loading this fact table?
Thanks
January 23, 2012 at 11:46 am
MikeBrey (1/18/2012)
I have a business requirement that I need to determine the difference between historic change of a SCD type 2 dimension attribute. At this time the dimension is tracking change to an order. What the managers want to know is when a customer service rep sets a promise date with a customer they have 2 working days (no weekends or holidays) to update that promise date depending on availability and other factors. The managers want to know the initial promise date and the final (any changes within the 2 work day limit) and compare the final promise date to the ship date of the order. I am recording the order throughout its lifecycle (bid, order processing, and invoicing) in the dimension. I plan on loading the fact table with the initial, final and ship dates from the dimension after the order is finally invoiced and there are no more changes to that order. I am new to data warehousing. My predecessor used only stored procedures to load the data warehouse. I am continuing that path until I learn SSIS. Does anyone have any thoughts on the most efficient way of loading this fact table?
I think this should probably be tracked on FACT_ORDER table; most likely treating it as a Temporal (or bitemporal if needed) Snapshot Fact table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 30, 2012 at 11:00 am
Sorry about the delay, other projects and had to research what a temporal fact snapshot table was. If I understand it is a fact table that behaves like a SCD2 dimension table. Is that a correct high level description? If so then I would insert a new row into the fact table everytime the promise date changes. In my reporting tool I would select the initial promise date from the fact table with the earliest occurance and somehow determine the row with the limitation with the 2 day window. Is this what you are suggesting?
February 21, 2012 at 7:30 am
MikeBrey (1/30/2012)
Sorry about the delay, other projects and had to research what a temporal fact snapshot table was. If I understand it is a fact table that behaves like a SCD2 dimension table. Is that a correct high level description? If so then I would insert a new row into the fact table everytime the promise date changes. In my reporting tool I would select the initial promise date from the fact table with the earliest occurance and somehow determine the row with the limitation with the 2 day window. Is this what you are suggesting?
Yes and Yes 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply