locked
Use the same dimension twice RRS feed

  • Question

  •  

    I need to use the date dimension twice within the same cube, but with different meaning, can I do this logically or should I create 2 separated dimensions?
    Sunday, June 8, 2008 6:19 PM

Answers

  • hello Jack, simply you can add the dimension twice to the same cube with different names and different relations.

    note:

    if you said the rlation correctly in the data source view then the cube will add the same dimension twice.

     

    Sunday, June 8, 2008 6:25 PM
  •  Vinival wrote:

     

    Approach 1. From table d1 on DSV we can create Dim1 and Dim2 on our SSAS project. We then add Dim1 and Dim2 to the cube, as CubeDim1 and CubeDim2.
    Approach 2. From table d1 on DSV we can create Dim1 on the SSAS project. We then add Dim1 as CubeDim1 and Cube Dim2.

     

    I normally use approach 1 when CubeDim1 and CubeDim2 have the same attributes and use approach 2 when CubeDim1 and CubeDim2 have different attributes, or when they have different keys.

     

     

     

    Thanks Vinival for your greate addition, but I think that you are reverse the use of approach 1 and 2 in, as in the above the write is:

     

    I normally use approach 2 when CubeDim1 and CubeDim2 have the same attributes and use approach 1 when CubeDim1 and CubeDim2 have different attributes, or when they have different keys.

     

    and that because approach 1 use different physical dimensions, and approach 2 use the same physical approach but different logical ones.

     

    please correct me if i understand u wrong.

     

     

     

    Heba El-Desouky

    Monday, June 9, 2008 9:14 AM

All replies

  • hello Jack, simply you can add the dimension twice to the same cube with different names and different relations.

    note:

    if you said the rlation correctly in the data source view then the cube will add the same dimension twice.

     

    Sunday, June 8, 2008 6:25 PM
  • Adding to what Heba El-Desouky said, Jack, we can also add the same dimension as different dimensions in the cube, which is known as "role-play".


    So,
    Approach 1. From table d1 on DSV we can create Dim1 and Dim2 on our SSAS project. We then add Dim1 and Dim2 to the cube, as CubeDim1 and CubeDim2.
    Approach 2. From table d1 on DSV we can create Dim1 on the SSAS project. We then add Dim1 as CubeDim1 and Cube Dim2.

     

    I normally use approach 1 when CubeDim1 and CubeDim2 have the same attributes and use approach 2 when CubeDim1 and CubeDim2 have different attributes, or when they have different keys.

     

    For example, if we have dim_date table on DSV, with attributes such as day, week number, month name, month number, quarter, year, etc.
    The requirement is to create Expiry Date dimension and Written Date dimension (say it is a cube for insurance industry). If both of them have Year, Month and Date attributes then I'd use approach 1. Where as if Written Date dimension has Year, Month and Date attributes, but they don't want Year attribute on the Expiry Date dimension, I'd use approach 2.

     

    Sometimes, fact table A has a date key column in surrogate integer format (such as 0, 1, 2, 3, ...) where as fact table B has a date key column in YYYYMMDD format (such as 20080609). In this case I'd create 2 named queries on the DSV (say d1 and d2). Both d1 and d2 are created from the same date dim table on the relational database, but taking different key columns on the named query select statement.

     

    I'd also create 2 name queries on DSV when the dimensions are from 2 different databases. For example, one is from data warehouse (DW) database and the other one is from ODS database. The one on the DW uses surrogate key where as the one on ODS use natural keys. The reason of creating a dimension from the ODS is because one measure group in the cube is from the ODS (because this measure group is not available in the DW).

     

    Here is a link to the "role play" dimension:
    http://msdn.microsoft.com/en-us/library/ms174487.aspx

     

    Hope this helps,
    Vincent

    Sunday, June 8, 2008 7:03 PM
  •  Vinival wrote:

     

    Approach 1. From table d1 on DSV we can create Dim1 and Dim2 on our SSAS project. We then add Dim1 and Dim2 to the cube, as CubeDim1 and CubeDim2.
    Approach 2. From table d1 on DSV we can create Dim1 on the SSAS project. We then add Dim1 as CubeDim1 and Cube Dim2.

     

    I normally use approach 1 when CubeDim1 and CubeDim2 have the same attributes and use approach 2 when CubeDim1 and CubeDim2 have different attributes, or when they have different keys.

     

     

     

    Thanks Vinival for your greate addition, but I think that you are reverse the use of approach 1 and 2 in, as in the above the write is:

     

    I normally use approach 2 when CubeDim1 and CubeDim2 have the same attributes and use approach 1 when CubeDim1 and CubeDim2 have different attributes, or when they have different keys.

     

    and that because approach 1 use different physical dimensions, and approach 2 use the same physical approach but different logical ones.

     

    please correct me if i understand u wrong.

     

     

     

    Heba El-Desouky

    Monday, June 9, 2008 9:14 AM
  • Yes you're right Heba, I got it mixed up. It should be the other way around as you mentioned.

    Thank for correcting it.

    Regards,

    Vincent

    Monday, June 9, 2008 10:29 AM