Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Book: Professional Microsoft SQL Server Analysis Services 2008 with MDX: 978-0-470-24798-3
This is the forum to discuss the Wrox book Professional Microsoft SQL Server Analysis Services 2008 with MDX by Sivakumar Harinath, Robert Zare, Sethu Meenakshisundaram, Matt Carroll, Denny Guang-Yeu Lee; ISBN: 9780470247983
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Book: Professional Microsoft SQL Server Analysis Services 2008 with MDX: 978-0-470-24798-3 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Display Modes
  #1 (permalink)  
Old April 29th, 2011, 10:28 PM
Registered User
Join Date: Apr 2011
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Lifecycle Tracking in Data warehouse

I am trying to design a data warehouse for a 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) of 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).

Also, I was also thinking that maybe I can have 1 fact table, and I can 'technically' combine the saletype and the eventtype into a big eventtype dimension. So, then in the fact table would be a big transaction fact table, which will have an eventid foreign key to the events dimension. But still, so now I have a fact table, with a row for every event that happens to a license. The license is repeated, and one event can appear for an event more than once (on different dates). So, if I choose to see the totals for an event, how can I see how many of those licenses also exist for a different event?

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

Just as a reference, this is what I have now:
1.DimProducts (PK: ProductID, and other attributes)
2.DimDate (PK: DateKey, and other attributes)
DimEvent (PK: EventID, and oither attributes)

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

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Tracking My mileage rkellogg VB How-To 2 September 6th, 2006 11:19 PM
Tracking who is changing what? rrhandle SQL Server 2000 1 August 15th, 2006 03:03 AM
Page Lifecycle Aaron Edwards ASP.NET 2.0 Basics 3 May 4th, 2006 11:13 PM
SESSION tracking natmaster Beginning PHP 1 August 18th, 2003 01:52 PM

All times are GMT -4. The time now is 10:55 AM.

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.