Follow the 2016 Pentaho Community Meeting live!

PCM Live Blog - Use Cases & Technical Room 2

This blog will be updated throughout the day to keep everyone up-to-date about the event!

Brussels airport company – Lokke Bruyndonckx

The Brussels Airport Company is the organization running the Brussels International Airport at Zaventem, Belgium.

For all sorts of purposes, a lot of data is gathered by Brussels Airport Company. This data contains both customer data and internal data:

  • License plates for cars entering the Airport Zone
  • Boarding passes
  • Person tracking through the buildings

Internal data:

  • Financial
  • Operational
  • Timesheets
  • Sharepoint usage
  • Plane information

All this information has to be loaded into the enterprise data warehouse to allow the building of reports on top of the data warehouse tables.

The BI team of Brussels Airport Company consists of 3 business analysts, 2 ETL consultants and 7 report consultants, delivering reports for end users throughout the entire company.

The architectural setup:

Import data usually comes from Excel, csv or txt files coming from operational machines and procedures and the internal database system. This information is loaded into a staging database (Sybase ASE) and after that loaded into the data warehouse (Sybase IQ).

Because the general procedure was the same for most projects (loading data files into staging, comparing staging to existing DWH, loading into DWH), an ETL and SQL generation tool is developed, making the lives of the ETL developers a lot easier.

Based on an Excel template containing all metadata about the information that needs to be imported, a metadata injection transformation will generate SQL statements and ktr-transformation files that will be used throughout the project. Setting up these templates for the SQL and ETL generation was a lot of work, but once this setup was completed, it started saving huge amounts of time by allowing the ETL to automatically generate a lot of work for the development team.

This auto-generated SQL statements and ETL transformations are then added to the main data warehouse loading jobs to be used every day.

ABN AMRO Clearing Query Wizard – Joep Prein

Joep Prein is a self-employed business professional who has worked in the financial sector for over 15 years.

Clearing is the process where one party is buying instruments and another party is paying for it. These processes might take a couple of days, and during these days a lot of information can change based on external factors (for example stock prices can change based on political factors, financial factors, …).

ABN AMRO Clearing provides services in risk mitigation for stock exchanges around the globe. ABN AMRO Clearing is an IT driven organization, not an old fashioned bank. It is one of the largest parties in the world for clearing and works for 85 stock exchanges with around 850 people working across the world.

Pentaho is used at ABN AMRO Clearing for loading ETL to the data warehouse, and also all the reports are built in Pentaho, producing over 10000 reports and SLA-related dashbopards every day. Csv files are delivered early in the morning with the latest business information and have to be processed and loaded for producing the most accurate reports based on the last information.

Aside from loading the data warehouse with ETL and produce reports on the server, ABN AMRO Clearing also provides a customized browser GUI to retrieve information for its customers, called The Wizard.

The wizard allows users to directly query the database through a GUI that allows you to select the fields you want to display in your report and select the filters that have to be applied in your report. Based on the selected criteria, you can execute the query and the result is showed in your GUI. The result can also be exported to different file types for later use.

The wizard is now being used by 4 customers of ABN AMRO Clearing in a production environment, and the plan is to continue adding clients to increase the use of The Wizard.

Cipal Schaubroeck – Ilse Bracke

Cipal is a software manufacturing organization producing customized software for Belgian municipalities, provinces and regional governments. Aside from developing regular operational software, they also wanted to produce Business Intelligence platforms for their customers to improve business intelligence.

Pentaho is chosen as the software to develop a central BI platform. PDI is used to extract information and load it into the data warehouse. On top of the data warehouse, the BA server is accessible for end users through reports.

The platform is called Athena: a central Pentaho environment hosted in the cloud. The idea is to provide self-service reporting for different municipalities and governments with different reporting needs. Using a multi-tenant system, different clients are able to access their own ‘personal’ portal with their specific report. The different governments can request reports to be provided by Cipal, which will then be made accessible through the platform for that specific user.

The Athena project started in 2010 with social welfare data, and right now there are already different data sources available with for example orders, budgets and budget adjustments for different governments. Virtual cubes are also used to combine different cubes and allow users to report on a combination of information.

The general way of working is that on a nightly basis, the necessary information is extracted from the different source applications and are loaded through a staging database into the central data warehouse.

The future brings more clients, larger cities mostly, expecially for finance reporting. Also planned is a migration to Pentaho 7 and a potential evolution to smart city architectures. Smart city architectures will probably be the next step for Cipal, since more information is available every day and requests for smart-city like projects are increasing and a pilot project is already started in june 2016.

Using the Power of Data Blending - Marco Vala

Marco Vala is a Product Owner at Pentaho. He will talk about the ways to combine data.

There are multiple ways to combine data most people use PDI to do the trick. PDI is able to connect multiple sources, explore and inspect the data.

Can an ordinary PDI transformation be used as a data source? The answer to that question will be PDI Data Services

Data Services are built on top of a transformation and will turn that transformation into a JDBC data source. Data Services are available in de 7.0 CE version. If transformations are used as Data Service the can respond to standard SQL queries.

Marco demonstrates the data services on the newest Pentaho Server. He uses the data service on a transformation with a simple table input step. He then added the Pentaho driver, received from the data service, to two SQL interfaces and directly connect to the data. The example is very simple but the transformation can be as complex as you wish.

Smartcity Copenhagen - Joana Avelar

Joana is a webdetails consulant who presented the Smartcity Copenhagen project.

The project was to combine multiple information and communication technologies to manage and optimize city assets, namely water/energy consumption.

Joana talked about using the Pentaho dashboard component and then demonstrates the use of it in the Smartcity Copenhagen project.

Improve Mondrian MDX usability with user defined functions - Raimond Simanovskis

In his current application Raimond creates a lot of calculated members, they looked for a way to create a method to reuse and structure the calculated members. To implement Hierarchies in their MDX they created two new functions currentHierarchy and currentHierarchyMember, which results in a simpler use of the hierarchies.

They have create User Define Functions to retrieve the current time period and to parse the date. They also define a lot of date calculations for example [Time].[Month].DateMember(‘3 months ago’). All of these User Defined Functions can be found on the EazyBI Github.