brihaspati
IS-IT--Management
I am designing a data model where data ( both dimension and fact) will have back in time changes or retroactive changes. Please suggest me pointers on how to handle it.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Need help modeling for back in time changes in dimensional model
- Thread starter brihaspati
- Start date
- Status
I sue CRC checks for my Slowly changing dimensions but you need to decide on if it is going to be at a dimenional level or a column level and then what needs to be a type 1 or type 2 change. As for changing the Fact Data I would probably handle that as some form of a type 2 style change but build a system to tie the new record to the old record. Changing fact data needs to be approached seriously and make sure all parties involved under stand the results.
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
Shoot Me! Shoot Me NOW!!!
- Daffy Duck
johnherman
MIS
MDX'er has aptly covered your problem with changing dimensions. As for the facts which change over time...here are some things to consider.
- Consideration should be given to a "lowest common denominator" solution for the columns in the fact(s). With this solution, columns which appear in older versions but not in the current version can be reached by a 1 to 1 join with a parallel fact table containing those valuable older fields which are no longer available.
- Consideration should be given to a "highest common denominator" solution whereby every column is represented in the fact table. Columns which do not apply are populated by nulls, or perhaps by some calculation. Care must be taken to assure that the units of measure are consistent across time (i.e. francs versus euros, pounds versus kilograms, etc) or that there is some conversion which can be used.
- Consideration should be given to multiple fact tables with views or materialized views providing the union of the fact tables across time.
I can porbably help more if you provide more specifics.
-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright
- Consideration should be given to a "lowest common denominator" solution for the columns in the fact(s). With this solution, columns which appear in older versions but not in the current version can be reached by a 1 to 1 join with a parallel fact table containing those valuable older fields which are no longer available.
- Consideration should be given to a "highest common denominator" solution whereby every column is represented in the fact table. Columns which do not apply are populated by nulls, or perhaps by some calculation. Care must be taken to assure that the units of measure are consistent across time (i.e. francs versus euros, pounds versus kilograms, etc) or that there is some conversion which can be used.
- Consideration should be given to multiple fact tables with views or materialized views providing the union of the fact tables across time.
I can porbably help more if you provide more specifics.
-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright
- Thread starter
- #4
brihaspati
IS-IT--Management
With type 2 dimensions, I will have to go and update dim keys in fact tables, which will be little messy. For ex., I have product dimension with fields say prdid, prdname, effectivefromdate and effectivetodate. I have salesfact where I have prdid, saledate and salesvolume.
product dim
1 Toothpaste 1/1/2004 12/31/2099
1 Toothpaste 1/1/2004 3/1/2004
1 Toothbrush 3/1/2004 12/31/2099
salesfact
1 1/5/2004 10
2 3/5/2004 20
when I run report in feb I should get 10 toothpastes and when I run report in april, I should get 30 toothbrushes.
what is the best way to achieve this w/o updating dim key in fact records?
product dim
1 Toothpaste 1/1/2004 12/31/2099
1 Toothpaste 1/1/2004 3/1/2004
1 Toothbrush 3/1/2004 12/31/2099
salesfact
1 1/5/2004 10
2 3/5/2004 20
when I run report in feb I should get 10 toothpastes and when I run report in april, I should get 30 toothbrushes.
what is the best way to achieve this w/o updating dim key in fact records?
johnherman
MIS
There are two ways, both similar, to handle your SCD. The first solution isto add an effective (or expiration, or both) date to each product record.
Another (more common) method is to assign a new key, often called a surrogate (or synthetic or warehouse) key to each product record. Facts associated with the "old" product number 1 (toothpaste) should be assigned the warehouse key for the toothpaste record. Facts associated with the product "toothbrush" should be assigned the warehouse key assigned to product number 1, toothbrush. This does not appear to work for your example.
-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright
Another (more common) method is to assign a new key, often called a surrogate (or synthetic or warehouse) key to each product record. Facts associated with the "old" product number 1 (toothpaste) should be assigned the warehouse key for the toothpaste record. Facts associated with the product "toothbrush" should be assigned the warehouse key assigned to product number 1, toothbrush. This does not appear to work for your example.
-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright
AdaptiveDW
Vendor
Have a look at a product called Kalido
It does current hierarchy, time of transaction and "any point of time" automatically.
It does current hierarchy, time of transaction and "any point of time" automatically.
I guess I was reading to much into your problem. I thought you were talking about handling retro-active changes. None of the methods described above will help you with that because you will have already linked your fact records to the image of the dimension at the point in time that the fact row is added to the table. The way to handle data latency is guided by your business requirements, do you treat the retro-active changes as type 1 changes, if you do recreating reports off the mart for a specific historical point in time ay not be the same. If you add a new image of the dimension row then you will need to adjust the any timestamping being used and if you do this then you would need to relink historical rows that fall into the timeslice effected. A technique I have used is to keep the previous image of any coulmns which are critical to the business and the update the rest of the columns in place (type 1).
As anyone who has faced posting late arrivals recognizes the problem is anything but stright forward.
hope that help
As anyone who has faced posting late arrivals recognizes the problem is anything but stright forward.
hope that help
ManneetSingh
MIS
There are two ways to solve this problem:
1) add a attribute current product to your dimension. So your product dim will look like this
current prod desc
1 Toothpaste 1/1/2004 12/31/2099 Toothbrush
1 Toothpaste 1/1/2004 3/1/2004 Toothbrush
1 Toothbrush 3/1/2004 12/31/2099 Toothbrush
When you join your dimension to your fact table using surrogate keys and group by current prod desc in april, you will get 30. You will get 10 in Feb using this approach. Your Current prod desc is nothing but maintaining a type 1 in a type 2 dim.
2) You can maintain a type 2 with the natural keys. Grouping with natual keys will yeild the same result except you might not get the name change.
1) add a attribute current product to your dimension. So your product dim will look like this
current prod desc
1 Toothpaste 1/1/2004 12/31/2099 Toothbrush
1 Toothpaste 1/1/2004 3/1/2004 Toothbrush
1 Toothbrush 3/1/2004 12/31/2099 Toothbrush
When you join your dimension to your fact table using surrogate keys and group by current prod desc in april, you will get 30. You will get 10 in Feb using this approach. Your Current prod desc is nothing but maintaining a type 1 in a type 2 dim.
2) You can maintain a type 2 with the natural keys. Grouping with natual keys will yeild the same result except you might not get the name change.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question