Extremely slow Excel MDX

Using Excel as a client is most of the time exceedingly slow. For example writing a simple query of the type:

SELECT [Measures].[Some Measure] ON 0,
[Product].[Product-Version].[Product] ON 1
FROM [Cubename]

in Management studio is in Excel transformed to:

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Product].[Product-Version].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Cubename] WHERE ([Measures].[Some Measure])

which takes several times longer to execute. As one starts drilling down it becomes increasingly worse with excel producing MDX that takes 100:s of times longer to execute then if I handwrite the mdx. This is with a very simple cube where Some Measure is not a calculated member. I can't even begin to imagine how slow it would be with a more complex cube. Is there anything to be done about this, any guidelines to follow to make it easer for Excel to generate "normal" mdx?

I had similar problem with Excel and OWC when accessing OLAP Cubes. Unfortunately, unless you optimize your cube, there is nothing can be done with how Excel generate mdx to retrieve data. Excel and OWC is closed code, and recently Microsoft announced that they will be stopping new releases for OWC. Howerver, Excel PivotTable has new version, take a look at Office 2007 in Beta version. It generates more efficient MDXs compared to Office 2003/2000.

Downside, it will take another 2-3 years for Office 2007 to be as popular as Office 2003, so, distribution of your solution in Office 2007 might be an issue if u decide to switch to Office 2007 in larger scale enterprise.

|||Thanks. Is there anything special you have in mind when you say "unless you optimize your cube"? Or do you mean the "ordinary" optimizations one does to make the server work decently fast? I will try to have a look at the 2007 beta though.|||

As mentioned earlier, you can take a look at Office 2007 sending bit different MDX queries. You also take a look at the ProClarity recently aquired by Microsoft see if you get better performance using it.

Also make sure you install latest service pack - SP1. There has been some performance improvements in it. You will see event more performance improvements in upcoming service pack 2. Watch for announcements of Community Technology Preview (CTP) to get your hands on upcoming SP2.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Extremely simple query (I hope) but I can't solve it

Hi, I'm new in MDX and I have a request that I think is extremely simple, but I can't make it.

I have a dimension with a hierarchy, like this

[Dim1]

[Hier_Dim1]

All

Level1

SLevel12

SLevel13

Level 2

SLevel21

SSlevel211

SSLevel212

etc.

I want the values of a measure [Measures].[Total] on the childrens of SLevel21, I write this MDX

SELECT NON EMPTY { [Measures].[Total] } ON COLUMNS

, NON EMPTY { [Dim1].[Hier_Dim1].&[SLevel21].Children} ON ROWS

FROM [My Cube]

Result Set I expect is

SSLevel211 Total1

SSLevel212 Total2

But I have this

Level1 SLevel2 SSLevel211 Total1

Level1 SLevel2 SSLevel212 Total2

I want to use result set on a report that receive as a parameter the member of hierarchy selected, so the number of columns returned should't depend on parameter selected.

Regards

Julio Diaz

What you are seeing here is a "flattened" cellset. I don't know of anyway of suppressing these extra columns that are generated when the results are "flattened" (converted from a multi-dimensional to a 2 dimensional result), but what you can do is to create a calculated member which will give you a consistant column name that you can use in your report. So in the example below you would use the [Dim1_MbrName] column in your report to get the names of the children of the selected member.

WITH MEMBER [Measures].[Dim1_MbrName] AS [Dim1].[Hier_Dim1].CurrentMember.Name

SELECT NON EMPTY { [Measures].[Dim1_MbrName],[Measures].[Total] } ON COLUMNS

, NON EMPTY { [Dim1].[Hier_Dim1].&[SLevel21].Children} ON ROWS

FROM [My Cube]

|||

Thanks Darren, It works good!

Now I′m trying to pass [Dim1].[Hier_Dim1].&[SLevel21] as a parameter, but it is another topics my question was answered :)

Regards

Julio Diaz