March 9, 2012 at 5:29 am
Hi,
Having an issue with my new attendance dimension.
The attendance dimension has 3 levels.
Every attendance within the academic year
Every school the child attended within the academic year
the last school the child went to in the academic year
The example I have given just shows a pupils 2008-2009 data
Attendance_IDSchool_IDLast_Attendance_IDSCHFROMTOBy AttendanceBy Schoollast attendance in the year
117School A05/09/200629/09/200811
227School B30/09/200812/10/200811
337School C13/10/200804/11/200811
467School D05/11/200826/11/20081
557School E27/11/200815/03/200911
667School D16/03/200907/06/200911
777School F08/06/200921/09/2009111
TOTAL761
In this example for grouping purposes when grouped on academic year There were 7 attendances at 6 schools by 1 pupil.
Ive added the dimension to Analysis Services and Set School_ID and Last_Attendance_ID as parents and it errors because ‘There are multiple parent attributes defined’ underlined in red
Does this mean you can only have one parent in each dimension?
Im really unsure how to go ahead with this at the moment. I cant find much help on the internet on this one.
March 9, 2012 at 8:02 am
Parent has a very specific meaning here, indicating that the Attribute is a parent in a parent-child relationship (also known in DB terms as a self-reference, like an employee table that has a ManagerID field).
Based on the data you've presented, it looks like you'd probably want separate dimensions for School and Student and use the attendance info as measure/s. But that informed opinion is based on the 7 rows of data you've supplied, so YMMV.
Steve.
March 9, 2012 at 8:19 am
Hiya,
They are self referencing in a way. I used to have them seperate but mostly I only need school info when discussing attendances and after lots of reading it seemed more natural to get everything in one table.
The last 3 columns are from my fact table.
I split my facts up into 3 so all the attendances within an academic year, all the schools within the academic year and then just the latest attendance to get the number of pupils in a grouping.
So school F is also the Last Attendance of the attendances (the manager as it were)
ID 6 is the manager 0f ID 4 at school level because the pupil attended the same school twice in the same year. None of the other attendances have this issue so the parent ID at the school level is always themselves.
Information Like the NC Year and the from and to dates are attendance information and belong to either the attendance or last attendance level but arent facts as it were.
I originally had just the school name and code etc in attendance and had a school dimension with all the other info in but it seemed silly to add two dimensions to a fact table when just one should do, expecially after reading up on the subject.
Some resources say that Parent Child (star schemas) are the best and some dont. Its all very confusing
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply