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

CSSREFRESHLEVEL:

• 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:

• CSSSYNCLEVEL
• SHAREDSERVICESREFRESHINTERVAL

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

Cache	Function
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

Cache	Default	Min/Max
Index	1 MB (Buffered I/O) 10 MB (Direct I/O)	1 MB(min)
Data	3 MB	3 MB(min)
Data File	32 MB	10 MB(min)
Calculator	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