i trying design data warehouse licensing vendor, sells licenses on ecommerce , various other venues. things want track sales, product lifecycle , activity. means there different sale types (such new purchase, promotional purchase, renewal) , different events/states of license, such - license can installed, renewed, activated, registered. license can renewed many times (on different dates).
so thinking dimensions simple - date, product, source, saletype , event/state. have 2 fact tables; 1 sales, , events, both of them having foreign keys dimension tables. fact tables accumulating fact table, every event add new row - hence, licenses can repeated. however, requirements states able cross reference these 2 facts , saletype , event dimensions. example, if sees product 'a' has 100 sales in ecommerce store of type 'new purchase', want see how many of 'those' 100 licenses got activated... , maybe want see, out of people activated, how many have registered... , (back saletype) of how many of registered, how many of them 'renewed'. , cannot define heirarchy, because have whole lot of combinations of these....
how can this? i'm reading, find there seems no way relate 2 facts based on license (which need do).
also, thinking maybe can have 1 fact table, , can 'technically' combine saletype , eventtype big eventtype dimension. so, in fact table big transaction fact table, have eventid foreign key events dimension. still, have fact table, row every event happens license. license repeated, , 1 event can appear event more once (on different dates). so, if choose see totals event, how can see how many of licenses exist different event?
i need provide these numbers measures, business user can see them on fly (using whatever olap browser want use)
note: using sql server analysis services , sql server 2008 r2
just reference, have now:
- dimproducts (pk: productid, , other attributes)
- dimdate (pk: datekey, , other attributes)
dimevent (pk: eventid, , oither attributes)
factlicenses(fk: productid; fk: datekey; fk: eventid, , license field(varchar))
so have license repeated, event every time happens license (installed, activated, renewed, cancelled, renewed (again). possible there 1 license same eventid, never on same datekey. primary key of table datekey + eventid + license
edit:
so, i've read in many places fact table in situation should accumulating fact table, has multiple columns pointing same (type) of dimension - (i.e. date) , should create role playing dimension each 1 of those. how account fact license can renewed multiple times, , can installed multiple times, etc...?
i've since gone ralph kimball's book, , found case study can solve issue me. i've merged sale type , event types 1 major group. given that, there still 2 groups of things - things can happen license once, vs things can happen license multiple times. can happen license once stored in accumulating fact table. can happen licene multiple times stored in different table (a different table each entity or 'type' of event can happen).
this solved problem me, because in analysis services, able make called 'referenced' relationship, relationship 'license'. of dimensions related different table can linked via original accumulating fact table (that has license column).
thanks input, whoever has tried answer.
Comments
Post a Comment