Create elements in Jedox ETL with Groovy

In multidimensional databases like Jedox, managing dimensions and hierarchies effectively is crucial for accurate data analysis and reporting. Consolidations, which define parent-child relationships within dimensions, are an important part of this process. In this post, we’ll explore how to automate the creation of base and consolidated elements using Groovy scripting in Jedox ETL. We’ll also dive into a practical business use case to showcase the power of this approach.

---

The Problem: Automating Dimension Hierarchies

Managing dimension hierarchies manually can be time-consuming and prone to errors, especially when dealing with dynamic or frequently changing datasets. Using Groovy in Jedox ETL, we can automate the creation and management of elements and their consolidations. This makes the process faster, more reliable, and easier to maintain.

Below is a Groovy script that demonstrates how to:

1. Connect to a database and retrieve a dimension.
2. Create base elements and a parent element.
3. Add consolidations between the parent and its child elements. Java documentation here.

---

The Script: Building Base and Consolidations in Jedox

Here’s the code:

def db = OLAP.getDatabase("OLAP_DATABASE");
def dimension = db.getDimensionByName("OLAP_DIMENSION");
//add parent element
dimension.addBaseElement("ParentElementName", IElement.ElementType.ELEMENT_STRING);
def parentElement = dimension.getElementByName("ParentElementName");
//add base elements
def name = "childElement3";
dimension.addBaseElement(name, IElement.ElementType.ELEMENT_STRING);
dimension.addBaseElement("childElement", IElement.ElementType.ELEMENT_STRING);
dimension.addBaseElement("childElement2", IElement.ElementType.ELEMENT_STRING);

// Retrieve existing elements
def childElement = dimension.getElementByName("childElement");
def childElement2 = dimension.getElementByName("childElement2");
def childElement3 = dimension.getElementByName(name);

def weight = 1;

// Create consolidations
def con1 = dimension.newConsolidation(parentElement, childElement, weight);
def con2 = dimension.newConsolidation(parentElement, childElement2, weight);
def con3 = dimension.newConsolidation(parentElement, childElement3, weight);

// Add all consolidations at once
def cons = [con1, con2, con3] as IConsolidation[];
dimension.addConsolidations(cons);

---

Code Walkthrough

1. Connecting to the Database:
   • OLAP.getDatabase("OLAP_DATABASE") retrieves the database by its name.
   • The getDimensionByName("OLAP_DIMENSION") method accesses the specific dimension where elements and consolidations will be added.
2. Creating Elements:
   • addBaseElement adds a new base element (in this case, a string type) to the dimension.
   • We’ve used the name “childElement” for the new element in this example.
3. Retrieving Elements:
   • getElementByName retrieves existing elements by their names, such as “childElement2” and “childElement3.”
4. Creating Consolidations:
   • newConsolidation establishes a parent-child relationship between elements with a specified weight.
   • For example, dimension.newConsolidation(parentElement, childElement, 1) creates a consolidation where parentElement is the parent of childElement with a weight of 1.
5. Adding Consolidations in Bulk:
   • By passing an array of consolidations to addConsolidations, we can efficiently add multiple relationships to the dimension at once.

---

---

Why Automate Consolidations?

Automating consolidations in Jedox offers several benefits:

• Efficiency: Quickly build or update large hierarchies without manual intervention.
• Accuracy: Minimize human errors in setting up relationships.
• Scalability: Easily adapt to changes, such as adding new products or categories.

---

Conclusion

Groovy scripting in Jedox ETL provides a powerful way to manage dimension hierarchies programmatically. Whether you’re setting up basic consolidations or building complex hierarchies, automation can save time and reduce errors. Try the script above with your data and let us know how it worked for you!

 If you think this helped you in your projects and saved you some time don’t hesitate to support our blog at https://buymeacoffee.com/stefanvelickovic33. It helps us to run and host the website.