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

[Anonymous]

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 a report and publish it in which the datasource will change dynamically according to a filter/front end selection (for example: date filter).

So when I select a histioric date, it should connect to the historic data source and when i select a recent date, it should connect to my SQL database.
The connection will be a direct query, NOT IMPORT.
I wasnt that both the connections should not exist parallelly so that the performance can be improved.

So basically, I should have the option of changing the data souce from the frontend (i.e. Power BI service published report, And not .PBIX file).

[Anonymous]

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]

@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]

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