none
Many to Many with Bridge sharing attributes in a hierarchy

    Question

  • I need a way to support multiple calendars.  I currently have a normal dim-date which is gregorian and a dim-school-calendar for each campus in the school district with a bridge fact table to relate them.

    My question is, there are attributes in the dim-date that I would like to use in hierarchies I am making for dim-school-calendar. 

    when I drag the attributes from Dim-Date to the the left attributes panel in the Dim_School_Calendar Dimension Design and I process the dimension, SSAS complains that it cannot join to Dim-Date because of the DSV. 

    Thanks for your help!

    Bryan


    • Edited by Bryan Shaw Friday, June 15, 2012 1:05 PM
    Thursday, June 14, 2012 8:45 PM

Answers

  • Hello,

    You will only be able to add an attribute from another dimension if the source table of that other dimension is related in dsv to the source table of the dimension. Once the relation established, you can define futher relations between attributes on the attribute relationship tab

    example from the AW: you can drop City from the Geography table into the Customer dimension because geography is related with Customer. But not the other way round.

    From Your Date dimension to the School calendar, one relation in the relation chain lays in the wrong direction.

    The following link explains prety extensively how to implement  many to many relation in SSAS. : http://www.sqlbi.com/articles/many2many/

    Philip


    • Edited by VHteghem_Ph Friday, June 15, 2012 1:39 PM
    • Marked as answer by Bryan Shaw Monday, June 18, 2012 9:29 PM
    Friday, June 15, 2012 1:34 PM

All replies

  • Here is a detailed copy of the Process Progress Dialog:

    Errors and Warnings from Response
     Internal error: The operation terminated unsuccessfully.
     Errors in the high-level relational engine. The 'dbo_DIM_Date' table that is required for a join cannot be reached based on the relationships in the data source view.
     Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'DIM School Calendar', Name of 'DIM School Calendar' was being processed.
     Errors in the OLAP storage engine: An error occurred while the 'School Calendar WID' attribute of the 'DIM School Calendar' dimension from the 'Attendance' database was being processed.
     Server: The operation has been cancelled.

    Friday, June 15, 2012 1:05 PM
  • Hello,

    You will only be able to add an attribute from another dimension if the source table of that other dimension is related in dsv to the source table of the dimension. Once the relation established, you can define futher relations between attributes on the attribute relationship tab

    example from the AW: you can drop City from the Geography table into the Customer dimension because geography is related with Customer. But not the other way round.

    From Your Date dimension to the School calendar, one relation in the relation chain lays in the wrong direction.

    The following link explains prety extensively how to implement  many to many relation in SSAS. : http://www.sqlbi.com/articles/many2many/

    Philip


    • Edited by VHteghem_Ph Friday, June 15, 2012 1:39 PM
    • Marked as answer by Bryan Shaw Monday, June 18, 2012 9:29 PM
    Friday, June 15, 2012 1:34 PM