EPM and ERP Analytics Calendar Mismatch - Custom Column Joins?

Rob Toogood

It would be useful to allow a custom columns created in semantic model branch extensions to be selected as primary keys. Not all data is perfectly formatted across different systems and often use different conventions as standard - e.g. ERP (Fusion) uses either "Jan-2024" or "1" for period and EPM applications seem to use "Jan" as the standard name/value for period… making joining EPM data to Fusion Ledger data difficult - especially in a big organisation where different teams are responsible for different elements of an extensive Oracle implementation (where many different Oracle applications are being rolled out simultaneously that are all interconnected).

It would be useful to have the flexibility in FDI to get around this by creating custom columns using expressions to bridge the gap rather than running SQL in ADW on the OAX_USER/Custom Schema.

I for example have created an expression which utilises EPM attributes "Jan" period and "FY24" planning year to obtain the correct format "period" which could be joined to ERP data on the standard accounting period attribute.

CONCAT(CAST("OAX$OAC"."#prefix#DW_PBCS_X_FAW_EPM_PLAN_F"."PERIOD" AS varchar(3)), CONCAT(cast('-20' as VARCHAR(3)), CAST(RIGHT("OAX$OAC"."#prefix#DW_PBCS_X_FAW_EPM_PLAN_F"."YEAR", 2) AS VARCHAR(2)))) - it's dirty and inefficient but it works and would work until the turn of the next century

Alternatively… can Oracle product teams get together and agree upon a standardised uniform calendar that works seamlessly across the Oracle ecosystem allowing integration between applications and realise the full potential of a product like FDI?