Cross reference facts and dimensions

View previous topic View next topic Go down

Cross reference facts and dimensions

Post  rahm0277 on Thu Apr 28, 2011 9:40 pm

I am trying to design a data warehouse for licensing vendor, who sells licenses on ecommerce and various other venues. The things they want to track are sales, product lifecycle and activity. What this means is that there are different sale types (such as new purchase, promotional purchase, renewal) and different events/states of a license, such as - a license can get installed, renewed, activated, registered. A license can get renewed many times (on different dates).

So I was thinking my dimensions would be very simple - date, product, source, saletype and event/state. I would have two fact tables; one would be for sales, and another would be for the events, both of them having foreign keys to the dimension tables. My fact tables would be an accumulating fact table, where every event would add a new row - hence, the licenses can be repeated. However, the requirements states that they be able to cross reference these two facts and the saletype and event dimensions. For example, If someone sees that product 'A' has 100 sales in the US ecommerce store of type 'new purchase', then they want to see how many of 'those' 100 licenses also got activated... and then maybe they would want to see, out of the people that activated, how many have registered... and then (back to saletype) how many of those that registered, how many of them 'renewed'. And I cannot really define a heirarchy, because you could have a whole lot of combinations of these....

How can I do this? As I'm reading, I find there seems to be no way to relate the two facts based on the license itself (which is what I need to do).

note: I am using sql server analysis services and sql server 2008 r2

rahm0277

Posts : 6
Join date : 2011-04-24

View user profile

Back to top Go down

Re: Cross reference facts and dimensions

Post  ngalemmo on Fri Apr 29, 2011 4:59 pm

Why isn't licence a dimension?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Cross reference facts and dimensions

Post  rahm0277 on Fri Apr 29, 2011 9:44 pm

It could be (if thats what I need). But every sale is a new license. Each license is unique. A license in this case is really like an order number (but has a life of its own). And I already have over a million licenses... is that right? Should a dimension table be that large?

Just as a reference, this is what I have now:


1. DimProducts (PK: ProductID, and other attributes)
2. DimDate (PK: DateKey, and other attributes)
3. DimEvent (PK: EventID, and oither attributes)

4. FactLicenses(FK: ProductID; FK: DateKey; FK: EventID, and License Field(varchar))

So I have a license repeated, with an event for every time something happens to the license (installed, activated, renewed, cancelled, renewed (again). It is possible there is one license with the same eventID, but never on the same DateKey. The primary key of the table is DateKey + EventID + License



rahm0277

Posts : 6
Join date : 2011-04-24

View user profile

Back to top Go down

Re: Cross reference facts and dimensions

Post  ngalemmo on Sat Apr 30, 2011 6:48 pm

then they want to see how many of 'those' 100 licenses also got activated... and then maybe they would want to see, out of the people that activated, how many have registered...

Makes me think you need the license id. Store it as a degenerate dimension... you do not need another dimension table.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Cross reference facts and dimensions

Post  rahm0277 on Sat Apr 30, 2011 11:35 pm

Got it. But I guess my real issue is how do I make an OLAP cube, so that these numbers are easily available without the user having to do MDX queries? I can't really pre-summarize these, because I have about 15 different saletypes, and about 20/30 separate events! And they are not really sequential - they can jump steps, and the events can be repeated...

rahm0277

Posts : 6
Join date : 2011-04-24

View user profile

Back to top Go down

Re: Cross reference facts and dimensions

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