Transactional fact question...

View previous topic View next topic Go down

Transactional fact question...

Post  VTK on Wed Sep 11, 2013 12:16 am

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.

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Transactional fact question...

Post  hkandpal on Wed Sep 11, 2013 10:05 am

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Transactional fact question...

Post  VTK on Thu Sep 12, 2013 7:24 pm

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.

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Transactional fact question...

Post  ngalemmo on Thu Sep 12, 2013 7:32 pm

Have you considered more than one fact table? The thing is you are trying to track different incompatible things in the same 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: Transactional fact question...

Post  VTK on Thu Sep 12, 2013 8:19 pm

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

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Transactional fact question...

Post  ngalemmo on Fri Sep 13, 2013 2:18 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Transactional fact question...

Post  VTK on Fri Sep 13, 2013 5:52 pm

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

VTK

Posts : 50
Join date : 2011-07-15

View user profile

Back to top Go down

Re: Transactional fact question...

Post  Kumpu on Tue Sep 17, 2013 1:13 pm

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

Kumpu

Posts : 2
Join date : 2013-09-17

View user profile

Back to top Go down

Re: Transactional fact question...

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