# CUBE and calculating the number of dimensions

if a data presented with `4-dimension` in which each dimension is dependent to hierarchical 3-level aggregate like (country, city, street), then we can summarize it into `4096` ways!

we know for cube with `n` dimension in which none of dimension is hierarchical we have `2^n` summarizing ways, but in this case that each dimension has hierarchical 3-level aggregate have `4^4=256` ways.

Why does the above statement mention that there are `4096` ways?

if I couldn't state my problem very well because of a flaw in my English usage, please see:

This is my formula, however my answer is very different from 4096!

It seems like the "hierarchical dimension"s example given as (year, month, week) is treated really the same as 3 independent dimensions - all 8 possible combinations are allowed - that implies they are independent/orthogonal.

So if you have 4 of these triplet dimensions, that's the same as 12 independent dimensions.

But in any case, the answer is basically the same.

The number of possible combinations of 0 to m possible choices of m independent dimensions is a sum of the binomial coefficients and Wolfram helpfully even simplifies that for us:

This is where the 8 possible combinations come from for 3 dimensions and shows why that list contains combinations with between 0 and 3 elements.

You can also look at that as a binary choice for each dimension - we know you can either choose to include or exclude a certain dimension, so you have 2 choices for whether each dimension is included or not, so you have 2 choices for the first, 2 for the second etc. The product of all those is `2 * 2 * ... = 2^n`

So for 12 dimensions, that is `2^12` or 4096.

If you treat it as 4 of the fatter dimensions (basically going from binary choices to octal choices), then the number of combinations in the product is 8 for each hierarchy, so you have 8 combinations possible of the first "dimension", combined with 8 combinations of the second "dimension", and so on, giving `8 * 8 * 8 * 8 = 8^4 = 4096` - and you can simply just view this as regrouping of the 2s into groups of 3.

I don't want to be remiss in pointing out that, in practical terms, many times hierarchical dimensions are not grouped in arbitrary ways as if the component dimensions are independent.

After all, they are in a hierarchy for a reason!

I notice even in the example given with (year, month, week), the (month, week) combination is pretty weak - because I am assuming that week is week number within the year. That aggregating would be for all years, and the weeks don't fit nicely into months, so is probably pretty rare for analysis.

There are many cases of 3-hierarchies where you would only be interested in grouping (d1, d2, d3), (d1, d2), (d1), () - never leaving out a parent - (country, state, city, street, ...) is a good example - a real state entity doesn't really exist as a concept independent of the country-state combination.

• Thanks for your times. so you means 256 is absolutely wrong. am I right? – Betty Andersson Jan 22 at 17:37
• @BettyAndersson, I believe 256 is wrong if you can choose any number of combinations of the 12 different dimensions, yes. – Cade Roux Jan 22 at 18:08

Welcome to DBA.SE. The question is more of a mathematical problem, than a database one. It might be better suited on a different site.

However, `8 ^ 4 = 4096`. Or the `CUBE` (3-level aggregate) to the power of the possible `ROLLUP` values is the number of possible variations which is then `4096`.

DISCLAIMER
I am no mathematician. If you want a thorough explanation, then you will have to post on a different site.