Essbase General
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Essbase General as PDF for free.
More details
- Words: 2,298
- Pages: 7
Essbase is a multidimensional database management system (MDBMS) that provides a multidimensional database platform upon which to build analytic applications. Essbase, whose name derives from "Extended Spread Sheet dataBASE", was originally developed by Arbor Software, which merged with Hyperion Software in 1998. It is currently available from Hyperion Solutions Corporation (now a subsidiary of Oracle Corporation [1]), and until late 2005 was also marketed by IBM as DB2 OLAP Server. [2] Oracle announced the acquisition of Hyperion on March 1, 2007. The acquisition closure was announced on April 18, 2007. The term "on-line analytical processing" (OLAP) was coined by database researcher E. F. Codd in a whitepaper that set out twelve rules for analytic systems, an allusion to his earlier famous set of twelve rules defining the relational model. This whitepaper, published by Computerworld, was somewhat explicit in its reference to Essbase features, and when it was later discovered that Codd had been sponsored by Arbor Software, Computerworld controversially withdrew the paper.[3] By comparison with "on-line transaction processing" (OLTP), OLAP defines a database technology that is optimized for processing human queries rather than transactions. The results of this orientation was that MDBMS oriented their performance requirements around a different set of benchmarks (Analytic Performance Benchmark, APB-1) than that of RDBMS (Transaction Processing Performance Council (TPC)). Many Hyperion products were renamed in 2005, giving Essbase an official name of Hyperion System 9 BI+ Analytic Services, but the new name was largely ignored by practitioners. The Essbase brand was later returned to the official product name for marketing purposes, but the server software still carried the "Analytic Services" title until it was incorporated into Oracle's Business Intelligence product suite. [4] Hyperion Essbase was named as one of the 10 most influential technology innovations of the last 10 years by Information Age magazine in its 10th anniversary issue, along with Netscape, Blackberry, Google, virtualization, Voice Over IP (VOIP), Linux, XML, the Pentium processor and ADSL. Editor Kenny MacIver said: "Hyperion Essbase was the multi-dimensional database technology that put online analytical processing on the business intelligence map. It has spurred the creation of scores of rival OLAP products – and billions of OLAP cubes".
Contents [hide] •
• •
1 History and Motivation o 1.1 Sparsity o 1.2 Aggregation 2 Block Storage (Essbase Analytics) o 2.1 Calculation Engine 3 Aggregate Storage (Enterprise Analytics)
• • •
o 3.1 Calculation Engine 4 User Interface 5 Administrative Interface 6 References
•
7 External links
[edit] History and Motivation Although Essbase has been categorised as a general-purpose multidimensional database, it was originally developed to address the scalability issues associated with spreadsheets such as Lotus 1-2-3 and Microsoft Excel. Indeed, the patent covering Essbase uses spreadsheets as a motivating example to illustrate the need for such a system. [1] In this context, "multi-dimensional" refers to the representation of financial data in spreadsheet format. A typical spreadsheet may display time intervals along column headings, and account names on row headings. For example: Jan Feb Mar Quantity 1000 2000 3000 Sales $100 $200 $300 Expenses $80 $160 $240 Profit $20 $40 $60
Total 6000 $600 $480 $120
If a user wants to break down these values by Region, for example, this typically involves the duplication of this table on multiple spreadsheets: North Jan Feb Mar Quantity 240 1890 50 Sales $24 $189 $5 Expenses $20 $150 $3 Profit $4 $39 $2
South Total Jan Feb Mar 2180 Quantity 760 110 2950 $218 Sales $76 $11 $295 $173 Expenses $60 $10 $237 $45 Profit $16 $1 $58
Total Region Total Jan Feb Mar 3820 Quantity 1000 2000 3000 $382 Sales $100 $200 $300 $307 Expenses $80 $160 $240 $75 Profit $20 $40 $60
An alternative representation of this structure would be a three-dimensional spreadsheet grid, giving rise to the idea that "Time", "Account", and "Region" are dimensions. As further dimensions are added to the system, it becomes very difficult to maintain spreadsheets that correctly represent the multi-dimensional values. Multidimensional databases such as Essbase provide a data store for values that exist, at least conceptually, in a multi-dimensional "hypercube".
[edit] Sparsity
Total 6000 $600 $480 $120
A technical problem faced by multidimensional databases is the physical representation of data as the number and size of dimensions increases. Say the above example was extended to add a "Customer" and "Product" dimension: Dimension Number of dimension values Accounts 4 Time 4 Region 3 Customer 10,000 Product 5,000 If the multidimensional database reserved storage space for every possible value, it would need to store 2,400,000,000 (4 × 4 × 3 × 10000 × 5000) cells. If each cell is represented as a 64-bit floating point value, this equates to a memory requirement of at least 17 gigabytes. In practice, of course, the number of combinations of "Customer" and "Product" that contain meaningful values will be a tiny subset of the total space. This property of multi-dimensional spaces is referred to as sparsity.
[edit] Aggregation OLAP systems generally provide for multiple levels of detail within each dimension by arranging the members of each dimension into one or more hierarchies. A Time dimension, for example, may be represented as a hierarchy starting with "Total Time", and breaking down into multiple years, then quarters, then months. An Accounts dimension may start with "Profit", which breaks down into "Sales" and "Expenses", and so on. In the example above, if "Product" represents individual product SKUs, analysts may want to also be able to report using aggregations such as "Product Group", "Product Family", "Product Line", etc. Similarly, for "Customer", natural aggregations may arrange customers according to geographic location or industry. The number of aggregate values implied by a set of input data can be surprisingly large. If the Customer and Product dimensions are each in fact six "generations" deep, then 36 (6 × 6) aggregate values are affected by a single data point. It follows that if all these aggregate values are to be stored, the amount of space required is proportional to the product of the depth of all aggregating dimensions. For large databases, this can cause the effective storage requirements to be many hundred times the size of the data being aggregated.
[edit] Block Storage (Essbase Analytics) Since version 7, Essbase has supported two "storage options" which take advantage of sparsity to minimize the amount of physical memory and disk space required to represent large multidimensional spaces. The Essbase patent[1] describes the original method, which
aimed to reduce the amount of physical memory required without increasing the time required to look up closely-related values. With the introduction of alternative storage options, this was named Block Storage Option (Essbase BSO), and later referred to as Essbase Analytics in marketing material. Put briefly, Essbase requires the developer to tag dimensions as "dense" or "sparse". The system then arranges data to represent the hypercube into "blocks", where each block is multi-dimensional array made up of "dense" dimensions, and space is allocated for every potential cell in that block. Sparsity is exploited because the system only creates blocks when required. In the example above, say the developer has tagged "Accounts" and "Time" as "dense", and "Region", "Customer, and "Product" as "sparse". If there are, say, 12,000 combinations of Region, Customer and Product that contain data, then only 12,000 blocks will be created, each block large enough to store every possible combination of Accounts and Time. The number of cells stored is therefore 192000 (4 × 4 × 12000), requiring under 2 megabytes of memory, plus the size of the index used to look up the appropriate blocks. Because this implementation is hidden from front-end tools (i.e., a report that attempts to retrieve data from non-existent cells merely sees "null" values), the full hypercube can be navigated naturally, and it is possible to load values into any cell interactively.
[edit] Calculation Engine Calculations in Essbase BSO can be specified as: • • • •
the aggregation of values through dimensional hierarchies; stored calculations on dimension members; "dynamically calculated" dimension members; or procedural "calculation scripts" that act on values stored in the database.
The first method (dimension aggregation) is implicitly performed through addition, or by selectively tagging branches of the hierarchy to be subtracted, multiplied, divided or ignored. Also, the result of this aggregation can be stored in the database, or calculated dynamically on demand -- members must be tagged as "Stored" or "Dynamic Calc." to specify which method is to be used. The second method (stored calculations) uses a formula against each calculated dimension member -- when Essbase calculates that member, the result is stored against that member just like a data value. The third method (dynamic calculation) is specified in exactly the same format as stored calculations, but are calculated when a value addressed by that member is accessed by a user, and are not stored. The fourth method (calculation scripts) uses a procedural programming language specific to the Essbase calculation engine. This type of calculation may act upon any data value in
the hypercube, and can therefore be used to perform calculations that cannot be expressed as a simple formula. A calculation script must also be executed to trigger the calculation of aggregated values or stored calculations as described above -- a built-in calculation script (called the "default calculation") can be used to execute this type of calculation.
[edit] Aggregate Storage (Enterprise Analytics) Although Block Storage effectively minimizes storage requirements without impacting retrieval time, it is limited by its treatment of aggregate data in large applications, motivating the introduction of a second storage engine, named Aggregate Storage Option (Essbase ASO) or more recently, Enterprise Analytics. This storage option makes the database behave much more similarly to OLAP databases like SQL Server Analysis Services. Following a data load, Essbase ASO does not store any aggregate values, but instead calculates them on demand. For large databases, where the time required to generate these values is inconvenient, the database can materialize one or more aggregate "views", made up of one aggregate level from each dimension (for example, the database may calculate all combinations of the fifth generation of Product with the third generation of Customer), and these views are then used to generate other aggregate values where possible. This process can be partially automated, where the administrator specifies the amount of disk space that may be used, and the database generates views according to actual usage. The major drawback of this approach is that the cube cannot be treated for calculation purposes as a single large hypercube, because aggregate values cannot be directly controlled, so write-back from front-end tools is limited, and complex calculations that cannot be expressed as MDX expressions are not possible.
[edit] Calculation Engine Calculations in Essbase ASO can be specified as: • •
the aggregation of values through dimensional hierarchies; or dynamically calculated dimension members.
The first method (dimension aggregation) is basically the same as for Essbase BSO. The second method (dynamic calculations) evaluates MDX expressions against dimension members.
[edit] User Interface
The most widely known user interface to Essbase is an add-in for Microsoft Excel (previously also Lotus 1-2-3). The add-in adds a menu to the spreadsheet application that can be used to connect to Essbase databases, retrieve data, and navigate the cube's dimensions ("Zoom in", "Pivot", etc).[2] With the release of System 9, Hyperion provided a new user interface add-in for Essbase called "SmartView for Microsoft Office". SmartView provides access to Essbase and other System 9 content for Microsoft Powerpoint, Microsoft Word, Microsoft Outlook as well as supplanting the previous add-in for Microsoft Excel. In 2005, Hyperion began to offer a visualization tool called Tableau under the name "Hyperion Visual Explorer".[3] Tableau was originally developed at Stanford University as a government-sponsored research project to investigate new ways for users to interact with relational and OLAP databases. Other user-facing applications with support for Essbase databases are: • • • • • • •
Hyperion Analyzer (aka Hyperion System 9 BI+ Web Analysis) Hyperion Reports (aka Hyperion System 9 BI+ Financial Reporting) Hyperion Enterprise Reporting Hyperion Intelligence (aka Hyperion System 9 BI+ Interactive Reporting) Hyperion SQR (aka Hyperion System 9 BI+ Production Reporting) Alphablox Arcplan dynaSight (aka Arcplan Enterprise)
The previous offerings from Hyperion are offered with new names as given below: Hyperion's previous offerings Hyperion System 9 BI+ offerings Hyperion Essbase ASO Enterprise Analytics Hyperion Essbase BSO Essbase Analytics Hyperion Analyzer Web Analysis Hyperion Reports Financial Reporting Hyperion Intelligence Interactive Reporting Hyperion SQR Production Reporting Hyperion Metrics Builder Enterprise Metrics An API is available for C, Visual Basic and Java, and embedded scripting support is available for Perl. The standardised XML for Analysis protocol can be used to query Essbase data sources using the MDX language.
[edit] Administrative Interface A number of standard interfaces are provided for the administration of Essbase applications:
• •
• •
•
ESSCMD, the original command line interface for administration commands; MaxL, a "multi-dimensional database access language" which provides both a superset of ESSCMD commands, but with a syntax more akin to SQL, as well as support for MDX queries; Essbase Application Manager, the original Microsoft Windows GUI administration client, compatible with versions of Essbase before 7.0; and Essbase Administration Services, later renamed Analytic Administration Services, and then back to 'Essbase Administration Services' in v. 9.3.1, the currentlysupported GUI administration client. Essbase Integration Server for maintaining the structure and content of Essbase databases based on data models derived from relational or file-based data sources.
[edit] References http://download.oracle.com/docs/cd/E10530_01/doc/index.htm 1. ^ a b Earle, Robert J. (1992) "Method and apparatus for storing and retrieving multi-dimensional data in computer memory". United States Patent 5,359,724 assigned to Arbor Software Corporation. 2. ^ Hyperion Solutions Corporation (2006). Essbase Database Administrator's Guide. 3. ^ Tableau Software (2005). Tableau Software Lands Global OEM Deal with Hyperion. Press release.
[edit] External links •
Hyperion at Oracle
Retrieved from "http://en.wikipedia.org/wiki/Essbase" Categories: OLAP
Contents [hide] •
• •
1 History and Motivation o 1.1 Sparsity o 1.2 Aggregation 2 Block Storage (Essbase Analytics) o 2.1 Calculation Engine 3 Aggregate Storage (Enterprise Analytics)
• • •
o 3.1 Calculation Engine 4 User Interface 5 Administrative Interface 6 References
•
7 External links
[edit] History and Motivation Although Essbase has been categorised as a general-purpose multidimensional database, it was originally developed to address the scalability issues associated with spreadsheets such as Lotus 1-2-3 and Microsoft Excel. Indeed, the patent covering Essbase uses spreadsheets as a motivating example to illustrate the need for such a system. [1] In this context, "multi-dimensional" refers to the representation of financial data in spreadsheet format. A typical spreadsheet may display time intervals along column headings, and account names on row headings. For example: Jan Feb Mar Quantity 1000 2000 3000 Sales $100 $200 $300 Expenses $80 $160 $240 Profit $20 $40 $60
Total 6000 $600 $480 $120
If a user wants to break down these values by Region, for example, this typically involves the duplication of this table on multiple spreadsheets: North Jan Feb Mar Quantity 240 1890 50 Sales $24 $189 $5 Expenses $20 $150 $3 Profit $4 $39 $2
South Total Jan Feb Mar 2180 Quantity 760 110 2950 $218 Sales $76 $11 $295 $173 Expenses $60 $10 $237 $45 Profit $16 $1 $58
Total Region Total Jan Feb Mar 3820 Quantity 1000 2000 3000 $382 Sales $100 $200 $300 $307 Expenses $80 $160 $240 $75 Profit $20 $40 $60
An alternative representation of this structure would be a three-dimensional spreadsheet grid, giving rise to the idea that "Time", "Account", and "Region" are dimensions. As further dimensions are added to the system, it becomes very difficult to maintain spreadsheets that correctly represent the multi-dimensional values. Multidimensional databases such as Essbase provide a data store for values that exist, at least conceptually, in a multi-dimensional "hypercube".
[edit] Sparsity
Total 6000 $600 $480 $120
A technical problem faced by multidimensional databases is the physical representation of data as the number and size of dimensions increases. Say the above example was extended to add a "Customer" and "Product" dimension: Dimension Number of dimension values Accounts 4 Time 4 Region 3 Customer 10,000 Product 5,000 If the multidimensional database reserved storage space for every possible value, it would need to store 2,400,000,000 (4 × 4 × 3 × 10000 × 5000) cells. If each cell is represented as a 64-bit floating point value, this equates to a memory requirement of at least 17 gigabytes. In practice, of course, the number of combinations of "Customer" and "Product" that contain meaningful values will be a tiny subset of the total space. This property of multi-dimensional spaces is referred to as sparsity.
[edit] Aggregation OLAP systems generally provide for multiple levels of detail within each dimension by arranging the members of each dimension into one or more hierarchies. A Time dimension, for example, may be represented as a hierarchy starting with "Total Time", and breaking down into multiple years, then quarters, then months. An Accounts dimension may start with "Profit", which breaks down into "Sales" and "Expenses", and so on. In the example above, if "Product" represents individual product SKUs, analysts may want to also be able to report using aggregations such as "Product Group", "Product Family", "Product Line", etc. Similarly, for "Customer", natural aggregations may arrange customers according to geographic location or industry. The number of aggregate values implied by a set of input data can be surprisingly large. If the Customer and Product dimensions are each in fact six "generations" deep, then 36 (6 × 6) aggregate values are affected by a single data point. It follows that if all these aggregate values are to be stored, the amount of space required is proportional to the product of the depth of all aggregating dimensions. For large databases, this can cause the effective storage requirements to be many hundred times the size of the data being aggregated.
[edit] Block Storage (Essbase Analytics) Since version 7, Essbase has supported two "storage options" which take advantage of sparsity to minimize the amount of physical memory and disk space required to represent large multidimensional spaces. The Essbase patent[1] describes the original method, which
aimed to reduce the amount of physical memory required without increasing the time required to look up closely-related values. With the introduction of alternative storage options, this was named Block Storage Option (Essbase BSO), and later referred to as Essbase Analytics in marketing material. Put briefly, Essbase requires the developer to tag dimensions as "dense" or "sparse". The system then arranges data to represent the hypercube into "blocks", where each block is multi-dimensional array made up of "dense" dimensions, and space is allocated for every potential cell in that block. Sparsity is exploited because the system only creates blocks when required. In the example above, say the developer has tagged "Accounts" and "Time" as "dense", and "Region", "Customer, and "Product" as "sparse". If there are, say, 12,000 combinations of Region, Customer and Product that contain data, then only 12,000 blocks will be created, each block large enough to store every possible combination of Accounts and Time. The number of cells stored is therefore 192000 (4 × 4 × 12000), requiring under 2 megabytes of memory, plus the size of the index used to look up the appropriate blocks. Because this implementation is hidden from front-end tools (i.e., a report that attempts to retrieve data from non-existent cells merely sees "null" values), the full hypercube can be navigated naturally, and it is possible to load values into any cell interactively.
[edit] Calculation Engine Calculations in Essbase BSO can be specified as: • • • •
the aggregation of values through dimensional hierarchies; stored calculations on dimension members; "dynamically calculated" dimension members; or procedural "calculation scripts" that act on values stored in the database.
The first method (dimension aggregation) is implicitly performed through addition, or by selectively tagging branches of the hierarchy to be subtracted, multiplied, divided or ignored. Also, the result of this aggregation can be stored in the database, or calculated dynamically on demand -- members must be tagged as "Stored" or "Dynamic Calc." to specify which method is to be used. The second method (stored calculations) uses a formula against each calculated dimension member -- when Essbase calculates that member, the result is stored against that member just like a data value. The third method (dynamic calculation) is specified in exactly the same format as stored calculations, but are calculated when a value addressed by that member is accessed by a user, and are not stored. The fourth method (calculation scripts) uses a procedural programming language specific to the Essbase calculation engine. This type of calculation may act upon any data value in
the hypercube, and can therefore be used to perform calculations that cannot be expressed as a simple formula. A calculation script must also be executed to trigger the calculation of aggregated values or stored calculations as described above -- a built-in calculation script (called the "default calculation") can be used to execute this type of calculation.
[edit] Aggregate Storage (Enterprise Analytics) Although Block Storage effectively minimizes storage requirements without impacting retrieval time, it is limited by its treatment of aggregate data in large applications, motivating the introduction of a second storage engine, named Aggregate Storage Option (Essbase ASO) or more recently, Enterprise Analytics. This storage option makes the database behave much more similarly to OLAP databases like SQL Server Analysis Services. Following a data load, Essbase ASO does not store any aggregate values, but instead calculates them on demand. For large databases, where the time required to generate these values is inconvenient, the database can materialize one or more aggregate "views", made up of one aggregate level from each dimension (for example, the database may calculate all combinations of the fifth generation of Product with the third generation of Customer), and these views are then used to generate other aggregate values where possible. This process can be partially automated, where the administrator specifies the amount of disk space that may be used, and the database generates views according to actual usage. The major drawback of this approach is that the cube cannot be treated for calculation purposes as a single large hypercube, because aggregate values cannot be directly controlled, so write-back from front-end tools is limited, and complex calculations that cannot be expressed as MDX expressions are not possible.
[edit] Calculation Engine Calculations in Essbase ASO can be specified as: • •
the aggregation of values through dimensional hierarchies; or dynamically calculated dimension members.
The first method (dimension aggregation) is basically the same as for Essbase BSO. The second method (dynamic calculations) evaluates MDX expressions against dimension members.
[edit] User Interface
The most widely known user interface to Essbase is an add-in for Microsoft Excel (previously also Lotus 1-2-3). The add-in adds a menu to the spreadsheet application that can be used to connect to Essbase databases, retrieve data, and navigate the cube's dimensions ("Zoom in", "Pivot", etc).[2] With the release of System 9, Hyperion provided a new user interface add-in for Essbase called "SmartView for Microsoft Office". SmartView provides access to Essbase and other System 9 content for Microsoft Powerpoint, Microsoft Word, Microsoft Outlook as well as supplanting the previous add-in for Microsoft Excel. In 2005, Hyperion began to offer a visualization tool called Tableau under the name "Hyperion Visual Explorer".[3] Tableau was originally developed at Stanford University as a government-sponsored research project to investigate new ways for users to interact with relational and OLAP databases. Other user-facing applications with support for Essbase databases are: • • • • • • •
Hyperion Analyzer (aka Hyperion System 9 BI+ Web Analysis) Hyperion Reports (aka Hyperion System 9 BI+ Financial Reporting) Hyperion Enterprise Reporting Hyperion Intelligence (aka Hyperion System 9 BI+ Interactive Reporting) Hyperion SQR (aka Hyperion System 9 BI+ Production Reporting) Alphablox Arcplan dynaSight (aka Arcplan Enterprise)
The previous offerings from Hyperion are offered with new names as given below: Hyperion's previous offerings Hyperion System 9 BI+ offerings Hyperion Essbase ASO Enterprise Analytics Hyperion Essbase BSO Essbase Analytics Hyperion Analyzer Web Analysis Hyperion Reports Financial Reporting Hyperion Intelligence Interactive Reporting Hyperion SQR Production Reporting Hyperion Metrics Builder Enterprise Metrics An API is available for C, Visual Basic and Java, and embedded scripting support is available for Perl. The standardised XML for Analysis protocol can be used to query Essbase data sources using the MDX language.
[edit] Administrative Interface A number of standard interfaces are provided for the administration of Essbase applications:
• •
• •
•
ESSCMD, the original command line interface for administration commands; MaxL, a "multi-dimensional database access language" which provides both a superset of ESSCMD commands, but with a syntax more akin to SQL, as well as support for MDX queries; Essbase Application Manager, the original Microsoft Windows GUI administration client, compatible with versions of Essbase before 7.0; and Essbase Administration Services, later renamed Analytic Administration Services, and then back to 'Essbase Administration Services' in v. 9.3.1, the currentlysupported GUI administration client. Essbase Integration Server for maintaining the structure and content of Essbase databases based on data models derived from relational or file-based data sources.
[edit] References http://download.oracle.com/docs/cd/E10530_01/doc/index.htm 1. ^ a b Earle, Robert J. (1992) "Method and apparatus for storing and retrieving multi-dimensional data in computer memory". United States Patent 5,359,724 assigned to Arbor Software Corporation. 2. ^ Hyperion Solutions Corporation (2006). Essbase Database Administrator's Guide. 3. ^ Tableau Software (2005). Tableau Software Lands Global OEM Deal with Hyperion. Press release.
[edit] External links •
Hyperion at Oracle
Retrieved from "http://en.wikipedia.org/wiki/Essbase" Categories: OLAP
Related Documents
Essbase General
November 2019 24
Essbase Intro
May 2020 13
Faq Essbase
June 2020 7
Essbase Optimization
May 2020 14
Essbase Mdx
July 2020 4