I've got a specific question how to design an OLAP cube in our data warehouse. We are an insurance company and I'm designing the data warehouse for the claims department.  We have three significant dimensions: claim_type_dim, branch_office_dim, and date_dim.   The measures we use are: claims_registered, claims_closed, open_claims.

A typical report we need to produce will show how many claims were registered, how many were closed, and how many are open - in a specific month, by claim_type_dim and branch_office_dim.

In the cube, each claim appears with a claim_id, and the month of the transaction of that claim. If the claim was opened that month, then I put 1 in the claims_registered measure. If the claim was closed that month, I put 1 in the claims_closed measure. If the claim is still open at the end of the month, I put 1 in the open_claims measure.

If the claim was registered in a previous month, but closed this month, then we'll see in the cube that in this month, claims_registered is 0 and claims_closed is 1. 

If the claim was registered in a previous month, but is still open this month, then this month, claims_registered and claims_closed are both 0, but open_claims is 1.

In the report, I sum up claims_registered according to date_dim, claim_type_dim and branch_office_dim. The problem is that when I drill through,  I get all the claims that appear in the cube that month, even though claims_registered may be 0 (as explained above).

Is this a design problem? If anybody has another idea for a design, I will be happy to hear it. 

Thanks.

Sharona

Hi Sharona,

It looks to me more like a design issue:

I assume your fact table contains the following fields:

ClaimID,
Month of transaction,
BranchID,
Date,
Claims_Open,
Claims_Closed,
Claims_Registered,
...

Baed on your method, the Grain of the DW is defined as Claim and ClaimID is unique key, which caused the problems in my point of view. Here's a quick example:

Assume a claim arrive in August 15th, 2013, then it is closed on September 15th, 2013. Your August Report will contain the following record:

ClaimID: 1
Month of transaction: 8
BranchID: 1
Date: 15/08/2015
Claims_Open: 1
Claims_Closed: 0
Claims_Registered: 1

Then the record is updated as follows in September Report:
ClaimID: 1
Month of transaction: 9
BranchID: 1
Date: 15/09/2015
Claims_Open: 0
Claims_Closed: 1
Claims_Registered: 0

Therefore when you drill down by month, the above claim will still appear, although the Claims_registered is 0. Therefore you could either use SCD2 on those fields and set CliamID as Business Key, or set transaction as Grain and record each transaction -- you will have 2 seperate records store in the fact table:

TrasactionID: 1
ClaimID: 1
BranchID: 1
Month of transaction: 8
Date: 15/08/2015
Claims_Open: 1
Claims_Closed: 0
Claims_Registered: 1

Transaction ID: 2
ClaimID: 1
BranchID: 1
Month of transaction: 9
Date: 15/09/2015
Claims_Open: 0
Claims_Closed: 1
Claims_Registered: 0

Also you need to think about the following cases:
Claim Reopen,
Claim Branch transfer  -- More than one staff from different Branch deal with the same Claim.

Hope you find that helpf, cheers.

Regards

stephen