Datawarehouse - Dimension, Fact, or Degenerative dimension

Question

I am building a warehouse for a billing system that bills a number of services/utilities on the same invoice, as such I am having real trouble conforming to a recognised design method for the warehouse. I think I still very much have an OLTP hat on.

As an example a single invoice could be produced containing charges for electricity, gas and mobile phones.

All three components of the bill, themselves have two components, fixed charges, and usage based charges. Even simplified to just a mobile phone it still means to store the level of detail required for the business I need 2 fact tables:

FactMobileFixedCharge
FactMobileCalls

Each of these go onto a single invoice, in addition to this there are transactions at an invoice level too, so I now need a further fact table:

FactInvoiceTransaction

The basic schema for this simple example is as follows:

All the reading I have done approaches the InvoiceLineItem example to resolve the issue of an invoice appearing like a fact and a dimension, where the facts of the invoice are essentially propogated to the child table, causing some redundancy but conforming to standard practises.

In the production billing system itself there are 15 tables that contain items that appear on an invoice, a summary table that contains invoice level charges and a summary of the charges in the other 15 tables by category, and the invoice table itself.

I don't feel that my scenario matches this though, because the invoice has a number of different things that also need to be reported on (Invoicing address, marketing material on invoice, payment method, invoice format, email, online or paper billing, balance brought forward, payments since the last invoice etc). While this is acceptable redundancy if I had one or two tables it seems like a lot for 15 fact tables.

In addition to being a lot of redundancy when there are multiple tables, I would also still need to link FactMobileCall and FactMobileCharge to get records for the same invoice, and I don't beleive a degenerate dimension is a good way to link the two.

Basically this all points towards Invoice being a dimension, especially as there are no measurements on an invoice level, however it still feels like it should be a fact as there will only ever be new records on each import.

Which leads to my questions:

1. Am I correct in thinking that an Invoice in this scenario should be considered a fact?
2. If so is it acceptable to have further facts referencing this fact, or is there a better way to approach this?
3. I can't believe I am the only person who has encountered a situation where the InvoiceLineItem examples don't really seem to fit. Is there a recognised way of dealing with this?

For further information, my warehouse schema currently looks likes this (where I have conceded to Invoice being a fact like dimension):

Answer 1

This is how I would approach it myself. Remember to think of your Facts as "actions" or "verbs" and your dimensions as the the descriptors of your facts.

So your invoice and invoice line items are both facts. One approach to dealing with this is what you mentioned, making the fact table to the granularity of the invoice level line item. This will lead to redundancy of certain things like you mentioned. This if fine for things as an billing address dimension and certain invoice level facts such as a tax could even be broken down to the line level item and it could then aggregate back up to the invoice level amount.

This approach can break down if you have invoice level facts that don't break down nicely. For example, are customers allowed to have more than one payment method? Could there be more than one type of marketing material attached to the invoice? Does the marketing material correlate to specific line items?

Asking these questions can definitely lead you to creating multiple facts such as FactInvoiceLineItem, FactInvoice, FactPayments, etc. that you end up tying together with something like invoice number. Doing this will give you more flexibility to analyze specific facts better.

If doing this approach and you are building cubes off the fact tables, I would recommend using views to load your cubes. This way you could have a high level view for the invoice that joined your various fact tables and had measures on there such as number of line items, number of payments, etc.

All that to say, I believe going that route is much better than splitting out your fact tables that have the same granularity.

Feel free to respond with more details if you need more help hashing it out.

Answer 2

A two-tier fact table is usually more trouble than it's worth. That's your OLTP instinct to normalize kicking in - fight it! It took me several months to get my head around the warehouse/star schema paradigm.

All that invoice header information can be tucked into a couple of extra dimensions. This will just add a couple of INT columns to your fact table, so the extra space required - where it counts - is minimal. Those dimension tables will only contain distinct sets of data, so there will actually be very little redundancy.

Based on what you've described, I'd start with this:

Invoices

• InvoiceSK (synthetic key, nonclustered PK)
• FixedCharge (fact)
• Usage (fact)
• UsageCharge (fact)
• InvoiceNumber (degenerate dimension)
• AccountSK (synthetic key, FK to Accounts)
• MarketingMaterialSK (synthetic key, FK to MarketingMaterial)
• ProductSK (synthetic key, FK to Products)
• DateBilledSK (synthetic key, FK to Dates)
• DatePaidSK (synthetic key, FK to Dates)

Payments

• PaymentSK (synthetic key, nonclustered PK)
• AmountPaid (fact)
• AccountSK (synthetic key, FK to Accounts)
• PaymentMethodSK (synthetic key, FK to PaymentMethods)
• DatePaidSK (synthetic key, FK to Dates)

Accounts This would be a type-2 SCD, so the info (e.g., address) stored for each fact record reflects the info on file at that time.

• AccountNumber
• FirstName, LastName
• StreetAddress, City, Region, Country, PostalCode, AreaCode

Products

• ProductCategory (electricity, gas, phone)
• ProductName
• ProductCode

I would make the foreign keys purely logical, not enforced; you don't need the overhead as long as your ETL is tight.

I don't know what PaymentMethods entails in your database. If it's simply "did they use check, a card/debit card, or is this a billing adjustment?" you can make that a degenerate dimension in Payments; if it has fields for card number and expiration date, etc., you should make it a dimension to avoid loading the fact table.

You may want to make a distinction between BillingAddress and ServiceAddress. You can either make two sets of address fields in the Accounts table, or hive addresses off into a new table and have the fact table reference both. All else being equal, I'd lean toward the first - it'll keep the fact table as narrow as possible.

The tricky part comes in connecting Invoices to Payments. This is a many-to-many relationship, which tends to be painful in a warehouse. For many circumstances, you don't need to: you can aggregate Invoices and Payments by AccountNumber as of a certain point in time and simply report on the balance due (you may store this in a monthly snapshot table). However, the business users will probably want a more exact breakout. The table can be very simple:

PaymentAllocation

• InvoiceSK
• PaymentSK
• Amount
• PK on { InvoiceSK, PaymentSK }

...but deciding how many dollars from each payment should go to each invoice line item rarely is. Assign revenue to the oldest invoice line item not yet paid in full? That's a start.

A similar approach works for allocating costs to revenue or vice-versa, to calculate fine-grained profit. It's a lot of work, but in the end you can calculate which customers and products, are actually driving the business - and that's gold.

A future step would probably be to create a Households table to group accounts for a single address.