Data Model
A Power BI data model is a data resource designed for analytics, accessible through queries. Reports can utilize either Data Analysis Expressions (DAX) or Multidimensional Expressions (MDX) as analytic languages to query these data models. DAX is the language employed by Power BI, while paginated reports have the flexibility to use both DAX and MDX. The Analyze in Excel feature specifically utilizes MDX.
PowerBI dataset
After creating a Power BI model using Power BI Desktop, when you upload it to a workspace in the Power BI service, it transforms into a dataset. This dataset serves as a Power BI element, acting as the data source for generating visualizations within Power BI reports and dashboards.
Analytic query
Power BI reports and dashboards interact with datasets through queries. When visualizing dataset information, Power BI initiates an analytic query, which transforms the dataset model into a human-readable query result, particularly effective in visual representations.
The analytic query progresses through three sequential phases:
Filtering
Grouping
Summarizing
Filtering, also referred to as slicing, focuses on a specific subset of model data, with filter values remaining unseen in the query result. Analytic queries commonly employ filters, particularly for time periods and other attributes. Filtering can occur at various levels in a Power BI report, including the report, page, or visual level. Slicer visuals are frequently used in report layouts to filter visuals on the report page. When row-level security (RLS) is enforced in the model, filters are applied to model tables to restrict access to specific data. Additionally, measures that summarize model data can apply filters.
Grouping, sometimes called dicing, segments the query result into distinct groups. Each group serves as a filter, and unlike the filtering phase, filter values are visible in the query result. For instance, grouping by customer filters each group according to the customer.
Summarization yields a singular value result. Typically, a report visual summarizes a numeric field through aggregate functions such as sum, count, minimum, maximum, and others. Simple summarization involves aggregating a column, while complex summarization is achieved by creating a measure using a DAX formula.
Tabular Model
A Power BI model is a tabular model. A tabular model comprises one or more tables of columns. It can also include relationships, hierarchies, and calculations.
Star Schema Design
To create an efficient and user-friendly tabular model, we advise adopting a star schema design. This well-established modeling approach is widely embraced by relational data warehouses. The process involves categorizing model tables into dimensions or facts.
Dimension tables provide descriptions of business entities, encompassing various modeled elements such as products, people, locations, and abstract concepts like time. On the other hand, fact tables store observations or events, representing instances like sales orders, stock balances, exchange rates, or temperature readings. A fact table comprises dimension key columns connecting to dimension tables and numeric measure columns. The layout forms a star, with the fact table at the center and related dimension tables forming the star's points.
Table storage mode
In Power BI, each model table (excluding calculated tables) possesses a storage mode property, which can be categorized as Import, DirectQuery, or Dual. This property dictates how table data is managed within the model:
Import: Queries fetch data stored or cached in the model.
DirectQuery: Queries directly pass through to the underlying data source.
Dual: Queries can either retrieve stored data from the model or pass through to the data source. Power BI dynamically determines the most efficient approach, prioritizing the use of cached data whenever feasible.
Model Framework
The model framework in Power BI is shaped by table storage mode settings, offering options such as import, DirectQuery, or composite. The instructional units in this module elaborate on each framework and offer guidance on their application.
Import Model: This configuration includes tables with the storage mode property set to Import.
DirectQuery Model: Tables in this model have their storage mode property set to DirectQuery and are part of the same source group, with the concept of a source group explained later in this module.
Composite Model: This model involves multiple source groups, providing a flexible approach to modeling.
SWETA SARANGI
25.02.2024