Can someone explain what the issue is

#1

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)

Quote:

team member (67)

allocated customer (3452)

vendor_Product dimension

Vendor (312)

Quote:

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

#2

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...b;en-us;304137

Quote:

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.
...

Quote:

- Deepak

"Matt" wrote:

Quote:

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

#3

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:

Quote:

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...b;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

#4

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:

Quote:

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...b;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

#5

is this a MOLAP cube?
How many total rows are loaded into the cube?
What happens if you use the Data Browser in Analysis Manager to repo this
report?
Have you tried using the MDX sample program that comes with AS on this
report?

dlr

"Matt" <Matt (AT) discussions (DOT) microsoft.com> wrote

Quote:

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

#6

Hi,

The fact table has approx 400,000 rows. the dimension levels have the
figures in brackets () as entered in the original post. Data browser performs
very slowly as well. i have tried using the MDX application and also have the
same issue. it also has trouble displaying crossjoin information in a useful
manner

here is the mdx

select

CROSSJOIN
(
hierarchize({[Time].[All Time].[2003],[Time].[All Time].[2004],[Time].[All
Time].[2003].[Quarter 3]: [Time].[All Time].[2004].[Quarter 2]}),
{ [Measures].[Cost], [Measures].[Total]}
)
ON COLUMNS,
NON EMPTY

CROSSJOIN
(
{[Team_ONLY].[Team Name].Members},
CROSSJOIN
(
{[Customer_only].[Company
Name].Members},
{[Product_only].[Product Name].members}

) ) ON ROWS
FROM SALES_ATOMIC
where [vendor_only].[vendor name].[veritas]

this example is not the worst case imagine an extra crossjoin on ROWS.... i
suppose in a way it would help to see the MDX that excel is producing as well
if someone could point me in the right direction...

i appear to get the most problems when the product level is dragged and
dropped onto rows next to the customer level.

just to re-iterate. the report is to show

for september 2003 and september 2004

qty and margin for
every product, per vendor, per sales team, , per employee in that sales
team, per allocated customer...

things begin labouring when product and customer are there together

"Dennis Redfield" wrote:

Quote:

is this a MOLAP cube?
How many total rows are loaded into the cube?
What happens if you use the Data Browser in Analysis Manager to repo this
report?
Have you tried using the MDX sample program that comes with AS on this
report?

dlr

"Matt" <Matt (AT) discussions (DOT) microsoft.com> wrote in message
news:5F13B834-38A2-4ED5-BB03-8DC2C53CF2CA (AT) microsoft (DOT) com...
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

#7

There's a couple of discrepancies between the MDX query below and the
dimension details you provided earlier:

- The query uses separate Team and Customer dimensions, but the
original post described a combined Team/Customer dimension.

- The time members in the query are at year/quarter level (not Sept.)

Anyway, you might try a NonEmptyCrossJoin() version of the query:

Quote:

Select

CROSSJOIN
(Hierarchize({[Time].[All Time].[2003],[Time].[All Time].[2004],
[Time].[All Time].[2003].[Quarter 3]: [Time].[All
Time].[2004].[Quarter 2]}),
{[Measures].[Cost], [Measures].[Total]}) ON COLUMNS,
NONEMPTYCROSSJOIN({[Team_ONLY].[Team Name].Members},
{[Customer_only].[Company Name].Members},
{[Product_only].[Product Name].members},
{[Time].[All Time].[2003], [Time].[All Time].[2004]}, 3) ON ROWS
FROM SALES_ATOMIC
where [vendor_only].[vendor name].[veritas]

Quote:

"Matt" <Matt (AT) discussions (DOT) microsoft.com> wrote

Quote:

Hi,

The fact table has approx 400,000 rows. the dimension levels have the
figures in brackets () as entered in the original post. Data browser performs
very slowly as well. i have tried using the MDX application and also have the
same issue. it also has trouble displaying crossjoin information in a useful
manner

here is the mdx

select

CROSSJOIN
(
hierarchize({[Time].[All Time].[2003],[Time].[All Time].[2004],[Time].[All
Time].[2003].[Quarter 3]: [Time].[All Time].[2004].[Quarter 2]}),
{ [Measures].[Cost], [Measures].[Total]}
)
ON COLUMNS,
NON EMPTY

CROSSJOIN
(
{[Team_ONLY].[Team Name].Members},
CROSSJOIN
(
{[Customer_only].[Company
Name].Members},
{[Product_only].[Product Name].members}

) ) ON ROWS
FROM SALES_ATOMIC
where [vendor_only].[vendor name].[veritas]

this example is not the worst case imagine an extra crossjoin on ROWS.... i
suppose in a way it would help to see the MDX that excel is producing as well
if someone could point me in the right direction...

i appear to get the most problems when the product level is dragged and
dropped onto rows next to the customer level.

just to re-iterate. the report is to show

for september 2003 and september 2004

qty and margin for
every product, per vendor, per sales team, , per employee in that sales
team, per allocated customer...

things begin labouring when product and customer are there together

"Dennis Redfield" wrote:

is this a MOLAP cube?
How many total rows are loaded into the cube?
What happens if you use the Data Browser in Analysis Manager to repo this
report?
Have you tried using the MDX sample program that comes with AS on this
report?

dlr

"Matt" <Matt (AT) discussions (DOT) microsoft.com> wrote in message
news:5F13B834-38A2-4ED5-BB03-8DC2C53CF2CA (AT) microsoft (DOT) com...
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

#8

you are right, my mistake. i stripped the dimensions down to separate
dimensions as part of my investigations (im not an expert in MDX). is it
possible to "dump" the MDX from queries in Excel, that way i should be able
to provide the exact query that is causing the problem. in the mean time i
will locate the mdx from reporting services

"dpuri" wrote:

Quote:

There's a couple of discrepancies between the MDX query below and the
dimension details you provided earlier:

- The query uses separate Team and Customer dimensions, but the
original post described a combined Team/Customer dimension.

- The time members in the query are at year/quarter level (not Sept.)

Anyway, you might try a NonEmptyCrossJoin() version of the query:

Select
CROSSJOIN
(Hierarchize({[Time].[All Time].[2003],[Time].[All Time].[2004],
[Time].[All Time].[2003].[Quarter 3]: [Time].[All
Time].[2004].[Quarter 2]}),
{[Measures].[Cost], [Measures].[Total]}) ON COLUMNS,
NONEMPTYCROSSJOIN({[Team_ONLY].[Team Name].Members},
{[Customer_only].[Company Name].Members},
{[Product_only].[Product Name].members},
{[Time].[All Time].[2003], [Time].[All Time].[2004]}, 3) ON ROWS
FROM SALES_ATOMIC
where [vendor_only].[vendor name].[veritas]

"Matt" <Matt (AT) discussions (DOT) microsoft.com> wrote

Hi,

The fact table has approx 400,000 rows. the dimension levels have the
figures in brackets () as entered in the original post. Data browser performs
very slowly as well. i have tried using the MDX application and also have the
same issue. it also has trouble displaying crossjoin information in a useful
manner

here is the mdx

select

CROSSJOIN
(
hierarchize({[Time].[All Time].[2003],[Time].[All Time].[2004],[Time].[All
Time].[2003].[Quarter 3]: [Time].[All Time].[2004].[Quarter 2]}),
{ [Measures].[Cost], [Measures].[Total]}
)
ON COLUMNS,
NON EMPTY

CROSSJOIN
(
{[Team_ONLY].[Team Name].Members},
CROSSJOIN
(
{[Customer_only].[Company
Name].Members},
{[Product_only].[Product Name].members}

) ) ON ROWS
FROM SALES_ATOMIC
where [vendor_only].[vendor name].[veritas]

this example is not the worst case imagine an extra crossjoin on ROWS.... i
suppose in a way it would help to see the MDX that excel is producing as well
if someone could point me in the right direction...

i appear to get the most problems when the product level is dragged and
dropped onto rows next to the customer level.

just to re-iterate. the report is to show

for september 2003 and september 2004

qty and margin for
every product, per vendor, per sales team, , per employee in that sales
team, per allocated customer...

things begin labouring when product and customer are there together

"Dennis Redfield" wrote:

is this a MOLAP cube?
How many total rows are loaded into the cube?
What happens if you use the Data Browser in Analysis Manager to repo this
report?
Have you tried using the MDX sample program that comes with AS on this
report?

dlr

"Matt" <Matt (AT) discussions (DOT) microsoft.com> wrote in message
news:5F13B834-38A2-4ED5-BB03-8DC2C53CF2CA (AT) microsoft (DOT) com...
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

#9

well it is certainly not the number of rows and its not excel itself. we
will need to folcus on the MDX cross joins - I will simiulate something
like this when I get a second.

dlr
"Matt" <Matt (AT) discussions (DOT) microsoft.com> wrote

Quote:

Hi,

The fact table has approx 400,000 rows. the dimension levels have the
figures in brackets () as entered in the original post. Data browser
performs
very slowly as well. i have tried using the MDX application and also have
the
same issue. it also has trouble displaying crossjoin information in a
useful
manner

here is the mdx

select

CROSSJOIN
(
hierarchize({[Time].[All Time].[2003],[Time].[All Time].[2004],[Time].[All
Time].[2003].[Quarter 3]: [Time].[All Time].[2004].[Quarter 2]}),
{ [Measures].[Cost], [Measures].[Total]}
)
ON COLUMNS,
NON EMPTY

CROSSJOIN
(
{[Team_ONLY].[Team Name].Members},
CROSSJOIN
(
{[Customer_only].[Company
Name].Members},
{[Product_only].[Product
Name].members}

) ) ON ROWS
FROM SALES_ATOMIC
where [vendor_only].[vendor name].[veritas]

this example is not the worst case imagine an extra crossjoin on ROWS....
i
suppose in a way it would help to see the MDX that excel is producing as
well
if someone could point me in the right direction...

i appear to get the most problems when the product level is dragged and
dropped onto rows next to the customer level.

just to re-iterate. the report is to show

for september 2003 and september 2004

qty and margin for
every product, per vendor, per sales team, , per employee in that sales
team, per allocated customer...

things begin labouring when product and customer are there together

"Dennis Redfield" wrote:

is this a MOLAP cube?
How many total rows are loaded into the cube?
What happens if you use the Data Browser in Analysis Manager to repo
this
report?
Have you tried using the MDX sample program that comes with AS on this
report?

dlr

"Matt" <Matt (AT) discussions (DOT) microsoft.com> wrote in message
news:5F13B834-38A2-4ED5-BB03-8DC2C53CF2CA (AT) microsoft (DOT) com...
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

#10

the plot thickens and maybe i now have some idea of where the problem is.
Following a small amount of research i added a log file entry to the *.oqy
file for my OLAP connection in Excel 2003... i ran the problematic report (by
dragging and dropping) and the MDX excel uses is as follows

SELECT NON EMPTY
CROSSJOIN(HIERARCHIZE(Except({AddCalculatedMembers (Except({AddCalculatedMembers(Except({AddCalculate dMembers(DrillDownMember({Except({AddCalculatedMem bers(DrillDownMember({DrillDownLevel({[Time].[All
Time]})}, {[Time].[All Time].[2003], [Time].[All Time].[2004]}))},
{[Time].[All Time].[2002], [Time].[All Time].[2001]})}, {[Time].[All
Time].[2003].[Quarter 3], [Time].[All Time].[2004].[Quarter 3]}))},
{[Time].[All Time].[2004].[Quarter 4], [Time].[All Time].[2004].[Quarter 2],
[Time].[All Time].[2004].[Quarter 1], [Time].[All Time].[2003].[Quarter 4],
[Time].[All Time].[2003].[Quarter 2], [Time].[All Time].[2003].[Quarter
1]}))}, {[Time].[All Time].[2004].[Quarter 3].[August], [Time].[All
Time].[2004].[Quarter 3].[July], [Time].[All Time].[2003].[Quarter
3].[August], [Time].[All Time].[2003].[Quarter 3].[July]}))}, {[Time].[All
Time].[2002], [Time].[All Time].[2001], [Time].[All Time].[2004].[Quarter 4],
[Time].[All Time].[2004].[Quarter 2], [Time].[All Time].[2004].[Quarter 1],
[Time].[All Time].[2003].[Quarter 4], [Time].[All Time].[2003].[Quarter 2],
[Time].[All Time].[2003].[Quarter 1], [Time].[All Time].[2004].[Quarter
3].[August], [Time].[All Time].[2004].[Quarter 3].[July], [Time].[All
Time].[2003].[Quarter 3].[August], [Time].[All Time].[2003].[Quarter
3].[July]})), {[Measures].[Margin], [Measures].[Qty]}) DIMENSION PROPERTIES
PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY
CROSSJOIN(HIERARCHIZE(Except({AddCalculatedMembers ({DrillDownLevel(Except({AddCalculatedMembers(Dril lDownMember({DrillDownLevel({[Team_Employee_Customer].[All
Team_Employee_Customer]})}, {[Team_Employee_Customer].[All
Team_Employee_Customer].[NHS]}))}, {[Team_Employee_Customer].[All
Team_Employee_Customer].[SALES], [Team_Employee_Customer].[All
Team_Employee_Customer].[PSC], [Team_Employee_Customer].[All
Team_Employee_Customer].[PLT], [Team_Employee_Customer].[All
Team_Employee_Customer].[NEWBIZ], [Team_Employee_Customer].[All
Team_Employee_Customer].[FULFIL], [Team_Employee_Customer].[All
Team_Employee_Customer].[CORP]}), [Team_Employee_Customer].[Acct
Manager])})}, {[Team_Employee_Customer].[All Team_Employee_Customer].[SALES],
[Team_Employee_Customer].[All Team_Employee_Customer].[PSC],
[Team_Employee_Customer].[All Team_Employee_Customer].[PLT],
[Team_Employee_Customer].[All Team_Employee_Customer].[NEWBIZ],
[Team_Employee_Customer].[All Team_Employee_Customer].[FULFIL],
[Team_Employee_Customer].[All Team_Employee_Customer].[CORP]})),
HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({ DrillDownLevel({[Vendor_Product].[All
Vendor_Product]})}, [Vendor_Product].[Vendor Name])}))) DIMENSION PROPERTIES
PARENT_UNIQUE_NAME ON ROWS FROM [SALES]

WHOAH !!!!! where did all the calculated members entries come from, my cube
has no calculated members at all... im assuming (quite obviously) that excel
is creating these in the background for some reason (maybe someone can
explain) id also like to know

a) how to recreate this query more efficiently using pure MDX
b) what can be done to either make excel do the query differently or
optimise the cube/dimensions to help the query run more efficiently

thanks you all for your help

"Dennis Redfield" wrote:

Quote:

well it is certainly not the number of rows and its not excel itself. we
will need to folcus on the MDX cross joins - I will simiulate something
like this when I get a second.

dlr
"Matt" <Matt (AT) discussions (DOT) microsoft.com> wrote in message
news:2EBB829E-9916-494E-9123-0414AB1B750E (AT) microsoft (DOT) com...
Hi,

The fact table has approx 400,000 rows. the dimension levels have the
figures in brackets () as entered in the original post. Data browser
performs
very slowly as well. i have tried using the MDX application and also have
the
same issue. it also has trouble displaying crossjoin information in a
useful
manner

here is the mdx

select

CROSSJOIN
(
hierarchize({[Time].[All Time].[2003],[Time].[All Time].[2004],[Time].[All
Time].[2003].[Quarter 3]: [Time].[All Time].[2004].[Quarter 2]}),
{ [Measures].[Cost], [Measures].[Total]}
)
ON COLUMNS,
NON EMPTY

CROSSJOIN
(
{[Team_ONLY].[Team Name].Members},
CROSSJOIN
(
{[Customer_only].[Company
Name].Members},
{[Product_only].[Product
Name].members}

) ) ON ROWS
FROM SALES_ATOMIC
where [vendor_only].[vendor name].[veritas]

this example is not the worst case imagine an extra crossjoin on ROWS....
i
suppose in a way it would help to see the MDX that excel is producing as
well
if someone could point me in the right direction...

i appear to get the most problems when the product level is dragged and
dropped onto rows next to the customer level.

just to re-iterate. the report is to show

for september 2003 and september 2004

qty and margin for
every product, per vendor, per sales team, , per employee in that sales
team, per allocated customer...

things begin labouring when product and customer are there together

"Dennis Redfield" wrote:

is this a MOLAP cube?
How many total rows are loaded into the cube?
What happens if you use the Data Browser in Analysis Manager to repo
this
report?
Have you tried using the MDX sample program that comes with AS on this
report?

dlr

"Matt" <Matt (AT) discussions (DOT) microsoft.com> wrote in message
news:5F13B834-38A2-4ED5-BB03-8DC2C53CF2CA (AT) microsoft (DOT) com...
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