Skip to main content

Posts

Showing posts with the label Essbase

Moving essbase outline from On-Prem to Cloud (OAC) works

I am part of the EPM competency at my organization and I often get pulled in to few of the discussions. There was one instance this week where I was pulled in to a meeting for a simple yet typical problem Problem statement Client had an Essbase ASO application that was migrated to Oracle Analytics Cloud and was not used for a long time. Recently, they have loaded the data and started validating. There is a currency conversion process which is not producing any results. They wanted to check what got changed / what was missed. Unfortunately, they only had the OTL and the data files and they do not have any on-prem instance where they could easily open the OTL in EAS. We had a lab instance where we opened the outline and figured out that the Entity dimension is missing the UDA's. Now, we wanted an easy way to get the UDA's so that we can make the change very quick There was no way that you could view an OTL file and reached out asking how can we extract the dimension from ...

Essbase Cloud - REST API

REST API is not something new in Oracle EPM world when it was nunvieled and what runs behind most of the Oracle EPM product portfolio and that is the direction what Oracle says and probably you should hear it and start looking in to it When OAC was released in March 2017 and when I took a look at it three months later, I always wondered if there were REST API's that powers EssCli and I was right. Oracle at Sunday Symposium did mention that and showed a demo of how the REST API's can be used for dynamic data loading and I was thrilled. I was even thrilled when my good friend Vijay Kurian gave a session on Essbase REST API. You can download his presentation once it is available for everyone from ODTUG . Being a presenter and attendee, we get early access to the presentations and I feel so much previleged about it If you want to get started right away and want to go and look at the documentation, it's not available yet. This is where it becomes difficult to know what al...

OAC - Switch Classic to Modern - Trick

Back to back posts today. But, I am excited and wanted to share this cool hack....Not sure if anyone has figured it out.... I did a blogpost on OAC new / modern interface and few features that I really liked. You can find my blogpost here . A good friend (became friends at Kscope18) Vijay Kurian  posted in his blog at theunlockedcube  about the Essbase Cloud features Part I and Part II and he has shown how to switch from classic interface to modern interface For some reason it didn't work for me in the pormotional OAC instance that I was using. However, I was able to switch based on what I poseted in my previous blogpost In general If you want to switch from Classic to Modern, follow the below screenshot However, for some reason that didn't work for me and I didn't knew why What I did might sound pretty silly but now I have the whole new Modern Interface that i can use without having to rely on the Deep Dive environment that I was using earlier. (That's Ora...

OAC - New Interface

At ODTUG Kscope18 , Oracle has revealed new features and groundbreaking updates that are going to come in the next release in OAC. Have you ever wondered what it's going to be I was so excited that I kicked-off the promotional offer and fired up my OAC instance. But, I was surprised and felt sad after looking at the instance that is in public cloud and the instance that is going to come in the next release. Not sure if I am missing anything here which will enable me to access the newer interface / newer instance The new interface looks sleek and looks a lot better than the previous interface. I am using the instance as parts of Hands-On lab at ODTUG Kscope18. Not sure till when this environment will be available. I will update this blog and make it more comparitive with what is available right now in public cloud New JET UI. I don't know if that is the right way to call it but that's what it says when you fire up the OAC instance new interface Less clutter and wa...

Kscope18 - Here I Come!!!

I got to know about ODTUG from 2009 and have been following it since then. Every year Kscope happens, I assure myself that someday I will present but T didn't had the confidence in myself to dare and submit an abstract. What If it won't get selected I have decided to myself that this year, I will overcome that fear in myself and work on four things that will enhance and improve my career Submit an abstract to Kscope18. I have submitted two abstracts and both of them got selected and I thank ODTUG selection committee from the bottom of my heart to give an opportunity to be present. I will try my best to do justice for the opportunity that you have given to me Continue blogging. I took a break for two years when my wife became pregnant in 2015 to take care of her and followed by taking care of my wife and daughter. The first year was really tough handling the kid by ourselves and decided to dedicate completely to my family Contribute enough to become capable of Oracle Ace...

Quick Tips on data recast and data validation

I was working on a data recast process which involves applying some complex mappings to the source data before it can be loaded to the target cube. This is quite a common exercise in applications related to Essbase, Planning and HFM. I used a database to hold all those mappings along with the source data. I would recommend this approach as you have full control on what mapping you are applying and you can keep track of what mapping changes you are doing since the data recast process is a repeatable process After the mappings have been applied, I had to do some manual addition of few accounts subtract data from few accounts. I am not an expert in SQL so excel pivot table to the rescue. All good and the data is ready to be loaded to the cube Tip #1: Standardization is the key Always use a standard load rule and a standard data load format for the data file (sort the columns from least to most and the dense dimension at the last and for columns). Also, use a standard delimiter...

Planning (BSO) to Reporting(ASO) Replicated Partition Issue (Not really an issue) - Solution

There was an issue reported from user that a small sheet with 15 rows and 6 columns from reporting cube (ASO) is taking around 6 minutes to retrieve and getting timed-out We tried from our end couple of times and identified that it was sporadic and tried all possible options to fix it. Sometimes, it retrieves very quickly and sometimes it takes a huge amount of time. Option 1: Outline size was around 200MB and we compacted the outline which brought down the outline size to 22 MB. But, this didn't work Option 2: We cleared aggregations (We were very sure that this will not improve performance). It didn't work Option 3: We have a database where we capture all our metadata and data loads with start time, end time and elapsed time. We noted down the times when the retrievals were faster and looked in to the database to find what process were run before the retrievals. We found that whenever a dimension build process is completed, the retrievals are faster but after that ...

ASO Report Optimization - Short Tip

Another short post on ASO Report Script Learning I was working on a project where we have an ASO cube and we had to extract the data from ASO cube to a different team. There were multiple requirements, where they wanted level0 of few dimensions and summary level of other dimensions I used an existing report script that was in a BSO cube and then updated the dimensions accordingly and to my surprise, the script ran for around 2 hours to generate around 20MB file I have written report scripts earlier and never did the script ran so long. It took a while for me and figured out that I was using LEV and when replaced with LEAVES, the report script ran for 40 seconds I expanded the scope of my report script and was able to generate 400MB file in 4 minutes Always use LEAVES if you need Level0 members of a dimension in ASO cube Happy Learning!!!

Copy Text data (Planning) as Copy Data (DATACOPY) in Essbase...Yes..It works!!!

Yes. You have read it right and I am talking right. There is a saying that if you understand a process end-to-end you open lot of possibilities for improvement / betterment and it suits perfect in this case As the title goes, we all have started our career in Essbase (atleast in my case) and moved to to different other tools. We might have received multiple requests / written different types of calculation scripts where we have to copy a subset of data from one combination to other combination. The real problem comes when a user who is well-versed with Essbase and even knows the technical stuff better comes with a requirement which blows your mind and you had to think / try every single possibility to convince him that this is not possible. The same thing happened with me Background We use planning application for Planning sessions, Yearly Budget and monthly forecast and initially the monthly forecasting in Excel (where they have numbers as well as text) and after moving to Ora...

Essbase Data load & Dimension Build Error log limit

If you are working on a project or in support, there are changes when you do not have the metadata in the system and you try to load data and you get lot of data rejects.       You then try to rebuild those members and then try to load the error file again and you get some more rejects. You might be wondering why not all the errors gets updated to the log file? Is there any limit to the number of errors that are written to the file? Yes. There is a limit both for DataLoad & DimBuild which can be defined in essbase.cfg file and the limit is different for "Dimension Build" and "Data Load" If you have checked the Technical Reference, you will see three configuration settings Taken from Dbag NOMSGLOGGINGONDATAERRORLIMIT Prevents data load or dimension build errors from being written to the application log after the limit described by the value of DATAERRORLIMIT is reached. Syntax NOMSGLOGGINGONDATAERRORLIMIT TRUE | FALSE The default value is FALSE...

Custom Tools/Utilities for Oracle EPM (Mostly Essbase) - Part I

     It's been around 8 years that I have started working on Essbase and from then there has always been a need to a functionality / custom tools which are not available with Essbase and have to either build one. In current world, there are lot of consultants who have built their own custom tools and few of them have made them available to public. When Oracle has made Oracle EPM set of tools available on  eDelivery  site, it has opened up the opportunities to lot of people who has started working on Essbase. This post is about for all those who might have thought that " I wish I had a tool that can accomplish this ". This post is about a collection of such tools that are available open to the public.  I would divide this in to multiple posts. In Part I, We will talk about Log Utilities Log Utilities Essbase Log Reconciler Logs are always the entry point to check for any issues / calculation but Essbase logs has always been difficult to...

Where is @ISIDESC (Boolean Function) in ASO for MDX Formulas?

It has come out of surprise that ASO doesn't have a Boolean function similar to @ISIDESC function in BSO for writing MDX formulas. But, how can I achieve it? There are two different ways of doing it. Using ISANCESTOR Function Using CONTAINS function with a combination of Descendants function [1] Using ISANCESTOR Function [ IsAncestor (Member1, Member2) ] As the name says, this function will check if Member1 is ancestor of Member2 . Let's take a simple example IsAncestor ([Market].CurrentMember, [Florida]) -    This will check if the CurrentMember in the retrieve is Ancestor for [Florida] . Now, If we switch the member1, member2 we will be able to achieve the @ISDESC functionality. IsAncestor ([Florida], [Market].CurrentMember) - This will check if [Florida] is ancestor of your CurrentMember and this condition will satisfy for all the members that fall under [Florida] . Using CONTAINS function with a combination of Descendants Function [ CONTAINS (Mem...

Variance Reporting - How to do it - Part III

In the Last series of Posts Here (Part I) and Here (Part II), we have talked about the basics of Variance Reporting (Part I) and Variance Reporting Across Time Periods (Part II).  In this post, we will talk about variance reporting Across Scenarios (Actual, Plan, Forecast). Most of the Organizations who have been using Oracle EPM set of tools as part of their financial process would have the below process setup Fetch the Actuals (transaction level data) from different source systems for their reporting & analysis. Most of the actual data comes from oracle GL, Oracle EBS, PeopleSoft or any type of an OLTP system or it could even come from flatfiles or excel or could be any readable format Have a Planning application where users can input their PLAN data. Most of the organizations have long-term goals and target and plan to achieve it. What are the steps that are involved to achieve their goals and targets is part of their planning. I am not going to talk in de...