Avg order count per active partner - multi-fact table, slowly changing dimension and a common date

[piotr-bratek]

Hi Guys,
What’s the best way to solve the following issue? I’d like to get the following chart.

E-commerce industry. There is a marketplace where you and me can sign a partnership agreement on a sort of Ebay platform and offer our products there. There is a KPI needed - “Avg order count per active partner”. The challenge is the partner base is changing (a partner can cancel the contract earlier) and the measure must be calculated within monthly periods.  We actually have two/three different processes (facts)
– orders,
– targets (for the KPI)
– and perhaps partner recruitment if we consider that as a fact.

In addition to that we have large data volumes (dozens of millions of rows, hundreds in the future) and actually the existing Fact table includes order details as well + perhaps some other facts. A simplified overview of data sources is presented below:

What would you do in terms of the data model and the final formula?

[radoslawkorga]

"  the measure must be calculated within monthly periods"
... what if partner cancel partnership...? how is he then recognized in given month... as active or not?

[piotr-bratek]

Yes, it is - even 1 day is enough.