Attribute data held as rows in source - how to dimensionally model???
2 posters
Page 1 of 1
Attribute data held as rows in source - how to dimensionally model???
Hi everyone, my first post and I have a dimensional modelling dilemma that I hope you can help me with?
To be honest I'm not sure the best way to proceed, let me explain...
I need to include new Theme attributes to an existing dimension, the attribute values are held as rows in the source database i.e. AttributeName & AttributeValue. A theme can have many attributes. My original idea to model this was to pivot the source data on the AttributeName and map this to newly created columns in the dimension table (one column for each attribute). Simple enough I thought but when I got to really thinking about it a problem became apparent. With this approach how do I gracefully deal with new attributes as they appear in the source table as they would require a new column adding to the dimension table?
Thanks in advance for any help.
To be honest I'm not sure the best way to proceed, let me explain...
I need to include new Theme attributes to an existing dimension, the attribute values are held as rows in the source database i.e. AttributeName & AttributeValue. A theme can have many attributes. My original idea to model this was to pivot the source data on the AttributeName and map this to newly created columns in the dimension table (one column for each attribute). Simple enough I thought but when I got to really thinking about it a problem became apparent. With this approach how do I gracefully deal with new attributes as they appear in the source table as they would require a new column adding to the dimension table?
Thanks in advance for any help.
Hillagio- Posts : 1
Join date : 2011-02-15
Re: Attribute data held as rows in source - how to dimensionally model???
Adding the Theme changes the level of the dimension. With theme, the dimension table is now 1 level lower.
Since the theme is one level lower, the gradularity of the fact table becomes lower.
way of dealing with it is to create a bridge table or to declare the grandularity of the fact table to be lower than it was. It;s hard to know what the correct solution is because you haven't discussed the fact table.
You are right that pivoting the themes would be a bad idea.
Since the theme is one level lower, the gradularity of the fact table becomes lower.
way of dealing with it is to create a bridge table or to declare the grandularity of the fact table to be lower than it was. It;s hard to know what the correct solution is because you haven't discussed the fact table.
You are right that pivoting the themes would be a bad idea.
Jeff Smith- Posts : 471
Join date : 2009-02-03
Similar topics
» Model Design best practice - add columns or pivot data for multiple rows ?
» Dimensional Model from a Hierarchical Data Source
» Using the Dimensional Data Warehouse as source data for the OLTP process
» Is it a best practice that Data warehouse follows the source system data type?
» How to model 1:N relation with daily history for milions of rows
» Dimensional Model from a Hierarchical Data Source
» Using the Dimensional Data Warehouse as source data for the OLTP process
» Is it a best practice that Data warehouse follows the source system data type?
» How to model 1:N relation with daily history for milions of rows
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum