3C. Design a semantic model in Power BI Flashcards
What should the overarching goal be when designing a semantic model?
Providing set rules for what makes a good semantic model is difficult because all data is different, and the usage of that data varies. Generally, a smaller semantic model is better because it performs faster and will be simpler to use. However, defining what a smaller semantic model entails is equally as problematic because it’s a heuristic and subjective concept.
To summarize, you should aim for simplicity when designing your semantic models.
What are primary and foreign keys, and what role(s) do they play in semantic models?
Relationships are defined between tables through primary and foreign keys. Primary keys are column(s) that identify each unique, non-null data row. For instance, if you have a Customers table, you could have an index that identifies each unique customer. The first row has an ID of 1, the second row an ID of 2, and so on. Each row is assigned a unique value, which can be referred to by this simple value: the primary key. This process becomes important when you are referencing rows in a different table, which is what foreign keys do. Relationships between tables are formed when you have primary and foreign keys in common between different tables.
What is a star schema?
In a star schema, each table within your semantic model is defined as a dimension or a fact table, as shown in the following visual. Fact at the center, dimensions as the points of the star.
What are fact tables?
They contain observational or event data values: sales orders, product counts, prices, transactional dates and times, and quantities. Fact tables can contain several repeated values. For example, one product can appear multiple times in multiple rows, for different customers on different dates. These values can be aggregated to create visuals. For instance, a visual of the total sales orders is an aggregation of all sales orders in the fact table.
With fact tables, it is common to see columns that are filled with numbers and dates. The numbers can be units of measurement, such as sale amount, or they can be keys, such as a customer ID. The dates represent time that is being recorded, like order date or shipped date.
What are dimension tables?
They contain the details about the data in fact tables: products, locations, employees, and order types. These tables are connected to the fact table through key columns. Dimension tables are used to filter and group the data in fact tables. The fact tables, on the other hand, contain the measurable data, such as sales and revenue, and each row represents a unique combination of values from the dimension tables. For the total sales orders visual, you could group the data so that you see total sales orders by product, in which product is data in the dimension table.
What are some typical differences between fact and dimension tables?
Fact tables are much larger than dimension tables because numerous events occur in fact tables, such as individual sales. Dimension tables are typically smaller because you are limited to the number of items that you can filter and group on. For instance, a year contains only so many months, and the United States are composed of only a certain number of states.
What are some attributes of a simple table structure (semantic model)?
A simple table structure will:
- Be simple to navigate because of column and table properties that are specific and user-friendly.
- Have merged or appended tables to simplify the tables within your data structure.
- Have good-quality relationships between tables that make sense.
- If warranted, group columns into folders
How many relationships can you have between two tables?
One active
Several inactive
What are ways to build a common date table, and what step is important regardless of how you create the date table?
- Source data
- DAX
- Power Query
And, remember to mark it as the official date table.
How do you build a date table by using source data?
Occasionally, source databases and data warehouses already have their own date tables. If the administrator who designed the database did a thorough job, these tables can be used to perform the following tasks:
Identify company holidays Separate calendar and fiscal year Identify weekends versus weekdays
Source data tables are mature and ready for immediate use. If you have a table as such, bring it into your semantic model and don’t use any other methods that are outlined in this section. We recommend that you use a source date table because it is likely shared with other tools that you might be using in addition to Power BI.
How do you build a date table by using DAX?
You can use the Data Analysis Expression (DAX) functions CALENDARAUTO() or CALENDAR() to build your common date table.
In Power BI Desktop, go to the Table tab on the ribbon. Select New Table, and then enter in the DAX formula. Remember to add columns for only year, month number, week of year, and perhaps week day.
The CALENDAR() function returns a contiguous range of dates based on a start and end date that are entered as arguments in the function.
Alternatively, the CALENDARAUTO() function returns a contiguous, complete range of dates that are automatically determined from your semantic model. The starting date is chosen as the earliest date that exists in your semantic model, and the ending date is the latest date that exists in your semantic model plus data that has been populated to the fiscal month that you can choose to include as an argument in the CALENDARAUTO() function.
How do you build a date table by using Power Query?
You can use M-language, the development language that is used to build queries in Power Query, to define a common date table.
Select Transform Data in Power BI Desktop, which will direct you to Power Query. In the blank space of the left Queries pane, right-click to open the following drop-down menu, where you will select New Query > Blank Query.
In the resulting New Query view, enter the following M-formula to build a calendar table:
List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
After you have realized success in the process, you notice that you have a list of dates instead of a table of dates. To correct this error, go to the Transform tab on the ribbon and select Convert > To Table. As the name suggests, this feature will convert your list into a table.
Next, you want to add columns to your new table to see dates in terms of year, month, week, and day so that you can build a hierarchy in your visual.
Why do you need to mark your date table as the date table?
By marking your table as a date table, Power BI performs validations to ensure that the data contains zero null values, is unique, and contains continuous date values over a period. You can also choose specific columns in your table to mark as the date, which can be useful when you have many columns within your table. Right-click the table, select Mark as date table, and then select Date table settings. The following window will appear, where you can choose which column should be marked as Date.
Remember: selecting Mark as date table will remove autogenerated hierarchies from the Date field in the table that you marked as a date table. For other date fields, the auto hierarchy will still be present until you establish a relationship between that field and the date table or until you turn off the Auto Date/Time feature. You can manually add a hierarchy to your common date table by right-clicking the year, month, week, or day columns in the Fields pane and then selecting New hierarchy. This process is further discussed later in this module.
How do you set up a table so that you can drill down in visuals?
Create hiearchies
What does it mean to “flatten” a hierarchy?
The process of viewing multiple child levels based on a top-level parent. For example, seeing all subordinates to a manager, specifically the separate and multiple subordinate levels under a CEO, for example.
