Update Data Source for Multiple Datasets (Same source, just updating the server) Whats the best way?

[rodneyc8063_1]

Just curious

Lets say we have 50 beautiful reports that our users love using. 

All 50 reports use our companies data warehouse - So its all the same source, but with varying measures/calculated columns and different tables being used in each report.

Lets say our DBA's tell us that they are going to upgrade the data warehouse server and change its IP to a whole new machine.

Is there a way to update these 50 reports to point to the new data warehouse server?

I know I can update the connection string details in the .pbix file, and reupload to the service. I also know that we can paramertirize the connection details, so we can update them within the service. But both of these options would require someone physically going to each dataset to some degree still to update the connection string.

With other tools, the connection details are a bit more centralized (for example OBIEE has a central TNSNAMES.ora file)

Im getting the sneaking feeling that this is the only option though, to go to each report and update it manually.

[Greg_Deckler]

@rodneyc8063_1 You should probably use a Dataflow to centralize your connection to the server. With a dataflow, you create it once and can use it many places. Then if something changes, you just have to update a single location.

Either that or use the Golden Dataset Usage Model. https://community.powerbi.com/t5/Community-Blog/Power-BI-Usage-Models-in-Pictures/ba-p/1342820

[rodneyc8063_1]

Hi @Greg_Deckler 

You just blew my mind with these ideas/suggestions

Im a bit new so pardon the silly question but I am having a hard time understanding the difference between creating a dataflow vs a dataset though?

If I understand correctly, a dataflow pulls in tables/queries and allows me to transform via power query. So for example, if I have a dataflow I could pull in 20 tables and shape them up. Then an end user would connect to this dataflow, and they can then pick and choose from amongst these 20 tables lets say only 5 of the tables - And create his own dataset and therefore report - Did I get that right?

When someone connects to a golden dataset or a centralized/promoted dataset - Can they not also prune tables out, add measures and do further modelling?

Or when someone connects to a shared dataset, then its "what you see is what you get" and you cant do any further modelling?

[Greg_Deckler · Accepted solution]

@rodneyc8063_1 - Generally think of a dataflow as equivalent to a Query in Power Query. So typically you are accessing a single data source and performing transformations on it. You can then connect to this dataflow from Power BI Desktop and use it as if you had built the query in the Power BI Desktop file. So, if I understand correctly, in your case you would create 20 dataflows. Users could mix and match these dataflows to build datasets in their own Power BI reports in Desktop.

For a Golden Dataset approach, you can create report level measures with a Live connection.

https://radacad.com/live-connection-when-power-bi-comes-hybrid

They cannot prune out tables or hide tables or hide columns, etc. You also cannot create new calculated columns so your options are limited. 

[rodneyc8063_1]

Hi @Greg_Deckler 

Ahhhh

Hm ok one last question regarding the "Golden Dataset" approach - I did some reading up, and it sounds like you said, tables can not be removed.

So is it safe to assume that also for the relationships are also unable to be edited?

Dataflows does sound like its the more flexible option in this case, as that would allow an end user to pick and choose, and set up relationships on their own if needed.

[Greg_Deckler]

@rodneyc8063_1 Correct, you cannot edit the relationships