Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Related measures Expand / Collapse
Author
Message
Posted Tuesday, February 19, 2013 8:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:36 PM
Points: 3, Visits: 12
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 measures 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 measures and the accompanying dimensions on this table as well. What I am struggling with is to find a way to "roll up" 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
Post #1421660
Posted Wednesday, February 20, 2013 10:50 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 12, 2014 4:47 AM
Points: 545, Visits: 742
I am not entirely sure what you are trying to do by your description, but essentially if I understand it you have two fact tables.

What are you exactly trying to do when rolling-up. Are you trying to roll-up the measures in the child table to analyse by the same dimensions as in the primary table. If this is the case you could have combined the tables to create a single fact table.
This only works though if the dimensionality of these measures is all at the same grain. If not you will need two separate fact tables. If this is the case then ensure that both fact tables have the appropriate dimensions to analyse by. If this happens to be the same dimension then use conformed dimensions.


Paul R Williams.
Post #1422211
Posted Wednesday, February 20, 2013 11:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:36 PM
Points: 3, Visits: 12
I am looking to connect the TestDetails dimensions to the test measure. Sorry if my initial posting was not clear.
Mike


  Post Attachments 
test detail.bmp (3 views, 488.41 KB)
Post #1422225
Posted Thursday, February 21, 2013 2:16 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, December 12, 2014 4:47 AM
Points: 545, Visits: 742
"I am looking to connect the TestDetails dimensions to the test measure".

Then you either need to combine the tables as I indicated so that you have one fact table. All dimensions can then be assigned to this fact table. You can then roll up from the test details to analyse by all dimensions which I think is what you require.

However, as I have also described as they are different grains this may not be feasible and you would need to be careful of the dimensionality of the primary table. In this case you can have separate fact tables but you would need to add the dimension information related to the test details to the test measure.

Do this in the DW if this exists. If not this should be possible by using the abstraction layer of the dsv and using a named query.


Paul R Williams.
Post #1422445
Posted Thursday, February 21, 2013 11:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:36 PM
Points: 3, Visits: 12
Thanks Paul: I see now the granularity between the two tables is my issue. I am going to modify the view to include the dimension attributes from both tables...Mike
Post #1422722
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse