Database Reference
In-Depth Information
In all cases, a drillthrough query on a ROLAP dimension will produce a complex
SQL statement that will join the fact table with all the dimensions included in the
cube, filtering data with the same criteria specified in the drillthrough query in MDX.
This is, unfortunately, necessary to ensure that Analysis Services returns correct
results. However, this is extremely expensive. We could try to index and optimize
the relational tables, but it will be hard to avoid complete scans of the fact table for
all possible queries. Only extremely clever partitioning of the fact table could give
acceptable results. As a result, think carefully before using ROLAP dimension in
large cubes for drillthrough operations, because query response times might be very
long and will consume a lot of resources on the relational source database.
Our personal opinion is that we recommend you do not use
drillthrough with ROLAP measure groups and/or dimensions and
large volumes of data. It's really not worth the pain! Look for some
kind of workaround, such as Reporting Services reports linked to the
cube with reporting actions.
Moreover, if we decide to use ROLAP dimensions anyway, pay attention to the
relationship type used with the measure group. In this situation, it is much better to
use the fact relationship type. This is because, if we use a regular relationship type,
before the time consuming query we just talked about, a
SELECT
DISTINCT
statement
over the whole fact table (without filters) will be executed just to populate the
possible dimension values in memory. It would be the same as the
SELECT
statement
executed to process a dimension with
ProcessingGroup
set to
ByTable
, and it could
fail if there is too much data to process.
Drillthrough on alternate fact table
As we said before, there is another option for storing the information to be queried
using drillthrough, but it can be used only on numeric attributes. We can add
measures to the cube, making them not visible in the cube metadata and so not
visible to the end users. We can also add these measures in a separate measure
group, so that they can have a different storage mode.
These special measures cannot be aggregated and for this reason, they should
have their
AggregateFunction
property set to
None
(which is available only in the
Enterprise edition). This approach might seem strange, but it is common to separate
measures built on degenerate dimension attributes from cube measures because they
have a different meaning and the main measure group is faster, if it does not contain
useless measures (smaller is always faster).
Search WWH ::
Custom Search