Transactional fact question...

We want to design a fact table to track some counters say New Sales, Active counters, Annual Premium.
Planning to build a transactional fact with effective dates as the counters can toggle between on and off instead of building a daily snapshot fact table. Plan to use 1 if it's a new sales or active, -1 if a policy is cancelled and 0 if a column does not change.
 

Code:


      NewSales1stLevel NewSales2ndLevel               Active     AnnPrem   From Date    To Date
123            1                      0                  1          1200  1/1/2013   1/9/2013
123            0                      1                  0             0  1/10/2013 1/14/2013
123            0                      0                  0             0  1/15/2013 1/31/2013
123            -1                    -1                 -1             0  2/1/2013  12/31/9999
 

We can answer following questions by just summing up the counters.
 
How many NewSales1stLevel on a particular day/week/month/year
How many NewSales1stLeve2 on a particular day/week/month/year
 
But, it won't be correct for the following question.
 
How many ACTIVE on a particular day/week/month/year as it's not for that day but for till that date.
 
In the example above, until 1/31/2013 this policy is active but if i plug in a date it won't give me a correct result. How do we handle this situation without having special code to handle the ACTIVE ? Other attributes are on daily basis but this one is over period. I can make it work by putting the same value until it changes but that's different from other columns which may cause confusion.
 
Please share your thoughts on how to design this.

Hi,

if you want to find
"How many ACTIVE on a particular day/week/month/year as it's not for that day but for till that date."

then what about if you check if your that date is falling in the from_date and to_date range, and if it is falling in that range then take that row in the count.

thanks

Himanshu

Himanshu
 
It won't work as If I pass a date 1/15/2013 then I will get 0 for active but it's actually active on that day. So, I need to use 1 in every row as long as it's active but something else changed like illustrated below.
 

Code:


     NewSales1stLevel NewSales2ndLevel               Active     AnnPrem   From Date    To Date
123            1                      0                  1          1200  1/1/2013   1/9/2013
123            0                      1                  1             0  1/10/2013 1/14/2013
123            0                      0                  1             0  1/15/2013 1/31/2013
123            -1                    -1                 -1             0  2/1/2013  12/31/9999


 
But now If I say get me all the actives for the month of January, it will give me 3 which is wrong where as it's actually 1. It's kind of Semi Additive and I can't sum it across the time but this is going to confuse a novice user so wondering what's the best way to model this.  
 
Any view is appreciated.

Have you considered more than one fact table? The thing is you are trying to track different incompatible things in the same table.

ngalemmo

Yes. I am keeping that option also open and I understand that these are two different type of attributes but isn't this normal to have additive and non-additive measures in the same fact table ?

Thanks

It's not a matter of additive or non-additive. In one case you have measures that record an activity (sales) while another you record a state (the customer is active). Placed in their proper context, all these measures are additive. Basically, the state measure is not at the proper grain.

It's not the customer status but Policy status. We are counting policies here but the difference is that one represents point in time and the other one is over the period of time.

But, I see your point that Sales and Status are two different context.

Thanks

this is little off for fact but have indicator column of "Y","N"
in this case
 
    NewSales1stLevel NewSales2ndLevel     Active    AnnPrem  IND     From Date    To Date
123            1                      0                  1          1200     Y        1/1/2013   1/9/2013
123            0                      1                  1             0       Y        1/10/2013 1/14/2013
123            0                      0                  1             0       Y        1/15/2013 1/31/2013
123            -1                    -1                 -1            0       N        2/1/2013  12/31/9999