0

I have created a dimensional model which is similar in structure to the financial reporting design in the AdventureworksDW environment, where the value of each account is held as a single value column in the fact table and the dimensions give the data its semantic meaning.

There are over a thousand columns in this model so it works well for adding or deleting additional columns. Here is a really good blog on this design: http://garrettedmondson.wordpress.com/2011/10/26/dimensional-modeling-financial-data-in-ssas/

Although this model works well for querying the dimensional model, and there are examples supporting this model for dimensional analysis, I'm concerned that this model is not standard for cube development or data mining which seem to prefer wider tables.

Questions: Is this design categorized as Entity-Attribute-Value (EAV)?

Would a design using multiple fact tables be better? So many wide fact tables (up to 10) with up to 200-300 columns each, but fewer rows.

Should I expect more performance issues with the much wider tables?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user2704501
  • 31
  • 1

1 Answers1

0

You are right that specific design is considered as EAV model.

By using such a design, you can easily add new accounts, hierarchies etc. You dont need to update your model.

I would not recommend one column per measure aproach. Most account will be null in most of the rows. Also with such a design, you need to read all of your measures even if you need to retrieve only one of them.

We heavily use account dimension in our cubes. Unfortunately things like shared members are not easy to handle in SSAS like in Essbase.

You need to create an Account dimension which is parent-child and also you need to have the key of this account dimension in the fact table as usual. By using account dimension, you get nice support for time balance functionality. Using time balance functionality of SSAS supposed to be faster than custom MDX code.

We are converting unary operators and parent-child relationships to formulas at the moment. So basically we have normal formulas, and parents in hierarchies also works as formulas. At the end we are flattening the hierarchy. So it is not possible to drill down in account dimension. We are using account dimension as a calculation engine only. It is possible to have proper hierarchies as well, but we decided not to mix custom rollup members and unary operators at the same time.

Shared members and all our formulas implemented as custom rollup members.

ebayindir
  • 441
  • 3
  • 10