1

I am going to model one of the star schemas for a university data warehousing project. We need to compare the actual application count with a target.

There are target counts (set by the colleges every year) associated with Departments, Course groups, and Courses.

The requirement is to ensure that the targets set get correctly allocated and also the progress of applications against the target.

One proposal is to include all the actual counts (department level total accepted count, course group level total accepted count, course level total accepted count) and corresponding target counts (dept level target counts, course group target counts, course target counts) in single fact table. One of the dimensions in this star schema is Course dimensions. It consists of all the course, course group and department information. I do understand a granularity problem here, but this could be handled at the cube implementation level.

Or if I want to set the target at different hierarchy levels of a dimension, should I build different fact tables? As mentioned below:

Implement 3 fact tables for 3 different types of targets and connect these fact tables to the actual fact table. In this situation, the Course dimension can snowflake into course group dimension and department dimensions. First fact is connected to course. Second fact to course group and the 3rd one to dept. The actual fact table is of granularity of course level so all three fact tables can be connected to the actual fact table via course. Note that the actual fact table is of grain course-level and this can be aggregated to get higher level such as course group and dept actual counts.

Data Architects please comment!

DatumPoint
  • 419
  • 4
  • 21
user1254579
  • 3,901
  • 21
  • 65
  • 104
  • 1
    If I understand the question right, you are going to have the most detailed fact table at course level. The other two (at group and dept level) would then be aggregates which can be computed inside the warehouse. In real life, you might be using materialized views for these aggregates. – Hellmar Becker Sep 25 '15 at 09:41
  • yes the fact table is of course level with all the actuals measures are of course level.The targets counts are of different levels that is course,course group and departments.The target counts are imported directly from an excel sheet and the actuals from the transactional system.The thing is i am going to keep all targets counts and actuals in the same fact table ! – user1254579 Sep 25 '15 at 13:14
  • I know the grains are not eqaul ,but I will be doing some level aggregation restriction in cube level!? – user1254579 Sep 25 '15 at 13:15
  • Do you have an example of how your data looks like? – Hellmar Becker Sep 25 '15 at 13:29
  • It doesn't sound right to put facts of different levels into the same fact table. A fact table should have a clear grain which should be consistent. You can make separate fact tables for the different grains and aggregate to those levels if needed. – Rich Nov 28 '18 at 11:39

0 Answers0