Different ways to get data from Essbase Cube

There are many different ways to extract data from an Essbase database. Here is the list of various methods of extracting data from your Essbase database:

Essbase Add-in for Microsoft Excel: 

Designed to work seamlessly with the world's most popular spreadsheet program, the Essbase add-in is the tool of choice of data analysts everywhere. We can pull data out of our Essbase database with the add-in and load up an Excel worksheet. Even though Microsoft Excel has a limit of 65,536 rows through the 2003 version and many more rows available in Microsoft Excel 2007, it seems that Essbase can have a hard time handling spreadsheets that contain more than 1,000 rows. Performance rapidly degrades and we can even experience timeouts.

Oracle Smart View: 

This tool is separate from the Essbase add-in and in fact, must be purchased as a separate component to be used with the Essbase suite of applications. As will be discussed at great length in the Appendix, Smart View is a dynamic data retrieval tool that allows the user to pull data directly from an Essbase database into any of the Microsoft Office components. This allows for the creation of Microsoft PowerPoint presentations that never need data updating as fresh data can be retrieved in real time into the slides. The same holds true for Microsoft Outlook emails and Microsoft Word documents.

Essbase Query Designer:

This tool is a part of the Essbase add-in and is a menu pick from the Essbase menu in Microsoft Excel. The Essbase Query Designer tool allows you to create simple to complex Essbase queries. This is accomplished through the use of an easy to use visual query wizard. The saved query can be opened and retrieved in Microsoft Excel to return your data.

Essbase Report Script: 

An Essbase report script can be created from scratch as an ASCII text file or conveniently, a report script can be created by Essbase, as a by-product of the Query Designer function. Essbase report scripts can be executed within a Microsoft Excel worksheet, Essbase command script or MaxL script, or from an Essbase API call.

Essbase MDX Query Language:

Essbase MDX can be easily described as a structured query language for multidimensional databases. MDX queries for the most part use simple and easy to understand statements to extract data from your Essbase database. MDX queries can be executed in much the same manner as Essbase report scripts or MaxL scripts.

             All of the above mentioned methods are used to report or extract the data that is in your Essbase database or cube. Some of the above mentioned tools go beyond the scope of the Microsoft Excel add-in and can be used by the other Microsoft Office products as well like Oracle Smart View. The Essbase add-in is the most complete tool in terms of functionality and ease of use. Included with the Essbase Add-in for Microsoft Excel is the Essbase Query Designer (EQD), which allows you to visually design custom reports from Essbase. The downside to the EQD is that the reports are only usable in Microsoft Excel. The Essbase report script can be used to run reports from a batch program and automatically send a morning report to key persons. While the formatting can be orderly, the report is still an ASCII based report without the pretty colors or graphs. And then there's MDX, which is a nice option, but the scripts do tend to require a bit of maintenance. However, the resultant output is in a very usable format.

Attribute Dimension Overview

    We can use attribute dimensions to retrieve and analyze data not only from the perspective of dimensions, but also in terms of characteristics, or attributes, of the dimensions. Essbase offers a specialized type of attribute dimension, called a varying attribute dimension. Varying attributes enable you to store changing attribute values in relation to other dimensions. 

Attribute Dimension Benefits:

          The following are a few ways that analysis by attribute provides depth and perspective, supporting better-informed decisions:

1. We can select, aggregate, and report on data based on common attributes.
2. By defining attributes as having a text, numeric, Boolean, or date type, you can filter data using type-related functions such as AND, OR, and NOT operators and <, >, and = comparisons. Only text attribute dimensions are supported in Planning databases.
3. We can use the numeric attribute type to group statistical values by attribute ranges; for example, population groupings such as <500,000, 500,000–1,000,000 and >1,000,000.
4. Through the Attribute Calculations dimension, automatically created by Essbase, we can view sums, counts, minimum or maximum values, and average values of attribute data.
5. We can perform calculations using numeric attribute values in calculation scripts and member formulas.
6. We can create cross-tabs of attribute data for a dimension, and you can pivot and drill down for detail data in spreadsheets.

         An attribute cross-tab is a report or spreadsheet showing data consolidations across attributes of the same dimension. Although we can create multilevel attribute hierarchies, they can have a serious impact on database and retrieval performance and should be avoided.

Attribute Calculations Dimension: 

            When we create the first attribute dimension in the outline, Essbase creates the Attribute Calculations dimension, which includes five members with the default names Sum, Count, Min (minimum), Max (maximum), and Avg (average). We can use the members in spreadsheets or reports to dynamically calculate and report on attribute data.
          The Attribute Calculations dimension is not visible in the outline. However in certain Oracle reporting tools that have been customized to work with Essbase, we can observe the presence of Attribute Calculations wherever we select dimension members, such as in the Member Selection dialog box in Financial Reporting or in Web Analysis.

The Attribute Calculations dimension has the following properties:

1. System-defined. Essbase creates the Attribute Calculations dimension and its five members automatically. We cannot add or delete members.
2. Label only. Like all label only dimensions, the Attribute Calculations dimension shares the value of its first child, Sum. When we view an attribute member in a report, you are effectively viewing the Sum calculations, whether or not the Sum member is displayed.
3. Dynamic Calc. The data in the Attribute Calculations dimension is calculated when a user requests it, and then it is discarded. You cannot store calculated attribute data in a database.
4. Not displayed in Outline Editor. The Attribute Calculations dimension is not displayed in Outline Editor. Members from this dimension can be viewed in spreadsheets and reports and managed on the Properties tab in Outline Editor.

There is no consolidation along attribute dimensions. We cannot tag members from attribute dimensions with consolidation operators or with member formulas to calculate attribute data.

Hyperion Business Rule Overview

In budgeting, the business logic requirement is not easy to achieve through the Planning outline design. We employ Business rules to implement complicated calculations. We are not going to code in any complicated programming language for a Business rule; rather, it will be all graphical in nature. Graphical interfaces ease Business rule creation, even for users with a business (non-technical) background. Nevertheless, Business rules can also be created by coding in all the calculations. In fact, the graphical interface also generates code behind the scenes and the user developing a rule using the graphical interface can switch to the source section for viewing the generated code. Business rules make use of the predefined functions and formulas of Essbase. Business rules are not created within the Planning web, unlike the outline structure or member formulas. We create Business rules through the EAS console. Nevertheless, we can create rules using the Calculation manager (which is a part of EPM architecture).

Some of the Advantages of Business rule are:

Easy to Create

1. Macros simplify the creation of business rules and sequences, and save business rule designers time by enabling them to reuse pieces of business rules in other business rules or macros.
2. Variables of the saved selection type can be used at run time and during design time to make business rules easier to design.
3. Predefined and reusable formulas that define cost and revenue calculations save business rule designers time during the design process. The following formulas are provided:

• Pro-Rata Ratio
• Units-Rates
• Variable
• Custom

The graphical depiction of components, formulas, and processes makes it easy to construct business rules in a multidimensional application.

Easy to Use

1. Business rules can be launched by business users from within Oracle Essbase Administration Services, the Business Rules Web Launcher, the Business Rules Command Line Launcher, and the Oracle Hyperion Planning, Fusion Edition Web Client.
2. Business rules can be launched in all Essbase server environments.
3. Business rules can be stored and run on a server across a wide area network (WAN).
4. Business rules with run time prompts ensure valid inputs from budget prepares.
5. Entry of run time prompt values during launching makes a single, centrally created business rule reusable by several users.

Easy to Maintain

1. The Business Rules architecture supports enterprise-wide maintenance and methods of use that can be successfully managed and deployed to multiple business users.
2. Business rules, sequences, macros, variables, and projects can be stored in any of these repositories: Microsoft Access, Microsoft SQL Server, Oracle, or DB2 for shared, enterprise wide access.
3. Use of a central repository makes administration and maintenance of business rules easy, because the repository stores information for applications in one database.
4. The logical organization of business rules, sequences, macros, and variables into projects makes them easier to find and maintain.

There are 4 main options in Hyperion Business Rule. They are

1. Rules – A rule typically runs a specific calculation/allocation on a single Essbase Database. It can be extended quite extensively to include advanced calculation scripts.
2. Sequence – A sequence basically helps in sequencing a list of rules based on business requirements. It basically provides an order to the Rules execution.
3. Macros – Macros are like mini rules which can be reused across multiple rules.
4. Global Variables – Global Variables are nothing but a set of variables that provide a global context to the variable values.

Since Hyperion Business rule comes along with EAS, its security is derived from Shared Services. Each rule/sequence execution/validation by a user can be controlled through the shared services console. HBR is relevant only to BSO cubes as ASO cubes do not support calculation scripts.

MaxL Scripts Overview

        MaxL is the multi-dimensional database definition language (DDL) for Essbase Server. Using MaxL, you can easily automate administrative and query operations on Essbase Server.
        A MaxL script contains a login statement and a sequence of MaxL statements, each terminated by a semicolon. If you use MaxL Script Editor to execute a MaxL script, the login statement is optional; you can select the Essbase Server that you want to connect to from the editor. Most MaxL statements begin with a verb and consist of grammatical sequences of keywords and variables. MaxL Script Editor color-codes the elements of the MaxL syntax and provides an auto-complete feature that helps you build statements as you type. We don't need to save MaxL scripts in order to execute MaxL statements. We can interactively type, execute and clear the MaxL statements within the editor to execute multiple scripts.

 Most operating systems support three channels for input/output:

• STDIN (standard input channel)
• STDOUT (standard output channel)
• STDERR (standard error channel)

                  Most operating systems also provide command-line options for re-directing data generated by applications, depending on which of the above channels the data is piped through.
                  Errors in MaxL are flagged as STDERR, allowing command-line redirection of errors using operating-system redirection handles. Non errors are flagged as STDOUT; thus normal output may be logged separately from error output. Beginning with Release 7.0, MaxL Shell errors are by default directed to the errorstream, and output is directed to the output stream.

Essbase Calculation Script Overview

       Calculation Script is group of command lines, those are use for calculation. Calculation scripts are text files that define how to calculate data in the database. In a block storage database, they represent the second method of calculations after the basic outline calculations. Calculation scripts perform calculations different from the consolidations and mathematical operations that are defined in the database outline. Calculation scripts files have a “*.csc” extension.

       A database contain two types of values: the values we have enter and the values that are calculated from the input data. 

• Outline calculations:  Outline calculations represent the simplest method of calculation. In BSO Essbase calculates the database according to the relationships between members in the database outline and according to member consolidation operators or formulas.
• Script based calculations: A calculation script contains a series of calculation commands, equations, and formulas. We use calculation scripts exactly how we want Essbase to calculate a BSO.
• With custom calculation scripts, we can override default database calculation on a temporary or permanent basis. In custom calculation scripts, we can refer to calculation rules defined in the database outline. The most common commands that reference outline calculations are CALC ALL, CALC DIM and AGG.

Calculation script Organization: 

        When creating a large calculation script, you need to plan the process, creating an architecture for calculation structure. Creating an architecture gives your calculation scripts a logical flow and ensures that calculation order and data block construction provide correct answers and optimal performance. Calculation script is organized in six standard sections:

• Information: It contains of details like script name, created by, on date, purpose of the script, directions to execute, versions, assumptions, comments, revised by and description.
• Housekeeping: This section sets the stage for the next sequence of calculations to occur. Two categories of commands used are SET and data manipulation commands. 
• SET commands prepare the calculator for proper processing of the commands in the resr of the script.
• SET CACHE HIGH: Instructs the calculation process to use the highest calculator cache setting stored in Essbase.cfg file.
• SET MSG SUMMARY: Instructs Essbase to display summary-level message in the application log of calculation process.
• SET UPDATECALC OFF: turns the intelligent calculator off. When intelligent calculation is used, Essbase calculates only blocks that were updated since the last calculation.
• SET CALCPARALLEL 4: Enables parallel calculation across four threads, in place of default serial calculation.
• Baseline fix: The baseline fix section is found near the top of most calculation scripts and defines the specific focus of the script. There are a number of issues to consider when creating a baseline fix. 
• Because Essbase requires you to reference data across all dimensions, it is common practice to add a member such as No customer to a dimension to represent data to which the dimension does not apply.

Basics of Hyperion Planning

          Oracle’s EPM System is a complete, open and integrated system that supports a broad range of analytic requirements.  It includes three layers of capabilities – Information Delivery, EPM and BI Applications and a common Business Intelligence Foundation.

          First is the Information Delivery Layer – this provides a complete set of information delivery and access capabilities – designed to address the needs of different types of users in an organization.  This includes interactive dashboards for execs and managers, ad-hoc analysis tools for power users, MS Office interface for Finance users, pixel-perfect reports and mobile support for casual users and others.

          Our EPM and BI Applications layer includes an integrated suite of market-leading performance management applications.  Based on the Hyperion suite, our PM Applications address key strategic and financial performance management processes including Strategy Management, Planning & Forecasting, Financial Close & Reporting, and Profitability Management.  It also includes integrated BI applications that can help users analyze data from ERP and CRM applications and also support specific industry requirements.

Hyperion Planning has the following characteristics: 

Pervasive: 
       It enables all levels of your organization to see information optimized for their role.
Comprehensive: 
       It incorporates information from your financial performance management, operational intelligence, and transactional applications.
Hot-pluggable: 
       It integrates with any data source, extraction, transformation and load tool, major business application, application server, security infrastructure, portal technology, front-end and analytical tools, and database.

Planning Features: 
        The following are the some of the features of planning.
Facilitates collaboration, communication, and control across multi-divisional global enterprises.
Provides ease of use and deployment through the Web or Oracle Hyperion Smart View.
Lowers the total cost of ownership.
Enhances decision-making.
Promotes modeling with complex business rules and allocations.
Integrates with other systems to load data.

Integrates with reporting applications for reporting and analysis.

Workspace Overview: 

         Workspace provides a centralized interface for viewing and interacting with content created using Hyperion financial applications, such as planning and financial management, and reporting content.

Benefits of using Workspace: 

• Single user logon to access both reporting and analysis content and planning.
• Single console for application management and creation.
• Centralized repository of key dimensional element for Hyperion products and console for controlling data flow between applications.
• Audit logging and process controls.
• Using workspace we can perform following tasks like build and manage applications.
• Manage data sources and manage metadata and data.
• Perform planning and consolidation application tasks, create rules with calculation manger.
• Conduct high-performance multidimensional modeling, analysis and reporting with Essbase.
• Use interactive reporting to generate ad hoc relational queries, self-service reports, and dashboards against ODBC data sources.
• Access and interact with other published content, like word or excel documents.
• Migrate entire applications or individual application artifacts to different servers.
• Launching workspace is done through entering the following URL in your web browser.

http://<web server> :< port number>/workspace.

Errors In Hyperion Workspace

Error: 
      Here is one of the possible error that we may face in Excel sheet with Excel Add-in: 
      "Essbase has encountered an internal Excel error. Your sheet will not be overwritten and none of your data will be lost. However, you are strongly urged to exit Excel and restart in order to clear up the problem" 

This was caused by a fixed Defect

Solution:

• Open your Excel Workbook.
• Go to File -> Save As and save it as an 'Excel 97-2003 Workbook'.
• Close and re-open this newly saved file.
• Go to Files -> Save As and save it back as an 'Excel Workbook' (this will reset it as an Excel 2007 book).

You should now be able to retrieve without errors.

Error:

     When logged into workspace to access the applications – some of the users are not able to view the applications and getting blank drop down list box from menu File->Open ->Applications->Planning -> Blank drop down list box as shown in the screen shot below.

Solution :

1. Add your Workspace Web server name to the list of Trusted Sites in IE (Internet Options > Security > Trusted Sites > Sites)

• Change the security settings for Trusted Sites by clicking the Custom Settings button in the Security Tab.

• Change the setting for ‘Allow script-initiated windows without size of position constraints’ to ‘Enable’.

• Refresh Internet Explorer windows and restart IE.

Error:

       Outlook issues from Smart View install.

To resolve this issue, use one of the following methods to remove the reference...

Method 1

• Start Outlook. 
• In the navigation pane, right-click the folder that is associated with the missing Outlook data file. 
• Click Close Folder_Name.

Note Folder_Name is the folder that is associated with the missing Outlook data file. 

• Restart Outlook.

Method 2

• Start Outlook. 
• On the File menu, click Data File Management.
• Click the folder that is associated with the missing Outlook data file, and then click Remove.
• Restart Outlook.