Getting started with Tabular Modeling
Create (Convert) a Tabular model
Important
Currently creating a tabular model is achieved by first creating a dimensional model, and then converting it to a tabular model.
To do so follow these steps:
Create a tabular model server if you do not have one yet.
On your multidimensional data model, in the properties pane of the model, select the tabular model server.
Save the settings and click on Convert Model to Tabular.
Differences after a model conversion
After conversion some or all of the following differences may be observed:
A Folder and model copy: A separate folder containing a copy of the original dimensional model, now converted to a tabular model. Note: The original model is untouched in its original location. Copy the new Tabular folder and model elsewhere if required.
Relationship validation errors: Relationship errors might appear on some pipelines/relationships. Refer to Relationship design errors and new validation in Tabular to understand the design differences and validations that could occur with tabular relationships.
Attribute column captions and ordering: These are implemented differently in Tabular described in Attributes - column captions and ordering.
Row level security not available: Row level security implemented by the Limit Data section of the model role, is not available for Tabular, due to the structure of data stored in columns and not rows.
Incremental data load option: Add and Update not available: Tabular can only do a full load or a add only load during processing.
Relationship active/inactive status: On conversion this is used as a strategy to avoid validation errors based on the rules described in Active / Inactive relationship conversion on upgrade Read more about the concepts of Inactive relationships below.
Relationships to Date remapped: Due to the change in key columns for Date pipelines, Relationships To the date pipeline is updated.
Modeling differences in tabular
Model design in tabular differs significantly from multidimensional modeling. The main differences are around relationships and attribute column captions and ordering.
Relationships
Tabular analysis services has been designed to work with a "Star Schema" and design principles should be followed wherever possible. More info on Star Schema design can be found in Star Schema design for a semantic layer
The most important aspect for relationships in Tabular, is relating one Fact table to many Dimension tables, to support filtering and grouping, but Tabular requires that there be only one active relationship between two tables. Because Tabular automatically chains relationships that flow in the same direction, this means there can only be one path between any two tables, whether a direct or series of relationships.
When it becomes necessary to include relationships that would otherwise produce ambiguous paths, two main strategies are discussed below:
Role playing relationships
Role-playing relationships are relationships in which the destination table is a copy of the original table, rather than the table itself. These are created in Data Hub by naming the relationship something other than the name of the destination table. When the tabular model is published the relationship will then point to a copy of the destination table named for the relationship. Role-playing relationships are useful in situations where two tables have multiple relationships between them and may need filters applied from both relationships at once. The main drawback of the use of these relationships is that the role-playing tables created increase memory usage relative to the size of the original. As such it's important to consider the destination table when making these decisions and try to avoid utilizing them for large pipelines (hundreds of thousands or millions of rows). Generally, these should be only used when the destination is a dimension pipeline and not when the destination is a fact pipeline.
Further information on the decision between active, inactive and role-playing relationships can be found in The Power BI guidance on inactive or active relationships provided by Microsoft.
Inactive relationships
Relationships can be set to be inactive within a tabular model. These relationships are then not used by default and don’t create ambiguous paths in the model. While an inactive relationship on its own has little value it can used in the calculation of a DAX measure via the USERELATIONSHIP function. This function effectively activates the relationship during the calculation of that specific measure. These measures can be created by entering their formulas in on the DAX tab of the pipeline. These inactive relationships and DAX measures are useful for individual measures on a pipeline that are to be filtered by a different relationship to the rest.
For example a sales pipeline may have multiple date relationships – order date and shipped date. Most measures on this pipeline may only care about the order date relationship for filtering purposes so this relationship would be set to be active. A separate measure used to display the number of orders shipped would need to be filtered by the inactive ship date relationship instead. By creating this measure as a DAX measure on the pipeline’s DAX tab and using the USERELATIONSHIP function to refer to that relationship this functionality can be achieved. More detail can be seen in The Power BI guidance on inactive or active relationships and documentation for the USERELATIONSHIP function can be found in USERELATIONSHIP Dax Function.
While inactive relationships that are not in use can be left in the model without contributing to ambiguous paths they provide no value and should be removed. They can later be added if they become necessary for DAX measures. Managing the relationships in this way helps reduce the complexity of a model and makes it easier to mange.
Attributes - column captions and ordering
Column captions and ordering refers to how the data will be displayed in reporting. For example, a month column may hold the value in the format yyyymm (202212), but when reported on, showing mmm yyyy (Dec 2022) may be much more pleasing and understandable. At the same time we want to retain the ability to order by the integer column, as this makes logical sense. Example 202201, 202202, 202203 etc as suppose to April 2022, Aug 2022, Deb 2022 etc
Dimensional modeling: Data Hub provides the ability to attach a Display value for each attribute by means of the Name Column property for attributes. The Order by column determines which column is used for the logical ordering.
Tabular modeling: Has no concept of name columns. instead the columns containing the "display values" should be used. In order to specify the same logical ordering, the Order by column property is provided.
Date Pipeline
The Date pipeline is regenerated on conversion and has a significantly different structure, due to the name column changes described above. The Date pipeline under multi-dimensional is made up of value columns named Calendar YQMD Year, Calendar YQMD Month, Calendar YQMD Week, etc. and caption columns named Calendar YQMD Year_Caption_1033, Calendar YQMD Month_Caption_1033, Calendar YQMD Week_Caption_1033. Each value column has its associated Caption column mapped to the name column property.
 |
Because Tabular doesn't have name columns, the date pipeline is instead made up of caption columns named Calendar YQMD Year, Calendar YQMD Month, Calendar YQMD Week, etc. and value columns named Calendar YQMD Year (Int), Calendar YQMD Month (Int), Calendar YQMD Week (Int), etc. Each caption column has its Order By Column set mapped to the value column.
 |
Date Key change and relationships TO the Date pipeline updated
Additionally, the Date column in multi-dimensional becomes Date Key in tabular (this is still the primary key column) and the associated Caption column is called Date. Due to this change in the Date Key column, relationships TO the date pipelines is also updated to map to the new Date Key column
Multidimensional Date Relationship to the "Date" Key column
 |
Tabular Date Relationship to the "DateKey" Key column
 |
Relationship design errors and new validation in Tabular
Relationship Cycles
Relationships forming a cycle is not allowed in Tabular. A relationship cycle is where pipeline A has a semantic relationship to pipeline B, and then a semantic relationship is created from pipeline B to pipeline A. This will result in a validation error.
Ambiguous Paths
Multiple paths (made up of active semantic relationships) cannot exist between two pipeline. If they do, a validation error will be shown on all the relationships involved in these ambiguous paths. Below is a simple example:
Pipeline A relates to B and C
Pipeline B relates to C
Ambiguous paths exist between A and C, thus all 3 relationships will show the validation error.
 |
 |
Note
Inactive semantic relationships and Roleplaying relationships are not considered for ambiguous paths. Roleplaying relationships where the relationship name does not match the related pipeline are considered as being related to a new pipeline.
Active / Inactive relationship conversion on upgrade
To avoid validation errors and work around restrictions on relationships in tabular as mentioned above there needs to be some conversion applied to existing relationships in the model as part of the conversion. This takes the form of setting some relationships to inactive. Which relationships become inactive is determined by the nature of the pipelines at each end of the relationship. The below MATRIX shows the conversion:
Converted TO | ||||
Converted FROM | FACT | DIMENSION | FACT DIMENSION | DATE |
FACT | INACTIVE | ACTIVE | INACTIVE | ACTIVE |
DIMENSION | INACTIVE | INACTIVE | INACTIVE | ACTIVE |
FACT DIMENSION | INACTIVE | ACTIVE | INACTIVE | ACTIVE |