Essbase Member Operation Utility - Rename Members

You can now perform bulk renames with this utility.

As I posted here, there was an issue with rename and that was because the way I was opening the outline.

openOutline() read-only mode was deprecated and now there are 3 options to open an outline

openOutline(boolean)
openOutline(boolean, boolean, boolean)
openOutline(boolean, boolean, boolean, boolean)

I was using the first option openOutline(true) - write mode and keep data in restructure. restructureCube(IEssCube.EEssRestructureOption.KEEP_ALL_DATA) it was removing the data with rename.

They was to set keepTrans to true, from the API guide

keepTrans - true to keep transactions. 
If you are opening an existing outline to make changes (readOnly == false), and you intend to restructure the cube and keep data,we recommend that you set this flag to true. When true, a log is kept of activities done to the outline. 
If you are starting from an empty outline or are not planning on saving data when you restructure, we recommend that you set this field to false. 
When false, no log is kept, saving time and memory.

Note: Take a backup of your outline and data before performing the operations

How it works

Before Rename

The data that you all remember (from our very old Sample Basic)

Create a file in the below format

oldname,newname


Run the utility with following options


You'll see a similar output.


After Rename



I've not included the support for renaming the members in formula. You'll have to do that manually. The tool will generate an exception if there is a validation error.

You can download the utility from here

Essbase Member Operation Utility - Count Members

I thought I'll be adding more fun stuff to this utility and keep it growing.

Rename is still an issue. I'll update once it is fixed.

For the people who are interested in this utility here is the download link

https://files.secureserver.net/0sUPxSol3xkpDe

I've added a new option where you can count the number of descendants, level 0 descendants under a parent.

You can create a file as shown below


  • Descendants will give you a count of all descendants of that parent.
  • Generation (n) will give you the count of all generation (n) members under that parent.
  • Level(n) will give you the count of all level (n) members under that parent.
  • Shared Descendants will give you the count of shared members under that parent.



Check this post for deleting members from cube.

I'm planning to add more fun stuff like member movement (now that is almost death to load rules)

Essbase Member Operation Utility - Delete Members

Again another draft item (there in drafts for almost 2 years now) getting materialized :)

If you look at load rules (I know Cameron is going to say dead to load rules, yes dead to load rules :) ) there is no option to perform a rename or a delete a single member or descendants.

This was the case with Planning too, but then we got DeleteSharedDescendants.cmd file which can delete all shared member instance from a hierarchy, we even saw OutlineLoad utility getting an Operation field where you can specify Delete Idescendants, Delete Descendants, Delete Level0

This one is for Essbase, you can now perform an automated member delete :)

Let's look at the Delete option
Mention the members and operation in a text file as given below

Format is
Member,Operation


Tool will then perform a delete operation :)

If you use a parent level and Delete Member operation, it'll not delete it and will ask you to change the operation.

Let's look at a delete operation on a modified version of Sample Basic.


I created a file with Delete Shared Descendants option.



I did ran a test on my Production system and I was able to delete 820 shared descendants in 10 mins.


I did a delete on 3 different alternate hierarchies.

The tool can be used for rename operation in a similar way, however I did find an issue with rename option, it removes all data from the cube!!!!!

That is one issue which I'm trying to solve and I will be adding the download link once that issue is fixed.

Move Planning Text Data to ASO with String concatenation

I saw an interesting question on OTN. 

I'm so tied up at work and don't get much time to do that often now, however when I do I try to go through ones that interest me.

For those who are interested can read it here. I gave an idea and that idea actually consumed me.

With all the work going on, I was thinking about my idea and it grow in me and now it is out. (Kind of like watching "Inception" and feeling that it can be real!!!!)

What Anubhav (I don't know him personally) was looking for is a way to get the text data from Planning and load that into a Reporting ASO cube.

As you all know in Planning you can have free form text (Text data type) and one using Smart List. There is an option (In Map to Reporting) where  you can convert Smart List to a dimension in your reporting application (Pretty neat stuff, if you ask me)

This is based out of a Text data type measure. How Planning handles text measure is interesting, if you've looked at the backend tables, you'll find a table named HSP_TEXT_CELL_VALUE



Now as you see there is no dimensional element in there, just an ID and a value, what happens is the ID gets stored in Essbase as a number and when the form (Smart View planning connection) opens, it runs a query (if it is a text measure) matches the Essbase value with the ID in this table and shows the text.

Now the idea is getting more clear, all I've to do is get an extract of account that I'm going against and does the mapping Planning does and get a list of all Text components.

Do I need to do that or is there an easier way. (I always like easy ;) )

If you've looked at OutlineLoad utility in the later releases (I think this was added from 11.1.2.2.300+), you've an option to extract data.

I used OutlineLoad to extract the data.


I got all Text values in a file now :)

I can now create a TextList (I still don't know what it is Smart List in one and Text List in another) from this file.

Oh I forgot to add that there is no MaxL command for importing a Smart List, however there is a JAVA API component which can do it. From here it was easy.

Do some data massaging and load the Text List into Essbase.

I created a bat file which will allow you to do this.

Bit of background on OutlineLoad parameters and Data.

This is how my form and data looks like


I'm using Account as my Load Member for the extract (the dimension which has Text Datatype)

I'm using Period as my Data member (I choose this because, that is one which I felt could be same across all places and will need less customization). The tool scans for 12 data fields only, if you need more then edit the FOR loop to add more tokens)

1 thing that I noted during the extract is it doesn't like /EDD switch if you are not using a properties file. No matter what you try it keeps telling that /EDD requires 4 arguments and I supplied more, add the same to a properties file and it works like charm.

Things to keep in mind

If you are planning to add a roll up concatenated string, then you should update the member aggregation properties in Essbase. Automatically Essbase tags a Text measure as never consolidate.


I created a jar file for importing Smart List (Text List) and you can use that for just importing a Text List to Essbase.


Let's look at the bat execution.


After the successful extraction of Text data using OutlineLoad, the tool will prompt you for addition input, If you want to add a roll up text then you'll have to add it manually in the TextList file.

So in my case I want the roll up to show a concatenated value of my two children.


The tool assigns the IDs automatically, As you saw P1 and P2 roll up to ProfitCenter (so the total is 3) I can add it manually in the file save it and press any key to upload the Text List.

My friend/colleague Kishore Mukkamala was asking what if I've sub roll ups. He did gave me the idea of updating that in the blog post (I forgot about this one)

If you've have roll ups, the toll won't know where it is, it'll sequentially add IDs to all text lists

You'll have to change the IDs and add roll up concatenation.

E.g If my hierarchy resembles this


Tool is going to produce a Text List file as shown below


You'll have to look at the Planning extracts and see what is the comment associated to P1 and P2

I know that it is 1 and 2 so 3 will become my concatenation, here is how the final file will look like


Save the file and press any key and you'll see a message that TextList import was successful.


Here is my Essbase pull


You can download the bat file from here

HTH

jMnDLoader a graphical approach for loading data and metadata into Hyperion Planning

This one was there in my drafts for about 2 years. Now that a long time considering that I was trying to create a utility. Now what was/is this about, it is about a thread on Network54 where Cameron Lackpour talked about adding a graphical interface to the OutlineLoad.cmd. I thought well that it is a good tool to work on (considering the number of switches you have to remember to make it work).

Then started the work and I could finish that in two weeks time. (Now you would be imagining, didn't he said two years) It was done in VB and my employer at that time was not comfortable with me publishing it :(

After few months Oracle came up with a web interface for Outline Load and I shelved mine :(

I was surprised when I got a call from my ex-colleague, asking about the possibility of running that on a Linux machine. This prompted me to create a java version so that I can run that on Windows and *nix systems.

I was lazy to work on that one (there is a web one out there and who will be interested - was my reason)

Why now? Someone reminded me that they've seen my tool and they are on 11.1.1.4 version and I realized that there is no such thing as web interface for that version. (there are a lot of versions that we are dealing with and I can't remember what came in which version ;))

So here we are jMnDLoader, the tools provides you with a graphical interface for OutlineLoad utility. It has an option where you can generate MetaData.

This is not a client version, it needs to be executed on the Planning server.


Tool makes intelligent selections for you ;) as you can see for Dry run, it'll not lock the dimension and rest of the options are not enabled.

This is still on the 11.1.1.x format (it doesn't have an option to add properties), however the screen captures are from a 11.1.2.3.500 version (it works with 11.1.2.3.500, except for the properties part)


You can generate Metadata using the tool.






I've added a help file to the tool, you can find more details on how it works there.

I'm planning to integrate EPMA metadata generation and if all goes well, maybe an interface for the infamous EPMA batch client.

You can download jMnDLoader from here.

HTH

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.