Excel MDX asks for extra data

Question

Hello,

The entire user base of my Data Warehouse uses Excel as their Client Tool.  We have found that Excel is VERY inefficient at writing MDX... to say the least.  One thing we notice is that Excel asks for many more attributes than are actually physically requested in the pivot table design.  These fields are always at the end of the ON ROWS clause, as seen below in bold and underlined.

WITH   MEMBER [Financial Location].[Hierarchy - MDSE Channel].[XL_QZX] AS 'Aggregate({[Financial Location].[Hierarchy - MDSE Channel].[Location MDSE Channel].&[Direct],[Financial Location].[Hierarchy - MDSE Channel].[Location MDSE Channel].&[Miscellaneous]})' MEMBER [Product].[Master Item].[XL_Q1X] AS 'Aggregate({[Product].[Master Item].&[99299768],[Product].[Master Item].&[99299653],[Product].[Master Item].&[99296098],[Product].[Master Item].&[99289053],[Product].[Master Item].&[99296949]})' SET [XL_Col_Dim_0] AS 'VisualTotals(Distinct(Hierarchize({Ascendants([Date].[Fiscal Date Calculation].[Trailing 26 Weeks]), Descendants([Date].[Fiscal Date Calculation].[Trailing 26 Weeks]), Ascendants([Date].[Fiscal Date Calculation].[MTD]), Descendants([Date].[Fiscal Date Calculation].[MTD]), Ascendants([Date].[Fiscal Date Calculation].&[Selected Date]), Descendants([Date].[Fiscal Date Calculation].&[Selected Date])})))'  SELECT NON EMPTY CrossJoin(Hierarchize(Intersect(AddCalculatedMembers({[Date].[Fiscal Date Calculation].[Fiscal Date Calculation].members}), [XL_Col_Dim_0])),{[Measures].[AEC],[Measures].[Original Price],[Measures].[Current Price],[Measures].[Net Sales Units - FP],[Measures].[Net Sales Units],[Measures].[Net Sales Dollars],[Measures].[AUR],[Measures].[Total Units ST %],[Measures].[Net Sales Product Margin %],[Measures].[WOH Units],[Measures].[OH EOP Units],[Measures].[Whse OH EOP Units],[Measures].[Whse OH EOP Dollars],[Measures].[OO Units Cum],[Measures].[OO Dollars Cum]}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({DrilldownLevel({DrilldownLevel({DrilldownLevel({DrilldownLevel({DrilldownLevel({[Product].[Hierarchy - Master Item RFS Concept].[All]})},[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Concept])},[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Category])},[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Division])},[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Department])},[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Class])})),Hierarchize(AddCalculatedMembers({DrilldownLevel({[Product].[Item Color Desc].[All]})}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Concept].[Master Item RFS Concept],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Concept].[Master Item RFS Concept Code],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Concept].[Master Item RFS Concept Desc],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Category].[Master Item RFS Category],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Category].[Master Item RFS Category Code],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Category].[Master Item RFS Category Desc],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Category].[MI RFS CONCEPT KEY AS IS],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Division].[Master Item RFS Division],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Division].[Master Item RFS Division Code],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Division].[Master Item RFS Division Desc],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Division].[MI RFS CATEGORY KEY AS IS],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Department].[Master Item RFS Department],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Department].[Master Item RFS Department Code],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Department].[Master Item RFS Department Desc],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Department].[MI RFS DIVISION KEY AS IS],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Class].[Master Item RFS Class],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Class].[Master Item RFS Class Code],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Class].[Master Item RFS Class Desc],[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Class].[MI RFS DEPARTMENT KEY AS IS],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Category Code],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Category Desc],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Collar Neck],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Delivery],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Desc],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Elastic],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item End Use],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Entity],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Fit],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Harmonized Tarrif Code],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Heel Type],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Knits Woven],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Length],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Life Cycle],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Merchandise Flag],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Num],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Pieces per Case],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Primary Content],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Product Type],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Rise],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Season Code],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Season Desc],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Silhouette],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Sizing],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item SKU Group Code],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item SKU Group Desc],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Sleeve Length],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Storage Type],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Stretch],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Styling],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Type Code],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Type Desc],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Vendor Manufacturer Num],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Waist Type],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Weave Knit Type],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Silhouette],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[MI RFS CLASS KEY AS IS],[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[MI SORT] ON ROWS  FROM [J Jill EDW] WHERE ([Date].[Hierarchy - Fiscal].[Fiscal Week].&[2011]&[21],[Financial Transaction Channel].[Hierarchy - Transaction Super Channel].[Transaction Super Channel].&[2],[Financial Location].[Hierarchy - MDSE Channel].[XL_QZX],[Product].[Item Type Desc].[All],[Product].[Master Item].[XL_Q1X])

Is there any way I can stop Excel from being so advantageous??  We're seeing less than optimal performance, and I would assume that bringing back unneeded data is a good place to start looking.

Thanks,

Chris

Answer 1

Hi,

What Excel version are you using and what version of SSAS are you using. Providing these details will help!

Best Regards

Thomas Ivarsson

 

Answer 2

We see this behavior primarily with Excel 2007, some of our users do use Excel 2010, I would have to run tests to state with 100% certainty that this happens in Excel 2010 as well.  The vast majority of our users are still on Excel 2007.

 

We are running SQL Server 2008 Enterprise Edition (10.0.3798.0)

 

Thanks,

Chris 

Answer 3

Someone? Anyone?

Answer 4

Can someone from MSFT please chime in here?  If there is no way to control Excel from asking for extra data that is fine, but I am very frustrated at the lack of documentation on why it is doing this and also the lack of response on this forum, and would really appreciate a response.

 

Thanks,

Chris

Answer 5

Is it an issue on both Excel 2010 and Excel 2007?

BR

Thomas Ivarsson

Answer 6

Yes, it happens in both Excel 2007 and 2010.

 

-Chris

Answer 7

May be you should log this issue into http://connect.microsoft.com/ as bug.It seems to be a bug only.

 

Answer 8

I agree,

Submit this as a bug or open a support case with MS Support.

Can you shortly explain what you have on rows, columns and filters/slicers in that noicy query? How does it look in the pivot table?

BR

Thomas Ivarsson

 

Answer 9

I submitted a bug to MS Support.

 

I have included formatted MDX below;

 

WITH
MEMBER	[Financial Location].[Hierarchy - MDSE Channel].[XL_QZX] 
AS 
'Aggregate(
	{
		[Financial Location].[Hierarchy - MDSE Channel].[Location MDSE Channel].&[Direct],
		[Financial Location].[Hierarchy - MDSE Channel].[Location MDSE Channel].&[Miscellaneous]
	}
)' 

MEMBER [Product].[Master Item].[XL_Q1X] 
AS 
'Aggregate(
	{
		[Product].[Master Item].&[99299768],
		[Product].[Master Item].&[99299653],
		[Product].[Master Item].&[99296098],
		[Product].[Master Item].&[99289053],
		[Product].[Master Item].&[99296949]
	}
)' 

SET [XL_Col_Dim_0] 
AS 
'VisualTotals(
	Distinct(
		Hierarchize(
			{
				Ascendants([Date].[Fiscal Date Calculation].[Trailing 26 Weeks]), 
				Descendants([Date].[Fiscal Date Calculation].[Trailing 26 Weeks]), 
				Ascendants([Date].[Fiscal Date Calculation].[MTD]), 
				Descendants([Date].[Fiscal Date Calculation].[MTD]), 
				Ascendants([Date].[Fiscal Date Calculation].&[Selected Date]), 
				Descendants([Date].[Fiscal Date Calculation].&[Selected Date])
			}
		)
	)
)' 

SELECT 
NON EMPTY 
CrossJoin(
	Hierarchize(
		Intersect(
			AddCalculatedMembers(
				{
					[Date].[Fiscal Date Calculation].[Fiscal Date Calculation].members
				}
			), 
			[XL_Col_Dim_0]
		)
	),
	{
		[Measures].[AEC],
		[Measures].[Original Price],
		[Measures].[Current Price],
		[Measures].[Net Sales Units - FP],
		[Measures].[Net Sales Units],
		[Measures].[Net Sales Dollars],
		[Measures].[AUR],
		[Measures].[Total Units ST %],
		[Measures].[Net Sales Product Margin %],
		[Measures].[WOH Units],
		[Measures].[OH EOP Units],
		[Measures].[Whse OH EOP Units],
		[Measures].[Whse OH EOP Dollars],
		[Measures].[OO Units Cum],
		[Measures].[OO Dollars Cum]
	}
) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME 
ON COLUMNS, 
NON EMPTY 
CrossJoin(
	Hierarchize(
		AddCalculatedMembers(
			{
				DrilldownLevel(
					{
						DrilldownLevel(
							{
								DrilldownLevel(
									{
										DrilldownLevel(
											{
												DrilldownLevel(
													{
														DrilldownLevel(
															{
																[Product].[Hierarchy - Master Item RFS Concept].[All]
															}
														)
													},
													[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Concept]
												)
											},
											[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Category]
										)
									},
									[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Division]
								)
							},
							[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Department]
						)
					},
					[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Class]
				)
			}
		)
	),
	Hierarchize(
		AddCalculatedMembers(
			{
				DrilldownLevel(
					{
						[Product].[Item Color Desc].[All]
					}
				)
			}
		)
	)
) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Concept].[Master Item RFS Concept],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Concept].[Master Item RFS Concept Code],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Concept].[Master Item RFS Concept Desc],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Category].[Master Item RFS Category],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Category].[Master Item RFS Category Code],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Category].[Master Item RFS Category Desc],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Category].[MI RFS CONCEPT KEY AS IS],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Division].[Master Item RFS Division],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Division].[Master Item RFS Division Code],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Division].[Master Item RFS Division Desc],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Division].[MI RFS CATEGORY KEY AS IS],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Department].[Master Item RFS Department],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Department].[Master Item RFS Department Code],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Department].[Master Item RFS Department Desc],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Department].[MI RFS DIVISION KEY AS IS],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Class].[Master Item RFS Class],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Class].[Master Item RFS Class Code],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Class].[Master Item RFS Class Desc],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item RFS Class].[MI RFS DEPARTMENT KEY AS IS],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Category Code],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Category Desc],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Collar Neck],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Delivery],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Desc],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Elastic],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item End Use],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Entity],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Fit],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Harmonized Tarrif Code],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Heel Type],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Knits Woven],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Length],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Life Cycle],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Merchandise Flag],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Num],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Pieces per Case],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Primary Content],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Product Type],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Rise],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Season Code],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Season Desc],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Silhouette],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Sizing],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item SKU Group Code],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item SKU Group Desc],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Sleeve Length],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Storage Type],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Stretch],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Styling],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Type Code],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Type Desc],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Vendor Manufacturer Num],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Waist Type],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Item Weave Knit Type],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[Master Silhouette],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[MI RFS CLASS KEY AS IS],
[Product].[Hierarchy - Master Item RFS Concept].[Master Item].[MI SORT] ON ROWS 
FROM [J Jill EDW] 
WHERE 
(
	[Date].[Hierarchy - Fiscal].[Fiscal Week].&[2011]&[21],
	[Financial Transaction Channel].[Hierarchy - Transaction Super Channel].[Transaction Super Channel].&[2],
	[Financial Location].[Hierarchy - MDSE Channel].[XL_QZX],
	[Product].[Item Type Desc].[All],
	[Product].[Master Item].[XL_Q1X]
)

The first two members are results of multi-select Report Filters.  Furthermore, there were two more single value Report Filters which can be seen in the WHERE clause, in addition to one "All" member.

The Set for the Columns is a multi-select from our Date Calculation attribute (which is a Dimension Calculated Member).  In the actual SELECT statement, you will see this used on the columns along with the desired calculations.

Then, on the rows you will see the attributes and hierarchies which were added to the row label, with no filtering.

Finally, all of the attributes I did not ask for on the Pivot Table are present at the end of the rows.

 

Thanks,

Chris

Answer 10

Hi Chris,

In AdventureWorks sample cube, it is this case. For exmaple, in my excel 2010 pivot table, [Measures].[Internet Order Quantity] and [Measures].[Internet Average Sales Amount] for Columns, [Customer].[Customer Geography] for Rows and [Date].[Calendar].[Calendar Year] for Report Filter. The MDX called by the excel as this:

SELECT
{[Measures].[Internet Order Quantity],[Measures].[Internet Average Sales Amount]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
ON COLUMNS ,
NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer Geography].[All Customers]},,,INCLUDE_CALC_MEMBERS)})
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
ON ROWS 
FROM (
       SELECT (
           {[Date].[Calendar].[Calendar Year].&[2005],[Date].[Calendar].[Calendar Year].&[2006],
            [Date].[Calendar].[Calendar Year].&[2007],[Date].[Calendar].[Calendar Year].&[2010]})
                ON COLUMNS
       FROM
       [Adventure Works]
              )
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

the MDX is got using SQL profiler. In the above excel MDX query, there is no extra data asked for.

But from your description, i am also very curious why the extra data is called by your excel pivot table. I am strugling with this issue. I will update here if there is any progress.

thanks,
Jerry

Answer 11

Hi Chris,

How do you get the excel pivot table MDX query? As i know, the members of Report Filters are always put into SUB SELECT instead of WHERE clause. If possible, please post the content captured using SQL Profiler.

thanks,
Jerry

Answer 12

Hi Jerry,

I run the following query to retrieve the MDX that a pivot table sends to the cube;

 

SELECT
session_id
, session_spid
, session_user_name
, session_last_command
, session_start_time
, session_cpu_time_ms
, session_reads
, session_writes
, session_status
, session_current_database
, session_used_memory
, session_properties
, session_start_time
, session_elapsed_time_ms
, session_last_command_start_time
, session_last_command_end_time
FROM $system.discover_sessions
WHERE	session_status = 1;

We also use SQL Sentry for our SSAS monitoring tool, and this allows me to view the MDX as well.  I see these extra columns in the MDX from both.

 

Thanks,

Chris

Answer 13

Hi Jerry,

When I am back in the office tomorrow I will use SQL Profiler to capture the MDX for a query too.

Thanks,

Chris

Answer 14

Hi Chris,

To capture the MDX query generated by Excel 2007 ro Excel 2010 you can use "OLAP PivotTable Extensions", which is a free tool you can find on codeplex.

The first feeling I looked at your MDX is that there are too many attributes / properties you included in your cube. It would increase your cube space (assume you use MOLAP storage mode) and slow down the performance. I would really suggest you keep your cube structure simple and only keep necessary attributes. If you do need to report those leaf level data, considering set up action so that user can right click and navigate to a reporting service report dedicated to extract leaf level data.

Regards,

George

Answer 15

Hi George,

I actually have "OLAP PivotTable Extensions" as well, I just left that out of my previous posts.  How I wish I could remove many of my attributes, because they are rarely used, however the business keeps claiming that they need them.  I offered the approach of setting the value of "AttributeHierarchyEnabled" to false, which from what I understand would then allow the attribute to only be visible as a property.  Then, when a user right clicks in a pivot, they could select the attributes and also, from what I read, retrieving these is much faster than an attribute.  

However, the business balked at this too... it's madness.  I know that VERY few of the attributes in the cube are actually used... but my hands are tied since the business keeps insisting they need them. 

I have always suspected that this could be part of the cause for the performance, do you think this is why I am seeing the extra columns??

Thanks,

Chris

Answer 16

Dear DW Guru,

My company also uses Excel as a front end very extensively. One thing I found by googling which may help you is a trick to run custom MDX queries directly in Excel. Then you can pivot the results:

 

 

This is how to overcome Excel’s limitations on custom MDX usage. First, create an OLAP connection string like you normally do. Then, in C:\Users\<username>\My Documents\My Data Sources folder, open the connection file in Notepad to edit.

 

Now you just need to change two places. Change the <odc:CommandType> value to "MDX" and <odc:CommandText> value to your MDX statement, similar to:

 

   <odc:CommandType>MDX</odc:CommandType>

   <odc:CommandText>with member a as sum([Recipes].[Campaign-Recipe].currentmember, [Measures].[Sessions CTR])

member b as sum(([Recipes].[UMetricsRecipe].&[Default]), [Measures].[Sessions CTR])

member [Delta] as a-b

select {[Measures].[Sessions CTR], a, b, delta} on 0,

non empty ([Recipes].[UMetricscampaign].children*[Recipes].[Campaign-Recipe].[UMetricsRecipe]) on 1

from [UMetrixCube]

where [Date].[Year-Month-Date].[Year].&[2011].&[1].&[2011-01-06T00:00:00]</odc:CommandText>

 

Save the file and execute the MDX either as new data feed (Data-Existing Connections) or a pivot (Insert-Pivot Table – find the conn string).

The good thing is that now you will be able to change the properties, including MDX queries, from within Excel and plug the results with other Excel formula instead of first executing MDX in Management Studio and then copying the results to Excel to analyze.

 

Hope it helps. It works for me

 

 

Answer 17

Dear Polaro,

 

This is amazingly cool!  I tried it out and it worked just as described.  I think some of the users may be intimidated by this, but our power users could definitely benefit!

 

Thanks for the tip!

Chris

Answer 18

Glad I could help. As mentioned, you can build a pivot on top of the MDX results so your users will not even be aware of the change. If, for instance, all they need is a live dash then it's a way to go

Answer 19

Guys, there's a very, very simple fix for this issue. Right click on the PivotTable and choose PivotTable Options. Then go to the Display tab and uncheck "Show properties in tooltips". Before doing that a query against [Product].[Product] in Adventure Works looked like this:

SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Product].[Product].[All Products]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME
,[Product].[Product].[Product].[Class]
,[Product].[Product].[Product].[Color]
,[Product].[Product].[Product].[Days to Manufacture]
,[Product].[Product].[Product].[Dealer Price]
,[Product].[Product].[Product].[End Date]
,[Product].[Product].[Product].[Large Photo]
,[Product].[Product].[Product].[List Price]
,[Product].[Product].[Product].[Model Name]
,[Product].[Product].[Product].[Product Key]
,[Product].[Product].[Product].[Reorder Point]
,[Product].[Product].[Product].[Safety Stock Level]
,[Product].[Product].[Product].[Size]
,[Product].[Product].[Product].[Size Range]
,[Product].[Product].[Product].[Standard Cost]
,[Product].[Product].[Product].[Start Date]
,[Product].[Product].[Product].[Status]
,[Product].[Product].[Product].[Style]
,[Product].[Product].[Product].[Subcategory]
,[Product].[Product].[Product].[Weight] ON COLUMNS
FROM [Adventure Works]
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

After unchecking that option, it looks like this:

SELECT NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Product].[Product].[All Products]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS 
FROM [Adventure Works]
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

That being said, I doubt this checkbox will make much of a performance difference unless you're connected to the cube over a very, very slow network. And the very rich info in tooltips is usually very, very valuable reference info for users. But please try it and report back some performance stats.

---

http://artisconsulting.com/Blogs/GregGalloway