Open Finance Laboratory OLAP Business Reporting

About OLAP

Here is brief explanation of OLAP from The OLAP Council [Ref: http://www.olapcouncil.org/research/glossaryly.htm#PAGE DISPLAY]

On-Line Analytical Processing (OLAP) is a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user.

OLAP functionality is characterized by dynamic multi-dimensional analysis of consolidated enterprise data supporting end user analytical and navigational activities including:

- Calculations and modeling applied across dimensions, through hierarchies and/or across members

- Trend analysis over sequential time periods

- Slicing subsets for on-screen viewing

- Drill-down to deeper levels of consolidation

- Reach-through to underlying detail data

- Rotation to new dimensional comparisons in the viewing area

OLAP is implemented in a multi-user client/server mode and offers consistently rapid response to queries, regardless of database size and complexity. OLAP helps the user synthesize enterprise information through comparative, personalized viewing, as well as through analysis of historical and projected data in various "what-if" data model scenarios. This is achieved through use of an OLAP Server.

Business Reporting Terminology and Data Model

Data processing application operates on facts, like banking accounts or trading positions. Facts can be linked to dimensions from business perspectives; for instance trading position can be associated with dimension “currency” or dimension “geography”. Dimensions can be arranged hierarchically, like “branch-city-country-region”.

Facts and dimensions are stored in operational database; the latter one can be any commercial database or data warehouse. Reporting definition data can reside either in the same, operational database or in separate, reporting database. Using separate reporting database (linked to operational database for getting access to facts) may have advantage in heterogeneous database environment for reporting process unification purposes. Reporting database operates with business fact attributes; mapping vendor or application specific dimensions onto the business facts is arranged by means of glue layer.

Business Attribute

Stores business model attributes with respect to their hierarchies, business dimensions (groups), and reference to a corresponding Glue Object.

Layout

Stores collection of reporting layouts that consist of business attributes with respect to their column and row arrangements.

Filter

Stores collection of reporting filters that consists of business attributes, attribute values, and qualifiers with respect to reporting criteria.

Metric

Stores reporting expressions (functions) and links to business facts; will be used to calculate/aggregate client’s data with respect to associated layout.

Fact

Stores references to business facts (amounts, etc.) with respect to its link to business dimensions.

Report

Stores report templates defined as a combination of specific layout, metric, and filter.

Folder

Stores report categories

Report, therefore, can be viewed as functions (Metric) on subset of facts (selected by Filter) aggregated cross-specified business attributes (Layout).

OFL OLAP Components

The heart of OLAP processing is OLAP Engine [OLAP Server, using The OLAP Council terminology, see sect. “About OLAP”]. OLAP Engine generates sophisticated SQL query to have aggregated data cross layouts given filtering criteria. Since Engine relies on relational database server to aggregate numbers, this type of OLAP Engine is usually referred as relational OLAP, or ROLAP. [Some database vendors allow extending the list of SQL-92 aggregate functions by so-called user-defined aggregate functions.]

OFL OLAP Engine provides developers with the small, intuitive set of Java APIs for running reports.

Another component is UI; the component employs grids for representing totals cross rows, cross columns, and super-totals. Drill-down is conducted by row, by column, or by grid cell. Drill-down operation is implemented by applying additional filtering criteria to existing report, and by adding extra layout dimension (if one is available in business dimensions hierarchy) to selected grid element(s).

What is special about OFL OLAP Business Reporting?

Consider the features below:

- Works with virtually any database, great extent of portability and customization;

- Small footprint, both on database and code base levels;

- Valuable addition to open-source and Java-oriented databases;

- Small set of OLAP Engine Java APIs;

- Client-Server model with database server being accessed from anywhere though JDBC;

- Architecture designed to build customized configurations of

o [Custom UI + OFL OLAP Engine], or

o [OFL OLAP UI + Custom OLAP Engine];

- Service contract per installation; NO server-side licensing fees;

- Summarizes years of experience in financial applications reporting;

- Planned development: Web services implementation;

- Optionally can be packaged with Open Finance Laboratory Database as specialized OLAP portfolio / risk reporting.

Business niche

OFL OLAP Business reporting can be recommended for:

- Small or medium size projects, where the low cost of deployment and maintenance is a key value;

- Dynamic business environments where customization is a key value;

- Open-source, light-weighted, and Java-oriented databases;

- Light-weighted Analytical Services Reporting Platform.

Use Cases

Risk Reconciliation Reporting. Client risk management system produces risk sensitivities report for a given portfolio. OFL Reporting adds values to risk reporting by providing client with detailed views on risk numbers with respect to security type, currency, tenors, trading desk, branch, etc.
Financial Application Metrics. OFL Reporting provides system administrators with detailed views on system metrics, including portfolio processing performance summary, portfolio performance as a function of a number of parallel tasks, performance as a function of security types, and other metrics.

OLAP Products Comparison Matrix

Feature	Crystal Analysis Professional	Business Objects	Cognos PowerPlay	Microsoft Analysis Services	Micro Strategy	OFL
Architecture	OLAP Client, Supports variety of OLAP data sources	OLAP Client, Supports variety of OLAP data sources	Server: MOLAP Cognos “PowerCubes”, Supports variety of OLAP data sources	Server: ROLAP, MOLAP, Hybrid OLAP Client: PivotTable Service	ROLAP (a) Intelligent Cubes (b) MDX Adapter	ROLAP
Databases / Database APIs	ODBC-compliant	ODBC-compliant; With “Business Query”: Specialized MS SQL Server support	With “Transformer”: Any relational data source	OLE DB for OLAP	Oracle 9i, IBM DB2, Teradata, Others	JDBC-compliant
APIs		VBA	XML	VBA, C++, COM	Java, COM, XML	Java
Integration	MS Office, IBM DB2 OLAP Server, Hyperion Essbase, SAP	MS Office, With “OLAP Access”: IBM DB2 OLAP Server, Hyperion Essbase, SAP	MS Office, Microsoft Analysis Services, IBM DB2 OLAP Server, Hyperion Essbase, SAP	MS Office	MS Office	Export / Import in CSV-format
Platform	Windows	Windows, UNIX	Client: Windows Server: Windows, UNIX	Windows	Windows, UNIX	Windows, Linux
Market sector specialization	N/A	N/A	Manufacturing, Retail, Financial Services, etc.	Various	Retail, Financial services, others	Financial Services
Licensing and pricing	1 user - $395 5 users – $1,876	N/A	N/A	Microsoft® SQL Server 2000 Enterprise Edition, 1 Processor License – 19,999; Standard Edition, 1 Processor License – 4,999	N/A	Service contract per installation - $69/year

References

www.olapreport.com

www.essbase.com

http://www.crystaldecisions.com

http://www.businessobjects.com

http://www.microstrategy.com

www.cognos.com

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agintroducing_8pfb.asp

www.Olap.com

www.xlcubed.com

http://sourceforge.net/projects/mondrian

Standards

www.xmla.org

http://jcp.org/en/jsr/detail?id=069