Members or sets in dimensional report

[cogcurious]

I have crosstab report which has products in the rows. Dimension has about 50 of products as members. I need to display about 10 members in the crosstab. Should i put the members individually in the report or use a set function. Set function would have all the members as part of it

[cogcurious]

Anyone

[CognosPaul]

The only reason not to use a set would be if you need to nest different items under each member. Using static sets instead of several members provide better performance and cleaner queries.

[cogcurious]

Thank Paul. I would agree using sets would be cleaner, but, wouldnt using functions effect performance. With that logic using members in the edges would give better performance. I am not sure though

[CognosPaul]

It depends on which functions and how you're using them. Individual members in crosstabs will not provide any performance boost, on the contrary the MDX generated will be (needlessly) more complex.

Consider the two following MDX statements (I'm cleaning them up a little to make them a little more legible):

Code Select Expand


WITH
   MEMBER [Measures].[COG_OQP_USR_COG_OQP_INT_m1noFact] AS 'NULL', SOLVE_ORDER = 1
   MEMBER [Dim Date].[YearMainHierarchy].[COG_OQP_INT_t6] AS '1', SOLVE_ORDER = 65535
   MEMBER [Dim Date].[YearMainHierarchy].[COG_OQP_INT_t5] AS '1', SOLVE_ORDER = 65535
   MEMBER [Dim Date].[YearMainHierarchy].[COG_OQP_INT_t4] AS '1', SOLVE_ORDER = 65535
   MEMBER [Dim Date].[YearMainHierarchy].[COG_OQP_INT_t3] AS '1', SOLVE_ORDER = 65535
   MEMBER [Dim Date].[YearMainHierarchy].[COG_OQP_INT_t2] AS '1', SOLVE_ORDER = 65535
   MEMBER [Dim Date].[YearMainHierarchy].[COG_OQP_INT_t1] AS '1', SOLVE_ORDER = 65535

SELECT
   UNION(UNION(UNION(UNION(UNION(UNION(UNION(UNION(UNION(UNION(UNION(
{([Dim Date].[YearMainHierarchy].[COG_OQP_INT_t1])}, {[Dim Date].[YearMainHierarchy].[Year].&[2005]}, ALL),
{([Dim Date].[YearMainHierarchy].[COG_OQP_INT_t2])}, ALL), {[Dim Date].[YearMainHierarchy].[Year].&[2006]}, ALL),
{([Dim Date].[YearMainHierarchy].[COG_OQP_INT_t3])}, ALL), {[Dim Date].[YearMainHierarchy].[Year].&[2007]}, ALL),
{([Dim Date].[YearMainHierarchy].[COG_OQP_INT_t4])}, ALL), {[Dim Date].[YearMainHierarchy].[Year].&[2008]}, ALL),
{([Dim Date].[YearMainHierarchy].[COG_OQP_INT_t5])}, ALL), {[Dim Date].[YearMainHierarchy].[Year].&[2009]}, ALL),
{([Dim Date].[YearMainHierarchy].[COG_OQP_INT_t6])}, ALL), {[Dim Date].[YearMainHierarchy].[Year].&[2010]}, ALL)
DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON AXIS(0)
FROM
   [forAll]
WHERE
   ([Measures].[COG_OQP_USR_COG_OQP_INT_m1noFact])  CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE


and

Code Select Expand


WITH
   MEMBER [Measures].[COG_OQP_USR_COG_OQP_INT_m1noFact] AS 'NULL', SOLVE_ORDER = 1
SELECT
   {[Dim Date].[YearMainHierarchy].[Year].&[2005]
, [Dim Date].[YearMainHierarchy].[Year].&[2006]
, [Dim Date].[YearMainHierarchy].[Year].&[2007]
, [Dim Date].[YearMainHierarchy].[Year].&[2008]
, [Dim Date].[YearMainHierarchy].[Year].&[2009]
, [Dim Date].[YearMainHierarchy].[Year].&[2010]}
DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY, PARENT_UNIQUE_NAME ON AXIS(0)
FROM
   [forAll]
WHERE
   ([Measures].[COG_OQP_USR_COG_OQP_INT_m1noFact])  CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE


Both are from a crosstab showing the years 2005 through 2010. As it's cube based the page loads instantly - I would need to enable some settings to see exactly how many milliseconds each query took to render and process. However, it is immediately obvious that the first MDX is the heavier one.

The second query is creating a set (with the curly brackets) of the requested years. Simple and to the point.

The first query is taking each member individually, and creating a set by unioning them. To be honest, I was a bit surprised. I was expecting 5 unions at most, but here are 11. Why? Let's see...
UNION(
   UNION(
         {([Dim Date].[YearMainHierarchy].[COG_OQP_INT_t1])}
       , {[Dim Date].[YearMainHierarchy].[Year].&[2005]}, ALL)
   , {([Dim Date].[YearMainHierarchy].[COG_OQP_INT_t2])}, ALL)

So it's unioning a calculated member with each member from the cube. I wasn't expecting that and I'm not entirely sure what it's for. Either way, the query using the static set is much lighter than the one with individual members.