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