ssas - "Average" aggregation of sparsely populated percentage values -
i have cost center allocation data populated, 1 record per day, each 1 cost center dimension key. cube has head count measure (the data set "1"), , aggregate function set "lastchild". means head count report count person once in cost center in given time period.
introducing partial allocations - new measure have percentage value allocation, allowing multiple concurrent cost centers allocation should add 100% (with "day" being granular level). trying figure out how configure aggregation on other time periods. thought "average" should work fine, i.e. person allocated cost center @ 50% half time period reported @ 25% period. problem see facts not populated days allocation cost center 0%. illustrate:
employee1 costcentera 1/1/2013 50% employee1 costcenterb 1/1/2013 50% employee1 costcentera 1/2/2013 100% employee1 costcentera 1/3/2013 100% ... etc 100% in costcentera days
the above data on report month shows 50% allocation costcenterb, though person allocated 1 day, , average percentage on monthly basis should 1.6%.
i suppose generate 0%-allocations in data, fact table explode result, i'd rather change how "average" aggregation treats percentage values in facts sparsely populated, i.e. average should calculated based on number of granular units in reporting period (days in month, in case 0.5/31), not number of rows in fact table (0.5/1). can done in ssas?
if measure average allocation off factor proportional "sparseness" of facts, i.e. ratio of days in period , actual fact rows, can corrected follows:
adjusted average allocation = (calculated average allocation) * (fact count)/(number of days in period)
i created 2 new hidden measures, 1 named [fact count] fact count (a measure using aggregatefunction "count") , calculated measure named [days in period count] number of days, using expression
count(descendants([date].[calendar].currentmember,5),includeempty)
with [calendar] being name of hierarchy in date dimension.
finally, added calculated measure implements corrective formula:
[measures].[allocative head count]/ ( [measures].[days in period count]/[measures].[fact count] )
and named [adjusted average allocation]. can use in reports , appears approximate average cost center allocation on longer periods.
the formular [days in period count] btw not work row totals when filters involved. opened another question this.
Comments
Post a Comment