# About
This site contains technical notes and articles by me, Mathias Thierbach. It is WIP, and will hopefully grow over time. All opinions are my own.
`notes.mthierba.net` is powered by [GitBook](https://www.gitbook.com/?utm_source=content\&utm_medium=trademark\&utm_campaign=mthierba), and the sources are hosted on [GitHub](https://github.com/mt-docs/notes.mthierba.net).
Topics to be expected:
* Power BI and Azure Analysis Services
* DevOps, Azure Pipelines, GitLab, GitHub Actions
* ASP.NET Core
* Azure
* Hangfire
* Orchard Core
* VS Code
* DocFX, Statiq
## pbi-tools
*
* [pbi-tools on Github](https://github.com/pbi-tools)
* [pbi-tools YouTube Playlist](https://tinyurl.com/pbi-tools)
## Contact
* Email: `contact` *at* `mthierba.net`
* Twitter: [@mthierba](https://twitter.com/mthierba)
* LinkedIn: [mthierba](https://www.linkedin.com/in/mthierba/)
* GitHub: [mthierba](https://github.com/mthierba)
* StackOverflow: [mthierba](https://stackoverflow.com/users/736263/mthierba)
* Power BI Community: [mthierba](https://community.powerbi.com/t5/user/viewprofilepage/user-id/5519)
# Analysis Services
# TOM Compatibility Levels
The missing documentation.
Ever since the convergence of Analysis Services (Tabular) and Power BI (Premium) has started, the Analysis Services API surface has evolved rapidly. New versions of the [TOM client libraries](https://www.nuget.org/packages/Microsoft.AnalysisServices.retail.amd64/) are being released frequently, and new Power BI (Premium) features introduce new TOM Compatibility Levels which must be configured at the database in order to enable certain features in the underlying engine.
However, official documentation of those feature sets and the corresponding compatibility levels and NuGet versions is very sparse.
Using the reflection approach described [in this article](/power-bi/analysis-services/list-new-features-in-tom-library), this page provides ***"The Missing Documentation"*** - a chronological listing of TOM features by compatibility level. Major features are annotated with related articles and blog posts.
* [Article: Compatibility level for tabular models](https://docs.microsoft.com/analysis-services/tabular-models/compatibility-level-for-tabular-models-in-analysis-services)
### API Changes in 19.65.7 - 6-Jul-2023
* Release of [TMDL Preview-3](https://www.nuget.org/packages/Microsoft.AnalysisServices.Tabular.Tmdl.retail.amd64/19.65.7.2-TmdlPreview) \[[NetCore](https://www.nuget.org/packages/Microsoft.AnalysisServices.Tabular.Tmdl.NetCore.retail.amd64/19.65.7.2-TmdlPreview)]
*No changes in public API*
### API Changes in 19.65.4 - 8-Jun-2023
*No changes in public API*
### API Changes in 19.64 - 4-May-2023
* Release of [TMDL Preview-2](https://www.nuget.org/packages/Microsoft.AnalysisServices.Tabular.Tmdl.retail.amd64/19.64.0-TmdlPreview) \[[NetCore](https://www.nuget.org/packages/Microsoft.AnalysisServices.Tabular.Tmdl.NetCore.retail.amd64/19.64.0-TmdlPreview)]
* REMOVED: `CalculationGroup.DefaultExpression` property *(Preview)*
* REMOVED: `CalculationExpression` class *(Preview)*
* ADDED: `CalculationGroupSelectionMode` enum *(Preview)*
* ADDED: `CalculationGroupExpression` class *(Preview)*
* ADDED: `CalculationGroup.MultiSelectionExpression` property *(Preview)*
* ADDED: `CalculationGroup.NoSelectionExpression` property *(Preview)*
### API Changes in 19.61 - 6-Apr-2023
* Release of [TMDL Preview-1](https://www.nuget.org/packages/Microsoft.AnalysisServices.Tabular.Tmdl.retail.amd64/19.61.1.4-TmdlPreview) \[[NetCore](https://www.nuget.org/packages/Microsoft.AnalysisServices.Tabular.Tmdl.NetCore.retail.amd64/19.61.1.4-TmdlPreview)]
* NEW MetadataObject types:
* `Calendar` *(Preview)*
* `TimeUnitColumnAssociation` *(Preview)*
* `CalendarColumnReference` *(Preview)*
* ADDED: `TimeUnit` enum *(Preview)*
## 1604 (new in 19.60 - 16-Mar-2023)
* ADDED: [`ModeType.DirectLake`](https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.modetype) *(used with* [*`Partition.Mode`*](https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.partition.mode)*)*
* ADDED: [`EntityPartitionSource.SchemaName`](https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.entitypartitionsource.schemaname)
### API Changes in 19.60
* ADDED: [`JsonScripter.ScriptExport(Database)`](https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.jsonscripter.scriptexport): *Create a script to export a database.*
## 1603 (new in 19.57 - 9-Feb-2023)
* [DataCoverageDefinition](https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.datacoveragedefinition) MetadataObject type: *A tabular DataCoverageDefinition object. The expression defined on this object gives hint about the data in a partition.*
* [Partition.DataCoverageDefinition](https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.partition.datacoveragedefinition): *A reference to an optional DataCoverageDefinition that provides the hint regarding the data that is covered by the partition.*
### API Changes in 19.55 - 11-Jan-2022
*No changes in public API*
### API Changes in 19.54 - 8-Dec-2022
*No changes in public API*
### API Changes in 19.52 - 10-Nov-2022
* Remarks added to [Model.ApplyRefreshPolicies](https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.applyrefreshpolicies), [Table.ApplyRefreshPolicy](https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.table.applyrefreshpolicy):
> - The execution flow of ApplyRefreshPolicy is the same as the flow that is triggered by a call to RequestRefresh, followed by a call to Model.SaveChanges.
> - The execution flow includes:
> * Calculating the expected partitioning scheme based on the effective date.
> * Comparing the expected partitioning scheme to the existing set of partitions and issue the needed changes to move to the expected scheme \[dropping, creating, and merging partitions as needed].
> * Refresh the newly created partitions as well as the existing partitions in the incremental window.
> - The only difference between the ApplyRefreshPolicy and the combination of RequestRefresh+SaveChanges is ApplyRefreshPolicy enables advanced options that are not available otherwise.
> - It is recommended to use ApplyRefreshPolicy when you want to use a single API call, especially with advanced options.
> - It is recommended to use the combination of RequestRefresh + SaveChanges when you want to combine the operation with additional authoring calls in the model before the call to SaveChanges.
### API Changes in 19.51 - 6-Oct-2022
*No changes in public API*
## 1601 (new in 19.49 - 19-Sep-2022)
Introduces **FormatStringDefinition** to measures. Previously, those only existed on [Calculation Items](https://learn.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.calculationitem.formatstringdefinition).
* [Measure.FormatStringDefinition](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.measure.formatstringdefinition): *A reference to a FormatStringDefinition object owned by this Measure.*
### API Changes in 19.48 - 15-Aug-2022
* [Model.ApplyAutomaticAggregations](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.applyautomaticaggregations): *Retrieves automatic aggregation recommendations from Analysis Services engine and applies changes to the model.*
* [JsonScripter.ScriptApplyAutomaticAggregations](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.jsonscripter.scriptapplyautomaticaggregations): *Scripts out a given Tabular database into an ApplyAutomaticAggregations command.*
## 1572 (new in 19.46 - 11-Jul-2022)
* [Table.ExcludeFromAutomaticAggregations](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.table.excludefromautomaticaggregations): *An indication whether the table is excluded from the automatic aggregations feature.*
## 1571 (new in 19.42 - 20-Jun-2022)
* [ObjectTranslation.Altered](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.objecttranslation.altered): *An indication if the translation of the property had been changed.*
## 1570 (new in 19.42 - 20-Jun-2022)
* [NamedExpression.ExpressionSource](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.namedexpression.expressionsource): *A reference to the NamedExpression where the parameter associated with the remote model.*
* [NamedExpression.RemoteParameterName](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.namedexpression.remoteparametername): *The parameter name defined in source model, applicable only for proxy model and empty for local model.*
### API Changes in 19.39 - 13-Apr-2022
* New value in [TraceEventClass Enum](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.traceeventclass)
* `DAXEvaluationLog = 135` *Output of EvaluateAndLog function.*
* New property in [ConnectionInfo Class](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.connectioninfo)
* `public AsAzureRedirection AsAzureRedirection { get; }`
## 1569 (new in 19.36 - 01-Mar-2022)
* [Model.MaxParallelismPerQuery](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.maxparallelismperquery): *Maximum degree of parallelism for query in formula engine*
## 1568 (new in 19.36 - 01-Mar-2022)
* [Model.MaxParallelismPerRefresh](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.maxparallelismperrefresh): *Determines the max possible number of parallel tasks in data refresh, within the resource constraints of the hosting service.*
## 1567 (new in 19.34 - 10-Feb-2022)
* ChangedProperties ([Column](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.column.changedproperties), [Hierarchy](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.hierarchy.changedproperties), [Level](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.level.changedproperties), [Measure](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.measure.changedproperties), [Relationship](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.relationship.changedproperties), [Table](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.table.changedproperties)): *Represents an indication of a change to one of the object's properties.*
## 1566 (new in 19.27 - 10-Nov-2021)
* [Model.DisableAutoExists](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.disableautoexists): *Disable auto exists behavior for SummarizeColumns.*
## 1565 (new in 19.26 - 08-Sep-2021)
**Hybrid Tables in PBI Premium**
*
*
*
API Changes
* [RefreshPolicyMode](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.refreshpolicymode) (Enum)
* RefreshPolicyMode.Import: *Creates import partitions during incremental refresh.*
* **RefreshPolicyMode.Hybrid**: *Creates import and DirectQuery partitions during incremental refresh.*
* [RefreshPolicy.Mode](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.refreshpolicy.mode)
## 1564 (new in 19.22 - 02-Jun-2021)
* [AutomaticAggregationOptions](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.automaticaggregationoptions)
* [Model.AutomaticAggregationOptions](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.automaticaggregationoptions)
## 1563 (new in 19.20 - 07-Apr-2021)
* [PartitionSourceType.Inferred](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.partitionsourcetype): *The data in this partition is populated by executing a query generated by the system.*
* [InferredPartitionSource](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.inferredpartitionsource): *Represents a Partition that its data will be populated by executing a query generated by the system.*
* [ParquetPartitionSource](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.parquetpartitionsource): *Represents a Partition that its data will be populated by executing a query generated by the system.*
## 1562 (new in 19.16 - 15-Jan-2021)
**Auto Aggregations**
*
*
*
API Changes
* [Table.SystemManaged](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.table.systemmanaged): *A boolean value that indicates whether the table is managed by the system. The system takes ownership of creation and deletion of such tables.*
## 1561 (new in 19.14 - 08-Dec-202)
* [SecurityFilteringBehavior.None](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.securityfilteringbehavior): *No filtering will occur from either end of the relationship.*
## 1560 (new in 19.12 - 12-Oct-2020)
* [Model.DiscourageCompositeModels](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.discouragecompositemodels): *Determines whether to discourage composite models.*
## 1550 (new in 19.9 - 10-Aug-2020)
* SourceLineageTag (Column, Hierarchy, Level, Measure, NamedExpression, Table): *A tag that represents the lineage of the source for the object.*
## 1545 (new 19.6 - 14-Jul-2020)
* [NamedExpression.ParameterValuesColumn](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.namedexpression.parametervaluescolumn): *Client tools apply filters to this column using M parameter. The presence of this property indicates model owner allows Dax queries to override this parameter, and columns data type must match the type specified in the meta tag of the parameter.*
## 1540 (new 19.4 - 16-Jun-2020)
* LineageTag (Column, Hierarchy, Level, Measure, NamedExpression, Table): *A tag that represents the lineage of the object.*
## 1535 (new in 19.2 - 01-Jun-2020)
* [Model.MAttributes](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.mattributes): *The string that has M attributes.*
* [NamedExpression.MAttributes](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.namedexpression.mattributes)
## 1520 (max level in 18.4)
* [Model.SourceQueryCulture](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.sourcequeryculture): *The name of the Culture used for formatting during refresh through Mashup.*
## 1510
* [Model.DataSourceDefaultMaxConnections](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.datasourcedefaultmaxconnections): *DataSourceDefaultMaxConnections will be used for connections to a data source if MaxConnections is set to -1 on the data source object or if there is no corresponding data source object for the data source.*
## 1500
* [CalculationItem.Ordinal](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.calculationitem.ordinal): *The zero-based ordinal value associated with a Calculation Item.*
## 1480
**QueryGroups**
* [Model.QueryGroups](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.querygroups): *Gets the collection object of all querygroups in the current Model.*
* [NamedExpression.QueryGroup](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.namedexpression.querygroup): *QueryGroup associated with the expression.*
* [Partition.QueryGroup](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.partition.querygroup): *QueryGroup associated with the partition.*
* [Table.ExcludeFromModelRefresh](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.table.excludefrommodelrefresh): *A boolean value that indicates whether the table is excluded from model refresh. When this is true, a refresh operation on the model would not trigger a refresh on the partitions of the table if they were already processed.*
## 1475
* [Model.DataSourceVariablesOverrideBehavior](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.datasourcevariablesoverridebehavior): *Controls whether this model allows data source variables to be overriden.*
## 1470
**Calculation Groups**
*
*
*
API Changes
* [CalculationGroup](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.calculationgroup), [CalculationItem](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.calculationitem)
* [Table.CalculationGroup](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.table.calculationgroup)
* [PartitionSourceType.CalculationGroup](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.partitionsourcetype): *The partition uses CalculationGroup as a source.*
* [Model.DiscourageImplicitMeasures](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.discourageimplicitmeasures): *Determines whether to discourage the implicit measures.*
## 1465
**"Enhanced Metadata Format"**
*
*
*
API Changes
* [PowerBIDataSourceVersion.PowerBI\_V3](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.powerbidatasourceversion): *Power BI V3 Data Sources support basic partition management operations.*
* [Model.ForceUniqueNames](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.forceuniquenames): *Determines whether measures can have the same names as any column in the model.*
* [LinquisticMetadata.ContentType](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.linguisticmetadata.contenttype): *Specifies the type of the linguistic metadata from the Content property. E.g. XML or JSON.*
## 1460
* [SummarizationType](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.summarizationtype) (Enum: GroupBy, Sum, Count, Min, Max): Specifies the Summarization type to be used by alternative sources' columns.
* [AlternateOf.Summarization](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.alternateof.summarization)
* [AlternateOf](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.alternateof): *Represents a AlternativeSource object. It is a child of either a Table or a Column object.*
* [Column.AlternateOf](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.column.alternateof): *Defines the AlternateOf reference source BaseTable or BaseColumn, and the Summarization.*
* [Table.AlternateSourcePrecedence](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.table.alternatesourceprecedence): *The ranking or precedence used to select the alternate source table in case more than one match is found.*
## 1455
**Dual Storage Mode**
*
API Changes
* [ModeType.Dual](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.modetype): *Allows support for dual mode of Import as well as DirectQuery.*
* [Measure.DataCategory](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.measure.datacategory): *Specifies the type of data contained in the measure so that you can add custom behaviors based on measure type.*
## 1450
**Incremental Refresh Policy (Import)**
*
*
*
*
API Changes
* [Table.RefreshPolicy](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.table.refreshpolicy)
* [BasicRefreshPolicy](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.basicrefreshpolicy)
* [RefreshGranularityType](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.refreshgranularitytype) (Enum: *Day, Month, Quarter, Year*)
* [RefreshPolicyType](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.refreshpolicytype) (Enum: *Basic*)
* [PartitionSourceType.PolicyRange](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.partitionsourcetype): *The partition uses an M expression to retrieve the data. The partition ranges are auto created based on RefreshPolicy.*
* [PolicyRangePartitionSource](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.policyrangepartitionsource)
* [PowerBIDataSourceVersion](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.powerbidatasourceversion) (Enum): *DataSource format version in Power BI Service.*
* [Model.DefaultPowerBIDataSourceVersion](https://docs.microsoft.com/dotnet/api/microsoft.analysisservices.tabular.model.defaultpowerbidatasourceversion): *Used by PBIX data source format conversion.*
## Earlier Versions
* 1100, 1103, 1200, 1400
# List new features in TOM library
How-to: Auto-generate a list of new features released in Analysis Services Tabular
This week, a new version (19.2.0.2) of the Tabular Object Model (TOM) was [released on NuGet](https://www.nuget.org/packages/Microsoft.AnalysisServices.retail.amd64/). In fact, this is a major version jump from the previous 18.7 release:
Those updates have been more frequent recently, and they usually coincide with new features coming to Azure Analysis Services or Power BI (Premium), and allow open-source tools like [Tabular Editor](https://tabulareditor.com/) to support those features.
So, those releases are generally very happy occasions and allow to become more productive with the wider Power BI ecosystem. However, it has always bothered me that the AMO/TOM libraries come with very sparse documentation, and virtually no change log or release notes. Lots of the functionality has to be discovered the hard way by trial and error.
The 19.x version number has made me particularly curious this time, and I wanted to know more. I remembered from previous use of TOM that I would occasionally receive a `CompatibilityViolationException` when running some code against an older on-prem Tabular server, for instance.
Taking a quick look inside reveals that certain parts of the API are annotated with an `CompatibilityRequirement` attribute:
The number referenced there clearly corresponds to the well-known [Compatibility Level for tabular models](https://docs.microsoft.com/analysis-services/tabular-models/compatibility-level-for-tabular-models-in-analysis-services). *Remember, 1200 was the level that properly kicked off tabular modeling with the introduction of TMSL, and 1400 was another milestone with the* [*introduction of M/PowerQuery integration into Tabular and structured data sources*](https://docs.microsoft.com/archive/blogs/analysisservices/supporting-advanced-data-access-scenarios-in-tabular-1400-models)*.*
Since the tight integration between Analysis Services and Power BI, quite a few new compatibility levels have been introduced, very recently 1520 in conjunction with the [enhanced PBIX metadata format](https://docs.microsoft.com/power-bi/desktop-enhanced-dataset-metadata).
So it didn't take very long to write a little LinqPad script to automatically extract and list all TOM API elements that happen to be annotated with that `[CompatibilityRequirement]` attribute.
Turns out the attribute internally distinguishes three different scopes, namely: "Box" (presumably on-prem SSAS), "Excel", and "Pbi". Since 1400 has been around for a long time now, the results are split into two tables below: All features introduced after 1400 first, then all others that are marked with *1200*, *1400*, or *Unsupported*.
How to read this? To take an example, the features around [Calculation Groups](https://www.sqlbi.com/articles/introducing-calculation-groups/) were introduced with level `1470` (which is also mentioned in [Kaspar's blog post here](https://www.kasperonbi.com/adding-calculation-groups-to-aas-or-pbi-premium/)). However, the ability to define a custom sort order for calculation items via the `Ordinal` property was only introduced with the `1500` level, hence requires a model at that level and a server that supports it as well.
Having checked one of our production Azure Analysis Services servers, they currently report those *SupportedCompatibilityLevels*: `1100,1103,1200,1400,1450,1455,1460,1465,1470,1475,1480,1500,1510,1520,1530,1000000`, i.e. go up as far as **1530**.
Hence, it is very interesting to see one item listing "1535" below (`MAttributes`), and even some explicitly marked as "Preview" (`AnalyticsAIMetadata`).
What is going to be announced here??
In any case, I hope this helps some folks (like myself) getting a bit more clarity about the features supported by the TOM API, and how those relate to the various new compatibility levels that were introduced post-1400.
The script used here is provided at the bottom, and can easily be re-run with any future releases appearing on NuGet!
## Table 1: Post-1400 Features
| Name | MemberType | Box | Excel | Power BI |
| ----------------------------------------- | ---------- | ------- | ------- | -------- |
| AlternateOf | Class | 1460 | 1460 | 1460 |
| AlternateOfAnnotationCollection | Class | 1460 | 1460 | 1460 |
| AnalyticsAIMetadata | Class | Preview | Preview | Preview |
| AnalyticsAIMetadataCollection | Class | Preview | Preview | Preview |
| BasicRefreshPolicy | Class | 1450 | 1450 | 1450 |
| CalculationGroup | Class | 1470 | 1470 | 1470 |
| CalculationGroupAnnotationCollection | Class | 1470 | 1470 | 1470 |
| CalculationGroupSource | Class | 1470 | 1470 | 1470 |
| CalculationItem | Class | 1470 | 1470 | 1470 |
| CalculationItem.Ordinal | Property | 1500 | 1500 | 1500 |
| CalculationItemCollection | Class | 1470 | 1470 | 1470 |
| Column.AlternateOf | Property | 1460 | 1460 | 1460 |
| ContentType | Enum | 1465 | 1465 | 1465 |
| DataSourceVariablesOverrideBehaviorType | Enum | 1475 | 1475 | 1475 |
| FormatStringDefinition | Class | 1470 | 1470 | 1470 |
| LinguisticMetadata.ContentType | Property | 1465 | 1465 | 1465 |
| Measure.DataCategory | Property | 1455 | 1455 | 1455 |
| Model.AnalyticsAIMetadata | Property | Preview | Preview | Preview |
| Model.DataSourceDefaultMaxConnections | Property | 1510 | 1510 | 1510 |
| Model.DataSourceVariablesOverrideBehavior | Property | 1475 | 1475 | 1475 |
| Model.DefaultPowerBIDataSourceVersion | Property | 1450 | 1450 | 1450 |
| Model.DiscourageImplicitMeasures | Property | 1470 | 1470 | 1470 |
| Model.ForceUniqueNames | Property | 1465 | 1465 | 1465 |
| Model.MAttributes | Property | 1535 | 1535 | 1535 |
| Model.QueryGroups | Property | 1480 | 1480 | 1480 |
| Model.SourceQueryCulture | Property | 1520 | 1520 | 1520 |
| ModeType.Dual | Field | 1455 | 1455 | 1455 |
| NamedExpression.MAttributes | Property | 1535 | 1535 | 1535 |
| NamedExpression.ParameterValuesColumn | Property | Preview | Preview | Preview |
| NamedExpression.QueryGroup | Property | 1480 | 1480 | 1480 |
| Partition.QueryGroup | Property | 1480 | 1480 | 1480 |
| PartitionSourceType.CalculationGroup | Field | 1470 | 1470 | 1470 |
| PartitionSourceType.PolicyRange | Field | 1450 | 1450 | 1450 |
| PolicyRangePartitionSource | Class | 1450 | 1450 | 1450 |
| PowerBIDataSourceVersion | Enum | 1450 | 1450 | 1450 |
| PowerBIDataSourceVersion.PowerBI\_V3 | Field | 1465 | 1465 | 1465 |
| QueryGroup | Class | 1480 | 1480 | 1480 |
| QueryGroupAnnotationCollection | Class | 1480 | 1480 | 1480 |
| QueryGroupCollection | Class | 1480 | 1480 | 1480 |
| RefreshGranularityType | Enum | 1450 | 1450 | 1450 |
| RefreshPolicy | Class | 1450 | 1450 | 1450 |
| RefreshPolicyAnnotationCollection | Class | 1450 | 1450 | 1450 |
| RefreshPolicyExtendedPropertyCollection | Class | 1450 | 1450 | 1450 |
| RefreshPolicyType | Enum | 1450 | 1450 | 1450 |
| SummarizationType | Enum | 1460 | 1460 | 1460 |
| Table.AlternateSourcePrecedence | Property | 1460 | 1460 | 1460 |
| Table.CalculationGroup | Property | 1470 | 1470 | 1470 |
| Table.ExcludeFromModelRefresh | Property | 1480 | 1480 | 1480 |
| Table.RefreshPolicy | Property | 1450 | 1450 | 1450 |
## Table 2: 1200/1400 Features
| Name | MemberType | Box | Excel | Power BI |
| ------------------------------------------------------ | ---------- | ----------- | ----------- | -------- |
| AttributeHierarchy.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| AttributeHierarchyExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| CalculatedPartitionSource.RetainDataTillForceCalculate | Property | 1400 | 1400 | 1400 |
| Column.EncodingHint | Property | 1400 | 1400 | 1400 |
| Column.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| Column.RelatedColumnDetails | Property | Unsupported | Unsupported | 1400 |
| Column.Variations | Property | 1400 | 1400 | 1200 |
| ColumnExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| ColumnPermission | Class | 1400 | 1400 | 1400 |
| ColumnPermissionAnnotationCollection | Class | 1400 | 1400 | 1400 |
| ColumnPermissionCollection | Class | 1400 | 1400 | 1400 |
| ColumnPermissionExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| ConnectionAddress | Class | 1400 | 1400 | 1400 |
| ConnectionDetails | Class | 1400 | 1400 | 1400 |
| Credential | Class | 1400 | 1400 | 1400 |
| Culture.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| CultureExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| DataAccessOptions | Class | 1400 | 1400 | 1400 |
| DataRefresh.MPartitionSourceOverride | Class | 1400 | 1400 | 1400 |
| DataRefresh.NamedExpressionOverride | Class | 1400 | 1400 | 1400 |
| DataRefresh.StructuredDataSourceOverride | Class | 1400 | 1400 | 1400 |
| DataSource.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| DataSourceExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| DataSourceOptions | Class | 1400 | 1400 | 1400 |
| DataSourceType.Structured | Field | 1400 | 1400 | 1400 |
| DetailRowsDefinition | Class | 1400 | 1400 | 1400 |
| EncodingHintType | Enum | 1400 | 1400 | 1400 |
| EntityPartitionSource | Class | 1400 | 1400 | 1400 |
| ExpressionKind | Enum | 1400 | 1400 | 1400 |
| ExtendedProperty | Class | 1400 | 1400 | 1400 |
| ExtendedPropertyType | Enum | 1400 | 1400 | 1400 |
| GroupByColumn | Class | Unsupported | Unsupported | 1400 |
| GroupByColumnCollection | Class | Unsupported | Unsupported | 1400 |
| Hierarchy.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| Hierarchy.HideMembers | Property | 1400 | 1400 | 1400 |
| HierarchyExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| HierarchyHideMembersType | Enum | 1400 | 1400 | 1400 |
| JsonExtendedProperty | Class | 1400 | 1400 | 1400 |
| KPI.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| KPIExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| Level.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| LevelExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| LinguisticMetadata.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| LinguisticMetadataExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| Measure.DetailRowsDefinition | Property | 1400 | 1400 | 1400 |
| Measure.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| MeasureExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| MetadataPermission | Enum | 1400 | 1400 | 1400 |
| Model.DefaultMeasure | Property | 1400 | 1400 | 1400 |
| Model.Expressions | Property | 1400 | 1400 | 1400 |
| Model.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| ModelExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| ModelRole.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| ModelRoleExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| ModelRoleMember.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| ModelRoleMemberExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| ModeType.Push | Field | Unsupported | Unsupported | 1200 |
| MPartitionSource | Class | 1400 | 1400 | 1400 |
| NamedExpression | Class | 1400 | 1400 | 1400 |
| NamedExpressionAnnotationCollection | Class | 1400 | 1400 | 1400 |
| NamedExpressionCollection | Class | 1400 | 1400 | 1400 |
| NamedExpressionExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| ObjectState.ForceCalculationNeeded | Field | Unsupported | Unsupported | 1400 |
| Partition.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| Partition.RetainDataTillForceCalculate | Property | 1400 | 1400 | 1400 |
| PartitionExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| PartitionSourceType.Entity | Field | 1400 | 1400 | 1400 |
| PartitionSourceType.M | Field | 1400 | 1400 | 1400 |
| Perspective.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| PerspectiveColumn.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| PerspectiveColumnExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| PerspectiveExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| PerspectiveHierarchy.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| PerspectiveHierarchyExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| PerspectiveMeasure.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| PerspectiveMeasureExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| PerspectiveSet | Class | Unsupported | Unsupported | 1400 |
| PerspectiveSetAnnotationCollection | Class | Unsupported | Unsupported | 1400 |
| PerspectiveSetCollection | Class | Unsupported | Unsupported | 1400 |
| PerspectiveSetExtendedPropertyCollection | Class | Unsupported | Unsupported | 1400 |
| PerspectiveTable.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| PerspectiveTable.PerspectiveSets | Property | Unsupported | Unsupported | 1400 |
| PerspectiveTableExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| RelatedColumnDetails | Class | Unsupported | Unsupported | 1400 |
| Relationship.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| RelationshipExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| Set | Class | Unsupported | Unsupported | 1400 |
| SetAnnotationCollection | Class | Unsupported | Unsupported | 1400 |
| SetCollection | Class | Unsupported | Unsupported | 1400 |
| SetExtendedPropertyCollection | Class | Unsupported | Unsupported | 1400 |
| StringExtendedProperty | Class | 1400 | 1400 | 1400 |
| StructuredDataSource | Class | 1400 | 1400 | 1400 |
| Table.DefaultDetailRowsDefinition | Property | 1400 | 1400 | 1400 |
| Table.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| Table.IsPrivate | Property | 1400 | 1400 | 1200 |
| Table.Sets | Property | Unsupported | Unsupported | 1400 |
| Table.ShowAsVariationsOnly | Property | 1400 | 1400 | 1200 |
| TableExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| TablePermission.ColumnPermissions | Property | 1400 | 1400 | 1400 |
| TablePermission.ExtendedProperties | Property | 1400 | 1400 | 1400 |
| TablePermission.MetadataPermission | Property | 1400 | 1400 | 1400 |
| TablePermissionExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
| Variation | Class | 1400 | 1400 | 1200 |
| VariationAnnotationCollection | Class | 1400 | 1400 | 1200 |
| VariationCollection | Class | 1400 | 1400 | 1200 |
| VariationExtendedPropertyCollection | Class | 1400 | 1400 | 1400 |
## LINQPad script
*Requires NuGet package: `Microsoft.AnalysisServices.retail.amd64` and namespace declaration: `TOM = Microsoft.AnalysisServices.Tabular`.*
* `TOM-Extract-CompatibilityRequirementsAttributes.linq` file available in [this Gist](https://gist.github.com/mthierba/4147d1cb94e67086a201dce472e3daf5).
```csharp
var asm = typeof(TOM.Server).Assembly;
var compatAttr = asm.GetType("Microsoft.AnalysisServices.Tabular.CompatibilityRequirementAttribute");
string ReadProperty(string name, object attr) => compatAttr.GetProperty(name).GetValue(attr).ToString();
string GetMemberType(Type t) => t.IsEnum ? "Enum" : t.IsInterface ? "Interface" : "Class";
Attribute GetCustomAttributeSafe(MemberInfo member, Type t)
{ // This is needed to avoid errors on a few specific attributes containing unsupported expressions - we're simply ignoring those
try {
return member.GetCustomAttribute(t);
}
catch (TOM.TomException) {
return null;
}
}
var members = asm.GetTypes()
.Select(t => new
{
Type = t,
CompatAttribute = t.GetCustomAttribute(compatAttr)
})
.Where(x => x.CompatAttribute != null)
.Select(x => new
{
Name = x.Type.FullName,
MemberType = GetMemberType(x.Type),
Box = ReadProperty("Box", x.CompatAttribute),
Excel = ReadProperty("Excel", x.CompatAttribute),
PBI = ReadProperty("Pbi", x.CompatAttribute)
})
.Union(
asm.GetTypes()
.SelectMany(t => t.GetMembers())
.Select(m => new
{
Member = m,
Name = $"{m.DeclaringType.FullName}.{m.Name}",
CompatAttribute = GetCustomAttributeSafe(m, compatAttr),
MemberType = m.MemberType.ToString()
})
.Where(x => x.CompatAttribute != null)
.Select(x => new
{
x.Name,
x.MemberType,
Box = ReadProperty("Box", x.CompatAttribute),
Excel = ReadProperty("Excel", x.CompatAttribute),
PBI = ReadProperty("Pbi", x.CompatAttribute)
})
)
.Where(x => /* toggle this for 1200/1400: */ !(((int.TryParse(x.Box, out var box) && box <= 1400) || x.Box == "Unsupported")
&& ((int.TryParse(x.Excel, out var excel) && excel <= 1400) || x.Excel == "Unsupported")
&& ((int.TryParse(x.PBI, out var pbi) && pbi <= 1400) || x.PBI == "Unsupported")))
.OrderBy(x => x.Name)
.ToArray()
.Dump();
// Convert to markdown table for blog post:
Array.ForEach(members, x => $"| {x.Name.Substring("Microsoft.AnalysisServices.Tabular.".Length)} | {x.MemberType} | {x.Box} | {x.Excel} | {x.PBI} |".Dump());
```
# XMLA Endpoint
## My Articles
* [How-to: Create a Power BI Premium dataset via XMLA endpoint and TOM](/power-bi/xmla-endpoint/create-powerbi-premium-dataset-via-tom)
## External References
* Power BI Docs: [Using enhanced dataset metadata (preview)](https://docs.microsoft.com/power-bi/desktop-enhanced-dataset-metadata)
* Power BI Docs: [Dataset connectivity with the XMLA endpoint](https://docs.microsoft.com/power-bi/service-premium-connect-tools#enable-xmla-read-write)
* Power BI Blog: ["XMLA" tag](https://powerbi.microsoft.com/blog/tag/xmla/)
* Power BI Blog: [Read/Write XMLA Endpoints in Power BI Premium Announcement](https://powerbi.microsoft.com/blog/announcing-read-write-xmla-endpoints-in-power-bi-premium-public-preview/) *(26 Mar 2020)*
* Power BI Blog: [Open-Platform connectivity with XMLA endpoints public preview](https://powerbi.microsoft.com/blog/power-bi-open-platform-connectivity-with-xmla-endpoints-public-preview/) *(28 Mar 2020)*
* Power BI BLog: [Announcing support for service principals using the XMLA endpoint in Power BI Premium](https://powerbi.microsoft.com/blog/announcing-support-for-service-principals-using-the-xmla-endpoint-in-power-bi-premium/) *(18 May 2020)*
* Article: [Using the XMLA endpoint without Premium](https://justb.dk/blog/2020/04/using-the-xmla-endpoint-without-premium/) *(7 Apr 2020)*
* Article: [What is the XMLA Endpoint for Power BI and Why Should I Care?](https://radacad.com/what-is-the-xmla-endpoint-for-power-bi-and-why-should-i-care) *(9 Apr 2020)*
* Article: [Demystifying the Power BI XMLA Endpoint](https://sqlserverbi.blog/2020/05/24/demystifying-the-power-bi-xmla-endpoint/) *(24 May 2020)*
# Deploy through XMLA Endpoint with TOM
How-to: Create a Power BI Premium dataset via XMLA endpoint and Tabular Object Model (TOM)
With the [Read/Write XMLA endpoint in public preview for Power BI Premium](https://powerbi.microsoft.com/blog/announcing-read-write-xmla-endpoints-in-power-bi-premium-public-preview/) as well as the [Power BI Enhanced "V3" Metadata Format in Preview](https://docs.microsoft.com/power-bi/desktop-enhanced-dataset-metadata), let's see what it takes to create, deploy, and modify a Power BI Premium hosted dataset using code only and the [TOM libraries](https://docs.microsoft.com/analysis-services/tom/introduction-to-the-tabular-object-model-tom-in-analysis-services-amo?view=power-bi-premium-current).
## Prerequisites
1. Power BI Premium capacity with the XMLA endpoint enabled for Read/Write.
2. A Power BI workspace assigned to the Premium capacity.
3. Access to the workspace at the *Contributor* level or higher.
4. The latest [TOM client libraries](https://www.nuget.org/packages/Microsoft.AnalysisServices.retail.amd64/), *19.2* at the time of writing.
## Enable XMLA Read/Write Mode
That is explained [here](https://docs.microsoft.com/power-bi/service-premium-connect-tools#enable-xmla-read-write) and requires a Power BI Tenant Admin to configure the capacity accordingly at .
Please note that this does not even require a full Power BI Premium account as the same functionality is available on any of the *Power BI Embedded* capacities (which are billed hourly and can be provisioned, paused, and deleted at any time, hence great for testing and POCs). *Just Thorning Blindbæk* has blogged about that approach [here](https://justb.dk/blog/2020/04/using-the-xmla-endpoint-without-premium/).
{% hint style="warning" %}
There is currently an issue when using a Power BI Embedded A SKU which prevents the Read/Write mode to be enabled successfully. The workaround for that is to either completely disable the *Dataflows* workload on the capacity, or to limit the Dataflows *Max Memory (%)* threshold to a maximum of 40%.
{% endhint %}
## Demo Setup
For this demo, we'll create a tiny model to ensure that code samples remain as simple as possible. To keep things interesting, though, we are going to connect to an on-prem SQL server, demonstrating that this approach works well even with a data gateway at play.
The model will connect to the `AdventureWorksDW2014` database, which can be retrieved [here](https://docs.microsoft.com/sql/samples/adventureworks-install-configure).
The full end-to-end script is available as a [LINQPad](https://www.linqpad.net/) script at the end.
Following common conventions, we're using two namespace aliases:
```csharp
using AMO = Microsoft.AnalysisServices;
using TOM = Microsoft.AnalysisServices.Tabular;
```
Furthermore, we'll need to reference this NuGet package: `Microsoft.AnalysisServices.retail.amd64`.
## Data Source/Query
Since we are replicating how Power BI creates datasets natively, we are using M/PowerQuery to load data into the model. The M query for this is extremely simple and only fetches three columns from the `DimCustomer` table:
```
let
Source = Sql.Database("YOUR_SERVER_NAME_HERE", "AdventureWorksDW2014"),
dbo_DimCustomer = Source{[Schema="dbo",Item="DimCustomer"]}[Data],
#"Select Columns" = Table.SelectColumns(dbo_DimCustomer,{"CustomerKey", "FirstName", "LastName"})
in
#"Select Columns"
```
## Create V3 Model with one Table
```csharp
var model = new TOM.Model {
DefaultPowerBIDataSourceVersion = TOM.PowerBIDataSourceVersion.PowerBI_V3,
Description = $"Deployed at {DateTime.UtcNow:s}"
};
```
The `PowerBI_V3` version setting represents the new enhanced metadata format which was introduced as a preview feature in Power BI Desktop March 2020. It brings with it significant improvements regarding the interplay between the tabular engine and PowerQuery which we'll look at below.
The `Description` property is entirely optional, but allows us to easily verify our deployments since the field updates each time that model definition is sent to the server.
```csharp
var table = new TOM.Table { Name = "Customers" };
table.Columns.Add(new TOM.DataColumn {
Name = "CustomerKey",
DataType = TOM.DataType.Int64,
IsHidden = true,
SourceColumn = "CustomerKey",
SummarizeBy = TOM.AggregateFunction.None
});
table.Columns.Add(new TOM.DataColumn {
Name = "FirstName",
DataType = TOM.DataType.String,
SourceColumn = "FirstName",
SummarizeBy = TOM.AggregateFunction.None
});
table.Columns.Add(new TOM.DataColumn {
Name = "LastName",
DataType = TOM.DataType.String,
SourceColumn = "LastName",
SummarizeBy = TOM.AggregateFunction.None
});
```
The three columns retrieved in the M code above are declared as model columns here, nothing special.
Finally, a simple measure is added so that the model can be consumed in a report more easily:
```csharp
table.Measures.Add(new TOM.Measure {
Name = "Number of Customers",
Expression = @"COUNTROWS(Customers)",
FormatString = "0"
});
```
## Declare M query inline as part of table partition
This step is where the enhancements of the V3 metadata model are most visible. Queries are no longer encoded in special data source connection strings. Instead, those are declared as part of the corresponding table partition. In fact, the model does not even require the definition of explicit data sources - those are simply inferred by the engine.
```csharp
table.Partitions.Add(new TOM.Partition
{
Name = "Customers-0001",
Mode = TOM.ModeType.Import,
Source = new TOM.MPartitionSource
{
Expression = String.Join("\n", new[] {
"let",
" Source = Sql.Database(\"YOUR_SERVER_NAME_HERE\", \"AdventureWorksDW2014\"),",
" dbo_DimCustomer = Source{[Schema=\"dbo\",Item=\"DimCustomer\"]}[Data],",
" #\"Select Columns\" = Table.SelectColumns(dbo_DimCustomer,{\"CustomerKey\", \"FirstName\", \"LastName\"})",
"in",
" #\"Select Columns\"" })
}
});
```
This is the same M query as shown earlier, only broken down into multiple strings and with escape characters for inline double quotes.
All that's left to do now is to add the table to the model created above:
```csharp
model.Tables.Add(table);
```
## Connect to the Premium Workspace and Create the Database
```csharp
var dbName = "AdventureWorksDW2014";
using (var server = new TOM.Server())
{
var connStrBldr = new SqlConnectionStringBuilder
{
DataSource = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORKSPACE_NAME",
UserID = "YOUR_AAD_USER_NAME",
Password = "YOUR_AAD_PASSWORD"
};
server.Connect(connStrBldr.ConnectionString);
server.Dump();
if (!server.Databases.ContainsName(dbName))
{
var db = new TOM.Database(dbName)
{
CompatibilityLevel = 1520,
StorageEngineUsed = AMO.StorageEngineUsed.TabularMetadata,
Model = model
};
server.Execute(Microsoft.AnalysisServices.Tabular.JsonScripter.ScriptCreate(db)).Dump();
}
else
{
using (var serverDb = server.Databases.GetByName(dbName))
{
model.CopyTo(serverDb.Model);
serverDb.Model.SaveChanges();
}
}
}
```
The script will create a new database with the `1520` *CompatibilityLevel* (which is required for the V3 metadata format). If a database with the same name already exists its model will be updated instead. Hence, this approach can also be used for incremental model development.
The *DataSource* connection string can be grabbed from the Premium-enabled Power BI workspace:
## First Run: Create a new database
After only a few seconds, Power BI shows a new dataset straight away:
However, the Gateway connection needs to be configured before the new dataset can be refreshed. This is only required once after creation and won't be necessary after any subsequent model changes.
Power BI has already recognized all data sources that require a gateway connection, and assuming those have been set up on a gateway, they can easily be configured in the settings for the dataset:
With that in place, a dataset refresh can be triggered via the Power BI UI, and it succeeds after only a few seconds since there is not very much data to load:
Let's create a report to verify that the dataset does indeed contain data:
This gives us the *Number of Customers* by first name - not very insightful, but sufficient to demonstrate this is a fully functional Power BI dataset now!
## Connect to the Dataset via SSMS
The same connection string can also be used to connect via SSMS:
That allows us to verify the Model *Description* property we've used for custom metadata earlier:
## Deploy a model change
First of all, the full script can simply be run again, and we would only expect the *Description* property to change and no further dataset refresh to be necessary:
Let's make a more interesting change, though, and use another new feature the V3 model gives us: **Shared Expressions**. We're taking the currently hard-coded SQL server name out of the partition of the *Customers* table, and define it as a single model-level parameter instead.
That also makes the example more realistic since any real-world dataset would surely have multiple tables/partitions connecting to the same external datasource. Defining that in only one place within the model makes it significantly easier to maintain, and also allows the model to be moved between environments more easily.
Our script only requires two modifications. Firstly, a *Named* (or *Shared*) Expression is added:
```csharp
model.Expressions.Add(new TOM.NamedExpression {
Name = "[SQL Server]",
Kind = TOM.ExpressionKind.M,
Expression = "\"YOUR_SERVER_NAME_HERE\" meta [IsParameterQuery=true, Type=\"Text\", IsParameterQueryRequired=true]"
});
```
Then, the existing partition declaration for the *Customers* table is adjusted to use a M query reference rather than the actual server name (`#"[SQL Server]"`):
```csharp
table.Partitions.Add(new TOM.Partition
{
Name = "Customers-0001",
Mode = TOM.ModeType.Import,
Source = new TOM.MPartitionSource
{
Expression = String.Join("\n", new[] {
"let",
" Source = Sql.Database(#\"[SQL Server]\", \"AdventureWorksDW2014\"),",
" dbo_DimCustomer = Source{[Schema=\"dbo\",Item=\"DimCustomer\"]}[Data],",
" #\"Select Columns\" = Table.SelectColumns(dbo_DimCustomer,{\"CustomerKey\", \"FirstName\", \"LastName\"})",
"in",
" #\"Select Columns\"" })
}
});
```
*By convention, I generally surround M parameters with square brackets to visually distinguish them from proper M query names.*
With those two changes, re-running the entire script doesn't take very long at all although we've made a relatively significant structural change.
One disappointment, however, is the discovery that Power BI is not (yet) recognizing those parameters as dataset parameters:
This is probably due to the preview status of the V3 model, and will hopefully be sorted out in the future.
We can verify that the change has gone through, though, by scripting the entire database in SSMS. The resulting TMSL script is indeed quite comprehensible:
```javascript
{
"create": {
"database": {
"name": "AdventureWorksDW2014",
"id": "XXXXXXXXXXXXXXX",
"compatibilityLevel": 1520,
"model": {
"description": "Deployed at 2020-04-13T19:35:23",
"culture": "en-US",
"defaultPowerBIDataSourceVersion": "powerBI_V3",
"tables": [
{
"name": "Customers",
"columns": [
{
"name": "CustomerKey",
"dataType": "int64",
"isHidden": true,
"sourceColumn": "CustomerKey",
"summarizeBy": "none"
},
{
"name": "FirstName",
"dataType": "string",
"sourceColumn": "FirstName",
"summarizeBy": "none"
},
{
"name": "LastName",
"dataType": "string",
"sourceColumn": "LastName",
"summarizeBy": "none"
}
],
"partitions": [
{
"name": "Customers-0001",
"mode": "import",
"source": {
"type": "m",
"expression": [
"let",
" Source = Sql.Database(#\"[SQL Server]\", \"AdventureWorksDW2014\"),",
" dbo_DimCustomer = Source{[Schema=\"dbo\",Item=\"DimCustomer\"]}[Data],",
" #\"Select Columns\" = Table.SelectColumns(dbo_DimCustomer,{\"CustomerKey\", \"FirstName\", \"LastName\"})",
"in",
" #\"Select Columns\""
]
}
}
],
"measures": [
{
"name": "Number of Customers",
"expression": "COUNTROWS(Customers)",
"formatString": "0"
}
]
}
],
"expressions": [
{
"name": "[SQL Server]",
"kind": "m",
"expression": "\"XXXXXXXXXXXXXXX\" meta [IsParameterQuery=true, Type=\"Text\", IsParameterQueryRequired=true]"
}
]
}
}
}
}
```
## Full Demo Scripts
The initial script as well as the updated version is available [in this Gist](https://gist.github.com/mthierba/33ee04af989562edb08b534755350a9a).
## Updates
### 2020-06-02
* Bumped AMO package version number to 19.2
* Changed the *Create New Database* section of script as previous version was no longer working as expected in a Power BI Premium workspace (Data sources did not get recognized when an empty database was created first and then updated. The new script version generates a single command to create and define the entire database.)
# How-To Articles
# Download PBIX for Report created in Service
How-to: Download the .PBIX file for report when the option is greyed out
*I've been there many times myself, and I have witnessed other people having the problem: The ability to create a new report directly in Power BI Service, connected to an existing dataset, is very powerful for its agility.*
*It comes with a huge limitation, though: **The resulting report cannot be downloaded as a PBIX file**, hence cannot be edited further in Power BI Desktop, or shared or archived as a file. Sometimes, new users aren't even aware of this, invest a lot of time and energy into building beautiful reports directly in the browser, only to realize later that they are now locked in.*
Here is the same problem reported in a Power BI Community Forum:
* [Can't download .pbix from report on service](https://community.powerbi.com/t5/Service/Can-t-download-pbix-from-report-on-service/td-p/854601)
**This article gives you a solution which works for anyone without any advanced technical skills.** It is based on two important insights:
{% hint style="info" %}
1. For a report to be downloadable as a .pbix file, it must have been created in Power BI Desktop originally.
2. The `Update Report Content` API allows to swap the contents of any two reports in Power BI Service, provided they are connected to the same dataset.
{% endhint %}
Putting those two points together, we can come up with this process to download a report PBIX that was previously blocked from being downloaded:
1. Report `A` has been created from dataset `B` in Power BI Service, and cannot be downloaded.
2. In Power BI Desktop, create a new report `C` connected to the same dataset `B`, leave it otherwise empty, and upload it to the same workspace. This report **can** subsequently be downloaded as a .pbix file, even once edits have been made to it in the Service.
3. Use the [Update Report Content API](https://docs.microsoft.com/rest/api/power-bi/reports/updatereportcontentingroup) - which can be triggered interactively on that docs page - to update report `C` with the contents of report `A`.
4. Download report `C`, now containing all the pages, filters and visuals from the original report `A`, and make further edits in Power BI Desktop.
## Using the *Update Report Content* API
The API requires a POST request to be made to this url: `https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports/{reportId}/UpdateReportContent`.
`{groupId}` and `{reportId}` represent the report **to be updated**. In our case that is the one we've uploaded as an empty/dummy report from Power BI Desktop, report `C`.
The request must be made with a json body like this:
```javascript
{
"sourceReport": {
"sourceReportId": "8e4d5880-81d6-4804-ab97-054665050799",
"sourceWorkspaceId": "2f42a406-a075-4a15-bbf2-97ef958c94cb"
},
"sourceType": "ExistingReport"
}
```
That describes the report the content is coming from, in our case report `A`. *`"sourceType": "ExistingReport"`* always has to be specified exactly like that.
If both reports are in the same workspace, `sourceWorkspaceId` does not need to provided, which simplifies the format further:
```javascript
{
"sourceReport": {
"sourceReportId": "8e4d5880-81d6-4804-ab97-054665050799"
},
"sourceType": "ExistingReport"
}
```
Let's start by collecting the necessary information. There are two reports in my workspace - one *Created in Service*, which cannot be downloaded, one *Created in PBI Desktop*, which can be downloaded:
The *Created in PBI Desktop* report is the one to be updated. Its url is: `https://app.powerbi.com/groups/7e2f5035-afa4-49c9-8a6d-9c5fdff2245d/reports/d7de9a81-ab33-48d1-b124-0305e4a9276f/ReportSection`.
Hence, I can extract:
* `groupId` = `7e2f5035-afa4-49c9-8a6d-9c5fdff2245d`
* `reportId` = `d7de9a81-ab33-48d1-b124-0305e4a9276f`
The *Created in Service* report is the source report, and its url is: `https://app.powerbi.com/groups/7e2f5035-afa4-49c9-8a6d-9c5fdff2245d/reports/51a1cd5d-78b7-434c-8268-08d3c63882a4/ReportSection`.
That gives me:
* `sourceReportId` = `51a1cd5d-78b7-434c-8268-08d3c63882a4`
## Invoking the *Update Report Content* API interactively
Let's navigate to the API Docs page for [Update Report Content](https://docs.microsoft.com/rest/api/power-bi/reports/updatereportcontentingroup). It has that really useful green "Try It" button:
Clicking this opens a new panel on the right with a *Sign in* button. Use that to sign in with your Power BI account:
Then, simply fill in the parameters collected earlier:
...and click the green "Run" button below:
If everything went well, "Response Code: 200" will be reported:
I can now open the report `C`, the one uploaded from Power BI Desktop earlier. It has exactly the same contents as report `A`, however, this one can also be downloaded!
Problem solved.