Posts

EPM Integration Agent: Configuration tips

By -
EPM Integration Agent was introduced sometime last year providing ability to connect to in-premise data sources. Though the tool has ability to connect to third party REST APIs via Groovy or Jython scripting but I assume the majority of the use cases would be to connect to relational databases, usually warehouses which had been used to feed data to various reporting and planning applications in the in-premise world. Unless clients move to any cloud based ERP, the data warehouses  are going to stay for a while and the need to pull data too. Oracle provides connectivity for Oracle and MSSQL server by default, but what about other data sources? Other than scripting, if any data source offers SQL capability, we can essentially pull data. I had a recent experience connecting to Netezza database via Integration Agent and thought of sharing some quick tips I learnt during this exercise. Integration Agent Port Agent requires a port to communicate and by default it is 9090. Many organisations,
1 comment
Read more

Groovy Series 7: Submit Groovy generated rules to Essbase

By -
Image
Ever since Groovy functionality was introduced with Planning cloud, it has evolved over a period of time, offering new capabilities and methods to achieve what was not possible with traditional Calc script rules. Sometime, I have been asked “Is Groovy a replacement for Essbase Calc scripts” and my answer is a big ‘NO’. Groovy should not be perceived as a replacement to Calc script, rather consider it a booster which enhances user experience and provides flexibility to dynamically generate Calc scripts and improves performance. Calc scripts are an integral part of how we run calculations on Essbase, whether we generate the code dynamically using Groovy or stick to traditional scripting. Lets look at different ways to submit these Calcs to Essbase and how Groovy has evolved. R eturn method This had been the oldest and default method until May 2019 when Job Factory class was introduced. Justifying its name, this method is used to submit a dynamically generated Calc script to Essbase for e
Post a Comment
Read more

Groovy Series 6: Beauty of cscParams

By -
Image
While working with Groovy scripts, a common task is to dynamically generate an Essbase calc script capturing the edited members from the grid. This script should then be submitted to Essbase for execution. But the important task is to ensure these edited members when passed on to the Calc script are written in required Essbase format, enclosed in quotes. In past we have been using various groovy methodologies to identify unique members and converting them to Essbase format concatenating with quotes, commas and spaces. How about if all of this can be done in one step my enclosing the entire collection in a method which spits out strings, enclosed in quotes and separated with commas ? Yes that’s what cscParams do for us. Let’s look at the above script example, in Step 1, I declare an empty list and push the edited accounts from the COA dimension into the list. Step 2 and 3 define two ways of converting this list into a string which I can use either dynamically generate a Calc script or p
Post a Comment
Read more

Groovy Series 5 : Interacting with external web services

By -
Image
Though this feature came in last year and I have been a bit late in writing about, I thought it would be still worth making a record of the same to help me (and others if people are reading) to make use of the same. T he update to EPBCS (though it no longer is PBCS or EPBCS, let’s just stick to it to symbolise it as availability of Groovy) , Oracle had made its Groovy features more powerful by adding an ability to interact with external web services. What this means is, we can now do HTTP GET or POST with external webservices. If you like to know more about the methods, you can check W3School . In Groovy API, we have Get(), Post(), Delete() and Put(), which do majority of the tasks. A very basic example would be the ability to update FX rates into our application in an automated way. Usually this is one of the forecast preparation tasks for finance users to ensure FX rates are updated and they might be crunching these rates manually to put in an average or current value. I will be usin
1 comment
Read more

Power BI + Oracle PBCS - Part 3: Hierarchical reports

By -
Image
After I wrote the other two posts and shared the same with few of my colleagues, a lot of question poured in asking ‘How about this..?’ or ‘Can we do this…?’, etc and one question asked by our very own Hyperion Jedi (Ahmed Hafez) left me intrigued looking for answers to ‘Can we have hierarchy driven report in Power BI when connecting to PBCS?’ This kept me busy for a few hours with an outcome, the snapshots of which look something like below. The first chart shows the top-level entity roll-up ‘All Entities’, providing an option to drill-down to BUs. The same action can be performed on BUs to lead to entities. Let’s look at the steps to achieve the required hierarchical structure. I would not talk about the steps to connect Power BI to PBCS and how to parse data, rather suggest to go through Part 1 and Part 2 of this post. In past posts, we utilised the concept of calling PBCS export data slice web service to pull data and parse it. In addition to pulling data slice, we woul
2 comments
Read more

Power BI + Oracle PBCS - Part 2: Connect and Visualise

By -
Image
This post is in continuation to Part 1 of the same topic in which I talked about how PBCS REST APIs can be used to pull data as JSON response. We used the Postman app to view our results. In this post, we will see how we can make a call to an HTTP POST request and parse the JSON response within Power BI. Note: I am not a Power BI expert and method shared herein is one such way of doing this. There might be more efficient ways to achieve the same where Power BI experts can help. Power BI offers a lot of inbuilt connectors and methods to pull data from discrete sources and they serve the purpose perfectly. But in certain cases, prebuilt connectors do not serve the purpose and that’s when “Blank Query” brings out its magic, helping to connect to anything which can be defined in Query terms. Because Power BI’s Web connector makes an HTTP GET request by default, we cannot use it to execute our data slice REST API which is POST in nature. Thus we use the Query to enable Power BI to m
7 comments
Read more