Fact referencing Fact

View previous topic View next topic Go down

Fact referencing Fact

Post  jflanner on 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!!

jflanner

Posts : 5
Join date : 2012-06-04

View user profile

Back to top Go down

Re: Fact referencing Fact

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

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Fact referencing Fact

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Fact referencing Fact

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Fact referencing Fact

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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