Using Date Dimensions

There are 2 scripts at the bottom of this page which can be used to:

• Build a table of all dates between 01-Jan-2000 and the 31-Dec-2100

• Create a view that we can use to select date groups i.e. this week, last week etc.

 Each of these scripts can be extended if needed.

You can run these scripts in your DB and then use in piDashboard.

Select the objects that you want the user to be able to use in the date dimension table, in the following screenshot you can see that we’ve added every column.

Create a join between this table and any other table that you want to query using dates.

In the following example, we’ve created a join between a field called ‘Course_start_date’ to the field called ‘Date’ in the calendar table.

I’ve joined Course_start_date – but that could be anything – and you can add multiple alias’s if you want to join the date dimension to multiple date columns.  (You will need to name your date objects carefully then)

So I can now use the object as I see fit.

I can then outer join this so I get ‘Missing dates’

My data isn’t that great! So I only have students starting in two months – but I want to show all.

I make the join an outer join (mine is RIGHT)

You can use the objects to do all manner of comparisons.

To get the difference between two date in MS SQL – I’ll be honest I’ll google it (I google everything to do with SQL)

This gives me this.

In my DB – I’m going to find out how many days it took a student to get a disciplinary

I create a manual object

So my calculation is like

Note: I’ve done day, but it would be soooo easy to do month, weeks, years etc.

Now let’s plot the bad behaviuor

So we can now see what’s happening

I don’t have time in application in my data set – but I can use this now for average time to be bad.

I can now use this in my dashboard

People doing animal care are pretty bad!! (Or the tutor likes giving detentions)

To do a cumulative count

And create the object with Cumulative selected ( I also name the object to make it clear)

I’ve used course – rather than dates because my data is a bit rubbish

Now split that by other dimensions

This will all work with your progression table (the snapshot in time). You can add the date dimension link to it, then select a date.

To show which step followed which step sequentially – we’d need to create a view.

This would group the data in the progression table by student, show this min max date for each step.  Constructed as a union query.  Then we can calculate days between each step.

This would be much simpler to build with you, probably a couple of hours work.

I have include the view script in this message as it also allows us to select date ranges nicely.

I Add date short name

Then join my date using a between

Now I can use this a  category object

Now in my dashboard I can select any of those periods