SQL Approach: Sample Sales Data Warehouse

2010-04-02

Sometimes you may need a simple small data warehouse only for test or learning purposes.

Having this goal in mind I wrote a short script that create a simple star schema:

Products (3 products with price attribute)

Customers (3 customers with city attribute)

Calendar (year-quarter-month with 6 years starting 3 years back)

Sales (random combinations product-customer-date and quantity/value)

Due to the small amount of data, a single query generates all combinations and then filter out some of them using a random number.

You'll obtain a different set of combinations and quantities each time you run the script.

SELECT

    T.CalendarDate,

    T.Customer,

    T.Product,

    T.Quantity,

    T.Quantity * P.ProductPrice Amount

FROM

    dbo.Products P

        INNER JOIN

    (--GENERATE ALL COMBINATIONS

        SELECT

            CalendarDate,

            C.Customer,

            P.Product,

            ROUND(RAND(CHECKSUM(NEWID())) * 9, 0) + 1 Quantity,

            RAND(CHECKSUM(NEWID())) Pick

        FROM

            dbo.Customers C

                CROSS JOIN

            dbo.Products P

                CROSS JOIN

            dbo.Calendar

        WHERE

            CalendarDateName < GETDATE()

    ) T ON

        T.Product = P.Product

WHERE

    T.Pick < .3 --REMOVE SOME COMBINATIONS

The RAND function generates random numbers. To initialize it with different integer values we can get the CHECKSUM of NEWID.

You could use the following script as a starting point for your experiments.

SQL Approach