Unified dimension

The reason behind this post is that I quite often meet the need for some little dimensions for certain cubes. Little now means that they consist of only a couple of attributes (like e.g. a Salesmethod dimension with two members: Reseller, Internet).

These dimensions are totally independent from each other so it would be separate them and create individual dimensions for them. But since we have too much dimensions anyway I began thinking of how to put all of them into one single dimension. It would be easier for us, developers, to maintain the solution this way I think.

So let's see what I've figured out :) I've created some demo files for it that can be downloaded from here:

  • a SQL Server 2005 database (UniDim.bak)
    • dbo.FactTable (7 rows) with a Sales column and two foreign key columns (for a SalesMethod and a Language dimension)

    • dbo.UniDim (4 rows) with an identity column for surrogate keys, an AttributeID (that will help connecting the fact table to this table but for that we will use a view), an AttributeText (the name for the ID like Reseller or Internet for SalesMethod), and a DimensionName (this tells us which dimension the certain row is used for) - maybe it seems a little complicated but if you take a look at the tables it will be simple enough :)

    • dbo.V_UniDim (4 rows) - this view is like a union of two separate dimension tables with an additional ID column and there are two columns for each dimension (1 ID and 1 name column), the data of different dims should be in separate columns in order to have different attributes in the AS Dimension.

  • a SQL Server 2005 Analysis Services database (UniDim.abf)
    • set the datasource to point to that freshly restored UniDim database
    • the DSV contains the FactTable and the V_UniDim view with relationships as follows:

    • same for the SalesMethod

    • The dimension is quite simple:
      • key attribute based on the ID column of the view (AttributeHieararchyVisible: False)
      • Language attribute (key column: LanguageID, name column: Language)
      • SalesMethod attribute (key column: SalesMethodID, name column: SalesMethod)
    • The cube itself is also simple, the trick comes when connecting the dimension!
      • when you add the UniDim to the cube you'll get the following result:

      • so the same dimension is added twice (under different aliases) because of the two different relationships with the fact table

      • the same for the Language dimension / attribute hierarchy
      • when browsing the cube in its present state you almost get the desired results except for that:

      • but that's not nice so one trick is to hide the Language.SalesMethod attribute from the Language dim (and the same for the other): go to the Cube Structure pan, switch to Attributes in the lower left part and set the AttributeHierarchyEnabled and AttributeHierarchyVisible properties false for Language\SalesMethod and for SalesMethod\Language:

      • Now let's take a look at the cube browser:

      • almost looking good the only problem is that the dimension name is explicitly showed before the attribute's name. The data is correct in the cube.
      • I know of the HierarchyUniqueNameStyle property of the CubeDimension that can be set to ExcludeDimensionName but it can be set for only one hierarchy because of the naming conflict.

If anyone has an idea how to display the attribute names without the dimension prefix I would welcome it in email (martinisti@gmail.com) since anonymous comments are disabled on this site. First I thought of creating a calculated member for the dimension but I'm a beginner in MDX so it's just an idea so far.

Any comments (emails) are welcome!

Published Tuesday, June 16, 2009 11:45 AM by MartinIsti


No Comments
Powered by Community Server (Commercial Edition), by Telligent Systems