SSAS Tabular Models

BimlFlex can generate a SQL Server Analysis Services Tabular model and an Analysis Services project for each project in a build. The model comes from the same semantic model definition that produces the Power BI Direct Lake output described in Semantic Models, so one set of metadata describes the dimensional model for both. Tabular model generation is set per project with the SsasTabularEnable setting and is off by default.

What a build emits

For every project whose SsasTabularEnable setting is on, the build generates one Analysis Services Tabular model and one Analysis Services project. This applies to any integration template, so SSIS, SQL and Fabric projects alike can carry a Tabular model. The output reads projects whose target connection is SQL Server, Azure SQL Database or Azure Synapse (the system types MSSQL, SQLDB and SQLDW). A project whose target connection has another system type is skipped with a warning that names these system types.

The model name is the project name. Two projects that resolve to the same model name are reported as an error, and neither model is emitted.

The Biml compiler writes the output to the SSAS folder under the build output path, one folder per project:

SSAS/
    <Model>_Tabular/
        <Model>.bim
        <Model>_Tabular.smproj
        <Model>_Tabular.sln

The .bim file holds the model at the project's compatibility level. The .smproj file is the Analysis Services project, and the solution file opens it in Visual Studio.

Tables and columns

The model holds the dimensions and facts of the project that are not excluded from the model, the same selection the Direct Lake output makes. Each table follows its data warehouse table:

• The model columns carry the column names of the data warehouse table.
• Every column carries a data type, the same type the Direct Lake output gives that column. A column of the Decimal or VarNumeric data type with at most 4 decimal places and at most 15 integer digits, a precision less scale of at most 15, carries the fixed decimal type Decimal. Any other shape of these types, or one with no precision or scale, carries Double at every compatibility level, and the build reports a warning that names the column and ends with It is mapped to double with possible precision loss. The fixed decimal type holds values up to 922,337,203,685,477.5807, so a column of exactly 15 integer digits, such as a precision of 19 with a scale of 4, that holds a larger value fails processing with no build warning. The key column of each dimension is marked as the table's key. On a dimension that the semantic.dataCategory key marks as the date table, as Data categories describes, the date column is the table's key in place of the integer key. The relationships on the integer key stay as they are, and the date column keeps the sort by column it has, such as a sort by the integer key.
• Columns the data warehouse table leaves out are not in the model, and columns excluded from the model are left out.
• Measures and hierarchies come from the semantic model overrides, exactly as for the Direct Lake output.
• A hidden column is marked as not available in MDX and is left out of the MDX surface of the model, unless it has its own sort by column, a column of its table sorts by it or a level of the table's hierarchy uses it. The SSAS Tabular output and the Direct Lake output both apply this rule.
• Each table carries one partition whose query selects the table's columns from the data warehouse table.

A column in a Tabular block supplied through the AnalysisMetadata extension point keeps the DataType it declares. A declared Automatic means the type is inferred from the data warehouse column.

Every generated model carries one provider data source for the data warehouse connection. The model uses this data source at every compatibility level.

Relationships

Relationships come from the reference columns in the metadata. Each relationship joins the referencing table to the key of the referenced table, many to one.

When a Tabular block supplied through the AnalysisMetadata extension point infers a relationship from a reference column, the relationship names the Tabular table and column over the foreign table and its key column. When the model holds two Tabular tables over one relational table, the first one in model order is the foreign end. When the model holds no Tabular table over the foreign table, or that table holds no column over the key column, no relationship is inferred and the compile reports one warning that names the missing end.

Enabling and configuring

Tabular model generation is enabled and configured from the settings editor. The settings live in the Analysis Services group:

Setting	Default	Behavior
SsasTabularEnable	N	Turns Tabular model generation on for a project. A project scoped value overrides the instance value.
SsasTabularServer	blank	The Analysis Services server named in the generated project. Blank means localhost.
SsasTabularDatabase	blank	The Analysis Services database named in the generated project. Blank means the model name.
SsasTabularCompatibilityLevel	blank	The compatibility level of the model: 1200, 1400, 1500, 1600 or 1700. Blank takes the level of the SsasTabularVersion build setting. Any other value is reported as an error, and no model is generated for that project.

Each compatibility level pairs with a SQL Server Analysis Services version, 2016 or later, and a deployed model needs an instance of at least that version:

Compatibility level	SQL Server Analysis Services version
1200	2016
1400	2017
1500	2019
1600	2022
1700	2025

The output script

The bundle script Create SSAS Tabular Model (_OutputSsasTabularModel.biml) adds the Tabular output to the build and toggles like the other output scripts. It is off when the build's emit targets leave out SsasTabular, under the TablesOnly preset and on a Preview build.

Licensing

The Tabular output is available under the same product capability as the semantic model output. A license that withholds the capability reports one error at build, starting with Error generating the SSAS Tabular models: followed by the capability name, and writes no Tabular file.

Customizing a table

A table's AnalysisMetadata extension point keeps precedence over the generated table. When the extension point supplies a Tabular block for a table, the build uses that block as written in place of the generated one, and the model holds the table through the supplied block together with its relationships.

Perspectives and KPIs

A model with two or more fact tables gets one perspective per fact, named after the fact. Each perspective holds the fact and every dimension a relationship leads to from it, with every column, measure and hierarchy of those tables. A model with a single fact gets no derived perspective. When two facts have Tabular names that differ only in case, each model names the second perspective with a numeric suffix and reports one warning of its own that names both facts and the suffixed name. A model with the facts Inventory and inventory holds the perspectives Inventory and inventory 2 in the Direct Lake, Import and SSAS Tabular models. Give the two facts object names that differ by more than case. Perspectives added through the SsasTabularModel extension point are written as given and are not renamed. When such a perspective matches a derived perspective or a semantic.perspective key of the model when case is ignored, Analysis Services refuses the model, so the build reports a warning that says to rename the extension point perspective and writes the element as it is.

Named perspectives, KPIs and data categories come from attributes on the table or its columns:

Attribute key	Level	Value	Property	Description
semantic.perspective	Table	The perspective name
semantic.kpi	Table or column	The name of a measure of the same table	The target DAX expression	The KPI description
semantic.kpiStatus	Table or column	The name of the same measure	The status DAX expression	The status graphic
semantic.dataCategory	Table or column	Time on a table, or one of the fourteen column categories on a column	The date column of a Time table

semantic.perspective adds the table to the perspective of that name, matched ignoring case. A name that no fact derives creates the perspective. The key is a table level key. On a column it is ignored with a warning, so set it on the table.

A semantic.perspective key whose name matches a suffixed perspective name when case is ignored is renamed as well, and the build reports a warning that says to give the key a name no other perspective of the model uses.

semantic.kpi names a measure of the same table, either a generated measure such as SalesOrder Count or one declared with semantic.measure. A KPI needs both keys for the same measure. A semantic.kpi key without a matching semantic.kpiStatus key is left out with a warning. The status graphic is one of TrafficLightSingle, ThreeFlagsColored, RoadSigns, TrafficLight, ThreeSymbolsUncircledColored, Shapes, ThreeStarsColored, FiveBarsColored, FiveBoxesColored or GaugeAscending, matched ignoring case. A blank graphic means TrafficLightSingle.

Data categories

semantic.dataCategory with the value Time on a dimension marks the dimension as the model's date table, which the DAX time intelligence functions need when the relationship from the fact runs on an integer date key. The date column is the model column of the date time data type whose metadata column name the key's property names, matched ignoring case. With a blank property, the date column is the table's one model column of the date time data type, and system columns do not count. A column has the date time data type when its metadata data type is Date, DateTime, DateTime2, DateTimeOffset or Time. The table carries the category Time, and its date column becomes the table's one key column in place of the integer key, because a table has one key column and the key of a date table is its date column. The relationships on the integer key stay as they are, and the date column keeps the sort by column it has. When the date column does not resolve, the build reports a warning that names the table and writes no category. That happens when the property names no model column of the date time data type, or when the property is blank and the table has no such column or more than one. Name the date column in the property when a table has more than one.

The property names a business column of the table. When the property names a system column, such as the row start and end dates, the build reports a warning that names the table and the column and states that a system column does not carry the date table key, and the category is not written.

On a column the value is one of the fourteen Power BI column categories: Address, City, Continent, Country, County, Image, ImageUrl, Latitude, Longitude, Organization, Place, PostalCode, StateOrProvince or WebUrl. The value is matched ignoring case and written in the casing listed here, so country is written as Country. Uncategorized on a column writes nothing and reports nothing.

Any other value writes nothing and reports one build warning that names the table, and the column for a key on a column. That covers a column category on a table, Time on a column, a value that is neither Time nor a column category, and Time on a fact table.

The Direct Lake output writes dataCategory on the date table and on each column with a category. The SSAS Tabular output writes DataCategory on the date table's Tabular element and on each such Column element, and the model file carries the category at every compatibility level from 1200 to 1700, so the deployed model carries it.

The build does not check that the date column holds unique dates with no gaps at day grain, as Power BI does when a person marks a date table. A date dimension at day grain meets that rule by its design.

These keys and the derived perspectives apply to both outputs. The Direct Lake model and the SSAS Tabular model carry the same KPIs, perspectives and data categories from the same metadata.

Roles and model elements

The project extension point SsasTabularModel carries child elements of the Tabular model as Biml text, with no Biml root element:

• A Principals element with the principals, each with its members, a TabularModelPermission or TabularTablePermission, and the data warehouse connection in ConnectionName.
• A Roles element that lists the principals by Principal. When the text carries a Roles element, the model holds exactly the listed roles.
• Optional Perspectives, CalculationGroups or Translations elements.

A TabularTablePermission names its table by the scoped name of the table's Tabular block, in the form Database.Schema.Table.Table, not by the display name. Text inside a Biml root element reads as the root's child elements, with a warning. The tables and relationships of the model always come from the metadata, so Tables or Relationships elements in the text are left out with a warning.

How output is verified

Emitted output is checked for compatibility with Microsoft's publicly available Tabular Object Model library at every compatibility level. That check is compatibility verification, not a certification or endorsement by Microsoft.

Generation is deterministic. The same metadata produces the same output on every build.

Deploying and operating the model

1. Deploy. Open the generated project in Visual Studio and deploy it, run the Analysis Services Deployment Wizard on the project's build output, or deploy the .bim file through the Tabular Object Model with a script. The target is an Analysis Services instance in Tabular mode whose version supports the model's compatibility level, as the table above lists.
2. Grant access. The generated data source impersonates the service account of the Analysis Services instance. Grant that account read access on the data warehouse database. For a named instance that runs under its default service account, the account is NT SERVICE\MSOLAP$<instance>.
3. Check the provider. The data source connection string is the data warehouse connection's string as entered in BimlFlex. On a server without the provider that string names, set the provider in the data source to the Microsoft OLE DB Driver 19 for SQL Server (MSOLEDBSQL19) before processing. With driver 19, encryption is on by default, so the connection string carries Use Encryption for Data=Optional unless the server presents a certificate the driver trusts.
4. Process and query. Process the database in full after the deploy, then query the model.

The model deploys and processes at every compatibility level from the one definition.