Crosstab - Hierarchy Levels Displayed in Differing Order

[adam_mc]

In a dimensional package (based on a Transformer Cube), I have a Product Hierarchy (Buyer/Department/Class).

If I produce a Crosstab as follows, it works perfectly and performantly:

                         Measures
Buyer|Dept         999.99          999.999


However, when I wish to display (based on business requirements) the crosstab in an order that differs from the hierarchy (as below), performance "stinks" to the point where the user cancels:

                         Measures
Dept|Buyer         999.99          999.999           

I have created a workaround by adding the Buyer as an attribute (Member Description) in Transformer of the Department level.
The crosstab then looks like this:

                                                          Measures
Dept|Member Description - Dept         999.99          999.999       

This works, but seems an illogical way to handle.
Does anyone have any better solutions?

Thanks in advance,
Adam.           

[MDXpressor]

Products can be a tricky dimension in a cube.  Many companies have thousands of products in their offering.  Can you tell me how many levels are in the dimension, and how many leaf level members there are?  How big is the cube in MB?  Are you using any filters in the query, either in the Detail Filter section of the query, or as a function 'filter()'.  Filters are not the way to isolate data in a Powercube, and can destroy your query performance in a hurry.

Powercubes are great when the number of members are few, and the cube size is small.  When you start getting into the 1000's of members, performance will drop, and quite possibly significantly, especially if you are querying against the leaf level.

[Lynn]

Perhaps an alternate drill path, with class as the convergence level, makes sense to consider?

[adam_mc]

MDXpressor...

Thanks for the response.

I have one Product Hierarchy which contains 2 Dimensions as follows:

• Division/Department/Class - 25/700/3000 members in each level
• Buyer/Department/Class - 50/700/3000 members in each level

I know these numbers aren't ideal for Transformer Cube, but this works for us at this time.
Neither goes to SKU level as we have 1.8Million SKU's.

Cube size is quite small 74Mb.

The report does contain detail filters - Before this starts a stream of comments, I am aware of the ongoing debate on the use of detail filters in a dimensional model.
Again, this works for us.

Detail filters are based on values of optional prompts for Division and Buyer.

Note: 
This cube was one of my very first attempts when very new to Cognos.
We have a number of critical executive reports running off of this cube, so I do not plan on redesigning at this time.

Any thoughts will be greatly appreciated.
Thanks in advance,
Adam.

[adam_mc]

Lynn...

I believe that is what I already have.

See my response above to MDXpressor which details the hierarchy and its two dimensions.

Thanks in advance,
Adam.

[MDXpressor]

Adam,
I can appreciate your desire to stay away from design changes.  And now that I have little better understanding, I have more questions than answers...

Is Dept in the in the 2nd levels of the 2 dimensions the same data?  If so, that would probably indicate a need to separate the Dept/Class data into it's own dimension.  I know you don't WANT to change the design of the cube, but, breaking hierarchical structure is actually a pretty fair burden on the BI.  In fact, I'm thinking you must have circumvented some warnings to even accomplish this.  The thing of cubes is they don't have to follow the tabular structure that your DW like has. 

Consider a Real Estate Deployment analyzing Building Efficiency.  From one source, we would get all the political boundaries (Country, Province/State, City, Building Name), as well as the building status (open/closed), Facility Type...

We store all of that data in a single table in our DW because these are truly attributes of the building. However when we build our cube, we might break that into 3 or maybe 4  dimensions(Geography, Status, Type). 
We may also build a bunch of cubes off the same DW, each with a particular function in mind.  Powercubes are cheap and easy (not like that girl I'm trying to date).  We build them like pulp fiction to serve specific needs, and sometimes we may build a cube to serve only that one purpose. 

What I might suggest, since altering your current cube would represent a risk to the current/ongoing analysis, a 2nd cube should be fairly easy to create:  take the existing model save as new model, and Pull the Dept, and Class data items into a new dimension.  Then rebuild your cube.

By having them in separate dimensions you do not break hierarchal sorting.  Each hierarchy is sorted independently of each other, and reversing the nesting does not affect the sorting processing.  It may be worth it to construct the cube this way if for no other reason than to test your cubes performance.  Right now you are not isolating the problem to any one part of the solution.  It could the could that's performing slowly, it could the your deployment, it could even be latency on the user side.  By doing a test cube build you can compare apples with apples

I hope this makes sense to you...

...

[adam_mc]

MDX...

Thanks for your your response.
As I said initially, I already have a "workaround" solution, but I am always trying to learn better and new techniques.

I do understand the concept of splitting into separate dimensions and I see that would work for this particular report use.
However, in other use cases for the same cube, by splitting, wouldn't I lose the ability to drill-down through the full hierarchy?
Or, are you suggesting, keeping the dimensions I have for drill down purposes and adding the new ones for report use cases?

In answer to your questions; Yes, Dept is the 2nd level of both dimensions.
However, our data is as such.

For the Division/Dept/Class dimension:
A Division contains a unique set of Departments, and a Department contains a unique set of Classes (so Class 100 only exists within Dept 10 which only exists in Division 1)

For the Buyer/Dept/Class dimension:
A Buyer contains a unique set of Departments and (as above) a Department contains a unique set of Classes (so Class 100 only exists within Dept 10 which only exists in Buyer John Smith)

However, Buyer John Smith can have products in multiple Divisions.
Hence, why I created two dimensions to begin with (albeit within the same hierarchy).

I'm not aware of any warnings in my Transformer Model.

Thanks again for your feedback.
I am very grateful.
Adam.