Warehouse White Paper
The completion of the Envera BI Platform.
Seecago was approached by Envera to assist in creating the next generation intelligence platform. One of Envera’s main concerns was that their current platform was outdated and mainly focused upon the basic report creation generated directly from a transactional database. This caused a delay in performance speed, limiting their options for designing reports summarizing long term data.
Seecago already has a history understanding their domain and reporting needs, so this expert personal specialization is perfect to allow Envera to reach the next step.
Envera requested the following from Seecago:
- Design a data warehouse using their database platform MySql
- Create an ETL (Extract Transform Load) platform
- Provide guidance to replace their current reporting tools: Birt and Crystal Reports
- Deliver a proof of concept using different BI vendors such as Tableau, Microstrategy, Microsoft, IBM
- Build the BI foundation on Envera’s final choice of vendor
- Provide training to the Envera team.
These requests were completed within the previous week. These are the results of the requested steps:
Warehouse
Envera’s data warehouse stores dimensions and facts based on their day to day event processing of gated communities. Events are processed by operators, talking to guests at a gate having the intention to visit a resident of that community. Basic reports are based on gate volume, number of visits at a gate, type of visitors (like scheduled, unscheduled), handling time of the visitor (wait time, handling time).
Figure 1 from above illustrates a simplified part of the entire warehouse. Using this design the analyst is able to dice and slice the warehouse data into different cubes, which allows them to look at traffic data by date, community by date, residents, and other information that may be needed.
Depending on the data needed by the different Envera departments, the gate traffic can be analyzed based on those needs: a few examples would be that the operation site of Envera is interested in day to day operator performance and traffic predications, the accounting and sales department are interested in efficiency of visitor handling on a monthly base. The warehouse includes aggregated data collection by months and operator shifts to support the month and weekly visitor handling reports.
ETL – Extract Transform and Load
The warehouse gets populated by an ETL system designed using Pentaho, a data integration and analytics platform. Using this platform Seecago built two major transformation jobs that are running hourly from the Pentaho Intelligence Server. All the job and transformation activities are logged. The jobs are also monitored by Nagios: Envera IT network monitoring tool to collect all significant day to day networking information. To be able to monitor the entire transformation is a huge priority. To have quality data in the warehouse is the most important requirement for data intelligence.
All the Envera Pentaho transformations follow the same flow: Get data from replicated database server, transform that data, check for data quality issues, log the artifacts, store the clean data into the warehouse.
Seecago was successfully able to show Envera the significant performance reporting improvements they gained using the warehouse, even with using their current tools. Reports they were unable to run before took now only a few seconds. Upgrading their reporting tool in the future would even enhance their positive experience for certain.
Vendor Selection
In the vendor evaluation of the next reporting tool, Seecago viewed the basic requirements: price, ease of report generation, reports are online (cloud based), able to integrate with all tools used by Envera like: MySql, Salesforce and Quickbooks.
After evaluation and showing a proof of concept using the warehouse, Envera decided to sign a contract with Microstrategy.
Microstrategy
Microstrategy is a leading provider of business intelligence tools. Tools varying from the creation of powerful dashboards to data modeling. Microstrategy platform provides everything that Envera needs now and in the future to support their business intelligence activities. Enver is able to design powerful graphical dossiers, strategic reports and use them for business intelligence. Using the Microstrategy Developer and the basic statistical tools, Envera is able to run different models using their data to be used for predictions or classifications. Neural Networks can advance their models using Microstrategy build-in integration with tools like R and Python.
Envera is now able to use centralized governed data to make quick operational – and long term strategic decisions. This will allow Envera to progress into the future.
If you want to learn more about this project or business intelligence, please reach out to Seecago at any time, find our contact details here.
