Show Smart List Usage - Forms & Accounts

How can I see which Smart List is associated to which member?

I had to do that on a PBCS implementation and as you know there is no access to the backend. Luckily the client named the Smart lists similar to the member names, however there were some which was shared between members!!!!

On OnPrem, it is easier I can query the tables :)

This query will give you the Smart List name along with the members it is assigned to.

What if you would like to know which Smart List members are used in which forms, you can run the below given query to get to that.

OnPrem got the answer for that too :)

I hope we get the Show Usage for Smart Lists, Menus and member usage in Calc Manager rules in the system.

I did write SQLs for the above ones and you can look them up here

Essbase Member Operation Utility - Change Data Storage

You can now use this utility to convert BSO application to Hybrid. (as long as you know what Hybrid is :))

If you don't then read my older brother's blog posts on the same :)

How it works

Create a file which is similar to the one below.

Run the utility with following options

Utility will update the storage properties of all parents in your hierarchy to the one that you specified.

In the example I changed all the parents under Market to a Dynamic Calc and all parents under Product to Store.

This can help you convert an existing BSO cube to Hybrid. Look at ASODYNAMICAGGINBSO

I'm planning to add another option to this which will let you know the members which should be tagged as "Never Share".

Happy Hybridising :)

You can download the file from here.

Update 07/29/2015

I tested this on 19K higher level (44K members) hierarchy and it was done in less than 3 minutes!!!
Turns out this is the fastest way to prepare Essbase cubes for Hybrid :)

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


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

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

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.

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.