SQLServerCentral / Data Warehousing / Analysis Services / MDX: Why is MDX so much slower than getting the ALL data from the cube? / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 23:40:24 GMT20RE: MDX: Why is MDX so much slower than getting the ALL data from the cube?http://www.sqlservercentral.com/Forums/Topic1404484-17-1.aspxDo 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. (?)Wed, 09 Jan 2013 17:01:24 GMTWood ChuckRE: MDX: Why is MDX so much slower than getting the ALL data from the cube?http://www.sqlservercentral.com/Forums/Topic1404484-17-1.aspxTry [code="other"]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 ROWSFROM (select [Dim Geographic].[Policy].[STATECODE].&[27] on columns from [Cube]);[/code]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[u]http://www.bidn.com/blogs/DevinKnight/ssis/1099/ssas-defining-attribute-relationships-in-2005-and-2008[/u]There is no time selected in the MDX - is this correct?Have you set aggregations in the cube?Questions, questions....MackWed, 09 Jan 2013 01:04:31 GMTMackersMDX: Why is MDX so much slower than getting the ALL data from the cube?http://www.sqlservercentral.com/Forums/Topic1404484-17-1.aspxI have a pivot grid attached to the cube. The cube has 7 million records and shows all our content really fastThe 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-----------------------------------------------withset [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 ROWSFROM [Cube];Tue, 08 Jan 2013 16:05:02 GMTWood Chuck