Designing the Data Warehouse for Analysis Services - Expert Cube Development with SSAS Multidimensional Models - page 24

SQL queries generated during cube
processing
When Analysis Services needs to process a cube or a dimension, it sends queries
to the relational database in order to retrieve the information it needs. Not all the
queries are simple SELECT s, there are many situations in which Analysis Services
generates complex queries. Even if we do not have space enough to cover all
scenarios, we're going to provide some examples relating to SQL Server, and we
advise the reader to have a look at the SQL queries generated for their own cube
to check whether they can be optimized in some way.
Dimension processing
During dimension processing, Analysis Services sends several queries, one for
each attribute of the dimension, in the form of SELECT DISTINCT ColName , where
ColName is the name of the column holding the attribute.
Many of these queries are run in parallel (exactly which ones can be run in parallel
depends on the attribute relationships defined on the Analysis Services dimension),
so SQL Server will take advantage of its cache system and perform only one
physical read of the table, so that all successive scans are performed from memory.
Nevertheless, keep in mind that the task of detecting the DISTINCT values of the
attributes is done by SQL Server, not Analysis Services.
We also need to be aware that, if our dimensions are built from complex views, they
might confuse the SQL Server engine which will not be able to perform at its best. If,
for example, we add a very complex WHERE condition to our view, then the condition
will be evaluated more than once. We have personally seen a situation where the
processing of a simple Time dimension with only a few hundred rows, which had a
very complex WHERE condition, took tens of minutes to complete. If the views uses
to feed a dimension are too complex, this normally means that the ETL phase is not
populating the dimensions in the right way and it would be better to review the ETL
and produce physical tables which are easier to query during cube processing.
Dimensions with joined tables
If a dimension contains attributes that come from a joined table, the JOIN is
performed by SQL Server, not Analysis Services. This situation arises very frequently
when we define snowflakes instead of simpler star schemas. Since some attributes
of a dimension are computed by taking their values from another dimension table,
Analysis Services will send a query to SQL Server containing the INNER JOIN
between the two tables.