Đố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
- Cài đặt
- Cấu hình cho dbt
- Khởi tạo 1 DB demo
- Tổ chức lại dữ liệu trên DW bằng dbt
- 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.sqlSELECT [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.sqlSELECT [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.