Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Related Measures

Navržená odpověď Related Measures

  • Monday, February 18, 2013 3:01 PM
     
     
    Sign In to Vote
    0

    I have two related tables in my DSV. The first table called Test has a primary key column called test_id which is an integer data type. I have successfully created mesures and the accompanying dimensions on this table. I have a second table called test_details which has a primary key of test_details_id. It also has a column called test_id which is a foreign key on the primary key column test_id on Test. The relationship is one (test) to many (test_details) I have also successfully created mesures and the accompanying dimensions on this table as well. What I am struggling with is to find a way to "rollup"  or relate the test_details dimensions to test. I believe this is possible. I am just not sure where I am coming up short. I have tried intermediate tables. I have established (I believe) all the necessary relationships in the DSV to  have these tables "see" each other but to no avail...

    Puzzled

    Mike  

     

All Replies

  • Monday, February 18, 2013 5:25 PM
     
     
    Sign In to Vote
    0

    I have two related tables in my DSV. The first table called Test has a primary key column called test_id which is an integer data type. I have successfully created mesures and the accompanying dimensions on this table. I have a second table called test_details which has a primary key of test_details_id. It also has a column called test_id which is a foreign key on the primary key column test_id on Test. The relationship is one (test) to many (test_details) I have also successfully created mesures and the accompanying dimensions on this table as well. What I am struggling with is to find a way to "rollup"  or relate the test_details dimensions to test. I believe this is possible. I am just not sure where I am coming up short. I have tried intermediate tables. I have established (I believe) all the necessary relationships in the DSV to  have these tables "see" each other but to no avail...

    Puzzled

    Mike  

     
  • Monday, February 18, 2013 5:55 PM
     
     
    Sign In to Vote
    0

    Hey Mike,

    Create that test_details dimension with test_id as primary key column and then relate that with your Test measure group. As you have test_id column in both the measure groups you can give the relationship directly.

    If you find any duplicates while making test_id as primary key column, change the properties 'key columns' and 'name columns' of that attribute to make that a unique combination.

    Hope this helps..

    Thanks,

    Rajasekhar 

    Rajasekhar

     
  • Monday, February 18, 2013 6:13 PM
     
     
    Sign In to Vote
    0

    Rajasekhar: Thanks for replying. How does my dimension point to the measure other that on the referencing column i.e. test_details.test_details_type references my dimension table test_details_typeDim. Where do I connect to the other test_id column? I hope that makes sense.

    Mike  

     
  • Tuesday, February 19, 2013 5:45 PM
     
     Proposed
    Sign In to Vote
    0

    Mike,

    Lets take an example,

    For suppose we have some tables like,

    

    Test and Testdetails are facts and Subject is a dimension which relates to Testdetails

    As you said, you relate the dimensions with whatever measure groups that respects.

    So, now in our scenario if we want to see subject wise marks , we need to relate subject dimension to test measure group.

    To relate a subject dimension(which relates to testdetails measure group) to test measure group,

    Make Testdetails table as intermediate dimension and relate subject dimension to test measure group using referenced relationship.

     So subject dimension will be related to testdetails table by subjectid and test details dimension will be related to test table by testid.

    Hope this helps you..

    Thanks.

    Rajasekhar


    • Edited by 'RajaSekhar' Tuesday, February 19, 2013 5:47 PM
    • Proposed As Answer by 'RajaSekhar' Wednesday, February 20, 2013 4:34 AM
    •  
     
  • Tuesday, February 19, 2013 8:10 PM
     
     
    Sign In to Vote
    0
    Thanks I will give it a try...are there any special settings for an intermediate dimension?
     
  • Wednesday, February 20, 2013 2:43 AM
     
     
    Sign In to Vote
    0

    mdmck,

    You have another option, which will lead to a faster cube, although it might take SQL longer to retrieve data.

    1. Create a view that joins Test and TestDetails, that includes all the columns from TestDetails and the foreign keys from Test
    2. Replace TestDetails table in your dsv with the new TestDetails view.
    3. Now you can connect the TestDetails measure group to all the dimensions that are connected to Test.

    I consider this a classic way of ensuring dependent fact tables include all the dimensionality of their parent fact.  For example, Transaction and TransactionSalesItem.

    I am not saying that this will be better for you, but you should be aware of the options and choose the one most appropriate for your project.

    http://RichardLees.blogspot.com 

    Richard

     
  • Wednesday, February 20, 2013 3:34 PM
     
     
    Sign In to Vote
    0
    Thanks for your help Richard. I can see there are many approaches and your might be a good solution. However I am looking to connect the TestDetails dimensions to the test measure. Sorry if my initial posting was not clear.  
    Mike
    • Edited by mdmck Wednesday, February 20, 2013 3:34 PM
    •