The slicing and dicing of data originates from dimension table columns. The tabular model labels all fact and dimension imports as a table. The distinction between dimensions versus facts are non-existent in Tabular Models. Cleaning up the attributes of dimensions in these tables helps organize the available slicers and dicers. This leads to better adoption by users. These additional configurations include renaming a table/column, hiding a table/column, removing columns, setting display folders and concatenating columns into a computed column.
There are some cases of a slicer or dicer coming from a fact table. Sometimes there is a trend to offer all attributes in one table or view. There 2 main problems with this path. It is confusing to the end user because of the de-normalization some reporting systems provide. This might overwhelm users with the amount of options and to see the model as too complicated to use on a daily basis. The other problem shows its ugly head when the source of data gets larger and larger, hurting the processing of the model. The DAX queries start to slow down and performance tuning is needed. A good star schema is still the best practice today.
Figure 1 shows the raw table for a customer in the original model developed so far in articles 3 and 4. Here, there are too many columns and no organization.
Figure 1 Viewing in Excel - Dimension Customer
The first apparent change is the table name. This should be renamed from Dimension Customer to Customer. To rename the table, right click the Dimension Customer tab in the Grid view, or edit the Table Name in the properties like in Figure 2.
Figure 2 Rename the Dimension Custom table
The second item is dealing with columns that are not required for reporting: Customer Key, Valid From, Valid To and WWI Customer ID.
The reporting user has indicated that attributes needed are Buying Group, Category, Customer and Postal Code. Others like WWI Customer ID might be hidden in order to plan for future requirements or to be used in calculated columns. The Customer Key is needed for relationships to fact tables so it has to be hidden.
The overwhelming influence for removing a column is performance. Every column used in a table in a Tabular Model is stored in memory. So, removing the Valid From and Valid To columns from the query to import data in the model would be wise. Every row imported would not include these values. Hidden columns are still stored in memory and are imported into the model. Instead of hiding or deleting the columns, you can control the import. To start, go to the table definition shown in Figure 3 in the Grid view.
Figure 3 Properties of Customer Table
In the properties, select the ellipse in the Source Data property. The Design… button launches the M interface shown in Figure 4.
Figure 4 M Code
The Design… button opens the Power Query Editor and gives options for mashing up data. The main objective in this article is to remove unneeded columns. This is done by right-clicking on the column and selecting Remove Column from popup menu. Figure 5 shows the Power Query Editor.
Figure 5 Power Query Editor
Notice the Applied Steps to the far right. This is one of the best features in this editor. Each change made in the edit session will be recorded. The user can back track what was executed. It is kind of like Ctrl+Z (undo) and Ctrl+Y (redo) on steroids. There is a settings icon to the right of some steps which show the details of the step. This could be like a step to remove the top 2 rows. The settings would let someone change it to removing 3 rows.
After using the Control key and clicking on Valid From, Valid To and Lineage Key to multi select columns, the right click menu gives the option to Remove Columns like Figure 6. There is also the menu option to Remove Other Columns which lets the user select the columns to keep and removing the other columns not selected. The Add Column From Examples is an intelligence option to create a new column and learn from data in the table.
Figure 6 Remove 3 Columns From Dimension Customer
The next step is to click the Import button (Figure 7) in the top left to save changes and refresh the data in the model.
Figure 7 Import changes
Figure 8 shows the M code for removing columns. The graphical interface is much easier to use than learning the M code but this is one method to understand the M Code generated.
Figure 8 M Code To Remove Columns
Once back in the Grid view, right click the columns to Hide from Client Tools. Figure 9 shows an example of hiding a column. This model will hide Customer Key, WWI Customer ID, Bill To Customer and Primary Contact hidden from client tools
Figure 9 Hide from Client Tools
The last item is to create a Hierarchy with the Buying Group and Category columns. This would be considered a user hierarchy. The other type is a natural hierarchy like was created in the previous article with Year, Quarter, Month and Day. The grid view is the easiest place to create this hierarchy. Figure 10 shows the icon for Grid View.
Figure 10 Grid View
To create a hierarchy, right click on a column (Figure 11) and select Create Hierarchy.
Figure 11 Create Customer Hierarchy
Rename the hierarchy to Customer Grouping and right-click on Category (Figure 12) to add to this hierarchy.
Figure 12 Add Columns to Hierarchy
Figure 13 shows the display in Excel after these changes. This is so much better for users while reporting.
Figure 13 Updated Customer Dimension in Excel
For the City Dimension, the following needs to be done. Figure 14 shows the results in the Grid View.
Rename the table to City
Remove columns Valid From, Valid To and Lineage Key
Create 2 Hierarchies
Regions with Region, Subregion, and Sales Territory
Geography with Continent, Country, State Province, and City
City Key, WWI City ID, Sales Territory, Region, Subregion and Location
Figure 14 City Dimension Table
The Employee Dimension table needs the following updates and should look like Figure 15.
Rename table to Employee
Remove columns Valid From, Valid To and Lineage Key
Hide Employee Key and WWI Employee Id
Figure 15 Employee Dimension Table
Stock Item Dimension
Stock Item Dimension needs the following updates. A new feature used in this dimension is Display Folder. The Display Folder property helps organize columns for display and selection in reporting tools like PowerBI and Excel.
Rename table to Stock Item
Hide all columns except Stock Item, Color, Selling Package, Buying Package, Brand and Size
Change the Display Folder for Selling Package and Buying Package to Packages like Figure 16
Figure 16 Display Folder for 2 Columns
Once a Tabular Model is initially created, cleaning up the dimension attributes assist in adoption of the model. Users want as little complexity as possible. Making it simple with Display Folders and Hierarchies are the main organization options in a Tabular Model. Remembering the difference between removing or hiding a column helps with performance and cleanup. Even if a column is removed or hidden, there are options to add back to the model later if needed.