One click integration: OLAP/RDBMS/Online Service

A requirement that is very often communicated to us in client projects these days is “Data mash up”. The ability to integrate internal and external data sources more effectively and enabling more holistic insights.

One of the key challenges in the setup of analytical application processes is the side by side use of relational databases often referred to as the “data warehouse” and multidimensional data stores.

The data warehouse architecture is typically “data driven“ or in other words it typically involves integrating existing data from different sources. This architecture is suited for flexible analysis and often requires specific technical expertise by the analyst.

The other approach is “concept driven” i.e. enabling the analyst to enter data, conduct what if analyses and to modify structures (business modelling). This mostly involves multi-dimensional (also referred to as OLAP) databases that are optimised for use by non IT specialist business users. Often an “in memory” approach is used in this architecture that enables extremely fast response times when calculating query results across 3-20 dimensions with multiple aggregation hierarchies.

None of the above concepts is “better than the other” the use case typically depends on the requirements at hand and in most cases Managility recommends a combined approach that involves both architectures.

In recent times a third aspect to consider is the integration of commercial web based data services for example provided in the Azure Data Market or by other third party providers. This enables organisations to put their data in “perspective” for example by comparing it to temperature, population or financial markets details.

Historically the “keeping in sync” of these different “data worlds” required extensive development work of ETL (Extraction, Transformation and Loading) processes.

To address the integration challenges Managility has recently developed a new approach that synchronises OLAP and relational sources: The PowerSync framework a solution that we provide to our clients free of charge. PowerSync automatically converts Jedox cubes into a relational dimensional model with fact and dimension tables.

The benefits are much improved audit options (every OLAP transaction is a relational record than can be easily traced) and a comprehensive view on the data. The combined sources can now be analysed with existing data warehouse reporting solutions or any standard BI tool that supports relational access.

In addition Managility Power Sync supports new technologies like PowerPivot, a component that enables analysis of large data volume sources in Excel and the easy integration of external sources. As per the example below the Jedox Sales demo can be easily extended with population information to enable a relative metric measuring sales per population.

At the moment we have used the approach at a few early adoption clients with very good feedback. Please don’t hesitate to contact the Managility team or me for any further questions or to setup a demo with your data.



Over the years I have lost count how many times I have heard the mantra “Excel is just a toy you can’t use for serious Business Intelligence”. Typically from “IT experts” that never had to work with a BI solution from an end user perspective.

In part this statement is true: Excel on its own is and was never designed to be a store for large data volumes required for BI purposes. An area for which it is still often misused in poorly designed spreadsheet convolutes.With the right analytical technology by its side though, this tool is still one of the most flexible BI front ends on the planet. Not to mention that there is a pretty knowledgeable base of an estimated 300m users around, that don’t have to be trained in yet another standalone BI client.

From the experience of 15 years in this industry and more than a hundred BI projects I can pretty confidently say, that for most business intelligence use cases, users will likely interact with a spreadsheet at some stage of the process. The notion that Excel has an important role to play and should be considered as part of a professional business intelligence solution is by now –despite initially different opinions- also shared by Gartner and other analysts (More here and here)

Separation of data storage and presentation

The crucial factor is to clearly define the boundaries of how Excel is used. Anyone working in this area will be familiar with the typical “Excel Hell” scenario: a convolute of unmanageable workbooks that are error prone and don’t really offer useful insight.The key to avoid these issues in a business intelligence context is in our opinion the separation of data storage and presentation layer: data should not be pasted directly from a data source (or even worse rekeyed) into a spreadsheet but made interactively accessible from consistent, “single version of the truth” data mart(s) that also include as much as possible key parts of the logic (especially calculations).

Complementary Excel Business Intelligence add-ins like Jedox, TM1 and most recently Power BI (xVelocity engine) exactly address this problem by providing central, server based data storage technologies optimised for analytic purposes and governed by professional multi user management capabilities. For example enabling that on the same report (or Excel spreadsheet) specific users can only view or change data cells (e.g. by account, department, etc.) based on their credentials (an area where Excel on its own falls crucially short). At Managility we have worked with all of these and believe they are great enablers of tremendously useful business intelligence solutions that typically cater for most analytical application requirements that involve “power users” in their familiar spreadsheet environment. Each one of these solutions have particular strengths and weaknesses and need to be evaluated based on the specific customer requirements. Of course there will always be use cases where a more controlled approach for non-Excel users is required but these days the above mentioned solutions offer extensions that enable publishing data as protected, interactive web applications or on mobile devices.

Exce(l)ptional capabilities added since 2010

The capabilities of Excel as a “serious BI frontend” have dramatically increased in recent times. Particularly since version 2010 new “game changing” elements -from a business intelligence point of view- have been added. Most important to mention here is “PowerPivot” and subsequently Power BI. A free Excel add-in -respectively with Power BI a separate desktop tool- developed by Microsoft that works in conjunction with Excel version from 2010.
PowerPivot/ Power BI provide the modelling environment to generate and use data models that are deployed using the Microsoft xVelocity in-memory technology. The key benefits of the technology are its ability to process staggering amounts of data (there are many reports from people analysing 100m records on their laptops) with lighting fast response times. In addition to better processing the other key focus is on data integration.
The enabler for this is Microsoft’s new BISM (Business Intelligence Semantic Model) a framework to better integrate different sources and data store technologies beyond the boundaries of internally produced data. For example by adding weather information from an external provider to analyse effects on internal sales data. With Excel version 2013 Microsoft has also added additional business intelligence features like drill down and drill across in PivotTables (a feature that also works with any complementary OLAP technology that supports ODBO) as well as amazing geographic analysis with Power Map (formerly GeoFlow) Excel Add-in. In this video we take a initial look in conjunction with randomised parking meter data from the City of Sydney. The vision, supported by great demos, is that this process is driven entirely by business end users. Here we are a bit less confident. Despite significant advances, we believe that this can work in limited areas but caution is required. Data integration particular across different sources will always be a complex and challenging subject that will likely require specific technical domain expertise.

The Best of both worlds

Microsoft’s business intelligence back end engines (like Power BI, Power Pivot and Analysis Services) are focused on read only analysis. In our client projects we have developed a variety of approaches that address this issue particularly with utilising memory based processes to handle extensive write back processes (e.g. data entry and simulation on aggregated levels e.g. all products and distribution according to typical in budgeting scenarios.
Please don’t hesitate to contact us to arrange a hands on experience of the new options in one of our Fast Start Workshops or the new Advanced Business Intelligence with Excel workshop. All in all, the improvements in Excel are definitely a huge leap forward and will pose a substantial threat to currently very popular, read only analysis tools like Tableau and Qlikview whose price tag will be closely analysed by organisations who have already invested in Microsoft licenses.



In this post I plan to share a trick in Jedox spreadsheet to lay out a dynamic formula driven calendar based on your input month and year value the month laid out calendar changes itself according to the week days monthly template is laid out.

The calendar is driven via another sheet in the wss, where the formulas are laid out for the monthly template to look upon according to the week day index’s.

The inspiration for me to build this calendar is partially a project requirement along with support from the following blog post laying out the same for excel.

This is also a clear representation of the power of Jedox in  terms of bringing excel to web, in its complete essence.

Download the attached wss file for yourself’s to review and feel free to ask me any relevant questions relating to the same.

Enjoy !!!