Sub Dimension Architecture RRS feed

  • Question

  • I'm working through an example where I have a list of products that share almost no attribute fields (similar to the kimball example for snowflaking Financial Product Dimensions).  I created a snowflake of Product Subdimensions: dimProduct1 (pk ProdID), dimProduct2 (pk ProdID), dimProduct3 (pk ProdID).  The keys are created using a bridge table, dimProductBase, so that ProdID is unique across all tables.

    My Fact table, factProductMetrics, has a fk ProdID.

    My 2 questions are:

    1. Is it bad practice to skip the bridge table and link directly to the fact table?

    2. I've read that this type of join can be resolved using a "relational view" but I'm not exactly sure how to do this in T-SQL?

    Thank you

    Tuesday, March 10, 2015 10:36 PM


  • Hi pmyahoo,

    According to your description, you want to use T-SQL to create a relational view contains fact table with dimension tables. Right?

    In Analysis Services, a data source view (DSV) is an abstraction of a relational data source that becomes the basis of the cubes and dimensions you create in a multidimensional project. It contains the logical model of the schema used by Analysis Services multidimensional database objects—namely cubes, dimensions, and mining structures. It is the metadata definition, stored in an XML format, of these schema elements used by the Unified Dimensional Model (UDM) and by the mining structures. So it's a virtual table like the View we create via T-SQL. Please refer to links below:

    Data Source Views (Analysis Services - Multidimensional Data) 

    Defining a Data Source View (Analysis Services)

    When designing data source views, to determine if using a bridge table depends on the relation ship between dimension table and fact table. If it's a many-to-many relationship, it's better to use a bridge. Please refer to an article below:

    SSAS/KIMBALL: modeling a N:M relation between dimensions (part I)

    If you have any question, please feel free to ask.

    Best Regards,

    Simon Hou
    TechNet Community Support

    • Marked as answer by pmyahoo Tuesday, March 17, 2015 2:55 AM
    Monday, March 16, 2015 12:35 PM