Forum Discussion
Architecture between PBI and database
Hello everyone,
I have a question about architecture between Power BI and relational database. What is the best practice? I can imagine a few options, a) one or more views per report, b) one datamart used in more reports.
Ad a) If there is a change in source, I need to repair n number of views. Ad b) If there is a change in source, I have to repair only one view, but if some new report need more new logic (group by definition etc.), previous reports could be broken, even table joins are still in reports.
I ask because I am worry about database changes and maintenance needed. If you would write some recommendations for the beginning, I would be happy. Or if you use some kind of middle layer?
Thanks in advance
Ondrej
Hi meny-534,
You're right, both options have pros and cons. Here's a breakdown to help you decide along:Option A: One or More Views per Report
- Pros:
- Flexibility: Each view can be tailored to the specific needs of a report.
- Lower Impact on Source: Changes to the source database have minimal impact on existing reports.
- Cons:
- Maintenance: Maintaining multiple views can be time-consuming, especially if the source data changes frequently.
- Duplication of Logic: Similar logic might be implemented in multiple views, leading to inconsistencies.
Option B: One Datamart Used in More Reports
- Pros:
- Centralized Logic: Transformations and calculations are defined in one place, simplifying maintenance.
- Reuse: Existing logic can be reused across multiple reports, improving efficiency.
- Cons:
- Rigidity: New report requirements might not be easily accommodated by the existing datamart schema.
- Impact on Existing Reports: Changes to the datamart can potentially break existing reports.
Recommended approach: Middle Layer with a Balanced Approach
- Create a central data warehouse or datamart to store pre-processed and transformed data. This layer serves as a single source of truth for reporting.
- Use a star schema for the data warehouse/datamart. This optimized schema facilitates efficient querying and analysis in Power BI.
- Create dimension tables to store descriptive attributes used for filtering and slicing data in reports.
- Create fact tables to store transactional data with measures for analysis.
If you find this insightful, please provide a Kudo and accept this as a solution.- Pros:
3 Replies
Hi meny-534,
You're right, both options have pros and cons. Here's a breakdown to help you decide along:Option A: One or More Views per Report
- Pros:
- Flexibility: Each view can be tailored to the specific needs of a report.
- Lower Impact on Source: Changes to the source database have minimal impact on existing reports.
- Cons:
- Maintenance: Maintaining multiple views can be time-consuming, especially if the source data changes frequently.
- Duplication of Logic: Similar logic might be implemented in multiple views, leading to inconsistencies.
Option B: One Datamart Used in More Reports
- Pros:
- Centralized Logic: Transformations and calculations are defined in one place, simplifying maintenance.
- Reuse: Existing logic can be reused across multiple reports, improving efficiency.
- Cons:
- Rigidity: New report requirements might not be easily accommodated by the existing datamart schema.
- Impact on Existing Reports: Changes to the datamart can potentially break existing reports.
Recommended approach: Middle Layer with a Balanced Approach
- Create a central data warehouse or datamart to store pre-processed and transformed data. This layer serves as a single source of truth for reporting.
- Use a star schema for the data warehouse/datamart. This optimized schema facilitates efficient querying and analysis in Power BI.
- Create dimension tables to store descriptive attributes used for filtering and slicing data in reports.
- Create fact tables to store transactional data with measures for analysis.
If you find this insightful, please provide a Kudo and accept this as a solution.Hello Shivu,
thank you very much, could you be little more specific to the recommended approach,
- datamart should by placed in source database still?
- joins should by set up at Power BI semantic layer?
- if I evaluate a datamart as correct for multiple reports in an area, how do I query it? query each table like select * from table?
Thank you in advance,
Ondrej
- Pros:
Hi meny-534,
Absolutely, the answer to your questions regarding the placement and querying of a datamart for your Power BI architecture is as:
Datamart Placement:
- Separate Database: It's generally recommended to have the datamart reside in a separate database from the source relational database.
Joins in Power BI:
- Power BI Semantic Layer: Yes, data joins should be defined within the Power BI semantic layer. This layer allows you to model the relationships between tables from the datamart, regardless of their physical location in the database.
Querying the Datamart:
- *Avoid Select : Avoid using "SELECT * from table" when querying the datamart within Power BI. This retrieves all columns, which might be inefficient and can lead to performance issues.
- Instead, explicitly specify the columns you need for your report in your Power BI queries. This improves performance and data security by limiting the amount of data transferred.
- Utilize the relationships defined in the Power BI semantic layer to navigate between tables in the datamart. Create calculated measures within Power BI to perform aggregations and calculations specific to your reporting needs.
If you find this insightful, please provide a Kudo and accept this as a solution.
