Re: RANKING CUSTOMERS/CALCULATED MEASURE/SQL 2000
- From: Deepak Puri <deepak_puri@xxxxxxxxxxxxxxx>
- Date: Fri, 09 Dec 2005 15:09:26 -0800
Hi Fernando,
Here are some possible issues with your approach:
- A cube or session Named Set will not be dynamically recomputed each
time the charge type selection changes.
- Maybe you're including customers that aren't in the Top40 Named Set,
so they're all assigned a rank of 1.
This recent thread discusses a solution, though there may be performance
problems when the row set is large:
http://groups.google.com/group/microsoft.public.sqlserver.olap/browse_fr
m/thread/51673105feb39eaf/4b8c06c0fc689ac9#4b8c06c0fc689ac9
>>
Newsgroups: microsoft.public.sqlserver.olap
From: "alanr"
Date: 13 Oct 2005 09:06:25 -0700
Subject: visual rank calculated member on server is very very slow
Greetings!
I am struggling with the performance of a visual ranking calculation
defined on the server. I based this on posts from Tom Chester and
others. I am using the September CTP (which as a side note seems to
have eliminated the need to do order the set before ranking?). Excel
2003 pivot table is client. Note that the member won't work with OWC
because the axis doesn't have the measure on it.
For very small dimensions it is just fine but for those with even a few
hundred members it takes more than a minute. With a thousand members
takes 10+ minutes. Ugh:( This is probably because it is performing
the rank calculation for each cell and nothing is cached between cells.
I have seen some very interesting posts from Deepak that suggest how to
cache ordered sets using "WITH" and Generate but I don't think there is
a way to do that within a server calculated member? Also, I don't want
to cache the ordered set, I want to cache the RANK with it's elegant
handling of ties.
Here is the definition of the member
CREATE MEMBER CURRENTCUBE.[MEASURES].[Rank Desc]
AS
-- IF GRAND TOTAL THEN NULL
IIF(StrToSet("Axis(1)").Item(0).Item(0).Dimension.CurrentMember.Level
IS StrToSet("Axis(1)").Item(0).Item(0).Dimension.DefaultMember.Level,
NULL,
-- MEASURE IS LAST ITEM ON COLUMN AXIS - IF EMPTY THEN NULL
IIF(IsEmpty(StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0)
Count
-1)), NULL,
-- DIMENSION IS LAST ITEM ON ROW AXIS
Rank(StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count
-1).Dimension.CurrentMember,
StrToSet("Axis(1)").Item(0).Item(StrToSet("Axis(1)").Item(0).Count
-1).Dimension.CurrentMember.Siblings,
-- RANK DESC
-1 * StrToSet("Axis(0)").Item(0).Item(StrToSet("Axis(0)").Item(0).Count
-1)))),
VISIBLE = 1
...
>>
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.developersdex.com ***
.
- Follow-Ups:
- Re: RANKING CUSTOMERS/CALCULATED MEASURE/SQL 2000
- From: fsanchez
- Re: RANKING CUSTOMERS/CALCULATED MEASURE/SQL 2000
- Prev by Date: How do you kill a long running query in SSAS?
- Next by Date: Re: MDX Issue - Deepak Please Help
- Previous by thread: How do you kill a long running query in SSAS?
- Next by thread: Re: RANKING CUSTOMERS/CALCULATED MEASURE/SQL 2000
- Index(es):
Relevant Pages
|
