Forum Discussion

cjgonzalez's avatar
cjgonzalez
Regular Visitor
8 years ago
Solved

Change Data Source but Keep Models and Visualizations

Hello,

 

I am using DirectQuery to get data from a data warehouse that we built that we are constantly making updates to. We currently have a single data warehouse that we are connected to, made models off of, and created visualizations for in Power BI.

 

However, we have multiple data warehouses (with the same data) where we test in some and make changes and then copy it over to another (for example: a production data warehouse, a testing data warehouse, a quality data warehouse, etc.)

 

My question is, because it is the same data and tables (generally just added tables or fields are the only changes), is it possible to change the data source in Power BI but keep the same data models and visualizations that we created for the original data warehouse source that we used? Or is it not possible because it is a completely different data source?

 

Currently when I try I get an error that you cannot use more than one source for DirectQuery, I am wondering if there is a workaround for this.

 

Thanks!

CJ

Need Help
  • mow700's avatar
    mow700
    8 years ago

    I like this approach for automation, thanks for suggesting the approach.  For manual updates I just use the Change Source dialog in Data Source Settings:

     

9 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    8 years ago

    Hi CJ,

    best practice for this scenario is to use a parameter for your connection-string to the database (or at least for the database name) that you use/reference in all your queries.

    That way you only have to change the parameter once and all your data that uses/references this parameter will be switched to the new datasource at once.

    • vjnvinod's avatar
      vjnvinod
      Impactful Individual
      7 years ago

      ImkeF

       

      how would this work with Excel Pivot data. i am not able to find a solution where Power BI connects to an excel (Pivotted excel) and can extract the data behind that pivot.

       

      any suggestion on this would be highly appreciable

      • ImkeF's avatar
        ImkeF
        Community Champion
        7 years ago

        If you want to extract the data behind a pivot-table in Excel, you would have to adress that source directly instead of the pivot. The Pivot table in Excel doesn't carry any metadata about it that could be retrieved by Power BI.

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    8 years ago

    Hi cjgonzalez,

     

    You can try to open a new Power BI desktop window, then get data from production data warehouse in DirectQuery mode, go to Query Editor -> Advanced Editor, copy the Power Query here. Then go to the report you want to change data source, paste this Power Query in Advanced Editor. 

     

     

    Best Regards,
    Qiuyun Yu 

    • mow700's avatar
      mow700
      Resolver I
      8 years ago

      I like this approach for automation, thanks for suggesting the approach.  For manual updates I just use the Change Source dialog in Data Source Settings:

       

  • amurias's avatar
    amurias
    Frequent Visitor
    7 years ago

    how to do it, when you have reports developed direcly in powerBI "online", and you change the server conection.

    Imagine direct query using enterprise gateway to a SSAS multidimensional data base.

    There is no way?

    Trying to download de PBIX from the portal is neither available (option appear grayed out)

    Thks!