RE: Can someone explain what the issue is
From: Deepak (Deepak_at_discussions.microsoft.com)
Date: 11/29/04
- Next message: Deepak: "RE: OWC IDE Frontpage, etc"
- Previous message: Dave Wickert [MSFT]: "Re: Data Usage Auditing"
- In reply to: Matt: "RE: Can someone explain what the issue is"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 29 Nov 2004 14:49:04 -0800
How many rows are typically returned in this report - is the cube sparse, so
that only a few of the possible Product/Customer combinations have data? And
what does the MDX query for Reporting Services look like - it should be
possible to tweak the query for sparse data. Also, did you try partitioning
the cube by month (if your report columns are always going to be specific
months)?
- Deepak
"Matt" wrote:
> Thanks Deepak but there are no calculated members in the cube, margin is
> calculated as part of the ETL process.
>
> im at a loss with it, performance also appears to be the same using sql
> reporting services.
>
> I suppose im looking for someone to confirm to me that this behaviour is to
> be expected with such a report ...
>
> "Deepak" wrote:
>
> > 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
> > >
- Next message: Deepak: "RE: OWC IDE Frontpage, etc"
- Previous message: Dave Wickert [MSFT]: "Re: Data Usage Auditing"
- In reply to: Matt: "RE: Can someone explain what the issue is"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
