Combining additive and semi-additive facts in a single report

Question

I'm working on a quarterly report. The report should look something like this:

col	Calculation	Source table
Start_Balance	Sum at start of time period	Account_balance
Sell Transactions	Sum of all sell values between the two time periods	Transactions
Buy Transactions	Sum of all buy values between the two time periods	Transactions
End Balance	Sum at the end of time period	Account_balance

so e.g.

Calculation	sum
Start_Balance	1000
Sell Transactions	500
Buy Transactions	750
End Balance	1250

The problem here is that I'm working with a relational star schema, one of the facts is semi-additive and the other is additive, so they behave differently on the time dimension.

In my case I'm using Cognos analytics, but I think this problem goes for any BI tool. What would be best practice to deal with this issue? I'm certain I can come up with some sql query that combines these two tables into one table which the report reads from, but this doesn't seem like best practice, or is it? Another approach would be to create some measures in the BI tool, I'm not a big fan of this approach because it seems to be least sustainable approach, and I'm unfamiliar with it.

Answer 1

For Cognos you can stitch the tables

The technique has to do with how Cognos aggregates

Framework manager joins are typically 1 to n for describing the relationship

A star schema having the fact table in the middle and representing the N with all of the outer tables describing/grouping the data, representing the 1

• Fact tables, quantitative data, the stuff you want to sum should be on the many side of the relationship
• Descriptive tables, qualitative data, the stuff you want to describe or group by should be on the 1 (instead of the many)

To stitch we have multiple tables we want to be facts

Take the common tables that you would use for grouping, like the period (there are probably some others like company, or customer, etc)

Connect each of the fact tables with the common table (aka dimension) like this:

• Account_balance N to 1 Company
• Account_balance N to 1 Period
• Account_balance N to 1 Customer
• Transactions N to 1 Company
• Transactions N to 1 Period
• Transactions N to 1 Customer

This will cause Cognos to perform a full outer join with a coalesce

Allowing you to handle the fact tables even though they have different levels of granularity

Remember with an outer join you may have to handle nulls and you may need to use the summary filter depending on your reporting needs

You want to include the common tables on your report which might conflict with how you want the report to look

An easy work around is to add them to the layout and then set the property to box type none so the sql behaves you want and the report looks the way you want

Answer 2

You'll probably need to setup determinants in the Framework Manager model. The following does a good job in explaining this: https://www.ibm.com/docs/en/cognos-analytics/11.0.0?topic=concepts-multiple-fact-multiple-grain-queries