Search code examples
data-warehousebusiness-intelligencecognosfactkimball

Combining additive and semi-additive facts in a single report

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.

Solution

  • 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