February 19, 2013 at 8:01 am
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
February 20, 2013 at 10:50 am
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.
February 20, 2013 at 11:22 am
I am looking to connect the TestDetails dimensions to the test measure. Sorry if my initial posting was not clear.
Mike
February 21, 2013 at 2:16 am
"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.
February 21, 2013 at 11:04 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply