Drill-across relationships (Invoices -> Payments etc.)

I have a question regarding the relationship between fact tables to enable drill-across between these. I have a number of fact tables in my design, Invoices and Payments, are two of them.

• Invoices contain invoice amounts per invoice row. InvoiceNumber and LineNumber is the natural key.
  
• Payments contains the actual payment transactions, most often against an invoice, full or partial payment. ItemReference (which could be an InvoiceNumber) and EventNumber is the natural key.
  

I'd like to create a relationship between these two fact tables to be able to drill-across from the Invoices to the Payments, for example if I would like to see how much of the full invoice amount that has been partially paid.

• Is the only way to achieve this to create a dimension containing a surrogate key and the unique InvoiceNumbers, then add a foreign key in both of the fact tables to the InvoiceNumber-dimension? Wouldn't the InvoiceNumber-dimension be unreasonably large (almost 1:1 to the Payments transactions)?
  
• Or should I keep the natural keys with the facts as degenerate dimensions and do the relationships/drill-across in the reporting tools? I believe all tools used (Reporting Services and Analysis Services) are able to do create the relationship from degenerate dimensions (fact dimensions), e.g. InvoiceNumber in Invoices relates to ItemReference in Payments?
  

The question is really quite generic, the same goes for drill-down/drill-across from a Payments snapshot to Payments transactions, Payments transactions to General Ledger transactions etc.

Thanks in advance!

Assuming you do not actually have an Invoice dimension (which is usually the case), all you need to do is maintain invoice number as a degenerate dimension in the facts and let the BI tool do the work.

If invoice/payment analysis is fairly frequent and performance becomes an issue, you may consider creating an aggreage fact table that combines the two fact tables.

Thank you for your answer ngalemmo!

Regarding not having an Invoice dimension. I stripped the invoice from all natural foreign keys to other dimensions (Customer, Currency, Invoice Date, Payment Due Date, Products etc.) and added them as surrogate foreign keys to my conformed dimensions. I also placed some of the remaining interesting Invoice fields (mostly flags) in a separate junk/mystery dimension (I named this dimension InvoiceInformation). This leaves only the InvoiceNumber and LineNumber as a degenerate dimension within the facts.

Yep. That makes sense.

Hi Daniel,

I am trying to do similar thing here, I have created 2 facts (Invoices and Payments).
My Invoice facts contains Invoice Num, Invoice Line Key, Invoice Line Number and other surrogate Fkey from dimensions.
And My payments fact will contain Payments/Transaction Amount.

COuld you please tell me if Invoice status code changes from New to Paid, will i have to go and update all time in my fact?

Sunny

Hello Sunny,

Are you keeping your Invoice Status Codes in a separate/junk/mystery dimension? In that case I'd change the FK in the facts once it changes from 'New' to 'Paid'.

Dim_InvoiceInformation

Code:

InvoiceInformationDimID          InvoiceStatusCode
-----------------------          -----------------
1                                New
2                                Paid


Fact_Invoice (New Invoice)

Code:

InvoiceNumber          InvoiceLineNumber          InvoiceInformationDimID          Amount
-------------          -----------------          -----------------------          ------
1                      1                          1                                999


Fact_Invoice (Paid Invoice, change the FK to Dim_InvoiceInformation)

Code:

InvoiceNumber          InvoiceLineNumber          InvoiceInformationDimID          Amount
-------------          -----------------          -----------------------          ------
1                      1                          2                                999


Hope the above will help you!

Best regards,
Daniel

Hi Daniel,

Really appreciated your quick response on this,

Nope, I am trying to Incorporate "Invoice Status Code" in my Invoice Fact Only.

So if I create a "Invoice Information" Dim which links to Invoice Fact then do you think it should solve the problem here? what other attributes I can have in "Invoice Information" Dim?

So far my Invoice Fact looks like this:

Invoice_Surrogate_PKey
Invoice_Native_Key
Invoice_Number
Invoice_Currency
Invoice_Desc
Invoice_Cents
Invoice_Type
Invoice_Status
Invoice_Source
Invoice_Name
Invoice_Refund
Invoice_Tax
Invoice_UpdateDate
Invoice_SKU
Invoice_Quantity
Invoice_Line_Number
Invoice_UnitPrice
Invoice_Line_Key
Invoice_CreateDate
Invoice_Dates_Surrogate_FKey
Product_Surrogate_FKey
Acct_Surrogate_FKey
ProdPlan_Surrogate_FKey
PA_Surrogate_FKey
ETL_Job_Timestamp

Do you think I should have Invoices level and Invoices Line level in one FACT, or should I split this in two facts?

Also if I join "Invoice Information" to "Invoices FACT" then will it be 1:1 or 1:n relationship.

Sunny.

Hi again Sunny,

A bit hard to explain in brief since this is quite fundamental dimensional modeling. I would strongly suggest you to try to place all your descriptive attributes in corresponding dimensions, e.g. Invoice_Name in a Customer dimension (if that is the Customer name) and put a FK in the facts instead. The dates Invoice_UpdateDate and Invoice_CreateDate could also be represented by role-playing FK's to a single Date dimension.

A few links that might help you:
The 10 Essential Rules of Dimensional Modeling
Keep to the Grain in Dimensional Modeling
A Dimensional Modeling Manifesto

It could be hard to find a natural dimension for fields such as Invoice_Status and Invoice_Type (flags/types/indicators/etc), therefore they could be placed in a junk/mystery dimension instead. More information on the junk/mystery dimension concept can be found here, http://www.rkimball.com/html/designtipsPDF/DesignTips2003/KimballDT48DeClutter.pdf (also contains an answer to your cardinality question).

Whether you should create one or two fact tables depend on the facts really. If all of your facts are on the Invoice Line level it would be perfectly fine to just have one fact table at the most granular (lowest) level. But if for example the Invoice_Tax measure is per Invoice and it's impossible to allocate (split) it per Invoice Line you might be forced to create two fact tables at different levels.

Hope this answer will guide you a bit further...

Best regards,
Daniel

Thanks Daniel,

I am sure this guidelines will place me somewhere,

Again thanks for your time.

Sunny