Adding a fact table with start date and end date to a multidimensional cube

0
votes

I have a fact table with payroll data and contains columns such employee id, dollars, start date & end date (pay period). So the granularity is not at the daily level. How can we add this fact table to my cube and link to date dimension? I have a date a typical date dimension in the cube with date, month, quarter and year.

note - the start and end date do not always fall on the sames days of the calendar month. I know it is terrible idea to somehow "convert" the grain to daily level by diving the dollar amounts by the number of days between start date and end date but i can't figure out another/better option.

1
ssasmdx
Which questions is the cube intended to answer? – FrankPl
What do you want to measure? – BI Dude
thank you, the users would like see dollars and hours side by side. Hours come from another time entry system where the granularity is daily. They would like to see how much they have paid to their staff over a period of x months etc. – sam

1 Answers

0
votes

As explained in the documentation, a relationship of a fact table/measure group to a dimension need not point to the key attribute/column of the dimension, it may point to any attribute (like the month attribute in your case). Then, you only can use the attribute that you linked to and attributes which have direct or indirect references from it. If you would have a reference from month to quarter and from quarter to year in your dimension, then the measures from your measure group referencing the month attribute can use quarter or year. But they cannot use days.