January 8, 2013 at 4:05 pm
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];
January 9, 2013 at 1:04 am
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
January 9, 2013 at 5:01 pm
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. (?)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply