Hi, I'm running in to an issue with OBIEE that I've never hit before. We're modelling classroom fill rates for classes at OSU. There are three main metrics we deal with - capacity (i.e. how many max people can be in a class), enrollment (how many people actually are in the class), and "open seats" (capacity - enrollment).

For 95% of the classes, there is no issue an everything works fine.

However, about 5% - 7% of our classes are "cross listed" - which means that two or more classes are taught by the same teacher at the same place at the same time. There are two main reasons we do this - classes that are offered to both undergrad and graduate students, and classes that cross colleges (for instance, some classes could cross engineering and medicine).

In those cases, we create a "cross listed class" which is essentially a hierarchy parent of the individual classes that roll up to it. But here's the rub - the capacity of the cross listed class may NOT be equal to the sum of the capacities of the classes beneath it - it can be completely different. For example, class A could have a capacity of 10, class B could have a capacity of 10, cross listed class AB may have a capacity of only 15 and not 20. This logically means "we can accept up to 10 people from either class A or class B, but only 15 people in total).

I'm having a VERY difficult time understanding how to implement this in OBIEE. I'm currently working on the idea having fact tables at both the "class" and "cross listed class" levels, but when I do that, I can't get totals to work correctly. In the above example with classes A and B, I'd expect a report to look like this:

Class A capacity     10

Class B capacity     10

Total capacity          15

Has anyone run in to issues like this before?

Thanks,

Scott