visual-studiopowerbissasssas-tabular

Visual Studio tabular cube: an M partition uses a data function which results in access to a data source different from those defined in the model

I have a very simple tabular cube in Visual Studio. I created parameters to represent my data source (via the Power Query Editor). I then wrote a query to create a table. That query uses the data source parameters. When I try to process the table, I encounter the following error:

An M partition uses a data function which results in access to a data source different from those defined in the model

How can I resolve this?

For reference, my table query is:

let
    Source = Sql.Database(#"Server", #"Database"),
    #"some table" = Source{[Schema="some_schema",Item="some_table"]}[Data]
in
    #"some table"

From the following post, somebody said that a way to solve this is to declare the SQL database as a data source. However, if I do that, my table won't be able to use the parameters. And, when I deploy the tabular cube to the Power BI service, Power BI won't see the parameters, which isn't what I want. I need Power BI to see the parameters so that I can set Parameter rules in a deployment pipeline.

an-m-partition-uses-a-data-function-which-results-in-access-to-a-data-source-dif

Solution

  • I did a working example in my environment with 2 Analysis Server databases. Adjust this for you SQL Server environment.

    You will need to create a Data Source for each database. When you create the data model in Visual Studio, you have to create Data Sources first. You can't just load a Table/Partition from a database via Source = Sql.Database(). You have to reference a Data Source such as Source = #"SqlDatabase/server", where SqlDatabase/server is the Data Source name.

    (Note this is different than Power BI. In Power BI, there are no Data Source objects, only Expressions and Partitions.)

    So from the Tabular Model Explorer, right click Data Sources and pick New Data Source:
    enter image description here

    Then add a Data Source for each and every database. Here are mine:
    enter image description here

    Then right click one of the Data Sources and Import New Tables:
    enter image description here

    Connect to the database and table as you normally would, and then click the Transform Data button to open Power Query. You'll see that the Source step will reference the Data Source.
    enter image description here

    You want it to be conditional based on a Parameter, so select Query > Parameters > New Parameter:
    enter image description here

    I named mine Environment with a Type of Text, with a Suggested Values of a List of values of Test and Prod, and I set the Default and Current Values to Test:
    enter image description here

    Note that these are just text values. Go back to the Source step in the Table query and change it to use an if statement. Remember Power Query is case sensitive, and watch your use of quotes and # as shown:

    let
    Source = if #"Environment" = "Test" then #"AnalysisServices/myserver_d01" else #"AnalysisServices/myserver_p01",

    Now by changing the parameter, you can switch between databases.