Hitachi Consulting BI Blog

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

DSV joins vs. Dimension Usage [Reed]

I had a client who added a new column to a table in the DSV and then coverted existing joins from a different column to the new column. (e.g., OldDateKey --> NewDateKey). Because the DSV was changed to reflect the new join, they assumed that everything would properly. But the cube kept using the old join. The problem is that the DSV only shows potential pathways. The "real" joins between the fact table and the dimension table take place in the Dimension Usage tab. Without changing the join column in the Dimension Usage, changing the DSV doesn't do anything. The reason has to do with role-playing dimensions. In the DSV you can have multiple join possibilities--the classic example is from fact table OrderDateKey, ShipDateKey, DeliveryDateKey columns to a single DateKey in the time dimension. Any of these joins is "possible", but for a specific cube dimension, you need the Dimension Usage to specify the actualized relationship. So, get the DSV right, sure. But don't forget that the Dimension Usage is where the real "joins" take place. - 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