- Microsoft SQL Server 2014 Business Intelligence Development Beginner’s Guide
- Reza Rad
- 647字
- 2021-08-13 17:55:28
Time for action – creating a hierarchy from multiple tables
Creating a relationship between tables in tabular and creating a hierarchy based on inter-related tables are two main parts that you will see in this example. In the first part of this example, we will create a relationship between the product table and the product category and product subcategory. In addition, in the second part, we will create a product category hierarchy based on the columns of these three tables:
- In the Model menu, click on the existing connections and open the current connection. Then, in the Import Tables wizard, choose DimProductCategory and DimProductSubcategory. Change their names to Product Category and Product Subcategory. Complete the wizard and import the records.
- Go to Diagram View; you will see two new tables added. There is a relationship between the product category and product subcategory. However, there is no relationship between the product subcategory and product. So we need to create a relationship.
- Drag-and-drop ProductSubcategoryKey from the Product table to ProductSubcategoryKey in the Product Subcategory table. A new relationship will be created as a one-to-many relationship. You can double-click on the relationship to change it if you need to.
- Go to Grid View of the Product table, add a new column at the end of table, and rename it as
ProductSubcategory. Write the following expression in the textbox near the fx icon:=RELATED(ProductSubcategory[EnglishProductSubcategoryName])
- Add another column and rename it as
ProductCategory, then write the following expression for the new column:=RELATED(ProductCategory[EnglishProductCategoryName])
- Select the Product Category table and change its hidden property to true. Do the same for the Product Subcategory table. You will see that these two tables will be shown in gray after the change.
- Go to Diagram View, create a new hierarchy in the Product table, and name it as
ProductCategoryHierarchy. Drag-and-drop the Product Category column (from the Product table), the Product Subcategory column (from the Product table), and EnglishProductName. - Save the changes and view the model in Excel. You will see that a new hierarchy is created under the Product table with category, subcategory, and product names.
What just happened?
Relationships can be created easily in a tabular model. In the previous chapter, you saw that there are multiple kinds of relationships between measure groups and dimensions such as regular, referenced, fact, and so on. However, creating relationships in the tabular mode is as simple as a database relationship. This simplicity when creating relationships and also in other parts of tabular development is the main reason why a tabular model can be developed easier and faster than multidimensional. Steps 1 to 3 showed you how to add new tables into the tabular model and create or edit relationships. Only the one-to-many relationship is supported in the tabular model, but you can create many-to-many relationships with intermediate tables.
As you saw in the previous example, hierarchies can only be created from columns of a single table. As a result, if you want to create a hierarchy from multiple tables, you can use calculated columns. In this example, you saw how we created calculated columns in the product table that shows the corresponding category or subcategory value from the related tables. For creating those columns, a DAX function named RELATED is used; this function will get data from the related table's column (shown in steps 5 and 6).
After creating new calculated columns, there is no need to have two new tables from the user's point of view, so we make them hidden by just changing the hidden properties of those tables to true (step 7). We can also hide columns in each table as required.
Step 8 shows a method that is similar to the method in the previous example, same as the previous example for creating a hierarchy; it just uses columns of the product table to create a hierarchy of product categories.