fleaster wrote:Hi all,
i happened to read in one of bedrocktm1's white papers that it was bad practise to do this because of the effect on performance - however, because im feeling particularly lazy and dont feel like creating a whole separate cube for commentary on financials, I was considering combining numbers + comments in the same cube... :p
So just wondering from people's experience
how bad the effect on performance actually is to do this...?
How bad the performance impact?
It all depends
- what is the size and density of the last dimension that contains the N and S elements?
- what is the difference in size and density between the last dimension (fixed in position by virtue of string data) vs the dimension that would otherwise be optimally last?
- how big is the cube?
- how sparse is the cube?
- what is the complexity of rules?
- are the strings data only or a mixture of data entry and rules?
The reason it's a sub-optimal (depending on circumstance) to mix N and S data types is that once string data is stored it is then no longer possible to optimize the cube as the last dimension is fixed in position. Although you can in theory still "optimize" the order of the remaining dimensions there's not really much point in doing so due to diminishing returns as about 90% of the possible optimization benefit is from the correct last dimension, 9% from the 2nd last, 0.9% from the 3rd last ... and then who cares (actually who cares after the 1st dimension). That's why it's a "bad" practice.
Certainly for any large cube that needs to be optimized for reporting it's not a good practice to mix in string elements. But for smaller cubes or cubes with only 2 or 3 dimensions it really makes no difference at all. And sometimes (like when building applications in Contributor) there may simply be no choice.
