Essbase Oracle Essbase Interview Questions And Answers 2h3862

1. How Essbase and OBIEE integrated? 2. What is the process for database creation? 3. What is the difference between SmartView and the Essbase add-ins? 4. What is the difference between Oracle OLAP and Essbase OLAP? 5. What is the difference between Essbase Studio, Essbase Integration Services and Essbase istration services? 6. What are the new features of the Essbase 11? 7. What is OLAP and what is the difference between its kinds? 8. What is the difference between aggregate storage ASO and block storage BSO?

1. How Essbase and OBIEE integrated? OBIEE stands for Oracle Business Intelligence Enterprise Edition and it is a solution for reporting and analysis. OBIEE can connect to dimensional as well as relational sources. OBIEE process converts Essbase cube, dimension, and hierarchies into logical relational star schema. Essbase data can be combined with data from other sources. Essbase cube can be imported into the physical layer of the OBIEE. OBIEE recreate a measure hierarchy similar to Essbase and it talks to Essbase via MDX queries. EPM Workspace and OBIEE Shared security provisioning and organization between Essbase and OBIEE, through application roles and policies Essbase is for what if analysis and business cubes; Hyperion is for financial and operational and strategic planning. We can install Essbase and the other EPM tools into the same WebLogic domain as OBIEE, using a single installer. Essbase SmartView s OBIEE as a data source, and the ability to spin-off aggregates from the RPD into Essbase ASO cubes. The (Excel, Word, PowerPoint, Outlook) add-ins for OBIEE is able to author reports as well as run existing Answers-authored ones

2. What are the steps for planning, creating and deploying Essbase databases? You can create the database outline in Essbase to define the database dimensions and hierarchy. Load the data using rules files to map db dimensions. Calculate data using calculation scripts; using the rules files you can ignore certain fields in the data source, manage header information, change the order, format member names, scale data values and create/accept and reject criteria for records. You can then analyze data in smart view. Below are the implementation phases and steps: Implementation phase Analysis and planning

Database creation

Deployment and

Implementation steps Identify business results Examine data sources Analyze sample reports Select a database type and design the database outline. 5. Create the database outline 6. Create the rules files 7. Create calculation scripts or deign aggregations. 8. Maintain outlines 9. Manage data flow. 10. Analyze data. 11. Provide management and . 1. 2. 3. 4.

3. What is the difference between smart View and the Essbase add-ins SmartView is the Oracle future tool. It is a tool for all application Essbase, HFM, Planning, Hyperion, OBIEE etc. It can be connected to multiple databases in the same workbook. One database connection per sheet, It can be connected to Oracle OLAP database. It can be integrated with MS Word, MS Excel, outlook and PowerPoint. The Essbase Excel Add-in is designed to only Essbase; The Below table lists the difference: Essbase Excel Add-in

Smart View

Drill Capabilities

Yes

Yes

Keep only, Remove Only

Yes

Yes

Member Selection

Yes

Yes

Query Designer

Yes

Yes (different)

Retain Formulas

Yes (must select formula preservation)

Yes (by default)

Formatted Reports

Yes

Yes

Cell Text / LROs

Yes

No

Adjust function

No

Yes

DTS

Yes

Yes

Substitution Variables

Yes

No; Coming Soon

Member name referenced in a linked formula

Yes

No; Coming Soon

Cascade

Yes

No; Coming Soon

Ancestor Position

Yes

Yes

Use Excel Formatting & Cell Styles

Yes

Yes

Yes – One Undo

Yes – Multiple Undo’s

No

Yes

Difference

Flashback / Undo POV Manager – Copy and Paste POVs

Share POVs

No

Yes

Define Default Starting POV

No

No; Coming soon with Smart Slices

Multi-Source Grids / Reports

Yes

Yes (using data points)

Alias Table defaults to Default

Yes

No

Submit Data to Essbase

Yes

Yes

Rpt Script

MDX

Copy / Paste Data Points

No

Yes

Refreshable Essbase Data in Word and PowerPoint

No

Yes

Visualize in Excel

No

Yes

Launch business rules

No

No

MDX vs. Rpt Script

4. What is the difference between Oracle OLAP and Essbase OLAP? Essbase and Oracle OLAP are two of the leading OLAP solutions. Below are the similarities between the two: Similarities between Essbase OLAP and Oracle OLAP

    



Excellent performance for query that needs summary level data. Fast, incremental update of data sets, which is required to facilitate frequent data updates. Rich calculation models that may be used to enrich analytic content A dimensional model that presents data in a form that is easy for business s to query and define analytic content

Storage



Essbase OLAP Multidimensional Arrays



Oracle OLAP Multidimensional Arrays



Method Query language Process Type Product dependency Primary use



s



Functions and features

  

  

MDX and XML/A Stand-alone server Any relational database



ing OLAP analytical applications Essbase is owned and managed by line-of-business s in partnership with the IT group. Essbase centers on custom analytics and BI applications with a focus on EPM. This strategy addresses the whatif, modeling, and futureoriented questions that companies need answered today in order to see into the future. Essbase uses a multidimensional database stored on disk and in RAM. The line of business typically has a large degree of uncertainty and needs a dynamic and changing environment. Essbase is the OLAP server that provides an environment for rapidly developing custom analytic and EPM applications. The data management strategy allows. Essbase to easily combine data from a wide variety of data sources, including the Oracle Database.











 

SQL, via query rewrite or SQL views In-database, embedded process Oracle Database Enterprise Edition



Enhancing SQL-based Data Warehouses



IT manages the Oracle database and with Oracle OLAP, all of the data resides in an Oracle database, governed by centralized data security and calculation rules. As an embedded component of the Oracle Database, Oracle OLAP benefits from the scalability, high availability, job scheduling, parallel processing, and security features inherent in the Oracle Database.





An SQL interface to OLAP cubes allows SQL-based applications to query cubes within an Oracle database, and benefit from the performance and analytic content of the OLAP option. The primary data-access language for Oracle OLAP is SQL, making Oracle OLAP a natural choice for enhancing the performance and calculation capabilities of an existing Oracle data warehouse.

5. What is the difference between Essbase Studio, Essbase Integration Services and Essbase istration services? The functionality of Oracle Essbase Integration Services has been incorporated into Essbase Studio, and s familiar with Integration Services will transit quickly to Essbase Studio. There are some slight differences in terminology and some difference in the sequence of tasks, but the two applications have much in common. In Integration Services, you create a data source name (ODBC DSN) to point to your source data, and Integration Services uses ODBC to connect to the database.

In Essbase Studio, you can use either ODBC or JDBC to connect to your data source during cube deployment. Cube deployment sets your load options for building an outline and loading data into an application and database. The two methods of connecting to your data source are known as “non-streaming” and “streaming.” As opposed to EIS, Essbase Studio gives developers the option of deploying all dimensions, one or a set of dimensions, or performing a data load via MaxL. It provides an intuitive interface with wizards that simplify data model design, cube design, and application building and deployment. It provides one environment for the deg of all data models and it provides the ability to create drill-through reporting as opposed to EAS. It provides the ability to build a dimension library that can be used to build other applications. It provides for the creation of calendar hierarchies with built-in wizards Disadvantage of Essbase Studio The EAS and EIS development tools are both more mature than Essbase Studio. Essbase Studio will need to be used in conjunction with EAS for the following development tasks in version. Configurations of certain Essbase Application settings need to be done in EAS. Creation of substitution variables is available only with EAS. Creation of security filters is available only with EAS. Dragging and dropping of which are not in Measures dimension is only ed in EAS. Calculation, rules, report scripts, MaxL, and MDX editors are not available in Essbase Studio. Creation of partitions is not available in Essbase Studio

6. What are the new features of the Essbase 11.1.1.0.0? 

Varying attributes; A varying attribute enables you to track changes in attribute values across one or more independent dimensions. The below example shows Varying attribute: Associated with Product dimension, varies with an independent dimension (Time). Measures associated with changed attribute values can be compared and analyzed. Changes in attributes themselves can be tracked and analyzed in reports. Data can be analyzed based on perspectives. Historical perspectives allow you to extend historical data patterns for one attribute during a given time frame into other time frames. Point-in-time perspectives allow you to view data associated with past and current attribute values. Month



April

May

June

July

August

Sales

6000

2000

1000

1000

7000

Representati ve

Jones

Jones

Smith

Smith

Smith

Text and date measures Text measures extend the analytical capabilities of Essbase beyond numerical data to text-based content. Storage and analysis of text content can be useful when a cell needs one of a finite list of text values. Date measures enable to have date-type values. Certain types of analysis that are difficult to represent by using the Time dimension are aided by the ability to process dates as measures.



Backup, transaction logging, and replay. This feature is ed only for block storage databases. Data cannot be written to an Essbase database while the database is undergoing a backup operation.



New ASO features; ASO Partial Data Clear is a new option that enables you to clear a well-defined region of the ASO cube without affecting other data areas. Data can be cleared by setting the values to 0 can be removed with a logical clear, but which generally is fast. Data that must be permanently erased can be fully removed with a physical clear, but it is slower.Two types of clear region options are available: o Physical clear of cells in a region; The Physical Clear option completely removes cells in the specified region

o Logical clear of cells in a region; The Logical Clear option, the default option, removes cells by creating compensating cells 

New calculation script and MDX functions



New XOLAP feature

7. What is OLAP and what is the difference between its kinds. OLAP (online analytical processing) is computer processing that enables a to easily and selectively extract and view data from different points of view. OLAP data is stored in a multidimensional database. Whereas a relational database can be thought of as twodimensional, a multidimensional database considers each data attribute (such as product, geographic sales region, and time period) as a separate "dimension." OLAP can be used for data mining or the discovery of relationships between data items. An OLAP database does not need to be as large as a data warehouse, since not all transactional data is needed for trend analysis. Using Open Database Connectivity (ODBC), data can be imported from existing relational databases to create a multidimensional database for OLAP. OLAP is a technology that s activities ranging from self-service reporting and analysis to purpose-built management applications such as planning and budgeting systems. An effective OLAP solution solves problems for both business s and IT departments. For business s, it enables fast and intuitive access to centralized data and related calculations for the purposes of analysis and reporting. For IT, an OLAP solution enhances a data warehouse or other relational database with aggregate data and business calculations. In addition, by enabling business s to do their own analyses and reporting, OLAP systems reduce demands on IT resources. OLAP offers five key benefits: a. Business-focused multidimensional data b. Business-focused calculations c. Trustworthy data and calculations d. Speed-of-thought analysis e. Flexible, self-service reporting

OLAP Types Three main types of OLAP are available: multidimensional OLAP, relational OLAP, and hybrid:

Multidimensional OLAP (MOLAP), The data is stored in a multidimensional. Both Essbase and Oracle OLAP use MOLAP technology. Essbase stores data in a multidimensional database. Oracle OLAP cubes are multidimensional objects stored in the Oracle Database. MOLAP cubes are automatically indexed based on the dimensions. Data can be located using offset addressing. To find a given value in a multidimensional array, a MOLAP product needs to use only multiplication and addition, and computers do those operations very fast. MOLAP technology is the best option for dense arrays, where most of the data cells in a cube contain a value. That said, both Essbase and Oracle OLAP have capabilities to manage sparse MOLAP cubes effectively. Relational OLAP Relational OLAP (ROLAP) uses a traditional star/snowflake schema and relational data sources only. With ROLAP, data is neither aggregated nor manipulated. The data is stored in relational tables that can be queried by SQL. ROLAP is ideal for lower density (sparse) cubes. ROLAP automatically provides all of the advantages of a relational database, such as high availability, replication, read consistent view of data, backup and recovery, parallel processing, and job scheduling. Hybrid OLAP With hybrid OLAP (HOLAP), the data is stored both in an OLAP data store and a relational database. For example, you may have summary-level data stored in the OLAP data store and detailed data stored in the relational database. You could then drill down from the OLAP data store to the detail stored in the relational database. Today, most OLAP products the hybrid architecture. Both Essbase and Oracle OLAP can be implemented in this fashion.

1 1 1

8. What is the difference between aggregate storage ASO and block storage BSO? Block Storage Block storage is the historical storage methodology in Essbase. Databases using this storage method hold data in small linear arrays, called blocks. The exact architecture of the blocks is determined by dense and sparse dimensions. For every intersection where a piece of data exists, Essbase creates a block. Essbase also creates a block for every intersection of Product, Market, and the scenario (Actual, Budget, and so forth) Aggregate Storage Aggregate storage databases store and manage data very differently from block storage databases. Instead of storing data in arrays (blocks), aggregate storage databases work with cells. In a block storage database, if you query a single value from a block, the entire structure comes into memory on the server. Because data structures are not pre-allocated, aggregate storage database can handle very expanded dimensionality and a lot more data. For instance, we have worked with models containing more than 10 million customers in a single dimension, as well as those with multiple millions of per dimension in many dimensions. With aggregate storage databases, data is loaded at level 0, and all upper-level (for example, East) and member formulas are derived dynamically. To optimize retrieval performance, you can run an aggregation process on the database to build stored values at some upper-level intersections. After loading data, Essbase analyzes the source data and builds aggregates to optimize those queries that will take the longest to resolve based on the structure of the database. You can also have Essbase monitor the query patterns of your base, and then build aggregations to serve your specific queries better. Essentially, the model is self-learning. In general, aggregate storage models are ideal for aggregating large data sets (also called rack and stack applications). While you can do complex mathematics in aggregate storage models, all formulas are derived dynamically. A formula that is overly complex can affect performance. Although there are usually numerous ways to optimize processing in aggregate storage databases so that complex formulas do not have a large impact on performance, the dynamic nature of such formulas should be taken into consideration.

BSO

ASO

Build dimensions

Y

Y

Load data

Y

Y

Write back to level 0

Y

Y

Retrieve data

Y

Y

Partitioning / Sharing Data

Y

Y (target or source in 11x)

Unlimited dimensions and

N

Y

Write back to any level

Y

N

Advanced calculation engine

Y

N

Calc scripts vs. MDX formulas

Y

N

Partitioning / Sharing Data

Y

Limited – pre-11x

Table 86. Inherent Differences Between Aggregate Storage and Block Storage Inherent Differences

Aggregate Storage

Block Storage

Storage kernel

Architecture that s rapid aggregation, optimized to high dimensionality and sparse data

Multiple blocks defined by dense and sparse dimensions and their , optimized for financial applications

Physical storage definition

Through the Application Properties window, Tablespaces tab in istration Services

Through the Database Properties window, Storage tab in istration Services

Inherent Differences

Database creation

Aggregate Storage

Migrate a block storage outline or define after application creation

Block Storage

Define after application creation

Note: Do not use the file system to copy a block storage outline into an aggregate storage application. Use the migration wizard in istration Services to migrate the outline.

Databases ed per application

One

Several (one recommended)

Application and database names

See Naming Restrictions for Applications and Databases. Names reserved for tablespaces, cannot be used as application or database names:

See Naming Restrictions for Applications and Databases.



default



log



metadata



temp

Application and database information display

Displayed in the Application Properties window and the Database Properties window in istration Services. (Information not ed by or relevant to aggregate storage applications is not shown. For a description of aggregate storage specific information, see the Essbase istration Services Online Help for the Application Properties window and Database Properties window.)

Displayed in the Application Properties window and the Database Properties window in istration Services

Configuration settings (essbase.cfg)

For a list of the settings that apply to aggregate For a list of the settings storage databases, see the Essbase Technical that do not apply to Reference. block storage databases, see

Inherent Differences

Aggregate Storage

Block Storage

the Essbase Technical Reference.

Table 87. Outline Differences Between Aggregate Storage and Block Storage Outline Functionality

Dense or sparse dimension designation

Aggregate Storage

Not relevant

Block Storage

Relevant

Multiple hierarchies Relevant enabled, dynamic hierarchy, or stored hierarchy designation

Not relevant

s dimensions and on dynamic hierarchies

Full

with the following exceptions: 

No two- calculation (however, for information on specifying the calculation order, see Calculation Order)



No association of attribute dimensions with the dimension tagged as s



Additional restrictions for shared . See Alternate Hierarchies.

on stored with the following exceptions: Full hierarchies  for the ~ (no consolidation) operator (underneath label-only only) and the + (addition) operator

Outline Functionality

Member storage types

Aggregate Storage



Cannot have formulas



Restrictions on label only (See Member storage types.)



No Dynamic Time Series



Stored hierarchy dimensions cannot have shared . Stored hierarchies within a multiple hierarchies dimension can have shared . See Stored Hierarchies.

with the following exceptions:  

Block Storage

for all member storage types in all types of Dynamic Calc and Store not relevant dimensions except On stored hierarchies, two limitations if a attribute dimensions member is label only: o

All dimension at the same level as the member must be label only

o

The parents of the member must be label only.

Note: On dynamic hierarchies, ability to tag any member as label only Note: On conversion from a block storage database, attribute dimension are tagged as Dynamic Calc. On standard dimension Dynamic Calc tags are converted and tagged as stored , which changes the Stored value on the Dimensions tab of the Database Properties window in istration Services.

Outline Functionality

Aggregate Storage

Block Storage

Ragged hierarchies , with possible performance impact and hierarchies with more than 10 levels

Outline validation





When database is started



When outline is saved



When block storage outline is converted to aggregate storage outline



When requests



When outline is saved



When requests

Outline paging



No

Database restructure

There are several levels of restructure; see Aggregate Storage Database Restructuring

There are levels of restructure; see Optimizing Database Restructuring.

Table 88. Calculation Differences Between Aggregate Storage and Block Storage Calculation Functionality

Aggregate Storage

Block Storage

Database calculation

Aggregation of the database, which can be predefined by defining aggregate views

Calculation script or outline consolidation

Formulas

Allowed with the following restrictions:

for Essbase calculation functions



Must be valid numeric value expressions written in MDX (cannot contain % operator, replace with expression:

Calculation Functionality

Aggregate Storage

Block Storage

(value1/value2)*100) 

No for Essbase calculation functions



On dynamic hierarchy , formulas are allowed without further restrictions

Calculation scripts

Not ed

ed

Attribute calculations dimension

for Sum

for Sum, Count, Min, Max, and Average

Calculation order Member formula calculation order can be defined by the using the solve order member property

Defined by the in the outline consolidation order or in a calculation script

Table 89. Partitioning and Write Back Differences Between Aggregate Storage and Block Storage Partitioning and WriteBack Functionality

Partitioning

Aggregate Storage

with the following restrictions: 

Transparent partitions and Linked partitions are ed



Replicated partitions are not ed



Aggregate storage database as the source database only for transparent

Block Storage

with no restrictions

Partitioning and WriteBack Functionality

Aggregate Storage

Block Storage

partitions 

ability to change data (write back)

No outline synchronization

Transparent partition technique used to enable Full limited write back

Table 90. Data Load Differences Between Aggregate Storage and Block Storage Data Load Functionality

Aggregate Storage

Block Storage

Cells loaded through Only level 0 cells whose values do not data loads depend on formulas in the outline are loaded

Cells at all levels can be loaded (except Dynamic Calc )

Update of database values

At the end of a data load, if an aggregation exists, the values in the aggregation are recalculated

No automatic update of values. To update data values you must execute all necessary calculation scripts.

Data load buffers

The loading of multiple data sources into Not ed. aggregate storage databases is managed through temporary data load buffers.

Atomic replacement of the contents of a database

When loading data into an aggregate Not ed. storage database, you can replace the contents of the database or the contents of all incremental data slices in the database.

Data slices

Aggregate storage databases can contain multiple slices of data. Data

Not ed.

Data Load Functionality

Aggregate Storage

Block Storage

slices can be merged.

Dimension build for shared

Full for parent-child build for all build method. Duplicate generation (DUPGEN) methods build method limited to building alternate hierarchies up to generation 2 (DUPGEN2).

Loading data mapped to dates

In a date-time dimension, you can load Date-time dimension type is data into level-0 using not ed. ed date-format strings instead of member names.

Table 91. Query Differences Between Aggregate Storage and Block Storage Query Functionality

Aggregate Storage

Block Storage

Report Writer

ed, except for commands Fully ed related to sparsity and density of data

Spreadsheet Toolkit

ed, with limited ability to change data (write back)

Fully ed

(See ability to change data (write back)Differences Between Aggregate and Block Storage.)

API

ed

Export

with the following restrictions: ed 

Export of level 0 data only (no upper-level export)

ed

Query Functionality

Aggregate Storage



MDX queries

Block Storage

No columnar export

ed

Queries on attribute Returns values for descendants of the that are non-level 0 member. associated with non-level 0 (See also Design Considerations for Attribute Queries.)

ed

Returns #MISSING for descendants of the nonlevel 0 member

Queries on attribute and shared

A shared member automatically shares A shared member does the attribute associations of its nonnot share the attribute shared member associations of its nonshared member

Query logging

Not ed

Query performance

Considerations when querying data Hierarchies not relevant from a dimension that has multiple hierarchies. SeeQuery Design Considerations for Aggregate Storage.

ed.

Features ed by Aggregate and Block Storage Some features are not ed for aggregate storage. The following table describes the differences between aggregate and block storage. Table 92. Features ed by Aggregate and Block Storage Features

Aggregate Storage

Block Storage

Aliases

ed

ed

Currency conversion

Not ed

ed

Features

Aggregate Storage

Block Storage

Data mining

Not ed

ed

Hybrid analysis

with the following restriction: queries that contain a relational member and an Essbase member with a formula in the same query are not ed.

ed

For example, if California is a relational member and the member Profit has a formula, the following report script returns an error: Jan California Profit !

Incremental data load ed

ed

LROs

Not ed

ed

Time balance reporting

with the following restrictions:

ed

Triggers



Skip Zeros is not ed



Time dimension must contain at least one stored hierarchy



Shared must be at level zero

After-update triggers ed

On-update triggers and after-update triggers ed

Features

Aggregate Storage

Block Storage

Unicode

Not ed

ed

Variance reporting

Not ed

ed

Date-time dimension type and linked attribute dimensions

ed

Not ed