Nesting of Dimensions
-
Hi Steve,
Not sure what the right terminology is to use, but here are the questions:
Question1:
I've created a virtual sales cube from actual and target cubes, pulling together qty, value, target, cost measures with calculated members for gross profit & % and discount %. Shared dimensions are customer, region, territory, sales rep, product, time, delivery method, trx type and trx no. The cube works fine, however the customer would like to when dragging and dropping / nesting dimensions next to each other (i.e. region then sales rep then customer) to only display the combinations of dimensions with aggregations in them and therefore filterring out any 'null' records. For example, if there are 10 regions, 10 sales reps and 10 customers; 1000 lines will be displayed (every combination) but only a small portion have valid data. Is there any way to only show these aggregations / filterring out the rest?
Question2:
The document dimension is used to display invoices by region and reps - is this generally a good idea to have such a detailed dimension in a cube? When nesting this dimension next to one or two of the other dimensions in the Analysis Services viewer or other front end apps, the server churns away for a long time and consumes a lot of memory. Please advise / what are the alternatives?
Regards,
Andre
-
For your first problem, I would think this is a presentation issue. A pivottable in Excel handles this issue correctly (I think).
If the UI does not take care of this, the only way around I can see is to write an MDX expression.
-
Noel is right, whatever UI you are using should allow you to filter these nulls out (most likely using either a nonemptycrossjoin function, or perhaps the NON EMPTY keywords).
On the 2nd question, I always try to avoid putting together dimensions like that, although everyone I speak to in MSft says it's ok to do it (they must be using better hardware than me
). An alternative (depending on the abilities of your front end) would be to try and use either drill through (to let users see the records used by AS to construct the summary value) or perhaps member properties, although you would need to have a member property that is like a comma separated list of invoice numbers which may look pretty bad, pluss be potentially hard to maintain. Lastly, we tend to page our data that comes back, so the user can use that dimension but the data is limited to say 200 rows at a time. Be aware that this has disadvantages also.HTH,
Steve.
Steve.
-
Thanks,
Seems Excel is the more flexible frontend (compared to Crystal Analysis that the customer is using). Any ideas on speeding up the OLAP query that runs or reduce the amount of memory used by Excel (150MB>)?
Regards,
Andre
-
Hey Guys,
I have the same problem (Question 1). I know it's a long time since this post (I was searching for a solution).
The thing is we have built our own Front-End application product for displaying the cube data.
What I would want is a way in which Question 1 in Andre's post can be achieved. Since it's nearly two years since the post, I was wondering if there could be any newer solutions anyone might have come up with.
Thanks
Gogula
-
If you've built your own front end then you write your own MDX too? (ie the front end generates MDX to query the cube). If so, then to nest items, you use either crossjoin e.g. crossjoin({set1}, {set2}) or, to answer your question and nest whilst showing only cells with values, then use nonemptycrossjoin (NECJ) e.g. nonemtpycrossjoin({set1},{set2}[,...][,<cj'd set count>])
NECJ is the fastest way to remove empty/null cells from a cellset but does have some limitations (can't work across calculated members). Depending on the complexity of your front end, this function may satisfy your needs.
HTH,
Steve.
). An alternative (depending on the abilities of your front end) would be to try and use either drill through (to let users see the records used by AS to construct the summary value) or perhaps member properties, although you would need to have a member property that is like a comma separated list of invoice numbers which may look pretty bad, pluss be potentially hard to maintain. Lastly, we tend to page our data that comes back, so the user can use that dimension but the data is limited to say 200 rows at a time. Be aware that this has disadvantages also.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply