Here is a situation I ran into recently concerning using a fact table in SSAS.  This experience may help you out if you run into something similar:

A data warehouse has an advertising table that has these four fields: Product Type, Inches, Lines, Pieces. The Inches, Lines and Pieces fields will only have a value in one of the fields, depending on the Product Type:

If Product Type equals “Display”, the Inches field will have a value and Lines and Pieces will be NULL.

If Product Type equals “Classified”, the Lines field will have a value and Inches and Pieces will be NULL.

If Product Type equals “Pieces”, the Pieces field will have a value and Display and Classified will be NULL.

You want to bring all this info into the SSAS cube and into the existing fact table called factRevenue. Here are two scenarios for bringing these fields into the SSAS cube:

1) Create a dimension called DimProductType, and create one field called Ad_Size that will be filled with the Inches field if the Product Type equals “Display”, or filled with the Lines field if the Product Type equals “Classified”, or filled with the Pieces field if the Product Type equals “Pieces”. One measure will be created on the Ad_Size field. PROS: Only one measure, less space, less performance tuning, flexibility to add a new product type with a new ad size (i.e. “Pages”) without having a structure change. CONS: Must always use the Product Type when querying the cube so as not to mix the different ad sizes (but can default DimProductType to one of the Product Types instead of “All”)

2) Create a dimension called DimProductType, and add three fields to the existing factRevenue table. The three fields being Inches, Lines, Pieces. Create a measure for each of these fields. PROS: By not having just one measure as in scenario #1, won’t cause a user to sum apples and oranges by specifying the measure and forgetting to use the Product Type dimension. CONS: Three measures means more space and more performance tuning needed, and if a new product type and ad size was added (i.e. “Pages”) it would mean creating another field and another measure

My solution was to go with option #1.  The concern is that someone would try to add pieces, lines, and inches together since they are in the same measure.  They might not know to use the product type dimension.  But there is no way to get around having to train the users at least a little.  It’s just like if you were tracking revenue, you would not break that up into multiple fields of posted revenue, un-posted revenue, adjusted revenue, budget, actual, plan, etc.  You would have to have dimensions like posted status and scenario and train the users to use those dimensions.