July 12, 2005 at 3:16 pm
I am currently stuck in attempting to calculate an average for one field. Usually, this is not a problem as you just divide the sum total of the field by the count total of the field.
However, the client has a different view on what an average is. They do not include the record if it is zero on Service Days, as zero signifies the Profit Center is closed (not forever, just for that time period).
On the individual profit center level, the profit center of course comes up with a zero as the average, which is correct, and I can detect at that level whether or not they have a zero as Service Days. However, my issue is at the Sub total and Total levels, where I have not been able to detect when a child record is 0.
Here are the details:
ProfitCenter ServiceDays Revenue AvgServiceDays RevenueDay
54155 20 5000 20 250
54156 0 0 0 0
Sub Total 20 5000 20 250
Here is what the average calculation results in:
ProfitCenter ServiceDays Revenue AvgServiceDays RevenueDay
54155 20 5000 20 250
54156 0 0 0 0
Sub Total 20 5000 10 500
As you can see, the AvgService Days get hit pretty hard when one Profit Center is closed, and the revenue per day calculation (Revenue/AvgServiceDays), ends up being way overstated.
Keep in Mind the AvgServiceDays is a formula based off the Measure ServiceDays, one summarized by Sum, one by Count. Somehow I really need to make the count summary ignore the zero valued ServiceDays when counting.
I know that there is most likely other ways of doing this and I am open to any suggestions.
However, please keep in mind that I am being fairly new at Analysis Services, and not fully understanding sets and the like yet, so try to be as descriptive as possible.
Anyone have any ideas?
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
July 12, 2005 at 3:20 pm
For the count to ignore 0 :
Select sum(case when SomeCol = 0 then 0 else 1 end) as NewCount from dbo.YourTable
It's hard to create the actual query without the table definition and sample data.
July 12, 2005 at 3:38 pm
Remi,
First of all, Thank you.
I know I can do this in SQL exactly as you listed, but where in Analysis Services can I type in a query like this? I need this average to be dynamic based upon the hierarchial structure they choose (one/many regions, one/many Districts, one/many workstations, etc...)
I could rebuild the Aggregate table to include this field, but I would need to get change approval to modify an existing database table. This would be a lot of waiting for this, but if it is the only way, then that will be the way I go.
Here is the actual table structure.
MART_SSS_GLBalances_Dashboard_AGGR
Fiscal_Period_Key INT Primary Key, Foreign Key
Profit_Center_Key INT Primary Key, Foreign Key
Revenue MONEY
ServiceDays INT
...
Lots more measure fields
...
DTStamp DateTime
The Fiscal_Period_Key is the time dimension and the Profit_Center_Key is the lowest level of the hierarchy which all other dimensions are built off of.
Tim Blum
Senior Manager, Outsourced Data Services
Learn2Live at Ureach.com
July 12, 2005 at 5:37 pm
I never worked with the analysis services... I'm gonna have to pass this on to other DBAs.
July 13, 2005 at 9:05 pm
Have you tried to add a distinct count of Profit Centres that have Service_Days >= 0 ?
If you can add this then it can be:
i) hidden from users (ie set the visiblity to false)
ii) used in an average calculation ie ServiceDays / <distinct_count>
Normally, for performance reasons, distinct counts are added to an exact copy of your existing cube (ie dimension wise) but with only the discount count as a measure (ie it is the *only* measure in the cube). You then use a virtual cube to combine the distinct countwith the existing other measures from the "base" cube. It is the virtual cube that you present to your users.
Let us know how you go.
Steve.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply