PowerBI Article 2: Data Modeling and powerbi Article 2

PowerBI Article 2: Data Modeling and powerbi Article 2

When analyzing data, you cannot always analyze a single data table. Sometimes you need to import multiple data tables to PowerBI, complex data analysis tasks are performed through data in multiple tables and their relationships. Therefore, to accurately calculate the analysis results, you must create a relationship between data tables in data modeling. In PowerBI, Relationship refers to the Cardinality and Cross Filter ction between data tables ).

Base (CArdinality)

The base relationship is similar to the foreign key reference of a relational table. It is associated with a single data column between two data tables. The data column is called a query column, and the base relationship between two data tables is, or 1: N, or N: 1, the meaning of the base relationship is:

• Multiple to one (N: 1): This is the most common default type. This means that a column in a table can have multiple instances with one value, while another related table (often called the lookup table) has only one instance with one value.
• One-to-one (): this means that a column in a table has only one instance with a specific value, and that is, the other related table.

For example, if the base relationship between TableA and TableB is 1: N, TableA is a query table of TableB, and TableB is called a reference table. In a query table, the values of the query columns are unique, duplicate values are not allowed. In the referenced table, the values of the query column are not unique.

In PowerBI, sometimes the referenced table references data that does not exist in the query table. By default, PowerBI automatically adds a value Blank to the query table, and all values that do not exist in the query table, all are mapped to Blank.

Cross Filter ction)

The filtering direction is the flow of filtering, indicating that a filtering condition filters other related tables. For example, if TableA filters TableB, the filtering direction can be bidirectional or unidirectional:

• Bidirectional: the default direction. This means that for filtering, both tables are considered to be the same table, which is very suitable for a single table with multiple query tables.
• Unidirectional: this means that one table can only be filtered by another table, but not by Reverse filtering.

1. bidirectional filtering

In the star structure, there is a reference table in the middle, surrounded by multiple search tables. The filtering relationship between the reference table and the search table is bidirectional, as shown below:

In general, two-way filtering is used for the star structure, which is the default direction. However, two-way filtering is not suitable for the modes in the following graph. In this mode, the filtering direction forms a loop, for this type of link mode, two-way filtering creates a set of relationships with unknown semantics. For example, to obtain the sum of a field in TableX, if you select to filter by a field in TableY, it is unclear how the filter flows, whether it flows through the top table or the bottom table?

If two-way filtering results in the ambiguity of the Data relationship, you can import the table twice (use another name for the second time) to eliminate loops. This will generate a relational model similar to a star architecture. With a star architecture, all links can be set to "two-way ".

2. Create indirect relationships

In the relationship between PowerBI reports, direct relationships refer to the direct contact between two tables of the relationship, and indirect relationships refer to the two data tables that establish a relationship through the intermediate table, two data tables associated with indirect links do not have direct contact. For example, the relationship between Students and StudentCourse is direct, and the relationship between Course and StudentCourse is direct, students and Courses use StudentCourse to establish indirect relationships. Indirect relationships can interact with data through a series of directly related data tables. This is automatically implemented by PowerBI and supports the creation of complex data models. However, exercise caution when using indirect links in data modeling. PowerBI has different options for Filter selection and deselected processing.

1. Use the following script to create a data table with multi-level relationships

The script creates four tables, which are used to represent students, courses, student electives, student presentations, and the relationship between students and courses: 1: N, the relationship between students and speeches is.

Create table dbo. courses (CourseID int not null primary key clustered, CourseName varchar (32) not null) create table dbo. students (StudentID int not null primary key clustered, StudentName varchar (64) not null) create table dbo. studentCourse (StudentID int not null, CourseID int not null, constraint PK _ StudentCourse primary key clustered (StudentID, CourseID) create table dbo. studentSpeaker (StudentID int not null, EventID int not null) insert into dbo. courses (CourseID, CourseName) values (1, 'English '), (2, 'China') insert into dbo. students (StudentID, StudentName) values (1, 'stu _ a'), (2, 'stu _ B '), (3, 'stu _ C'), (4, 'stu _ D') insert into dbo. studentCourse (StudentID, CourseID) values (1, 1), (2, 1), (3, 2) insert into dbo. studentSpeaker (StudentID, EventID) values (1,101), (4,102)View Code

2. Create a relationship between tables in the Relationships view.

Cardinality is created based on the relationship between data, and the filtering direction is set based on the logic of filtering. By default, PowerBI automatically checks the relationship between (AutoDetect) data and creates a link based on the check results (column name and column value uniqueness). In the Relationships view, A link is a line with a Direction. The two ends of the line are numbers, indicating the Cardinality relationship. The directed arrow in the middle of the line indicates the Direction of filtering ).

PowerBI will not be smart to perfection. You need to modify the relationships automatically created by PowerBI based on the internal relationship of the data, or, for example, change the relationships between Students and StudentCourse to: 1: N and bidirectional filtering. Double-click the link (line) to bring up the Edit Relationship form:

The column name and sample data are displayed in the lower panel of each table. The Cardinality relationship is just to one. Its Expression is: *: 1. the table on the left of the expression is located above, and the table on the right is located below, the data columns used to establish the relationship are selected in gray mode. Select Both for Cross filter ction, select Make this relationship active, and click OK to create a link, for example, click link (line ), the data column used to establish the link is selected.

3. Use indirect relationships to meet business needs

The business requirement of a report is to count the number of students as speakers Based on the Course (Course ).

When making reports, you must be familiar with the relationship between data and data. In StudentCourse, three students select courses with student IDs 1, 2, and 3 respectively, in StudentSpeaker, only students with student ID 1 meet the requirements. Therefore,The number of students as speakers Based on the Course statistics should be:

• The number of English students is 0;
• Change the number of students in Chinese to 1;
• Make statistics on all courses. The number of students is the sum of the numbers of optional English and optional Chinese, 1 (= 0 + 1 );

1. Set the course Filter

The Course table is a query table. Because the courses (CourseID) in StudentCourse exist in the Course table, the Blank option does not exist in the Slicer chart.

2. Add a Card chart to display the statistical quantity.

Add a Card chart (Visualizations) to the Page. In the Fields attribute of the chart, select the StudentID FIELD of THE sudentspeaker data table. The attribute value is automatically changed to the aggregate function + of + field value.

 

3. Set Aggregate functions

As a student may act as a Speaker in multiple events, StudentID must be de-duplicated, right-click the Fields attribute value "Count of StudentID" in the chart and select Count (Distinct) as the aggregate function)

4. Set the chart display attributes.

Switch to the "brush" Icon, disable Category lable, enable Title, and modify Title Text, Font color, Alignment, and Text Size,

5. Analyze report data

Select Chinese for the course. The quantity is Blank.

Select English for the course. The number is 1.

Select all courses. The quantity is 1.

6. clean data

By default, no Course (Course) is selected for the chart, and the number is 2. The result is "Incorrect" logically. For filters without any options, powerBI does not perform any filtering Association.

The cause of the error is that StudentSpeaker has dirty data in the data table, and students (in this example, students with student ID 4) who have not taken any courses appear in the StudentSpeaker data table. The query result must be corrected, dirty data must be cleaned.

4. Edit interaction Behaviors

Select different CourseName and measure value Speaker # automatically filters data based on the Filter and re-collects statistics. The flow of this Filter is one-way, determined by the Cross Filter ction in the Data Relationship, PowerBI allows you to edit the interaction between the Filter and the metric value without modifying the relationship, enables different charts in the report to selectively respond to or not respond to Filter conditions ).

1. Select Filter, switch to the Format menu, and select Edit Interactions"

2. Edit interaction behavior

By default, the Filter of the Card chart is selected and switched to disabled. In this way, selecting any option in the Course Filter will not affect the data values displayed in the Card chart.

5. Certain design principles should be observed in Data Modeling

In data modeling, you not only need to attribute business requirements, but also need to be familiar with the data and its relationships and abide by certain design principles to avoid some obvious errors:

• Design the report filtering conditions (Filer) and measurement values based on business needs;
• Filters are the starting point of data modeling. data models are designed based on the internal relationship between the filter conditions and data;
• Load data based on the internal relationship between data to ensure that no dirty data exists in the data table.

 

Articles in the PowerBI series:

• PowerBI Article 1: Design PowerBI reports
• PowerBI Article 2: Data Modeling

 

Reference:

Power BI documentation

Create and manage relationships in Power BI Desktop