Fact referencing Fact

[jflanner]

Hello folks:

I am modeling a business that sells subscription based services. Our customers buy time bound subscriptions to 1 or multiple services. These subscriptions must be renewed yearly. In fact - attributes of a 2011 subscription will be different then those of a 2012 subscription - even though the product is labled the same.

So subscription is a Fact. We count them. We aggrigate attributes like price. We analyze them across dimensions like Date. They do not exist on their own. They are a relationship between subscriber, product, and time.

My problem is - we have a FactInvoice. We invoice monthly. We invoice on subscriptions. So ... from that perspective, Invoice is a Fact and Subscription is a dimension.

How do I properly model this? I'm thinking I put a FactSubscription foreign key in the FactInvoice table - but that goes against the "rules" as I understand them.

Can someone point me to some guidence here??

Thank You!!

[Jeff Smith]

There are potentially 2 fact tables. if you want to keep track.report on invoices, then you need a fact table with the invoices. If you want to keep track of subscriptions, you would need a subscription fact table. The Subscription fact would look a lot like the invoice table but it would have the Invoice dimension nor dates associated with the invoice.

[BoxesAndLines]

If there are not any other "describing" attributes of a subscription, I would simply include the subscription identifier as a degenerate dimension within the Invoice fact table.

[ngalemmo]

The fact represents the act of subscribing, while the subscription represents the contract. Much like an order header, subscription does not exist as a dimension, rather the attributes of the subscription become dimensions for the subscription fact. So, the subscription # itself becomes a degenerate dimension and is stored as such on both the subscription fact and the invoice fact. The invoice fact (if these are purely subscription related invoices) should contain many of the same dimension references as the subscription fact, so it can be used on its own.

[Sponsored content]