How do I handle slowly changing dimension tables in 1010data?
I have some slowly changing dimension tables for attributes with start and end effective dates that I want to combine in an analysis. In a traditional SQL database, I would use an inequality join. What should I be using in 1010data to perform this link?
Best Answer
S
Steven
said
over 5 years ago
1010data has a mechanism specifically to join slowly changing dimensions to fact tables, called as-of links. Let's take an example with a Sales and a slowly moving Product table, below.
Sales Table
Product Dimensions -- Slowly Changing
In this case we want to bring in the cost data, which is updated over time into Sales. We always want the latest cost data added. Matching these within 1010data means an as-of link. The Macro Code for this is here:
The last column in Col and Col2 are the slowly moving dimension match column (as-of). In this case product is exactly matched and then the last column brings in the latest value.
In row 2, you see that if the only values for a given product happen "after" the asof match date, you'll see there is no match
Very important --> the slowly changing dimension table has to be sorted! Notice that this table is sorted by product and by update_date. This is required for asof link to work properly
1010data has a mechanism specifically to join slowly changing dimensions to fact tables, called as-of links. Let's take an example with a Sales and a slowly moving Product table, below.
Sales Table
Product Dimensions -- Slowly Changing
In this case we want to bring in the cost data, which is updated over time into Sales. We always want the latest cost data added. Matching these within 1010data means an as-of link. The Macro Code for this is here:
The last column in Col and Col2 are the slowly moving dimension match column (as-of). In this case product is exactly matched and then the last column brings in the latest value.
In row 2, you see that if the only values for a given product happen "after" the asof match date, you'll see there is no match
Very important --> the slowly changing dimension table has to be sorted! Notice that this table is sorted by product and by update_date. This is required for asof link to work properly
create new ticket
check ticket status
Steven
How do I handle slowly changing dimension tables in 1010data?
I have some slowly changing dimension tables for attributes with start and end effective dates that I want to combine in an analysis. In a traditional SQL database, I would use an inequality join. What should I be using in 1010data to perform this link?
1010data has a mechanism specifically to join slowly changing dimensions to fact tables, called as-of links. Let's take an example with a Sales and a slowly moving Product table, below.
Sales Table
Product Dimensions -- Slowly Changing
In this case we want to bring in the cost data, which is updated over time into Sales. We always want the latest cost data added. Matching these within 1010data means an as-of link. The Macro Code for this is here:
The result of this is here:
Key takeaways from this are...
More information and a detailed guide can be found here: https://www2.1010data.com/documentationcenter/prime/1010dataUsersGuide/index_frames.html?q=Linking/AsOfLinks.html
Steven
1010data has a mechanism specifically to join slowly changing dimensions to fact tables, called as-of links. Let's take an example with a Sales and a slowly moving Product table, below.
Sales Table
Product Dimensions -- Slowly Changing
In this case we want to bring in the cost data, which is updated over time into Sales. We always want the latest cost data added. Matching these within 1010data means an as-of link. The Macro Code for this is here:
The result of this is here:
Key takeaways from this are...
More information and a detailed guide can be found here: https://www2.1010data.com/documentationcenter/prime/1010dataUsersGuide/index_frames.html?q=Linking/AsOfLinks.html