Thiết kế CSDL QLBH theo mô hình Data Warehouse

[HungBKBIA]

Mình có một bài toán như sau:

Cần quản lý một hệ thống các cửa hàng bán lẻ hoạt động theo mô hình sau:
+/ Các cửa hàng khi gia nhập hệ thống sẽ phải đăng ký các sản phẩm mà cửa hàng đó bán. Toàn bộ thông tin kinh doanh của cửa hàng(bán những sản phẩm nào, số lượng bao nhiêu,...) sẽ được gửi về csdl được đặt trên một server chung theo chu kỳ mỗi giờ một lần vào cuối ngày.
+/ Trong quá trình hoạt động thì cửa hàng có thể đăng ký thêm loại sản phẩm.
+/ Kết quả hoạt động kinh doanh sẽ được lưu trữ lại trong thời gian 5 năm để phục vụ cho việc phân tích và báo cáo.

Với một bài toán như trên thì mình nghĩ ngay đến data warehouse. Tuy nhiên có một khó khăn đó là số loại sản phẩm của cửa hàng lại không thể biết trước được và có thể thay đổi theo thời gian.

Theo mình thì có 2 cách để thiết kế như sau (xét đơn giản với trường hợp là chỉ quản lý doanh thu tổng của từng sản phẩm theo cửa hàng):
+/ C1: DOANHTHU(MaCuaHang,ThoiDiem, MaSP, TongDoanhThu ) --> giải quyết được vấn đề số loại sản phẩm có thể bị thay đổi. Tuy nhiên sẽ làm tăng số lượng dòng trong bảng lên rất nhiều lần và làm tăng thời gian query!
+/ C2: DOANHTHU(MaCuaHang,ThoiDiem,SP1, SP2,....) --> làm giảm thời gian query nhưng lại gặp rắc rối khi có thêm loại sản phẩm mới!

Cao thủ nào có biện pháp giải quyết thì góp ý cho mình với.

[Red Devilic]

Chẳng thấy ai thiết kế CSDL theo C2 của bạn.

Cách lưu dữ liệu và thể hiện ra các báo cáo không nhất thiết, và rõ ràng không cần phải giống nhau. Như thế mỗi lần thêm SP mới, lại phải thay đổi cấu trúc bảng hay sao

DataWarehouse cũng lưu theo cách 1.

Số lượng dòng trong bảng tăng là dĩ nhiên, DW nào cũng có dữ liệu lớn, còn phụ thuộc vào cách bạn truy vấn, index, partition, v.v...

Ngoài ra ở DW không bao giờ thiếu các bảng lưu dữ liệu tổng hợp, nhằm tăng tốc độ truy vấn, và hạn chế query nhiều lần cho cùng 1 dữ liệu.

Bạn nên đọc kĩ thêm về DataWarehouse.

[HungBKBIA]

Cảm ơn bạn Red Devilic đã cho ý kiến. Mình chưa bao giờ làm DWH nên cũng chưa ước lượng được hết các giới hạn. Mình cũng nghĩ cách C1 là hợp lý nhất.
Còn về partition thì mình định chia theo thời gian.

Trong quá trình làm có gì vướng mắc mình sẽ tiếp tục trao đổi.

[vuht2000]

thực ra trong nhiều trường hợp cách làm C2 có thể biện minh được. Tuy nhiên còn phụ thuộc vào mức độ "động" của các thuộc tính (ở đây là các sản phẩm) và nhu cầu phân tích dữ liệu.
Ví dụ nếu cần các query dạng tìm các cửa hàng trong tháng vừa qua bán được ít nhất 10 sản phẩm A và ít nhất 20 sản phẩm B và không bán được sản phẩm C nào... thì C2 rất thích hợp. Nhưng nếu có đến vài trăm sản phẩm thì cách lưu trữ như vậy lại rườm rà quá.
Thiết kế Datawarehouse có hai cách tiếp cận:
- dùng relational model: giống như thiết kế hệ thống OLTP, chuẩn hóa tối đa để đảm bảo tính toàn vẹn dữ liệu cao nhất và độ ổn định của model, thích nghi được với các thay đổi. Ví dụ như thêm sản phẩm cho cửa hàng thì chỉ việc thêm bản ghi thay vì phải sửa đổi cấu trúc bảng. Mô hình này còn gọi là snowflake (bông tuyết xù)
- dùng dimensional model: là thiết kế theo dạng bảng fact bao quanh bởi các bảng dimension. Tên khác của nó là star schema (mô hình hình sao). Cách này không quan tâm đến dạng chuẩn, phi chuẩn thoải mái. Mục đích của nó là đơn giản hóa các câu lệnh SELECT dùng trong phân tích dữ liệu, tránh join nhiều tầng. Và kỹ thuật "dàn hàng ngang" thành các cột như cách C2 ở trên cũng có thể được áp dụng khi cần thiết.
Rõ ràng là hai cách thiết kế trên không dung hoà với nhau. Ở các hệ thống nhỏ có thể bạn sẽ thấy relational model kèm theo một vài bảng bổ sung phục vụ báo cáo (những bảng này đã hơi có mùi dimensional model rồi). Ở những hệ thống lớn hơn, thường mỗi model được tách riêng thành các database khác nhau:
- database dùng relational model tiếp nhận dữ liệu từ các nguồn bên ngoài, kiểm tra chỉnh sửa validate dữ liệu... Dữ liệu sau khi được lưu vào đây đã đảm bảo chặt chẽ nhất và có thể đáp ứng được bất kỳ dạng báo cáo nào (nhớ là "có thể" chứ chưa hẳn đã thích hợp nhất)
- database dùng dimensional model lấy dữ liệu từ database kia, được lưu trữ ở dạng thích hợp với một mục đích báo cáo cụ thể. Database này có thể được dùng thẳng cho báo cáo, hoặc làm đầu vào cho olap cube. Vì mục đích báo cáo thay đổi nên database này cũng có thể thay đổi theo. Và vì database này thụ động lấy nguồn từ database kia nên hoàn toàn có thể tái tạo lại khi cần.

[HungBKBIA]

Theo mình nghĩ nói đến DWH là nói đến một lượng dữ liệu có số bản ghi cực lớn nên việc sử dụng thiết kế như trên OLTP là không hợp lý lắm. Tiếp cận DWH theo hướng nào phụ thuộc vào mô hình OLAP định thiết kế (MOLAP hay là ROLAP). Theo mình tìm hiểu thì thông thường với các ứng dụng không yêu cầu tính toán, tổng hợp cao thì sử dụng ROLAP ngược lại thì dùng MOLAP.

Khi sử dụng mô hình ROLAP thì sẽ phải thiết kế các DDS (Dimension Data store)là trung tâm theo các mô hình là star hoặc snowflake như bạn đã đề cập. Trong trường hợp việc chuyển đổi dữ liệu từ các nguồn về DWH quá phức tạp thì có thể sử dụng thêm khối NDS (Normalize Data store - tại đây sẽ phải tuân thủ chặt chẽ các chuẩn) để làm khâu đệm.

Bạn nào muốn tìm hiểu thêm về DWH thì có 1 tài liệu rất hay đó là: "Building.a.Data.Warehouse.With.Examples.in.SQL.Ser ver"

Hiện tại mình mới chỉ đang đọc tài liệu nên kinh nghiệm thực tế chưa có. Rất mong được trao đổi thêm với các bạn quan tâm.

[vuht2000]

MOLAP và ROLAP chỉ là các phương pháp lưu trữ dữ liệu ở mức vật lý, chứ không liên quan đến mô hình dữ liệu (data model). Và chỉ áp dụng cho dữ liệu tổng hợp (aggregate data), còn khi cần drilldown phải truy nhập vào bảng fact thì vẫn phải vòng sang relational database cho dù olap của bạn là molap.
star và snowflake là hai mô hình khác hẳn nhau. Snowflake chính là mô hình dùng trong các hệ OLTP. Trong datawarehouse nó giúp tạo ra sự ổn định của mô hình và nhất quán dữ liệu như tôi đã nêu ở post trước. Không có gì cản trở nó lưu một lượng dữ liệu lớn. Hệ thống công ty tôi đang dùng sau hơn 1 năm đã có hai bảng fact chứa gần 2 tỷ bản ghi, mỗi tháng bơm vào khoảng 100 triệu bản ghi.
Như bạn nói thì tôi có thể hình dung Normalized data store chính là CSDL tuân theo thiết kế relational model, còn dimensional data store là theo dimensional model.

[megaownage]

Công việc của bạn là làm gì?
1. Bạn là người thiết lập hệ thống Data Warehouse ?
2. Bạn là người phân tích dữ liệu về doanh thu?
3. Bạn là người thiết kế hệ thống cung cấp cữ liệu từ các cửa hàng về Trung Ương?

Chỉ có công ty nhỏ mới xảy ra việc 1 nguòi làm nhiều hơn một điều trên. Trong trường hợp này, đi vào các rắc rối của Data Warehouse là tự rước phiền não vào thân.

Đối với công ty lớn, mỗi công việc trên được phân định rõ ràng. Và 3 người phải cùng làm việc với nhau. Bạn không nên dẫm chân người khác.

[HungBKBIA]

Mình làm phần việc 1 và 2. Minh nghĩ là học cái to để áp dụng cho cái to từ nó trở xuống. Nên mình muốn tìm hiểu để áp dụng. :D

Khi thiết kế DWH, kỹ thuật partition là kỹ thuật không thể thiếu. Tuy nhiên khi sử dụng nó thì lại gặp một vấn đề đó là partition column bắt buộc phải thuộc tập các key column, unique index column. Chẳng hạn như trường hợp sau:

Create table Fact_Value
(
Value_Key bigint identity(1,1),
Date_key int not null,
constraint PK_Fact_Value primary key (Value_Key)
) on ps_Fact_Value_Scheme(Date_Key)

sẽ gây ra lỗi:"Partition columns for a unique index must be a subset of the index key.". Trừ khi sửa thành constraint PK_Fact_Value primary key (Value_Key,Date_Key) thì mới ok.

Như vậy khi thiết kế bảng phải lựa làm sao cho thỏa mãn điều kiện này. Điều này có gò ép quá không?

[Red Devilic]

Cái bạn thiếu, là kiến thức cơ bản

Người ta thì đi từ móng nhà đi lên, bạn thì đi từ nóc nhà đi xuống.

Vì thế nếu bạn muốn làm về DW, và thực sự muốn làm 1 cách nghiêm túc chứ không phải kiểu "làm xong để đấy" thì còn sớm.

Trước khi áp dụng một vài yếu tố mà bạn cho là "bắt buộc phải có", ít ra bạn cũng phải tìm hiểu cụ thể nó là cái gì, và phải nghiên cứu kĩ trước khi áp dụng.

Như lỗi mà bạn nói, nếu đọc qua tài liệu hoặc tra cứu trên MSDN thì cũng rất dễ tìm ra phương án là Không tạo UNIQUE INDEX.

Vì bắt buộc khi tạo Unique Index thì cột Partition phải nằm trong đó. Sửa Unique Index thành Non-Clustered Index là xong.

Tìm hiểu và tìm hỏi nên cân bằng với nhau. Tôi thấy bạn cùng 1 câu hỏi nhưng gửi trong 3 topic khác nhau trong 3 ngày. Không đến thời gian như vậy, chỉ cần 5-10p tập trung tìm kiếm và đọc tài liệu cũng có thể tự trả lời

[HungBKBIA]

Như lỗi mà bạn nói, nếu đọc qua tài liệu hoặc tra cứu trên MSDN thì cũng rất dễ tìm ra phương án là Không tạo UNIQUE INDEX.

Vì bắt buộc khi tạo Unique Index thì cột Partition phải nằm trong đó. Sửa Unique Index thành Non-Clustered Index là xong.


Bạn để ý trường hợp mình đưa ra là: Primary Key (PK lúc nào mà chả là unique) không chứa Partition Column nên mới dẫn đến lỗi đó.

Mình đã tìm hiểu và cũng có một giải pháp như sau nếu vừa muốn giữ Value_Key làm PK, vừa muốn partition column là Date_Key.

B1: Tạo bảng bình thường, không partition cho bảng
Create table Fact_Value
(
Value_Key bigint identity(1,1),
Date_key int not null,
constraint PK primary key nonclustered (Value_Key),
)

B2: Tạo một partitioned clustered index IDX_Fact_Value_Value_Key theo scheme mà mình định partition cho bảng --> việc làm này sẽ biến một bảng không partition thành một bảng có partition.

Create clustered index IDX_Fact_Value_Value_Key
on Fact_Value(Value_key)
on ps_Fact_Value_Scheme(date_key)

B3: Tạo các nonclustered index khác tùy mục đích sử dụng

P/S: bạn Red Devilic nói đúng là mình thiếu kiến thức cơ bản, mình học dần theo cách là gặp khó ở đâu thì tìm hiểu + tìm hỏi ở chỗ đó. Rất cám ơn các bạn đã góp ý và giúp đỡ!

[vuht2000]

Cách của bạn không giải quyết được vấn đề. Clustered index mà bạn tạo ở bước 2 không phải là unique, khi đó partition key sẽ tự động được include vào.
Khi partition table cần lưu ý:
1. các index nên được partition giống như table (dùng cùng partition function). Khi đó index được gọi là aligned với table. Điều này không bắt buộc nhưng nên làm vì có nhiều lợi ích về sau này (index rebuild, switch in, switch out...).
2. Nếu đã tuân thủ theo điều 1 thì các index, và đương nhiên cả primary key, bắt buộc phải chứa partition key.

Trở lại vấn đề của bạn, có hai cách giải quyết:
(a) tạo bảng dùng partition; sau đó tạo unique non-clustered index trên trường Value_Key một cách bình thường, không partition. Lúc này index không aligned với table. Hoặc
(b) đưa thêm trường Date_Key vào primary key và partition bảng.
Với cách làm (a), nếu Value_Key là not null thì nó tương đương với primary key về mặt ràng buộc dữ liệu. Nếu bạn muốn đặt yêu cầu này lên cao nhất thì hãy dùng cách này. Tuy nhiên index này không aligned với table nên làm mất nhiều ưu điểm của partitioning. Chỉ cần 1 index không aligned với table là hỏng rồi.
Cách làm (b) rõ ràng không đáp ứng được yêu cầu duy nhất trên một mình trường Value_Key, chỉ duy nhất trong mỗi partition key. Nhưng nên nhớ khi truy vấn bảng partition luôn cần đưa partition key vào mệnh đề WHERE, để giúp tối ưu câu lệnh. Do đó bạn sẽ không gặp duplicate rows.

Với một người đang tìm hiểu để xây dựng hệ thống data warehouse như bạn, theo tôi bạn chưa cần áp dụng table partitioning, chưa kể đến những rắc rối kể trên của nó. Trong thời gian đầu nó cũng chưa phát huy gì nhiều tác dụng, và sau này bạn luôn có thể partition các bảng đã có.