How to create a Date Dimension with hierarchy?

[melvinleng]

Hi,

How do i create a date dimension that rolls up to Accounting Period and then Year?

I believe i need to store the dates as a number instead of string, so that i will be able to do date comparision, etc.

If i have source data coming in as:
2011, 2011_P01, 2011-01-01
2011, 2011_P01, 2011-01-02
2011, 2011_P01, 2011-01-03
...

How do i do the mapping or write the TI code to load these dates as numbers? And then display them in a "YYYY-MM-DD" format in the cube view?
Otherwise, is there any 'best practices' regarding creating a date dimension?
Thanks.

Regards,
Melvin

[Alan Kirk]

How do i create a date dimension that rolls up to Accounting Period and then Year?

I believe i need to store the dates as a number instead of string, so that i will be able to do date comparision, etc.

One thing to be very clear on; you are not storing the dates "as" anything. Dimension elements are not data, they are metadata; that is, data which describes other data. In the case of a dates dimension, it describes what time period a particular numeric or string value refers to. Your dimension elements are like column and row headers in an Excel sheet; if you have a value in cell A1, then row "A" column "1" is simply telling you that a particular value is stored at that location. It tells you nothing about the actual value that is stored there.

Your element names are always "strings" in the sense that even if they're in numeric format, they're text. But that's in the same sense that row "A" and column "1" in Excel are strings. They don't store data, they simply tell you where data is stored.

If i have source data coming in as:
2011, 2011_P01, 2011-01-01
2011, 2011_P01, 2011-01-02
2011, 2011_P01, 2011-01-03
...

How do i do the mapping or write the TI code to load these dates as numbers?

1/ Forget the mapping tab, it's rubbish.
2/ Your data source appears to be comma delimited. That's fine for this purpose.
3/ Create a new process and point it to your data source.
4/ Let it read the variables. You should have three of them.
5/ On the Variables tab, set the Variable Type to String for all of them, and the Contents Type to Other.
6/ If the dimension doesn't already exist, use DimensionCreate in the Prolog tab. Use DimensionExists in an If() function if you're not sure whether it exists. (Look under the heading "Process Control TurboIntegrator Functions" in the TM1 Reference Guide for the syntax of the If() function.)
7/ On the Metadata tab, use the following functions (refer to the TM1 Reference Guide for further information about them):
- DimensionElementInsert to add the three element types. The first two columns will be C types, the last one an N type. Don't worry about the fact that the elements may have been added by previous rows (like 2011 will have been added by the first row, and will appear again in the second; TI will ignore the command if the element already exists).
- DimensionElementComponentAdd to add the period to the year and the date to the period.
- Use DimensionSortOrder to do a ByHierarchy sort to get your elements ordered correctly.

And then display them in a "YYYY-MM-DD" format in the cube view?

Because you're using the element name as it comes in from your source, your base element name will already be in the format YYYY-MM-DD.

Otherwise, is there any 'best practices' regarding creating a date dimension?

Best practice is not to ask that question around these parts, pilgrim, because you'll usually start a saloon fight. Especially when the subject of whether to use one time dimension or two is raised. (You can read a thread or two about that from the FAQ thread if you're interested.)

[melvinleng]

Hi Alan,

Thanks alot for your advise. I think i understand how TM1 dimension works now.

I worked on SAP BW before (but new to TM1), and i know that their dimension support the 'date' datatype, and so allows for a true date hierarchy, which can roll up to period, year, etc. But for TM1, it seems then dimension elements can only be string values.

The challenge i have is to be able to disply dates in different formats, (DD-MM-YYYY or MM-DD-YYYY, etc).
And i need to be able to do some date comparison, like to filter records (e.g. from 1 cube to another) based on a certain date range (e.g. last 30 days from today).

So how do i do such things when there is no true 'date' dimension?

Thanks,
Melvin

[qml]

The challenge i have is to be able to disply dates in different formats, (DD-MM-YYYY or MM-DD-YYYY, etc).
And i need to be able to do some date comparison, like to filter records (e.g. from 1 cube to another) based on a certain date range (e.g. last 30 days from today).

So how do i do such things when there is no true 'date' dimension?

Familiarise yourself with attributes, especially with aliases. Your date dimension can have aliases that would store the different date formats (as long as they're unique).
As to the date ranges - one way of achieving this is to have a separate attribute or alternative hierarchy that would store this information. E.g. you can have a consolidation called "Last 30 days" that would have the 30 respective elements underneath it. Of course, it would have to be changed every day, but this you can easily automate with a TI process/chore. Or similarly, you can have a daily updated attribute that would have a certain value for dates meeting specific criteria (this could even be rule-calculated with a little bit of effort) and then you can easily filter dates by this attribute.

[nish]

HI Alan,

I see this post is dated but I'm hoping to find an answer to a similar issue I'm facing.

I'm new to TM1, and am trying to create a continuous Time dimension. My data is coming from a date table in SQL Server. I have 5 years of data with date being the lowest level, and month, quarter and year in the table. I create an Element for date, and Consolidation elements for the month, quarter and year. However, my roll-up is not working correctly. For example, all my dates from February roll up to month February in all the five years. So 2009-02-01 element gets inserted five times in all the years. See the attached screenshots. How can I fix this?

[declanr]

HI Alan,

I see this post is dated but I'm hoping to find an answer to a similar issue I'm facing.

I'm new to TM1, and am trying to create a continuous Time dimension. My data is coming from a date table in SQL Server. I have 5 years of data with date being the lowest level, and month, quarter and year in the table. I create an Element for date, and Consolidation elements for the month, quarter and year. However, my roll-up is not working correctly. For example, all my dates from February roll up to month February in all the five years. So 2009-02-01 element gets inserted five times in all the years. See the attached screenshots. How can I fix this?

Distinct element names are needed e.g. "Q1-2010" and "Q1-2011".

[nish]

Thank you! That worked great!