Last post Jun 24, 2016 08:14 AM by Zhi Lv - MSFT
Jun 22, 2016 03:23 PM|ImpossibleIsNothing|LINK
I am trying to design the logical model for a potential data warehouse system and I have a quick question.
In E-R databases the "snowflake" schema is usually the case for many table relationships. Please correct me if I am wrong but during the design of the logical diagram we try to denormalize this snowflake of e-r diagrams and incorporate all data in one table
which could be the dimension tables and also transfer the measures into the fact table later. Is that the correct approach? That also means that the dimension tables contain duplicated values as these are denormalized tables from the snowflake relationship
of the transactional database?
Please let me know if my question is not clear enough.
Thank you all for your responses.
Jun 23, 2016 05:52 AM|Zhi Lv - MSFT|LINK
Please correct me if I am wrong but during the design of the logical diagram we try to denormalize this snowflake of e-r diagrams and incorporate all data in one table which could be the dimension tables and also transfer the measures into the fact table later.
Is that the correct approach? That also means that the dimension tables contain duplicated values as these are denormalized tables from the snowflake relationship of the transactional database
As you said, if you put all data in one table, it will
contain duplicate values and the database will have
lots of redundancy. So, I'm not suggesting you do that.
You can refer to the following article to create database:
Jun 23, 2016 01:18 PM|ImpossibleIsNothing|LINK
I really need an example of how to deal with snowflake in E-R diagram. In order to give an example I want to create a Fact table with which I want to create a relationship with a dimension. I want to combine information from two tables. For example as it
is now we have a table i.e. Action which is linked to another table i.e. Action Cost. There is one to many relationship between Action and Action Costs because one action may have costs in different years i.e. 2014, 2015 etc. Now if I want Action and Action
Cost tables to combine them together and link them with the Fact table how do I do this? Also the Fact table is connected with a one to many relationship with Action table because a Cost may be splitted in different Actions and each Action is spitted in different
times of year therefore the same actions appears two three or four times but with different amount for every year.
Do you know how to deal with this one to many to many relationship in case I only want to keep one to many? Isn't the case that I am going to have one table only as a dimension and therefore have duplicated Actions ID because of the kind of relationship
it exists in the system now?
Thanks. I hope I was clear.
Jun 24, 2016 08:14 AM|Zhi Lv - MSFT|LINK
You can refer to the following link to configure relationship between tables: