How to Use Drillthrough in Jedox with PostgreSQL

Drillthrough is a very useful feature in Jedox that helps users get more detailed data directly from their cubes. For example, when analyzing aggregated data, you can right-click on a cell in your report and see the detailed information behind it. This can save a lot of time when you need deeper insights without creating complex new reports.

In this article, I will explain:

1. Why drillthroughs are used.
2. What options do you have for setting up drillthrough.
3. A step-by-step guide on how to use PostgreSQL as a drillthrough source in Jedox.
4. A referral link to get started with a free PostgreSQL database on Aiven.

Why Use Drillthrough?

Drillthrough is commonly used in analytics to:

• Quickly check the source data behind aggregated values.
• Validate the data loaded into the cube.
• Provide more transparency to report users who want to see raw data.

For example, if you have sales data aggregated by country, a drillthrough could show the transactions from individual stores or sales representatives in that country.

Options for Drillthrough in Jedox

In Jedox, you can set up drill through in two main ways:

1. None: No drillthrough is enabled. Users won’t be able to see the underlying detailed data when clicking on aggregated values in the cube.
2. Direct Drillthrough: Data is fetched dynamically from the source database (e.g., PostgreSQL) every time a drillthrough is executed. It does not require pre-storing detailed data in the cube. Recommended when the source data changes frequently or is too large to load entirely into Jedox.
3. Persisted Drillthrough: Detailed data is preloaded and stored in the cube during the data load process. Provides faster drillthrough results because it avoids querying the database at runtime. Best for scenarios where data does not change frequently or where performance is critical.

In this guide, we focus on the second method using PostgreSQL hosted for free on Aiven.

How to Set Up Drillthrough with PostgreSQL

Follow these simple steps to create a drillthrough in Jedox:

Step 1: Create a PostgreSQL Database on Aiven

1. Go to Aiven’s website and sign up for a free account using this referral link.
2. Create a PostgreSQL database instance.
3. Load your sample data into the database. For this example, I used a table called stuff_location with the following columns:
   • ID: A unique identifier.
   • Country: The country where the staff is located.

Step 2: Create a PostgreSQL Connection in Jedox

1. In Jedox, go to the Database tab and create a new connection.
2. Choose PostgreSQL as the connection type.
3. Enter the connection details from your Aiven database (host, port, database name, username, password).

Step 3: Extract Data from PostgreSQL

1. Use Jedox Integrator to extract data from the stuff_list table.
2. Check the connection and make sure the data is loaded correctly.

Step 4: Create a Cube in Jedox

1. Create a new cube in Jedox with the following dimensions (note that we don’t load all the information but just consolidated/important for us):
   • ID: The unique identifier.
   • Country: The location of staff.
2. Load the extracted data into the cube with drillthrough mode Direct.

Step 5: Enable Direct Drillthrough

1. In Jedox Integrator, set up a Drillthrough extract.
2. Map the cube dimensions (ID and Country) to the corresponding columns in your PostgreSQL table. (important to have same column names)
3. Test the drill through to ensure it’s retrieving the data correctly.

Step 6: Test Drillthrough in a Report

1. Create a simple report in Jedox using the cube.
2. Right-click on a cell in the report and select Drillthrough.
3. You should now see detailed data from your PostgreSQL table.

Conclusion

Using a PostgreSQL database is an excellent way to set up a flexible and powerful drillthrough in Jedox. With just a few steps, you can integrate detailed external data with your Jedox cubes and provide more insights to your users.

If you want to get started quickly, use this Aiven referral link to create your free PostgreSQL database.

Stay tuned for my upcoming video tutorial and sample files to help you follow along with this guide. Let me know in the comments if you have any questions or feedback!

 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.