Managing Metadata Updates in EPM Cloud with Groovy: A Cheatsheet

Published: June 19, 2024 | By WeRun Consulting

Metadata in Oracle EPM Cloud defines the structure of dimensions and members—properties like aliases, descriptions, and UDAs. This cheatsheet demonstrates how to automate updates using Groovy and CSV files with csvIterator(), leveraging the Dimension and Member classes. Builds on our UDA Management guide.

Updating Metadata from CSV

Purpose: Automate metadata updates by reading member properties from a CSV file, ensuring scalability and accuracy.

Key Methods:

application.getDimension(dimName, cube): Retrieves the Dimension object.
dimension.getMember(memberName): Gets the Member object.
member.toMap(): Converts properties to a Map for editing.
dimension.saveMember(map): Saves updated or new member properties.
csvIterator(fileName): Parses CSV from the inbox.

Example 1: Update Aliases from CSV

Update Entity Aliases

Update default aliases for Entity members in "Plan1" using "EntityUpdates.csv".

// Get cube and dimension
Cube cube = operation.application.getCube("Plan1")
Dimension entityDim = operation.application.getDimension("Entity", cube)

// CSV file details
String csvFileName = "EntityUpdates.csv" // Format: MemberName,Alias
List errors = new ArrayList()
List updates = new ArrayList()

// Parse CSV with csvIterator
Iterator csvIter = csvIterator(csvFileName)
csvIter.eachWithIndex { String[] row, int idx ->
    if (idx == 0) return // Skip header
    String memberName = row[0]?.trim()
    String alias = row[1]?.trim()
    
    // Validate input
    if (!memberName || !alias) {
        errors.add("Row " + (idx + 1) + ": Missing member name or alias")
    } else {
        Member member = entityDim.getMember(memberName)
        if (!member) {
            errors.add("Row " + (idx + 1) + ": Member " + memberName + " not found")
        } else if (member.toMap().get("Alias: Default") == alias) {
            updates.add("Row " + (idx + 1) + ": " + memberName + " already has alias " + alias + " - skipped")
        } else {
            // Update alias
            Map memberMap = member.toMap()
            memberMap.put("Alias: Default", alias)
            entityDim.saveMember(memberMap as Map)
            updates.add("Row " + (idx + 1) + ": Updated " + memberName + " alias to " + alias)
        }
    }
}

// Output results
if (errors.isEmpty()) {
    println "Metadata updates completed successfully:"
    updates.each { println it }
} else {
    println "Errors detected, updates aborted:"
    errors.each { println it }
    if (!updates.isEmpty()) {
        println "Successful updates (not applied due to errors):"
        updates.each { println it }
    }
}

CSV Format: MemberName,Alias (e.g., "East,East Region").

Logic: Reads CSV, validates entries, updates "Alias: Default" if changed, and logs results.

Tip: Upload CSV to the inbox before running.

Use Case: Standardize entity aliases for reporting.

Example 2: Update Descriptions from CSV

Update Product Descriptions

Update descriptions for Product members in "Sales" cube using "ProductUpdates.csv".

// Get cube and dimension
Cube cube = operation.application.getCube("Sales")
Dimension productDim = operation.application.getDimension("Product", cube)

// CSV file details
String csvFileName = "ProductUpdates.csv" // Format: MemberName,Description
List errors = new ArrayList()
List updates = new ArrayList()

// Parse CSV with csvIterator
Iterator csvIter = csvIterator(csvFileName)
csvIter.eachWithIndex { String[] row, int idx ->
    if (idx == 0) return // Skip header
    String memberName = row[0]?.trim()
    String description = row[1]?.trim()
    
    // Validate input
    if (!memberName || !description) {
        errors.add("Row " + (idx + 1) + ": Missing member name or description")
    } else {
        Member member = productDim.getMember(memberName)
        if (!member) {
            errors.add("Row " + (idx + 1) + ": Member " + memberName + " not found")
        } else if (member.toMap().get("Description") == description) {
            updates.add("Row " + (idx + 1) + ": " + memberName + " already has description " + description + " - skipped")
        } else {
            // Update description
            Map memberMap = member.toMap()
            memberMap.put("Description", description)
            productDim.saveMember(memberMap as Map)
            updates.add("Row " + (idx + 1) + ": Updated " + memberName + " description to " + description)
        }
    }
}

// Output results
if (errors.isEmpty()) {
    println "Metadata updates completed successfully:"
    updates.each { println it }
} else {
    println "Errors detected, updates aborted:"
    errors.each { println it }
    if (!updates.isEmpty()) {
        println "Successful updates (not applied due to errors):"
        updates.each { println it }
    }
}

CSV Format: MemberName,Description (e.g., "ProdA,New Product A").

Logic: Similar to alias updates, but targets "Description" property.

Tip: Ensure descriptions are concise to fit UI display limits.

Use Case: Document product details for end-users.

Example 3: Bulk Update Multiple Properties from CSV

Update Alias and UDA for Entities

Update aliases and UDAs for Entity members in "Plan1" using "EntityBulkUpdates.csv".

// Get cube and dimension
Cube cube = operation.application.getCube("Plan1")
Dimension entityDim = operation.application.getDimension("Entity", cube)

// CSV file details
String csvFileName = "EntityBulkUpdates.csv" // Format: MemberName,Alias,UDA
List errors = new ArrayList()
List updates = new ArrayList()

// Parse CSV with csvIterator
Iterator csvIter = csvIterator(csvFileName)
csvIter.eachWithIndex { String[] row, int idx ->
    if (idx == 0) return // Skip header
    String memberName = row[0]?.trim()
    String alias = row[1]?.trim()
    String uda = row[2]?.trim()
    
    // Validate input
    if (!memberName || !alias || !uda) {
        errors.add("Row " + (idx + 1) + ": Missing member name, alias, or UDA")
    } else {
        Member member = entityDim.getMember(memberName)
        if (!member) {
            errors.add("Row " + (idx + 1) + ": Member " + memberName + " not found")
        } else {
            Map memberMap = member.toMap()
            String currentAlias = memberMap.get("Alias: Default") ?: ""
            String currentUda = memberMap.get("UDA") ?: ""
            List udaList = new ArrayList()
            if (currentUda) {
                udaList.addAll(currentUda.split(","))
            }
            
            boolean aliasChanged = currentAlias != alias
            boolean udaChanged = !udaList.contains(uda)
            
            if (!aliasChanged && !udaChanged) {
                updates.add("Row " + (idx + 1) + ": " + memberName + " already has alias " + alias + " and UDA " + uda + " - skipped")
            } else {
                if (aliasChanged) {
                    memberMap.put("Alias: Default", alias)
                }
                if (udaChanged) {
                    udaList.add(uda)
                    memberMap.put("UDA", udaList.join(","))
                }
                entityDim.saveMember(memberMap as Map)
                updates.add("Row " + (idx + 1) + ": Updated " + memberName + " - Alias: " + alias + ", UDA: " + uda)
            }
        }
    }
}

// Output results
if (errors.isEmpty()) {
    println "Metadata updates completed successfully:"
    updates.each { println it }
} else {
    println "Errors detected, updates aborted:"
    errors.each { println it }
    if (!updates.isEmpty()) {
        println "Successful updates (not applied due to errors):"
        updates.each { println it }
    }
}

CSV Format: MemberName,Alias,UDA (e.g., "East,East Region,HighPriority").

Logic: Updates both "Alias: Default" and "UDA", appending UDAs if new, and skips unchanged properties.

Tip: Validate UDAs exist in the app or handle creation separately.

Use Case: Comprehensive metadata refresh from a single file.

Example 4: Add or Update Entities with Properties from CSV

Add or Update Entities with Properties

Add new Entity members or update existing ones with alias and UDA in "Plan1" using "NewEntities.csv".

// Get cube and dimension
// Get cube and dimension
Cube cube = operation.application.getCube("Plan1")
Dimension entityDim = operation.application.getDimension("Entity", cube)

// CSV file details
String csvFileName = "EntityUpdate.csv" // Format: MemberName,Parent,Alias,UDA
List errors = new ArrayList()
List updates = new ArrayList()

// Parse CSV with csvIterator
Iterator csvIter = csvIterator(csvFileName)
csvIter.eachWithIndex { String[] row, int idx ->
    if (idx == 0) return // Skip header
    String memberName = row[0]?.trim()
    String parent = row[1]?.trim()
    String alias = row[2]?.trim()
    String uda = row[3]?.trim()
    
    // Validate input
    if (!memberName || !parent || !alias || !uda) {
        errors.add("Row " + (idx + 1) + ": Missing required field (MemberName, Parent, Alias, UDA)")
        return
    }
    
    // Check parent existence
    Member parentMember = resourceDim.getMember(parent)
    if (!parentMember) {
        errors.add("Row " + (idx + 1) + ": Parent " + parent + " does not exist or is inaccessible in " + entityDim.getName())
        return
    }
    
    // Build member properties and save (create or update)
    Map memberMap = [
        "Member": memberName,
        "Parent": parent,
        "Alias: Default": alias,
        "UDA": uda,
        "Data Storage": "Never Share",
    ] as Map
    
    try {
        resourceDim.saveMember(memberMap)
        updates.add("Row " + (idx + 1) + ": Saved " + memberName + " under " + parent + " with Alias: " + alias + ", UDA: " + uda)
    } catch (Exception e) {
        errors.add("Row " + (idx + 1) + ": Failed to save " + memberName + " - " + e.getMessage())
    }
}

// Output results
if (errors.isEmpty()) {
    println "Metadata updates completed successfully:"
    updates.each { println it }
} else {
    println "Errors detected, updates aborted:"
    errors.each { println it }
    if (!updates.isEmpty()) {
        println "Successful updates (not applied due to errors):"
        updates.each { println it }
    }
}

CSV Format: MemberName,Parent,Alias,UDA (e.g., "NewEast,TotalEntity,New East Region,HighPriority").

Logic: Validates parent exists, adds new member if it doesn’t exist, or updates alias and UDA if it does, with error handling.

Tip: Ensure your user has write access to the Entity dimension.

Use Case: Expand or refine Entity hierarchy with alias and UDA metadata.

Key Considerations

- Permissions: Ensure Groovy rule has metadata update rights.
- CSV Location: Upload to inbox (e.g., via UI or REST API).
- Refresh: Add a cube refresh post-update if needed (e.g., via a rule or job).

Tips and Tricks

Validate Members First

Check member existence to avoid errors.

if (!dimension.getMember("Test")) {
    println "Member Test not found"
    return
}

Log Before and After

Track changes for auditing.

Member member = dimension.getMember("East")
Map map = member.toMap()
println "Before Alias: " + map.get("Alias: Default")
map.put("Alias: Default", "New Alias")
dimension.saveMember(map as Map)
println "After Alias: " + map.get("Alias: Default")

Disclaimer

This blog reflects my personal insights, written independently of my employer or Oracle. EPM Cloud features evolve, so verify with Oracle’s official documentation.

WeRun Consulting