Crossjoin Date and Time tables

[IndyDoF]

Hello,

I am trying to provide a pivot table showing hourly availability of staff. My data model contains a date table and an hours table.

Example of what i want to acheive:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Hour[/TD]
[TD]Staff[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]8[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]9[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]10[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

Staff is just the number of people on shift multiplied by the number that are available for that hour.

My fact table looks like the following (this is linked to my dates table):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]7 Shift[/TD]
[TD]8 Shift[/TD]
[TD]9 Shift[/TD]
[/TR]
[TR]
[TD]1/1/2013[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2/1/2013[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3/1/2013[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]4/1/2013[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5/1/2013[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]6/1/2013[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]7/1/2013[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8/1/2013[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9/1/2013[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Now, I have created a reference table to show what shifts work what hours (including assigned lunch times), this is linked to my hours table. Looking like this (but with all 24hours of the day in it):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Hour[/TD]
[TD]7 Shift[/TD]
[TD]8 Shift[/TD]
[TD]9 Shift[/TD]
[/TR]
[TR]
[TD]7:00:00AM[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8:00:00AM[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9:00:00AM[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]12:00:00PM[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1:00:00PM[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Now for the life of me I cant seem to be able to write a measure to pull these together. As the hour and date tables dont have a relationship and the shift table and fact table dont have a relationship.

I have tried to write a crossjoin but cant find any information on referencing which column that I want to use in the calculation formula. I feel that this should be an easy problem to solve but at the moment it is doing my head in. Does anyone have any suggestions on what would be the best approach for this?

 

[Jacob Barnett]

At the risk of sounding like a broken record, I think the complexity of what you are trying to do is 100% driven by the fact that the layout of your 'fact' table is 'wrong'. Below are details of a solution that is crazy simple if you can 'unpivot' your fact table either on or before import.

The reason for this is that I would absolutely have a 'Shift' dimension in my model but for to create relationships the Shift information in the Fact table to be in a single column.

My solution is based on a fact table that looks like:

At this point I also did something similar with your reference table so I could relate both to a table call 'Shift' which is just the names of the different shifts. I created the date table which is linked to the fact table and also an hour table which is linked to the reference table. The key from here is using the 'Ones and Zeros' in the reference table to mulitply by the Employees working at that time to deliver the answer. You'll need 2 measures, one to multiply the number of employees by 'working' and another to Iterate them by Shift:

[Raw Employees] =sum(Fact[Employees])*sum(Reference[Working])

[Iterated Employees] =SUMX(Fact,[Raw Employees])

This does the job although PowerPivot will try and tell you that you are missing a relationship - this is partly true in that your fact table isn't related to your hours table at all but the relationship between the hours and the reference table along with the iteration gives the correct answer. I posted my 2 minute workbook here:

https://docs.google.com/file/d/0Bz5yMU2oooW2UU5rX3dsMGI4c28/edit?usp=sharing

If you want to discuss methods for getting the fact table into this format post back what kind of import it is and I may be able to advise!

Jacob

 

[IndyDoF]

Thank you very much, this makes sense and makes everything much more manageable. The table is simply an import from an excel spreadsheet I am provided by the Team Leader. I am thinking I can just create a VBA script to move the data in to the correct format unless there is a better way to do it?

 

[Jacob Barnett]

Nope, VBA is always my choice when it comes to flattening Excel data.

There is a techinique documented here: http://www.contextures.com/PowerPivot-Identical-Excel-Files.html that enables you to access the SQL editor for an Excel import so maybe that's another avenue to explore. Myguess is that the UNPIVOT function might not work but there is probably a way to get it done!