Named Set Fails in Excel

[greenbergjosh]

Hello,

I am using the FoodMart schema to test Mondrian and how it interacts with Excel and Saiku. There is one named set in the schema.

TopCount([Warehouse].[Warehouse Name].MEMBERS, 5, [Measures].[Warehouse Sales])

I also created another named set for testing

{([Product].[Drink]),([Product].[Food]),([Product].[All Products])}

Both NamedSets are in the Warehouse Cube element.

Both NamedSets fail to display in Saiku.
Both NamedSets do display in Excel, but neither work.

To avoid any confusion, this has nothing to do with my JustFoodAndDrink NamedSet. All of the same problems occur with only the Top Sellers NamedSet that comes with the sample FoodMart schema.

When I try to add either the JustFoodAndDrink or the Top Sellers NamedSets to my Excel pivot table, I get the following error.

mondrian.olap.MondrianException: Mondrian Error: Sytax error at Line 1, column 116, token 'CELL' at mondrian.parser.JavaccParserValidatorImpl.convertException(JavaccParserValidatorImpl.java:99)

If I look at the logs, I see that when I used the JustFoodAndDrink NamedSet, this is what gets sent to Mondrian:
SELECT NON EMPTY Hierarchize({[[JustFoodAndDrink]]} ) DIMENSION PROPERTIES HIERARCHY_UNIQUE_NAME ON 0 FROM [Warehouse] CELL PROPERTIES CELL_ORDINAL

You will notice that there is no Measure selected. So, it just pops an error dialog and I cannot use the NamedSet.

If I modify the MDX to be this (and just manually run the MDX via Saiku, since Excel will not send the right MDX):
SELECT NON EMPTY {Hierarchize(Distinct({[JustFoodAndDrink]}))} DIMENSION PROPERTIES HIERARCHY_UNIQUE_NAME ON 0 FROM [Warehouse] WHERE ([Measures].[Store Invoice]) CELL PROPERTIES CELL_ORDINAL

Then it works fine. The only change I made was to add WHERE ([Measures].[Store Invoice]), which is obviously required.

Finally, if I use Excel to create a NamedSet, which means it is not part of the mondrian cube schema but created in Excel using their menus, then I can include the named set in my pivot table and it works fine. Excel sends the following to Mondrian:
WITH SET [FoodDrink] AS '{([Product].[Drink]),([Product].[Food]),([Product].[All Products])}' SELECT NON EMPTY {Hierarchize(Distinct({[FoodDrink]}))} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Warehouse] WHERE ([Measures].[Store Invoice]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Essentially just using the WITH clause.

So, in summary:

1. The NamedSet in the Mondrian schema does not show up in Saiku
2. The NamedSet in the Mondrian schema shows up in Excel, but does not work (as detailed above)
3. There does not seem to be hanger dimensions or another viable approach.

I have also tried settings including:
mondrian.rolap.aggregates.Use=true
mondrian.rolap.aggregates.Read=true
mondrian.native.topcount.enable=true
mondrian.native.filter.enable=true
mondrian.rolap.ignoreInvalidMembers=true
mondrian.rolap.EnableRolapCubeMemberCache=true
mondrian.olap.elements.NeedDimensionPrefix=false

Nothing seems to make Mondrian NamedSets work with any tool that I can find. Separately, though similarly, there seems to be no way to create a calculated member on a non-measure dimension.

I am using the latest builds of everything and the default foodmart schema and data. This should not be a user error, as I have only used all defaults and it does not work.

I'd be grateful if anyone can point me in the right dimension or simply confirm that NamedSets in a Mondrian schema, calculated members on non-measure dimensions, and hanger dimensions are all incompatible with any user interface (or in the case of hangers, simply no longer available).

Thank you very much.