Publishing: Business Intelligence
Formed in 1986, Frontline is the UK’s largest magazine sales, marketing and distribution company. The organisation is a joint venture between three large publishers; BBC Worldwide, Bauer Media and Haymarket Media Group.
Business Intelligence solution revolutionises the UK’s biggest magazine distributor’s Business Performance Management.
The organisation sells and distributes over 160 magazine titles, including 58 of the top 200 best-selling titles in the UK (source: ABC). In 2010, the group supplied 480m copies of their magazines to over 55,000 retail outlets, ranging from the biggest hypermarket to the smallest corner store.
Frontline are at the forefront of industry thinking, imbuing sustainable innovation and maximising the relationships between publishers, retailers, wholesalers, finishers and carriers. Their distribution model is focused on getting the right magazines, in the right numbers, into the right shops, at the right time.
Frontline receives an enormous volume of sales data from many sources. This data includes actual sales and supply figures for all retailers stocking its magazines as well as more limited data for competitor titles. Frontline also produce forecasts of sales. This business critical data needs to be shared with partners and sister companies.
A Business Performance Management project began in 2009; this initiative recognised that the degree to which their data resided across different systems and locations, including a plethora of individual spreadsheets with their own locally held data, was proving to be a barrier for business heads across the whole organisation, barring them from making fully informed decisions.
Getting a complete, consistent picture of performance was too time-consuming, and the manual effort required to produce reports was resource hungry, fraught with the risk of duplicated effort and human error diminishing the robustness of the report itself.
A mature Oracle data warehouse, built up progressively over a number of years, was already in existence, but reporting was manually intensive as users tended to simply export data to Excel before ‘hand-crafting and e-mailing these reports to partners.
As a result, Frontline felt their ability to take critical decisions was being compromised. They decided that if they could address these issues they could widen the gap between themselves and their competition.
The solution requirements
Frontline wanted to work towards a vision where they drew together all the data describing performance and the market in which they operate into a single up to date, reliable, robust single version of the truth.
This had to be done in a way which was resource efficient and, importantly, a way that made accessing the right information quick and easy, thus enabling faster, smarter decision making across the business.
With that in mind Frontline set about trying to find a solution which would support them in achieving this with the following selection criteria.
- Access via the browser. Many of the users are not based at Frontline, particularly the publishing and marketing teams. It was important to put those users closer to the information by giving them access to it via the web
- No client-side software. It was important that any solution wasn’t reliant on having to install software on the client side (because each partner was managing its own IT infrastructure)
- Not limited by different technologies. Different publishers work on different technologies. E.g., some may be using Macs, PCs and some would be using different versions of Windows; the solution had to work in all those circumstances
- Robust security. With this being a joint venture business, managing confidential information between partners is of upmost importance. The solution needed to handle Frontline’s security model
- Data governance. There are strict rules governing how the company can use data acquired from a number of different retailers and wholesalers, and how it can be shared across the business varies depending on the data provider
- Speed of Performance. Another criterion, and arguably the most important, was speed of performance. Speed of performance would be a critical success factor in any solution being successful and gaining user adoption
- Ease of use. Similar to speed of performance, ease of use and flexibility would be a key to success. Being able to present information to users in a familiar front-end, such as excel would be advantageous as this wouldn’t require training or be a barrier to adoption
- Version control & auditing. Managing version control of reports and providing an audit trail of changes made and the ability to refer back to previous versions if required
Other considerations: Frontline had already made the decision to move to Windows 7, SharePoint 2010 and Office 2010. They had looked at other BI solutions, but it made sense to maximise their investment and evaluate the Microsoft BI stack to see if that met their selection criteria.
Selecting Business Agility and the BI solution
Following attendance at a Business Agility BI education seminar, Paul Smirk, Commercial Analyst Controller for Frontline, invited Business Agility to visit the Frontline Head office to explore their specific requirements in more detail.
Business Agility proposed carrying out a Proof of Concept to prove SharePoint 2010 BI was fit for purpose. Business Agility hosted this PoC in the Rackspace Cloud to decouple the project from the timeline of the internal rollout.
The PoC was required to meet 3 main criteria:
- External users needed to access the reports via a web browser and receive a near instant response to see the reports.
- The solution needed to accommodate Frontline’s security model which is based on combination of the role of an individual and the source of the data. E.g. for certain data sources partners can only see the detailed data for their own publications, but can see some aggregated information across all publications
- The time taken to refresh the data/reports needed to be reasonable. This would include the time taken to refresh the cube during the day and the process to be completed to recreate and publish the report(s). “Reasonable” is defined as 3-4 hours for the full refresh process.
The PoC consisted of:
- SharePoint 2010 Enterprise
- SQL Server 2008 R2 Enterprise
- Power Pivot for SharePoint
- Oracle 11g (loaded with an extract of the data warehouse)
The solution made use of SQL Server Analysis Services (SSAS) to extract data from the Oracle data warehouse and produce an OLAP cube.
This cube contained data from the warehouse and calculated measures. These were measures not present in the warehouse but were required for reporting at various levels of aggregation.
The security model required was very complex. Security was defined by a mapping table, mapping user roles to the partner data they were permitted to see. This mapping table was used within the cube to define role based, dimension level security.
This SSAS cube was used as a data source for PerformancePoint Services dashboards, containing drillable charts and graphs.
Power Pivot was used to produce reports against both the SSAS cube and the Oracle warehouse.
Following a successful PoC, Business Agility installed and configured Frontline’s production SharePoint 2010 environment. The solution was then deployed to the new Farm.
The solution is fulfilling the criteria of:
- removing any manual intervention
- eliminating the risk of human error
- improving the timeliness and robustness of the reported data
- The reports are no longer limited in terms of drill-through, and have the potential for processing millions of rows of data behind a familiar excel front end
- users are to get a rapid response to their interrogations of the data
In terms of developing their live reporting, using this new tool set, it’s fair to say that Frontline are still in the early stages. Most reports are making use of the functionality in SharePoint 2010, such as slicers and Power Pivot. Their future developments will look at developing reporting cubes, using Analysis Services and building interactive dashboards with Performance Point.
To find out more about how Business Agility can help you with your reporting, please contact us and speak to one of our Business Intelligence experts.