Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Set up schema

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?

Need Help

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-juanli-msft
        Community Support
        7 years ago

        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