How to model a effective date dimension in DMR with alias shortcut

This document is a practical extension of concept described by my ex-post. (How to use “Alias Shortcut” for role playing dimensions in Framework Manager)  The goal is try to figure out a most effective way to design date dimension in DMR. The date dimension must satisfy following criteria:

1)    There must be only one physical date dimension. Once changed, the date dimensions at business layer will reflect the change accordingly.

2)    There must be only one date dimension at business layer. Once additional columns of date dimension changed, all date dimensions should be changed accordingly.

3)    Date dimension at date dimensional layer should be user friendly.

 

1.    Approach in detail

Database Layer:

-       Date dimension  (from physical table)

Business Layer:

-       Date1 dimension  (model from database layer and added all additional columns)
-       Date2 dimension  - alias shortcut to Date1, Please note any change of Date1 will be reflected in Date2.
-       Date3 dimension  - alias shortcut to Date1 , Please note any change of Date1 will be reflected in Date2.

Relationship

      FactTable ß----------à Date1
      FactTable ß----------à Date2
      FactTable ß----------à Date3

DM Layer:

-       Date1 dimension   --  mode dimension from Date1 from business layer
-       Date2 dimension  --  mode dimension from Date2 from business layer
-       Date3 dimension  --  mode dimension from Date3 from business layer
-       FactTable Measures

(It is impossible to define a single date dimension at DM layer and make other date dimension refer to the first date dimension. If there is any change for date 1 dimension at DM layer, you need to change Date 2 and Date 3 accordingly by manual.)

Presentation  Layer:

-       Date A dimension   --  Shortcut to Date1 dimension from DM layer
-       Date B dimension  --  Shortcut to Date2 dimension from DM layer
-       Date C dimension  --  Shortcut to Date3 dimension from DM layer
-       FactTable Measures
Date A, Date B and Date C is user friendly name.

 

2.    Sample implementation

The some screenshots below demonstrates the idea described above.

 

3.    Note

Cognos DOES NOT support reference shortcut to point to alias shortcut.