Post reply

Fact table pointing to the same dimension twice. Don't quite know how to deal with this

  • November 27, 2012 at 9:39 am

    #145728

    Hi,

    Im having a problem and Im hoping that someone might be able to help me.

    I have for example a fact table A date time dimension and an attendance dimension

    Dim Date Time Attendance_Id Fact child SEN

    2009/2010 1 1

    2010/2011 2 1

    2011/2012 3 1

    So the child was a SEN child in all 3 years. They attendance at school ID 1 in 2009/2010,

    School ID 2 in 2010/2011 ad School ID 3 in 2011/2012 3 years 3 schools (This is just an example obviously.

    But the requirement came through that even if we are looking at 2009/2010 academic year we still might want to know the current attendance.

    Dim Date Time Attendance_Id current_Attendance_ID Fact child SEN

    2009/2010 1 3 1

    2010/2011 2 3 1

    2011/2012 3 3 1

    Now if I Do the following....

    Have Both Fact IDS pointing to the one Attendance dimension in the data source

    Copy The Attendance Dimension and then Past, Renaming to Current Attendance, The Source is still attendance.

    Add current attendance to the cube...

    However this still means that both Attendance and Current Attendance both need processing even though they are coming off the same data set.

    meaning that there will be unneccessary time lost.

    Is there any way I can do this where I had one dimension Attendance.... And yet the cube thinks its looking at Current Attendance....

    I'm struggling to find any information about this although I have noticed that some people have mentioned it in regards to date time.....

    really struggling with this...

    I could of course create a data source dimension identical to Attendance as Current Attendance.... Then Copy the attendance dimension, ensuring it points to the Current Attendance in the data source.

    Then Point to Current Attendance.... Thus shooting up production time. But it just seems a bit silly and there should be a better way of doing it....

    Any help would be GREATLY appreciated. I'm a little lost with this ATM

    Debbie

  • November 29, 2012 at 3:01 am

    #1563619

    Hi again...

    Im guessing no one has replied because this might be a silly question?

    Im thinking it must be something to do with roleplaying dimensions....

    http://msdn.microsoft.com/en-us/library/ms174487(v=sql.90).aspx

    At least this might be something I can look into.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply