Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Change data source of report (eg: switch between SQL server and Teradata/HDinsight) in PBI service

I have a requirement in which the historic data (large volume of data) will be stored in a different datasource and the current data (smaller volume) will be stored in a SQL database. I was to make ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous ,

If these datasource has same data structure, you can refer to following blog to parameterize your connection string to achieve dynamic change between different data sources.

Using the Power BI Service Parameters to change connection strings (To possibly change between Dev

You need to add if statement on source step to use parameter to change different connectors:

let
    Source =if SourceType="SQL" then Sql.Database(Server,Database) else Teradata.Database(Server)
in
    Source

Notice: Bold part is query parameters.

 

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous Thank you for your reply.
    First to add to the context, YES, the schema of the data will remain the same.
    But I think this is not a potential solution for my use-case as:
    1. I will have to navigate to the dataset settings page to change parameters (Whereas I want the data source to dynamically change with the help of a selection in the report itself)
    2. The connection needs to change from SQL to some other data source. So, only parameterizing the database and server link will not suffice

     

    Any further help is appreciated. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      HI Anonymous ,

      #1, In fact, report selection and query tables are stored on different data levels. Your can't use query parameter to get report selections and push it to query table side.
      Data level: database -> query table[queries, query parameters] -> data model[data model tables, calculated column/table, RLS] -> report view[visual, slicer, filter, measure] 

      #2, You can consider to write custom function with different type of data connectors and use if statement and query parameters to achieve dynamic switch data connectors.

      Regards,

      Xiaoxin Sheng