Friday, April 29, 2016

Essbase Security & Cache Management

Essbase Native Security
  • Pre-System 9: Essbase native (legacy) security
  • Post-System 9: Shared Services security
Essbase Layered Security
  • Global Access Layer
  • None
  • Read
  • Write
  • Calculate
  • DB Manager
  • Users & Groups Layer: The type of security levels remains the same irrespective of the user or group level. The chosen security combination varies between User & Group. Access is usually given to whole DB or Sub set of DB or It is usually restricted  to No DB Access as shown in the below diagram.
  • Access Privileges
Access privileges are divided into two categories i.e. Application privileges & Database access privileges.
Application Access privileges
  • None
  • Access Databases
  • Application Manager
Database Access privileges
  • None
  • Filter
  • Read Only
  • Read/Write
  • Calculate
  • Database Manager
Application Access Type Security
Designates the type of applications to which a user has access.
  • Available beginning with Release 9
  • Applies to Essbase and Planning users
Creating Users in Native Security Mode
Essbase Security File
  • Contains all Essbase security information for the server
  • Is located in the Essbase Server  bin directory
Essbase Security File Tips
  • Handling the following scenarios:
–  Corrupted security file  
–  Copied security file that does not work  
  • Backing up SEC and BAK files  
–  Locating in separate folders
–  Starting up after a server interruption
  • Checking security file contents
–  DUMP command  
–  Exporting the Essbase Security File (first available in Release 9.3.1)
Creating Essbase Security Filters
Essbase Security Filter Layer
  • Cell-level database access
  • Assigned to users or groups
  • Filter settings:  
    –  None  
    –  Read  
    –  Write
    –  Meta Read
  • AND/OR filter logic
  • Not applied to calculations
Creating Security Filters
  • Administration Services Console provides the Filter Editor.
Filter Behavior
The below figure addresses the restrictions due to various filters i.e.,
  • No Access or NONE to Balance sheet of Qtr1->OEM-> Lighbolt 365A.
  • READ to Cost of Sales of Qtr1->OEM-> Lighbolt 365A
  • WRITE  to Net Sales of Qtr1->OEM-> Lighbolt 365A
Multiple Conditions
Multiple conditions are applied when applying filters separately i.e. to each member level. When applied in combinations it functionality varies as shown below compared to that applied separately.  
Meta Read Filters
Filter Design Issues
  • Calculation Access
     – Can be assigned for all or  individual calculation scripts
     – Ignores filter access settings
    – Includes read/write access
    – Provides access to the default calculation (can be “;”)
  • Login Delays
    Resolving Security Conflicts
        When security permissions overlap, the following two rules determine security precedence: 
  • An access level that defines a more detailed dimension combination list takes precedence over a level with less detail.  
  • If the preceding rule does not resolve the overlap conflict, the highest access level is applied.
Shared Services Security
A system for centralized user and group management
  • Accesses Shared Services and external user directories
  • Provides one interface for all Oracle Hyperion products  
  • Includes security migration utility  
  • Includes GUI (console)
Converting Essbase to Shared Services Security
The installation default is native (legacy) security mode.
  • Convert:
    –  Administration Server
    –  Essbase Server
  • Migrate:
    –  Administration Server users and groups
    –  Essbase Server users and groups

Externalize Users Wizard
Externalize Users Wizard migrates Administration Services and Essbase Server users from legacy security to Shared Services security.
  • Converts Essbase Server to Shared Services security  
  • Provides user conversion logic and options

Post-migration Shared Services Security
After Essbase Server and Essbase users and groups are migrated to Shared Services security, the following are available:
  • Native Essbase users and groups  
  • Externally authenticated users
  • Custom-authenticated users
And the following structure is created for assigning security:
  • Shared Services projects
  • Shared Services applications
  • Essbase applications
Provisioning Users in Shared Services
The process of assigning roles and access permissions to users for Essbase applications
  • Requires Shared Services security  
  • Uses corporate or native Shared Services user directories
  • Is performed in Shared Services Console
After migration, fine-tune permissions through:
  • Calculation access 
    * Filter access

Shared Services User Roles
  • Categorized under three license types:  
    –  Power User
    –  Interactive User
    –  View User
  • Linked to your license agreement
Assigning Database Filter and Calculation Script Access
  • Create filters and calculation scripts in Essbase.
  • Assign access through Shared Services Console.
Synchronizing Essbase and Shared Services Security
  • Specifies when Essbase and Shared Services refresh security information for all users, groups, and applications on an Essbase Server
  • Options: auto, manual (default)  
  • Example: CSSREFRESHLEVEL auto
Companion configuration commands:
External Authentication
  • Is built into all Oracle Hyperion products (beginning with System 9)  
  • Is integrated with Shared Services security  
  • Supports the following authentication repositories:  
    –  LDAP-enabled user directories (Oracle Internet Directory, MSAD, others)  
    –  SAP  
    –  NT LAN Manager (NTLM)
    –  Relational database (Oracle, DB2, SQL Server)  
  • Employs existing corporate structure of user accounts  
  • Enables single sign-on capability
  • Works with Shared Services Console for centralized user and group management
Single Sign-on
  • Eliminates multiple sign-ons for multiple Oracle Hyperion products
  • Uses a Security Application Programming Interface (Security API)
    –  Validates users  
    –  Determines user access to EPM System products
  • Allows you to enable single sign-on directly to EPM System products or from external systems.
Optimizing Block Storage Caches
Block Storage Optimization Overview: Adjusting cache settings is just one of several strategies for optimizing block storage database performance.
  • Outline design
  • Calculation script design
  • Cache memory settings
  • Report buffer settings
  • Data compression methods  
  • Fragmentation management
Cache Types
Index cache
Holds IND pages
Data cache
Holds recently accessed data blocks
Data file cache
Holds PAG files
Calculator Cache
Tracks data blocks during calculation (bitmap)
Dynamic Calculator Cache
Manages data blocks for dynamically calculated dense members
Index Cache
Data Cache
Data File Cache
Calculator Cache
Bitmap that tracks data blocks during calculations:
  • Uses varying amounts of memory, depending on the database configuration and the available memory
  • Requires testing to determine performance improvement
  • Is set in the essbase.cfg file with the CALCCACHE command
  • Is invoked from the calculation script
Dynamic Calculator Cache
Memory buffer that stores all of the blocks needed for calculating a Dynamic Calc member in a dense dimension:
  • Separate cache for each open database
  • DYNCALCCACHEMAXSIZE setting in the essbase.cfg file
    –  20 MB default  
    –  0 (zero) disables  
  • Five configuration file settings
1 MB (Buffered I/O)
10 MB (Direct I/O)
1 MB(min)
3 MB
3 MB(min)
Data File
32 MB
10 MB(min)
200 KB
200 MB(max)
Dynamic Calculator
20 MB

Setting Cache Sizes
  • Set most cache sizes in the Database Properties dialog box.
Cache Hit Ratios
  • Hit ratios measure the effectiveness of your cache settings.
  • The hit ratios indicate the percentage of time that a requested item of information is available in the cache, and does not have to be retrieved from disk. Three hit ratios help you tune your block storage database.
  • Hit ratio on index cache—Indicates the Essbase kernel success rate in locating index information in the index cache without retrieving another index page from disk
  • Hit ratio on data cache—Indicates the Essbase kernel success rate in locating data blocks in the data cache without retrieving the blocks from the data file cache
  • Hit ratio on data file cache—Indicates the Essbase kernel success rate in locating data file pages in the data file cache without retrieving the data file from disk