はじめに

ローカルの SQL Server だけで「DWHっぽい」データモデルを触ってみたいときに、さっと使えるサンプルがあると便利です。（たまに、Microsoft Fabric の Lakehouse や Warehouse を検証するときに、SQL Server からデータベースをコピーしたいとき、これらのダミーデータがあると良いかもしれませんね）

ここでは、以下のようなミニ DWH を一括で作る SQL スクリプトを遊んでみようと思います。

データベース：SalesStarDW
スキーマ：典型的な スター・スキーマ
- 日付ディメンション（DimDate）
- 顧客ディメンション（DimCustomer）
- 商品ディメンション（DimProduct）
- 店舗ディメンション（DimStore）
- 営業ディメンション（DimSalesperson）
- 売上ファクト（FactSales、計算列 SalesAmount 付き）
データ：
- 日付：2020-01-01〜2024-12-31
- 各ディメンション：30件ずつ
- ファクト：ランダムに 1,000 行生成

前半：概要と簡単な遊び方
後半：スクリプト全文
という構成にしています。

何が作られるか（ざっくり解説）

ディメンションテーブル

DimDate
- DateValue（自然キー）、DayOfWeekName、MonthName、CalendarYear、IsWeekend など
- 日付分析（年・月・曜日・平日/週末）がしやすい構造
DimCustomer
- CustomerSegment（Retail / SMB / Enterprise）
- City、Country、SignupDate、IsActive など
DimProduct
- Brand、Category、SubCategory
- UnitPrice（標準単価）、IsActive
DimStore
- StoreType（Retail / Outlet / Online）
- City、Region、Country、OpenDate、IsActive
DimSalesperson
- Title（Sales Rep / Senior Sales Rep / Sales Manager）
- Region、HireDate、IsActive

ファクトテーブル

FactSales は、典型的な売上ファクトテーブルです。

外部キー：DateKey、CustomerKey、ProductKey、StoreKey、SalespersonKey
明細情報：OrderNumber、OrderLineNumber、Quantity、UnitPrice、DiscountAmount
計算列：SalesAmount（Quantity * UnitPrice - DiscountAmount）

計算列 SalesAmount は PERSISTED なので、クエリ時に毎回式を書かなくてもそのまま合計を取ることができます。

どうやって遊ぶか（最低限のステップ）

1. スクリプトを実行する

SQL Server Management Studio (SSMS) や Azure Data Studio などで、後半の「サンプル SQL コード全体」をそのまま実行します。

SalesStarDW データベースが作成される
既存のテーブルがあれば DROP される
ディメンションとファクトが作成され、データが投入される

2. 行数の確認

本当に 1,000 行入っているか確認します。

USE SalesStarDW;
GO

SELECT COUNT(*) AS FactSalesRowCount
FROM dbo.FactSales;

3. 売上明細のサンプルを確認

ディメンションを JOIN して、どんなデータが入っているか簡単に確認します。

SELECT TOP 5
    f.SalesKey,
    d.DateValue,
    c.FullName       AS CustomerName,
    p.ProductName,
    st.StoreName,
    sp.FullName      AS SalespersonName,
    f.Quantity,
    f.UnitPrice,
    f.DiscountAmount,
    f.SalesAmount
FROM dbo.FactSales f
JOIN dbo.DimDate        d  ON f.DateKey        = d.DateKey
JOIN dbo.DimCustomer    c  ON f.CustomerKey    = c.CustomerKey
JOIN dbo.DimProduct     p  ON f.ProductKey     = p.ProductKey
JOIN dbo.DimStore       st ON f.StoreKey       = st.StoreKey
JOIN dbo.DimSalesperson sp ON f.SalespersonKey = sp.SalespersonKey;

4. 年別・国別の売上を集計してみる

簡単な集計クエリで、スター・スキーマの感覚を掴めます。

SELECT
    d.CalendarYear,
    st.Country,
    SUM(f.SalesAmount) AS TotalSales,
    COUNT(*)           AS LineCount
FROM dbo.FactSales f
JOIN dbo.DimDate  d  ON f.DateKey  = d.DateKey
JOIN dbo.DimStore st ON f.StoreKey = st.StoreKey
GROUP BY d.CalendarYear, st.Country
ORDER BY d.CalendarYear, st.Country;

実行例

サンプル SQL コード全体

以下が、今回利用しているスクリプト全文です。
そのまま貼り付けて実行すれば、SalesStarDW ミニ DWH が作成されます。

------------------------------------------------------------
-- 0. Create and select database
------------------------------------------------------------
IF DB_ID('SalesStarDW') IS NULL
    CREATE DATABASE SalesStarDW;
GO

USE SalesStarDW;
GO

------------------------------------------------------------
-- 1. Drop tables if you re-run the script
------------------------------------------------------------
IF OBJECT_ID('dbo.FactSales', 'U') IS NOT NULL DROP TABLE dbo.FactSales;
IF OBJECT_ID('dbo.DimSalesperson', 'U') IS NOT NULL DROP TABLE dbo.DimSalesperson;
IF OBJECT_ID('dbo.DimStore', 'U')       IS NOT NULL DROP TABLE dbo.DimStore;
IF OBJECT_ID('dbo.DimProduct', 'U')     IS NOT NULL DROP TABLE dbo.DimProduct;
IF OBJECT_ID('dbo.DimCustomer', 'U')    IS NOT NULL DROP TABLE dbo.DimCustomer;
IF OBJECT_ID('dbo.DimDate', 'U')        IS NOT NULL DROP TABLE dbo.DimDate;
GO

------------------------------------------------------------
-- 2. Dimension tables
------------------------------------------------------------

-- 2.1 Date dimension
CREATE TABLE dbo.DimDate (
    DateKey        INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    DateValue      DATE              NOT NULL,       -- natural key
    DayNumber      TINYINT           NOT NULL,       -- 1-31
    DayOfWeekName  NVARCHAR(20)      NOT NULL,
    MonthNumber    TINYINT           NOT NULL,       -- 1-12
    MonthName      NVARCHAR(20)      NOT NULL,
    QuarterNumber  TINYINT           NOT NULL,       -- 1-4
    CalendarYear   SMALLINT          NOT NULL,
    IsWeekend      BIT               NOT NULL
);
GO

CREATE UNIQUE INDEX UX_DimDate_DateValue
    ON dbo.DimDate(DateValue);
GO

-- 2.2 Customer dimension
CREATE TABLE dbo.DimCustomer (
    CustomerKey      INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    CustomerCode     NVARCHAR(20)      NOT NULL,
    FullName         NVARCHAR(100)     NOT NULL,
    CustomerSegment  NVARCHAR(20)      NOT NULL,  -- Retail / SMB / Enterprise
    City             NVARCHAR(50)      NOT NULL,
    Country          NVARCHAR(50)      NOT NULL,
    SignupDate       DATE              NOT NULL,
    IsActive         BIT               NOT NULL
);
GO

CREATE UNIQUE INDEX UX_DimCustomer_CustomerCode
    ON dbo.DimCustomer(CustomerCode);
GO

-- 2.3 Product dimension
CREATE TABLE dbo.DimProduct (
    ProductKey     INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ProductCode    NVARCHAR(20)      NOT NULL,
    ProductName    NVARCHAR(100)     NOT NULL,
    Brand          NVARCHAR(50)      NOT NULL,
    Category       NVARCHAR(50)      NOT NULL,
    SubCategory    NVARCHAR(50)      NULL,
    UnitPrice      DECIMAL(10,2)     NOT NULL,  -- << key column
    IsActive       BIT               NOT NULL
);
GO

CREATE UNIQUE INDEX UX_DimProduct_ProductCode
    ON dbo.DimProduct(ProductCode);
GO

-- 2.4 Store dimension
CREATE TABLE dbo.DimStore (
    StoreKey    INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    StoreCode   NVARCHAR(20)      NOT NULL,
    StoreName   NVARCHAR(100)     NOT NULL,
    StoreType   NVARCHAR(20)      NOT NULL,   -- Retail / Outlet / Online
    City        NVARCHAR(50)      NOT NULL,
    Region      NVARCHAR(50)      NULL,
    Country     NVARCHAR(50)      NOT NULL,
    OpenDate    DATE              NOT NULL,
    IsActive    BIT               NOT NULL
);
GO

CREATE UNIQUE INDEX UX_DimStore_StoreCode
    ON dbo.DimStore(StoreCode);
GO

-- 2.5 Salesperson dimension
CREATE TABLE dbo.DimSalesperson (
    SalespersonKey INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    EmployeeCode   NVARCHAR(20)      NOT NULL,
    FullName       NVARCHAR(100)     NOT NULL,
    Title          NVARCHAR(50)      NOT NULL,
    HireDate       DATE              NOT NULL,
    Region         NVARCHAR(50)      NOT NULL,
    IsActive       BIT               NOT NULL
);
GO

CREATE UNIQUE INDEX UX_DimSalesperson_EmployeeCode
    ON dbo.DimSalesperson(EmployeeCode);
GO

------------------------------------------------------------
-- 3. Fact table
------------------------------------------------------------
CREATE TABLE dbo.FactSales (
    SalesKey        BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    DateKey         INT           NOT NULL,
    CustomerKey     INT           NOT NULL,
    ProductKey      INT           NOT NULL,
    StoreKey        INT           NOT NULL,
    SalespersonKey  INT           NULL,
    OrderNumber     NVARCHAR(20)  NOT NULL,
    OrderLineNumber TINYINT       NOT NULL,
    Quantity        INT           NOT NULL,
    UnitPrice       DECIMAL(10,2) NOT NULL,
    DiscountAmount  DECIMAL(10,2) NOT NULL 
        CONSTRAINT DF_FactSales_Discount DEFAULT (0),
    SalesAmount     AS (Quantity * UnitPrice - DiscountAmount) PERSISTED
);
GO

------------------------------------------------------------
-- 4. Relationships (Foreign Keys + Indexes)
------------------------------------------------------------
ALTER TABLE dbo.FactSales
    ADD CONSTRAINT FK_FactSales_DimDate
        FOREIGN KEY (DateKey) REFERENCES dbo.DimDate(DateKey);

ALTER TABLE dbo.FactSales
    ADD CONSTRAINT FK_FactSales_DimCustomer
        FOREIGN KEY (CustomerKey) REFERENCES dbo.DimCustomer(CustomerKey);

ALTER TABLE dbo.FactSales
    ADD CONSTRAINT FK_FactSales_DimProduct
        FOREIGN KEY (ProductKey) REFERENCES dbo.DimProduct(ProductKey);

ALTER TABLE dbo.FactSales
    ADD CONSTRAINT FK_FactSales_DimStore
        FOREIGN KEY (StoreKey) REFERENCES dbo.DimStore(StoreKey);

ALTER TABLE dbo.FactSales
    ADD CONSTRAINT FK_FactSales_DimSalesperson
        FOREIGN KEY (SalespersonKey) REFERENCES dbo.DimSalesperson(SalespersonKey);
GO

CREATE INDEX IX_FactSales_DateKey        ON dbo.FactSales(DateKey);
CREATE INDEX IX_FactSales_CustomerKey    ON dbo.FactSales(CustomerKey);
CREATE INDEX IX_FactSales_ProductKey     ON dbo.FactSales(ProductKey);
CREATE INDEX IX_FactSales_StoreKey       ON dbo.FactSales(StoreKey);
CREATE INDEX IX_FactSales_SalespersonKey ON dbo.FactSales(SalespersonKey);
GO

------------------------------------------------------------
-- 5. Populate DimDate : full 5-year range
--    2020-01-01 to 2024-12-31
------------------------------------------------------------
DECLARE @StartDate DATE = '2020-01-01';
DECLARE @EndDate   DATE = '2024-12-31';
DECLARE @d DATE = @StartDate;

WHILE @d <= @EndDate
BEGIN
    INSERT INTO dbo.DimDate
        (DateValue,
         DayNumber,
         DayOfWeekName,
         MonthNumber,
         MonthName,
         QuarterNumber,
         CalendarYear,
         IsWeekend)
    VALUES
        (@d,
         DAY(@d),
         DATENAME(WEEKDAY, @d),
         MONTH(@d),
         DATENAME(MONTH, @d),
         DATEPART(QUARTER, @d),
         YEAR(@d),
         CASE WHEN DATENAME(WEEKDAY, @d) IN (N'Saturday', N'Sunday') THEN 1 ELSE 0 END);

    SET @d = DATEADD(DAY, 1, @d);
END;
GO



------------------------------------------------------------
-- 6. Populate DimCustomer : 30 customers
------------------------------------------------------------
;WITH N AS (
    SELECT TOP (30)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM master.sys.objects
)
INSERT INTO dbo.DimCustomer
    (CustomerCode, FullName, CustomerSegment, City, Country, SignupDate, IsActive)
SELECT
    'CUST-' + RIGHT('000' + CAST(n AS VARCHAR(3)), 3)               AS CustomerCode,
    'Customer ' + CAST(n AS VARCHAR(3))                              AS FullName,
    CASE (n % 3)
        WHEN 1 THEN 'Retail'
        WHEN 2 THEN 'SMB'
        ELSE        'Enterprise'
    END                                                             AS CustomerSegment,
    CASE (n % 5)
        WHEN 1 THEN 'Tokyo'
        WHEN 2 THEN 'Osaka'
        WHEN 3 THEN 'Nagoya'
        WHEN 4 THEN 'Jakarta'
        ELSE        'Singapore'
    END                                                             AS City,
    CASE (n % 5)
        WHEN 1 THEN 'Japan'
        WHEN 2 THEN 'Japan'
        WHEN 3 THEN 'Japan'
        WHEN 4 THEN 'Indonesia'
        ELSE        'Singapore'
    END                                                             AS Country,
    DATEADD(DAY, n, '2018-01-01')                                   AS SignupDate,
    CASE WHEN n % 10 = 0 THEN 0 ELSE 1 END                          AS IsActive
FROM N;
GO


------------------------------------------------------------
-- 7. Populate DimProduct : 30 products
------------------------------------------------------------
;WITH N AS (
    SELECT TOP (30)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM master.sys.objects
)
INSERT INTO dbo.DimProduct
    (ProductCode, ProductName, Brand, Category, SubCategory, UnitPrice, IsActive)
SELECT
    'PROD-' + RIGHT('000' + CAST(n AS VARCHAR(3)), 3)               AS ProductCode,
    'Product ' + CAST(n AS VARCHAR(3))                              AS ProductName,
    CASE (n % 4)
        WHEN 1 THEN 'Contoso'
        WHEN 2 THEN 'Fabrikam'
        WHEN 3 THEN 'AdventureWorks'
        ELSE        'Tailspin'
    END                                                             AS Brand,
    CASE (n % 3)
        WHEN 1 THEN 'Electronics'
        WHEN 2 THEN 'Furniture'
        ELSE        'Accessories'
    END                                                             AS Category,
    CASE
        WHEN (n % 3) = 1 THEN
            CASE WHEN (n % 2) = 0 THEN 'Laptop'   ELSE 'Monitor'  END
        WHEN (n % 3) = 2 THEN
            CASE WHEN (n % 2) = 0 THEN 'Desk'     ELSE 'Chair'    END
        ELSE
            CASE WHEN (n % 2) = 0 THEN 'Mouse'    ELSE 'Keyboard' END
    END                                                             AS SubCategory,
    CAST(ROUND(50.0 + (n * 15.0), 2) AS DECIMAL(10,2))              AS UnitPrice,
    CASE WHEN n % 12 = 0 THEN 0 ELSE 1 END                          AS IsActive
FROM N;
GO


------------------------------------------------------------
-- 8. Populate DimStore : 30 stores
------------------------------------------------------------
;WITH N AS (
    SELECT TOP (30)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM master.sys.objects
)
INSERT INTO dbo.DimStore
    (StoreCode, StoreName, StoreType, City, Region, Country, OpenDate, IsActive)
SELECT
    'STORE-' + RIGHT('000' + CAST(n AS VARCHAR(3)), 3)              AS StoreCode,
    'Store ' + CAST(n AS VARCHAR(3))                                AS StoreName,
    CASE (n % 3)
        WHEN 1 THEN 'Retail'
        WHEN 2 THEN 'Outlet'
        ELSE        'Online'
    END                                                             AS StoreType,
    CASE (n % 6)
        WHEN 1 THEN 'Tokyo'
        WHEN 2 THEN 'Osaka'
        WHEN 3 THEN 'Nagoya'
        WHEN 4 THEN 'Fukuoka'
        WHEN 5 THEN 'Jakarta'
        ELSE        'Singapore'
    END                                                             AS City,
    CASE (n % 6)
        WHEN 1 THEN 'Kanto'
        WHEN 2 THEN 'Kansai'
        WHEN 3 THEN 'Tokai'
        WHEN 4 THEN 'Kyushu'
        WHEN 5 THEN 'Java'
        ELSE        'Singapore Region'
    END                                                             AS Region,
    CASE (n % 6)
        WHEN 5 THEN 'Indonesia'
        WHEN 0 THEN 'Singapore'
        ELSE        'Japan'
    END                                                             AS Country,
    DATEADD(DAY, n * 30, '2015-01-01')                              AS OpenDate,
    CASE WHEN n % 15 = 0 THEN 0 ELSE 1 END                          AS IsActive
FROM N;
GO


------------------------------------------------------------
-- 9. Populate DimSalesperson : 30 salespeople
------------------------------------------------------------
;WITH N AS (
    SELECT TOP (30)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM master.sys.objects
)
INSERT INTO dbo.DimSalesperson
    (EmployeeCode, FullName, Title, HireDate, Region, IsActive)
SELECT
    'EMP-' + RIGHT('000' + CAST(n AS VARCHAR(3)), 3)                AS EmployeeCode,
    'Salesperson ' + CAST(n AS VARCHAR(3))                          AS FullName,
    CASE
        WHEN n % 10 = 0 THEN 'Sales Manager'
        WHEN n % 5  = 0 THEN 'Senior Sales Rep'
        ELSE              'Sales Rep'
    END                                                             AS Title,
    DATEADD(DAY, n * 20, '2016-01-01')                              AS HireDate,
    CASE (n % 5)
        WHEN 1 THEN 'Kanto'
        WHEN 2 THEN 'Kansai'
        WHEN 3 THEN 'Tokai'
        WHEN 4 THEN 'Indonesia'
        ELSE        'Singapore'
    END                                                             AS Region,
    CASE WHEN n % 18 = 0 THEN 0 ELSE 1 END                          AS IsActive
FROM N;
GO


------------------------------------------------------------
-- 10. Populate FactSales : 1,000 order lines
--     Spread across 5-year date range and all dimensions
------------------------------------------------------------

;WITH N AS (
    SELECT TOP (1000)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM master.sys.objects s1
    CROSS JOIN master.sys.objects s2
)
INSERT INTO dbo.FactSales
    (DateKey,
     CustomerKey,
     ProductKey,
     StoreKey,
     SalespersonKey,
     OrderNumber,
     OrderLineNumber,
     Quantity,
     UnitPrice,
     DiscountAmount)
SELECT
    d.DateKey,
    c.CustomerKey,
    p.ProductKey,
    st.StoreKey,
    sp.SalespersonKey,
    'SO-' + RIGHT('000000' + CAST(((n - 1) / 3) + 1 AS VARCHAR(6)), 6)  AS OrderNumber,
    CAST(((n - 1) % 3) + 1 AS TINYINT)                                  AS OrderLineNumber,
    ((n - 1) % 10) + 1                                                  AS Quantity,    -- 1–10
    p.UnitPrice                                                         AS UnitPrice,   -- from DimProduct
    CASE WHEN n % 7 = 0
         THEN CAST(ROUND(p.UnitPrice * 0.10, 2) AS DECIMAL(10,2))       -- ~10% discount
         ELSE 0.00
    END                                                                 AS DiscountAmount
FROM N
CROSS APPLY (
    SELECT TOP 1 DateKey
    FROM dbo.DimDate
    ORDER BY NEWID()
) AS d
CROSS APPLY (
    SELECT TOP 1 CustomerKey
    FROM dbo.DimCustomer
    ORDER BY NEWID()
) AS c
CROSS APPLY (
    -- *** THIS IS THE IMPORTANT FIX ***
    -- Select BOTH ProductKey and UnitPrice so alias p exposes p.UnitPrice
    SELECT TOP 1 ProductKey, UnitPrice
    FROM dbo.DimProduct
    ORDER BY NEWID()
) AS p
CROSS APPLY (
    SELECT TOP 1 StoreKey
    FROM dbo.DimStore
    ORDER BY NEWID()
) AS st
CROSS APPLY (
    SELECT TOP 1 SalespersonKey
    FROM dbo.DimSalesperson
    ORDER BY NEWID()
) AS sp;
GO


------------------------------------------------------------
-- 11. Quick sanity checks (optional)
------------------------------------------------------------

SELECT COUNT(*) AS FactSalesRowCount FROM dbo.FactSales;

SELECT TOP 5
    f.SalesKey,
    d.DateValue,
    c.FullName       AS CustomerName,
    p.ProductName,
    st.StoreName,
    sp.FullName      AS SalespersonName,
    f.Quantity,
    f.UnitPrice,
    f.DiscountAmount,
    f.SalesAmount
FROM dbo.FactSales f
JOIN dbo.DimDate        d  ON f.DateKey        = d.DateKey
JOIN dbo.DimCustomer    c  ON f.CustomerKey    = c.CustomerKey
JOIN dbo.DimProduct     p  ON f.ProductKey     = p.ProductKey
JOIN dbo.DimStore       st ON f.StoreKey       = st.StoreKey
JOIN dbo.DimSalesperson sp ON f.SalespersonKey = sp.SalespersonKey;


SELECT
    d.CalendarYear,
    st.Country,
    SUM(f.SalesAmount) AS TotalSales,
    COUNT(*)           AS LineCount
FROM dbo.FactSales f
JOIN dbo.DimDate  d  ON f.DateKey  = d.DateKey
JOIN dbo.DimStore st ON f.StoreKey = st.StoreKey
GROUP BY d.CalendarYear, st.Country
ORDER BY d.CalendarYear, st.Country;

SELECT TOP 3 * FROM dbo.DimCustomer;
SELECT TOP 3 * FROM dbo.DimDate;
SELECT TOP 3 * FROM dbo.DimProduct;
SELECT TOP 3 * FROM dbo.DimSalesperson;
SELECT TOP 3 * FROM dbo.DimStore;
SELECT TOP 3 * FROM dbo.FactSales