locked
Data warehouse quick question RRS feed

  • Question

  • User-1040198395 posted

    Hi all,

    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.

    Wednesday, June 22, 2016 3:23 PM

Answers

All replies

  • User-271186128 posted

    Hi ImpossibleIsNothing,

    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:

    http://www.programering.com/a/MDMyAzNwATU.html

    http://www.codeproject.com/Articles/359654/important-database-designing-rules-which-I-fo

    Best regards,
    Dillion

    Thursday, June 23, 2016 5:52 AM
  • User-1040198395 posted

    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.

    Thursday, June 23, 2016 1:18 PM
  • User-271186128 posted

    Hi ImpossibleIsNothing,

    You can refer to the following link to configure relationship between tables:

    http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx

    https://msdn.microsoft.com/en-us/data/jj591620

    Best regards,
    Dillion

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 24, 2016 8:14 AM