iMinds Business Analytics Solution
iMinds collaborates with research partners to convert digital knowhow into real-life products and services that change people's lives for the better. As a business incubator, iMinds also guides researchers, young entrepreneurs and start-ups in the successful market introduction of their ideas.
iMinds takes lots of initiatives to help starters. It is however not always clear which initiatives lead to which results (new starting companies, scientific publications, relevant research results, additional employment, partnerships ...).
This lack of insight originates because data is managed in different operational systems. There is no possibility to easily "combine this data in accessible dashboards. These systems include:
- ERP: EVI (Unit4Agresso)
- Publication Software: 4DVision, custom made
- CRM: SalesForce.com
- Doctorates, other important financial data: mostly Excel based
Next to this, iMinds wants to give insight to the board and the business unit manager into different aspects of the iMinds projects such as:
- Key Performance Indicators (KPIs) & dashboards
o Corporate KPI reporting (KPI's concerning revenue, number of partnerships, evolutions over time...)
o Departmental KPI reporting
KPI's regarding number of staff members, researchers, publications, start-ups, partnerships, doctorates
These KPI's determine how next year's budget will be divided among the research groups/ departments
- Project Financing
- Geographic spreading of project partners
- Human Resources (In- and Out-Flow)
- Portfolio Management of companies coming from an iStart-project
o Financial Information of the starter
o Overview of the investments (e.g. stockholders, partners)
o Overview of the affiliates (Board of Directors...)
o Which are the preferred partners?
o Which are the recurring partners?
o Which - by iMinds anticipated - facility is being used by who (office space)?
In 2013 management took the decision to start a strategic reflection about how to deal with management information in a more efficient way.
The endless manual joining of information in Excel-sheets, with all consequences that will entail, was to be tackled in a professional manner. element61 was included in this reflection to contain the Business Analytics concepts better, to explore the different technologies on the market and to list the ingredients of a successful project, including a strong involvement of the internal teams in the chosen solution.
A Business Intelligence project was started with selection of a BI front end solution and a data warehouse layer. This data warehouse layer was being built using timeXtender. timeXtender is a so called "data warehouse automation technology which builds, maintains and adjusts the data warehouse layer in a strongly simplified manner. It allows users without a thorough SQL Server- database knowledge to maintain the system themselves.
iMinds chose next to this for Tableau as a Business Analytics front-end. This solution offers the possibility to tap into this newly created data warehouse-database. In a transitional period, there was also the possibility to use separate Excels and flat files as a source.
The creation of a data warehouse layer was a must to realize the following objectives:
- Dont burden the transactional systems with sometimes heavy database-questions
- Combination and alignment of data from several sources
o ERP, CRM, Publications, Excels
o To couple several entities which appear in several sources (e.g. research group, partner...), SQL Server 2012 Master Data Services (MDS) was used. In this, master- and mapping- tables are managed in a user-friendly Excel environment, but data is stored in a database
- Enrichment of the Master data for analytical goals such as
o Geographical coordinates for postal codes, cities and countries
o KPI goals
o Historical financial information which is not included in the ERP
- Data quality control
o During the ETL-process a check is made whether data is complete, correct and consistent
To give users access to this information a number of star-schemas were built and publicized on the Tableau Server, which was afterwards used in the Tableau Client to build analyses and dashboards.
These star-schemas make sure that:
- Users dont require knowledge of SQL databases and concepts like "joins
- Every item is accounted for only once
- A number of calculations are defined in advance
Business Intelligence was new for iMinds, so there were no restrictions from the past regarding used technology. The only requisite was that the solution needed to be scalable and could support an "agile development method. This "agile manner made sure that work could be done in frequent iterations, in which not all information requirements needed to be complete and detailed before starting. Microsoft SQL Server was however used in the organization.
Therefore the following technical architecture was chosen:
- Database: Microsoft SQL Server 2012
- ETL & data warehouse automation-solution: timeXtender 2014
o timeXtender is a solution which works on top of SQL Server to facilitate the ETL-process. The solution includes "out of the box:
Project Repository (version-control, metadata)
Full documentation of the designed data warehouse
Graphical / Drag & Drop user interface
Support of an agile-development methodology
- Mapping and extra data sources: Microsoft SQL Server Master Data Services 2012
- Front-end: Tableau 8 Client and Server (soon a upgrade to Tableau 9 is foreseen)
element61 was involved in the iMinds project for the following roles and activities, in close cooperation with the BICC, finance & ICT:
- Introduction of Business Intelligence terminology & concepts
- Guidance in taking the right choices with regard to the BI-architecture and retained solutions
- Installation and guidance of the installation of the numerous tools (e.g. Microsoft SQL Server 2012, timeXtender, Master Data Services 2012)
- SQL Server & Master Data Services expertise
- Design and development of the data warehouse by timeXtender certified consultants
- Design, development and unlocking of the "stars on the Tableau Server
- Guidance in usage of Tableau (Server and Client)
Since the first presentation of the KPIs based on the new BI implementation the reactions of the public were estatic: finally the KPIs are no longer "black box, but the board members or business unit managers can see for themselves what the underlying numbers are. They can also see the impact of certain decisions. The solution has been used for more than a year by now and is still being expanded with new data sources and additional dashboards.
For many users its a revelation to see which transparent and re-integrated information is at their fingertips thanks to the strategic vision and investment in the building of the data warehouse with the adjoining Business Intelligence technology.