MXC Cube - No Totals

Paul Wyatt

Is it possible to get a grand total of rows and columns when using an MXC Cube?

 

I want to understand MXC cubes more.  One thing I have found is that I cannot get row and column totals when using an MXC cube and so have to create a duplicate of the MXC cube, setting the datatype to DOUBLE and not MXC.  I then have to create a second datareader to populate this cube due to MXC cubes having to be populated with dedicated datareaders.

 

 

I'm quite happy knowing haw to achieve all that I can with my current processes but if there are alternative methods of achieving this, I'd be very interested in learning of them.  I'd also be interested to learn more of how other users use MXC Cubes. I learnt from Michele Roscelli how to use them to relate and count entities horizontally and vertically across trees and now use them prolifically.

Björn Reuber

Hi,

 

you can get a Total for an MXC cube pretty easy. Just add an Algorithm Poining to the mxc Block with Algortim Type "Total".

BUT keep in mind the value could be different if you have the value on an aggregated level, cause an MXC cube is using an OR for aggreagtion and not an AND MXC data type

 

I just created an MXC cube on Month/Product. Assuming I sell every Product, every month, so it's containing a 1.

Due to the fact that I'm then also selling also every pridct every year, the total for by month and by year would be different.

In my case I've got 33 Products:

by Year:

by month:

 

So in some cases the pure Total doesnt make sense

 

Regards

Björn

Paul Wyatt

Thanks Björn.  What a simply BRILLIANT answer - I would never have considered simply using a block reference on its own.  Your answer also emphasised the need to fully understand what an MXC cube does with the figures (context) over a simple COUNT cube.  This is why I raised the question, to identify the varying properties of each method and so understand their strengths and weaknesses.

 

Can I expand on my question and ask you if you know a method to TOTAL the sub-totals as can be achieved with the algorithm (rt(a) in a COUNT Cube dataview (DV)?

 

In the image below, the DV using the COUNT cube can also be summarised so that the totals can be seen as the first column.  This is achieved with the algorithm rt(b).  This cannot be done with the MXC Cube DV and so the only way to see the grand totals is to use the native total column to the end of the DV - which I've greyed out.  The reason for greying the totals column is that I expressly need the totals column as the first one and I do not want to use 2 separate DVs which must then be carefully aligned to 'appear' as one DV.

 

.

Paul Wyatt

CORRECTION TO IMAGE: The algorithm used is not rt(a) but simply a, as stated in the the solution provided by Björn.  The algorithm rt(a) is used in the top DV as a block to display the TOTAL the sub-totals into a column in the far left.

Björn Reuber

very short question/idea on this (havn't tried it out), did you tried NEXEL to have the total?

Paul Wyatt

I find that anything I design in NEXEL ends up being delivered in SQL + Cube solutions as Best Practice.  This makes explaining methods and functions to the team far easier, as well as creating more easily supported models later on.  That said, NEXEL is excellent and I really do love its functionality.