vendredi 20 novembre 2020

OLAP cube designing: runtime generated measures

I'm facing a requirement that I'm not entirely sure that can be achieved by pure OLAP designing (anyway, my OLAP knowledge is SO small).

I have a dimension table such as

DROP TABLE IF EXISTS TMP_DEBUG_OLAP_44_BUILDING;
CREATE TABLE TMP_DEBUG_OLAP_44_BUILDING AS
SELECT 1 AS building_id,'Building 1' AS building_name
UNION ALL
SELECT 2 AS building_id,'Building 2' AS building_name
;

and a table fact (fact-1) such as

DROP TABLE IF EXISTS TMP_DEBUG_OLAP_44_FACTS;
CREATE TABLE TMP_DEBUG_OLAP_44_FACTS AS
SELECT 1 AS building_id,10 AS budget_amount,11 AS estimate_amount
UNION ALL
SELECT 2 AS building_id,12 AS budget_amount,13 AS estimate_amount
;

and a factor-conversion fact table (fact-2) such as

DROP TABLE IF EXISTS TMP_DEBUG_OLAP_44_FACT_FACTORS;
CREATE TABLE TMP_DEBUG_OLAP_44_FACT_FACTORS AS
SELECT 'DK' AS country_id,1.0 AS FACTOR
UNION ALL
SELECT 'MX' AS country_id,1.5 AS FACTOR
;

I've been asked to generate a measure per tuple [Measures].[Budget amount]_vs_country, so that it adds, per tuple in fact-2, a measure, multipling [Measures].[Budget amount] * factor_value.

Example: fact-table-1 first row is building_id=1, I need 3 measures: [Measures].[Budget amount], budget-amount1.0(DK) and budget-amount1.5(MX).

Now, if another country is added into the tables, there's a new tuple, hence, a new measure.

Is there any way this could be achieved without modifying the cube?

Thanks and regards!

Aucun commentaire:

Enregistrer un commentaire