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

piotr-bratek — Fri, 20 Jan 2023 11:25:57 GMT

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?

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

radoslawkorga — Fri, 20 Jan 2023 09:33:09 GMT

" 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?

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

piotr-bratek — Fri, 20 Jan 2023 10:51:30 GMT

Yes, it is - even 1 day is enough.

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

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

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

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