Not sure how to build my best source

[jpdejongjr]

Global company. Six databases on 3 servers, same table structure in every database. I want to combine them for a single data collection to build reports for myself and my colleagues.  I don't know if I need a dataflow, datamart, or what I need...

I took the long road in a desktop file to pull the same table from all six databases.  I then appended them into a single table.  That was successful, but I am wanting to create something bigger and better that can be referenced by many reports as the data source, so that those reports can all report the global facts, rather than the individual, regional ones.

What do you recommend?  I am at a beginner level in Power BI and the tutorials I find, talk way past my level of experience.

[ichavarria · Accepted solution]

Hi @jpdejongjr,

Based on your scenario, it seems like you want to create a centralized data repository that can serve as the single source of truth for all your global company data, which can be used to build reports that show a holistic view of your business.

In order to achieve this, you would need to first decide on a data integration strategy that will allow you to combine data from multiple sources (in your case, the six databases) into a single data repository. There are several options available, including dataflow and datamart.

A dataflow is a cloud-based ETL (extract, transform, load) tool that allows you to create data integration workflows that can pull data from multiple sources, transform it into a common format, and load it into a centralized data repository. It is designed to handle large volumes of data and is typically used for enterprise-level data integration.

A datamart, on the other hand, is a subset of a data warehouse that is designed to serve a specific business function or department. It is typically used to provide a smaller, more focused view of the data, and is often used for reporting purposes.

Given your beginner-level experience with Power BI, it may be best to start with a datamart, which is a simpler solution compared to a dataflow. To create a datamart, you would need to first design a data model that represents the data you want to store in the repository. This would involve identifying the key data entities in your business (e.g., customers, sales, products, etc.) and defining the relationships between them.

Once you have designed your data model, you can then use Power BI to create a data source that pulls data from all six databases and stores it in your datamart. You can then create reports in Power BI that use this data source as the data source, allowing you to create reports that show a holistic view of your business.

Let me know if you have any additional or follow-up questions!

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly