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:

(https://www.wolframalpha.com/input/?i=sum+m+choose+n+for+n+%3D+0+to+m)

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.

StackExchange Database Administrators Q#283758, answer score: 4