Sparse data

When modeling for analysis or reporting, it is important to consider the nature of the business questions versus the nature of the data source.

A common scenario is that a relationship between a dimension and a fact table in a star schema is optional. This means that not every dimensional member is mandatory in the fact table. OLAP engines compensate for this by inserting an appropriate value when creating the OLAP structure for any dimensional intersection points that do not have data.

For example, an Analysis Studio user wants to create this report:

Region	2005	2006
Canada		1,000,000
Mexico	500,000	750,000
United States	1,000,000	1,250,000

When modeling, it is common to override optional relationships between dimensions and facts for improved performance. However, when performing analysis or reporting on sparse data where you require information about dimensional members that have no facts, outer joins must be enabled to ensure that data is returned for valid dimensional intersection points.

To enable outer joins, do the following:

Check with your database administrator to ensure that the data source can support full outer joins.
Import metadata with outer joins enabled.