Disappearing Members in Local Cubes
One of the characteristics of local cube technology in SQL Server 2000, as developed by Microsoft, is that any members that have no keys in the fact table are omitted from the local cube. I suspect this is done primarily to save space. This behavior is fine for many situations, but in some situations it gives very poor results.
(NOTE: This is not a problem with AS2005 local cube files.
All members of all dimensions are included, whether or not they have any data.)
There is a way to work around this problem, which works in some, but not all,
situations. The solution requires adding a record to the fact table for each
missing member. This record can have a zero value for all measures, so that it
doesn't change any values.
This work-around is not very difficult, if the following are both true:
1. You are only using the Sum Aggregate Function in your measures.
2. You are only slicing on a single dimension.
If you are using other Aggregate Functions, you probably will not be able to
obtain a completely accurate result. If you are slicing on multiple dimensions,
the solution just takes more effort.
Here's an example of how to use this work-around in the Warehouse cube from the
FoodMart 2000 sample database. This database does not have any records for
Canada and Mexico in 1997, unless it has been modified since installation.
View the problem:
1. Open the Analysis Manager, right-click on the Warehouse cube from FoodMart
2000, and select the OLAP Client Manager Add-in.
2. Go to the Members tab and select the Time dimension.
3. Choose the third Cube Choice option - Include all selected members in cube(s).
4. Choose the Year level and put 1997 in the Included Members box.
5. Push the Create Cube(s) button.
6. When the local cube has been created, press the View Cube button. The local
cube should open in the Local Cube Viewer. Examine the Store and the Warehouse
dimension. At the Country level, they will each have only one member - USA. Both
the Canada member and the Mexico member will be removed from the local cube,
because they have no data for 1997.
Fix the problem:
1. Open the relational database on which FoodMart 2000 is based. In a default
Analysis Services 2000 installation, this will be a Microsoft Access file
located at:
C:\Program Files\Microsoft Analysis Services\Samples\FoodMart 2000.mdb
2. Open the inventory_fact_1997 table and add the following two records, one of
which adds a value for Mexico in 1997 and the other adds a value for Canada in
1997:
Record #1
product_id 1 (Any valid product_id from the Product Dimension can be used.)
time_id 367 (Any valid time_id referencing a date in 1997 can be used.)
warehouse_id 20 (Any valid warehouse_id for a warehouse in Canada can be used.)
store_id 20 (Any valid store_id for a store in Canada can be used.)
Set all the other fields (all the measures) to a value of 0.
Record #2
product_id 1 (Any valid product_id from the Product Dimension can be used.)
time_id 367 (Any valid time_id referencing a date in 1997 can be used.)
warehouse_id 12 (Any valid warehouse_id for a warehouse in Mexico can be used.)
store_id 12 (Any valid store_id for a store in Mexico can be used.)
Set all the other fields (all the measures) to a value of 0.
3. Close the inventory_fact_1997 table and do a full process on the Warehouse
cube.
4. Follow the instructions above for viewing the problem. You will see that the
local cube now contains both the Canada and the Mexico members.
A few other things to think about:
1. This solution only works when Sum is used as the Aggregate Function for all
the measures in the cube. It does not work if you have measures that use Count,
Distinct Count, Max, or Min as the Aggregate Function. If you have Sum measures,
none of the values displayed in the cube will be changed by the extra records in
the fact table, but they will be changed if you are using the other Aggregate
Functions. The only way to work around this additional problem would be to use
Calculated Members, so that the newly entered fact table records would be
ignored. But it can be hard to get these calculated members to work correctly.
The other option is to inform your users that Count members can sometimes
overstate the real results. In the case of the Warehouse example, the most they
would be overstated is by 2.
2. If you look at the data for the Product Family level of the Product dimension
in the local cube (after adding the two additional records) you will see that
Drink is displayed as 0 for stores or warehouses in Canada and Mexico, while
Food and Non-Consumable are displayed with a blank cell. In many OLAP client
tools you can choose to display null values as 0's. If you do that you will see
a consistent display for these different members.
3. Similarly, most OLAP client tools allow you to display or hide empty rows or
empty columns. The record for Drink in 1997 for Canada and Mexico now has a
value of 0, so it will not be hidden, even if the user chooses to hide empty
values.
4. If you return to the Members tab and choose to remove more of the local cube,
this work-around may become ineffective. For example, if you choose to only
display Food in the Product dimension, along with only displaying 1997 in the
Time dimension, the Canada and Mexico members will again disappear, because we
only added records for them that had a member in the Drink member of the Product
Family level. If you want to create local cubes with additional slicing, you
have to add records to the fact table so that there is one for every combination
of slices that you are creating. For example, if you were creating cube slices
for each of three members of the Product Family, you would need to add 6
records:
Mexico Food
Mexico Drink
Mexico Non-Consumable
Canada Food
Canada Drink
Canada Non-Consumable