Something More....

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...

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...

“Visualization gives you answers to questions you didn’t know you had.” – Ben Schneiderman . Undoubtedly, visualization is playing a vital role in today’s corporate world. The data is no longer confined to tables or worksheets, be it a CXO or analyst, its far easy to consume information and report via pretty visuals knit together into colourful dashboards. This has lead to an upward trend of visualization tools like Oracle Analytics Cloud (OACS), Microsoft Power BI, Qlik Sense, Tableau, etc. With each product bringing its own benefits to the table, it is important for the firms to invest in the best and harness each product’s capability. PBCS might be a leading planning and budgeting SaaS application but doesn’t offer all dashboarding capabilities or the varied charts available with Power BI. Similarly, Power BI or OACS are best for dashboards not for planning and budgeting. Not all firms invest in OACS with PBCS and tend to have a heterogeneous architecture of BI products. Thu...

PBCS or better known as Planning with the recent rechristening by Oracle, gives us an ability to export data into a flat file using our very own DATAEXPORT command in business rules. Lets see how this command have some interesting feature in cloud version vs the on-premise one. In the on-premise version (the one I am using is Planning 11.1.2.4.004.5 and Calc Manager 11.1.2.4.013.005) if we tried to generate a file name using any RTP, it would not allow us to validate the rule. Let’s say, we ignore the validation error and still deploy the rule, the rule executes but generates the file with the name “VisionData_{Year}.txt”, leaving no option to generate a file which could have dynamic names. Though this doesn’t seems to be showstopper in anyway as the data would have the RTP member inside the file, but considering we like to append date in YYYYMMDD format to the name, it would be impossible. Come to the world of cloud and Oracle has changed the way Calc Manager processes the RTP in the...

I thought of sharing a learning from a mistake I recently made when using CalcMgrDateToString function and hope you do not make the same. The CalcMgrDateToString accepts the date format which we like to return, say yyyy for years and MMM in Jan, Feb etc. The options for the date format can be picked up from Java’s SimpleDateFormat class and this is where things get ignored sometimes. The important thing to note here is the usage of yyyy vs YYYY as the parameter to the function. The function accepts both YYYY (all uppercase) as well yyyy (all lowercase) and both of these parameters return a different value. We might not see any difference in the returned value unless we use it with Dec 31st of any year. Let’s give it a try and see what happens. In the fist snapshot I am using simple code to return the year date part for 31st Dec 2019. Since Essbase stores the date values as YYYYMMDD, i have directly used the value 20191231 as first parameter and passed the second parameter as yyyy (all...

Creating members on the fly and Planning functions are no longer a new gig to boast about. In case you still need some hint, please click on above hyperlinks to navigate to Oracle guide to throw some light on them. How about we try to combine the two and come up with a unique requirement where we create a member of the fly and then use it with planning function ? Today I would like to talk about a similar scenario which was more of an attempt to use the planning function to get the start period of a scenario member and drive calculations by scenario start period rather than using substitution variables. This would enable same calculations being executed against multiple scenarios which may have different start periods. We can either have pre-created scenarios or give an option to create one on the fly. Let’s go with second option and be able to run some calculations on a newly created member via form. I created a scenario and enabled it for dynamic children. Cre...