Database Reference
In-Depth Information
Limitations of calculated members
As we have seen, a calculation dimension does not need to have a relationship with
any fact table. Since all of the values on it will be calculated, there is no need to
have any physical records to hold its data. So we might think that it's right that a
calculation dimension should be made up of only calculated members.
Nevertheless, even if they appear to the end user exactly as real members, calculated
members suffer some limitations in Analysis Services and we need to be well aware
of them before trying to build a calculation dimension:
•
Drillthrough does not work with calculated members. Even if they can
be used to make up a set of coordinates to a cell in the cube, just like real
members, Analysis Services will refuse to initiate a drillthrough action on
a cell covered by a calculated member. Of course, even when we can do a
drillthrough we still have the problem of getting it to return the results we'd
expect, but that's a problem we'll deal with later.
•
There are severe limitations in the security model for calculated members.
These limitations will be described in a later chapter, but basically, you
cannot secure a calculated member role. While you can use dimension
security to secure a real member, you cannot hide the metadata associated
with a calculated member.
•
Some clients handle calculated members in a different way compared to real
members. Even if this is a client tool limitation, we need to be aware of it
because our users will always interact with a cube through a client tool and
will not care whether the limitation is on the client or the server. The most
important client tool that suffers from this problem is Excel 2007, which does
not display calculated members on non-measures dimensions by default, and
which in some circumstances forces you to select all or none of the calculated
members on a hierarchy, rather than just the ones you need. In later versions
of Excel, this issue has been solved.
Therefore, from the point of view of usability and flexibility, calculated members
are second class citizens. We need to create a proper, physical structure as the basis
for our calculation dimension. In other words, we need to create real members, not
calculated ones.
Of course, we are not going to pre-calculate all of the values for our calculated
members and store them in fact table. What we will do instead, is to fool Analysis
Services creating a real member while, under the hood, we overwrite the value of
that real member with the result of an MDX calculation.
Search WWH ::
Custom Search