If you are 11.1.2.4 and you use ASO Plan Types, you may be aware of a bug that prevents drill-through from working properly. Essentially, I can define drill-through in BSO and everything works fine, but ASO just doesn’t work. You can see the intersection as being available for drill-through, but when you attempt to actually drill through…you get this:
Well that sucks. But wait, there is now hope! First, you need 11.1.2.4.004. Then, you need the recently released intermediate patch 23023767. Now if we look at the read-me, we see this:
That is not terribly promising. In fact, it just plainly says absolutely nothing about drill-through. It doesn’t even mention ASO at all. But hey, let’s follow the instructions and patch our system anyway. Flash forward a few minutes, or hours depending on your installation and we should be ready to try it out again:
Now that looks better! Working drill-through from an ASO Plan Type! The cool part is that we can use FDMEE…or something else. Be on the lookout for another post on using DrillBridge with Planning AND PBCS.
Brian Marshall
April 26, 2016
If you have a ASO cube where timing is everything or a cube with multiple large data sets, waiting for data loads to process in series can waste valuable time. Luckily, Essbase supports parallel data loads! Sadly…while the documentation for this isn’t bad, it is inaccurate. I’m sure it was accurate a few (or many) versions ago, but the drive names have changed as have the DLL’s. So first, let’s set up our MaxL rule to execute multiple parallel loads from a single SQL data source. Here’s the code:
import database RetASO.RetASO data
connect as hypservice identified by 'Hyp3r10n!'
using multiple rules_file 'dRev', 'dExp'
to load_buffer_block starting with buffer_id 100
on error write to "e:\\data\\RetASO\\dRev.txt";
So this MaxL code assumes that you have at least two load rule developed. I’ve built a load rule for revenue and a load rule for expense. Each of these rules load roughly 2 million rows of data into my ASO cube. So let’s execute this and see what happens:
If we read the error, it tells us exactly what the problem is. We need a data source that supports multiple connections. For this we can turn to the included Data Direct drivers. Let’s go ahead and create a new data source to use for Essbase that will support multiple connections. We start by firing up our ODBC Data Sources (64-bit) in under Administrative Tools on our Essbase Server.
Click on the System DSN tab:
Now add a new connection:
Scroll down and select the DataDirect 7.1 SQL Server Wire Protocol (on 11.1.2.4) or the DataDirect 7.0 SQL Server Native Wire Protocol (on 11.1.2.3) driver from the list.
We also need to know the driver name, so scroll over to the right and make note of the name of the DDL file. It should be ARSQLS27.DLL (on 11.1.2.4) or ARSQLS26.DLL (on 11.1.2.3).
Once you click Finish you should see a new window that will let you fill in your server and database information. Enter your information and click Test Connect.
Enter your credentials.
Assuming we’ve done everything correctly, it should establish a connection.
Now we have a connection, but it does not support multiple connections by default. This means we need to tell it to allow multiple connections. We follow the docs and we create an esssql.cfg file in the bin directory of our Essbase server:
This is where we have to change it up a little. The docs haven’t changed in a while so they reference a very old version of the driver. Try this out instead:
[
Description "DataDirect 7.1 SQL Server Wire Protocol"
DriverName ARSQLS27
UpperCaseConnection 0
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 0
]
Or for 11.1.2.3:
[
Description "DataDirect 7.0 SQL Server Native Wire Protocol"
DriverName ARSQLS26
UpperCaseConnection 0
UserId 1
Password 1
Database 1
SingleConnection 0
IsQEDriver 0
]
Once we save our file, we are ready to restart Essbase and give our data load another try.
Success! Parallel data loads are one of the many ways that we can improve our ASO process performance. In the future we’ll cover other ways to address ASO load performance. Hopefully this will help those of you that tried using the docs and did not find success.
Brian Marshall
April 4, 2016
I needed a break from the Planning repository and recently had a colleague inquire about getting the name of a member as a string value in ASO. This is a relatively simple process, but it occurred to me that when working with strings in Essbase, sometimes it helps to actually see what you are working with. So how do we display text in Essbase based on an MDX formula?
First, we have to turn on typed measures. In Essbase, the idea of typed measures are things like dates and text lists. Typed measures also give us the ability to display formula-based text from Essbase in Smart View. I specify Smart View because this functionality does not work in the Excel Add-In. So let’s start by enabling typed measures:
- Make sure you really want to do this, because there is no undo.
- Open your outline (I’m using ASOsamp in this example) and go to the properties tab.
- Change Typed measures enabled to True.
- Click OK when prompted.
Now that we have typed measures enabled, we can try to display some text. First let’s open the Measures dimension and add a member. I chose ProductName as my member. Once we have that member added, we can follow a few more steps and be ready for a retrieve:
- Open the Properties of the newly created member (in my case, ProductName).
- On the information tab, modify the Associate Format String to the following: MdxFormat([Products].CurrentMember.Member_Name)
- Next we need to associate a formula for the member. We do this because Essbase will not display a format string for a missing member. I just used a literal zero to ensure we always have a value.
- Save and restructure your cube.
We should be ready to see some text in Smart View:
Now that we have some text, we can start to manipulate that text and see things. First let’s do something simple, like get the length of our member name:
Set our format string: MdxFormat(NumToStr(Len([Products].CurrentMember.Member_Name)))
And in Smart View:
Quick side-note…notice that we use the NumToStr function because the MdxFormat function requires a string parameter.
As one last sample, here’s how we use a text function like Left:
Set our format string:
MdxFormat(Left([Products].CurrentMember.Member_Name,5))
And in Smart View:
There we have it. Text manipulation that you can see. Once you get your formula just right, you can use the strings in other formulas. You can use all of your regular functions and go nuts with strings.
In the last three days, I’ve had two different people ask me how to do currency conversion in ASO. Procedural calculations in ASO is tricky and the documentation isn’t great (and that’s being very polite to Oracle). So let’s take a look at how to do a simple currency conversion in ASO. First, we’ll need to set up a database to try this out. In my case, I’m going to start with Sample.Basic. The problem with that of course is that it isn’t an ASO application. I quickly converted my Sample.Basic from BSO to ASO with the conversion utility, but several additional steps were required:
- The Measures dimension has several MDX formula’s that need to be fixed.
- Deleted the Opening Balance member.
- Fix the Ratio member formulas.
- Add a new parent named Exchange Rates.
- Add two children, CAD and Exchange Rate.
- The Product dimension comes across as dynamic due to shared members.
- Add a roll-up for All Products and move the 100, 200, 300, and 400 parents underneath it.
- Change All Products and Diet to be stored hierarchies.
- Make the Product dimension label-only.
- We’ll use the Market dimension for our currencies.
- Create two new parents, USA and Canada.
- Move East, West, South, and Central under USA.
- Move Illinois and Louisiana under Canada.
- Rename Illinois to Ontario.
- Rename Lousiana to Quebec.
- Add CAD as a UDA to both of the Canada children.
- Create a new dimension named Currency.
- Add two children, Local and USD.
- Make the dimension label-only.
I’ve included a zip file for download here. This file includes the following:
- SampASO.otl – The modified outline file discussed above.
- SampASOFinal.txt – The sample data file used to test the currency calculation.
- currency.csc – The calculation script we’ll discuss shortly.
- currency.msh – The MaxL script that actually executes the currency conversion.
- currency.bat – The batch file used to execute the MaxL script.
Once you’ve made the changes to your outline (or used the one I’ve included), load the attached data file. This file can be loaded with no load rule. After the load has completed, we are ready to actually try our calculation. We have one last change to make to the outline before we get there. We will make use of our UDA that we added to the Market dimension. Modify the formula of the Exchange Rate member:
CASE WHEN IsUDA([Market].CurrentMember, “CAD”) THEN
([CAD],[No Market],[No Product])
ELSE
1
END
This calculation will determine the exchange rate member to reference in our actual calculation. Essentially, we check to see if the member is flagged as CAD and if so, we find the exchange rate intersection. If not, we just return 1 so that all of our local values get copied over straight into USD. The only downside to this method is that we have to modify this calculation every time we need to add a currency. Another way to approach this would be with an attribute dimension rather than a UDA. But for now, we’ll stick with simple. And now we have our data, we have some rates, and some logic to determine the rates. Let’s actually calculate the USD amount from Local.
To calculate the USD amount, we’ll be using the Execute Calculation MaxL statement. If you look up the documentation, you will see quite a few options for the ASO version of this statement. For currency conversion, we only have a few required options:
- local script – The CSC file that you find in the zip file. This will contain the calculation to be executed.
- source region – The MDX set containing all of the data elements necessary to complete our calculation. If you try to reference a member thats not included in your set, you’ll get an error. Think of this like an Excel spreadsheet you might reference with a formula.
- pov – An MDX set containing the point of view. Every intersection returned by your MDX statement will have the calculation in our CSC file executed against it. This can get dangerous in a hurry. Anything we do here can dramatically impact the number of cells generated and the amount of time and processing power required to complete the calculation.
Let’s examine the contents of the CSC file before we get into the MaxL script:
([USD]) := ([Local])*([Local],[Exchange Rate]);
In this example, we are setting the USD member equal to the Local member times our calculated Exchange Rate that we set up earlier. Now let’s actually dive into our MaxL script:
execute calculation on database SampASO.SampASO with
local script_file “currency.csc”
POV “{ CrossJoin({Descendants([Year],10,LEAVES)},
CrossJoin({Descendants([Profit],10,LEAVES)},
CrossJoin({Descendants([All Products],10,LEAVES)},
CrossJoin({Descendants([Market],10,LEAVES)},
CrossJoin({Descendants([Scenario],10,LEAVES)},
{[USD]}
)))))}”
SourceRegion “{ CrossJoin({Descendants([Year],10,LEAVES)},
CrossJoin({Descendants([Profit],10,LEAVES),[Exchange Rate]},
CrossJoin({Descendants([All Products],10,LEAVES)},
CrossJoin({Descendants([Market],10,LEAVES)},
CrossJoin({Descendants([Scenario],10,LEAVES)},
{[Local]}
)))))}”;
I’d first like to note that if you look this statement up in the documentation, it says execute calculation on database app.db. However, if you just copy the sample at the bottom of the documentation, they left out the word database. So if you get a syntax error and you copied it from the sample, this might be your issue. Now on to the first piece of our statement. First, we reference the CSC file that we’ve already covered. That’s pretty straight forward. Next, we tell Essbase the POV that we want to use to execute that script. In this example, we are actually executing the calculation on almost the entire cube, save the Currency dimension. Luckily, this is a very small cube.
Finally, we tell Essbase what the source for all of our data is. If you compare the two, at first glance it looks like the only difference is the that we reference Local in our source and USD in our POV. But, if you look very closely at the second line of our statements, the POV only has Profit leaf members, while the source has the Exchange Rate member. Now that we have our MaxL, CSC, and batch script ready to go, let’s execute the command and see what happens:
If everything goes according to plan, we should see a large number of cells generated by Essbase.
