Bike Tour Business Intelligence: Part 6 [Building the Fact Table]

At this stage the BI database contains the Date, Time and Location Dimension tables as well as a staging table with all the points captured by my GPS. This post covers creating and populating the Fact table, as well as running some queries over the dataset.

To recap, the star schema design has three Dimension tables all related to the Fact table, as shown below:

Star schema GPX

Creating the Fact Table

The Fact table creation flow involves two steps – the creation and population of the Fact table itself, followed by the creation of a clustered index (on all key columns) and the foreign key relationships to the dimension tables.


The SQL SELECT .. INTO query makes it easy to create and populate the Fact table:

With some additional Execution SQL Tasks to prepare the database for the data load and to clear down staging tables, the final SSIS workflow is complete (click to view the full workflow):


Click to see the workflow in detail

Running it against all my GPX files results in a Fact table of just over 350,000 rows.

Some sample queries

With all the data loaded it’s hard to resist writing some queries…. which I did, and then promptly found some outlying data points. Cycling in the Czech Republic was not a highlight and we took a shortcut through Prague by train. As I still had my GPS recording, it captured the journey leaving a couple of 50km+ segments which skew the data. I added a filter to the Fact table generation query so that only points under 10km apart are imported, ensuring only data generated by cycling is available for analysis.

What days did we cycle the farthest?


What’s the longest daily ride per country?


Did we ride further in the morning morning or the afternoon?


Which day of week did we have the highest moving average speed (assume moving is speed > 5kmh)?

Answer…. none in particular!


Summary so far

The DW database now contains all the points recorded by my GPS – next up I will be creating an Analysis Services Cube and performing some analytics over the dataset.

This entry was posted in SQL Server, SSIS. Bookmark the permalink.

2 Responses to Bike Tour Business Intelligence: Part 6 [Building the Fact Table]

Leave a Reply

Your email address will not be published. Required fields are marked *