May 27, 2008 at 8:31 am
Hi every one,
This is my first post....
I am facing some issues while browsing the cube using excel 2007.
In our cube we have
6 Dimesion tables
1 fact table
the data is very huge around 30 million
There are 8 calculated measures and 7 Dimensions
Each Dimesnion is having 8 attributes with no attribute relation ship.
We are able to browse this using Cube browser and Proclarity but facing problems with excel services.
can any body help on this....
Thanks
Noor.........
May 27, 2008 at 9:17 am
How's the performance while browsing the cube in Visual Studio?
I possibly have a similar issue posted here http://www.sqlservercentral.com/Forums/FindPost506118.aspx
May 27, 2008 at 9:22 am
yeah, the performance is good in Visual studio(In cube browser).
But it will become slow when we increase the levels..
May 27, 2008 at 9:47 am
Excel and Proclarity both use PTS (Pivot Table Services) for access to the AS cube. I would suggest you download the OLAP PivotTable Extensions add-in for Excel:
http://www.codeplex.com/OlapPivotTableExtend
This will allow you to see the produced MDX. Excel does some strange things when it generates MDX for a PivotTable. It often generates everything on rows and pivots the data itself. If also sometimes uses sub-queries where it does not have to. This can be based on formatting options you have selected. You may have to tweak the formatting a bit to help your performance.
May 27, 2008 at 11:36 pm
Thanks for the reply.......
As you suggested, it will be more user friendly(UI) for the excel services but how come it will increase the performance of the cube.
However we are using the SQL profiler to see the MDX queries, where we can have a look what exactly it is doing behind the scenes.....
March 15, 2011 at 7:58 am
Did someone find a solution to this problem?
April 26, 2011 at 8:32 am
Hi,
I found that Excel (2003 & 2007) produced extremely poor MDX queries. It seems to work out the totals on rows and columns (switching this off in the pivot mearly hides the totals and does not supress Excel producing these totals in it's MDX). So if you have many attributes on the rows then the query will be huge
For example I have written a typical query my users wanted. This returns in 4 seconds and gives me 5201 rows and 14 columns
select {[Measures].[Budget]} on columns,
non empty {[Client Details].[RS Name].children}
* {[Client Details].[RS Id].children}
* {[Client Details].[RG Name].children}
* {[Client Details].[RG Id].children}
* {[Client Details].[RM Name].children}
* {[Client Details].[RM Id].children}
* {[Product Banners].[Banner].children}
* {[Salesforce Details].[Region].children}
* {[Salesforce Details].[Division Name].children}
* {[Salesforce Details].[Desk].children}
* {[Salesforce Details].[Sales Area].children}
* {[Salesforce Details].[Sales Desk Region].children}
* {[Salesforce Details].[Person Name].children} on rows
from [Nomura Global Revenue]
If i did this in Excel the query fails. This query errors with a “Exception of type 'System.OutOfMemoryException' was thrown.” error. I ran a trace on the server and found it was running the below MDX
If exclude salesperson from my MDX query it takes 2 minutes 11 seconds and returns 6 rows and 87,175 columns
SELECT NON EMPTY CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(Hierarchize({DrilldownLevel({[Client Details].[RG Name].[All]})}), Hierarchize({DrilldownLevel({[Client Details].[RG Id].[All]})})), Hierarchize({DrilldownLevel({[Client Details].[RM Id].[All]})})), Hierarchize({DrilldownLevel({[Client Details].[RM Name].[All]})})), Hierarchize({DrilldownLevel({[Product Banners].[Banner].[All]})})), Hierarchize({DrilldownLevel({[Salesforce Details].[Person Name].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Salesforce Details].[Person Name].[Person Name].[Leave Date],[Client Details].[RM Name].[RM Name].[RG Name],[Client Details].[RM Name].[RM Name].[RM Asia FI Tier],[Client Details].[RM Name].[RM Name].[RM Europe FI Tier],[Client Details].[RM Name].[RM Name].[RM European EQ Research Tier],[Client Details].[RM Name].[RM Name].[RM European EQ Tier],[Client Details].[RM Name].[RM Name].[RM Id],[Client Details].[RM Name].[RM Name].[RM Japan FI Tier],[Client Details].[RM Name].[RM Name].[RM Us FI Tier],[Client Details].[RG Name].[RG Name].[RG Asia FI Tier],[Client Details].[RG Name].[RG Name].[RG Europe FI Tier],[Client Details].[RG Name].[RG Name].[RG European EQ Research Tier],[Client Details].[RG Name].[RG Name].[RG European EQ Tier],[Client Details].[RG Name].[RG Name].[RG Id],[Client Details].[RG Name].[RG Name].[RG Japan FI Tier],[Client Details].[RG Name].[RG Name].[RG Us FI Tier],[Client Details].[RG Name].[RG Name].[RS Name] ON COLUMNS FROM [Nomura Global Revenue] WHERE ([Measures].[FID Bottom-Up Budget]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
In the end we tried ProClarity - the queries are good but it's not in Excel. We've just started migrating out XLCubed to our users. It's looking like a really good product (I don't work for XLCubed!)
Mack
May 6, 2011 at 1:16 am
The answer is that Excel produces overly complex and generally inefficient MDX. Its primarily designed to fit the somewhat "offbeat" requirements of the Excel Pivot Table - rather than the more erudite and clean matrix grids found in other cube tools. (Good one MSFT!)
You should take a look at some other tools (I noticed a few in this thread already). One we have just taken a deep dive into is Pyramid Analytics. Super cool stuff.
😎
May 6, 2011 at 2:12 am
Thx guys.
I would like to avoid a new tool for our customers.
Does anyone know if Sharepoint 2010 Performancepoint Services would make any difference? or the PowerPivot Component of excel 2010?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply