Four-step dimensional design process

1. Choose the business process
   
   • The number and nature of processes to include depends on the scale of the sources and scope of the project
   • In this example, the chosen processes are Internet Sales, Reseller Sales, and General Ledger
2. Declare the granularity
   • For each business process (or fact) to be modeled from step 1, define the meaning of each row:
     • These should be clear, concise business definitions--each fact table should only contain one grain
     • Consider scalability limitations with Power BI Desktop and balance the needs between detail and history (for example, greater history but lower granularity)
     • Example: One Row per Sales Order Line, One Row per GL Account Balance per fiscal period

1. Identify the dimensions
   
   • These entities should have a natural relationship with the business process or event at the given granularity
   • Compare the dimension with any existing dimensions and hierarchies in the organization (for example, Store)
     • If so, determine if there's a conflict or if additional columns are required

1. Identify the facts
   • These should align with the business processes being modeled:
     • For example, the sum of a quantity or a unique count of a dimension
   • Document the business and technical definition of the primary facts and compare this with any existing reports or metadata repository (for example, Net Sales = Extended Amount - Discounts).
   • Given steps 1-3, you should be able to walk through top business questions and check whether the planned data model will support it. Example: "What was the variance between Sales and Plan for last month in Bikes?"
     • Any clear gaps require modifying the earlier steps, removing the question from the scope of the data model, or a plan to address the issue with additional logic in the model (M or DAX).