Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Fact referencing Fact

4 posters

Go down

Fact referencing Fact Empty Fact referencing Fact

Post  jflanner Mon Jun 04, 2012 10:59 am

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!!


Posts : 5
Join date : 2012-06-04

Back to top Go down

Fact referencing Fact Empty Re: Fact referencing Fact

Post  Jeff Smith Mon Jun 04, 2012 2:11 pm

There are potentially 2 fact tables. if you want to keep 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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Fact referencing Fact Empty Re: Fact referencing Fact

Post  BoxesAndLines Mon Jun 04, 2012 2:51 pm

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.

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Fact referencing Fact Empty Re: Fact referencing Fact

Post  ngalemmo Mon Jun 04, 2012 6:32 pm

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.

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

Back to top Go down

Fact referencing Fact Empty Re: Fact referencing Fact

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum