Defining Dimension Structure

[comma]

I remember someone already asked about this before, I can't find the thread.

I want to try to create dimension for Department and Sub Department. And I'm not sure whether I only have to create one dimension with the Sub Department added as the child of the Department, or should I create a Department dimension and a Sub Department dimension? What are the benefits and the drawbacks of each approach?

Thanks before for your help guys.

[Alan Kirk]

I remember someone already asked about this before, I can't find the thread.

I want to try to create dimension for Department and Sub Department. And I'm not sure whether I only have to create one dimension with the Sub Department added as the child of the Department, or should I create a Department dimension and a Sub Department dimension? What are the benefits and the drawbacks of each approach?

Thanks before for your help guys.

The deciding question to my mind would be whether there is any prospect of a sub-department falling across multiple departments, or possibly moving between departments over time. If that's a possibility, then I would say that a sub-department is a separate and distinct characteristic of a value and warrants its own dimension.

If, on the other hand, a sub-department is by definition bound to a single department, then it's simply about the level of granularity that you're reporting on. A single dimension would be the preferred option then. Others may have different mileages, but that's the rule of thumb that I would use.

[comma]

whether there is any prospect of a sub-department falling across multiple departments

Well, there is. But only a few (if not just one). And since it's just a few, I thought it would be simpler to create that sub department twice under two different departments, and then add another new consolidation element whose children are those two sub departments.

Another consideration is if I created a Sub Department dimension, there's a big chance that user will match the Sub Department to a wrong Department.

What do you think?

[ajain86]

1 dimension should be the way to go for your model. It is best when you want to avoid end user confusion. If 2 dimensions are so closely related, I find it best to combine them into 1 dimension.

The one issue I can see is that data being double counted if you have a sub department in multiple departments.

[comma]

I'd like to elaborate more on this dimension thing.

Let's say I have DimA, DimB, and DimC. For one element of DimA, user supposed to be able to input to only one element of DimB, and one element of DimC. In other words, one element of DimA can only be matched to one element of DimB and one element of DimC.

What's the best approach for this then? Should I create DimB and DimC as dimensions (Approach 1)? Or should I just add DimB and DimC as the attribute of dimension DimA (Approach 2)?

If Approach 1 is used, then I think it'll be less user friendly. Because after user choose the element of DimA, they still have to choose the element of DimB and DimC, when it can be automated.

But in approach 2, user cannot see the data per value of DimB or DimC, plus the selected DimB and DimC cannot be exported to Excel as a separate column.

[Steve Vincent]

sounds more like you need a relational database like MS Access rather than TM1. It's not the correct type of tool for entering & reporting data with those kinds of relationships.

[comma]

Yes, I know it's more like relational than OLAP. But can't we implement it using TM1 so we can utilize its calculation engine?