Help with measure for first Real project

Question

I've been getting familiar with power pivot and convinced my boss to allow me to do a project with PP

The report is premium for insurance policies.

now i'm stuck and looking for some guidance. 

underlying data is a snowflake, fact tables around 60M rows.

The measure is total premium for a policy, as of a date,

This is calculated by finding the max endorsement number , finding the coverages for that endorsement and summing all like coverages for all endorsements less than the max endorsement number.   

example

policy  	endorsement     coverage	prem
APF6051188     
		0		CRIM			1
		0		GENL		2
		0		LIAB			3
		1		CRIM			1
		1		GENL		2
		1		LIAB			3
		3		CRIM			1
		3		GENL		2
		3		MGMT		5

so for the example above the max endorsement is 3

the coverages for endorsement 3 are CRIM,GENL,MGMT  so only those coverages are summed, total is 14

the LIAB coverage from endorsement 0 and 1 do not get added.

my powerpivot has two tables,

policy and facttrans

policy has policyseqid, policy and endorsement, fact has policyseqid, premium and coverage

joined by policyseqid

I've been able to select the policies by date range but i am not sure how to attempt the premium calculation.

Thanks for any help or direction

--scott

Answer 1

Could you provide samples of your tables?

Answer 2

I have to create a dimension table ‘Coverage’ and establish relationship with your original fact table on coverage:

Table2

COVERAGE

CRIM

GENL

LIAB

MGMT

Table1

endorsement	coverage	prem
0	CRIM	1
0	GENL	2
0	LIAB	3
1	CRIM	1
1	GENL	2
1	LIAB	3
3	CRIM	1
3	GENL	2
3	MGMT	5

Then I created a calculated column CoverageX in your original table Table1 to get whether this endorsement needs to be summed:

CoverageX :=CONTAINS(FILTER('Table1', [endorsement]=MAX([endorsement])), [coverage], [coverage])

Finally you need a measure for your total premium:

=SUMX(FILTER('Table1', [CoverageX]="TRUE"), [prem])

Now you can create a pivot table showing what you want:

Row Labels	Total Prem
CRIM	3
GENL	6
MGMT	5
Grand Total	14

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 3

Thanks for the reply,

I'm trying to work throught your logic and get an error on the contains function I'm on 2008r2 is that 2012 only?

Here is the actual data in excel.

https://skydrive.live.com/redir.aspx?cid=a4d0e74c1efb667e&resid=A4D0E74C1EFB667E!135&parid=A4D0E74C1EFB667E!113&authkey=!AAJcatyPX49G-jI

in this example policy GPPAPF605118-01 should total 12000

here is the sql that i am trying to duplicate in powerpivot, if that helps any one.

select dp.POLICY,SUM(WRITTENPREMIUM) 
from dwh.DIMPOLICY dp
cross apply (select POLICY, tricovdesc,max(a.endorsementnbr) as endorsementnbr
		from dwh.DIMPOLICY p 
		join dwh.DIMCOVERAGE dcv on p.POLICYSEQID=dcv.POLICYSEQID 
		cross apply (select MAX(endorsementnbr) endorsementnbr from dwh.DIMPOLICY p1 where p.POLICY = p1.POLICY and p1.ENDORSEEFFDATE <= '2012-03-25') a
		where '2012-03-25' >= policystatuseffectivedate AND '2012-03-25'  < policyinforcetodate and a.endorsementnbr=p.ENDORSEMENTNBR
		and dp.POLICY=p.POLICY 
		group by POLICY,TRICOVDESC) inf
join dwh.factpolicytrans f on dp.POLICYSEQID=f.POLICYSEQID
join dwh.DIMCOVERAGE cv on f.COVERAGESEQID=cv.COVERAGESEQID and inf.TRICOVDESC=cv.TRICOVDESC
where BUSINESS = 'GPP' and dp.ENDORSEMENTNBR <= inf.endorsementnbr
group by dp.POLICY

Answer 4

sr750,

Can you upgrade to PowerPivot 2012? It's free addin and works on Excel 2010. You can download it at http://www.microsoft.com/download/en/details.aspx?id=29074

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 5

i guess i can!  I was under the impression that powerpivot sqlserver 2012 would only work with sql server 2012, i guess not since i downloaded it and is seems to work. 

I was able to use contains but the max endorsement is not changing with the date filter.  i seem to have a misunderstanding of how the relationships work, i expected the max endorsement to change with the date selection.  

selecting january month end date should cause the maximum endorsement for GPPAPF6050812-01 to change to 6

https://skydrive.live.com/redir.aspx?cid=a4d0e74c1efb667e&resid=A4D0E74C1EFB667E!138&parid=A4D0E74C1EFB667E!115&authkey=!AFsXYCfgCvGzfDs

Thanks again

Scott

Answer 6

Scott, the slicer doesnt work because it doesnt have relationship with any other column. You need to establish some kind of relationship for it.

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 7

the calculated measure for policy premium has the slicer in it and it works as expected for that, I was expecting that to filter the result set, I guess i can understand it is only impacting the calculation where it is defined. 

Thank you for taking the time to try to help me wit this. I'm about out of time to get something working. I think it's time for me to just do this in SQL and get it done.  i hate to admit defeat, but i do not see a light at the end of the tunnel.

Thanks again

scott

Answer 8

Scott, PolicyPremium works because it's a calculated measure which refers to 'MonthEnd'[AccountingDate]. I tried to set up a relationship on this [AccountingDate] column but I dont know what column from which table to link to. If you set up a relationship for it, it will work.

Or, it'll work too if you can embed that relationship in your maxEnd measure, such as

maxEnd =CALCULATE(MAX(dimpolicy[ENDORSEMENTNBR]), <something with 'MonthEnd'[AccountingDate] here>)

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 9

ok, that makes some sense, i added the relationship in the measure for maxend, that is working ok now.  I tried to replicate your logic and add a measure with contains to just show true/false, i can't seem to get that to work.  I can't reference the coverage and tricov fields both?

https://skydrive.live.com/redir.aspx?cid=a4d0e74c1efb667e&resid=A4D0E74C1EFB667E!141&parid=A4D0E74C1EFB667E!115&authkey=!AFa4sPX90aJKITM

Answer 10

[Total Premium]=SUMX(FILTER('Table1', [CoverageX]="TRUE"), [prem])

Scott,

In my example, I didnt create a 'measure' with contains to show true/false. It's a 'calculated column' called CoverageX. And this calculated column is then used in the measure [Total Premium]

CoverageX :=CONTAINS(FILTER('Table1', [endorsement]=MAX([endorsement])), [coverage], [coverage])

[Total Premium] =SUMX(FILTER('Table1', [CoverageX]="TRUE"), [prem])

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 11

The max endorsement is date dependent, the report can run for a previous month end date and endorsements can be future dated.   My understanding of calculated columns is that they are not context sensitive.  I was trying to duplicate your logic as a measure so the date selected would change the calculation. in my sample data set with a january monthend date 2 of the policies have endorsments that are not in effect.

your example has coverage at the policy dimension level,  the actual data has it as a degenerate dimension in the fact. i added a coverage table like in your example, but can not seem to get the max endorsement in the policy to "see" the other tables (fact or coverage) even though they are all joined through tthe fact table. 

would it make more sense (and can it be done) to add a calculated column to dimpolicy that gets all coverages for the current endorsement and sums all matching coverages for the curernt and preceeding endorsements?  I realize that this would slow down refreshes because the calculations would take place when refreshing.

Thanks again for putting up with my beginner questions!

  

Answer 12

I think add a calculated column in dimpolicy would make more sense, at least for myself:)

Can you modify the calculated column to use MAXX instead of MAX to filter your max endorsement? such as,

=CONTAINS(FILTER('Table1', [endorsement]=MAXX(FILTER('Table1', <my filter>), [endorsement])), [coverage], [coverage])

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 13

Ok, after a lot of banging my head on the wall,  i couldn't get the calculated column working, but i have a measure that is working, but only if i am at the policy level, it is not rolling up correctly.  I'm sure the is due to the maxend measure being used in the InforcePremium calculation.  Any thoughts on how to correct it.

https://skydrive.live.com/redir.aspx?cid=a4d0e74c1efb667e&resid=A4D0E74C1EFB667E!115&parid=A4D0E74C1EFB667E!113&authkey=!AOFSfGwnQPUt_Fk

Thanks

Scott

Answer 14

I don't quite understand the columns in your workbook (inforce test - Editable), but if I create a relationship between 'MonthEnd'[AccountingDate] and  'Dimpolicy'[INFORCETODATE] column, the slicer will work without the need to modify my calculated column formula.

Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 15

couldyou refer the given link, may it help you.

https://www.ibm.com/developerworks/mydeveloperworks/blogs/woolf/entry/measuring_real_project_progress?lang=en

Answer 16

ok, i gave up on adding as a measure, I've added a calculated column that sums up the current endorsement and all prior endorsements with the coverage of the current endorsement. this seems to be working ok.  I've added a measure that selects the max endorsement for the selected month end date, but now the measure only works at the policy level and does not rollup.  any thoughts?

https://skydrive.live.com/redir.aspx?cid=a4d0e74c1efb667e&resid=A4D0E74C1EFB667E!144&parid=A4D0E74C1EFB667E!115&authkey=!AMCaYjbcEflV1tY

Thanks

Scott

Answer 17

Scott,

This is because of the filter condition you specified. You always filter the table to be dimpolicy[policyheaderseqid]=max(FactPolicy[policyheaderseqid]) && ...

Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 18

I thought that was probably the problem, so how can i select only the highest endorsement number that meets the date criteria for each policy but still have it roll up ?

Answer 19

You can try creating a new measure like this:

[New Measure]=IF(ISFILTERED(dimpolicy[POLICY]), [Measure 3], "ROLLUPRESULT")

where ROLLUPRESULT is something that is the sum of all policy level numbers (sorry I dont know how to get this one but maybe you know how:))

Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 20

https://skydrive.live.com/redir.aspx?cid=a4d0e74c1efb667e&resid=A4D0E74C1EFB667E!148&parid=A4D0E74C1EFB667E!115&authkey=!AAtlDsGxWssPH7I

I finally got it working, Thanks for your help, now if i can just get it to run faster

Answer 21

Great! What's your perf number look like? How many rows and how many seconds when you see the result after changing a slicer value?

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 22

with just the premium it is in the 3-5 second range, that that is ok, but that is with about 90k dimension 500k fact, that is a subset of the full data set, the full set is about 10x larger.  If that performance scales linear that will be a problem.  The other problem is if i add the policy and customer counts in it then slows to 10-15 seconds, I'm sure that is caused by the filter on the counts, but that is the only way i can seem to get only the accounts and policies with premium.

Thanks

Answer 23

I think probably it's because of multiple use of EARLIER function. Can you send me a makeup subset workbook that shows the perf issue? (takes around say 10 seconds or so where you expect it to finish in 1-5 seconds). My microsoft alias is chuxu.

Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights