SSAUDIT And Cell Properties In Essbase
This article essentially consists of two parts – About SSAUDIT feature, which records each transaction detail and about Cell properties and sandbox dimension types.
The above mentioned aspects are useful in obtaining information on who submitted the data into Essbase cube and how it was loaded.
A cube is a metaphor for multidimensional data storage which has a symmetrical three-dimensional shape, either solid or hollow, enclosed by six equal squares.
Analysts prefer to view multidimensional data in cubes, instead of relational tables. A Data Cube is a multidimensional matrix that facilitates analysis from various perspectives.
Below are the three methods to load data into Essbase cube.
- Using Lock & Send option from a spreadsheet
- Using rule file for the files which we get from the source system
- Using free form loading for bulk data loads from any backup cubes
Consider a scenario, where certain users entered the wrong data into the cube due to the incorrect security setup. They might have actually submitted data for some entities which are not supposed to be accessed by them. Now the following questions are raised.
- Who are the users who accessed the Cube in that time frame?
- How to find out the suspect users?
- How to know the data values submitted by suspect users?
Let us consider any specific intersection with the below example having six dimensions. The challenge here is, to know how, when and by whom the data was loaded/submitted into the cube for that intersection.
In order to overcome this challenge, we use SSAUDIT feature in Essbase which helps us to keep track of the data modified by users.
Part – 1
SSAUDIT feature in Essbase to track every transaction in Application logs
Enabling the SSAUDIT feature in Hyperion Essbase:
- Open the Essbase configuration file which is called as Essbase.cfg present in
- Add our Hyperion Essbase Application and Database name for which we want to use the
SSAudit APPNAME DBNAME D:\Hyperion\Essbase\app\APPNAME
SSAUDIT CTB DB1 D:\Hyperion\Essbase\app\CTB
- Save the Essbase.cfg file
- Restart the Essbase Server
- By default, SSAUDIT will create two files (“.ALG” and “.ATX”) in the application path folder D:\Hyperion\Essbase\app\APPNAME with app name CTB.ATX and CTB.ALG files.
- Below is the example of “.ALG” file => which stores history records from every update transaction, including username, time stamp, and number of updated rows.
[Thu Dec 01 12:43:04 2019] Create Spreadsheet Update Log [Thu Dec 01 12:50:48 2019] Log Updates from User [admin] Starting at Row  For A Total Of  Rows [Thu Dec 01 12:52:12 2019] Log Updates from User [admin] Starting at Row  For A Total Of  Rows [Thu Dec 01 12:56:28 2019] Log Updates from User [admin] Starting at Row  For A Total Of  Rows [Thu Dec 01 12:58:17 2019] Log Updates from User [admin] Starting at Row  For A Total Of  Rows
7. Below is the example of “.ATX” file => which stores the update transaction records that can be used as the input source for data load
"Chennai" "HYD" "DELHI" "Bangalore" "Kolkata" "SI" "Budget" "DietCoke" "Sales" "Jan" #Mi #Mi 400. 250. 1200. 2080. "Budget" "Coke" "Sales" "Jan" #Mi #Mi 1400. 2525. 1200. 5682. "Budget" "RootBeer" "Sales" "Jan" #Mi #Mi 1400. 2489. 1268. 3452.
Advantage: Keeps track of data changes made by the users.
Drawback: If SSAUDIT feature is enabled, Lock & Send operation will be slower on that database, which slows down the client grid data update operations.
Part – 2
Cell Properties and Sandbox dimension types in Essbase
In the latest version (188.8.131.52 patch 500 which is specifically given to Apple customer by Oracle) we have Cell properties and Sandbox new dimension types with which we get more information on how and when the users update the data in Essbase cube.
Cell Properties is the new dimension type and below is the screenshot of Cell Properties and Sandbox dimensions types.
Figure: Screenshot image from EAS Console showing Cell Properties and Sandbox dimensions
Cell Properties dimension consists of three members
- Value: Data value which is stored in Essbase cube.
- Status: Cell status will track when and how a particular cell in the cube was populated. It will track how the value in a cell was arrived at – Load, Calc, Manual Entry and Dynamic.
- TID: Transaction ID for the latest transaction that modified the cell.
A status member having 6 numeric codes represents how the value has arrived into the cube. Cell statuses will be accessible via Calc Scripts, API and MAXL. For any intersection, the cell status must show the latest value and how the value got updated in the cube with below six status numbers.
|Cell Status – System Managed Statuses|
| Unknown (0) – reported when the system in not known |
Loaded (1) – Loaded from an external source
INPUT (2) – Data was entered into the cell by using a spreadsheet lock-an d-send or grid update
DYNAMIC (3) – Dynamic Calc in POV
DERIVED (4) – Data was derived automatically by the system
CALCULATED (5) – Data which was calculated by a user process
Table 1: Cell status numeric codes
Below is the example of showing how a record will exist in cube with Value, Status and TID
|HYD||J71B:Outdoor Customer||Dependent Demand||7433.83346||1||3747633|
Table 2: Example of record with Value, Status and TID
Sandbox dimension is another newly introduced dimension which is like a Version dimension concept in Hyperion Planning. Based on the requirement, we can add “n” number of sandboxes under Sandbox dimension. Here is an example.
In the above example (Table 2), by seeing Status as 1, we can confirm that the value (7433.83346) is loaded from an External source.
But, there is a concern here. While trouble shooting the data issue, we cannot know when this value was (7433.83346) and by whose User ID it got loaded/submitted into Essbase cube.
Our Team at MOURI Tech raised SR with Oracle to add below new two members under Cell Properties dimension. Oracle has appreciated and accepted to come up with new two members in their future releases, which are highlighted below.
If the new members – DATE and UserID are added under Cell Properties dimension, we can see a record just like below. In further releases, Oracle might add these two members in Cell Properties.
|HYD||J71B:Outdoor Customer||Dependent Demand||7433.83346||1||3747633||02-Aug-19||SID0643|
Benefits: By having DATE and UserID members in Cell Properties, we can know when the latest data changes happened in any intersection while troubleshooting the data issues. With this one can easily find answers for the questions about who submitted the data and when or how it was submitted.
Contact for further details
Seshu Kumar Desai
Team Lead – Analytics Oracle