locked
Add a Region-Country dimension with many to many relationship RRS feed

  • Question

  • I am designing a Cube in BIDS 2012 and I encountered a many to many relationship, which is actual really common, that is giving me some trouble.

    In my fact table I have TotalSales measure that has Country, Customer and Edition (of Product) as dimensions. At the beginning I used just Country but now I have been asked to use Region, which is an higher hierarchical level of Country. 

    Basically a Region contains multiple Countries and a Country can belong to multiple Regions. For instance the Country Italy can be placed inside the region South-Europe and Europe. There is no relationship among Regions, so Europe and South-Europe are considered two distinct Regions and South-Europe is not a sub-region of Europe.

    The schema is a s follows:

    With this schema I can process the cube but I just take inside the Dimension Structure the table Country, ignoring the CountryRegion and Region tables. If I take the RegionID from any of the two tables into my dimension the process fails:

    Errors in the high-level relational engine. The 'dbo_CountryRegion' table that is required for a join cannot be reached based on the relationships in the data source view.

    I tried different combinations, also disconnect Coutry from the Fact Table and linking the CountryRegion table on the Country attribute of the fact table on Country.lngID = FactSalesTableTEST.Country but when I process the dimension I always get a processing Error.

    I would like to have a dimension Coutry with a Region-Country hierarchy based on this schema. How can I achieve my goal?

    Thursday, November 14, 2013 11:52 AM

Answers

  • Thanks fro your reply! However I found an article that covers exactly my case and it works!

    http://social.technet.microsoft.com/wiki/contents/articles/19854.tutorial-many-to-many-dimension-example-for-sql-server-analysis-services.aspx

    • Marked as answer by Trinakriae Thursday, November 21, 2013 11:15 AM
    Thursday, November 21, 2013 11:14 AM

All replies

  • Hi Trinakriae,

    In your scenario, a Region contains multiple Countries and a Country can belong to multiple Regions. Now you want to create a dimension Country with a Region-Country hierarchy based on this schema, right? Since it's not one to many relationship on this hierarchy, it's not a natural hierarchy. However we cannot create a relationship between country and region if you want to create a unnatural hierarchy. Please refer to following links.
    Unnatural Hierarchies in Analysis Services 2008
    Create unnatural hierarchy (age band/gender)
    Hierarchies in Analysis Services

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, November 21, 2013 2:54 AM
  • Thanks fro your reply! However I found an article that covers exactly my case and it works!

    http://social.technet.microsoft.com/wiki/contents/articles/19854.tutorial-many-to-many-dimension-example-for-sql-server-analysis-services.aspx

    • Marked as answer by Trinakriae Thursday, November 21, 2013 11:15 AM
    Thursday, November 21, 2013 11:14 AM