Data Modelling -- linking Header and Detail Fact Tables.

Hi All,
I am looking for some help on modelling approaches i am confronted with. Its regarding bringing the "Invoice Line Details" into the DW for Financial Analytics.
Here is the scenario :
We have a Transaction Fact Table W_AR_XACT_F which contains the 'Invoice Header' details. This table has 10 dimension tables joined to it.
To bring in the 'Invoice Line' details we are building a custom fact table 'WC_INVOICE_LINE_F'.
So how and where can establish the 1:M relationship between the 'Header' and 'Line' fact ?
My dilema is how to link these 'two fact 'tables with a 1:M join. Can I connect these two tables directly with 1:M or do I need to introduce a DIM/HELPER table in between these two fact tables.
1:M
1> W_AR_XACT_F -------> WC_AR_INVOICE_LINE_F
OR
1:1 1:M
2> W_AR_XACT_F -------> HELPER_TABLE ---------> WC_AR_INVOICE_LINE_F

Do both of these hold good or should I need to 'connect them differently. This is typical scenario of having to report the Line details in a report along with Header details.

Any tips to model this would be appreciated.

regards,
Sandeep.

As with any two fact tables, you join between them using conforming dimensional keys. In your case, one would assume there is an invoice dimension (either real or degenerate). You would join rows using that key. There is no need for a bridge/helper table.

Hi ngalemmo,

Many thanks for the reply.
I dont have a 'real' invoice dimension associated with the Header Fact (W_AR_XACT_F). Shall I be creating one ?
In case of a 'degenerate' dimension, how do I create one?
Will this table establish the 1:M parent - child relationship between my Header Fact --> Line Detail Fact ?
Which tables holds the keys to 'two' fact tables?
I am not an expert, would appreciate if could throw more light on this to me.

regards,
Sandeep.

A degenerate dimension is a dimension with no attributes. It is usually the case with things such as invoice and order headers that the information about the document is covered in other dimensions, all that is left is the number (invoice number, order number, etc...) that identifies the document. A degenerate dimension simply means you store that identifier (i.e. the invoice number) as a column in the fact table.

Hi ngalemmo,
Many thanks for your suggestion.

Actually, this is the scenario. There is a table in the warehouse, W_SALES_INVOICE_LINE_F which captures the AR line details along with some 'Order Mgmt' source tables.
I am leveraging it now for my purpose. As you have suggested I am planning to model this by having a 'conforming' dimension to both these FACT tables.
Now my Header Table is 'W_AR_XACT_F' and Detail Table is 'W_SALES_INVOICE_LINE_F'. This is how i am planning to model this:


1> I will create a Dimension called 'WC_AR_INVOICE_D'.
2> The primary key in this table would be based of 'Invoice Number'.
3> I will introduce 'invoice_wid' in both W_AR_XACT_F and W_SALES_INVOIVE_LINE_F tables and use it to join to WC_AR_INVOICE_D.


W_AR_XACT_F --> W_AR_INVOIVE_D
W_AR_INVOIVE_D -> WC_AR_INVOICE_LINE_F

Both these physical joins would be a 1:1 joins and the query to fetch the line details will be routed from 'Header' to 'Detail' table via the 'W_AR_INVOICE_D' table.

What do you feel about this model ? Do You think I am missing something ?

Please share your thoughts on this.

regards,
Sandeep.

That will work. However, you may want to review what will be in the invoice dimension. Most dates relating to an invoice should be dimensions themselves with FKs on both header and detail facts. What remains could be reduced to one or two junk dimensions, again with FKs on both facts. The invoice number itself could then be stored in the facts as a degenerate dimension.

Hi ngalemmo,
Thanks for time and reponse.

As you mentioed, my Invoice dimension is degenerate and will just have the foreign keys to connect to the fact table. As all other attribute information is available in the surrounding dimension tables for the two fact tables.

I have another challenge now. The actual tables I am trying to model here are in Financial Analytics for AR module of Oracle Apps.
On the Warehouse side :

My Header table is W_AR_XACT_F
Detail table -- WC_XACT_AR_LINES_F

On the source side :

RA_CUSTOMER_TRX_ALL -- header table identified by cust_trx_id.
RA_CUSTOMER_TRX_LINES_ALL -- cust_trx_line_id.


On the DW, this is the cardinality i was establishing :
(1:M)
W_AR_XACT_F ---> WC_XACT_AR_LINES_F.

to cater to
(1:M)
RA_CUSTOMER_TRX_ALL ---> RA_CUSTOMER_TRX_LINES_ALL.

But on the source side, I have found that there is a further split at the 'Lines Level', i.e each of the 'Line Items' in RA_CUSTOMER_TRX_LINES_ALL can multiple 'distribution lines' in 'RA_CUST_TRX_LINE_GL_DIST_ALL' table.
This is the relation there:

(1:M) (1:M)
RA_CUSTOMER_TRX_ALL ---> RA_CUSTOMER_TRX_LINES_ALL ---> RA_CUST_TRX_LINE_GL_DIST_ALL.


How do I model this level of granularity?

a. Should I further be creating 'another' fact table to cater to further granular data (one line into multiple lines) from 'RA_CUST_TRX_LINE_GL_DIST_ALL' and again have a conformed dimension with my 'detail' table ?

b. Use only 'one new' detail fact, but build it to the most granular level ?


I would really appreciate your suggestions on how to model this scenario.


regards,
Sandeep.[strike]

The line distribution detail is modeled as a separate fact table. It should assume all the dimensions of the line fact as well as additional dimensions to support the distribution. Each fact table should stand on its own, without the need to join line and distribution to get line dimensional information such as product, customer, etc...

Sandeep and ngalemmo,

Good informative conversation.

My Question, what will be the degenerate dimension in "RA_CUST_TRX_LINE_GL_DIST_ALL" Fact table. And how to trace back to
"RA_CUSTOMER_TRX_LINES_ALL" and finally "RA_CUSTOMER_TRX_ALL" Fact table. i.e which distribution line is linked to which customer line and to which customer transaction.

@Sandeep, Can you post the snapshot of the dimensional model.

Sunny

Order # and Line # would be examples of degenerate dimensions.

thanks ngalemmo. thats the answer i was expecting.
now my question is: how to query the Order No's which do no have any Line No's OR
the Line No's which do not have any Distribution No's.

I don't follow...

A distrubution transaction would reference the order & line it is shipping. You would not have a distribution reference in the order line facts since it is possible there could be multiple shipments for a line. If a line hasn't shipped, there wouldn't be a distribution referring to it. This is normal.

If you are building an order-to-cash warehouse, there are a lot of events taking place, all of which are reflected in their own atomic fact table... so, you have order line facts, order line distribution facts, distribution facts, invoice line facts, invoice distribution facts and so on. These are independent of each other and you do not have a full end-to-end picture until, at least, an invoice is cut.

If you are building an aggregate of this activity, you can build it incrementally by unioning various facts together, or depending on the business need, wait until it the order is closed.

I am following you. As I am using BO as my reporting tool so I can have multiple data providers or I can combine sub queries and can do union/intersection/minus etc... on these queries involving multiple facts.

My datawarehouse is not a order to cash warehouse. It is a Consultation warehouse.
Here I have Companies(clients) who requests for conducting a Visit at their sites. So a Request ID is created and which can generate one or multiple Visits to the requesting Company. So an Employee conducts a Visit or Visits and find 0/1 or multiple hazards at the site.

I have created 3 fact tables: Request Fact, Visit Fact and Hazard Fact

Request Fact columns -->Request ID, conformed dimensions(employee, company etc) and some facts related to Request
Visit Fact columns --->Visit ID, Request ID, conformed dimensions and some facts related to Visit
Hazard Fact columns--->Hazard ID, Visit ID, Request ID, conformed dimensions(Hazard type and all dimensions used by Visit and Request Fact)

So I have star schema with 3 facts and conformed dimensions without joining any facts.

Now this will list me all the requests which have visits and also all the visits which found hazards.
And I was concern with finding all the requests on which there are no visits. And all the visits with no hazard found.

Hope I am making sense.