Complex Bridge Table

Hi all,

I have to implement a documents workflow in my data warehouse
So, I have some documents labeled as Type A, other documents labeled as Type B and others labeled as Type C
A Type A document can produce one or more Type B documents and Type B documents can origin one or more Type C documents. The result is the following:

Type A           Type B        Type C
Doc Nr. 1     Doc Nr.100   Doc Nr 200
Doc Nr. 1     Doc Nr.100   Doc Nr 300
Doc Nr. 1     Doc Nr.500   Doc Nr.400
Doc Nr. 1     Doc Nr.500   Doc Nr.450
Doc Nr. 1     Doc Nr.600   Doc Nr.900

I modeled it as shown in the following image (click on it to enlarge):


In this way (using 1,1 relationship in bridge table in both side) I can relate documents each others using my BI tool. But, when I calculate the cost measure in a list that shows the 3 types of documents, each Type A document will be duplicated for each of its "sons" and this happens also for Type B documents.

Using the previous example, considering the following scenario
Document                   Sum of the cost measure of each document rows
Type A - Doc Nr. 1        100
Type B - Doc Nr. 100     300
Type C - Doc Nr. 200     500
Type C - Doc Nr. 300     100



Type A        Type B        Type C
Doc Nr. 1     Doc Nr.100   Doc Nr 200
Doc Nr. 1     Doc Nr.100   Doc Nr 300
------------------------------------
200             600              600           <------TOTAL COST MEASURE

Instead, I'd like

Type A        Type B        Type C
Doc Nr. 1     Doc Nr.100   Doc Nr 200
Doc Nr. 1     Doc Nr.100   Doc Nr 300
------------------------------------
100             300              600

I know the problem is due to the 1,1 relationshop from bridge table to my dimensions but using 1,n cardinality I don't know more what documents is related to others.
How can I edit my model in order to solve the aggregation  problem?
Many thanks to all!

Possible solution:
1. Put the Parent Doc ID (or other suitable reference) in the Type B and Type C Dimensions (or in a bridging table that just holds parent/child keys)
2. Query all three fact tables independently (e.g. sub-queries) but include the Parent Id in the query
3. Join the 3 result sets using the parent keys and then query this single result set

Thanks nick for your post.
I thought  to these solutions but they are not suitable for free analysis performed by BI user. Im my opinion, these solutions can be achieved developing an ad hoc report....are you agree?

I'm not sure what you mean by "free analysis by BI user". As a general rule you should not be exposing the underlying tables of your DW to your users - the best solution is to be using a proper BI tool (OBIEE, BO, SSRS/SSAS, etc.). If you are allowing users to write SQL then it should be against Views you have created that hide any complexity/ambiguity - and also allow you to change the underlying tables without breaking everyone's queries.
Given this,, if your create a View that uses the approach that I outlined then would this allow "free analysis by BI user".
If you are going to allow completely free analysis by your BI users using SQL then presumably they are SQL experts and will also have a complete understanding of the DW data model and would therefore also be able to write the SQL I have suggested?

Hi nick,
users are typical *End users* without any SQL knowledge. So for "Free analysis" I mean a common use of a BI tool able to display some data in a list or in a crosstab performing a drag&drop on dimensions and measures of interest.
So, If I consider your solutions I can't produce an analysis using only drag&drop but I must develop a complex report (which can be executed by users in the BI environment) in order to implement a join like you wrote in the third solution.
I hope my explanation is clear (otherwise, i'm sorry for my english...)
Many thanks for your time

Hi,

so you write the SQL statement that queries the facts and joins the results together and then you put a View on top of this and expose the View through your BI tool to your end users as though it was any other table or sets of tables. In fact your BI tool should be able to handle this for you anyway; I know that in OBIEE, for example, you can combine multiple physical data sources into a single logical object that the end users see.

The metadata layer of any decent BI tool should let you perform complex data calculations and manipulation in order that what gets presented to the end-users is simple.

Regards,