Combining Dimensions [Reed]

The way AS2005 manages attributes in a dimension changes the design dynamic a bit: Which attributes should be combined into a single dimension? I've run into this issue from two sides.

On the one hand, we had a Project dimension with a lot of changing attributes. For example, the Project Manager would change frequently. To avoid dealing with creating lots of Type II instances of the Project, we decided to pull the Project Manager off as a separate key in the fact, making it a "Rapidly Changing Dimension". In 2000, this was often a very effective strategy. It's simple and easy to implement, but in 2005, we found that query performance was very bad. Even thougy the PM to Project relationship is very sparse, each crosstab query had to do a cartesian product and find the empty intersections. When we moved the PM back into the Project dimension, AutoExist suddenly made it really fast to find the projects for a given manager, or the managers for a given project.

Conversely, I had a client with lots of customers and lots of products, but where the relationship between customers and products was very stable and very sparse. It seemed bizarre, but we explored putting both customers and products into a single dimension, with the root key as the Customer/SKU combination. Again, the query performance gain was substantial, especially when looking for which customers buy which products, etc. In 2000, I would never have considered putting Customer and Product into a single dimension, but given the high cardinality, stable relationship, and inherent sparsity, it seems to be a really good idea in 2005.

- Reed

ByAccount aggregation bug workaround [Joe Kasprzak]

When working with Account type dimensions and semi-additive Measures across a variety of account types with different aggregation functions, you can use the ByAccount aggregation function to set the proper aggregation behavior.

There is a bug that sometimes occurs to the finance measure where the ByAccount option doesn’t properly set various aggregation rules for each account type. This can be manually fixed by the following process:

* Right click the solution in the Solution Explorer window and click “Edit Database” to display the current settings for the finance measures by account type.

The ByAccount aggregation function should have set for each account type to either sum for additive or lastnonempty for accounts which are semi-additive.

* Click the Aggregation Function in the Account Type Mapping box and change the aggregation function from Sum to Lastnonempty for the Flow, Liability, Balance, Asset and Statistical account types.

- Joe