Time Dimension With Multiple Sets for Different Fiscal Year Definition 1

[Rubicks]

I work for an interntional company that provides Data Warehouse modules to its clients across the globe. In an attempt to accomodate different Fiscal Year defintions for different clients, they have created an un-orthodox Time Dimension... it has different set of time records for different domains in the same Time table.

For example, for a company in, say, Mexico, with a domain of QMEX, it will have it own set of time records with its fiscal year starting in Sept and ending in Aug.
Or for a company in Canada, with a domain of QCAN, it will have its own set of time records, in the same table, with its fiscal year starting in Nov and ending in Oct.



This is creating a number of problems, especially with respect to hierarchical traversal... and, of course, this makes the attributes flexible where as normally they would be rigid.

Also, they are using for the Primary Key a decimal(18,0) data type.

I've never seen anything like this before (5 yrs BI) and I'm wondering if anyone else has.. and whether this a good idea or bad idea... and why?

 

[RiverGuy]

Make separate hierarchies for each fiscal calendar. You'd have your normal calendar hierarchy, Oct-Sep fiscal hierarchy, Jul-Jun fiscal hierarchy, 4-4-5 hierarchy, etc. If you need to analyze across different calendars, then choose the normal calendar hierarchy.