This repo contains a simplified star for PostgreSQL with 4 tables (1 fact and 3 related dimensions) from Microsoft's AdventureWorksDW (more information here).
Adventure Works DW (DW for "Data Warehouse") comes in various flavors of Microsoft-related technologies: Transact SQL, SQL Server, and even as Power Bi projects. You can find PostgreSQL dumps out there on GitHub, but I was unable to make them work.
I gave up on trying to restore the whole database, because I just need it for small demonstration purposes. I got the idea of simplifying the model from this Medium article
I modified the scripts with the help of Chat GPT.
Things I've done:
- Told ChatGPT to translate the Transact SQL to PostgreSQL.
- Replaced money by float, then convert float to money in Postgres.
- Made table and column names lowercase instead of CamelCase (which is PostgreSQL standards).
- I modified the files by hand. I did not record them.
- This is what worked after a lot of pain and trials with other files.
For now, I created a PostgreSQL dump. I would like to create the same for SQLite, and maybe some other formats too.
The PostgreSQL directory contains a backup file, you can create a new database from it.
Another option is to use the create_tables.sql file to create the tables and the use the CSV files in the data directory to load the tables (separator is a pipe |).
You can query the database to answer analytical questions, such as these:
/* What products make us a higher income? */
SELECT
modelname,
englishdescription,
SUM(extendedamount) AS sales_amount
FROM
factinternetsales
JOIN dimproduct ON dimproduct.productkey = factinternetsales.productkey
GROUP BY
modelname,
englishdescription
ORDER BY sales_amount DESC
LIMIT 5| modelname | englishdescription | sales_amount |
|---|---|---|
| Mountain-200 | Serious back-country riding. Perfect for all levels of competition. Uses the same HL Frame as the Mountain-100. | 7929476.45 |
| Road-150 | This bike is ridden by race winners. Developed with the Adventure Works Cycles professional race team, it has a extremely light heat-treated aluminum frame, and steering that allows precision control. | 5549896.77 |
| Road-250 | Alluminum-alloy frame provides a light, stiff ride, whether you are racing in the velodrome or on a demanding club ride on country roads. | 4451258.23 |
| Touring-1000 | Travel in style and comfort. Designed for maximum comfort and safety. Wide gear range takes on all hills. High-tech aluminum alloy construction provides durability without added weight. | 2992007.85 |
| Road-350-W | Cross-train, race, or just socialize on a sleek, aerodynamic bike designed for a woman. Advanced seat technology provides comfort all day. | 1580219.71 |
/* What are our sales by shipping Month */
SELECT
englishmonthname,
SUM(extendedamount) AS sales_amount
FROM
factinternetsales
JOIN dimdate ON dimdate.datekey = factinternetsales.shipdatekey
GROUP BY
englishmonthname
ORDER BY sales_amount DESC| englishmonthname | sales_amount |
|---|---|
| December | 3237510.66 |
| November | 3092692.95 |
| June | 2848558.44 |
| October | 2767543.22 |
| August | 2670946.19 |
| July | 2512004.52 |
| September | 2487639.80 |
| May | 2087135.22 |
| January | 2070900.08 |
| March | 1934254.88 |
| April | 1904794.12 |
| February | 1744697.81 |