萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 建立資料倉庫 (Data Warehouse) 入門:從新建資料庫到建表

資料倉庫本質上是為分析查詢優化的關聯式資料庫。在 SQL Server 中建立資料倉庫,起步動作與一般 OLTP 資料庫無異,核心差異在於後續的維度模型設計、ETL 流程與索引策略。本文示範最基礎的建庫建表操作。

SSMS 圖形化建立資料倉庫資料庫

  1. SSMS 連線實例,展開左側「物件總管」。
  2. 右鍵 資料庫 (Databases) → 新增資料庫 (New Database...)。
  3. 「一般」頁籤:
      • 資料庫名稱:如 DW_Sales。
      • 擁有者:保留預設或指定專用登入。
      • 資料庫檔案:建議資料檔 (.mdf) 與記錄檔 (.ldf) 放置於不同磁碟機 (如 D:\Data、L:\Log),初始大小依預估資料量設定 (如 10 GB),自動成長設為固定增量 (如 512 MB) 而非百分比。
      • 檔案群組:可新增 FG_Fact、FG_Dim 等檔案群組,將事實表與維度表分離存放,利於備份與效能調校。
  4. 「選項」頁籤:
      • 相容性層級:選最新版本 (如 SQL Server 2019 = 150)。
      • 復原模式:資料倉庫通常採 簡單 (Simple) 或 大量記錄,減少記錄檔膨脹;若需時間點還原則用 完整。
      • 自動關閉:False (避免頻繁啟動/關閉延遲)。
      • 自動建立/更新統計資料:True。
  5. 按「確定」完成建立。

T-SQL 腳本快速建立 (可納入版控)

CREATE DATABASE DW_Sales
ON PRIMARY
( NAME = 'DW_Sales_Data', FILENAME = 'D:\Data\DW_Sales.mdf', SIZE = 10GB, FILEGROWTH = 512MB ),
FILEGROUP FG_Fact
( NAME = 'DW_Sales_Fact', FILENAME = 'D:\Data\DW_Sales_Fact.ndf', SIZE = 50GB, FILEGROWTH = 1GB ),
FILEGROUP FG_Dim
( NAME = 'DW_Sales_Dim', FILENAME = 'D:\Data\DW_Sales_Dim.ndf', SIZE = 5GB, FILEGROWTH = 256MB )
LOG ON
( NAME = 'DW_Sales_Log', FILENAME = 'L:\Log\DW_Sales.ldf', SIZE = 10GB, FILEGROWTH = 512MB )
WITH RECOVERY SIMPLE;
GO

ALTER DATABASE DW_Sales SET AUTO_CREATE_STATISTICS ON, AUTO_UPDATE_STATISTICS ON, AUTO_CLOSE OFF;
GO

建立第一張維度表範例

USE DW_Sales;
GO
CREATE TABLE Dim_Date (
    DateKey     INT          NOT NULL PRIMARY KEY,  -- 格式 YYYYMMDD
    FullDate    DATE         NOT NULL,
    DayOfWeek   TINYINT      NOT NULL,
    MonthName   NVARCHAR(20) NOT NULL,
    Quarter     TINYINT      NOT NULL,
    Year        SMALLINT     NOT NULL,
    IsHoliday   BIT          NOT NULL DEFAULT 0
) ON FG_Dim;
GO

資料倉庫後續關鍵工作 (不在本文範圍,但必須規劃)

  • 星狀/雪花綱模型設計 (Kimball / Inmon 方法論)。
  • ETL/ELT 流程:SSIS、Azure Data Factory、存儲程序、PolyBase。
  • 分割資料表與分割索引 (大型事實表必備)。
  • 叢集欄存索引 (Columnstore Index) 加速分析查詢。
  • 資料品質、緩慢變維度 (SCD Type 1/2/3) 處理。
  • 安全性:資料列層級安全性 (RLS)、動態資料遮罩。

建立資料庫只是第一步,良好的架構設計與自動化 ETL 才是資料倉庫價值發揮的關鍵。

copyright © 萬盛學電腦網 all rights reserved