Persistence of disabled Allow connects

ESSbase - v11.1.1.3
DBAG - Release 11.1.1 - Page# 635

To disable connections to an application we would uncheck the option Allow connects on the application properties.  DBAG states the persistence of this disabled option as below:

Connections are disabled until any of the following actions occur:
1. The application is stopped and restarted.
2. An administrator re-enables connections.

I had to disable connections to an application & tried to test option1.  I had stopped the database, stopped the application, started the application back & found that the option is still unchecked which is in contrary to option1.

To further test it, I had restarted the ESSbase service & found that the option has been checked now.  To confirm the behavior I had tested it again & the results are same.  So here is what I concluded:

Connections are disabled until any of the following actions occur:
1. The ESSbase (not application) is stopped and restarted.
2. An administrator re-enables connections.

**DBAG could be right & our ESSbase environ might be mischievous, who knows!!

BSO & ASO outline metadata

D:\Hyperion\products\Essbase\eas\server\EssbaseAso.metadata.xml
D:\Hyperion\products\Essbase\eas\server\EssbaseBso.metadata.xml

Seeing those two XML files since the Essbase installation... Everytime I just open it, browse it & close it. These files looks to define the outline metadata of BSO & ASO. Always wanted to edit the files & see if the change really gets into effect. In case that happens, we can define the default settings for our cubes' outline.

For example, by default the duplicate members in BSO/ASO outlines is disabled. I see this section in the file, what if I change defaultValue to true & restart Essbase service? Will it start accepting duplicate names by default?

But I do not have enough courage to test that on Prod.

Essbase Custom Defined Functions & Macros

Q: Where are CDF/macro definitions stored on server?

A: They are stored in a XML file at the location below...
<HYPERION_HOME>\products\Essbase\EssbaseServer\java\essfunc.xml

Smart slice storage & security

Q1: Where are smart slices stored? Is it on client or server? What is the location? How much space do they take up?
Q2: How do we secure smart slices?

Note: Version is 11.1.1.3

A1: Smart slices are stored on the server. They are stored as XML files which hardly take up any space (usually in kb). Its like the data slice definition is stored in XML files. These files are stored at <hyperion_home>\products\Essbase\aps\data\cubeviews\

A2: We cannot secure smart slices, they can be seen by all the users who have access to the database. But when retrieving data, it may show up #No Access if the user doesn't have access to data.

Here is the link to a discussion on oracle forum

Export an essbase cube using a report script from MaxL editor

export database 'AppName.'DbName' using server report_file 'ReportFileName' to data_file 'D:\export.txt';

When the above line is executed from a MaxL editor in EAS, it writes to the specified path on the server but not client.

How do we make it work to write to a client?

Share a folder on your machine to yourself with Full control & use that UNC path to export the file...

export database 'AppName.'DbName' using server report_file 'ReportFileName' to data_file '\\ComputerName\SharedFolder\export.txt';

Did it work? No, it errors out saying...

Unable to open file ['\ComputerName\SharedFolder\export.txt']

If you have noticed, you will find that there is only a single backslash before the ComputerName when there should be two. Ok, so now try this one...

export database 'AppName.'DbName' using server report_file 'ReportFileName' to data_file '\\\\ComputerName\SharedFolder\export.txt';

Still errors out but you are seeing it correct with two backslashes...

Unable to open file ['\\ComputerName\SharedFolder\export.txt']

This is because of the security of the shared folder... we had given the share a permission to ourselves thinking we are executing it but that is not how it looks to be working.

We have to give the Essbase server the permission to write to the location...
1. Right click on the folder & select properties.
2. Click on the Sharing tab & add the Essbase server with Change & Read permission. Full Control is not required. Also remove the Everyone
3. Now go to the security tab & add the Essbase server with Write & Modify permission.

Now try to execute the script again, now it should work. If it doesn't work feel free to play around with security & try again.

EPM diagnostic tool validate.bat errors out

The validate.bat file when run on the server, errors out with the java.lang.NullPointerException

Two reasons could be...

1. The default password of admin id might have been changed. Set it back to the default password & try again.
2. This file can only run with the ID that was used during installation. Check if you have logged into the machine with same ID. For the file to run using any other id, follow the instructions below & try again.
1. Login to the machine using the id that was used to install EPM suite.
2. Open the Start|Run prompt & type in %userprofile% & hit enter.
3. In the folder that opened, find the file ".oracle.products"
4. Copy this file to the profile folder of the user using which you want run the validate tool.
5. Now login using the other id & try to run the validate tool, it should work.

User logging details from Essbase log

The user logging information can be extracted from the essbase.log file. However its is difficult to parse the log file & get the information out of it. Here is a method to get that information.

Note that I was using Essbase v11.1.1.3

As we already know there is a relational database involved with Essbase... we can query the table serverlogdetail & get the information required. In the query below, "Essbase" is the schema name that is usually provided while configuring the EPM.

This query lists all the users logged into Essbase in December 2010. When we generate the LogCharts in Essbase, these tables are populated. So, before we run this query, we have to generate the log charts.

What is that we are achieving after all? Once we get the output from the query to excel, we can pivot it & analyze the users' usage, like which user is most active, which user has logged in how many times etc.

Whether useful or not, this is some good info to know... Information is wealth!! isn't it?

select * from essbase.serverlogdetail where
extract (year from entrydate) = '2010' and
extract (month from entrydate) = '12' and
msgtext like 'Logging in user%'



Gurus out there: If any of the information above looks to be wrong, pls write a comment to correct it.