Ultimately each business process will be represented by a fact table with a star schema of many-to-one relationships to dimensions. In a discovery or requirements gathering process it can be difficult to focus on a single business process in isolation as users regularly analyze multiple business processes simultaneously or need to. Nonetheless, it's essential that the dataset being designed reflects low level business activities (for example, receiving an online sales order) rather than a consolidation or integration of distinct business processes such as a table with both online and reseller sales data:
- Confirm that the answer provided to the first question of the project template regarding data sources is accurate:
- In this project, the required business processes are Internet Sales, Reseller Sales, Annual Sales and Margin Plan
- Each of the three business processes corresponds to a fact table to be included in the Power BI dataset
- Obtain a high-level understanding of the top business questions each business process will answer:
- For example, "What are total sales relative to the Annual Sales Plan and relative to last year?"
- In this project, Internet Sales and Reseller Sales will be combined into overall corporate sales and margin KPIs
- Optionally, reference the data warehouse bus matrix of business processes and their related dimensions:
- For example, discuss the integration of inventory data and the insights this integration may provide
- In many projects, a choice or compromise has to be made given the limited availability of certain business processes and the costs or timelines associated with preparing this data for production use:
- Additionally, business processes (fact tables) are the top drivers of the storage and processing costs of the dataset and thus should only be included if necessary.
A common anti-pattern to avoid in Power BI projects is the development of datasets for specific projects or teams rather than business processes. For example, one dataset would be developed exclusively for the marketing team and another dataset would be created for the sales organization. Assuming both teams require access to the same sales data, this approach naturally leads to a waste of resources, as the same sales data would be queried and refreshed twice and both datasets would consume storage resources in the Power BI service. Additionally, this isolated approach leads to manageability and version control issues, as the datasets may contain variations in transformation or metric logic. Therefore, although the analytical needs of specific business users or teams are indeed the priority of BI projects, it's important to plan for sustainable solutions that can ultimately be shared across teams.