Advanced Topics in MDX - Professional SQL Server Analysis Services 2005 with MDX - page 303

Removing Empty Cells
The number of cells in a cube is the uniquely identifiable space within the
cube. It is the product of the number of members in each attribute of each di-
mension. This is referred to as the cube space. As you can imagine, the entire
cube space can be quite large. Of the entire cube space, the cells which con-
stitute the product of attribute hierarchies of each dimension can potentially
have fact data. However, in a typical cube most of these cells will not have fact
data. For example, take a simple cube that contains dimensions product, time,
and store. Assume the fact table contains IDs for dimensions, product time,
and the sales amount. The product dimension table typically contains columns
pertaining to the dimension, such as product name, product category, product
weight, product color, and discount. The store dimension table would contain
information about the store such as city, state, country, and number of employ-
ees. The time dimension might contain day, month, and year. As the owner of
existing stores you might be interested in looking at the sales of various
products in stores across various time periods every week, month, or quarter
to make a decision on what product lines to enhance to grow your business.
The store manager may be interested in identifying the sales of the products
along with discounts so that he can stock products that sell the most while hav-
ing discounts to maximize the profit of the store. Hence, the types of questions
that might be requested from your UDM might be different based on the user.
Because the cube space is typically quite large, a vast majority of the cells
might be nulls, meaning no data is available. If your queries include attributes
from the same dimension such as sales of products that have 10% discount,
Analysis Services automatically returns the sale of products that have exactly
10% discount. As you learned earlier in this chapter, Analysis Services uses
AUTO EXISTS and eliminates all members in the products hierarchy that do
not exist with the 10% discount member in the discount hierarchy. Hence the
results you get will not contain null values. However, if you query for data
across dimensions, you can end up with several cells that are nulls. Often you
are not interested in the cells with null values and you do not want to retrieve
them in the result set to begin with. Analysis Services provides several func-
tions and keywords that help you in eliminating null values in your result set.
Assume you want to analyze the Internet Sales amount across various coun-
tries for various products. Every product might not be sold in every country and
so you can end up with certain country-product combinations that do not have