Modeling Fact Tables that have direct relationships, but at a detail and not a dimension layer

[darylm74]

I have an accounts receivable table with a header (accounts receivable dimension) and then a detail (accounts receivable fact). These fit together nicely, but my problem is that we often tie in records from our customer ledger for reports. The customer ledger can act like a dimension table in some respects, meaning that multiple line items in the accounts receivable fact roll up and tie to the customer ledger's open and closed amount. The issue is that sometimes it goes the other way around. A check that comes in the accounts receivable tables may have just two lines, but may have several lines in the customer ledger. Sometimes they match one to one. I have a very odd mixture of granularities and the items in one table may never have a match in the other, and vice versa.

My question is, has anyone dealt with a situation like this before? Also I am wondering if I have to break the customer ledger into very specific business processes that accounting does, such as handling unapplied cash, invoices, sales orders, etc. I know we have reports that span accross multiple business processes that use all over the various granularities. I am worried about breaking it up so much that it is confusing as well.

I hope the description makes sense, as it is a very confusing situation to describe. It doesn't easily fit into any of the models in the DWH books I have.

[ngalemmo]

In a dimensional model, fact tables do not have direct relationships with each other. Period. That is the modeling form.

Measures held in different fact may be combined based on shared dimensions.

It is also, usually, not best practice to have a 'header' dimension. They are, for the most part, useless for integrating facts. Header data is most often broken down into multiple conforming dimensions. All you usually have left over is a document identifier (voucher #, invoice #, etc...) which winds up as a degenerate value in the fact table.

[darylm74]

I realize that facts are not to tie together, and in all of the cases that I have designed before, I have not had this isue. In this case though, I am dealing with a table which sometimes is related to the existing fact table, and sometimes not. Due to the granularities, where sometimes they match 1 to many, other times, 1 to 1 and yet others, many to 1, I can't just tie the values into the existing fact table, otherwise there would be a duplication of values and things would not add up. Here are the scenarios:

Multiple Accounts Receivable Records roll up to 1 Customer Ledger Record.
1 Accounts Receivable Record is related to multiple Customer Ledger Records, rolled up.
1 Accounts Receivable Record is tied to 1 record in the Customer Ledger Record, and they tie out without aggregation
A record in the Accounts Receivable Fact may not have a corresponding Customer Ledger Record and Vice Versa.

As for the "header" dimension, I understand what you are saying, but it has a lot more than just a degenerate key value. It has several codes, code descriptions, remarks, etc.

[darylm74]

Ok, here is the business case:

I have a table, which is account receivables. Account recivables can have multiple lines and are additive. I also have Invoices, which are stored in the customer ledger which have multiple lines and sometimes are additive, and sometimes are already at sort of a rolled up level.

The case where there are mutliple account receipts, usually a debit and a credit, to one rolled up customer ledger record is the case of unapplied cash. I could possibly break this process out, but for a lot of reports, we add across various types of receipts, including unapplied cash.

The issue is that there is no one to one match between the two. There may be receipts that come in that are not related to invoices, and there are invoices that never have receipts. I also have cases where multiple invoice line items are paid for by 1 check (receipt), but also cases where multiple checks are used to cover only one invoice line item or maybe 2 checks for 10 invoice line items. I can't easily tie these directly. There is not always a common dimension between the two data sets, but there is definitely a direct correlation between line items, and users have a need to get down to the details.

Maybe it is a forrest for the trees issue, and any help would be appreciated.

[GMELHAFF]

I also have similar situation where I have orders, invoices, and receipts where an order may exist with no invoice or receipt and receipts with no order.

The obvious simplistic options (mentioned in Ralph's books) are separate facts for the line items for each. This is not only very complex to consume but requires transformation logic in reporting to join them since there's logic required to align orders to invoices (there's no direct FK navigation between them within the source system thanks to Oracle ERP's convoluted data model).

Another option is just store when there's a receipt (that's what's currently being done) but then we can't do order fulfillment analysis. A previous design by someone else did that and it meets most needs but not all.

The preference is one fact to meet all needs. But of course there cannot be grain violation. I'm leaning towards populating the fact for the lowest grain for a point in time. Since the line items are normalized (amounts split) across the line level items when they exist across each of these, it is possible to store the lowest grain and simply update it when the counterpart arrives. For example, an Order line populates with some null handling for the degenerate facts such as receipt number (negative or zero, etc.) then when/if the related receipt arrives, I update the fact for the degenerate facts and any dimensions.

Any thoughts?

[Sponsored content]