Block Creation in ASO!!!

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

HTH

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)

E:Toolsunixbingawk -Ft -v OFS=t "/Forecast/  {printf $1; for (i=3; i <= NF-1; i++) printf FS$i; print NL,0 }" actualdata.txt |E:Toolsunixbinsort -u >forecast.txt

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)