Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX: Why is MDX so much slower than getting the ALL data from the cube? Expand / Collapse
Author
Message
Posted Tuesday, January 8, 2013 4:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 12:41 PM
Points: 8, Visits: 93
I have a pivot grid attached to the cube. The cube has 7 million records and shows all our content really fast

The MDX below produces 217,000 records in 29 seconds (way too slow). Much slower then hooking up to the cube directly. We need to limit what the user sees by specific locations or by state boundaries but I am finding MDX is too slow...

Your guidance is appreciated!!!

----------------------------------------------
MDX CODE for pivot grid to show state, county... locations
-----------------------------------------------
with
set [x] as
{ // this may also be filled with hundreds or thousands of locations...
[Dim Geographic].[Policy].[STATECODE].&[27]
}

SELECT
NON EMPTY{
--([DIMLINEOFBUS].[LINEOFBUSTYPE].MEMBERS, [Measures].MEMBERS),
([DIMLINEOFBUS].[LINEOFBUSTYPE].&[Commercial], [Measures].MEMBERS),
([DIMLINEOFBUS].[LINEOFBUSTYPE].&[Auto], [Measures].MEMBERS),
([DIMLINEOFBUS].[LINEOFBUSTYPE].&[Residential], [Measures].MEMBERS)
} ON COLUMNS,
NON EMPTY {
[x] * //{[Dim Geographic].[STATEABBRV].children} *
{[Dim Geographic].[COUNTYNAME].children} *
{[Dim Geographic].[CITY].children} *
{[Dim Geographic].[POSTALCODE].children} * [Dim Geographic].[LOCATIONID].[LOCATIONID]
} ON ROWS
FROM [Cube];
Post #1404484
Posted Wednesday, January 9, 2013 1:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
Try
SELECT 
NON EMPTY{
{[Measures].MEMBERS} *
{[DIMLINEOFBUS].[LINEOFBUSTYPE].&[Commercial],
[DIMLINEOFBUS].[LINEOFBUSTYPE].&[Auto],
[DIMLINEOFBUS].[LINEOFBUSTYPE].&[Residential]}
ON COLUMNS,
NON EMPTY {
{[Dim Geographic].[COUNTYNAME].[All].children} *
{[Dim Geographic].[CITY].[All].children} *
{[Dim Geographic].[POSTALCODE].[All].children} *
{[Dim Geographic].[LOCATIONID][All].children}
} ON ROWS
FROM (select [Dim Geographic].[Policy].[STATECODE].&[27] on columns from [Cube]);

Do you need all the measures?
In addition have you set the attribute relationships correctly on the Geography hierarchy? Below is a link to this

http://www.bidn.com/blogs/DevinKnight/ssis/1099/ssas-defining-attribute-relationships-in-2005-and-2008

There is no time selected in the MDX - is this correct?
Have you set aggregations in the cube?

Questions, questions....
Mack
Post #1404582
Posted Wednesday, January 9, 2013 5:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 12:41 PM
Points: 8, Visits: 93
Do you need all the measures?
Yes, I need to provide Line of Business in columns of the Pivot Grid.

In addition have you set the attribute relationships correctly on the Geography hierarchy? Yes, I believe they are correct.

There is no time selected in the MDX - is this correct?
Yes, the database is a snapshot in time for the customer. No history.

Have you set aggregations in the cube?
Yes.

Your example query made did help a little but I'm at 26 seconds and need to get down to 2 seconds.

I think one issue may be that my geo. dimension table is has the same number of rows as my fact table. (?)
Post #1405079
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse