Optional relationships in SSAS 2005

rtblog@realtechnology.com — Tue, 15 Jul 2008 21:16:34 GMT

On a recent project using Sql Server Analysis Server (SSAS) 2005, I ran into some challenges with fact tables that had optional relationships (nullable foreign keys) to related dimension tables.

I used a design were FactTableA had a regular relationship to DimensionA and DimensionB, however, these were both optional relationships. The cube functioned correctly until I added a referenced relationship to DimensionC via DimensionA. When I did this, the count of records in FactTableA suddenly dropped to a value corresponding to the count of non-null values for the foreign key referencing DimensionA. The cube was forming an inner join between FactTableA and DimensionC via DimensionA; not what I wanted at all.

The fix was simple, in the "Define Relationship" screen for DimensionC, I unchecked the "Materialize" checkbox. The documentation doesn't mention this impact on the data, it merely mentions that perfomance is improved when the value is checked.

SSAS 2005 nullable foreign keys referenced dimensions

RT-blog

Optional relationships in SSAS 2005