PowerBI Reports architecture
Background information -
Above diagrams shows PowerBI reports architecture.
SQL DB is the FinancialDataMart database which gets data imported from many tables in other databases on the same server 10.0.0.4, such as RefData, D365, TDS etc.
Orange lines are Reports refresh requests. Green boxes are data sets and circles are Reports.
Current limitation is that there is just one gateway handling too many reports refresh requests. There are two main issues either Gateway times out or SQL times out.
Proposed solution - At present there is one to one mapping between datasets and reports. This needs to be migrated to FDM (Financial Data Mart) so that one dataset can feed data to multiple reports thus reducing refresh requests significantly. This is shown as yellow dataset box in above diagram.
Other options are not viable -
1. Stagger reports refresh so that we don't run too many reports at a time. But this is not possible as Operations team want most of their critical reports to be available by 9am UK time.
2. Create a new VM and move FinancialDataMart DB on it so that it does not get impacted by traffic and load on current 10.0.0.4 server. This should be done anyway in long term even after FDM solution goes live.
Gateway user prodpbigateways@accelins.com is used to connect to gateway installed on WinApp01 server.
User account REP_PowerBI is used to access SQL FinancialDataMart database on server 10.0.0.4 via the gateway