07 Aug Romanian pharmacy vitalizes the activity with Business Analytics solution
For the Centrofarm chain of pharmacies, getting quick and easy managerial reports is an important goal for good business. Therefore, the company has implemented a complex solution based on Microsoft SQL Server 2012 Business Intelligence and Microsoft SharePoint Server 2013 Enterprise to increase performance in data mining, processing and analysis integrated with Office Excel and Microsoft PowerView for information viewing . The solution enables automated reporting, without support from the IT department, and soon became critical to supporting business decisions.
Business requirements. Initial situation
Pharmaceutical retail is an activity with a great dynamics, both in terms of its large portfolio of products and the sector specific: alternative suppliers, high competition, compensated recipes, etc. Therefore, a powerful analysis and reporting solution is a must. Centrofarm had a Business Intelligence (BI) solution since 2009, but it was limited to 5 users and was developed on an outdated technology that induces high costs for support. Moreover, the solution was not very easy to use by the staff in the business departments, which induced a huge pressure on the IT Department. “With the previous solution, producing and running reports was difficult, we were running a series of scripts directly on the production data base to get the data we needed to build custom reports, according to requests. Obviously, these processes consumed time and delayed the delivery of information to the beneficiaries”, says Marius Dumitrescu, Centrofarm IT Manager.
In August 2012, Centrofarm decided to change this solution and named a project team to define requirements and choose an alternative. Internal requirements demands a highly ergonomic solution to allow broad categories of users access to BI, data consolidation from multiple sources, and a high level of automation in reporting and analysis. In order to identify the right solution, Centrofarm initiated a detailed analysis of the local market offerings, and the short list included: the software table, QlikView and the Microsoft Analytics platform. After evaluating and testing all of the selected solutions, Centrofarm chose Microsoft Analytics and Crescendo as an implementing partner.
We needed a tool familiar to users, and Microsoft’s solution met these requirements – our requirements. When reporting and filtering data is done in a program like Excel 2013 it is clear that use will be easy. Complementary we also appreciated PowerView that allows interactive viewing of data directly from the browser, as well as PowerPivot that help run complex analyzes of existing data in Excel. Another distinctive element for the Microsoft SQL BI server was Power Query that combines and refines data from a variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, Azure SQL Database, sharepoint lists and also offers the ability to search for public data in sources like Wikipedia, says Marius Dumitrescu.
For choosing Crescendo as a partner mattered both the existing business relationships following previous contracts (infrastructure services, Microsoft Lync deployment, etc.), but above all the Business Analytics / Business Intelligence competencies and references presented. Crescendo is also a Microsoft Gold and Silver Partner on several technology lines.
The main business objective of the project was to extend the use of the Business Analytics solution in all departments: operational, marketing, commercial and financial, so that any decision taken within the company can be supported quickly with relevant information. The project started in February 2013 with a complex phase of necessary functional analysis, during which a test database was created for easy tracking of the results.
Centrofarm decided that the historical data used in the project should be 2 years old. ETLs (Extract-Transform-Load) have been defined for each operational line (acquisitions, sales, inventory management) for which dedicated cubes have been defined, and a fourth dimension with a data cube dedicated to consolidated analysis. After analyzing and validating data models for each cube, dedicated data extraction mechanisms were defined, which took into account the specificity of Centrofarm’s activity, especially the existence of compensated recipes and integration with SIUI (the Single Integrated Information System of the National House Health Insurance).
From a technological point of view, the solution is based on Microsoft SQL Server 2012 Business Intelligence for Data Extraction, Processing and Analysis, SharePoint Server 2013 as the Web publishing platform and Microsoft Power View, Microsoft PowerPivot, Office Excel 2010, and Office Excel 2013 as tools visualization and user interface. The Sybase 11 ASA Server transactional database of the ERP solution has been integrated into Microsoft SQL with a dedicated connector, but in the future Centrofarm will also migrate its ERP solution to a Microsoft SQL database. In addition, to manage the solution, the IT Department uses the Microsoft System Center Configuration Manager. Data Warehouse is mainly aggregated from operational applications (ERP, POS, customer loyalty), but it is possible to import data from the Internet (company website) through dedicated web services. this feature allows future development of structured and unstructured data analysis (Big Data type) by adding in Data Warehouse data from websites, social media, sensors placed in stores, etc. , Marius Dumitrescu said.
As a practical way of working, most users chose to generate reports in Excel, being a very familiar interface at Centrofarm. Excel 2013 brings native integration with PowerPivot and “data analysis expression” functions, and by connecting to a comprehensive data warehouse it can generate very complex analyzes. PowerPivot is also available as an add-in for Excel 2010, give it such a technology to a financial manager and you’ll be surprised at what you can do with it after reading a book like: Office 2013 step by step, examples of applications “said Marius Dumitrescu, IT Manager, Centrofarm.
For mobile users, Centrofarm opted to access reports and analytics on Windows 8 tablets through SharePoint.
The solution came into production in July 2013 and is currently being used by people in the Commercial, Sales and Marketing departments, as well as Top Management. In the next step, Centrofarm will extend the use of Microsoft Business Analytics at each workstation so that managers of these locations can access their own business details and performance indicators. The Business Analytics solution has allowed us to keep the most profitable products on the shelf but also to run more efficient marketing campaigns. With Microsoft Business Analytics, we differentiate ourselves from competition, because we better understand our own business and the market in general, we can be more inventive in approaching trends and obviously take the most inspired decisions, “said Marius Dumitrescu, Centrofarm IT Manager.
Considerably reducing the time of getting business reports and analyzes. Reports that previously required 3-4 hours of work are now available automatically. Moreover, with Microsoft tools, users can produce their own reports without the IT department intervention. With Microsoft Business Analytics, we have achieved the goal of any required management report being available within 2 hours, Dumitrescu said.
Increasing the degree of achievement of sales targets. By permanently knowing the situation on each product, supplier, location and taking action accordingly. From an operational point of view, the Business Analytics solution allows us to track the evolution of sales on complex criteria and to really understand the degree of achievement of our goals. Thus, the management team can make the right decisions in a timely manner for these goals to be achieved or exceeded. Also, based on sales analyzes, we have defined a proactive supply policy to permanently stock the most wanted and sold drugs. In the future, this process will be automated, said Marius Dumitrescu.
Increasing the quality and effectiveness of marketing campaigns. Centrofarm’s marketing department uses Microsoft Analytics intensely to analyze the effectiveness of previous activities and plan future campaigns. Marketing colleagues will keep models with the best impact, they will be able to negotiate on a real basis co-marketing funds from partners and will have a direct and easy impact on sales, says Marius Dumitrescu.
Reducing pressure on the IT department. The simplicity of Microsoft’s solution makes non-technical users able to handle themselves without requests and help from the IT Department, both in getting reports and publishing them. The technical team can therefore allocate all available resources to other projects with high impact in the organization.
From the point of view of Return on Investment, Centrofarm’s internal assessments have shown a major impact of the solution on the company’s efficiency and implicitly a quick depreciation of the investment in the Microsoft solution, concluded Marius Dumitrescu.