CODEWITHSUNDEEP
powerbi
variable
variable

Reputation: 9644

What is the approach to merge data from multiple databases (same schema) using Power BI?

I have 3 OLTP databases, all using the same database schema. Each db represents one department.

I am exploring Power BI as a solution for reporting at the company level, so all departments combined.

What is the approach to combine data from multiple dbs into a data warehouse? For example - do I need SSIS to combine the 3 dbs into 1 data warehouse?

Another option could be to have 1 shared dataset per db, and then the final report can connect and combine multiple live datasets? Or is there another way with Power BI like combining multiple live datasets?

Any reference link on how if someone has done this?

Upvotes: 0

Views: 1142

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88851

Or is there another way with Power BI

Yes. Simply create a single import model and load data from all three databases in it. So for each table in your Power BI model you would have three Power Queries set to not load into the model, and you would append them in a query that is used to load your model. See eg: https://learn.microsoft.com/en-us/power-query/append-queries

Upvotes: 1

Jon
Jon

Reputation: 4957

Best practice would be to:

  1. Extract the data into a single database (DWH or reporting schema)
  2. Build the necessary items there for your data model, be it reporting schema, or star/snowflake schemas
  3. Connect Power BI to that schema.

Combining datasets is going to be tricky, you may have the same measures in each of the datasets. Combining in the database, with any added columns to indicate the department is the best option in terms of supporting updating/adding/removing items. For example, if the schema changes in the DB's you do it in one place, not three datasets. The toolset in DB/SSIS will be better suited to the heavy lifting of the data to a location.

You would use SSIS to extract the data if on-prem data, Azure Data Factory for Azure DB's. Extract to a staging schema, convert/transform the data into its final from, with a new schema to define what it is, facts/dimensions other schema names such as reporting can be used, depending on the data model you wish to build. Most of this is covered by the standard ETL pattern of OLTP to an OLAP database.

Upvotes: 0

Related Questions