Hierarchies in dimensions.

[noobdimmodeler]

I am new to dimensional modeling and have the following scenario and was wondering if someone would be willing to give me some advice on this. I am a little confused about how to best approach this.

5 fact tables that are not really such a problem in the sense that they are fairly well defined and have many non-conformed dimensions attached to them.
A single (very large in terms of the volumes of data- 7-10 million records) conformed dimension among all these facts.

The questions I have pertain to the conformed dimension mostly.

In this dimension, currently data looks like this:

-Structure 1_level 1
-Structure 1_level 2
-Structure 1_level 3
-Structure 1_level 4

After level 3, we have two types of data. In one situation, there will be one additional level and in the other there will be two additional levels. I have kept these two types together in a single dimension table (just a conceptual model in the planning stage right now) because the five facts are applicable to both types of data and the reporting requirements are to report on both types together.

Now, each of the two types of data (each row in the table), will have a 4 level departmental structure associated with it.

I know it is acceptable to have multiple hierarchies in a dimension but am worried I may be trying to de-normalize this too much.

Also, there are many dimensional attributes I have not mentioned here for the sake of simplicity. But let's say there will be about 15 or so more. Those are not hierarchical in nature.

Like I mentioned earlier, I am trying to put together a plan at the moment. I may decide that it may be a good idea to have a departmental structure table and include that in each (or some of the 5 facts) for ease of reporting by departmental structure.

My questions are, should I keep all of this information in a single table? If I have another departmental dimension table, I will have to put keys from that table into this conformed dimension thereby making it behave like a (factless) fact and a dimension (as each departmental unit can have one or many Structure_level 1s). Is this acceptable? I don't like the idea much.. are there modeling "tricks" that I am unaware of to deal with this. On the other hand, having the department in the remaining fact tables may be pretty useful in the long run as there are many reporting requirements for analysis by department (maybe I am just wrong about its usefulness.. not sure, but I think it would make things easier/faster). It is imperative though that the conformed dimension described here remains a conformed dimension and we not just have an department dimension as the only conformed dimension. The two data types define the granularity of the fact tables. I have read all of Dr. Kimball's books, but it is a lot of information and will take time to really sink in. All seems so very simple conceptually, but when you actually get to doing it in practice, things get confusing! I know he warns against having a departmental view of things, but here I think it may not be a case of having a departmental view, more so having many requirements to report transactions by department which is why it may not be a bad idea to have a dept dimension and put sks into the facts for it.

Another option may be to do both: keep the dept info in the conformed dimension as text and have a separate department dimension to link into the facts.. this seems a little "iffy" to me though.

I think I am missing something basic in terms of modeling this correctly. Just looking for a push in the right direction if someone has thoughts.

The flat dimension may look like this:

Column headers:


-Structure 1_level 1
-Structure 1_level 2
-Structure 1_level 3
-Structure 1_level 4
-Type one or type two.
-Structure 1.1 _level5
-Structure 1.2 -level 5
-Structure 1.2 -level 6
-dept lev 1
-dept lev 2
-dept lev 3
-dept lev 4


Sample rows:

1A, 1B, 1C, 1D, 1, abcd, n/a, n/a, d1,d2,d3,d4
2A, 2B, 2C, 2D, 2, n/a, xyz, uvw, d1, d2, d3,d4
3A, 3B, 3C, 3D, 1, a, n/a, n/a, d1, d2, n/a, n/a
4A, 4B, 4C, 4D, 2, n/a, dbc, dcd, d1, n/a, n/a, n/a

In the first hierarchy, nothing can be null. For the departmental hierarchy, we must associate with level 1. But, other levels can be n/a as long as everything in the lower level is n/a as well. In other words, it is common to all of lev 2, 3 and 4 departmental subsidiaries. For these three:
-Structure 1.1 _level5    --> SET1 (set number referenced below)
-Structure 1.2 -level 5  ---->SET2
-Structure 1.2 -level 6  ---->SET2

Will always have the 1.1 level 5 as n/a or the combination of 1.2 level 5 and 6 as n/a. One "set" will be n/a, the other will not depending on whether it is a row for type 1 or type 2 data.

This will also be a "rapidly changing dimension" so I may make a history fact for it and keep only current data in the dimension table.

Appreciate your time helping a noob! Please do not hesitate to criticize, I am new to this and will take it in the right spirit! Even if your response is "read up on "xyz" it will help you" that would be great!