Brook Preloader

Blog

SSAUDIT And Cell Properties In Essbase

Introduction

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.

  1. Using Lock & Send option from a spreadsheet
  2. Using rule file for the files which we get from the source system
  3. 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.

  1. Who are the users who accessed the Cube in that time frame?
  2. How to find out the suspect users?
  3. 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.

Entity Product Year Period Scenario Account Data
HYD AXE 2019 Oct Actual Sales 5000
HYD AXE 2019 Sep Actual Sales 4256
HYD AXE 2019 Aug Actual Sales 1235

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:

  1. Open the Essbase configuration file which is called as Essbase.cfg present in
    SERVER\d$\Hyperion\Essbase\bin folder
  2. Add our Hyperion Essbase Application and Database name for which we want to use the
    SSAUDIT feature.
    SSAudit APPNAME DBNAME D:\Hyperion\Essbase\app\APPNAME
    SSAUDIT CTB DB1 D:\Hyperion\Essbase\app\CTB
  3. Save the Essbase.cfg file
  4. Restart the Essbase Server
  5. 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.
  6. 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 [1] For A Total Of [10] Rows [Thu Dec 01 12:52:12 2019] Log Updates from User [admin] Starting at Row [8] For A Total Of [15] Rows [Thu Dec 01 12:56:28 2019] Log Updates from User [admin] Starting at Row [20] For A Total Of [35] Rows [Thu Dec 01 12:58:17 2019] Log Updates from User [admin] Starting at Row [28] For A Total Of [44] 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 (11.1.2.4 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

  1. Value: Data value which is stored in Essbase cube.
  2. 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.
  3. 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

      Value Status TID
      sb1 sb1 sb1
      W39.25-Jun W39.25-Jun W39.25-Jun
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.

Sandbox <5>  
  Sb1
  Sb2
  Sb3
  Sb4

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.

  1. Value
  2. Status
  3. TID
  4. DATE
  5. UserID

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.

      Value Status TID DATE UserID
      sb1 sb1 sb1 sb1 sb1
      W39.25-Jun W39.25-Jun W39.25-Jun W39.25-Jun W39.25-Jun
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
seshud.in@mouritech.com
MOURI Tech

0 0 vote
Rating
guest
0 Comments
Inline Feedbacks
View all comments