none
Design questions

    Pergunta

  • Hello,

    I have looking at designing a DW and currently looking at the data model. At the moment, I am looking at creating a date dimension, the problem that I have is that I have multiple fact tables emerging from the model, and some of the fact tables having 6-8 datetime columns, these date fields mean different things atimes. We can have update date, creation date, delivery date, sales date, business date etc.

    In this kind of setup, one single date dimension wouldnt work as some of the dates are granular to the time level, where others are not. I was thinking of looking at all the fact tables and looking for common date patterns and including that as part of the date dimension. The other date columns which needs the time element etc woulnt go into the dimension table, but remain within the fact table.

    I also have other questions.

    1. Is it possible to have a fact table with over 30 measures.
    2. How can one deal with multiple fact tables with a 1:n relationship.
    3. What is the easist and most straight forward way to do this, lets assume that a date came in from the source sytems, what is the easiest way to get the matching DateKeyID dimension key. I know this can be done with a join, just wondering if there is an easier way to do it.
    4. A sales table might include columns like notes, will this become part of the fact table.
    5. How about columns like status, and other bit type columns.

    Looking forward to a reply.

    Thanks.

    terça-feira, 3 de abril de 2012 15:13

Todas as Respostas

  • Let us take one part at a time:

    In this kind of setup, one single date dimension wouldnt work as some of the dates are granular to the time level, where others are not.

    Maintain ypur date time table at the lowest granularity as possible. If nothing works, then design a different fact table with only required columns at different grain.

    Is it possible to have a fact table with over 30 measures

    Yes. You can have a s many as measures that the business might need for data exploration.

    A sales table might include columns like notes, will this become part of the fact table

    What is notes?

    How about columns like status, and other bit type columns

    Model them in the dimension table.


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    quarta-feira, 4 de abril de 2012 06:37
  • Let us take one part at a time:

    In this kind of setup, one single date dimension wouldnt work as some of the dates are granular to the time level, where others are not.

    Maintain ypur date time table at the lowest granularity as possible. If nothing works, then design a different fact table with only required columns at different grain.

    Is it possible to have a fact table with over 30 measures

    Yes. You can have a s many as measures that the business might need for data exploration.

    A sales table might include columns like notes, will this become part of the fact table

    What is notes?

    How about columns like status, and other bit type columns

    Model them in the dimension table.


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Thanks for the reply.

    When you say the following "Maintain ypur date time table at the lowest granularity as possible. If nothing works, then design a different fact table with only required columns at different grain."  The problem that I am having is that one date dimension table wouldnt work. In which cases do you still have date fields in a fact table ?

    what I meant by notes is the following. Imagine a sales data mart, the system allows for customers to place special instructions as part of thier order, lets say the instructions says that press the bell when delivering or something like the my flat is in the basement by the left of the building or perhaps a repair booking system which leaves a note (free text) column for the engineer saying, please arrive before 10AM. Fields that you cant really normalise onto a dimension etc.

    Things like status and other bit fields, I have about 8 of such columns in my model. Does that mean that I need 8 different dimension to explain these ?

    Thanks.

    quarta-feira, 4 de abril de 2012 09:25
  • 1. You can create one single date dimension and can link it to a fact multiple times as a Role playing dimension. Refer below links for more details

    http://gauravsqlserver.blogspot.com/2011/03/role-playing-dimension-in-ssas.html

    http://msdn.microsoft.com/en-us/library/ms174487(v=sql.90).aspx

    <cite>2. Regarding notes , if you nececesarily need that to be used reporting keep it as a dimension rather than keeping it in a fact. BTW can you please brief about the usage of Notes column in reporting.</cite>

    <cite></cite>

    quarta-feira, 4 de abril de 2012 09:54
  • The notes column isnt going to be used for reporting, the problem here is that the users like to analyse the raw data. Things like notes althought not heavily used, but can be beneficial during investigations.
    quarta-feira, 4 de abril de 2012 10:09
  • Create a dimension with necessary hierarchy and attributes for such investigative needs.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    quarta-feira, 4 de abril de 2012 10:39
  • Create a dimension with necessary hierarchy and attributes for such investigative needs.

    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    Are you referring to the notes column here ?

    Also, on my fact table I have colums which are like bits data type, so they can only either be 1 or 0 representing a flag. Should that have its own dimension as well ?

    Also does everything else follow this rule ?

    segunda-feira, 16 de abril de 2012 09:39
  • For low cardinality values as in this case, you can also consider creating Junk Dimensions.

    Excellent read: http://www.sqlmag.com/article/data-management/data-warehousing-junk-dimensions


    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog

    segunda-feira, 16 de abril de 2012 09:59
  • Thanks for the reply.

    Is it possible to have historical facts. Normally historical dimension records will be of SCD type 2. Now how about facts, is it possible to have historical facts too and using a startdate and enddate columns.

    Also the lowest grain of the date columns looks like this: 2004-04-28 12:02:00

    Does this mean that my date dimension needs to cater for the following

    1 year = 365 days / 366 as per leap year.
    1 day = 24 hours
    1 hour = 60 mins

    1 year = 365 * 24 * 60

    segunda-feira, 16 de abril de 2012 15:22
  • If I have a fact table with different types of locations, is it possible to combine all the types of locations into a dimension table and then reference it with the FK ?

    Thanks.

    terça-feira, 8 de maio de 2012 15:17