Create my own data warehouse

I had the time to create a small example data warehouse.

As sourcing system, I used the Northwind database. This is an example database that is provided by Microsoft. It is a really nice database: about 13 tables and tables loaded with 3- 2200 records.

I created a set of extraction scripts on this. This creates a set of 13 files, each one a download of one of the tables. These files are subsequently loaded into a staging database – again an upload of the previously created extraction file. This staging database is subsequently loaded into a datavault data warehouse. For a ddl see here.

In total, we have next set of scripts

  • 13 scripts to dump the 13 tables of the Northwind database
  • 13 scripts to load the files into a staging database
  • 10 scripts to load the hubs in the datavault database
  • 7 scripts to load the links in the datavault database
  • 9 scripts to load the satellites in the datavault database
These scripts can be started from a dashboard that is stored online. See here .
It was simply fun to create this. For the techies: everything is written in php and the database are stored in a MySQL database.