top of page
  • MK

Convert Data Sources for Premium Migration

Updated: Jul 27, 2021

When migrating a tabular model from Analysis Services or Azure Analysis Services to Power BI Premium, you may run into an issue related to the data sources you are using and the table partitions referencing those data sources. To understand this, let's cover some basics.


There are 2 types of data sources: 'provider' data sources (sometimes called 'legacy') and 'structured' data sources.


Provider Data Sources


As their name suggests, provider data sources require the user to enter a 'provider' (i.e. Microsoft SQL Server). The table partitions which reference a provider data source generally use SQL (or a form of it - depending on the exact data source). Models built in Visual Studio which are of compatibility level 1200 will default to use provider data sources. Additionally, models built in Tabular Editor will also default to use provider data sources.


Structured Data Sources


Structured data sources are essentially Power Query connecters which are designed to use the M language. The table partitions which reference a structured data source generally use M. Models built in Visual Studio which are of compatibility level 1400 or higher will default to use structured data sources and use the M language in partitions which reference these data sources.


Power BI Desktop


When you create a table in Power BI Desktop, it deviates from the outline above. It actually does not create a data source. It simply stores the data source information within the table's partition as M-code.


Recap and Potential Issue


In general, provider data sources align with partitions using SQL while structured data sources align with partitions using M. However, Analysis Services and Azure Analysis Services support partitions which use SQL and reference a structured data source. Note that Power BI (Premium) does not support this. In Power BI, a partition that uses SQL must reference a provider data source. Due to this difference between Analysis Services and Power BI, it is essential to make sure your data sources and partitions are aligned before migrating to Power BI Premium. Below is an example of the error message you will get if you try to deploy such a model to Power BI Premium.



Solution


As the error message indicates, there are two solutions to this problem. First, you can update the 'problematic' partitions to use M. The other option is to convert the structured data source into a provider data source. I will give you a hint - the second option is much simpler. Additionally, if your partitions use SQL, I generally recommend using a provider data source anyways.


Well, as has become common with my blog, I have written a C# script which automates this process for you. Now, your migration to Power BI Premium becomes that much simpler.


Download the script here.


Running this script in the Advanced Scripting window within Tabular Editor will do the following:

  1. Loop through all structured data sources

  2. Create a provider data source with the same connection properties as the structured data source.

  3. Update any partitions which reference the structured data source to reference the new provider data source.

  4. Delete the structured data source.

  5. Update the name of the provider data source to match the name of the original structured data source.

Note: If your data source requires authentication credentials, you may have to enter the password into the connection string properties of the data source before you deploy.


Note: The above script works in both Tabular Editor 2 and 3.


And just like that - a potential blocker has been removed and you should be on your way to successfully migrating to Power BI Premium!


Integration with Best Practice Analyzer


This potential issue has been encapsulated in a newly released rule in Version 1.2 of the Best Practice Rules for Tabular Editor's Best Practice Analyzer.


The name of the new rule is: [Error Prevention] Avoid structured data sources with provider partitions


Make sure to download the latest rules here!


Conclusion


Before migrating to Power BI Premium, it is important to align on your data source and partition strategy. If you are using SQL-type sources, I highly recommend using provider data sources. It will be more natural and will easily transfer to Power BI Premium. Many people believe that Power BI Premium requires structured data sources and that the partitions must be in 'M'. However, this is not the case. Provider data sources work just fine in Premium and can be managed quite easily when working with Tabular Editor (version 2 or 3). For more information on data sources in Power BI and Analysis Services, see this link.

6 Comments


Richard Hains
Richard Hains
Sep 30, 2023

Hi MK, we were able to migrate our SSAS tabular models to premium per user following your article. However, when the data source is a tabular model in premium I tried to recreate the connection sting using system.data.msolap8 then deploy the model to premium but when setting the gateway the data source is not recognized. Any idea what I am doing wrong?

if you read this and can send me an email to rhains@capitalpower.com I will appreciate Richard

Like

Alberto Gentilcore
Alberto Gentilcore
Mar 16, 2023

Hi, I was reading your post and I couldn't understand it the need of change.


For example, in the Microsoft documentation, they say: "With the XMLA endpoint, Premium also supports structured and provider data sources"


So, if they supports Structured and Provider, why do we need changes the data source?

Like

UPDATE: if I try to manually do what the C# script does, i.e. create a 'provider' type version of the 'structured' type data source that I have now (an ODBC connection to Snowflake), I basically get the same error: "The action failed with the following error: Object reference not set to an instance of an object."


But I did manage to create it and convert all tables to the provider version of the data source and deploy to Power BI Premium with the following instructions: https://tabulareditor.github.io/2019/07/03/TabularEditorTricks2.html


But unfortunately, my initial problem ('Data source credentials' in Dataset settings staying greyed out, due to which I cannot refresh the migrated Dataset), is not solved. I'm starting to think that it's maybe an issue…

Like

I believe this to be the solution to my problem, but I'm getting the following error when trying to run the script in Tabular Editor. Could you please help out here? Thanks!

"Error on line 27


NullReferenceException

Object reference not set to an instance of an object."

Like
Replying to

This script and solution in this post is only for models with structured data sources and where the partitions use regular SQL (and not M-code). Given that the tabular editor post you mentioned worked for you, your partitions use M-code (which means that the solution in this post will not be relevant to you).

Like

Alex Powers
Alex Powers
Jul 26, 2021

Great write-up! Adding this to my custom scripts ASAP.

Like
bottom of page