Issue with OBIEE 11G. Records being counted twice

0
votes

My development team is working with OBIEE 11G to make an analysis as follows:

Discover which policyholders are in alert. An alert is defined as this: when the quantity of claims of a policyholder is superior to a certain threshold. If the policyholder has at least one alert in one of its claims, then the policyholder is in alert. The problem is that those thresholds are defined to a particular key (combination of type of client, range of age, type of pathology and other stuff) and a policyholder can have many keys and a threshold for each key, so the quantity of claims varies. Something like this:

Policyholder    Key #Claims Threshold
ABC123          XYZ  3       4
                WQE  3       2
EFG456          ABC  1       2

The ABC123 policyholder has 6 claims in total, 3 for the key XYZ (which has a threshold of 4) and 3 for the key WQE (which has a threshold of 2). On the other hand, the EFG456 policyholder has 1 claim for the key ABC that has a threshold of 2. So in this case, ABC123 policyholder should be in alert because the quantity of claims for the key WQE is greater than the threshold.

So, in OBIEE 11G my team added two columns, one to mark the records in alert and one to mark the records which are not in alert. Like this:

Policyholder    Key #Claims Threshold   Alert   notAlert
ABC123          XYZ  3       4           0       1
                WQE  3       2           1       0
EFG456          ABC  1       2           0       1

You see the problem now? OBIEE 11G does not see policyholder ABC123 as a unit and mark it both as in alert and not in alert, which is wrong. The correct info should be:

Policyholder    Key #Claims Threshold   Alert   notAlert
ABC123          XYZ  3       4           0       0
                WQE  3       2           1       0
EFG456          ABC  1       2           0       1

Because, it doesn't matter if the policyholder did not reach the alert for key XYZ. If an alert is discovered, the complete file of the policyholder is examined to resolve the alert.

Is there anyway of telling this to OBIEE 11G???

Please help!!

2
oracleoracle11gbusiness-intelligenceobiee
Could you just create your report (or dashboard) and set a filter to only show where Alert = 1? You said you don't care how many lines (keys) a PolicyHolder has, only that one of those lines are in alert. So if you create a report to show you only where Alert = 1, you could filter out the lines where Alert = 0? Also I suppose you could use a sub-report to filter out where Alert =0 so you would be left with only the policyholders you care about. Would that work? Also, why have two flagging columns? Why not have only Alert = 1 for an alert and Alert = 0 for nonAlert? Why have nonAlert at all? – Mark P.

2 Answers

0
votes

I think this is a dimensional modeling problem instead of an OBIEE one: In order to help I will make a few assumptions:

  1. PolicyHolder and Key are separate dimensions: Although the "key" dimension contains some attributes from the policyholder, such as type of client and age group; it also combines other entities like pathology and to me that is enough to consider it at least a mini dimension.

  2. The "Is in alert flag" can be modeled as a factless fact table: It looks like you only need to know if a particular policyholder is in alert, there is no metric associated with the event and you only need a flag that is either 0 or 1. This can be solved with a simple table that includes at least 3 columns: FK_POLICYHOLDER,FK_DATE and the flag. You already have a flag but it is included in the claims table as a calculated column, if you model this flag as a separate table you will have control of the dimensionality and granularity of the alert. See What dw model is appropriate when there's no measure?.

  3. The metric "number of claims" has a different dimensionality than the alert flag. I think the crux of the problem is that flags are calculated at the key level but for reporting purposes are only needed at the Policyholder level. If you want alerts to be assigned to a PolicyHolder "as a unit" then you need a fact table that is linked to the PolicyHolder dimension and NOT LINKED to the key dimension

Concretely:

  • Create a separate dimension table for your "Key" entity (type of client, pathology, etc.)
  • Create a new factless fact table that contains alerts for a policyholder, this table should not link to the Key dimension.
  • Change the "Alert" column in your report, you should get that value from the flag counter of your new factless fact table.
0
votes

Firstly, the ALERT columns seem redundant. It's an incredibly simple calculation that would be better done by OBI dynamically. That way you can check for policy holders in alert (on the aggregate of their keys) or for each key.

If I wanted to fix that calculation in OBI I would do it with a calculated logical column in the BMM (based on other logical columns) simply evaluating CLAIMS against THRESHOLD:

CASE WHEN CLAIMS >= THRESHOLD THEN 1 ELSE 0 END

That way the flag can work at multiple levels (either for POLICYHOLDER or KEY). But it seems a very simple calculation that could just be done in the Analysis as a filter (or selection step).

Even simpler though (assuming you have CLAIMS and THRESHOLD as measure columns with a SUM aggregation, and POLICYHOLDER and KEY as dimension column) would be to ignore any sort of alert column altogether. If you don't bring KEY into the Analysis OBI would give you each policy holder, their total claims and total threshold. You could then use selection steps or a filter in the criteria to remove those not over the threshold.