Converting partitions from M to Legacy
When building Analysis Services tabular models on top of a Data Warehouse or Data Mart on a relational database, I recommend using the Legacy (Provider) data sources instead of the Power Query data sources available since SQL Server 2017. Unfortunately, Power Query data sources have become the default in SSDT, and it has become quite tricky to create Legacy data sources (in short, check “Enable Legacy data sources” under Options > Analysis Services Tabular > Data Import).
There are a couple of reasons why I prefer Legacy data sources:
- Refresh performance is similar, but in my experience the Power Query data sources have a small initialisation overhead, which can be annoying if you need to do many frequent, small refreshes.
- You don’t want to do any M transformation in your Tabular model partition queries anyway – that’s the whole purpose of having an ETL process where data is loaded into a star schema on the relational source.
- When deploying a model or executing a CreateOrReplace TMSL script, credentials used by legacy data sources are not dropped.
- You get to use the sweet Tabular Editor Import Table wizard
If you already created your model using a Power Query data source and M partitions, here are the steps you need to do, in order to switch to Legacy:
- Create a Legacy data source on your model and point it to your relational database. Give it a name, for example, “SQLDW”.
- Paste the following script in Tabular Editor’s Advanced Scripting tab:
var legacy = (Model.DataSources["SQLDW"] as ProviderDataSource); foreach(var table in Model.Tables) { if(table is CalculatedTable || table is CalculationGroupTable) continue; table.Partitions.ConvertToLegacy(legacy); // foreach(var partition in table.Partitions) partition.Query = "SELECT * FROM " + table.Name; } - Before running the script, adjust the name of the data source in line 1, if you provided a different name for the new legacy data source.
- (Optional) If the names of the imported tables in your model correspond to the names of tables or views in your data source, you can uncomment line 7 to automatically set the query of each partition to a basic
SELECT * FROM <table/view name>-query. - Run the script
- Go through each partition in your model to verify that the partition is of the correct type (Legacy), and that the partition is using the proper data source. If you skipped step 4, also make sure to enter the proper SQL query on each partition:
- Delete your Power Query data source, which should now no longer be in use by any partitions in your model.
And that’s it – all partitions on your model are now 100% legacy partitions.
Fine print
Update August 2020: There was a bug in the original script if you uncommented line 7, in that the loop would iterate through ALL tables of the model, including calculated tables and calculation group tables. Setting the DAX expression of a calculated table to “SELECT * FROM …” is probably not what you want, and if your model contained a calculation group table, the script would outright crash. This is because the Query property is not supported for partitions on a calculation group table. I added the check in line 5 to skip any calculated tables or calculation group tables in the model.
The Partitions.ConvertToLegacy(<data source>) method called by the script replaces each M partition on a table, with a Legacy partition that points to the specified (legacy) data source. It also assigns the M expression from the original M partition to the “Query” property of the newly created legacy partition, which is of course nonsense, as legacy data sources do not understand M queries. This is why you should go through each partition to update the query manually, or use the optional step 4, provided your source tables/views have the same names as the imported tables.
You could also consider modifying line 7 of the script to construct the legacy partition query in a different way, to save the manual hassle of going through each partition query. But this assumes that you have some consistency in the way tables and/or partitions have been named within your model:
foreach(var partition in table.Partitions) partition.Query = "SELECT * FROM [tabular].[vw_" + partition.Name + "]";This example uses the partition names to construct the query. So if you have a ResellerSalesFY2019 partition in your model, the query would become: SELECT * FROM [tabular].[vw_ResellerSalesFY2019].
Lastly, if you’re not afraid of doing some string manipulation using C#, you could probably “parse” the original M expression in order to extract the schema- and table name used within the query, but that is outside the scope of this post.
If anyone reads this and doesnt have the same namn on the partition as the source table, I use this script to use the power query of each partition to create the query for each table.
Just switch schema “tabular” to whatever your schema is.
var legacy = (Model.DataSources[“SQL/DWAccess”] as ProviderDataSource);
foreach(var table in Model.Tables)
{
if(table is CalculatedTable || table is CalculationGroupTable)
continue;
table.Partitions.ConvertToLegacy(legacy);
foreach(var partition in table.Partitions)
{
var currentQuery = partition.Query;
var schemaMatch = System.Text.RegularExpressions.Regex.Match(currentQuery, @”Schema=””(.*?)”””);
var itemMatch = System.Text.RegularExpressions.Regex.Match(currentQuery, @”Item=””(.*?)”””);
if (schemaMatch.Success && itemMatch.Success)
{
var schema = schemaMatch.Groups[1].Value;
var item = itemMatch.Groups[1].Value;
partition.Query = string.Format(“SELECT * FROM [{0}].[{1}]”, schema, item);
}
else
{
partition.Query = string.Format(“SELECT * FROM [tabular].[{0}]”, partition.Name);
}
}
}