Skip to main content

PBCS/EPBCS - ASO exclude shared & Dynamic

As you all are aware that Oracle releases patches to EPM cloud every month (EDMCS is released every 2 months) and the patches are applied on first-week of Friday in Dev and third-week of Friday in Prod

I did a post long back about a challenge that I have faced in on-premise and how I have addressed that.

New functions were released in Nov-2018 release of PBCS. Below is an excerpt from the readiness document. You can find the document here

New Aggregate Storage Functions in Calculation Manager
The following Aggregate Storage functions have been added to Calculation Manager. These functions filter the shared and dynamic members from the Point of View (POV). The functions can be used in the POV of an Aggregate Storage custom calculation or allocation.

  • @FilterDynamic(Dimension Name, Member Name) removes all dynamic members from the list of members
  • @FilterShared(Dimension Name, Member Name) removes all shared members from the list of members
  • @FilterSharedAndDynamic(Dimension Name, Member Name) removes all dynamic and shared members from the list of members
These are very helpful functions. Previously, you don't have a way of excluding shared members / dynamic members. Remember, that these have only been added for running custom calculations on ASO. I wish these functions are available as part of data maps as well and works across the board for both BSO and ASO

Previously, you would have to address the same by using UDA's and using those to exclude the members / include the members that can be used in performing custom calculations
in on-premise world, you have a workaround where you can use a mix of filter and member properties to address this and I have used this very heavily in the ASO applications that I have built so far

Filter(Filter(Descendants([NetIncome], [Account].Levels(0)),NOT [Account].CurrentMember.Shared_Flag),[Account].CurrentMember.MEMBER_TYPE <> 2)

The above logic will exclude the shared members and dynamic calc members. This can be used in Clear, procedural calcs and MDX queries

However, in the cloud world, you do not have access to run the MDX queries / MaxL scripts like the way you can do in on-prem. If you have to run procedural calcs on ASO, you have to use calculation manager and is limited to what you can actually do using MaxL and MDX scripts

Oracle have heard and have come with these functions to help you out with easy peasy ways of addressing the never-ending challenges you face in the ASO world. This is just the beginning. There are lot many things that you can do with procedural calcs and MDX compared to what you can do with with the calculation manager on ASO. I wish they add the functionality to execute MDX queries and the ability to store the result in the inbox/outbox folder

I haven't personally tried them if you have do let me know and will link your blog here.

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

EPM Cloud Tips & Tricks - #1

The first EPM cloud product was released in 2014 and it's been six years till date. I was recently part of an FCCS implementation project. I know what you might be thinking. Coming from completely essbase and planning background and been working on it for almost 12 years and doing an FCCS project? Well, it turned out that way and it was a change for me too than being in my comfort zone and took it as a challenge.  I have been very busy for over the past one year and I didn't really had a chance or time to get back to my blogging and sharing my knowledge. The project finally went live and I am going to share my bit of learnings. Some of it you might alread know The first tip is going to be an easy one and those who have worked in FDMEE / Data Management in the cloud, you might already know it. But, this is very important when it comes to FCCS as zeroes are valid from Balance Sheet standpoint Data Management by default doesn't load zeroes. Below is a excerpt from the document...
1 comment
Read more

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...
Post a Comment
Read more