RepSy, a cutting edge decision support platform for planning and optimisation
We are living in the age of data overload. The ability to extract meaning out of data and present it lucidly, directly translates into competitive edge. That is why the wealth of information hidden in accumulated data cannot be ignored. Accordingly, cutting edge business decisions must be supported by likewise tools that are able to absorb scattered data and render it easily consumable.
In this article, we aim to describe what it took to create just such a tool. While all of the latest frenzy over data is undoubtedly exciting, let us not forget that the decisions are still in the hands of people.
What is RepSy? In short, it is an abbreviation of Reporting System. Well, this does not say too much. To understand RepSy in detail, we need to get an insight into the business it supports.
In MOL Group Downstream, Supply Chain Management Planning and Optimisation (SCM P&O) department is responsible for optimising the hydrocarbon products on different time horizons – short, mid and long term – starting from crude oil purchase, through the refinery and petrochemical plants operations, under given logistics facility environment and sales market potential.
This complex business can only be modelled with tens of thousands of equations. PIMSTM (Process Industry Modelling System), an industry standard linear programming based solution, provides just that. It maximises profit in a given environment by utilising the synergies of our common markets and transfers between refineries and petrochemical plants.
To understand and interpret the output of the optimisation in an easy way and provide solid basis for better decision making, we need a proper reporting system. This is where our story begins.
PROBLEM STATEMENT AND MOTIVATION
As mentioned in the introduction, our business and the related processes are complex. They serve various stakeholders from expert to managerial level on different planning time horizons. In this multi-player game, all parties have the same goal – to reach concrete decisions that maximise profit under given circumstances related to our assets and markets.
To understand the output of the optimisation, which is otherwise not easy to interpret, we need a reporting solution. However, AspenTech, the market leader of downstream software industry does not provide such a tool neither for PIMSTM nor for PSCPTM (Petroleum Supply Chain Planner − another modelling and optimisation tool), resorting all market players to develop one for themselves.
Such a custom-made reporting solution was developed in-house by MOL using MS Access 10 years ago. By now, the tool reached its business and technical limits. We had to act.
In response, the vision of RepSy was born: a fast and responsive system with dynamic report building capabilities and state-of-the-art visualisation, which is easy to maintain and extend. It must support market and asset separation, and be attractive for the X, Y, and Z generation.
Between a breakthrough idea and working software stands the arduous process of realisation. That is where we faced a huge problem. On average 70−80% of corporate business intelligence (BI) projects fail according to research by analyst firm Gartner. Not a promising fact. However, one year later RepSy was launched as the culmination of an on-time, on-spec, and on-budget project. What did we do differently to successfully land in the remaining 20−30%?
- In-depth project preparation (60% of total duration) that allowed ample time to understand and articulate business needs with the help of local and global teams
- Multi-round tendering process with focus on companies with balanced business and technical understanding
- Involve MOL IS Development, Infrastructure and Operation from the very start
- Dedicate a full-time business architect to act as a three-way bridge between developers, business and IS
- Apply third-party quality assurance to mitigate the risk of introducing a new technology and help us navigate through the novel iterative development process
- Preferred off-the-shelf (commercially produced) components to keep maintenance at minimum and maximise potential for extension, and
- Hold multi-round trainings for all stakeholders and a 3-week user acceptance test with 22 participants and 185 test cases.
Above all, we managed to gain trust to operate as a virtual mini start-up in a large organisation with adequate leeway to act on our own and bear the responsibility.
SOLUTION AND PROJECT SUMMARY
After the multi-round tendering process, Microsoft Power BI based system seemed to be the answer to our challenges – but why? While we examined all leading solutions based on ‘Gartner Magic Quadrant for BI and Analytics Platforms’, we had to realise that all of our stakeholders’ data wind up in Excel anyhow. So why not keep it there all the way? Contrary to the general perception of MS Excel, the 2013 version with Power BI offers a plethora of high-end self-service tools for data storage, analysis, collaboration and visualisation, such as
- MS Office 2013 Professional Plus − Excel 2013 with Power add-ins like PowerPivot, Power View, Power Map, Power Query, and Master Data Services
- Windows Server 2012 IIS 8.0
- MS SharePoint 2013 with Outlook integration
- MS SQL server 2012 R2 (SSAS, MDS, DQS) and
- Microsoft System Center Operations Manager (SCOM) 2012.
The above off-the-shelf components together with two additional custom modules developed using Microsoft .NET Framework 4.5.2 are the main pillars of RepSy (see Figure 1).
Fig. 1. RepSy architecture outline
(PSCP – Petroleum Supply Chain Planner; MDB – MS Access database; db – database; SQL – Structured Query Language; MDS – Master Data Services; DWH – data warehouse)
Instead of ploughing through every nook and cranny of the architecture in Figure 1, let us highlight the most impactful milestones in the journey of our data that drive SCM P&O processes the most and distinguish RepSy from standard BI solutions:
- MDS ensures retrospective data transparency and comparability via multi-level business-driven hierarchies, among others for drill-down capabilities serving the varying and disparate needs of our report consumers, from expert to managerial level
- RepSy Service and Tabular in-memory database enables near real-time BI for multi-user environment, i.e. simultaneous multiple data upload, data retrieval, comparison and analysis without significant performance degradation
- SharePoint 2013 Enterprise platform (with MS Outlook integration), acting as RepSy’s central hub, where users, data and reports meet and collaborate under proper access rights management and
- SCOM helps IS Help Desk and IS Operation to monitor RepSy component availability real-time and troubleshoot quickly and effectively.
REPSY IN USE
We were pondering how to best showcase the decision supporting capabilities of RepSy for a long time. First, we wanted to share some case studies from our daily operation where clear-cut reports facilitate superior business reasoning. Without long-winded explanation of our business, case studies seemed to be cumbersome to understand.
A more easy-going approach is to show how RepSy can serve various report consumers with different information needs. Who is RepSy’s target audience, after all?
PIMS users, who primarily focus on in-depth analysis, are supported by dynamic and responsive report building experience using Excel 2013 with Power add-ins. In Figure 2 we can see how our refineries’ units are to operate in the next planning cycle. Using PivotTable fields on the right side, PIMS users can quickly access any planning data at varying resolution. Please note that in all figures, business-wise sensitive information has either been blurred or randomised.
Fig. 2. Capacity report
Operative experts need targeted information to execute the accepted plan. They receive and see relevant fixed reports in Excel. On top of that, thanks to Outlook integration, the same information is available to them right in their email client (see Figure 3).
Fig. 3. Planned amounts of chemicals to sell in MOL for MOL Group companies. Of course, SLOVNAFT, INA and Global colleagues receive information relevant to them.
Managers crave high-level information that exposes areas crying out for decisions. RepSy provides just that in visually appealing dashboards using Power View for SharePoint which can be interactively viewed in a browser (see Figure 4).
Fig. 4. Biodiesel average sales prices versus FCA (free carrier) fossil refinery mark-up chart, with bubble sizes representing planned sales volumes to regions in a given country
Figure 3 clearly shows the most to the least profitable regions at refinery gates; a valuable information for any manager. These Power View reports can also be integrated interactively into PowerPoint presentations.
First of all, we would like to thank the departments, experts and managers as well as the selected developer company, Grape Solutions Plc. – full of gifted and driven people, who made it all possible.
By now, RepSy has been serving 250+ users in 4 locations for 4 months, without any major hiccup. Our users started to make better business decisions with the help of RepSy and think in terms of RepSy – in RepSy terms. The result of their day-to-day experience with the new de-facto reporting tool resulted in enhancement ideas that will facilitate their wildest analyses, further driving their never-ending odyssey for profit. They are truly the hidden stars of RepSy.
Keywords: planning and optimisation, decision support, reporting system, Microsoft, Power BI, visualisation, RepSy