關於資料倉儲的設計！

下面以SQLServer2000和Analysis Service為例，並結合以前的專案經驗，把相關的知識和大家一起分享探討！在公司時用英文寫的，懶得做翻譯了！

Data Warehouse Concept

A data warehouse is a subject-oriented, integrated, nonvolatile, time-variant collection of data designed to support management DSS needs.

1、Subject-Oriented

Subject-Oriented data is organized around major subject areas of an enterprise and is useful for an enterprise-wide understanding of those subjects. For Example, a banking operational system maintains independent records of customer savings, loans, and other transactions. A warehouse pulls this independent data together to provide financial information.
The data from diverse sources is transformed so that it is consistent and meaningful for the warehouse.
So the main work is around the fact table.
1.1 Original data from data source
1.2 Dimension
1.3 Measurement
1.4 dimension granularity
1.5 Star model or snowflake model
1.6 OLAP model

2、Integrated

Data on a given subject is integrated.
In many organizations, data resides in diverse independent systems, making it difficult to integrate the information into a single set of meaningful data from analysis. A key characteristic of a warehouse is that data is completely consolidated or integrated. Data is structured in a globally accepted manner, even when the underlying source data is structured differently (conforming dimension). Integration and transformation processes can be time-consuming and costly. It requires commitment from every part of the organization, particularly top-level managers who make the decisions and allocate resources and funds.
So the main work may be like :
2.1 Universal dimension from different department.
2.2 Dimension design include NULL value or violate constraint
2.3 Define mid-exchange table 2.4 But we didn’t control the data quality caused by man-made

3、Nonvolatile

Typically, data in the data warehouse is read-only (less volatile than operational systems). Data is loaded into the data warehouse for the first-time load, and then refreshed regularly. Warehouse data is accessed by business users. Warehouse operations typically involve:
Loading the initial set of warehouse data (often called the first-time load)
Refreshing the data regularly (called the refresh cycle)
So the main work is around:
3.1 DTS design and schedule
3.2 The dimension data is incremental, it is to say that they can be inserted and updated, but can’t be deleted. And dimension data must be unique.
3.3 The original or fact data is incremental, but according to requirement, it can be updated or deleted under the control. The full process is danger and impossible when the data volume is too huge.

4、Time-Variant

Warehouse data is by nature historical; data is retained for a long time, from two to ten years, compared with one to three months of data for a typical operational system. The data allows for analysis of past and present trends, and for forecasting, using what-if scenarios.
Base the Time-Variant and data volume, we must consider :
4.1 The design about the database4.2 The design about the table
4.3 The OLAP increment

資料庫設計，主要針對SQLServer2000而言，包括以下方面：

資料庫設計
檔案組設計
歷史資料表和當前資料表設計
分割槽表設計
資料庫連結使用
日誌表
增量資料抽取
維度資料抽取
原始資料抽取
日結資料處理
OLAP的增量處理和分割槽
資料庫調優

資料庫設計

資料庫一般的分類方式有：
按照業務來分(財務系統和銷售系統)
按照處理階段來分(原始資料和日結資料)
按照資料儲存時間來分(當前資料和歷史資料)

當然這些分類標準不是一成不變的也可以交叉分類，而且不同的關聯式資料庫也不一致，如Oracle可以使用不同資料塊大小的表空間儲存不同資料，SQLServer和Sybase則採用不同的資料庫實現對資料的儲存。
建議資料庫的分類按照不同的處理階段進行資料儲存，可以方便的進行資料庫的備份和管理工作。如
dataware_org 儲存ODS層資料，保留一定期限的原始資料
dataware_fact 儲存日結資料，可以較長時間的保留系統資料。
dataware_dim 儲存維度基礎資料。

待續........

關於檔案組的設計

資料庫可以按照以下幾種情況進行檔案組設計(其實同上，關鍵如何分類更加合理)：
1、按照業務資料來源分類
2、按照ETL處理過程分類
3、按照資料的儲存週期分類(歷史資料還是臨時資料)
4、按照資料的物理儲存型別分類(即索引還是資料)

個人建議，採用按照物理儲存型別和資料儲存週期進行分庫，如
歷史資料檔案組
臨時表資料檔案組
索引檔案組

關於歷史資料和臨時資料的分開處理

眾所周知，對於大資料量的資料儲存任何資料庫都與遭遇效能瓶頸。
因此建議對於大資料量的表採取分表處理：即將資料區分為臨時資料和歷史資料分開儲存

儘管可能會帶來一些維護和處理上的不便，但是顯而易見可以提升系統的效能。臨時表中少量的資料可以有效地進行日結等處理，臨時表資料需要進行定期地進行資料的轉移工作。
還有一個更大的好處是，臨時表和歷史表放在不同的檔案組或者資料庫中，可以減少系統的IO衝突和備份週期的制定.

關於分月表的問題

首先SQLServer在資料處理中存在效能問題，當一張表資料超過1000萬以上時，其查詢更新刪除的效率顯著降低，因此每個資料表的資料量要控制在一 定範圍內；其次SQLServer不支援分割槽處理。但是SQLServer2000提供了一種類似分割槽的解決辦法，採用分月表形式(當然也可以按照其他分 類)

即採用UNION的形式將各個相同表結構的表合併起來，作為一個完整的表來使用。當然這種檢視仍存在一定的效能問題和限制(以後會逐步發散開來)
例如：
create view v_fact_table as
select * from t_fact_table_200601 union all
select * from t_fact_table_200602 union all
select * from t_fact_table_200603
每個月自動產生一張 t_fact_table_YYYYMM資料表，然後動態更新v_fact_table檢視。

資料庫連結
連結伺服器配置允許 Microsoft® SQL Server™ 對其它伺服器上的 OLE DB 資料來源執行命令。連結伺服器具有以下優點：
遠端伺服器訪問。
對整個企業內的異類資料來源執行分散式查詢、更新、命令和事務的能力。
能夠以相似的方式確定不同的資料來源。
連結伺服器有以下兩種形式：
SELECT * FROM LinkedDatabase..usename.table
SELECT * FROM OPENQUERY(LinkedDatabase, 'SELECT * FROM table')
兩種各有優缺點
第一種寫法更加清晰，但有時候受限制比較多
第二種寫法更加通用一些，甚至可以執行遠端儲存過程

關於控制表和日誌表
1、監控維度資料的抽取和完成狀態
2、監控原始業務資料的抽取和完成狀態(包括時間點增量幅度的控制)
3、監控事實資料的執行和處理狀態(包括時間點增量幅度的控制)
4、監控OLAP增量處理的狀態和時間點
ETL部分——關於維度抽取

1、普通維度的抽取和處理
2、父子維度的抽取和處理
3、雪花維度的抽取和處理

關於維度的處理方式，主要包括三種方法
1、完全覆蓋法，即只保留最後一次更新的記錄
2、全歷史記錄法，即採用替代鍵的方式對每次發生變更的記錄進行記錄，同時對此次業務資料的相應維度進行替換。
3、記錄最新紀錄及上一次歷史，即只保留當前和上次的更新記錄，前兩種的這種策略。

通常情況下，對於維度不敏感的情況下采用第一種方式比較簡單易行
第二種方法則相對比較複雜，對於系統處理的要求也比較高

ETL部分——關於原始業務資料抽取

主要是採取增量抽取的方式，此外還要考慮抽取對原業務系統的效能影響

通常的處理原則
減少每次資料抽取的時間和事務的大小，減輕資料抽取時對業務系統的效能影響。

ETL部分——關於資料日結的處理

主要是採取增量處理的方式，此外還要考慮處理時對系統效能的影響

通常的處理原則
First get begin time from fact table or original table
Then get end time from original table, it need to be under the control by loglimit
Loop between the begin time and end time
Modify the log table status

OLAP的設計和處理

通常情況下對於大資料量的CUBE採用分割槽形式
對於CUBE處理而言，通常情況採用指令碼形式，以方便資料的增量處理和CUBE分割槽的融合

分割槽和增量的主要原因
全量資料重新整理對系統效能影響很大
可以對分割槽進行區域性處理，而不影響整個CUBE

關於資料倉儲效能的最佳化主要包括：

1、定期進行資料的轉移和清除工作
2、定期實現對資料庫日誌的收縮，尤其是大事務的處理之後
3、定期進行資料庫索引的重建工作。

監控：
定期透過Performance效能監視器收集資料庫伺服器的CPU，記憶體，硬碟統計資訊
定期分析DTS的日誌資訊
定期分析Windows的日誌資訊

關於備份

OLAP的備份
OLAP是採用獨佔式處理方式的，備份時不允許CUBE的處理，因此要合理的進行OLAP的備份和資料處理的關係。

指令碼：
@echo off
rem save database,
rem switch to the path of backup command
e:
cd e:Microsoft Analysis ServicesBin
msmdarch /a 機器名 "c:MSSQLCUBE" "BIOLAP" "F:BIBACKUPCUBEBIOLAP.CAB"
備份工作由Windows作業系統進行排程或者SQLServer均可

關於資料庫的備份
資料庫的備份也會影響系統的正常執行，因此也需要進行合理的排程工作任務
備份的策略建議是
1、2、3、4、5、6採用增量備份
7採用全量備份

沒心事好好整理，其實可以擴充套件的東西很多......

也希望大家提出寶貴意見！