a meta language for mdx queries in e log business

A meta-language for MDX queries in eLog Business Solution

Sonia Bergamaschi*, Matteo Interlandi*, Mario Longo°,

Laura Po*, Maurizio Vincini*

*Department of Computer Engineering - University of Modena & Reggio Emilia

°eBilling S.p.A.

28th IEEE International Conference on Data Engineering (ICDE) Washington, DC, USA, April 1-5, 2012

Laura Po "A meta-language for MDX queries in eLog Business Solution"

eLog• eLog, a business intelligence solution re-designed and

upgraded jointly with the database group of the University of Modena and Reggio Emilia and eBilling

• eBilling - an Italian SME supplier of solutions for the design, production and automation of documentary processes for top Italian companies.

• Over 140 medium and large companies• Sky, Mediaset, H3G, Tim Brazil • In a period of 18 months 150 millions mail pieces = 1 TB of

data


BCPortal• The BCPortal® framework core consists of several modules

for generating, delivering, managing company business communications.


eLog

Shipment Processing & Delivery

SystemDocument generation

system

Document Reception

System

Document Formatting

System

Document DeliverySystem

FRONT END

BACK END

eLogClient Service Provider

• KPIs (Key Performance Indicators) • SLAs (Service Level Agreements).


Toward a multidimensional approach

• MDX is very powerful and effective but unfeasible for unskilled users

transactional system

semi-static reporting: an intermediate approach between static reporting and OLAP

the decision maker - is autonomous in the process of creating, managing and sharing new analysis- can intuitively define new indicators and aggregate functions

eLog - the new version

multidimensional system

Meta-language


Key features• Simple GUI - all the complexities are masked by the underlying

system.• Data conceptualization - a set of XSD schemas (for defining

mapping between cube attributes and visualized attributes) decouple the GUI and the OLAP server

• Modular and reusable software – MDXGenerator is a stand-alone java library, OLAP engine independent.


GUI

Predefined and custom attributes

Save and load functionalities

4 quadrants the user fills with attributes in order to createthe desired query


Data Conceptualization• Each pivot configuration is codified in a set of XML files that

will be the input of the MDXGenerator component.

• Each XML configuration conforms to an high level conceptualization implemented in a XML Schema (XSD)

• 9 XSD schemas, each of which models a particular part of the pivot configuration.


MDX Generator• A standalone library that provides functionalities for

automatic multidimensional queries generation starting from a XML pivot configuration.

• It is also responsible for • the syntactic verification of the pivot configuration • the semantic correctness of the MDX expression


Syntactic verification • syntactic verification of the pivot

configuration • the input is verified to be

syntactically well formed,• it is divided in subsections of

interest and parsed,• six different structures are filled

with the elements extracted from the pivot configuration

• Data Cubes • Data Attributes• 4 structure, one for each quadrant.


Semantic correctness• semantic correctness of the MDX expression

• For every custom attribute, it performs on-the-fly translation of the meta-language expression into MDX.

• It verifies that the elements contained in the configuration can be mapped into the data schema on the OLAP server.


MDX query• MDXGenerator starts the composition of the final

query: a java object containing strings for each section of the MDX query

It is filled wrapping the information from the 6 structures with MDX language constructs

SELECT

FROM

WHEREIt contains a list of cubes – then, the Cube Selection algorithm choses the best cube and consequently reassembles the query


Meta-language & custom attributes definition• a simplified version of the MDX language both in semantics

and syntax• the requirements were defined by eBilling after an analysis

conducted on the KPIs.• multiple versions of the meta-language for the different

localizations The main features are:• a list of functions to combine and manage attributes

values;• the hierarchy management, to create new members inside a

hierarchy or new hierarchies embracing a subset of members in a hierarchy


Number, Constant, MDX Entity, Expression

• Number • Constant - any sequence of characters and (potentially)

numbers• MDX Entity - any member belonging to a dimension inside a

cube• Expression can be of three types:

• Arithmetic Expression arithmetic operators (“+”, “-”, “*”, “/”)• Comparison Expression relational operators (“ >”, “ <”, “ =”, “

>=”, “ <=” “ <>”)• Logic Expression logic operands separated by “AND”, “OR”,

“XOR” and “NOT” operators.


Functions• The functions that have been implemented in order to

combine and manage values within the measures (and dimensions) are the following:

• format, if, concat, contains, current, previous, following• create hierarchy


Example

This Dimensional Fact Model represents the monitoring activity of the SLA related to some mail pieces, called missives.


Example – new member inside a hierarchy

• Query : for the first and the second quarter of each year, compute for every client and for the cities of Naples and Rome, the number of missives in SLA and the total number of missives that are both in SLA and out SLA.


Define a new custom attribute

XML file


Define the pivot configuration

• Query : for the first and the second quarter of each year, compute for every client and for the cities of Naples and Rome, the number of missives in SLA and the total number of missives that are both in SLA and out SLA.

MDXquery


Moreover…To improve time-performance:• Cache – MDXGenerator

• Already parsed configurations are stored in a cache• If a pivot configuration is stored in memory, the data can be

retrieved directly from the cache, thus decreasing the query generation time

• Cube Selection• The Cube selection is an algorithm that chooses among all the

cubes contained in the pivot configuration the optimal one for the execution of the query

• The algorithm has been designed to be as general as possible and completely independent on the input cubes.


Test• 15 typical queries from a minimum of 3 dimensions to a maximum

of 8 different dimensions (a typical pivot analysis)• 8.974.662 facts (related to 8 months of eLog’s data production) -

Btree index for the foreign keys on the fact table• concurrent access for up to 25 users

Exploiting the cache outperforms by 7 times the performance of the MDXGenerator


Conclusion• New version of eLog: the business intelligence solution of

eBilling for document management traceability, optimization and analysis.

transactional system multidimensional system

MDX Generator – a stand alone library

Data model conceptualization: XSD schemas } middle layer between the framework and the OLAP engine

Customized attributes – Meta-language

Cache (pivot configurations)

Cube Selection algorithm

optimization of the query}


Future Work• To extend the compliance of MDX language also for

Microsoft SQL Server (so far, it is compliant with Mondrian system).

• More experiments on the use of the eLog system on customer data.

• To investigate how MDXGenerator can be enhanced to improve the query rewriting for optimizing the query processing


Thanks for your attention• Contacts:

• just type «Laura Po» at Google.com• [email protected]

• DBGROUP• www.dbgroup.unimo.it

• Ebilling• www.ebilling.it

• Download this presentation:• http://www.dbgroup.unimo.it/~po/pubs.html• or http://db.tt/1aJsuTJo

mailto:[email protected]

http://www.dbgroup.unimo.it/

http://www.ebilling.it/

http://www.dbgroup.unimo.it/~po/pubs.html

http://www.dbgroup.unimo.it/~po/pubs.html

http://db.tt/1aJsuTJo




Cube Selection• The query object created by the MDXGenerator contains in

the FROM field all the cubes that might be used.

• The Cube selection is an algorithm that chooses among all the cubes contained in the pivot configuration the optimal one for the execution of the query

• The algorithm has been designed to be as general as possible and completely independent on the input cubes. • it is not mandatory that the input cubes share the same

semantic structure• in eLog each of the input cubes takes into consideration

different portion of the schema containing data at different granularity


Cube SelectionGiven• S(C,D) schema • C the set containing all the cubes • D the set of all dimensions• Q the query derived from the pivot table configuration,• L the set of all the distinct levels belonging to at least one

dimension contained in the set D we define• QL’ the query that involves the non empty set L’ of hierarchical

levels of dimensions, where L’ L and CL[l] the not empty subset of C containing the cubes where the dimension of the level l L is used.

• |c| the number of dimensions in the cube c • co the cube containing all the levels with lowest granularity.


Semantic correctness• semantic correctness of the MDX expression

• For every custom attribute, it performs on-the-fly translation of the meta-language expression into MDX.

• During the meta-language translation, the solve order sequence is automatically defined following the position in the quadrant, chosen by the user

• It verifies that the elements contained in the configuration can be mapped into the data schema on the OLAP server.

a meta language for mdx queries in e log business

Technology

mdx queries

elog elog

metalanguage expression

mdx generator

mdx queryselectit

query laura poa metalanguage

mdx query mdxgenerator

desired querya metalanguage