Architecture between PBI and database

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.