Deisgn of Date Dimension Table

[Dineshpullagura]

Hello All,

In my data model i have a transaction fact and a bunch of aggregate facts at month level. I am trying to model a date dimension which can be used for both transaction and aggregate fact.

I have a format like this..

Date_ID     Date         Month_Row Month    Month_Name   Year_Row   Year
  1        09/16/2015     0                 9      September               0          2015
  2        09/17/2015     0                 9      September               0          2015
  3                              1                 9      September               0          2015
  4                                                                                       1           2015 


The Month_Row as '1' identifies that the entry is for a month and Year_Row as '1' identifies it as year and it can be used for month and year aggregates respectively.

Can we model the date dimension in cube designer, with the dimension table in this format. Or is there any other way to model the date dimension table.

Thanks for your time.

[MFGF]

Hello All,

In my data model i have a transaction fact and a bunch of aggregate facts at month level. I am trying to model a date dimension which can be used for both transaction and aggregate fact.

I have a format like this..

Date_ID     Date         Month_Row Month    Month_Name   Year_Row   Year
  1        09/16/2015     0                 9      September               0          2015
  2        09/17/2015     0                 9      September               0          2015
  3                              1                 9      September               0          2015
  4                                                                                       1           2015 


The Month_Row as '1' identifies that the entry is for a month and Year_Row as '1' identifies it as year and it can be used for month and year aggregates respectively.

Can we model the date dimension in cube designer, with the dimension table in this format. Or is there any other way to model the date dimension table.

Thanks for your time.

Hi,

To make life easy for yourself, think of the data in terms of a hierarchy. Each member in the hierarchy will need a uniquely defined ID (business key). As I see it you will end up with four distinct levels:

• All Years -- with a single member that all time aggregates up to. Cube Designer adds this for you automatically.
• Year - with each distinct Year value from your table as a member. Your Year members will require both an ID and a caption, and the caption needs to be a character string, so you might make life easy for yourself by adding an extra column that contains the year value casted as a char data type.
• Month - with each month of each year being a separate member. You're going to struggle a little here, because your table does not contain values that are unique for each month (eg 201509). You need a Month ID column that contains these sorts of values. You will also need a sensible caption, too - labelling a month as "September" will be very confusing, because every year will have a month with the same caption. What would you see if you dragged the 201509 and 201409 month members into a report? September and September. Very confusing for users. Create a caption column that concatenates the month name and year eg September 2015.
• Date. - with each date being a separate member. Here you would probably want a numeric and a character version of each date to use as the ID and caption eg 20150916 for the ID and 09/16/2015 (casted as a character string) as the caption.

Cheers!

MF.

[bus_pass_man]

Try looking at the sample model for pointers about modeling for multi-grain and aggregates.   The Redbook has material too.   All 3 modeling chapters would be useful I think.  The other chapters on aggregates would be handy.

In addition to the sections on multi-fact multi-grain and aggregates, you might want to read the seconds on levels.  You need keys to uniquely identify members in a hierarchy.  You don't need to create expressions concatenating columns, you can add multiple columns in the level unique key editor ( the top button on the right of the level editor. )  The retailer name level in the retailers dimension in the sample model has an example of that.

In general, you want to have keys which are integers, which the data base can do stuff faster ( sorry for the overly technical language) than non-integers. If you don't have the luxury of getting your ETL to do that then you need to settle for what you can but you need to understand this aspect of your data warehouse design and the implications in terms of performance.

I'm interested in learning about why you have records which seem to exist just to be month or year records.  Are you omitting keys for those entities which join your time table to fact tables at those grains?

If you're using month-row etc. as part of your keys those nulls could be a problem.