dashboard and reporting tools: integration of bi tools in the geospatial domain

Dashboard and reporting tools: Integration of BI tools

in the geospatial domain

Thierry BadardCo-founder and CTO

Colloque AGMQ, Saint-Hyacinthe, October 19th, 2010.

http://www.spatialytics.com/

http://creativecommons.org/licenses/by-sa/2.5/ca/

2intégration d'outils BI en géomatique

L'incontournableen GeoBIOctober 19, 2010

About Spatialytics

1.Software editor in Geospatial BI (GeoBI)● Supports and develops its own GeoBI open source projects

(http://www.spatialytics.ORG)● Will propose a commercial edition (software and SaaS)● Works with a network of integrators / resellers

2.Solutions integrator in Quebec province● Consulting● Reseller of the OpenGeo Suite – Enterprise Edition● Professional services

Training and support Developement and integration

http://www.spatialytics.ORG/



Business Intelligence (BI)• Business Intelligence applications are usually used to

better understand historical, current and future aspects of business operations in a company.

• The applications typically offer ways to mine database- and spreadsheet-centric data, and produce graphical, table-based and other types of analytics regarding business operations.

• They support the decision process and allow to take more informed decision!



Business Intelligence (BI)



Business Intelligence (BI)● Rely on a sometimes complex architecture which

may include some robust components and applications like:– OLTP data sources (DBMS, files, sensors, ...)– ETL tools– Data warehousing (DW)– On-line Analytical Processing (OLAP) servers and

clients– Reporting tools– dashboards– Data mining



Dashboards• Is an executive information system user interface that (similar to an

automobile's dashboard) is designed to be easy to read

• Dedicated to track the flows inherent in the business processes that they monitor

• Graphically, users may see the high-level processes and then drill down into low level data (often unavailable to the senior executives)

• Provide decision makers with the input necessary to "drive" the business

• Graphical user interface displays summaries (tables), graphics (e.g. bar charts, pie charts, bullet graphs, "sparklines," etc.) and gauges (with colors similar to traffic lights) in a portal-like framework to highlight important information

• Dashboards are interactive and could be updated in (near) real time!



Dashboards - benefits

• Visual presentation of performance measures

• Ability to identify and correct negative trends

• Measure efficiencies/inefficiencies

• Ability to generate detailed reports showing new trends

• Ability to make more informed decisions based on collected business intelligence

• Align strategies and organizational goals

• Save time over running multiple reports

• Gain total visibility of all systems instantly



Reporting tools

• Reports provide thus some static snapshots in time of the performance/status of the entity you are examining

• Reporting tools automate the production of unified reports which join different views of the enterprise in one place

• They enable the querying of data sources (OLAP data cubes, Excel files, DBMS) with different logical models to produce a human readable report in different formats (pdf, word, openoffice, Excel, html, …)

• While reports can be distributed in print form or via email, they are typically accessed via a corporate intranet



Dashboard and reporting tools

• A lot of tools exist!

• Proprietary vs. open source

• E.g. Cristal reports, MicroStrategy, SAP, ...

• I will introduce the major open source tools :– JasperServer, JasperReport, iReport– BIRT– Pentaho Reporting, Dashboards and CDF



Jaspersoft

• http://www.jaspersoft.com

• Complete BI suite (ETL, OLAP server, ...)

• Open source edition includes 3 tools for reporting:

– iReport : report designer

– JasperReport : report engine

– JasperServer : report portal

• Commercial edition adds:

– a drag-and-drop dashboard designing tool

– Some flash based animated components

• 3D animated and advanced charts

• Basic flash maps with very few interactions (hyperlinks)

– Google maps API integration: just markers on the map!

http://www.jaspersoft.com/



Jaspersoft



Jaspersoft

- Demo -



Actuate• http://www.actuate.com/

• http://www.eclipse.org/birt

• BIRT (Business Intelligence and Reporting Tools) is an open source Eclipse-based reporting system which can be integrated with Java/J2EE applications

• Report editor is based on/integrated in the well-known Eclipse IDE

• Like JasperServer/Report, it enables the production of reports with minimal interaction capabilities

– Input parameters

– Static links

• What about maps support?

– It offers just a basic integration of some flash maps (as Jasper, based on FusionMaps) and of the Google Maps API

– As Jasper, just markers or clickable predefined areas

– No-drill down or roll-up, very limited thematic mapping capabilities, ...

http://www.actuate.com/

http://www.eclipse.org/birt



Actuate



Pentaho• http://www.pentaho.com

• As Jasper, Pentaho provides a complete BI suite

• Note : Jasper Analysis (OLAP Server) is Mondrian! ;-)

• As Jasper, dashboards are an exclusive feature of the commercial edition!

• Open source edition provides:

– Pentaho Reporting

• Comes with a report designer

• Reports are hosted in a portal with authentication

– Community dashboard framework (CDF)

• Design of dashboard is performed mainly programmatically

• Could be hosted in the same portal as reports and comm. dashboards

• As BIRT and Jasper, maps support is very limited!

– Basic integration of the Google Maps API with just markers

http://www.pentaho.com/



Pentaho



Interested in tools comparison?

• Some very interesting links:– Open Source Reporting Comparison - BIRT, Jasper,

Pentaho http://www.innoventsolutions.com/open-source-reporting-comparison.html

– BIRT, Jasper, Pentaho - Comparison Matrix http://www.innoventsolutions.com/birt-jasper-pentaho-comparison-matrix.html

http://www.innoventsolutions.com/open-source-reporting-comparison.html

http://www.innoventsolutions.com/birt-jasper-pentaho-comparison-matrix.html



Why do we need geo-analytical dashboard and reports?

• “About eighty percent of all data stored in corporate databases has a spatial component” [Franklin 1992]

• Enable the exploration of spatial relations between data – To take into account all aspects of data

– And then take informed decisions

• Some phenomena can only be observed and interpreted by representing them on a map!– Spatial distribution,

– Spatiotemporal evolution, etc.

• OK … but we do not just need some markers on a map. We need to have:

– Rich, complex and customizable thematic mapping functionalities

– Drill-down and roll-up capabilities to navigate into the data and correlate/analyze them at different levels of detail

– Synchronisation (or not) between tabs, diagrams and maps

– A rich and lightweight toolbox to make the existing tools geo-capable or to easily create by its own some geo-analytical dashboards and reports ...



SOLAPLayers• SOLAPLayers is a lightweight cartographic component (framework)

which enables navigation in geospatial (Spatial OLAP or SOLAP) data cubes, such as those handled by GeoMondrian.

• It aims to be integrated into existing dashboard frameworks in order to produce interactive geo-analytical dashboards.

• Such dashboards help in supporting the decision making process by including the geospatial dimension in the analysis of enterprise data.

• First version stems from a GSoC 2008 project performed under the umbrella of OSGeo.

• Licensed under BSD (client part) and EPL (server part).

• http://www.solaplayers.org

http://www.spatialytics.org/



SOLAPLayers v1

• Version 1 was based on OpenLayers and Dojo

• It allows:− the connection with a Spatial OLAP server such as

GeoMondrian,

− some basic navigation capabilities in the geospatial data cubes,

− and the cartographic representation of some measures as static or dynamic choropleth maps, maps with proportional symbols.



SOLAPLayers v1



SOLAPLayers v1

- Demo -



SOLAPLayers v1

• Version 1 was a mostly proof of concept!

• It presents important limitations:− Allows only the cartographic representation (no crosstabs or charts)

− Works only for one measure and the spatial dimension !

− Offers limited navigation capabilities in the geospatial data cubes

− Is able to connect to GeoMondrian only

− Extending the framework is difficult due to thelack of flexibility and the poor documentation of Dojo,

− Integration with other currently used geo-web and dashboard frameworks was difficult

− ...



SOLAPLayers 2.0• So, SOLAPLayers has undergone (and is still undergoing ;-) ) a

deep re-engineering!

• Version 2 is fully based on ExtJS/GeoExt (and hence OpenLayers)

− It will make its integration with other geo/web and BI/dashboard frameworks easier

− It provides some new ExtJS components dedicated to GeoBI!

− Based on the philosophy for the development of applications adopted by these geo-web frameworks, it allows an easier creation/maintenance of the produced geo-analytical dashboards!

− Like ExtJS, it supports internationalization!



SOLAPLayers 2.0 - Architecture

MDX

OLA

P4J

SOLAPJSON

Client

Authentication

Built-in or LDAPServer

Server

Native or XML/A

SOLAP Server

1




MDX

OLA

P4J

SOLAPJSON

Client

Authentication


Server

Native or XML/A

SOLAP Server

Native or XML/A

Geospatial data source(WFS, DBMS, ...)

OLAP Server

1

2




MDX

OLA

P4J

SOLAPJSON

Client

Authentication


Server

Native or XML/A

SOLAP Server

Native or XML/A


OLAP Server

Bridge architecture • Maximize what is in place in organisations• But, no Geo-MDX capabilities available!

1

2




MDX

OLA

P4J

SOLAPJSON

Client

Authentication


Server

Native or XML/A

SOLAP Server

Native or XML/A


OLAP Server


1

2

3




MDX

OLA

P4J

SOLAPJSON

Client

Authentication


Server

Native or XML/A

SOLAP Server

Native or XML/A


OLAP Server


• For simple geo-dashboards• Based on transactional data• Thematic mapping• No Geo-MDX and drill-down or roll-up capabilities!

1

2

3



SOLAPLayers 2.0 - Geo-dashboard made easy!Define the template of the dashboard in a HTML file1



SOLAPLayers 2.0 - Geo-dashboard made easy!Define the template of the dashboard in a HTML file1

Define your dashboard components in a JS file and map it to the div in the HTML file2



SOLAPLayers 2.0 - Geo-dashboard made easy!

Enjoy! ;-)

3



SOLAPLayers 2.0

- Demo -



SOLAPLayers 2.0 – Sum-up & roadmap

• As GeoExt which provides Geospatial extensions to ExtJS, SOLAPLayers provides GeoBI extensions to ExtJS

• So, to make it simple: SOLAPLayers = GeoBIExt!

• At present, it provides the main components for creating geo-analytical dashboards

– Map, crosstab, column chart, line chart, ...

– But, many more to come and to develop! ● Cube explorer, query builder, time slider/navigator, gauges, score cards, social graphs, ...

– Advanced interaction capabilities and settings will be added to each components!

– Additional thematic mapping capabilities are also required: multi-maps, ...

• We anticipate to have a first full and stable version in January 2011

• Integration with some reporting and dashboard tools presented here are in progress ... Stay tuned!



Questions?

Dr. Thierry BadardCo-founder and CTO

[email protected]

Next meetings :

mailto:[email protected]

dashboard and reporting tools: integration of bi tools in the geospatial domain

Technology