MDX-Optimization

hi does anyone see anything that can be improved with the following MDX?
thanks ahead.
--This returns TOP 1000
--Brokers with their ZipCodes and Actual Sales, Actual Tickets, and Actual
Activities
--For a given Territory
--and date range
--and Sales Range
--and Activity Range
with
member [Measures].[zip] as '[Firm].[standard].currentmember.
properties("Zip")'
Member [Measures].[SalesAmt] as 'CoalesceEmpty([Measures].[S
ales Amt],0)'
Member [Measures].[TicketCnt] as 'CoalesceEmpty([Measures].[
Ticket Qty],0)'
Member [Measures].[ActivityCnt] as 'CoalesceEmpty([Measures].
1;Activity
Cnt],0)'
SET [Times] as '{ [Time].[Standard].[Month].&[1/1/2
004] :
[Time].[Standard].[Month].&[12/1/2004] }'
MEMBER [Measures].[Actual_Sales] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[SalesAmt])))'
MEMBER [Measures].[Actual_Ticket] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[TicketCnt])))'
MEMBER [Measures].[Actual_Activity] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[ActivityCnt])))'
SET [FilterSales] AS
'FILTER(
NONEMPTYCROSSJOIN
(
{DESCENDANTS([Firm].[Standard].[All Firms],[Firm].[
Standard].[Contact])},
{[Territory Current].[Standard].[Territory].[Ben Alcid]
},
{[Times]},
{[Company].[Standard].[Organization].[CompanyName]},
{[Transaction Type].[Standard].[Type].[Paid]},
1
),
([Measures].[Actual_Sales]) >= 1
and ( [Measures].[Actual_Sales]) <= 5000000
and ([Measures].[Actual_Activity]) >= 1
and ([Measures].[Actual_Activity]) <= 100
)'the second part of the select is here:
select {[Measures].[zip], [Measures].[Actual_Sales],
[Measures].[Actual_Ticket],[Measures].[Actual_Activity]} on
columns,
{
ORDER(
SUBSET ({[FilterSales]} ,0,1000 ),
[Measures].[zip], BASC
)
}
--DIMENSION PROPERTIES [Firm].[standard].properties("Zip")
on rows
From SalesActivity
"bc" wrote:

> hi does anyone see anything that can be improved with the following MDX?
> thanks ahead.
> --This returns TOP 1000
> --Brokers with their ZipCodes and Actual Sales, Actual Tickets, and Actual
> Activities
> --For a given Territory
> --and date range
> --and Sales Range
> --and Activity Range
> with
> member [Measures].[zip] as '[Firm].[standard].currentmembe
r.properties("Zip")'
> Member [Measures].[SalesAmt] as 'CoalesceEmpty([Measures].[
;Sales Amt],0)'
> Member [Measures].[TicketCnt] as 'CoalesceEmpty([Measures].
1;Ticket Qty],0)'
> Member [Measures].[ActivityCnt] as 'CoalesceEmpty([Measures].&
#91;Activity
> Cnt],0)'
> SET [Times] as '{ [Time].[Standard].[Month].&[1/1
/2004] :
> [Time].[Standard].[Month].&[12/1/2004] }'
> MEMBER [Measures].[Actual_Sales] AS
> 'SUM({[Measures].[zip]},SUM({[Times]},([Measures
].[SalesAmt])))'
> MEMBER [Measures].[Actual_Ticket] AS
> 'SUM({[Measures].[zip]},SUM({[Times]},([Measures
].[TicketCnt])))'
> MEMBER [Measures].[Actual_Activity] AS
> 'SUM({[Measures].[zip]},SUM({[Times]},([Measures
].[ActivityCnt])))'
> SET [FilterSales] AS
> 'FILTER(
> NONEMPTYCROSSJOIN
> (
> {DESCENDANTS([Firm].[Standard].[All Firms],[Firm].
[Standard].[Contact])},
> {[Territory Current].[Standard].[Territory].[Ben A
lcid] },
> {[Times]},
> {[Company].[Standard].[Organization].[CompanyName]
},
> {[Transaction Type].[Standard].[Type].[Paid]},
> 1
> ),
> ([Measures].[Actual_Sales]) >= 1
> and ( [Measures].[Actual_Sales]) <= 5000000
> and ([Measures].[Actual_Activity]) >= 1
> and ([Measures].[Actual_Activity]) <= 100
> )'
>|||Firstly, what is meant by "TOP 1000" - it looks like Subset() is
selecting the first 1000 from [FilterSales].
Secondly, some simplification seems possible - why are the
CoalesceEmpty() calculated measures needed?
[vbcol=seagreen]
--This returns TOP 1000
--Brokers with their ZipCodes and Actual Sales, Actual Tickets, and
Actual
Activities
--For a given Territory
--and date range
--and Sales Range
--and Activity Range
with
member [Measures].[zip] as
'[Firm].[standard].currentmemb_er.properties("Zip")'
SET [Times] as '{ [Time].[Standard].[Month].&[1/_1/
2004] :
[Time].[Standard].[Month].&[12_/1/2004] }'
MEMBER [Measures].[Actual_Sales] AS
'SUM({[_Times]},([Measures].[Sales Amt]_))'
MEMBER [Measures].[Actual_Ticket] AS
'SUM({[_Times]},([Measures].[Ticket Cnt_]))'
MEMBER [Measures].[Actual_Activity] AS
'SUM({[_Times]},([Measures].[Activity C_nt]))'
SET [FilterSales] AS
'FILTER(
NONEMPTYCROSSJOIN
(
{DESCENDANTS([Firm].[Standard]_.[All
Firms],[Firm].[Standard].[Cont_act])},
{[Territory Current].[Standard].[Territory_].[Ben Alcid
] },
{[Times]},
{[Company].[Standard].[Organiz_ation].[CompanyName]},
{[Transaction Type].[Standard].[Type].[Paid]_},
1
),
([Measures].[Actual_Sales]) >= 1
and ( [Measures].[Actual_Sales]) <= 5000000
and ([Measures].[Actual_Activity]) >= 1
and ([Measures].[Actual_Activity]) <= 100
)'
Select {[Measures].[zip], [Measures].[Actual_Sales],
[Measures].[Actual_Ticket],[Me_asures].[Actual_Activity]} on
columns,
{
ORDER(
SUBSET ({[FilterSales]} ,0,1000 ),
[Measures].[zip], BASC
)
}
--DIMENSION PROPERTIES [Firm].[standard].properties("_Zip")
on rows
From SalesActivity[vbcol=seagreen]
Also, if the [Times] set is large, an equivalent set using higher
aggregations can improve performance:
http://groups-beta.google.com/group...erver.olap/msg/
dacc5ced4823970a[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
From: "Chris Webb" <OnlyForPostingToNewsgro...@.hotmail.c_om>
Date: Fri, 31 Dec 2004 04:01:01 -0800
Subject: RE: Problem with MDX query
I don't think the query is hanging, I think it's just taking a very long
time
to complete! Summing up all those days in your date range is going to
take a
long time, plus I'll bet that your second query (because it doesn't
mention
the YearMonthDay dimension at all) probably hits aggregations whereas
your
first query doesn't.
Since this is a fairly common problem I've just made it the subject of
the
first entry of my new blog, which you can read here:
http://spaces.msn.com/members/_cwebbbi/Blog/cns!1pi7ETChsJ1un__2s41jm9I.
.
In your case, I think the approach of replacing the day members in your
day
range set with common ancestors could solve the problem.
HTH,
Chris[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||hi thank you for responding, here is the query again and let me explain the
cube structure I have:
I have 2 cubes (Activities, Sales) and a virtual cube SalesActivity.
The Measures SalesAmt and TicketCnt came from the Sales Cube.
The Measure ActivityCnt came from Activity Cube.
These 3 are actual columns from the Fact tables which made up the cube
(Fact_Act and Fact_SLS).
The problem is the query seems to take a long time to run.
And yes you are right about the [Times] set. If I change to Year Level
instead of a lower level Month it is faster. But unfortunately I can not do
this because originally the requirement from the business is that the user
can choose any day range
so it was worst before when it was at the Day Level...since then I have
moved up to the Month Level...I can not go up to the Year Level because the
users want to see results across multiple months.
The CoalesceEmpty functions you see are there because since this is a
virtual cube I am referencing (SalesActivity) the numbers can be Empty so I
am just replacing it with zero where appropriate (because zero means
something here, example: we have sales but no activities for the criteria
selected or vice versa).
The other thing I want to mention is that I have the ZipCode as the member
properties of a dimension [Firm].[Standard] with 2 levels
Firm and Contact. A firm has many contacts and the zipcode is the zipcode
for the contacts. So I have 2 physical tables (Firm and Contact, one to man
y
relationship).
The Firm table is the source for the Firm Level and the Contact table is the
source for the Contact Level (the zipcode column is in the contact table).
Since the ContactID is the Leaf Level here, it is referenced in the 2 Fact
tables Fact_Act and Fact_SLS (it is a foreign key in the Fact tables).
Should I Create a new Location dimension
(State,County,City,ZipCode,Latitude,Long
itude) with ZipCode as the Lowest
Level and Latitude and Longitude as Dimension Properties and put the column
ZipCode inside the Fact Tables so that it can be referenced quickly? This is
because the query below returns a list of zipcodes that match the criteria
and then the user choose a particular zipcode to drill down to see further
detail on the facts...it is a mappoint application integrated with OLAP.
thank you for your help
--This returns TOP 1000
--Brokers with their ZipCodes and Actual Sales, Actual Tickets, and Actual
Activities
--For a given Territory
--and date range
--and Sales Range
--and Activity Range
with
member [Measures].[zip] as '[Firm].[standard].currentmember.
properties("Zip")'
Member [Measures].[SalesAmt] as 'CoalesceEmpty([Measures].[S
ales Amt],0)'
Member [Measures].[TicketCnt] as 'CoalesceEmpty([Measures].[
Ticket Qty],0)'
Member [Measures].[ActivityCnt] as 'CoalesceEmpty([Measures].
1;Activity
Cnt],0)'
SET [Times] as '{ [Time].[Standard].[Month].&[1/1/2
004] :
[Time].[Standard].[Month].&[12/1/2004] }'
MEMBER [Measures].[Actual_Sales] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[SalesAmt])))'
MEMBER [Measures].[Actual_Ticket] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[TicketCnt])))'
MEMBER [Measures].[Actual_Activity] AS
'SUM({[Measures].[zip]},SUM({[Times]},([Measures].
[ActivityCnt])))'
SET [FilterSales] AS
'FILTER(
NONEMPTYCROSSJOIN
(
{DESCENDANTS([Firm].[Standard].[All Firms],[Firm].[
Standard].[Contact])},
{[Territory Current].[Standard].[Territory].[Ben Alcid]
},
{[Times]},
{[Company].[Standard].[Organization].[CompanyName]},
{[Transaction Type].[Standard].[Type].[Paid]},
1
),
([Measures].[Actual_Sales]) >= 1
and ( [Measures].[Actual_Sales]) <= 5000000
and ([Measures].[Actual_Activity]) >= 1
and ([Measures].[Actual_Activity]) <= 100
)'
select {[Measures].[zip], [Measures].[Actual_Sales],
[Measures].[Actual_Ticket],[Measures].[Actual_Activity]} on
columns,
{
ORDER(
SUBSET ({[FilterSales]} ,0,1000 ),
[Measures].[zip], BASC
)
}
--DIMENSION PROPERTIES [Firm].[standard].properties("Zip")
on rows
From SalesActivity
"Deepak Puri" wrote:

> Firstly, what is meant by "TOP 1000" - it looks like Subset() is
> selecting the first 1000 from [FilterSales].
> Secondly, some simplification seems possible - why are the
> CoalesceEmpty() calculated measures needed?
>
> --This returns TOP 1000
> --Brokers with their ZipCodes and Actual Sales, Actual Tickets, and
> Actual
> Activities
> --For a given Territory
> --and date range
> --and Sales Range
> --and Activity Range
> with
> member [Measures].[zip] as
> '[Firm].[standard].currentmemb-er.properties("Zip")'
> SET [Times] as '{ [Time].[Standard].[Month].&[1/-
1/2004] :
> [Time].[Standard].[Month].&[12-/1/2004] }'
> MEMBER [Measures].[Actual_Sales] AS
> 'SUM({[-Times]},([Measures].[Sales Amt]-))'
> MEMBER [Measures].[Actual_Ticket] AS
> 'SUM({[-Times]},([Measures].[Ticket Cnt-]))'
> MEMBER [Measures].[Actual_Activity] AS
> 'SUM({[-Times]},([Measures].[Activity C-nt]))'
> SET [FilterSales] AS
> 'FILTER(
> NONEMPTYCROSSJOIN
> (
> {DESCENDANTS([Firm].[Standard]-.[All
> Firms],[Firm].[Standard].[Cont-act])},
> {[Territory Current].[Standard].[Territory-].[Ben
Alcid] },
> {[Times]},
> {[Company].[Standard].[Organiz-ation].[CompanyName
]},
> {[Transaction Type].[Standard].[Type].[Paid]-},
> 1
> ),
> ([Measures].[Actual_Sales]) >= 1
> and ( [Measures].[Actual_Sales]) <= 5000000
> and ([Measures].[Actual_Activity]) >= 1
> and ([Measures].[Actual_Activity]) <= 100
> )'
> Select {[Measures].[zip], [Measures].[Actual_Sales],
> [Measures].[Actual_Ticket],[Me-asures].[Actual_Activity]}
on columns,
> {
> ORDER(
> SUBSET ({[FilterSales]} ,0,1000 ),
> [Measures].[zip], BASC
> )
> }
> --DIMENSION PROPERTIES [Firm].[standard].properties("-Zip")
> on rows
> From SalesActivity
>
> Also, if the [Times] set is large, an equivalent set using higher
> aggregations can improve performance:
> http://groups-beta.google.com/group...erver.olap/msg/
> dacc5ced4823970a
> Newsgroups: microsoft.public.sqlserver.olap
> From: "Chris Webb" <OnlyForPostingToNewsgro...@.hotmail.c-om>
> Date: Fri, 31 Dec 2004 04:01:01 -0800
> Subject: RE: Problem with MDX query
> I don't think the query is hanging, I think it's just taking a very long
> time
> to complete! Summing up all those days in your date range is going to
> take a
> long time, plus I'll bet that your second query (because it doesn't
> mention
> the YearMonthDay dimension at all) probably hits aggregations whereas
> your
> first query doesn't.
> Since this is a fairly common problem I've just made it the subject of
> the
> first entry of my new blog, which you can read here:
> http://spaces.msn.com/members/-cwebbbi/Blog/cns!1pi7ETChsJ1un-_2s41jm9I.
> ..
> In your case, I think the approach of replacing the day members in your
> day
> range set with common ancestors could solve the problem.
> HTH,
> Chris
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Here are inline responses to your clarifications:
1)
----
If I change to Year Level
instead of a lower level Month it is faster. But unfortunately I can not
do
this because originally the requirement from the business is that the
user
can choose any day range
so it was worst before when it was at the Day Level...since then I have
moved up to the Month Level...I can not go up to the Year Level because
the
users want to see results across multiple months.[vbcol=seagreen]
The link that I provided in my post lets the user specify an arbitrary
time range at a lower level, then converts it into an optimized
combination of higher and lower members. So this should work in your
scenario, even at [Day] level.
----
2)
----
The CoalesceEmpty functions you see are there because since this is a
virtual cube I am referencing (SalesActivity) the numbers can be Empty
so I
am just replacing it with zero where appropriate (because zero means
something here, example: we have sales but no activities for the
criteria
selected or vice versa).[vbcol=seagreen]
But you don't need to do this for Sum() function, since it will
effectively treat empty as 0 - this could be faster.
----
3)
----
Should I Create a new Location dimension
(State,County,City,ZipCode,Latitude,Long
itude) with ZipCode as the
Lowest
Level and Latitude and Longitude as Dimension Properties and put the
column
ZipCode inside the Fact Tables so that it can be referenced quickly?[vbcol=seagreen]
Since "Zip" is already a Member Property, you could create a Virtual
Dimension, and compare its performance to adding a new dimension from a
ZipCode field in the fact table. But can multiple contacts have the same
ZipCode - in which case, do you still need to return the contacts along
with the zip codes? If this is so, then something like:
NonEmptyCrossJoin([ZipCode].[Zip].Members,
DESCENDANTS([Firm].[Standard].[All Firms],[Firm].[Standa
rd].[Contact]),
{[Territory Current].[Standard].[Territory].[Ben Alcid]
},
{[Times]},
{[Company].[Standard].[Organization].[CompanyName]},
{[Transaction Type].[Standard].[Type].[Paid]},
2) could be returned on rows, already ordered by Zip.
----
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Deepak,
"The link that I provided in my post lets the user specify an arbitrary
time range at a lower level, then converts it into an optimized
combination of higher and lower members. So this should work in your
scenario, even at [Day] level."
thank you for your response but I can't seem to get to the link you provided
.
can you put the code here or give me the link again?
Yes I do need to return contacts along with the zipcodes and yes one zipcode
can belong to multiple contacts. I will try a virtual dimension as you
suggested and let you know. I also tried partition the cube to different
year but the performance seems to be the same.
"Deepak Puri" wrote:

> Here are inline responses to your clarifications:
> 1)
> ----
> If I change to Year Level
> instead of a lower level Month it is faster. But unfortunately I can not
> do
> this because originally the requirement from the business is that the
> user
> can choose any day range
> so it was worst before when it was at the Day Level...since then I have
> moved up to the Month Level...I can not go up to the Year Level because
> the
> users want to see results across multiple months.
> The link that I provided in my post lets the user specify an arbitrary
> time range at a lower level, then converts it into an optimized
> combination of higher and lower members. So this should work in your
> scenario, even at [Day] level.
> ----
> 2)
> ----
> The CoalesceEmpty functions you see are there because since this is a
> virtual cube I am referencing (SalesActivity) the numbers can be Empty
> so I
> am just replacing it with zero where appropriate (because zero means
> something here, example: we have sales but no activities for the
> criteria
> selected or vice versa).
> But you don't need to do this for Sum() function, since it will
> effectively treat empty as 0 - this could be faster.
> ----
> 3)
> ----
> Should I Create a new Location dimension
> (State,County,City,ZipCode,Latitude,Long
itude) with ZipCode as the
> Lowest
> Level and Latitude and Longitude as Dimension Properties and put the
> column
> ZipCode inside the Fact Tables so that it can be referenced quickly?
> Since "Zip" is already a Member Property, you could create a Virtual
> Dimension, and compare its performance to adding a new dimension from a
> ZipCode field in the fact table. But can multiple contacts have the same
> ZipCode - in which case, do you still need to return the contacts along
> with the zip codes? If this is so, then something like:
> NonEmptyCrossJoin([ZipCode].[Zip].Members,
> DESCENDANTS([Firm].[Standard].[All Firms],[Firm].[Stan
dard].[Contact]),
> {[Territory Current].[Standard].[Territory].[Ben Alci
d]},
> {[Times]},
> {[Company].[Standard].[Organization].[CompanyName]},
> {[Transaction Type].[Standard].[Type].[Paid]},
> 2) could be returned on rows, already ordered by Zip.
> ----
>
> - Deepak
> Deepak Puri
> Microsoft MVP - SQL Server
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||Here's a Google link to the thread where Chris Webb discussed the
possibility to optimize a time series, and I posted some sample MDX
code. You can refer to Chris's BI Blog (referenced in this thread) for
more details:
http://groups-beta.google.com/group...erver.olap/msg/
dacc5ced4823970a[vbcol=seagreen]
Newsgroups: microsoft.public.sqlserver.olap
Subject: RE: Problem with MDX query
Chris Webb Dec 31 2004, 4:01 am
I don't think the query is hanging, I think it's just taking a very long
time
to complete! Summing up all those days in your date range is going to
take a
long time, plus I'll bet that your second query (because it doesn't
mention
the YearMonthDay dimension at all) probably hits aggregations whereas
your
first query doesn't.
Since this is a fairly common problem I've just made it the subject of
the
first entry of my new blog, which you can read here:
http://spaces.msn.com/members/_cwebbbi/Blog/cns!1pi7ETChsJ1un__2s41jm9I.
.
In your case, I think the approach of replacing the day members in your
day
range set with common ancestors could solve the problem.
HTH,
Chris
Deepak Puri Jan 3, 1:07 pm show options
Hi Chris,
Here's an alternative formulation that doesn't refer to the individual
time dimension levels explicitly, so it should work with various
hierachies. An assumption here is that 'MYRANGE' is at the leaf level of
the time hierarchy:
*The initial time range expressed in months*/
SET MYRANGE AS '{[Time].[1997].[Q1].[3]
:[Time].[1998].[Q3].[7]}'
/*Test whether Time member is a subset of range */
Member [Measures].[InRange] as
'Except(Descendants([Time].Cur_rentMember,, LEAVES),
MYRANGE).Count = 0'
/* Include all members in range whose parents are not in range */
SET MYNEWRANGE AS
'Filter([Time].Members, [Measures].[InRange]
AND Not ( [Measures].[InRange], [Time].Parent))'
/*Helper calculated members to display the steps*/
MEMBER MEASURES.STEP1 AS 'SETTOSTR(MYRANGE)'
MEMBER MEASURES.STEP5 AS 'SETTOSTR(MYNEWRANGE)'
/*Show working*/
SELECT {MEASURES.STEP1,
MEASURES.STEP5} ON 0
FROM SALES[vbcol=seagreen]
- Deepak
Deepak Puri
Microsoft MVP - SQL Server
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!