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

Moreover, when the data is moved into the cube, there will be no
relationship between the dimension and the facts containing the NULL key,
leading to either processing errors or situations where the user will see
partial data. It is much better to add a new member to the dimension, and
relate the facts with missing key values to that member in our ETL. Although
the Unknown Members feature in Analysis Services does this for us, we will
have more flexibility if we handle the issue ourselves.

• Should we use SQL Server FOREIGN KEYS ? The correct technique to adopt

here is as follows: we should define the foreign keys in the data model and
activate them during debugging, in order to detect errors. When the system
is in production, the foreign key can be disabled in order to speed up the
ETL code.

• Key errors : There should be no key errors at all. If we enable foreign

key checking, then we will end up with a table without key errors. This
is important; even though we can leverage Analysis Services' key error
handling functionality during processing, we advise not using it because
the presence of a key error should be detected and resolved during the ETL
process, as with NULL keys.

Physical database design for Analysis
Services
Apart from the issue of modeling data in an appropriate way for Analysis Services,
it's also important to understand how details of the physical implementation of the
relational data mart can be significant too.

Multiple data sources
All of the dimension and fact tables you intend to use should exist within the same
relational data source, so for example, if you're using SQL Server, this means all
the tables involved should exist within the same SQL Server database. If you create
multiple data sources within Analysis Services, then you'll find that one is treated as
the primary data source; this has to point to an instance of SQL Server (either SQL
Server 2000 or above) and all data from other data sources is fetched via the primary
data source using the SQL Server OPENROWSET function, which can lead to severe
processing performance problems.