- Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide
- Reza Rad
- 338字
- 2021-08-13 17:55:24
Time for action – creating a hierarchy
In this example, we will see how a user can create hierarchies in the browser and how a developer can create a predefined hierarchy for business requirements by performing the following steps:
- Go to the cube Browser, drag-and-drop Calendar Year, Month, and Date, one after another. Then, drag-and-drop Sales Amount and Order Quantity. You will see a hierarchy created dynamically from the browser.
- Now, go to the product's Dimension Designer, drag-and-drop the EnglishProductCategory name into the Attributes pane, and rename it
ProductCategory. Do the same for EnglishProductSubcategoryName and rename itProductSubcategory. Rename the Product Key attributeProductand change its name column to EnglishProductName. - Drag-and-drop Product Category from the Attributes pane into the Hierarchies pane.
- A new hierarchy will be created; rename it
ProductCategoryHierarchy. - Drag-and-drop the product Subcategory under the Product Category option (in the <new level> area), and then add Product under that.
- Select Product Category and change the AttributeHierarchyVisible option of this attribute to false. Do this for the Product Subcategory and Product attributes as well.
- In the Dimension browser, you will see that only Product Category Hierarchy is listed, and the values of this hierarchy have created a tree of Product Category, Subcategory and Product.
What just happened?
In this example, you created a user hierarchy easily with just a few drag-and-drops. The hierarchy of this example is created from one table. Creating hierarchies from multiple related tables is as simple as creating a hierarchy from a single table; you just need to import attributes from the Data Source View pane into the attributes area and you can then create a hierarchy based on the attributes.
You also saw that after creating Product Category Hierarchy, we set the visibility of the attributes to false. As the user can access the Product Category, Subcategory, and Product attributes through Product Category Hierarchy, there is no need to have separate attributes. Making them invisible from the user's perspective will reduce the user's confusion with regards to too many attributes.