Set up schema

[Anonymous]

I am using Power BI embeded with Direct Query mode. My source is a MSSQL database with multiple schemas. I have the same set of tables in multiple schemas (it is our reasization of multy tenancy). For example, I have schema1.Invoice and schema2.Invoice tables. 

I have tried to create Scema parameter and use it like this:

Source = Sql.Database(SqlServerInstance, Database, [Query="select * from ["&schema&"].[Invoice]"])

It works on Desktop, but when I publish it on the Server, the Schema parameter doesn't publishes.

How can I pass the schema in a query?

[v-juanli-msft]

Hi @Anonymous

I make a test using parameter in direct query mode, when publish to service, i can't find parameter as you did.

Since i doesn't find any document saying that parameter is not supported for direct query in power bi service,

I will confirm if it is by default or an issue and will update as soon as possible.

Best Regards

Maggie

[Anonymous]

Hi @v-juanli-msft
I am sorry for sorry for the annoyance but we actually are blocked of using Power BI cause of this problem. Multiple schemas is the main idea in our project and we definitely need the ability to send schema as a parameter. Please, write what approach can we use to implement this?

[v-juanli-msft]

Hi @Anonymous

Sorry for any inconvenience.

Parameters are not supported with DQ and LiveConnect today on the service.

For any workaround, you could use Power BI API.

But there are some limitations.

For example:

Updates the datasources of the specified dataset from the specified workspace.

• The original datasource and the new datasource must have the exact same schema.
• For cached models, the dataset must be refreshed to get the data from the new datasources. Wait 30 minutes for the update datasources operation to complete before refreshing. 
• Changing datasource type is not supported.
• Datasources that contain parameters on the connection string are not supported

More details:

Updates the datasources of the specified dataset from "My Workspace".

Updates the datasources of the specified dataset from the specified workspace.

Best regards

Maggie

[Anonymous]

Is there any workaround that allows to create Datasource in Direct Query mode dynamically with setting the SQL statement?

[v-juanli-msft]

Hi @Anonymous

I'm so sorry that i am unable to help you efficiently.

If you have any problem, you could open a ticket to get more quickly help.

https://powerbi.microsoft.com/en-us/support/

Best Regards

Maggie

[v-juanli-msft]

Hi @Anonymous

Dynamically changing data source of published report is not available now. Please vote on https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/8786290-ability-to-change-the-dat....

Currently the only way to change the connection string from Power BI Service is using the API Set All Connections,

However it only works for the dataset in DirectQuery mode (not work for SSAS live connection).

Best Regards

Maggie