Power BI Data Modeling and Design Intro (part 2)

by Ross McNeely

Practice Manager, Enterprise Data Management

In Part 1 of this article, we took a broad look at data types: categorical, reference, process, and transactional. In Part 2, we will be diving into the method of gathering the necessary data elements of an organization so that it can be represented in a Power BI data model. If your project/product is still collecting requirements from the organization, keep the following recommendations in mind.

Requirements Discovery Tips
1.  Invite business analysts or knowledge workers to share their requirements within context.
2.  Background knowledge for the requirement should be identified and/or provided.
3.  Use new vocabulary in conversation to discover requirement meaning.
4.  Leverage pictures, charts, graphs, maps, etc. as the user presents it.

After the requirements have been gathered, it is the Power BI data modeler’s 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.

Power Bi Data Modeling And Design Part 2 Pic

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 considered part of a client site in your marketing division. Be sure to understand the context of your business requirements, and keep in mind there might be requirements for a fully qualified customer, like an address or email, that must be included.
What: This typically relates to the product or service the enterprise offers, but can also refer to an internal operation process. One example would be the marketing team tracking different types of promotions they offer.
When: This is straight forward; “When” can mean a calendar date, fiscal date, or both. 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, such as the URL or email list where a promotional offer was published.
Why: The “Why” is 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.