Đối với những khách hàng đã có DW ( SQLserver ) và cần xây dựng 1 big BI Model, việc sử dụng Power Query nội tại trong Power BI đôi khi không phải là ý khôn ngoan - đôi khi rất tệ trong việc dev/maintain. Đối với những dự án như vậy việc sử module hóa việc tranform dữ liệu là cần thiết. Do vậy tôi sẽ giới thiệu dbt 1 tool khá thú vị trong : dễ dàng chuyển các transform từ DEV > TEST > PRD, module hóa các câu lệnh SQL, dễ sử dụng khi có thể tranning cho user.

Cấu trúc của bài viết

1. Cài đặt
2. Cấu hình cho dbt
3. Khởi tạo 1 DB demo
4. Tổ chức lại dữ liệu trên DW bằng dbt
5. Kết luận

Cài đặt

Note: dbt cloud không hỗ trợ cho SQLserver nên chúng ta sẽ phải cài đặt trên On-Prem.

Cấu hình: Windows Server 2022 Datacenter / Microsoft SQL Server 2022 (RC1) - 16.0.950.9 (X64) - Dev / Intel(R) Xeon(R) CPU E5-2673 v4 @ 2.30GHz 2.29 GHz/ 16 GB ram.

Phần mềm download:

• python-3.9.13-amd64: dbt chỉ mới hỗ trợ cho python 3.9 là sao nhất, nếu cài đặt phiên bản mới hơn 3.10 sẽ bị lỗi. Link
• git-2.38.1: cần cài đặt cái này thì dbt mới chạy, ngoài ra còn để sử dụng thư viên của git nữa. Link
• Visual Studio Code: sử dụng công cụ này để gõ lệnh Link
• Microsoft ODBC Driver 18 for SQL Server: sử dụng thư viện này để dbt kết nối Link
• Bản backup db Contoso.10K.bak: sử dụng để làm DW demo Link

Cài đặt các phần mềm trên:

• python: chuột phải vào bộ cài đặt để cài ở mode adminstrator nếu không sẽ báo lỗi. Sau đó ấn nút Install Now ( tích chọn python 3.9 to PATH ) > khi xuất hiện màn hình Setup was sucessful có nghĩa là đã cài thành công.
• Cài đặt git: mặc định tôi không rành git nên sẽ để mặc định cài đặt rồi ấn next đến khi hoàn thành.
• Cài đặt Visual Studio Code: cài đặt theo mặc định
• Microsoft ODBC Driver 18 for SQL Server : nếu có hỏi gì thì cứ ấn next cho xong.

Dựa theo best practices của dbt chúng ta sẽ tạo ra môi trường python riêng cho dbt Link

Mở Window PowerShell rồi gõ tuần từ các lệnh: python -m venv dbt-env # 1. tạo môi trường dbt-env\Scripts\activate # 2. kích hoạt môi trường dbt-e

# 1. tạo môi trường# 2. kích hoạt môi trường dbt-e

Cài đặt thư viện dbt-sqlserver: trong màn hình PowerShell nhập lệch sau rồi gõ enter:

pip install --upgrade pip # 1. Cập nhật pip lên phiên mới nhất pip install -U dbt-sqlserver # 2. Cài đặt thư viên dbt cho sqlserver dbt init sqlserverdemo # 3. Khởi tạo 1 Project dbt mới

Các bước tuần tự 1 -> 3

Cấu hình cho dbt

Thông tin kết nối đến DB không nằm trong project mà nằm ở bên ngoài, bạn di chuyển đến thư mục "C:\Users\n\.dbt\" rồi tạo file profiles.yml có nội dung như bên dưới:

sqlserverdemo # Giống trong dbt_project.yml   target: dev   outputs:     dev:       type: sqlserver       driver: 'ODBC Driver 18 for SQL Server' # (The ODBC Driver installed on your system)       server: localhost # db được cài cùng với dbt       port: 1433       database: demo # kết nối đến db tên demo       schema: dbo       user: tk đăng nhập       password: mật khẩu       trust_cert: true # Db của tôi yêu cầu trust cert

Ở màn hinh PowerShell đang mở gõ:

cd sqlserverdemo

1. Vào bên trong thư mục dbt project

dbt debug

2. Kiểm tra các thiết lập chính xác

dbt run

3. Chạy test để tạo 1 table, 1 view

Các bước tuần tự 1 -> 2

3. Chạy test để tạo 1 table, 1 view

3. Chạy test để tạo 1 table, 1 view

Khởi tạo 1 DB demo

Khôi phục từ file backup đã download Contoso.10K.bak vào DB.

Thay đổi file config:

sqlserverdem   target: dev   outputs:     dev:       type: sqlserver       driver: 'ODBC Driver 18 for SQL Server' # (The ODBC Driver installed on your system)       server: localhost       port: 1433       database: 'Contoso 10K'       schema: dbo       user: tk đăng nhập       password: mật khẩu       trust_cert: trueo

sau đó sử dụng lệnh : dbt debug để kiểm tra lại 1 lần nữaC:\Users\n\dbt-env\sqlserverdemo

Tổ chức lại dữ liệu trên DW bằng dbt

Đa phần các trường hợp chúng ta không có quyền kết nối đến DB PRD. Thậm chí trong môi trường Dev cũng không quyền sửa các bảng trong DW. Thường tôi sẽ làm việc trên 1 Schema riêng cho Power BI và phát triển các Table và View ở đây.

Chuột phải lên dbt project vừa tạo sqlserverdemo nằm trong đường dẫn "C:\Users\n\dbt-env\sqlserverdemo" ( tùy vào máy tính của bạn )

cấu trúc của dbt project

Tạo foldel và file theo cấu trúc, nội dung file ta cứ để trống sẽ điền sau:

SQLSERVERDEM -models --Contoso.yml    

file

--PowerBI         

foldel

---vOrder.sql     

file

---vDate.sql       

file

---vCustomer.sql   

file

---vStore.sql     

file

---vProduct.sql

file

Cập nhật file dbt_project.yml; ta bổ sung thêm cấu hình cho Foldel Power BI

... models   sqlserverdemo:

Config indicated by + and applies to all files under models/example/

    example:       +materialized: view     PowerBI:       +materialized: view

mode

      +schema: PowerBI

custom schema

Nội dung của các file vừa tạo mới:

--Contoso.yml version: 2 sources:   - name: Data     tables:       - name: Orders         - name: OrderRows       - name: Date         - name: Customer       - name: Product         - name: Store

• name: CurrencyExchange vOrder.sql

  SELECT         Orders.OrderKey AS [Order Number],         OrderRows.[Line Number] AS [Line Number],         Orders.[Order Date],         Orders.[Delivery Date],         Orders.CustomerKey,         Orders.StoreKey,         OrderRows.ProductKey,         OrderRows.Quantity,         OrderRows.[Unit Price],         OrderRows.[Net Price],         OrderRows.[Unit Cost],         Orders.[Currency Code],         [CurrencyExchange].Exchange AS [Exchange Rate]     FROM         {{ source('Data', 'Orders') }}             LEFT OUTER JOIN {{ source('Data', 'OrderRows') }}                 ON Orders.OrderKey = OrderRows.OrderKey             LEFT OUTER JOIN {{ source('Data', 'CurrencyExchange') }}                 ON                     [CurrencyExchange].Date = Orders.[Order Date] AND                     [CurrencyExchange].[ToCurrency] = Orders.[Currency Code] AND                     [CurrencyExchange].[FromCurrency] = 'USD'

  -vDate.sql   

  SELECT       [Date],

  [DateKey],   We do not import DateKey in the view

        [Year],       [Year Quarter],       [Year Quarter Number],       [Quarter],       [Year Month],       [Year Month Short],       [Year Month Number],       [Month],       [Month Short],       [Month Number],       [Day of Week],       [Day of Week Short],       [Day of Week Number],       [Working Day],       [Working Day Number] from {{ source('Data', 'Date') }}

  -vCustomer.sql 

      SELECT         [CustomerKey],         [Gender],         [GivenName] + ' ' + [Surname] AS [Name],         [StreetAddress] AS [Address],         [City],         [State] AS [State Code],         StateFull AS [State],         ZipCode AS [Zip Code],         [Country] AS [Country Code],         [CountryFull] AS [Country],         [Continent],         [Birthday],         [Age] AS [Age]     FROM         {{ source('Data', 'Customer') }}

  -vStore.sql  SELECT     StoreKey,     [Store Code],     [Country],     [State],     [Name],     [Square Meters],     [Open Date],     [Close Date],     [Status] FROM     {{ source('Data', 'Store') }} ---vProduct.sql     SELECT         ProductKey,         [Product Code],         [Product Name],         [Manufacturer],         [Brand],         [Color],         [Weight Unit Measure],         [Weight],         [Unit Cost],         [Unit Price],         [Subcategory Code],         Subcategory,         [Category Code],         Category from {{ source('Data', 'Product') }}

Ra màn hình PowerShell để chạy dbt run để tạo view trên DB: ta sẽ thấy có những view mới được tạo với Schema được tự động dbo_PowerBI.

dbt rất tuyệt khi tạo chúng ta mối liên hệ giữa các đối tượng bằng cách gõ lệnh:

dbt docs generate

document

dbt docs server

openserver

Kết luận

dbt cho phép chúng ta model câu lệnh tạo cảm giác như đang lập trình vậy web hay app. Khi đẩy phần code này lên > TEST > PRD các kết nối sẽ được trỏ đến db >TEST > PRD vô cùng đơn giản khi file config không nằm trong source code.