Recently there was a requirement for organizing columns from certain dimensions into individual entities within the presentation layer of powerview and powerbi. The approach that was taken was to create additional dimension tables that reference only those subset of columns. Similar to the diagram below.
While the above design will do the job, we see that the relationship is 1:1 and therefore in the interest of designing a star schema we need to denormalize the data so that it look like the table Customer_source in the data source view of the cube while still providing a split into column subsets as shown on the right hand side of the above diagram.
In this case we import the table customer_Source as is and then use BIDShelper to give logical folder paths to the columns as needed. You can download BIDS helper from here. Make sure to check the version as per your installation of SQL Server. Once installed you can right click the model (in this case I am using Tabular model) and navigate to the folder Tabular Display Folder as shown below.
In the below screenshot you can see how certain columns from within the fact table have been bucketed under a folder called time and then a folder as per the nature of the column.
The same kind of hierarchy can also be implemented on dimension tables as well as shown below:-
The end result will look as shown in the below screen, this way we do not need to compromise on the Cube design to accommodate presentation layer requirements.
|
|
SSMS Browser | Excel |
Please Consider Subscribing
