April 24, 2012 at 9:39 am
I have the following code that I am using on the Adventureworks 2008 R2 database but it just hangs when using the Customer base
WITH
MEMBER [measures].[total] AS
[Measures].[Internet Sales Amount] + [Measures].[Internet Tax Amount]
MEMBER [Measures].[Avg Total] AS
IIF(ISEMPTY([measures].[total]),null,Avg
(
[Customer].[Customer].[Customer].Members
,[measures].[total]
))
MEMBER [measures].[standard deviation] AS
IIF(ISEMPTY([measures].[total],null,StdDev
(
{
[Customer].[Customer].[Customer].Members
}
,[measures].[total]
))
MEMBER [Measures].[StatusValue] AS
IIF(ISEMPTY([measures].[total]),null,CASE
WHEN
[Measures].[Total]
>
[Measures].[Avg Total] + [measures].[standard deviation]
THEN 'Good'
WHEN
[Measures].[Total]
<
[Measures].[Avg Total] - [measures].[standard deviation]
THEN 'Low'
ELSE 'Normal'
END)
SELECT NON EMPTY
{
,[measures].[total]
,[Measures].[Avg Total]
,[measures].[standard deviation]
,[Measures].[StatusValue]
} ON COLUMNS
,NON EMPTY
[Customer].[Customer].[Customer].Members ON ROWS
FROM [Adventure Works];
Is there a way of speeding this up?
The calulcations are correct but it just doesn't like being applied to across the whole customer base which practically makes it not useable
Any help would be really helpful
I have read this link but I am not sure of how I can apply it to this query
May 15, 2012 at 2:10 am
Did you try SQL Profiler to get a better idea where exactly it hangs?
It's a great tool let you see the inner workings of the SSAS engine.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply