I have a Customer dimension with 4 levels: country, state, city and
customerID. Some of the reports needed use country, state and city,
some use customerID only and some use all levels. Is it better to
break the dimension into 2 dimensions, one of country, state and city
and another dimension of customerID only? I'm asking this as the
reports are slow.

Thanks in advance for any help or suggestions.

By "report" do you mean using SQL Server Reporting Services, or some other
front-end tool?
In general, this is really a front-end topic around displaying various
levels -- not a back-end question about the server or the multidimensional
design itself.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Marcus" <marcusyeoh (AT) gmail (DOT) com> wrote

Depending on the reporting tool that you are using, do you know what the
generated MDX queries look like, since those may also shed some light on
why the reports run slowly?


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The reporting tool is SQL Server Reporting Services and here is the
MDX query. Location dimension indicates where the product was shipped
from.

//CustomerLocation
SELECT NON EMPTY{ [Measures].[Sales],[Measures].[Quantity],
[Measures].[LineCost],[Measures].[LineDiscount],[Measures].[Growth
Parallel],[Measures].[Growth From Previous],[Measures].[Contribution
ProductBrand] } ON COLUMNS,
{ NONEMPTYCROSSJOIN(
Descendants([Customer].[CustID].[Country].MEMBERS,
[Customer].[CustID].[City],LEAVES),
{[Location].[Loc].Members}
)} ON ROWS
,{Descendants([Invoice].[Date].[2000]:[Invoice].[Date].[2004],[Invoice].[Date].[Month],LEAVES)}
ON PAGES FROM Sales

Do you know how much time is taken in MDX query execution versus report
rendering - can you estimate just query performance by running it in
another MDX client?

One thing to confirm (if you haven't already) is that all calculated
measures in the query have their Non Empty Behavior set to a base
measure (if possible). This can make a big difference with large
cross-joins (see below).

Other than that, approx. how many members are at different levels of the
[Customer] and [Location] heirarchies?


http://support.microsoft.com/default...b;en-us;304137
Keyword

View products that this article applies to.

This article was previously published under Q304137
SUMMARY

In some cases, a query slows down considerably when you use both the NON
EMPTY keyword on an axis of a Multidimensional Expression together with
a calculated member.

This article describes how you can optimize a query, by using the Non
Empty Behavior property for the calculated member.

MORE INFORMATION

Use of the NON EMPTY keyword on a MDX statement causes the calculated
member to evaluate the calculated member's expression for each member to
determine whether or not the member is empty. The extra time taken for
the evaluation is what causes the NON EMPTY keyword to slow down the MDX
query.

To optimize a query that uses the NON EMPTY keyword, set the Non Empty
Behavior property to a base measure of the cube, so that if the base
measure is empty the calculated member's value is considered empty and
the expression is never evaluated, which increases the query
performance.

How to Enable the Non Empty Behavior Property

To enable the Non Empty Behavior property, use these steps:
Right-click the Sales cube, and then click Edit.

In the Cube Editor, scroll down to the Calculated Members folder.

By default, the Calculated Members folder is expanded. Select the
calculated member Sales Average, and then click Properties to open the
Properties pane for the Sales Average calculated member.

In the list of properties, locate the Non Empty Behavior drop-down list
box and click Store Count.
Save the cube.

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!