Forum Discussion
Post PartisanAdvice needed on handling datasource changes 'just once' rather than in each individual report
Hi all,
I'm looking for some advice on an architectural setup so handle any datasource changes just once rather than having to handle that change in every single Power BI report. I'll explain my current predicament.
The datasource within my organisation is Dynamics 365 (D365). However, we are using Data Export Services (DES) to move that data from D365 to SQL Server (D365 >> DES >> SQL Server). Therefore SQL Server will be where the Power BI reporting consumes its source data.
If I have 101 Power BI reports and they all consume a field, such as, Orders[Order Number] but that same field is changed to Orders[Order Numbers] (notice the additional 's' on the field name). This would result in each of the 101 Power BI reports failing due a source field name change. This is a simple example.
What I would like to do is make it so I handle (change) the Order[Order Numbers] field name to be what originally was Order[Order Number]. This way none of the 101 Power BI reports will need amending as they are developed to expect Order[Order Number]. Is there a way to achieve this?
There must be many organisations that report on a system (datasource) that is constantly changing, like the example I provided, so how do these organisations handle these source data changes?
I've thought about using a Power BI (Power Query) Dataset as an intermediary layer between SQL Server and the 101 reports. So the 101 reports will connect the Power Query dataset to obtain its data. The problem with this approach are the following two items:
1) If I bring all the SQL Server datasource tables (say 50 tables) into the Power Query dataset then each of the 101 Power BI reports will have the entire 50 tables present in them. This will most probably lead to the dataset size being over 1GB (we don't have Premium Capacity or a Report Server - we can do if we need to).
2) Due to each of the 101 Power BI reports consuming the Power Query dataset for its source data, it means that each (101) individual Power BI report cannot have its own Power Query code. This is a showstopper as each Power BI report will NEED its own Power Query logic, and this logic may not be relevant to the other Power BI reports (hence not applying it in the intermediary Power Query dataset).
What is the best practice to handling datasource changes just the once?
Any advice will be very much appreciated, and is desperately needed. Thanks.
You might want to consider creating database views that serve as an intermediate layer. Each source table would have a corresponding view, allowing you to control column name changes in the view, without interrupting Power BI reports. Other advantages of this approach:
- The ability to exclude unnecessary columns.
- The ability to exclude unnecessary rows (for example, temporary deletions, unwanted data, data before a certain date).
- The ability to create a single-column key, if the table has a composite key. This makes it easy to create table relationships in Power BI.
- The ability to create easy-to-use column names.
- The ability to perform calculations in SQL Server.
D365 >> DES >> Tablas de SQL Server >> Vistas de SQL Server >> Power BI
1 Reply
- DataInsights
Super User
You might want to consider creating database views that serve as an intermediate layer. Each source table would have a corresponding view, allowing you to control column name changes in the view, without interrupting Power BI reports. Other advantages of this approach:
- The ability to exclude unnecessary columns.
- The ability to exclude unnecessary rows (for example, temporary deletions, unwanted data, data before a certain date).
- The ability to create a single-column key, if the table has a composite key. This makes it easy to create table relationships in Power BI.
- The ability to create easy-to-use column names.
- The ability to perform calculations in SQL Server.
D365 >> DES >> Tablas de SQL Server >> Vistas de SQL Server >> Power BI