Combine Different Power BI Data Sets to one Consolidate Report

[ziggyz]

My company has 15 different branches located in different sites/regions. Each branch has their own back office system / database (On-premise) and would have Power BI desktop installed to connect to the system/database for reporting.The resulting report contains the business of the branch.

There is a need to centralize the reporting in HQ to consolidate the report. Do you know how this can be accomplished? Basically we have 15 branches with 15 identical back office systems installed on premise (15  identical databases) and we want to have a consolidated report of whats happening in all branches.

The two possible solutions I can think of right now are:

1. Each site would transform the data they need into excel and each site would then send the file to HQ. Power BI would then append the data into one for consolidated reporting.

2. An Azure Datawarehouse is setup wherein the reporting data from each site would be uploaded to the cloud. Power BI would then get its data from the centralized DB.

Do you know of any other solution?

[amitchandak]

@ziggyz , I would prefer the second approach to having an Azure SQL warehouse for consolidation. I know power bi can do that, but would prefer a consolidation on a database

Bring in data from all sources with source as one of the columns, append and create fact and dimensions