Handling Transactional-Level Data - Expert Cube Development with SSAS Multidimensional Models - page 125

The next step is to build the relationship between this dimension and the fact
table. We can use two relationship types: fact and regular . If we choose the regular
relationship type, we have to identify Line Number as the granularity attribute, and
use ID_FactSales for both Dimension Columns and Measure Group Columns in the
Define Relationship dialog box. Choosing the fact relationship type does not require
any further properties to be set. Both relationship types are equivalent in this case. We
might prefer the fact relationship type just because, when looking at the Dimension
Usage tab, it immediately reminds us that we are using the fact table as a dimension.
At this point, it is clear that having a large dimension for transaction details might
be time and resource consuming. The dimension process operation will take a very
long time (similar if not slower than the measure group process!) and it is hard to
optimize in an incremental update since there is no way to partition a dimension. If
we have a few million rows in our fact table, this solution will probably work well
enough. But if we have tens, hundreds, or even thousands of millions of rows in our
fact table, then processing time will likely be too long to be affordable. At this point,
we need to consider some alternatives that can improve processing time.
Drillthrough with ROLAP dimensions
The simplest change we can make to our dimension is on its StorageMode property.
It is set to MOLAP by default, which means that all dimension data is read during
dimension processing and is stored in Analysis Services' own native compressed,
indexed, and optimized format; at query time, Analysis Services only uses this copy
of the data. Usually, this will give us the fastest query response times we can hope
to have.
As an alternative, we can try to change the StorageMode property of the Sales
Order dimension to ROLAP. At this point, we'll see different behavior for both
dimension processing and drillthrough queries. Processing a ROLAP dimension
does not require any SQL queries to be generated to fetch data; it simply empties
the Analysis Services cache. When it comes to querying, we'll see different behavior
depending on the relationship type used between the measure group and the
ROLAP dimension.