HI Sincfusion,
We are looking for OLAP
component, and your is pretty good. But MDX queries make me sad.
We made some simple query with
filter, and MDX query look like a big ugly monster.
Let me show you example.
SELECT NON EMPTY({[Measures].[Order Count]}) dimension properties MEMBER_TYPE,
PARENT_UNIQUE_NAME ON COLUMNS , NONEMPTY( VISUALTOTALS(
({{hierarchize(Union(Intersect(VISUALTOTALS({
Except(Descendants([Date].[Calendar], 1, SELF_AND_BEFORE) , {[Date].[Calendar].[Calendar
Year].&[2005], [Date].[Calendar].[Calendar Year].&[2006],
[Date].[Calendar].[Calendar Year].&[2007], [Date].[Calendar].[Calendar
Year].&[2008], [Date].[Calendar].[Calendar Year].&[2009]}) }),
Drilldownlevel({ [Date].[Calendar] })) , Except(Drilldownlevel({
[Date].[Calendar] }) , {[Date].[Calendar].[Calendar Year].&[2005],
[Date].[Calendar].[Calendar Year].&[2006], [Date].[Calendar].[Calendar
Year].&[2007], [Date].[Calendar].[Calendar Year].&[2008],
[Date].[Calendar].[Calendar Year].&[2009]})))}}) ),{[Measures].[Order
Count]}) dimension properties
MEMBER_TYPE, PARENT_UNIQUE_NAME ON
ROWS FROM [Adventure Works] CELL PROPERTIES VALUE, FORMAT_STRING,
FORMATTED_VALUE
How this query is in EXCEL
SELECT
NON EMPTY Hierarchize({DrilldownLevel({DrilldownLevel({[Date].[Calendar].[All
Periods]},,,INCLUDE_CALC_MEMBERS)},[Date].[Calendar].[Calendar
Year],INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Date].[Calendar].[Calendar
Semester].[Calendar Year] ON COLUMNS
FROM (SELECT ({[Date].[Calendar].[Calendar Year].&[2010]}) ON
COLUMNS FROM [Adventure Works]) WHERE
([Measures].[Order Count]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE,
BACK_COLOR, FORE_COLOR, FONT_FLAGS
Differences are obvious. Your
mdx designer made it using "EXCEPT". That very tough way for OLAP
CUBE, and requires much resources to execute.
Why is that and can you change
it?
Kind regard, Aleaxander.
