Fusion Analytics Warehouse
FAW Extensibility Use Case - Add New Custom Calculated Measures to Existing Fact Folders in HCM Work
This post covers following high level steps for adding new custom measures/metrics to out of the box (seeded) HCM subject areas.
A. Identify custom measures
B. Construct the formula
C. Extend Semantic Model
D. Publish, test and merge Semantic Model
E. Version and Promote to production
1. Identify custom measures that are frequently used by your business users. Let’s consider an example, “APAC Headcount”.
2. Construct, validate and test your custom formulae in OAC projects:
FILTER("HCM - Workforce Core"."Facts - Workforce Headcount"."Headcount" USING "HCM - Workforce Core"."Location"."Country" IN ('India', 'Philippines', 'China', 'Singapore'))
Few more examples:
Twelve Month Rolling Attrition:
(((PERIODROLLING("Core"."Fact - Assignment Event"."Voluntary Termination Headcount"+"Core"."Fact - Assignment Event"."Involuntary Termination Headcount", -13,-1)))/((((PERIODROLLING("Core"."Fact - Assignment"."Headcount", -13, -13))+(PERIODROLLING("Core"."Fact - Assignment"."Headcount", -1, -1)))/2)))
EMEA Employee FTE:
FILTER("Core"."Fact - Assignment"."Employee FTE" USING "Core"."Dim - Worker Location"."Country" IN ('UK', 'France', 'Germany'))
3. Semantic Model Extensions:
a. Create a custom branch
In Oracle Fusion Analytics Warehouse, open the Application Navigation menu, click Console, and then click Semantic Model Extensions. On the Semantic Model Extensions page, click Create and then click Create Branch to create a branch as an empty container. In Create a Branch, enter a name for your branch, for example: Custom Metrics.
b. Add a Step
On the branch page, click Add Step -> Add Derived Columns
Add details for the step, choose HCM - Workforce Core for the target subject area and Facts - Workforce Headcount for the Folder.
Create columns for custom measures:
You can add other columns here - click on Create Column
When done adding all columns - Click on Finish.
c. Merge, Version, and Promote
On the Semantic Model Extensions page, under Other Branches, hover over a branch to view the Actions menu. Click Merge with Main
Create a Version and include the merged branch
Custom branch is merged with Main - and versioned, it's now ready for Promotion (migration) to Production instance.
Verify the custom measures show up in HCM - Workforce Core subject area -> Facts - Workforce
d. Promote the changes to Production Environment
In Oracle Fusion Analytics Warehouse, open the Application Navigation menu, click Console, and then click Promote Objects. You see the Promote Objects page.
On the Promote Objects page, select the target environment to which you want to publish your customizations. Select the Semantic Model Extensions check box and the saved/latest version of the main branch, in our case, it is - Custom_Measures_V1