Separate Dimension or Hierarchy

[CiskoWalt]

Hello,

My question:
When is it better to create a dimension rather than a hierarchy within a dimension?
What are the trade-offs?
Will concatenating codes values make it difficult to drill-down to relational data in the source system?
Should the depth of the hierarchy be one of the deciding factors? By depth I mean the number of child elements owned by
a parent. If the majority of the parent accounts only one child then then the hierarchy is not useful.

An example of concatenating code values:

We have 5 segments in our Oracle Financials application.

Entity
Dept
Major Account
Performance code
InterCompany code

3 dimesniosn were created from these 6 code values:

Dim1 : Entity
Dim2 : Dept
Dim3: GLACCT [Major Account] - [Performance code] - [InterCompany code]

The three code values are contatenated to create one element in the GLACCT dimesion.

Major:
Major - Performance Code
Major - Performance Code - Intercompany code

008001 - Cash
008001-0000000 - Cash : Default
008001-0000000-000 - Cash : Default : Default

There is little value in this hierachy, since each parent only contains one child record. If the majority of the accounts are set up this way, would it be better to have three dimesnions rather than the hieratchy?

Thanks,

Walt

[David Usherwood]

Who recommended combining 3 Oracle segments in a single dimension? It's rarely a good idea.

[tomok]

Who recommended combining 3 Oracle segments in a single dimension? It's rarely a good idea.

Well.....it depends on what you are concatenating. In this example, speaking from my accounting background, I see very little to be gained by separating an Oracle Financials account code into it's three separate elements. It mostly depends on how granular the data is behind these different codes. I would make the decision based on the amount of detail the users want to see. For the Account dimension I would more than likely make it a single dimension with a hierarchy. If you are talking about the Entity and Department then I would more than likely keep them as separate dimensions. It all depends on the reporting needs.

[JDLove]

My view is its possibly best to split them out, you don't really loose that much and its much more flexible.
I would expect Entity & Dept should be dimensions but also the other segments as well, then make the Accounts dimension into a nice hierarchy with weightings as needed.

I was once asked to hold 15 segments in a GL cube .... Oracle COA that would have made the GL cube 22 dimensions (Budgeting and Planning model), it was unusual as 5 segments were undefined ! and another 5 were very specific to sections of the accounts. The data was held in this detail but the needs were not reporting but adhoc analysis so I recommended separate cubes for that detail and keep the GL tight !

Also bear in mind that requirements change and your approach should allow for flexibility if possible....