Multiple Fact table with different grain w/conformed dimensions - BO Challenge

Hi,

I manage an ad hoc reporting environment on a dimensional model in which in many instances users need to report against multiple fact tables (different grain, sharing dimensions). The client tool (Business Objects) builds the tool based on what the user selects (no free hand SQL). A query is generated for each fact table by BO.
Data in these 2 fact tables are at different level. The challenge we are facing in BO is, when measure object from fact1 is dragged to result panel and a dimension object from other star(i.e. data containing fact2 w/detail measures) as part of the filter condition with conformed dimension used as predefined context path. Value in measure object doubles (i.e., multiplies) depending on number detail entries associated to it.

Policy, policy premium, policy status is in fact1. (there several other dimensions that are linked to this star).
Policy, Rider/benefit, rider premium, rider status is in fact2 w/more detail grain. (Similarly there are several other dimensions linked to this star.)
Common predifined context and access path between these 2 facts is via policy.

In business objects, if you pick measures from both fact, you get correct value. BO produces separate queries for each measure and merges the result later. If you pick measure from one fact and only dimension from other star in the filter condition (not result object), it does a direct join across two facts using policy that are at different granular level (i.e it does not produce separate query since fact2 is not part of result object, only filter condition) producing incorrect measure value.

Basically, I want BO to build sub-query that filter data in fact2 (detail table) based on filter condition and pass that information to main fact to get the appropriate value.

Any thoughts on how to solve it via BO or at database leve before passing it to BO?

If you pick measure from one fact and only dimension from other star in the filter condition (not result object), it does a direct join across two facts using policy that are at different granular level (i.e it does not produce separate query since fact2 is not part of result object, only filter condition) producing incorrect measure value.

BO will do that if the dimension and the measure are in the same context and you have defined join paths in that context to do what you describe. The problem is the context. The correct way to set up a Universe against a dimensional model is to have each star in its own context. Do not define a context that joins stars. If that dimension is applicable to facts in the other star, that dimension should have been part of the star to begin with.

[quote="ngalemmo"]

BO will do that if the dimension and the measure are in the same context and you have defined join paths in that context to do what you describe. The problem is the context. The correct way to set up a Universe against a dimensional model is to have each star in its own context. Do not define a context that joins stars. If that dimension is applicable to facts in the other star, that dimension should have been part of the star to begin with.

If I have independent context for each of it, I wouldn't be able to corelate data between two stars, in this case, Policy level data and Rider data. It's a ad hoc environment, we want to give users flexibility to traverse across seemlessly.
Example: If users want to see total premium for policies that have rider xyz.

Also, if we have seperate context and all required dimensions are in both the stars including conforming dimensions, then we would be duplicating the objects in BO. More confusion to users.

Does this make sense or am I missing something? Please advice.

Thanks!

sr_da wrote:
If I have independent context for each of it, I wouldn't be able to corelate data between two stars, in this case, Policy level data and Rider data. It's a ad hoc environment, we want to give users flexibility to traverse across seemlessly.
Example: If users want to see total premium for policies that have rider xyz.

Also, if we have seperate context and all required dimensions are in both the stars including conforming dimensions, then we would be duplicating the objects in BO. More confusion to users.

Does this make sense or am I missing something? Please advice.

Thanks!

No. First, if each star is in its own context (the fact table and its dimensions), and you queried measures from two facts, BO will generate two queries and combine results on the common dimensions. Second, it does not require additional objects. You have the normal objects for each table. It is the fact that an object is common to both contexts tells BO that it is a conformed value to match results across facts.

I am sorry, that is exactly how we have now. BO is generating two queries and is combining results on the common dimensions.
This was working perfectly fine as long as measures were picked from both stars as result (i.e. display) objects. If you pick measure from only one fact and have dimension object from other star (i.e., just in filter condition not in result object), BO will not generate 2 separate queries. It automatically does a direct inner join between two stars and applies the filter condition and inflates the aggregated value.

Example:

Star1.Fact1 is policy level
Star2.Fact2 is Rider level (Each policy can have one or more riders and related premium and face amount.)

In ad hoc environment:
Result object: Policy level total annual premium.
Filter Condition: Where Rider Name="ABR"

BO generates sql as follows:

select sum(total annual premium) from fact1, policy, fact2, rider_dimension
where fact1.policy_keyid=policy.policy_keyid
and policy.policy_keyid=fact2.policy_keyid
and fact2.rider_id=rider_dimension.rider_id
and rider_dimension.rider_name='ABR'

fact2 is much lower level grain compared to fact1, this causes total_annual_premium to be inflated.

Expected correct query is:
select sum(total annual premium) from fact1, policy
where fact1.policy_keyid=policy.policy_keyid
and policy.policy_keyid in( select distinct fact2.policy_keyid from fact2, rider_dimension
where fact2.rider_id=rider_dimension.rider_id
and rider_dimension.rider_name='ABR')

Please let me know.

BO will NOT generate the query you describe unless these joins:

fact1.policy_keyid=policy.policy_keyid
and policy.policy_keyid=fact2.policy_keyid
and fact2.rider_id=rider_dimension.rider_id
and rider_dimension.rider_name='ABR'

are all in the same context. OR you have defined a derived table that includes those joins.

We are trying multiple options, with separate context and single predefined path in a context across multiple stars. No luck!

Option-1:
If we use separate context we get incompatible query error: Common dimension used in both context is Policy.

Result Objects-
Star1.Fact: Policy Annual Premium
Condition object-
Star2:Rider.RiderName=ABR (or Rider status="Submitted")

BO throws an error, incompatible filter object error, since filter condition is dimension object from another star which is not part of result object.

If I add a measure rider permium to result object from Star2, BO will produce 2 separate queries but the result is incorrect. Please see the query below:

Query1:
select sum(total annual premium) from star1.policy fact
Query2:
select sum(total rider premium) from star2.rider_fact, Rider_dim where rider_dim.RiderName='ABR'
and rider_fact.riderid=rider_dim.riderid

The merged result from these 2 are incorrect. We do not have total premium on policies that have rider='ABR' from the above query result.

Option-2:
If we define single context, it produces the result as I mentioned in my prior post.

I thought this is very commonly used in BO. You have 2 Stars, with measure from star1 is used as result object and filter condition/object based on star2.dimension.

No luck, so far. Hope, I am more clear on the issue this time.

sr_da wrote:We are trying multiple options, with separate context and single predefined path in a context across multiple stars. No luck!

Option-1:
If we use separate context we get incompatible query error: Common dimension used in both context is Policy.

Result Objects-
Star1.Fact: Policy Annual Premium
Condition object-
Star2:Rider.RiderName=ABR (or Rider status="Submitted")

BO throws an error, incompatible filter object error, since filter condition is dimension object from another star which is not part of result object.

Correct... that is what is supposed to happen if the contexts are set up correctly.

sr_da wrote:If I add a measure rider permium to result object from Star2, BO will produce 2 separate queries but the result is incorrect. Please see the query below:

Query1:
select sum(total annual premium) from star1.policy fact
Query2:
select sum(total rider premium) from star2.rider_fact, Rider_dim where rider_dim.RiderName='ABR'
and rider_fact.riderid=rider_dim.riderid

The merged result from these 2 are incorrect. We do not have total premium on policies that have rider='ABR' from the above query result.

Option-2:
If we define single context, it produces the result as I mentioned in my prior post.

Correct again, which is why I suggested your contexts were incorrect.

The fundimental problem is you are trying to use a dimension that is not in the fact table from which you are getting measures. The only way this would work is you need a rider dimension for the policy annual premium fact. Creating a new multivalued dimension bridge and subsequent changes to the fact table would be the correct way to go... but that is a lot of work. Another approach would be to create a view or, in BO, define a derived table that does the same thing. It would not peform as well as a pysical DB change, but will solve the problem.

The new view or derived table would return distinct values of policy ID, rider code and other rider attributes based on a join of the rider dimension and the rider level fact table. This would act as a dimension of the policy annual premium fact table using the policy ID as the FK.

If someone queries for a specific rider, it will produce correct results (due to the distinct constraint). If they query for more than one rider, they must group totals by rider and the grand total should not be used.

ngalemmo wrote:
The new view or derived table would return distinct values of policy ID, rider code and other rider attributes based on a join of the rider dimension and the rider level fact table. This would act as a dimension of the policy annual premium fact table using the policy ID as the FK.

If someone queries for a specific rider, it will produce correct results (due to the distinct constraint). If they query for more than one rider, they must group totals by rider and the grand total should not be used.

Appreciate your patience, I would get this right. :-)

- View would pretty much contain everything related to other star along with policy ID excluding rider measures(i.e., rider premium, face amount etc.). So, we would have each policy ID with more then on rider in the view.
- I need to include the new view as dimension using policy ID (fk) to policy level fact table. (The link to fact will be distinct policy ID).
- To achieve this, we did overide the regular equi join in designer and changed it to policy fact.policy ID in(select distinct policy ID from Policy_Rider_view).

Here is the BO sql:
select sum(policy premium) from policyfact, policy_rider_vw
where policyfact.policy ID in (select distinct policy ID from policy_rider_vw)
and policyfact.policy ID = policy_rider_vw.policy ID
and policy_rider_vw.rider_name='ABR'

We want in the above query, rider_name to be part of the internal sub query not outside as regular join with policy fact.

If users pick Rider status as filter condition and a policy contains 2 or 3 riders, the BO query would inflate the numbers again.
select sum(policy premium) from policyfact, policy_rider_vw
where policyfact.policy ID in (select distinct policy ID from policy_rider_vw)
and policyfact.policy ID = policy_rider_vw.policy ID
and policy_rider_vw.rider_status='Submitted'

Policy Fact
POLICY ID Policy Premium
12 $100
13 $200
14 $200

Policy Rider View
POLICY ID RIDER Rider Status
12 Av Submitted
12 AB Submitted
12 AC Issued
13 AB Submitted
14 AB Submitted

View will still contain more then one row per policy that affects the query.

You would still use an equijoin to the derived table.

You define the derived table as follows:

select distinct f.policy_id, d.rider_name, d.rider_desc (etc...)
from rider_facts f, rider_dim d
where f.rider_fk = d.rider_pk

You expose the rider dimension columns of the derived table as new objects.

When you query the SQL would look like:

select sum(f.policy premium), dt.rider_name
from policyfact f,
(select distinct f.policy_id, d.rider_name, d.rider_desc (etc...)
from rider_facts f, rider_dim d
where f.rider_fk = d.rider_pk
and d.rider_name = 'ABR') dt
where policyfact.policy ID = dt.policy ID
group by dt.rider_name

For the numbers to be correct, you MUST include rider attributes so that totals are grouped by rider. With a derived table, this will be enforced by BO since it will not use the derived table or view unless it includes attributes from it. As mention before, if more than one rider is selected, the grand total will NOT be correct. There is nothing you can do about that... that is the basic problem with a many-to-many relationship.

Define the new rider objects in a subfolder below the policy facts, and include the join to the derived table in the context for policy facts. Users must select one of these new rider objects to report policy facts by rider. If they use the original rider objects off the rider dimension, BO will give an error because those objects are not in the correct context.

ngalemmo wrote:
select sum(f.policy premium), dt.rider_name
from policyfact f,
(select distinct f.policy_id, d.rider_name, d.rider_desc (etc...)
from rider_facts f, rider_dim d
where f.rider_fk = d.rider_pk
and d.rider_name = 'ABR') dt
where policyfact.policy ID = dt.policy ID
group by dt.rider_name

When we create derived table in BO or a view, the sql it creates is predefined select statement. It does not add any condition as part of the inner predefined select statement (overall select stmt is considered as a table). It applies the filter condition outside which causes the issue.

select sum(f.policy premium)
from policyfact f,
(select distinct f.policy_id, d.rider_name, d.rider_desc (etc...)
from rider_facts f, rider_dim d
where f.rider_fk = d.rider_pk) dt
where policyfact.policy ID = dt.policy ID
and d.rider_name = 'ABR'

This still okay since the filter condition is based rider name.

If you change that now by rider status:
- And if more then one rider is associated to same policy
- And if we have more then one rider associated with same Rider status,
Then the value will inflate the value.

Should I be using weighting factor (i.e., count), to arrive at the premium value?

Yes, you could include a count (i.e. a constant value 1 in the view or derived table) to calculate an average, which would be defined as an object as sum(policy_amt)/sum(count)

So, this means we don't have any other option in BO for this correct?

That's about it with what you have. The problem you have has nothing to do with BO actually... nor with the model. It is simply that a policy has many riders and, if you are reporting the total policy amount by many riders, it will not sum correctly. You need to calculate an average, or allocate by rider (which you already have in the rider premium facts), or group by rider.

As mentioned before, restructuring your policy fact table to support a multi-valued rider dimension with a bridge table will solve the problem and allow to to select policies with combinations of riders without double counting the amounts.

I am not sure if multi-valued dimension table will solve the problem.

I currently have
- Policy dim, Policy fact, agency, agent, insured, insured role etc.

- Policy dim, Rider fact, , rider dim, insured, insured role etc
(note: policy, insured is conformed dimensions)

When we create multi-valued dimension, we would do the following:
- Rider dim and rider status will remain as is.

- Will have a new dimension called policy rider group. This would be bridge table to policy fact and rider detail.
- Will have a new detail table, policy rider detail that links every rider entry to policy rider group dimension based on policy.
- Will also have policy rider group dimension that links to policy fact as dimension.

Question:

1. With multi-valued dimension, you still have groupID in policy fact that links to many riders when you traverse through bridge table, how will BO handle it differently? It still returns multiple rows with equi-join among these tables (back to my original filter only question).

2. How is this different compared to model that contains policy dimension that is bridge between policy fact and rider fact? I understand that in this approach you do not have groupID as dimension in policy fact, but still BO has to go through same equi-join. Also policyID itself acts as grouping between these tables.

A multi-valued dimension structure will not return duplicate results if queried properly. You can handle it in BO by aliasing bridge/dimension pairs up to the maximum number of riders you would let the user's choose (4 or 5 should suffice). A discussion of how to query is found in
http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/what-to-do-when-the-weighting-factor-of-a-bridge-table-no-longer-seems-relevant-t441.htm

Looks like in your prior post, the final line with URL link got cut off. Can you please post it again.

Also, if you have more then one multi-valued dimensions and both are in use at the same time, I am not sure if BO can handle it. I am wondering what's the optimal way to handle it via database or thru BO.

ngalemmo wrote:A multi-valued dimension structure will not return duplicate results if queried properly. You can handle it in BO by aliasing bridge/dimension pairs up to the maximum number of riders you would let the user's choose (4 or 5 should suffice). A discussion of how to query is found in
http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/what-to-do-when-the-weighting-factor-of-a-bridge-table-no-longer-seems-relevant-t441.htm

Thank you, it's a nice article. Bridge table concept works only for certain attributes in the dimension that have unique value, not for all.

In the diagnosis example:
The filter condition in your query was based on specific diagnose name "X" or "Y".
Let's say you have a claim with 3 Diagnosis and we have status on Diagnosis.

Claim Claim Amt
100.......$100
200.......$100
300.......$300

Claim DiagnosisName Diagnosis_Status
100..............X.............Opened
100..............Y.............Opened
200..............Z.............Verified

Let's create multi-valued dimension for above dataset.
Here is the query I would like to execute:
I would like to get pending total claim amount(aggregated field in BO) based on diagnosis status "Opened", the multi-valued query would still return 2 rows for Claim# 100 and inflate the claim amount to $200. (Note: Diagnosis name is not part of the condition.)

Multi-valued works only if the dimension column is specifically unique such as DiagnosisName='X' or 'Y'. Then you would get correct result.

Is my understanding correct or am I missing something here?

In summary, in above example multi-valued dimension cannot solve the problem.

Yes, the referenced query examples involved looking for specific combinations of codes, (code A AND B AND C, etc...) which was your earlier premise.

If you need to query as an aggregate of many dimension rows based on other attributes (or OR's of specific codes), you need to use an IN clause... fact_group_key IN (set of group keys satisfying dimension predicate)

You could do this with the policy ID but a multi valued dimension group would perform better as the number of groups will usually be far less than the number of policies.

ngalemmo wrote:Yes, the referenced query examples involved looking for specific combinations of codes, (code A AND B AND C, etc...) which was your earlier premise.

If you need to query as an aggregate of many dimension rows based on other attributes (or OR's of specific codes), you need to use an IN clause... fact_group_key IN (set of group keys satisfying dimension predicate)

You could do this with the policy ID but a multi valued dimension group would perform better as the number of groups will usually be far less than the number of policies.

Thanks! I just got back from vacation.
I agree with you, using sql we can always use "IN clause & Distinct" to ensure uniqueness or avoid incorrect aggregation. In BO we cannot force it to use "IN clause", irrespective of we using multi-valued bridge table or regular multiple fact table approach.

Multi Fact Table approach:
Policy Fact, Policy Dim, Rider Fact, Rider Dim, Rider Status
(Note: Policy Dimension acts as bridge b/w Policy Fact and Rider Fact)

Multi-valued Bridge table approach:
Policy Fact, Policy Dim, Policy Rider Group Dim (linked to Policy fact and Rider), Policy Rider Group Bridge (it contains rider amt, status etc.), Rider Dim, Rider status.

The end result in BO would be same per my earlier post. When BO filter condition is based on dimension value that have same value (i.e., 2 riders w/same status) in Policy Rider Group Bridge table, then Policy level annual premium will automatically be incorrect with regular equi-join. Unfortunately we cannot force BO to do IN clause or sub query using bridge table or regular approach.

This has been a challenging area to tackle not only for Riders, but for other functional areas (i.e., stars at different level) that I would like to relate by using conformed dimensions.

Same as the claims example:
Claim Claim Amt
100.......$100
200.......$100
300.......$300

Claim DiagnosisName Diagnosis_Status
100..............X.............Opened
100..............Y.............Opened
200..............Z.............Verified