June 23, 2009 at 5:31 pm
Hello AS gurus!
I have a measure GrossBusinessHours that is defined at day grain. Contractor is expected to work 8 hours a day, 40 hours a week and It is de-normalized into a fact table ContractorsCharges where it used for some other calculations. Fact table is at chargeID grain and is the source for my SSAS cube.
ChargeID ChargeDate GrossBusinessHr WorkedHours HoursType WeekName ContractorName
10 6/1/09 8 3 Project1 Week1, 09 Andy
20 6/1/09 8 1 Project2 Week1, 09 Andy
30 6/2/09 8 4 Project1 Week1, 09 Andy
40 6/4/09 8 6 Project2 Week1, 09 Andy
if I just aggregate, let's say by week in which my ChargeDates fit, I end up with GrossBusinessHours being greater or less then what I expect to see. In data sample above I would end up with 16 hours for the week which is wrong, I need to display 40 hours
So at week level I need to show this:
ContractorName GrossBusinessHours WorkedHours
Andy 40 14
How do I need to define GrossBusinessHours measure? I understand it should be semi-additive but how exactly. I played with different settings with no success.
Please help!
Thanks a lot in advance!
Peter.
June 24, 2009 at 10:03 am
I changed the grain of my fact table - it is at day level now. I bit closer to what I need. I am trying to figure out how to dynamically calculate number of days in time period. It looks like I need to write MDX expression and define my GrossBusinessHrs as calculated measure.
I am not familiar with MDX yet.
Could anyone suggest a simple tutorial to start with and give me a query sample to start with.
Thanks!
Peter.
June 27, 2009 at 6:03 pm
Just a thought, but if you're using AS2K5 or 2K8, why not have a fact table with Week_id and Employee_id as the foreign keys and the measure is the gross hours that you expect them to work. e.g. Week1 Steve 2
You remove the gross hours from your 'worked' fact table, but now you have 2 conforming dimensions (week and employee)that relate to both fact tables. Both facts should now aggregate using simple summation and the multiple measure groups should let you put (say) Employee on rows, Week on columns and nest the 'worked hours' and 'gross hours' measures on the columns.
HTH,
Steve.
June 29, 2009 at 11:32 am
Steve,
The requirement is to show every employee whether they worked or not on any project (whether they filed a timecard or not). My fact table contains only event of filing a time card. So what I did is I built a sparse fact table starting with cartesian product of employee_id and week_id from my conforming dimensions and outer joined my fact to the resulting dimnesion. Thus I got all my fiscal weeks no matter whether my fact table contains all of them or not. It worked out pretty well, addition works fine across all metrics.
But I am still curious if there is an elegant way to handle it through MDX instead of building huge fact tables. Any comments?
Regards,
Peter Shvets.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply