3A. Describe Power BI Desktop models Flashcards
What are the high-level steps to create a data model in Power BI?
- Connect to data
- Transform and prepare data
- Define business logic by adding Data Analysis Expressions (DAX) calculations
- Enforce data permissions with row-level security by adding roles
- Publish the model to Power BI
Why is it important to develop optimal data models?
It is important for delivering good query performance and for minimizing data refresh times and the use of service resources, including memory and CPU. The fewer resources that are used, the more models that can be hosted and at lower cost.
What is the default, most optimal, data model?
A star schema
What are some benefits to star schemas?
It presents a user-friendly structure and it supports high-performance analytic queries.
Describe a star schema, and the types of tables in it, and how are those tables related to each other?
It classifies model tables as either fact or dimension. In a diagram, a fact table forms the center of a star, while dimension tables, when placed around a fact table, represent the points of the star.
In the model, dimension tables are related to fact tables by using one-to-many relationships. The relationships allow filters and groups that are applied to dimension table columns to propagate to the fact table. This design pattern is common.
What is a fact table?
Its role is to store an accumulation of rows that represent observations or events that record a specific business activity. For example, events that are stored in a sales fact table could be sales orders and the order lines. You could also use a fact table to record stock movements, stock balances, or daily currency exchange rates. Generally, fact tables contain numerous rows. As time passes, fact table rows accumulate.
What is a dimension table?
It describes your business entities, which commonly represent people, places, products, or concepts. A date dimension table, which contains one row for each date, is a common example of a concept dimension table. The columns in dimension tables allow filtering and grouping of fact table data.
What must each dimension table include?
Each dimension table must have a unique column, which is referred to as its key column. A unique column doesn’t contain duplicate values and it should never have missing values. In a product dimension table, the column could be named ProductKey or ProductID. Likely, additional columns will store descriptive values, like the product name, subcategory, category, color, and so on.
What is an analytic query?
A query that produces a result from a semantic model. Each Power BI visual, in the background, submits an analytic query to Power BI to query the model. The analytic query is written as a Data Analysis Expressions (DAX) query statement. However, you don’t need to write a native DAX statement; you only need to configure report visuals by mapping semantic model fields.
What are the phases of an analytic query, and in what order are they implemented?
- Filtering, or slicing, targets the data of relevance. In Power BI reports, filters can be applied to three different scopes: the entire report, a specific page, or a specific visual. Filtering is also applied in the background when row-level security (RLS) is enforced. Each report visual can inherit filters or have filters directly applied to it.
- Grouping, or dicing, divides query results into groups.
- Summarizing produces a single value result. Typically, numeric columns are summarized by using summarization methods (sum, count, and many others). These methods are simple summarizations. More complex summarizations, like a percent of grand total, can be achieved by defining measures that are written in DAX.
However, not all analytic queries need to filter, group, and summarize:
- Commonly, report visuals are filtered, perhaps by a time period or geographic location.
- Grouping is optional. For example, a card visual, which is used to display a single value, isn’t concerned with grouping.
- Typically, report visuals summarize. One notable exception, however, is the slicer visual, which isn’t concerned with summarization.
What is the standard methodology for adding and configuring visuals in a report?
- Select a visual type, like a bar chart.
- Map semantic model fields, which are displayed in the Fields pane, to the visual field wells. For a bar chart, the wells are Y-axis, X-axis, Legend, Small multiples, and Tooltips.
- Configure mapped fields. It’s possible to rename mapped fields or toggle the field to summarize or not summarize. If the field summarizes, you can select the summarization method.
- Apply format options, like axis properties, data labels, and many others.
What does the word “field” refer to in the context of Power BI reports, and which model resources are fields?
Fields is a collective term that is used to describe a model resource that can be used to configure a visual. The three different model resources that are fields include:
- Columns
- Hierarchy levels
- Measures
Which type of field/model resource cannot be used to Summarize data in a visual?
Hierarchy levels
Which type of field/model resource cannot be used to Group data in a visual, and can only filter data in special cases?
Measures
How can you use columns in a visual?
To filter, group, and summarize column values. Summarizing numeric columns is common, and it can be done by using sum, count, distinct count, minimum, maximum, average, median, standard deviation, or variance. You can also summarize text columns by using first (alphabetic order), last, count, or distinct count. Additionally, you can summarize date columns by using earliest, latest, count, or distinct count.
