In Part 1 of Power BI Data Modeling and Design we covered at a high level the data types categorical, reference, process, and transactional. In Part 2 we will dive into the method of gathering the necessary elements of an organization, so it can be represented in a Power BI data model. If the project/product is still collecting the requirements from the business remember to keep the following recommendations in mind.
Requirements Discovery Tips
- Invite business analysts or knowledge workers to share their requirements within context.
- Background knowledge for the requirement should be identified and/or provided.
- Use new vocabulary in conversation to discover requirement meaning.
- Leverage pictures, charts, graphs, maps, etc. as the user presents it.
After the requirements have been gathered it is the Power BI data modelers job to begin development. Taking time before you dive into the data sources to fully understand the requirements will help prevent model redesigns later. When reading the requirements use these steps to identify components for the data model.
Identifying Data Model Components
- Who: This may be the trickiest to fully define from an enterprise perspective. A customer in your operations division may be rolled up as part of a client’s site in your marketing division. Be sure to understand the context of your business requirements. Keep in mind there might be requirements for a fully qualified customer like an address, or email that must be included.
- What: Typically relates to the product, or service the enterprise offers, but can also refer to operations internal process. Another example would be the marketing team tracking different types of promos they offer.
- When: This is either straight forward such as calendar date, or fiscal date, or both dates. The ‘When’ can become complicated if you need to look at working days, or better yet working days by country. Remember ‘When’ traditionally has a very strong hierarchy. Examples would be Second, Minute, Hour, Day, Week, Month, Quarter, Year.
- Where: Often refers to physical locations like distribution centers, customers, and shop locations. Can also reference online locations like a website URL, or an email list of customer used to communicate an offer.
- Why: The ‘Why’ should be the type of interaction occurring between the enterprise and the ‘Who’. Examples would be a Sales Order, or a customer returning a product. Social media trends can help establish the context of why sales are increasing, or if returns start to increase.
- Facts: The facts or measures are typically entities like sales amount or an item inventory count. The facts are always within the context of the Who, What, When, Where, and Why.
Ross McNeely Blog 