how to create source parameters for a tabular cube in Visual Studio

[mr_wizard]

This post is about Visual Studio.  Since there isn't an option for that tool on this site, I am putting this post under the next best option.

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 link, 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.

https://stackoverflow.com/questions/53199044/an-m-partition-uses-a-data-function-which-results-in-ac...

[v-jialongy-msft]

Hi @mr_wizard 

The error you're encountering in Visual Studio while processing a tabular cube usually stems from a mismatch or security configuration issue between the data sources defined in your model and the actual data sources your queries are trying to access. 

To resolve this issue, you'll need to carefully manage how data sources and parameters are defined and used in your model, especially when deploying to the Power BI service. Here are a few steps and considerations that might help:

1. Define the Data Source Explicitly in the Model
First, ensure that your data source is explicitly defined within the model's data source settings. This is crucial for security and consistency, particularly when processing the model:In your Visual Studio project for the tabular model, go to the model's properties and ensure that the SQL Server and database are properly defined as a data source. You might need to adjust this to match exactly what your Power Query M formula uses.

2. Parameterize Your Data Source

Parameters in Visual Studio: When defining parameters in Visual Studio, ensure they are also correctly mapped and recognized in the tabular model's settings. Check that these parameters are accessible and correctly configured in the model's expressions or properties.

3. Adjusting Deployment Settings for Power BI

Deployment Parameters: When deploying to Power BI Service, ensure that the deployment settings allow for parameterization. This often involves setting up the parameters through the service’s interface and ensuring they're mapped correctly to your model’s queries.

Parameter Rules in Power BI: After deployment, use the Power BI Service to configure Parameter Rules in your deployment pipeline. This allows for different configurations in your development, test, and production environments without altering the underlying model each time.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mr_wizard]

Something doesn't seem right.  In Power BI Desktop, I don't need to explicitly create a data source for my parameters.  I can just write an M query using my parameters.  Are you saying that isn't allowed in Visual Studio?  Again, if I need to explicitly create a data source that corresponds to my parameters, that defeats the purpose, as I will now need to update both my parameter values and my data source every time that I want to toggle between sources in Visual Studio.