INTRODUCTION

Configuring a Power BI semantic model involves refining data structures, creating relationships, and setting up calculations. Semantic model is the last stop in the data pipeline before reports and dashboards are built. It is the end product of the raw data that has been extracted, transformed, loaded, modeled, built relationship, and written calculation. The Semantic model consist of Data connections to one or more data sources,Transformations that clean and prepare the data for reporting, Defined calculations and metrics based on business rules to ensure consistent reports and Defined relationships between tables.

Key words to note in Semantic Modelling are;

1. Fact table and Dimension table:
The Fact table records the quantitative and numerical data. It is where every single details are recorded. The Dimension table act as the descriptive companion to the fact table, containing the attributes or characteristics that provide context to the data.

2. Primary and Foreign Key:
Primary Keys are unique identifier assigned to a specific record with a database table ensuring that no two rows are identical or repeated. foreign Keys are columns or group of columns in one table that provides a link between data in two tables by referencing the primary key of another.

3. Star Schema
Star Schema is a data modeling technique where a central fact table is surrounded by several dimension tables that provide descriptive content.

4. Cardinality
Cardinality defines the kind of relationship between two tables. They are;

• One to Many (1.*)
• Many to one (*.1)
• One to One (1.1)
• Many to Many (.) The cardinality of a relationship is described by the "one" (1) or "many" (*) icons located at the ends of the relationship line.

5. Cross Filter Direction
The direction determine how filters propagate. Possible cross filter options are dependent on the relationship cardinality type.

• One to Many - Single or Both sides
• One to One - Both sides
• Many to Many - Single to either table or both The cross filter direction of a relationship is described by the arrows located in the middle of the relationship line.

6. Active and Inactive Relationship
An Active relationship is the primary relationship that is used by Power BI to calculate and filter data automatically.
An inactive relationship is a secondary relationship activated with the Power BI DAX function temporarily whenever there is a need to perform a specific calculation.
An active relationship is a solid line; an inactive relationship is a dotted line.

7. Disconnected Table
Disconnected Table is a table that doesn't need a relationship to another model table and are useful in what-if scenario or field parameters.

ARCHITECTURE OVERVIEW

In this blog, you learn how to:

• Create model relationships.
• Configure table and column properties.
• Create hierarchies.
• Create quick measures.
• Configure a many-to-many relationship

A. Create Model Relationships
Data modeling is the process of organizing data into tables and creating relationships. The data model is a visual and structural blueprint that defines how data is stored, organized and related within a database.

STEPS

1. In Power BI Desktop, to view all table fields, in the Data pane, right-click an empty area, and then select Expand All.
2. To create a table visual, in the Data pane, from inside the Product table, check the Category field.
3. To add another column to the table, in the Data pane, check the Sales | Sales field.
4. Notice that the table visual lists four product categories, and that the sales value is the same for each, and the same for the total. This is because there isn't a model relationship between these tables.
5. To switch to the model designer, at the left select the Model view icon.
6. On the Home ribbon, select Manage Relationships.
7. In the Manage Relationships window, notice that no relationships are yet defined.
8. To create a relationship, select + New relationship.
9. To configure a relationship from Product table to Sales table, in the From table dropdown list, select the Product table, and in the To table dropdown list, select the Sales table.
10. The following properties were automatically configured:

B. Create additional relationships
New relationships in Power BI are also created through the drag and drop columns.

STEPS

1. Firstly, switch to Model view.
2. From the Reseller table, drag the ResellerKey column onto the ResellerKey column of the Sales table.
3. In the New relationship window, review the configuration, and then select Save.
4. Additional model relationships are created using the following; Region table to Sales table using SalesTerritoryKey columns as the common column for both tables. Salesperson table to Sales table, using the EmployeeKey columns as common key for both tables.
5. In the model view diagram, tables are arranged so that the Sales table is positioned in the center of the diagram, and the related tables are arranged about it. (Disconnected tables are positioned to the side).
6. Save the Power BI Desktop file.

C. Configure the Product table
In this task, you'll configure the Product table with a hierarchy and display folder.

STEPS

1. Switch to Model view.
2. In the Data pane, if necessary, expand the Product table to reveal all fields.
3. In the Product table, right-click the Category column, and the select Create hierarchy.
4. In the Properties pane, in the Name box, replace the value with Products.
5. To add levels to the hierarchy, in the Hierarchy dropdown list, select Subcategory and then select Product, and then select Apply Level Changes.
6. In the Data pane, notice the Products hierarchy. To reveal the hierarchy levels, expand it.
7. To organize columns into a display folder, in the Data pane, first select the Background Color Format column.
8. While pressing the Ctrl key, select the Font Color Format column.
9. In the Properties pane, in the Display Folder box, enter Formatting. 10.In the Data pane, notice that the two columns are now inside a folder.

D. Configure the Region table
This task configures the Region table with a hierarchy and updated categories.

STEPS

1. In the Region table, create a hierarchy named Regions, with the three levels: Group, Country and Region.
2. Select the Country column (not the Country hierarchy level).
3. In the Properties pane, expand the Advanced section (located at the bottom of the pane), and then in the Data Category dropdown list, select Country/Region.

E. Configure the Reseller table
This task configures the Reseller table to add a hierarchy and update data categories.

STEPS

1. In the Reseller table, create a hierarchy named Resellers, with the two levels, Business Type and Reseller
2. Create a second hierarchy named Geography, with the following four levels: Country-Region, State-Province, City, and Reseller
3. Set the data category for the following columns (not within the hierarchy):

• Country-Region to Country/Region
  

• State-Province to State or Province
  

• City to City
  

F. Configure the Sales table
This task configure the Sales table with updated descriptions, formatting, and summarization.

STEPS

1. In the Sales table, select the Cost column.
2. In the Properties pane, in the Description box, enter: Based on standard cost
3. Select the Quantity column.
4. In the Properties pane, from inside the Formatting section, set the Thousands Separator property to Yes.
5. Select the Unit Price column.
6. In the Properties pane, from inside the Formatting section, set the Decimal Places property to 2.
7. In the Advanced group (you may need to scroll down to locate it), in the Summarize By dropdown list, select Average. By default, numeric columns will summarize by summing values together. This default behavior isn't suitable for a column like Unit Price, which represents a rate. Setting the default summarization to average will produce a meaningful result.

G. Bulk update properties
This task updates multiple columns using single bulk updates. This approach is used to hide columns, and format column values.

STEPS

1. In the Data pane (or model diagram), select the ProductKey column in the Product table .
2. While pressing the Ctrl key, select the following 13 columns SalesTerritoryKey (Region Table), ResellerKey (Reseller), EmployeeKey, ProductKey, ResellerKey, SalesOrderNumber and SalesTerritoryKey (Sales Table), EmployeeID, EmployeeKey, UPN (Salesperson Table), EmployeeKey, SalesTerritoryKey (SalespersonRegion), and EmployeeID (Targets).

3. In the Properties pane, set the Is Hidden property to Yes.
   
   The columns were hidden because they're either used by relationships or will be used in row-level security configuration or calculation logic.

4. Multi-select the following three columns: Standard Cost (Product Table), Cost and Sales (Sales Table).

5. In the Properties pane, from inside the Formatting section, set the Decimal Places property to 0 (zero).
   

H. Explore the model interface

STEPS

This task explains how to switch to Report view, review the data model interface, and configure the auto date/time setting.

1. Switch to Report view.
2. In the Data pane, notice the following:

1. In the Options window, on the Current File section, navigate to Data Load > Time Intelligence, and uncheck Auto Date/Time.
2. In the Data pane, notice that the date hierarchies are no longer available.

I. Create quick measures

STEPS

This task creates two quick measures to calculate profit and profit margin. A quick measure creates the calculation formula for you. They're easy and fast to create for simple and common calculations.

1. In the Data pane, right-click the Sales table, and then select New Quick Measure.
2. In the Quick Measure pane, in the Select a Calculation dropdown list. From inside the Mathematical Operations group, select Subtraction.
3. From the Data pane, drag the Sales (Sales field) into the Base Value well.
4. Drag the Sales (Cost field) into the Value to Subtract box.
5. Select Add.
6. In the Data pane, inside the Sales table, notice that new measure. Measures are indicated by the calculator icon.
7. To rename the measure, right-click it, select Rename, then rename as Profit. Tip: To rename a field, you can also double-click it, or select it and press F2.
8. In the Sales table, add a second quick measure, based on the following requirements:
9. Use the Division mathematical operation.
10. Set the Numerator to the Sales (Profit field).
11. Set the Denominator to Sales (Sales field).
12. Rename the measure as Profit Margin.
13. Ensure the Profit Margin measure is selected, and then on the Measure Tools contextual ribbon, set the format to Percentage, with two decimal places.
14. To test the two measures, first select the existing table visual on the page.
15. In the Data pane, check the Profit and Profit Margin measures to add them to the table.
16. Select and drag the right guide to widen the table visual.
17. Verify that the measures produce reasonable results that are correctly formatted.

J. Create a many-to-many relationship

STEPS

This task creates a many-to-many relationship between the Salesperson table and the Sales table.

1. In Report view, select a blank area of the report page.
2. To create a new table visual, in the Data pane, check the following two fields: Salesperson (Salesperson) and Sales (Sales).
3. Notice that Michael Blythe has generated almost 9 million dollars of sales.
4. Switch to Model view, then drag the SalespersonRegion table to position it between the Region and Salesperson tables.
5. Use the drag-and-drop technique to create the following two model relationships:
6. Salesperson | EmployeeKey to SalespersonRegion | EmployeeKey
7. Region | SalesTerritoryKey to SalespersonRegion | SalesTerritoryKey The SalespersonRegion table can be considered to be a bridging table.
8. Switch to Report view, and then notice that the visual hasn't updated the sales result for Michael Blythe hasn't changed.
9. Switch back to Model view, and then follow the relationship filter directions (arrowhead) from the Salesperson table. Note that the Salesperson table filters the Sales table. It also filters the SalespersonRegion table, but it doesn't continue by propagating filters to the Region table (the arrowhead is pointing the wrong direction).
10. To edit the relationship between the Region and SalespersonRegion tables, double-click the relationship.
11. In the Edit Relationship window, in the Cross Filter Direction dropdown list, select Both. 10.Check the Apply Security Filter in Both Directions checkbox. 11.Select Save. 12.Notice that the relationship has a double arrowhead now. 13.Switch to Report view, and then notice that the sales values have still not changed. 14.Switch to Model view.
12. To force filter propagation via the bridging table, edit (double-click) the relationship between the Salesperson and Sales tables. 16.In the Edit Relationship window, uncheck the Make This Relationship Active checkbox. 17.Select Save. Filter propagation will now follow the only active path. 18.In the model diagram, notice that the inactive relationship is represented by a dotted line. 19.Switch to Report view, and then notice that the sales for Michael Blythe are now nearly 22 million dollars. 20.Notice also, that the sales for each salesperson—if added—would exceed the table total. 21.Switch to Model view, and then in the model diagram, select the Salesperson table. 22.In the Properties pane, in the Name box, replace the text with Salesperson (Performance). The renamed table now reflects its purpose: it's used to report and analyze the performance of salespeople based on the sales of their assigned sales regions.

K. Relate the Targets table

STEPS

This task creates a relationship to the Targets table.

1. Create a relationship from the Salesperson (Performance) (EmployeeID column) and the Targets (EmployeeID column).
2. In Report view, add the Targets (Target field) to the table visual.
3. Resize the table visual so all columns are visible.