how to find out which columns to join

Question

Is there an easy way to find out the column dependencies between tables in the DSV of a cube? The reason I am asking this question is: When there are lots of tables (fact and dim tables) in the dsv, it is hard to follow the lines.

Help appreciated!

Answer 1

You can right click on the arrows and hit Edit Properties, which tells you the columns of the tables that match up, and what those tables are. It's a bit tedious, but it'll get you there.

Answer 2

The easiest way to do this would be to create a new diagram with just the tables you were interested in.

Whe you are editing the DSV, in the top left corner is a "Diagram Organizer" right click and choose the option to add a new diagram. This allows you to setup a sort of "view" of a sub-set of the tables in your DSV. Then you can right click on this empty diagram, add one or more of the tables that you are interested in and then you can right click on these tables and choose the "Add related tables" option until you have drilled as deep as you need to into the relationships.

Answer 3

Open the DSV file in a text editor and scroll down to the bottom. There you will find a section called <xs:appinfo>. The relationships are all defined there, like this (for example):

<msdata:Relationship name="Data-TimeDimension" 
msdata:parent="dbo_TimeDimension" msdata:child="Data" 
msdata:parentkey="Date" msdata:childkey="ClickDateHour" 
msprop:Description="" 
msprop:IsLogical="True" />

Depending on what you wanted you could apply an XSL transform and generate an html page, or write a program that parses that and dumps it out in some other format.