Home Dashboard Directory Help
Search

queries get slower the more dimensions that are added to the cube by GregGalloway


Status: 

Active


3
0
Sign in
to vote
Type: Bug
ID: 777597
Opened: 1/26/2013 6:24:07 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

Look at the attached cube. Unzip and restore it to SSAS2012 SP1 CU1 or higher. Note it's got one measure group with 3 dimensions in the Adventure Works cube. But note that there are 40 other dimensions, all role-playing dimensions off the same dimension. Note those 40 other dimensions aren't connected to any measure group (or at least not to the measure group in question).

And note the following tests produce the same results for both of the attachments. The CubeAttributeQueryScalabilitySmall.zip contains a backup of a cube where the 40 other dimensions each have one row. The CubeAttributeQueryScalability.zip contains a backup where the 40 other dimensions are about 750,000 members in the key. The determining factor appears to be the number of dimensions or maybe the number of cube attributes in the cube.

Now run this MDX query which should take about 43 seconds if you clear the cache first:
with
member test as
Sum(
[Customer].[Customer Key].[Customer Key].Members
*[Product].[Product Key].[Product Key].Members
,iif(IsEmpty([Measures].[Extended Amount]),null,1)
)
select test on 0
from [Adventure Works]
CELL PROPERTIES FORMATTED_VALUE

Now run this query, the only change being that we're selecting from the LinkedCube cube, which contains the one measure group as a linked object and doesn't contain the 40 other dimensions. If you clear the cache first, this query should run in about 21 seconds, which is twice as fast as the same query against the mega cube. Incidentally, that's the same time as if I deleted the 40 dimensions out of the main cube.

with
member test as
Sum(
[Customer].[Customer Key].[Customer Key].Members
*[Product].[Product Key].[Product Key].Members
,iif(IsEmpty([Measures].[Extended Amount]),null,1)
)
select test on 0
from [LinkedCube]
CELL PROPERTIES FORMATTED_VALUE

This is the simplest repro I could provide. In my real client scenario, we've got a huge enterprise-wide cube with 50 measure groups and about 240 cube dimensions and about 2100 cube attributes (i.e. the row count from MDSCHEMA_HIERARCHIES is 2100 rows). In this real-world cube, the a query which takes about 200 seconds will take only 70 seconds if you remove all the unrelated dimensions from the cube. It's the same scenario as the attached repro cube, just on a bigger, more realistic scale.

It surprised me this calculation didn't run in block computation mode on AS2008 R2 SP2 or on AS2012 SP1. The "Total cells calculated" perfmon counter goes through the roof when you run this calculation. And the fact that this calculation is slow is probably why the overhead of the 40 dimensions is so apparent.

Because that slow query had nothing on rows, it appears it fell back to cell-by-cell. Changing the CalculationAdvancedEvaluatorDisableMask setting to 1 solved the performance problem. But further testing results on a large workload of queries has been mixed. So this isn't a setting that you want to change without thorough testing.

Regardless, this slow cell-by-cell query highlights the underlying scalability issues when you start adding more dimension attributes into the cube space. I hope you will consider fixing this in a future release.
Details
Sign in to post a comment.
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
CubeAttributeQueryScalabilitySmall.zip 1/26/2013 1.01 MB
CubeAttributeQueryScalability.zip 1/26/2013 59.43 MB