With the data warehouse bus matrix as a guide, the business intelligence team can work with representatives from the relevant business teams and project sponsors to complete the following four-step dataset design process:

1. Select the business process.
2. Declare the grain.
3. Identify the dimensions.
4. Define the facts.

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.

All rows of a fact table should represent the individual business process from step 1 at a certain level of detail or grain such as the header level or line level of a purchase order. Therefore, each row should have the same meaning and thus contain values for the same key columns to dimensions and the same numeric columns.

The grain of fact tables ultimately governs the level of detail available for analytical queries as well as the amount of data to be accessed:

• Determine what each row of the different business processes will represent:
  • For example, each row of the Internet Sales fact table represents the line of a sales order from a customer
  • The rows of the Sales and Margin Plan, however, are aggregated to the level of a Calendar Month, Products Subcategory, and Sales Territory Region

• Review and discuss the implications of the chosen grain in terms of dimensionality and scale:
  • Higher granularities provide greater levels of dimensionality and thus detail but result in much larger fact tables
  • If a high grain or the maximum grain is chosen, determine the row counts per year and the storage size of this table once loaded into Power BI datasets
  • If a lower grain is chosen, ensure that project stakeholders understand the loss of dimensionalities, such as the inability to filter for specific products or customers

The dimensions to be related to the fact table are a natural byproduct of the grain chosen in step 2 and thus largely impact the decision in step 2. A single sample row from the fact table should clearly indicate the business entities (dimensions) associated with the given process such as the customer who purchased an individual product on a certain date and at a certain time via a specific promotion. Fact tables representing a lower grain will have fewer dimensions. For example, a fact table representing the header level of a purchase order may identify the vendor but not the individual products purchased from the vendor:

• Identify and communicate the dimensions that can be used to filter (aka slice and dice) each business process:
  
  • The foreign key columns based on the grain chosen in the previous step reference dimension tables.
  • Review a sample of all critical dimension tables, such as Product or Customer, and ensure these tables contain the columns and values necessary or expected.
• Communicate which dimensions can be used to filter multiple business processes simultaneously:
  • In this project, the Product, Sales Territory, and Date dimensions can be used to filter all three fact tables.
  • The data warehouse bus matrix referenced earlier can be helpful for this step
• Look for any gap between the existing dimension tables and business questions or related reports:
  • For example, existing IT-supported reports may contain embedded logic that creates columns via SQL which are not stored in the data warehouse
• Strive to maintain version control for dimension tables and the columns (attributes) within dimension tables:
  • It may be necessary for project stakeholders to adapt or migrate from legacy reports or an internally maintained source to the Corporate BI source

A significant challenge to the identity of the dimensions step can be a lack of Master Data Management (MDM) and alternative versions. For example, the sales organization may maintain their own dimension tables in Excel or Microsoft Access and their naming conventions and hierarchy structures may represent a conflict or gap with the existing data warehouse. Additionally, many corporate applications may store their own versions of common dimensions, such as products and customers. These issues should be understood and, despite pressure to deliver BI value quickly or according to a specific business team's preferred version, the long-term value of a single definition for an entire organization as expressed via the bus matrix should not be sacrificed.

The facts represent the numeric columns to be included in the fact table. While the dimension columns from step 3 will be used for relationships to dimension tables, the fact columns will be used in measures containing aggregation logic such as the sum of a quantity column and the average of a price column:

• Define the business logic for each fact that will be represented by measures in the dataset:
  • For example, gross sales is equal to the extended amount on a sales order, and net sales is equal to gross sales minus discounts
• Any existing documentation or relevant technical metadata should be reviewed and validated
• Similar to the dimensions, any conflicts between existing definitions should be addressed so that a single definition for a core set of metrics is understood and approved
• Additionally, a baseline or target source should be identified to validate the accuracy of the metrics to be created.
  • For example, several months following the project, it should be possible to compare the results of DAX measures from the Power BI dataset to an SSRS report or a SQL query
  • If no variance exists between the two sources, the DAX measures are valid and thus any doubt or reported discrepancy is due to some other factor