Show Validation Rules usage on Webforms

One more post in the Show Usage series ;)

I was trying to see whether I can get a list of all Data Validation rules in a Planning application so that I can get rid of some of them real quick.

Think about all these series as my rants against PBCS (and in some cases ON-PREM)

I can run the below given query to find out what are my existing DV rules and which forms they are associated with.
And here is the result.

I don't know how the Row location is populated, it is not the row that is shown on the Webform, it is not the count of POV Dims+Col Dims + Row number, not the expanded number of Rows. (I did get lost while trying to figure out the ROW_LOCATION logic)

I've added a fail safe in the SQL when the column number goes beyond 26 (A~Z).

If you've a column number goes beyond 676, the SQL won't show correct results. (I hope you are not working at a place where you've a Webform which goes beyond ZZ!!!!)

If you want to see what each rule looks like, that is stored under HSP_FORM_DV_CONDITION

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)

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.