Share via

Top Tip: How to use the same dimension twice on one fact table with SQL Server 2000 Analysis Services

  • Article

I’m grateful to Cristian Petculescu for this Top Tip: How to use the same dimension twice on one fact table with SQL Server 2000 Analysis Services.

To use the same dimension twice, against one fact table, you essentially use the same dimension table only with a new alias to reference it by. To do this copy the dimension (right click it in Analysis Services Manager) and then paste it back in again. At this point it will tell you you already have a dimension by that name, so you edit the name it presents in the dialog box to a new name (thereby creating an alias).

Now in SQL Server 2005 it will let you do this with the same dimension natively without the alias.

Comments

  • Anonymous
    May 16, 2005
    Hi Mat,
    Personally when I've done this in the past I've accomplished it by building a view on top of the dim table and building the second dimension off that.
    In my experience, if the saame dimension table is being used for 2 dimensions it is because there is a subtle difference between the 2 dimensions (e.g. perhaps one of them required the Unknown member in the dimension whereas the other doesn't) and it is usually possible to model this subtle difference in the view.

    Just my opinion...!!!

    -Jamie