Tabular Editor tricks – convert to legacy

Key takeaways

• Legacy (Provider) data sources still have a place for SSAS and AAS: For models on a relational warehouse deployed to SQL Server or Azure Analysis Services, legacy sources avoid the init overhead and credential-drop issues of Power Query partitions.
• A short C# script converts every M partition to Legacy: Point a new legacy data source at your database, run Partitions.ConvertToLegacy() across the model, and skip calculated and calculation group tables.
• You must fix the partition queries afterwards: ConvertToLegacy copies the M expression into the Query property, which legacy sources can't read, so set a proper SQL query on each partition, optionally derived from table or partition names.
• This is an SSAS/AAS technique, not a Power BI one: Legacy data sources are not recommended for Power BI or Fabric semantic models.

This summary is produced by the author, and not by AI.

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:

1. Create a Legacy data source on your model and point it to your relational database. Give it a name, for example, “SQLDW”.
2. 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;
}

3. 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.

4. (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.

5. Run the script

6. 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:

7. 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 isn't 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.

For further reading

• Importing tables in Tabular Editor (Tabular Editor). Documentation for the Import Table wizard in Tabular Editor, which is particularly well-suited to models using legacy data sources backed by a relational warehouse.
• Tabular Editor Tricks – Object State (Tabular Editor). Describes the Dynamic LINQ filter technique for identifying unprocessed objects across a tabular model, a useful follow-up step when verifying partition state after a conversion.
• You’re Deploying it Wrong! – AS Edition (Part 4) (Tabular Editor). The fourth article in the DevOps series, covering schema validation against the relational source and BPA checks run as part of the build pipeline, both of which are most relevant for models on legacy data sources.
• Tabular Editor CLI documentation (Tabular Editor). Documents the current cross-platform CLI for validation and deployment automation, the modern replacement for older command-line workflows around legacy-partition models.

In conclusion

Converting a model's partitions from Power Query to Legacy is mostly a one-time scripting job: create the legacy data source, run ConvertToLegacy across the model, then replace the copied M expressions with real SQL queries. Done carefully, you end up with a model whose partitions are 100% legacy, with steadier refresh behaviour and no dropped credentials on deployment. Just remember this advice is scoped to SSAS and Azure Analysis Services models built on a relational warehouse, not to Power BI or Fabric.