Những phương pháp tốt nhất khi xây dựng mô hình dữ liệu trong Microsoft Power BI

Chào các bạn,

Trong bài viết hôm nay tôi sẽ chia sẻ một số phương pháp tốt nhất (best practices) khi xây dựng mô hình dữ liệu (data modeling) trong Microsoft Power BI. Các phương pháp này sẽ phát huy tác dụng tốt nhất khi project của các bạn bắt đầu scaling ( dataset có thể mở rộng tới hàng triệu dòng). Nếu dữ liệu của bạn nhỏ (dataset khoảng vài ngàn record) thì có thể bạn sẽ không thấy sự khác biệt nhiều lắm. Microsoft Power BI giúp người dùng có thể dễ dàng để bắt đầu, nhưng để có thể master được Power BI thì bạn thực sự cần hiểu rõ và có kinh nghiệm sử dụng những kỹ thuật cao hơn như M Query và DAX (Data Analysis Expression) language. Trong đó, việc có thể thành thạo về DAX sẽ đòi hỏi ở bạn rất nhiều thời gian để thực hành.

Tình huống

Một mô hình dữ liệu thiếu hiệu quả sẽ làm chậm tốc độ hiển thị của báo cáo, thậm chí là với một bộ dữ liệu nhỏ.

Đề xuất giải pháp

Xây dựng một mô hình nhỏ nhất có thể.
Mô hình dữ liệu dạng star schema sẽ hỗ trợ tốt cho việc phân tích sau này.
Các mối quan hệ của các bảng dữ liệu phải được xây dựng một cách có mục đích và ý đồ rõ ràng.

Cardinality là gì?

Cardinality là phép nối giữa hai bảng thể hiện mối quan hệ số giữa các hàng của một bảng với các hàng của bảng còn lại.
Cardinality là cách quản lý mối quan hệ giữa các bảng dữ liệu.
Bạn có thể đọc thêm link bên dưới để hiểu hơn cách tạo và quản lý mối quan hệ giữa các bảng dữ liệu trong Microsoft Power BI.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

Bối cảnh

Mô hình dữ liệu chứa các cột có độ chính xác cao hơn cần thiết. Ví dụ như ngày giờ chính xác tới mili giây, trọng lượng chính xác tới 6 số thập phân.
Mô hình dữ liệu chứa các cột với số lượng các giá trị unique cao.

Tại sao điều này không tốt?

Độ nén dữ liệu thấp hơn khi độ chính xác/cardinality cao.
Tăng thời gian load dữ liệu vào bộ nhớ
Tăng refresh time

Đề xuất giải pháp

Xóa các cột nếu cần thiết
Giảm độ chính xác
Chia ngày giờ thành hai cột: ngày và giờ

Bối cảnh

Hầu hết các mối quan hệ của mô hình dữ liệu là 2 chiều

Tại sao điều này không tốt?

Sử dụng filters/slicers sẽ khiến quá trình phải xử lý nhiều mối quan hệ và làm quá trình chậm đi.
Một vài filter hầu như không tạo thêm giá trị nào cho việc phân tích

Đề xuất giải pháp

Chỉ sử dụng quan hệ 2 chiều khi mà bối cảnh thực sự đòi hỏi.

Microsoft Power BI Basic Quiz

This is the quiz to test your knowledge on data analysis and Microsoft Power BI. There will be maximum 60 minutes for the test.

Let's do the test and find out your result.

1 / 47

Which data role enables advanced analytics capabilities through reports and visualizations?

1) Data scientist

2) Data engineer

3) Data analyst

2 / 47

Which data analyst task has a critical performance impact on reporting and data analysis?

1) Model

2) Analyze

3) Visualize

3 / 47

Which one of the following options is a key benefit of data analysis?

1) Decisive analytics

2) Informed business decisions

3) Complex reports

4 / 47

What is the common flow of activity in Power BI?

1) Create a report in Power BI mobile, share it to the Power BI Desktop, view and interact in the Power BI service.

2) Create a report in the Power BI service, share it to Power BI mobile, interact with it in Power BI Desktop.

3) Bring data into Power BI Desktop and create a report, share it to the Power BI service, view and interact with reports and dashboards in the service and Power BI mobile.

4) Bring data into Power BI mobile, create a report, then share it to Power BI Desktop.

5 / 47

Which of the following are building blocks of Power BI?

1) Tiles, dashboards, databases, mobile devices.

2) Visualizations, datasets, reports, dashboards, tiles.

3) Visual Studio, C#, and JSON files.

6 / 47

A collection of ready-made visuals, pre-arranged in dashboards and reports is called what in Power BI?

1) The canvas.

2) Scheduled refresh.

3) An app.

7 / 47

What type of expression do you use to extract data from Microsoft SQL Server?

1) DAX

2) T-SQL

3) MDX

8 / 47

You're creating a Power BI report with data from an Azure Analysis Services Cube. When the data refreshes in the cube, you would like to see it immediately in the Power BI report. How should you connect?

1) Import

2) Connect live

3) Direct Query

9 / 47

What can you do to improve performance when you are getting data in Power BI?

1) Do some calculations in the original data source

2) Only pull data into the Power BI service, not Power BI Desktop

3) Use the Select SQL statement in your SQL queries when you are pulling data from a relational database.

4) Combine date and time columns into a single column

10 / 47

What is a risk of having null values in a numeric column?

1) That function SUM of data will be incorrect.

2) That function MAX of data will be incorrect.

3) That function AVERAGE of data will be incorrect.

11 / 47

If you have two queries that have different data, but you want to concatenate the results into one query with all the combined rows, which operation should you perform?

1) Append

2) Merge

3) Combine Column

12 / 47

Which of the following selections are not best practices for naming conventions in Power BI?

1) Rename columns to have spaces in them.

2) Abbreviate column names.

3) Replace values that have integers with human readable results.

13 / 47

What does data granularity mean?

1) Data granularity is the filter direction that is associated with the relationship between two columns.

2) Data granularity is the level of detail that is represented in the data.

3) Data granularity is a many-to-many relationship.

4) Data granularity is a type of schema design.

14 / 47

What is the difference between a fact table and a dimension table?

1) Fact tables contain observational data while dimension tables contain information about specific entities within the data.

2) Fact tables contain information about specific entities while dimension tables contain information about observational data.

3) There is no difference.

4) Dimension tables tell you about specific roles in Power BI while fact tables tell you information about facts that are associated with those roles in Power BI.

15 / 47

Which two functions will help you compare dates to the previous month?

1) CALCULATE and PREVIOUSMONTH

2) TOTALYTD and PREVIOUS MONTH

3) CALCULATE and TOTALTYD

16 / 47

Why would you want to override the default context?

1) To create measures that behave according to your intentions, regardless of what the user selects.

2) To create measures that do not summarize or average.

3) To create new relationships.

17 / 47

How do you use an inactive relationship in a single measure?

1) Change the active relationship in the Modeling tab.

2) Use the USERELATIONSHIP function.

3) Delete one relationship.

18 / 47

What benefit do you get from analyzing the metadata?

1) The benefit of analyzing the metadata is that you can clearly identify data inconsistences with your dataset.

2) The benefit of analyzing the metadata is to get familiar with your data.

3) The benefit of analyzing the metadata is to know the number of rows, columns and tables being loaded into your model.

19 / 47

What can be achieved by removing unnecessary rows and columns?

1) It is not necessary deleting unnecessary rows and columns and it is a good practice to keep all metadata intact.

2) Deleting unnecessary rows and columns will reduce a dataset size and its good practice to load only necessary data into your data model.

3) Deleting unnecessary rows and columns can damage the structure of the data model.

20 / 47

Is it possible to create a relationship between two columns if they are different DATA TYPE columns?

1) Yes, if cardinality of the relationship is set to Many-to-Many.

2) No, both columns in a relationship must be sharing the same DATA TYPE.

3) Yes, the above is fully supported in latest version of Power BI desktop.

21 / 47

What is the benefit of using a report tooltip?

1) To give users additional information about a report visual, such as the author and date/time it was created.

2) To provide additional detail that is specific to the context of the data that is being hovered over.

3) To give users the ability to export data from the visual.

22 / 47

Which of the following options is not one of the four components in the Q&A visualization?

1) The question box, where users enter their question and are shown suggestions to help them complete their question.

2) A pre-populated list of suggested questions

3) Automatic creation of a custom tooltip

4) An icon that users can select to convert the Q&A visual into a standard visual.

5) An icon that users can select to open Q&A tooling, which allows designers to configure the underlying natural language engine.

23 / 47

Do you need to import custom visuals each time you want to use them when you are developing a new report?

1) No, custom visuals are always available for selection under the Visualization pane.

2) Yes, custom visuals must be imported from AppSource each time you start developing a new report.

3) No, custom visuals only need to be imported once and will always remain in Power BI for future use in a new report.

24 / 47

Which of the following filters are not available in Power BI reports?

1) Drillthrough

2) Page type

3) Report level

4) Page level

25 / 47

How can you analyze performance of each of your report elements?

1) By analyzing your metadata

2) By using performance analyzer

3) By deleting unnecessary rows and columns to reduce your dataset size

26 / 47

Can you use bookmarks to create a slide show in Power BI?

1) No, you cannot, because bookmarks are not dynamic.

2) Yes, you can, by adding buttons as navigation to go between saved bookmarks.

3) No, you will require a specific visual to achieve this task.

27 / 47

What is a dashboard?

1) A canvas of report elements that can be built in Power BI Desktop

2) A canvas of report elements that can be built in Power BI service

3) Dashboards can be built by using visuals that are developed with an underlying data source.

4) The canvas in which you can view the data model of a report

28 / 47

What is one way that reports and dashboards differ?

1) They are the same.

2) In reports, you can use the slicers and filter by selecting a data point on a visual; in dashboards, you can only filter a dashboard tile in focus mode, but can't save the filter.

3) In reports, you can have multiple pages; in dashboards, you can have only one page.

4) You can only build reports and dashboards in Power BI service.

29 / 47

Where can you configure and set data alerts?

1) Data alerts can be set only in Power BI service on specific visuals such as KPI cards, gauges, and cards.

2) Data alerts can be set in both Power BI service and Power BI Desktop on any kind of visual.

3) Data alerts can be set in Power BI service on any kind of visual.

4) Data alerts can be set only in Power BI Desktop on specific kinds of visuals such as KPI cards and gauges.

30 / 47

Why are parameters important in Power BI paginated reports?

1) They allow the report developer to control the refresh interval of the report.

2) They allow the user to control aspects of how the report is rendered when the report is run.

3) They are required so that Power BI can call the paginated report.

31 / 47

Power BI paginated reports are created by using which tool?

1) Power BI Desktop

2) Power BI service

3) Power BI Report Builder

32 / 47

Power BI paginated reports is an evolved technology that was built from which original tool?

1) SQL Server Analysis Services

2) SQL Server Reporting Services

3) Microsoft SharePoint

33 / 47

What Power BI feature can give an in-depth analysis of the distribution of data?

1) The Next Level of Hierarchy feature can give in-depth analysis because it will allow you to drill down for all subcategories and is not used to analyze the distribution.

2) The Analyze feature allows a user to understand why the distribution looks the way that it does.

3) Only time series analysis can provide in-depth analysis on the data.

34 / 47

Where are time series charts located?

1) The filter pane is where all filters on visuals and pages are located.

2) Time series charts can be imported from AppSource.

3) The fields pane is where all charts are located.

35 / 47

What visual should be used to display outliers?

1) The line chart is best-suited to display outliers.

2) The scatter chart is best-suited to display outliers.

3) The clustered column chart is best-suited to display outliers.

36 / 47

What does the AI splits feature do?

1) AI splits work by considering all available fields and determining which one to drill into and will only return the highest or lowest value within your dataset.

2) AI splits work by considering all available fields and determining which one to drill into to get the highest/lowest value of the measure that is being analyzed.

3) AI splits only display the difference between highest and lowest value of the measure that is being analyzed.

37 / 47

Can you access the Q & A feature by using buttons?

1) No, to use the Q&A feature, you will need to add the Q&A visual to your reporting canvas.

2) Yes, you can access the Q&A feature by selecting Q&A button type.

3) Yes, you can, but you will need to add the Q&A visual to your reporting canvas and then link your button with the visual that you have added.

38 / 47

Which of the following selections are not features of the Q&A visual?

1) Adding new synonyms to fields through Q&A tooling.

2) Converting a Q&A answer into a visual inside your report.

3) Searching for help topics about Power BI.

39 / 47

What does the decomposition tree not enable you to do?

1) Conduct root cause analysis to understand a measure better.

2) Conduct what-if analysis with built-in parameters.

3) Automatically analyze selected dimensions to find where a measure is highest or lowest.

40 / 47

How is the Admin workspace role different from other types of workspace roles?

1) Admin is the only role that can add and remove users.

2) Admin is the only role that can publish or update apps.

3) Admin is the only role that can create, edit, or delete content in a workspace.

4) Admin is the only role that can publish content to a workspace.

41 / 47

Which one of the following options is the best description of a workspace?

1) A workspace is a feature in Power BI service that allows you to view reports only.

2) A workspace is a feature of Power BI Desktop that allows you to build reports only.

3) A workspace is a centralized location or repository that allows you to collaborate with colleagues and teams to create collections of reports, dashboards, and so on.

4) A workspace is a feature that allows you to view and edit the data model, build visualizations, and transform the data.

42 / 47

What feature in Power BI service can you use to troubleshoot the flow of data from its source to its destination?

1) Usage Metrics report

2) Query Caching

3) Quick Insights

4) Lineage view

43 / 47

Where are dataset-scheduled refreshes configured?

1) Power BI service

2) Power BI Desktop

3) AppSource

44 / 47

What reserved parameters configure the start and end of where Incremental refresh should occur?

1) Start and End parameters

2) StartRange and EndRange

3) RangeStart and RangeEnd

45 / 47

What is the difference between Promotion and Certification when you are endorsing a dataset?

1) Promotion does not need specific permissions while Certification requires permission from the dataset owner to access to the dataset.

2) Promotion is for broad usage while Certification needs permission granted on the Admin Tenant settings.

46 / 47

Which function will tell you the username of the person who is signed in to Power BI service?

1) USERPRINCIPALNAME()

2) LOOKUPVALUE()

3) USEROBJECTID()

47 / 47

Where can you test RLS by using different security roles?

1) Power BI Desktop only

2) Power BI service only

3) Both Power BI Desktop and Power BI service

Your score is

The average score is 28%

Chia sẻ bài viết

Những phương pháp tốt nhất khi xây dựng mô hình dữ liệu trong Microsoft Power BI

About Author

Xây dựng mô hình dữ liệu tốt (Data Modeling)

Tình huống

Đề xuất giải pháp

Kiểm tra kiến thức của bạn

Di chuyển việc tính toán tới nguồn dữ liệu

Bối cảnh

Tại sao điều này không tốt?

Đề xuất giải pháp

Kiểm tra kiến thức của bạn

Xóa các bảng và cột dữ liệu không sử dụng đến

Bối cảnh

Tại sao điều này không tốt?

Tránh việc sử dụng các cột có độ chính xác/cardinality cao

Bối cảnh

Tại sao điều này không tốt?

Đề xuất giải pháp

Tránh sử dụng chuỗi (Strings), sử dụng số nguyên (Integers) nếu có thể

Tại sao điều này không tốt?

Đề xuất giải pháp

Sử dụng mã định danh số nguyên (Integer Surrogate Keys), tiền-sắp xếp (pre-sorting) mã định danh trước khi load dữ liệu vào mô hình

Cẩn thận khi sử dụng quan hệ 2 chiều (Bi-Directional Relationships)

Bối cảnh

Tại sao điều này không tốt?

Đề xuất giải pháp

Sử dụng chế độ Summarization mặc định

Bối cảnh

Tại sao điều này không tốt?

Đề xuất giải pháp

Bạn có muốn kiểm tra kiến thức về Power BI của mình không?

Bài viết bạn có thể quan tâm

1 thought on “Những phương pháp tốt nhất khi xây dựng mô hình dữ liệu trong Microsoft Power BI”

Leave a Comment Cancel Reply