Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2010
    Posts
    20

    Unanswered: Time Dimension: From Months to Days

    Hi all,

    Apologies in advance for another newb post.

    I have a dimension that displays down to the month level. It links to the fact table at the month level.

    CFO wants to see Revenue data down to the day level.

    Do I need to rewrite the FACT Table to incorporate this or is the data 'There' but I cannot get to it.

    Does that make sense ?

    TIA

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If your fact table has data stored aggregated at the month level, you'll have to go back to your staging table(s) and rebuild the fact table at the lowest level (daily) that you need to report.

    If your fact table actually stores data at the daily (or lower) level, then you can drill down into it without changing the fact table.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2010
    Posts
    20
    Thanks Pat. Spot on, the procedure that loads the fact table groups at the month stage.

    I will have to rewrite the procedure.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Before you do a fair bit of work to recreate this data, and use about 25 times as much space to store it you need to ask a couple of questions.

    How will taking this data down to the daily level help make better decisions? What will the user or users be able to do with that detail that they can't do now?

    There are a lot of expenses that aren't daily, especially in manufacturing. Can you break those expenses down in ways that make sense for the business? If you can't get uniform and sensible data at the daily level, trying to compute revenue at the daily level is almost impossible. At best it will be skewed, more likely it will be bad enough to cause bad decisions. This is a real BI problem that you MUST be able to either solve or explain, because it can make your data produce GIGO instead of good decisions.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2010
    Posts
    20
    Thanks for the advice Pat.
    I agree with everything you say there but our CFO wants it done.
    We are a retail shop, with few stores (very high end clothing retail) and the daily sales figures are already downloaded via ETL for a daily sales report.

    So basically the leaf detail is already there. I just have to get it into a new cube that analayses discounts etc.

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •