Sử dụng Query Folding trong Power BI

Ngày đăng: 13/11/2022

Khi xây dựng báo cáo bằng Power BI, chúng ta thường phải kết nối đến nhiều nguồn dữ liệu khác nhau. Độ lớn, độ phức tạp của dữ liệu cũng như các thao tác phức tạp khiến hiệu suất load dữ liệu bị ảnh hưởng. Do đó, việc tối ưu hiệu suất xử lý và load dữ liệu là một việc cần thiết. Một số cách chúng ta thường làm như sau:
– Loại bỏ những dòng, những cột không cần thiết.
– Biến đổi dữ liệu từ bảng dữ liệu chi tiết thành bảng tổng hợp
– Biến đổi dữ liệu ngay trong chính dữ liệu nguồn
– Tách cột dữ liệu date time thành 2 cột date và time
– Cuối cùng, đó là sử dụng Query folding
Bài viết này sẽ giải thích về Query folding và cách sử dụng chúng.
Query Folding là khả năng dịch một truy vấn ngôn ngữ M trong Power Query tạo ra một câu lệnh truy vấn có thể được biên dịch và thực thi bởi nguồn dữ liệu của người dùng. Thao tác này cung cấp khả năng thực thi truy vấn nhanh hơn nhiều so với việc trích xuất tất cả dữ liệu được yêu cầu từ nguồn dữ liệu của bạn và chạy tất cả các truy vấn chuyển đổi được yêu cầu trong Power Query, từ đó tiết kiệm thời gian cho các chuyển đổi đó trong Power Query.

Cơ chế Query Folding

Khi chúng ta làm việc với nguồn cơ sở dữ liệu trong Power Query Editor, thao tác clean và transform chúng ta thao tác trên đó, bản chất là Power BI thực thi M query. Power Query kéo dữ liệu nguồn đổ về, và tại đây, Power Query thực thi các lệnh clean, transform.

Quá trình này sẽ mất thời gian. Để tăng hiệu năng xử lý, Power Query sẽ đánh giá những thao tác của chúng ta, xem xét chúng có thể thực hiện được ngay trong chính cơ sở dữ liệu hay không. Nếu một thao tác hoặc một chuỗi thao tác có thể thực hiện được ngay tại đấy, Power Query sẽ chuyển biên dịch từ M query sang ngôn ngữ của nguồn dữ liệu, gọi là Native query, sau đó truyền tới nguồn dữ liệu. Và tại đấy, thao tác xử lý được thực thi.

Các thao tác có thể sử dụng Query Folding

Các phép biến đổi nguồn dữ liệu quan hệ có thể đạt được Query Folding là những phép biến đổi có thể được viết dưới dạng một câu lệnh SELECT đơn lẻ. Câu lệnh SELECT có thể được xây dựng với các mệnh đề WHERE, GROUP BY và JOIN thích hợp. Nó cũng có thể chứa các biểu thức cột (tính toán) sử dụng các hàm dựng sẵn phổ biến được cơ sở dữ liệu SQL hỗ trợ. Các phép biến đổi có thể sử dụng Query Folding:

  • Xóa cột
  • Đổi tên cột (SELECT và dùng As).
  • Lọc các hàng, với các giá trị tĩnh hoặc tham số Power Query (vị ngữ mệnh đề WHERE).
  • Phân nhóm và tóm tắt (mệnh đề GROUP BY).
  • Mở rộng các bản ghi thuộc tính (các cột khóa ngoại nguồn) để đạt được sự kết hợp của hai bảng nguồn (mệnh đề JOIN).
  • Merge non-fuzzy các truy vấn có thể fold dựa trên cùng một nguồn (mệnh đề JOIN).
  • Append các truy vấn có thể fold dựa trên cùng một nguồn (toán tử UNION ALL).
  • Thêm cột tùy chọn với logic đơn giản (biểu thức SELECT cột). Logic đơn giản ngụ ý các hoạt động không phức tạp, có thể bao gồm việc sử dụng M function có các chức năng tương đương trong nguồn dữ liệu SQL, như các hàm thao tác toán học hoặc văn bản. Ví dụ: các biểu thức sau đây trả về thành phần năm của giá trị cột OrderDate (để trả về giá trị số).
  • Pivot và unpivot (toán tử PIVOT và UNPIVOT).

Các phép chuyển đổi ngăn chặn Query Folding

Danh sách sau đây mô tả các phép biến đổi ngăn truy vấn gấp. Danh sách này không phải là một danh sách đầy đủ.

  • Merging các truy vấn dựa trên các nguồn khác nhau.
  • Append các truy vấn dựa trên các nguồn khác nhau.
  • Thêm các cột tùy chọn với logic phức tạp. Logic phức tạp ngụ ý rằng  các hàm M  không có hàm tương đương trong nguồn dữ liệu. Ví dụ: các biểu thức sau định dạng giá trị cột OrderDate (để trả về giá trị văn bản).
  • Thêm cột chỉ mục.

Xác định khi nào một truy vấn có thể đạt được Query Folding

Trong cửa sổ Power Query Editor, bạn có thể xác định thời điểm có thể xếp lại một truy vấn Power Query. Trong phần Applied Steps, click chuột phải vào 1 step, nếu thông tin View Native Query được bật thì có nghĩa thao tác đó có thể được chuyển hóa thành Query Folding.

(Nguồn: https://learn.microsoft.com/en-us/power-query/power-query-folding)

Nếu bạn bấm chuột phải vào bước được áp dụng cuối cùng, nếu tùy chọn View Native Query được bật (không chuyển sang màu xám), thì toàn bộ truy vấn có thể được fold. Nếu tùy chọn View Native Query không được bật (chuyển sang màu xám), thì đây là bằng chứng cho thấy không phải tất cả các bước truy vấn đều có thể được fold. Tuy nhiên, vẫn có thể một tập hợp con các bước vẫn có thể được gấp lại. Bằng cách tra cứu ngược lại từ bước cuối cùng, bạn có thể kiểm tra từng bước để xem liệu tùy chọn View Native Query có được bật hay không. Nếu vậy, thì bạn sẽ biết được ở đâu, trong trình tự các bước, Query Folding không thể đạt được nữa.
Chúng ta có thể click vào View Native Query để xem đoạn query đã được biên dịch và được sử dụng bên trong chính Cơ sở dữ liệu để tăng hiệu năng.

(Nguồn: https://learn.microsoft.com/en-us/power-query/power-query-folding)

Tối ưu việc sử dụng Query Folding

Như ở trên, chúng ta có thể thực hiện Query Folding toàn bộ các thao tác biến đổi nhưng cũng có thể chỉ thực hiện được 1 phần, hoặc chỉ đúng 1 thao tác. Khi gặp 1 step không thể biên dịch thành Native Query, tất cả các step sau đó sẽ không thể chuyển thành Native Query được nữa. Điều này dẫn đến chúng ta chỉ có thể thực hiện được Query Folding 1 phần.
Để tối đa các thao tác có thể chuyển thành Native Query từ đó tạo ra Query Folding toàn bộ hoặc Query Folding 1 phần lớn nhất có thể, chúng ta nên thực hiện liền nhau các thao tác có thể chuyển thành Native Query.
Ví dụ, chúng ta cần làm các thao tác sau: Xóa cột, Xóa dòng, đổi tên cột, append với 1 bảng khác. Thứ tự chúng ta nên làm sẽ là:
1.Xóa cột
2.Xóa dòng
3.Đổi tên cột
4.Append
Vì thao tác Append không thể chuyển thành Native Query do đó chúng ta đặt sau cùng.

Tổng kết

Vậy là hôm nay chúng ta đã hiểu hơn về cơ chế Query Folding của Power Query và cách nó giúp cho việc xử lý dữ liệu và load dữ liệu được nhanh hơn. Hãy lưu nhanh kĩ năng này về cẩm nang kiến thức của mình để có thể áp dụng nọ mọi lục mọi nơi nhé!

Với các bạn mới bắt đầu tìm hiểu Power BI & mong muốn ứng dụng Power BI trong công việc, hãy tham khảo khóa học PL-300: Microsoft Power BI Data Analyst từ Datapot.

Được thiết kế và giảng dạy bởi các chuyên gia giàu kinh nghiệm làm việc và đào tạo trong ngành dữ liệu, được triển khai tại các doanh nghiệp như Unilever, SHB, MB Life Ageas,.., khóa học sẽ mang đến cho bạn những kiến thức và kỹ năng thực tiễn nhất.

ĐĂNG KÝ NGAY

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *