In the previous recipe, we created a three-level dimension with a denormalized table. Let's do it with normalized tables. Product, Product Subcategory, and Product Category all reside in different tables related with referential integrity constraints as clearly described in the database diagram.
Like I said earlier, I always set the mapping method to manual whenever I create a new attribute. That prevents the editor from looking at every column in the database with the same name and add it automatically to the attribute definition. I prefer to have control over which tables are selected and which are not when it comes to generating SQL. You may also have noticed that some tables are bold in the attribute editor and some are not. Bold tables are the primary source for that specific attribute, often referred to as lookup tables .
We will begin this time from the top of the dimension, Product Category.
Follow these steps:
- Create the Product Category attribute and drag ProductCategoryKey column as ID from the DimProductCategory table, set the Mapping method to Manual, and hit OK.
- When you are in the Create New Attribute Form window, you'll see that there are two tables in the Source tables pane on the right (DimProductCategory and DimProductSubcategory).
- Check both of them, the bold one should be DimProductCategory, if not, use the Set as Lookup button to make it bold.
- Create the DESC form with the EnglishProductCategoryName field from DimProductCategory.
- Close the editor and save the attribute as
Product Category. - Now create the
Product Subcategoryattribute, with ProductSubcategoryKey as ID, set the Mapping method to Manual, and check both DimProduct and DimProductSubcategory, this time the bold one should be the latter. - Use EnglishProductSubcategoryName as the DESC form (Manual mapping).
- Now before saving the attribute, click on the Children tab and add Product (the Product attribute was created in a previous recipe) then click on the Parent tab and add Product Category.
- Click on Save and Close, name it
Product Subcategoryand update the schema.
The Product Category attribute ID appears in two tables: the column ProductCategoryKey is PK in the DimProductCategory table and FK in the DimProductSubcategory. When we set the DimProductCategory to bold, we specify that the one with the PK is the lookup, that is, the principal source of information for that attribute, where the description comes from. We then select the second table to tell MicroStrategy that those two columns are the same ID.
Whenever the two tables appear in the same SELECT, they will be joined on ProductCategoryKey.
Likewise, the Product Subcategory and Product attributes are related with ProductSubcategoryKey being it the PK in DimProductSubcategory and the FK in DimProduct.
There is hence a cascading relationship that goes from Product Category to Product passing through Product Subcategory, which is the attribute that relates upward with Product Category and downward with Product.
We need to specify parent/child link only once in either attribute, as the setting will be automatically reflected to the corresponding counterpart.