-1

I have to design a cube for students attendance, we have four status (Present, Absent, Late, in vacation). the cube has to let me know the number of students who are not present in a gap of time (day, month, year, etc...) and the percent of that comparing the total number.

I built a fact table like this:

City ID  | Class ID   | Student ID   | Attendance Date | Attendance State | Total Students number
--------------------------------------------------------------------------------------------
1        |  1         | 1            | 2016-01-01      | ABSENT            | 20

But in my SSRS project I couldn't use this to get the correct numbers. I have to filter by date, city and attendance status.

For example, I must know that in date X there is 12 not present which correspond to 11% of total number.

Any suggestion of a good structure to achieve this.

whd.nsr
  • 684
  • 6
  • 12

1 Answers1

2

I assume this is homework.

Your fact table is wrong.

  1. Don't store aggregated data (Total Students) in the fact as it can make calculations difficult.
  2. Don't store text values like 'Absent' in the fact table. Attributes belong in the dimension.

Reading homework for you:

  • Difference between a Fact and Dimension and how they work together

  • What is the grain of a Fact and how does that affect aggregations and calculations.

There is a wealth of information at the Kimball Groups pages. Start with the lower # tips as they get more advanced as you move on.

Wes H
  • 4,186
  • 2
  • 13
  • 24