Organization and user friendly data model development in Power Pivot & Tabular environments take precedence over almost all other concerns.
We will discuss best practices of creating a data dictionary, naming rules, etc. of Power Pivot & Tabular data models in other, related postings.
In this posting, we will discuss some options of organizing the data. As stated previously, if the data model is not organized in a friendly manner (i.e. dimensions split from facts, etc.), it may prove to be difficult to navigate.
Creating dimensions separate from facts allows easier navigation of the data model. Creating your calculated fields in appropriate fact tables makes it easier for the end user to find those measures. Naming fields appropriately is also key.
Power Pivot & Tabular data models differ in the development of user friendly solutions. In a Power Pivot model, you are limited to these practices when making the model more user-friendly. In some engagements, I have created a new “table” just to house some of my calculated fields. This allowed me to provide a custom name to the fact table and eased the understanding of where to access custom measures for the end user.
In a tabular model, you have other options of organizing the data. BIDS Helper allows extra features and functionality that is not available in a Power Pivot model.
BIDS Helper
BIDS Helper offers several features missing in SQL Server Data Tools that were available for multidimensional models but not for Tabular. These include the following:
Tabular Translations Editor
Per BIDS Helper documentation:
Unlike Multidimensional projects, Tabular projects in SQL 2012 do not support translations. This feature gap makes Tabular models less user-friendly when the users are international since the field list in Excel PivotTables and Power View is not in their native language.
Another common scenario where this BIDS Helper feature helps is the following. Imagine you are a developer building a Tabular model for users who speak Hebrew natively. It may be easier to code the model with English table and column names so that your DAX would be more readable. But then you want to show your model to users with Hebrew table and column names. This BIDS Helper feature would let you code in English but display to users in their native language.
Warning: While translations of metadata work in Tabular models, they are not officially supported by Microsoft. If you encounter a bug in how Tabular handles translations and open a support case, Microsoft may not provide support.
–
BIDS Helper provides a UI for editing translations on metadata (not on data). The following objects can be translated:
- Database caption
- Cube caption
- Perspective caption
- Table caption and description
- Column caption, display folder, and description
- Measure caption, display folder, and description
- Hierarchy caption, display folder, and description
- Level caption
- Action caption and description
Note: Only metadata is translated (i.e. column names) not the data (i.e. values in the rows in tables) since Tabular models do not support translating data like Multidimensional models do.
This feature allows interesting workarounds to common problems. For instance, if you would like to utilize the same cube but have different departments that require different nomenclature for the same field, you would typically have one option; create a new calculated column that duplicates the field in question and renames the field to the required nomenclature while creating a different perspective for the department requiring the rename. With tabular translations editor, you are able to reduce the memory footprint required by not requiring the extra calculated field(s). Instead, you would create a new translation “language” for each department requiring different nomenclature
* Obviously, it is preferred for the same organization to utilize a synonymous nomenclature across departments. This may be more useful in the case of a multi-tenant environment where each tenant utilizes different naming conventions. As well, be careful not to make the maintenance of multiple translations and perspectives a spaghetti nightmare!
Tabular Display Folders
Per BIDS Helper documentation:
Unlike Multidimensional projects, Tabular projects in SQL 2012 do not support display folders. This feature gap makes Tabular models less user-friendly since the field list in Excel PivotTables is longer and less organized than it could be.
–
Warning: While display folders work in Tabular models, they are not officially supported by Microsoft. If you encounter a bug in how Tabular handles display folders and open a support case, Microsoft may not provide support.
BIDS Helper provides a UI for editing display folders on measures, columns, and hierarchies. All display folders are edited in the same place. Right click on the .bim file and choose Tabular Display Folders…
This is a great feature. Typically, developers only have the naming convention and/or table creation as options to categorize their fields. This is definitely a limitation of the product as you may want your most used attributes / measures to be first in the list or grouped differently. This feature allows developers to organize attributes under dimensions or calculated fields / measures in fact tables into categories that make more sense to an end user. For example, if the date dimension has standard calendar attributes as well as fiscal calendar attributes and time related attributes, those could be organized under three different display folders under the date dimension so as to not clutter the end user’s view and make it easier to navigate to the attribute they actually require.
We will likely add to this posting as more on this in a later post as well).