Block Creation in ASO!!!

It's been a while I planned to write about this. Lot of things happened after that(personal and professional) and I couldn't finish writing it.

Now you must be thinking why is he talking blocks in ASO, there are no blocks in ASO, we all know that.

I just coined that term because everyone in BSO world understands it :) Now that ASO has procedural calculations it is high time someone come up with a term ;)

If you don't like Block creation, then how about efficient procedural calculation in ASO :)

Now if you are wondering what a procedural calc is (where to use one), I can try to explain it in a BSO fashion.

Think about a dynamic formula in BSO, it gets executed irrespective of which level you are (level 0 or higher). Now if I've to make the formula work for all level 0 only - for higher members just do a roll up, I'll have to write a calc script to achieve it (or just load the calculated number)

Now in a ASO till lately the only option was to load the calculated number, with the release of 11.1.2.x you've two options.
  1. Load the calculated values
  2. Run a procedural calc on ASO cube
I was working on a requirement which calculates the residual forecast. Simple formula.

Take the sum of forecast starting from first month in the cube to the current period. I have a calc member (Residual_Forecast_Calc) which has a formula and a store member (Residual Forecast) to store the procedural results.

Everything looks good till I ran the calc. The calc just keet running.

Yes I did wait for it to be done for more than 15 hours. Oh not on the production box.

This is on my test machine and it didn't finish (this is on and a modified version of ASOSamp)

Now what, Oh you can use NONEMPTYMEMBER to restrict it, is that your answer. Ok Let's try it!!!!

Formula updated, now look at the results.

$%$^$%# nothing, yes and why would it work? I don't have a forecast in Feb (which is the current month, so it becomes actual)

Wait, what if I can trick it? That's where the block creation or efficient use of NONEMPTY/....... comes into picture. I know that there is forecast there for some months. What if I load those forecast figures against current month. (Don't load those real numbers)

I created a SQL load rule

If you look at the SQL, I'm grabbing all the distinct member combination which has forecast till current month. (Note the absence of Time)

Now look at the Data column, it is all zeros.

Let's load this to the database.

So what are we doing here.

We are loading zeros against current month Forecast (ZfcstLd) then comes the real actual and real forecast data.

I'm using Overwrite existing data, so if I've real forecast data coming (sqlld2), it'll overwrite the zeros the we loaded early. Also I didn't check "Ignore zero values".

Now if you are using a MaxL statement you can create a different buffer for these.
  1. buffer which allows zero load for ZfcstLd
  2. buffer which ignores zero and missing for sqlld1 and sqlld2
Let's look at the results after the load.

Zeros are in and the calc is working. Let's run our allocation and see how much time it is taking.

Yes .375 seconds (no I won't even do the performance math against 15 hours and still running)

Now, if you want to remove the zeros that you loaded to perform the calculation, you can run a merge statement to remove the zero cells.

Results after calc and merge


Update 02/19/2015

I got a question as comment from amarnath asking what if the file is just a flat file. In my case I'm using a relational database and it is easy to find the distinct values.

You can use OS scripting tools to find out the distinct list. In my example I had to figure out the distinct Forecast values.

Here is the command that I used. As usual I like using the *nix variants (you can find them from the UnxUtils site)

What is it doing?

I'm telling gawk that my delimiter is tab (-F) and I'm asking it to use the same delimiter for the output (-v OFS)

It searches for keyword "Forecast" and print all columns except 2nd and last (Date and Data fields) (NF-1, will remove the data column)

printf $1; for (i=3; i <= NF-1; i++) printf FS$i; print NL,0 (NL,0 adds a zero at the end) released and first impressions

Today got released!!!!!

I was eagerly waiting for this release. However I don't think I'm as excited after installing it.

Couple of changes, the world's largest service name is no longer there. Installer won't prompt for your support id anymore.

Workspace gets new look and feel (more Fusion feel)

New feature in Shared Services, where you can run provisioning report for a user by right clicking the user.

You can run diagnostics information in Planning

There are multiple artifacts available for monitoring. I ran diagnostics for Forms and found that it is is giving a status report.

You can search for specific forms.

What is not providing is how the form got a "Good" rating.

However "Grid Diagnostics" provides bit more information for forms.

This one has information on Load time, How many Unsuppressed Columns/Rows are there in the form, How many pages and What was the load time. That is a good information :)

I did see Custom Styles.

I did see Smart View saying there is a new version ( out there. However couldn't Smart View page still shows

I couldn't figure out where SandBox is so I decided to use the help file and seems like either help file is not updated or it is not there :(

No sandboxing (or I'm sleepy, maybe it is there and I cannot find it)

I didn't see Valid Combinations, I didn't see saving EXCEL formulas back to Planning.

Oh my god, I'm sleepy.

Will update you all later :)

Update 02/06/2015

I had to perform an uninstall and noticed that the uninstaller takes a bottom up approach. Foundation Services is the last one to be uninstalled. I've not uninstalled prior versions (11.1.2.x) and not sure when this change was introduced.

NUMSys 3.0 - Native User Management System for Shared Services

The latest greatest NUMSys is here.

I'm really excited about this version, it took me a while to think about all the reports and of course to implement it.

This is the release which saw a bunch of updates.

You got new menus now :)

It tries to mimic most of the EXCEL functionality, with Copy and Paste you can now get data in and out of EXCEL (a big thanks to Mike Nader for this suggestion)

All the tables now allows you to perform multiple selections.

As you can see there is a Reports tab in NUMSys.

You can create a bunch of reports for documentation purpose.

  • User/Group Provisioning Report
  • Application Ownership Report
  • User/Group Comparison Report
  • User/Group Inheritance Report
  • User/Group Report

Provisioning Report

Provisioning report can generate an inheritance report along with user/group role.

It can plot upto 5 levels.

Application Ownership Report

Using this you can see which user is the owner of the application. Extremely useful in case of Essbase and Planning applications.

Comparison Report

Using this you can compare the users in app1 vs app2

This will generate two reports (app1 vs app2 and app2 vs app1) with the users in the application and the difference.

Inheritance Report

This is similar to provisioning report, this will plot the user/group's ancestral tree to 5 levels.

User/Group Report

This is the old documentation report which can report on all users/groups in all directories.

You can look at the help file for more details and I almost forgot the download link :)

You can download NUMSys 3.0 from here.

NUMSys 2.1 - Native User Management System for Shared Services

Happy New Year

I think I should share a New Year message that I received.



"Success" = "hardwork"+"determinations"+"lil luck";


I fell in love with that New Year Calc script :) Even my New Year wishes are calc scripts ;)

This post will introduce you to the Fourth Version of NUMSys (You can get the older versions from the "NUMSys" menu, why would you ;)) :)

In this version you can now specify which directory to search for User(s)/Group(s).

I'm working on a provisioning documentation module, which will provide a report on the provisioning inheritance. (Almost there, so that will be added to the next release)

A big shout of thanks to Cameron, for providing the idea. 

Well this is going to be different format though :)

You can download NUMSys 2.1 from here.

I keep hearing about the pain in producing a sensible SOX report. If someone out there is interested in providing the samples they produce for user access. I can try to add that in to the documentation module.

How to sort Planning Webform folders

Merry Christmas and to those who are not celebrating, Happy Holidays

It is hard to type without two fingers :( So I'll make this short. Oh, I cut two of my fingers within two weeks time frame, so it is an eventful vacation.

I've often heard this question about sorting webform folders. I'm not sure why this feature has not made into Planning yet.

There are different ways to achieve sorting of Webforms (1st the supported way and the next - the unsupported way).

Supported/Oracle recommended way

1. Export existing forms.
2. Create the folders in the order you need
3. Import the forms back

There were times where I couldn't make this work. I guess it was because I was not doing it right :(

Just for kicks :) I was looking for an option to see whether this can be achieved in an easier way.

What I'm going to describe below is an unsupported way, continue at your own risk ;)

1st step is to find out the object_id and the position of the folders

1. Object_id and position of the folders
You can use the above given SQL for that purpose.

Let's say the client want to make Forecast as last folder.

You can go the supported route and import the forms back, or you can update the position of Forecast.

Commit the changes and restart the services. Ta da

I'll be seeing you next year, so Happy New Year in advance.
Hope to see the Rise of Hybrid next year. (can't wait long)

Views discussed in this blog are my personal views.
They do not represent the view of organizations, businesses or institutions that I'm part of.