Miscellaneous Analysis Services Topics - SQL Server Analysis Services 2012 Cube Development - page 417

lational data source. For example, you can reference a knowledge-based article: ht-
tp://support.microsoft.com/kb/959026 for instructions to read the uncommitted data
in db2 data sources. Please note that if you alter the cartridge, every statement will
read uncommitted data—you won't be able to control it for individual statements. Fur-
thermore, you cannot add query hints to SQL statements used in the named queries
or partition definitions. This is because Analysis Services uses such SQL statements
as subqueries and writes an outer query prior to sending the statement to the rela-
tional source—using query hints in subqueries is not permitted.
Processing performance issues
Analysis Services' processing performance largely depends on how fast you can ex-
ecute queries against the relational data source. If you're limited to read-only access,
you need to speak with the DBAs and see if they could review the query execution
plans and come up with necessary indexes. Bear in mind that SSAS does not of-
fer much flexibility in how the processing queries are written—you need to capture
the progress report begin and progress report end events in SQL Profiler during pro-
cessing to obtain the SQL statements. As discussed in the previous chapters, you
can adjust the ProcessingGroup dimension property and use the ByTable option
instead of the default ByAttribute option to run only one query while processing
the dimension; by default, SSAS will run one query per attribute. You can also adjust
queries defining each partition as necessary. To maximize processing performance,
you should try processing as many partitions in parallel as possible. From the per-
formance perspective, it is always preferable to build a true Star schema model in-
stead of using a normalized database model.
You may also run into processing performance issues if you have a very large num-
ber of dimensions and measures in your fact table. In some Star schema models,
each new attribute is celebrated with a separate dimension. This approach is simple
to implement results in very wide fact tables. In fact with such a data model each row
read during processing might require more than one buffer. If this is the case, you
should try splitting measures into different measure groups. Better yet, try to com-
bine some of the dimensions, build necessary attribute relationships, and use role-
playing dimensions whenever appropriate.