Transformer generates categories incorrectly

[halkosj]

When transformer builds the dimension hierarchies, it excludes any categories that don't exist in the fact table. It should include EVERY category, whether represented by the data or not.  For example, if there were no data in the fact table for Females, then transformer would build a "Gender" dimension which includes only one value - 'M'.  It should include all valid lookup values ('M' and 'F'), not just those represented by the fact table data.

[JGirl]

If you wish for all dimensional values to be incldued in the cube ensure the following:

1.  You have a separate data source for the dimension providing all values
2.  In Transformer, if you go to the 'Dimension Map' pane, and double click on a dimension level and the properties window will appear.  Go to the General tab, and change the 'inclusion' setting from 'Default (when needed)' to 'Always Include'.

You will need to do this for each level in each dimension you wish to always include.

A few words of warning with this approach though:
* It is useful for small dimensions such as gender, but be cautious using it on larger dimensions
* keep an eye on the cube to make sure including the unused categories isnt creating performance or cube size issues
* unless you periodically clean up the model to remove old categories, this could result in values that may no longer exist in your dimensions still being included in the cube.

Hope this helps.
J

PS.  You'll also find an inclusion setting to deal with the (Blank) categories you referred to in your other post, although i'd double check there are no blanks, '', ' ', nulls etc in your source data.

[halkosj]

Okay, we'll try this - but this is very surprising.

We were under the impression that developing a good relational model in Framework Manager was essential to creating a good OLAP model in Transformer. But now you're saying that Transformer ignores the FM optionality/cardinality settings (which is what produces the outer joins to give us ALL dimension values, not just those represented by the fact table).

So I guess we really need to know what are the modeling rules/decisions in an FM relational model which are ignored by Transformer, and therefore need to be re-modelled in Transformer.

We've looked around for documents, white papers, etc. which discuss this, but can find nothing - any recommendations?