資料倉庫本質上是為分析查詢優化的關聯式資料庫。在 SQL Server 中建立資料倉庫,起步動作與一般 OLTP 資料庫無異,核心差異在於後續的維度模型設計、ETL 流程與索引策略。本文示範最基礎的建庫建表操作。
DW_Sales。FG_Fact、FG_Dim 等檔案群組,將事實表與維度表分離存放,利於備份與效能調校。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
建立資料庫只是第一步,良好的架構設計與自動化 ETL 才是資料倉庫價值發揮的關鍵。