Display Column into a Specific Table

Question

Goal:
Display the data of column ProductSubcateoryname into the table DimProduct.

Problem:
The data of column ProductSubcateoryname exist only in the table DimProductSubcategory but not in the table DimProduct.

How should I enable to make the data of column ProductSubcateoryname to be available in table DimProduct by using Analysis service?

Answer 1

There are several possibilities:

• You can set up the Subcategory dimension as a referenced dimension. To do this, make sure you have the ProductSubcategoryKey as a - probably invisible - attribute in your product dimension. Then go to the cube object, "Dimension Usage" tab, and for each measure group, click on the rectangle at the intersection of the measure group and the Subcategory dimension, and set the relation type to "Referenced", select the intermediate dimension "Product", and select the subcategory key as join key for both sides.
• You can integrate the subcategory table into the product dimension table using a named query in the data source view. You would then just set up one product dimension, and not have a separate subcategory dimension. Your query would look like this:

    SELECT p.ProductKey, p.ProductLabel, ... -- all DimProduct columns
          ,s.ProductSubcategoryLabel, ... -- all DimProductSubcategory columns
      FROM DimProduct p INNER JOIN DimProductSubcategory s
           ON p.DimProductSubcategorykey = s.DimProductSubcategoryKey

• Of course, you can also integrate the subcategory table into the product table using a view on the database side - which you then reference instead of the tables. The definition of the view would be the same as that for the query above. Just the named query is part of the cube definition without affecting the relational database, while this approach would code this on the relational side, and leave the DSV simple. Where you do this type of adaptation is a matter of personal preference or convention.