Most SSAS solutions are built using fact and dimension tables. If you must develop against a database using the third normal form, you may have to define the necessary objects in your data source views as named queries and/or named calculations. This is relatively straightforward as long as you have the necessary permissions to the underlying objects (tables or views) and you are comfortable using the SQL syntax of a given RDBMS. Additionally, you'll need the parameters for connecting to the relational data source using a .NET or OLEDB provider supplied either by Microsoft or another vendor. Keep in mind that Microsoft will not support third-party providers. If you have any issues during processing or ROLAP queries, you'll have to work directly with the vendor who supplied the driver.

By default, Analysis Services uses a read-committed transaction isolation level. With this level enforced, you should never experience data consistency issues. You could improve processing performance by using a read-uncommitted transaction isolation level, because the relational database wouldn't have the locking overhead while reading the fact and dimension table data. To ensure that each processing command reads uncommitted data, you can edit the cartridge corresponding to the relational data source. For example, you can reference a knowledge-based article: http://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. Furthermore, 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 relational source—using query hints in subqueries is not permitted.

Analysis Services' processing performance largely depends on how fast you can execute 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 offer 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 processing 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 performance perspective, it is always preferable to build a true Star schema model instead of using a normalized database model.

You may also run into processing performance issues if you have a very large number 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 combine some of the dimensions, build necessary attribute relationships, and use role-playing dimensions whenever appropriate.