RE: Can someone explain what the issue is

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Deepak (Deepak_at_discussions.microsoft.com)
Date: 11/29/04 

Date: Mon, 29 Nov 2004 10:09:40 -0800

In case any of the measures is calculated (maybe Margin?), ensure that its
"Non Empty Behavior" Property is set to an appropriate cube base measure.
This should improve the performance of Excel MDX queries with the NON EMPTY
clause:

http://support.microsoft.com/default.aspx?scid=kb;en-us;304137
>>
INF: How to Increase the Speed of MDX Queries that Contain the NON EMPTY
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

"Matt" wrote:

> right here goes, i have a set of cubes, none of which is greater than about
> 100MB. one in particular is causing a real headache
>
> System
>
> 2 1.7 Ghz XEON CPU DL 580 - RAID 10 storage with SQL enterprise SP3, 3GB RAM
> fix and is dedicated to cube processing
>
> the Problem Cube
>
> 3 dimensions and 4 measures
>
> Team_Employee_Customer Dimension:
>
> contains a hierarchy of
>
> team (6)
> |
> team member (67)
> |
> allocated customer (3452)
>
> vendor_Product dimension
>
> Vendor (312)
> |
> Product (4513)
>
> Time Dimension
>
> split down into days, weeks, months, quarters, years
>
> 4 measures, Cost, Sales Total, Margin, Qty of items
>
> the ETL process only extracts "live data" for all these dimensions so, a
> customer is only included if they have made a sale, a product only included
> if it has been sold etc.
>
> the problem i have is two fold. I have a user (using excel 2003) who is
> using the cube to create a report like the following
>
> Columns
>
> september 2003 , september2004
>
> Rows
>
> Team, Employee, Customer, Vendor, Product
>
> measures
>
> Qty and Margin
>
> Firstly i realise that this is not the kind of report OLAP was intended as
> the data being retrieved is atomic. Also i have set
> "Execution Location=3;Default Isolation Mode=1" in the *.oqy file for the
> cube
>
> my problem is that the report takes hours to complete if at all, the query
> is based on a specific vendor and a specific team, which can be pretty much
> any combination. I have tried creating partitions on the most frequently used
> vendors, making sure i choose a slice based on the specific vendor and
> although it seems a little quicker it still pretty much grinds to a halt..
>
> my question is, is this a problem with my design or is it that OLAP was
> never meant to produce data to this level of detail, where effectively every
> level of every dimension is being produced. Also, is there a better way to
> do this
>
> thanks in advance for any help
>

Relevant Pages

  • Re: Pivot table is very slow
    ... then have you set "Non Empty Behavior" for them? ... Problems with Calculated member -- Correction ... Empty Behavior property for the calculated member. ...
    (microsoft.public.sqlserver.olap)
  • Re: How to increase speed of query?
    ... Problems with Calculated member -- Correction ... How to Increase the Speed of MDX Queries that Contain the NON EMPTY ... a query slows down considerably when you use both the NON ... Empty Behavior property for the calculated member. ...
    (microsoft.public.sqlserver.olap)
  • RE: Can someone explain what the issue is
    ... Thanks Deepak but there are no calculated members in the cube, ... > This should improve the performance of Excel MDX queries with the NON EMPTY ... > Empty Behavior property for the calculated member. ...
    (microsoft.public.sqlserver.olap)
  • RE: Can someone explain what the issue is
    ... How many rows are typically returned in this report - is the cube sparse, ... >> This should improve the performance of Excel MDX queries with the NON EMPTY ... >> Empty Behavior property for the calculated member. ...
    (microsoft.public.sqlserver.olap)
  • Re: Analysis services
    ... what you are asking is that whenever one sends a query to the ... cube, the Date dimension should not have NON EMPTY in front of it - in order ... you use for building line charts don't have NON EMPTY in front of Date ...
    (microsoft.public.sqlserver.olap)