Multi-select problem with Excel as front-end

Hello,

I am trying to find a solution to a very elusive problem.

I have reviewed carefully every aspects of my cube as well as my test case and made only little progress.

When using Excel to connect to an AS2005 SP2 cube, if I try to select multiple items in a page selector, I get the grand-total of the cube instead of the total for my sub-selection. What I do not get is that it works for dim attributes with few members and it doe snot with dim attributes with more members.

The most frustrating part is that the bug exist ONLY in Excel 2003 (SP2).

I try the exact same cube in its XL03 and its Xl07 version, for the exact same view and setting I see that Excel is generating quite a different query in 03 and 07.

I have no control over the way Excel create the query. I tried to change things in the cube to coax XL03 to generate a query that works and I cannot get it right.

I cannot use XL2007 in production due to corporate policies.

Is there any way we could get a service pack for XL03, or any way to define the cube to force XL03 to generate correct query?

I cannot reproduce this behavior in the AdventureWorks sample cube, probably because it has not enough data.

Thanks for your kind attention,

Philippe

XL 2007 Correct query is as follow:

Code Snippet

SELECT NON EMPTY
Hierarchize(AddCalculatedMembers({DrilldownLevel({[Weeks].[All Weeks]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM (SELECT ({[PTI3 CD].[All].[PAA],[PTI3 CD].[All].[PAB]})
ON COLUMNS FROM [ST_Crawl])
WHERE ([Data Switch].[Data Switch].&[On],[Exclusions].[Exclusions].&[Enabled],
[Quarters RSD].[Quarter 00].&[200703],
[Products].[All],[Measures].[Crawl RSD]) CELL PROPERTIES VALUE

XL 2003 query not working is as follow:

Code Snippet

WITH MEMBER
[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All].[PAB] , [PTI3 CD].[All].[PAA] } )'
SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]
WHERE ([Measures].[Crawl RSD], [Data Switch].[Data Switch].&[On],
[Exclusions].[Exclusions].&[Enabled], [Quarters RSD].[Quarter 00].&[200703],
[PTI3 CD].[XL_QZX], [Products].[All])

Hi Philippe,

Not a solution, but maybe another data point - does this modification to the XL 2003 query still not work right?

Code Snippet

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers(

{DrillDownLevel({[Weeks].[All Weeks]})}))
DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS

FROM [ST_Crawl]
WHERE ([Measures].[Crawl RSD], [Data Switch].[Data Switch].&[On],
[Exclusions].[Exclusions].&[Enabled], [Quarters RSD].[Quarter 00].&[200703],
{[PTI3 CD].[All].[PAB], [PTI3 CD].[All].[PAA]}, [Products].[All])

|||

I tried your suggestion and get the same issue, it returns the total.

If I try to query the immediate parent level then I get correct result with just one curious clue, the All weeks item would be "Error" but the data I need is just OK in that case as shown bellow. So in XL03 for level 3 it is wrong and for level 2 it is right while the same is right in Xl07 for both level 2 and 3. Very out of my limited undestanding.

This query for level 2 returns correct result. I do not see much difference except for the order of the last segment of the query. i tried all type of things like changing the dimension and hierarchies in every possible way and still no go with XL03.

i took great care of cleaning the underlying data up to perfection in that particular cube however this problem is a problem I am experiencing in many cubes and Despite creating logical keys, set the Cardinality and the RelationshipType as well as getting a rid of all star schema type of dimension, I still hit the wall. Note that level 3 (PTI3) has 217 members and that any level bellow that with more members will fail with the same issue.

I also tried to change the visual total property on the server side and the 2 options available on the Excel side, no luck.

Not that these cubes use calculated members, however even if I try a pure measure it still bug. So what could I do? Create a cube with no calcualte measures and same dimension structure? But still XL07 works while XL03 does not.

Should I post the cube code?

Code Snippet

WITH MEMBER

[PTI2 CD].[XL_QZX] AS 'Aggregate ( { [PTI2 CD].[All].[PB] , [PTI2 CD].[All].[PA] } )'

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]

WHERE ([Measures].[Crawl RSD], [Data Switch].[Data Switch].&[On],

[Exclusions].[Exclusions].&[Enabled], [Quarters RSD].[Quarter 00].&[200703],

[PTI3 CD].[All], [PTI2 CD].[XL_QZX], [Products].[All])

|||"Note that level 3 (PTI3) has 217 members and that any level bellow that with more members will fail with the same issue" - but I thought that PTl3 is a different dimension than PTl2. Could you explain how these "levels" are related, and their structures? As far as the cube script, you could post the sections that relate to the query. For example, if [Crawl RSD] is a calculated measure - and any scoped calculations which apply as well.|||

Guys,

I cannot post the dimension code, too big, however I am getting closer to understand this Excel 2003 problem with cubes.

Here is a query that works very well.

Works

WITH MEMBER

[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All].[PAB] , [PTI3 CD].[All].[PAA] } )'

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]

WHERE ([Measures].[Bill Amt], [Data Switch].[Data Switch].&[On], [Pti2 CD Desc].[All],

[Quarters RSD].[Quarter 00].[All], [PTI3 CD].[XL_QZX])

Here a query which does not work, it returns the grand total. The difference is in red.

Does not work

WITH MEMBER

[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All].[PAB] , [PTI3 CD].[All].[PAA] } )'

SELECT NON EMPTY HIERARCHIZE(AddCalculatedMembers({DrillDownLevel({[Weeks].[All Weeks]})}))

DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [ST_Crawl]

WHERE ([Measures].[Bill Amt], [Data Switch].[Data Switch].&[On], [Pti2 CD Desc].[All],

[Quarters RSD].[Quarter 00].[All],

[Products Hierarchy].[All], [PTI3 CD].[XL_QZX])

The way this section of the dimension is build is as follow.

1 Hierarchy called Products Hierachy with Business, PTI2 CD Desc, PTI3 CD, Product ID

- Product ID is the dimension key, it has an attribute relationship ROOT PART ID rigid, many

Other dimension attributes are

- ROOT PART ID attribute relationship PTI3 CD rigid, many

- PTI3 CD attribute relationship PTI2 CD Desc, rigid, many

- PTI2 cd Desc Attribute relationship Business, rigid, many

- Business

The problem is that if I have both the hierachy and the attributes, excel (2003 only) will include conflictual limits in its query. If I remove the hierarchy it work.

I can use the hierachy when the PTI3 attribute is in the excel however to use PTI3 I must remove the hierarchy...

Users likes both direct access to any attribute and hierarchy browsing, I must educate them not to have both at the same time.

I tried to rebuild the cube from scratch with no calculated measures, no go. I spotted another cube where I have the exact same dimension structure and where it works. I will investigate this to see if there is a difference in the data or structure.

Any idea?|||

One more clue.

If i remove the time dimension from the view, Sub-Select works when both the attribute and the hierarchy are in the cube.

See this

Code Snippet

WITH MEMBER

[PTI3 CD].[XL_QZX] AS 'Aggregate ( { [PTI3 CD].[All Products].[PAB] ,

[PTI3 CD].[All Products].[PAA] } )'

SELECT FROM [ST_Crawl]

WHERE ([Measures].[Bill Amt], [Exclusions].[All Products].[Enabled],

[Data Switch].[Data Switch].&[On], [Products Hierarchy].[All Products],

[Pti2 CD Desc].[All Products],

[PTI3 CD].[XL_QZX])