The Health department of the NSW Government wanted to update their IT infrastructure and systems with respect to Business Intelligence. BICG was awarded this project to implement an effective solution. The BICG Team comprises of Sara Balsamini, Carl Herbert, Peter O’Gorman, Cody Middlebrook, Anupama, and Michael Rio, who are BICG’s Élite Business Intelligence professionals. In an interview, BICG Team Members, Cody Middlebrook and Sara Balsamini, discuss with Rajen Jani about the ongoing project.
NSW Health Government has 18 districts within the state, the state allocates budget to each of these regional areas to spend in the IT department, to assist in developing and maintaining their IT infrastructure and systems. One of the Reporting tools that the entire state used was Business Objects, whose state wide license was dropped leaving the regions to find a new software to replace this existing infrastructure. BICG, who are experts in Microsoft Business Intelligence and Reporting, was asked to replace their existing Business Object SAP reporting framework and move it to Microsoft.
BICG developed a solution to replace the existing SAP Business Objects Infrastructure by utilizing Microsoft technologies and other technologies as follows:
– SharePoint 2013
– SQL Server 2012
– SQL Server Analysis Services 2012
– SQL Server Integration Services 2012
– SQL Server Reporting Services 2012
BICG also designed the solution to sit in a Centralised Data Centre with shared SharePoint applications for all the 18 districts to leverage. Out of 18 districts, solutions to 3 districts, namely Northern NSW Health District, Murrumbidgee Health District, and Northern Sydney Health District have been offered. The project is going on for the other 15 health districts.
RJ: Welcome Cody. Welcome Sara. It’s great to have you both for this interview. So let’s start. How is the project designed?
CM: The project is designed to not only replace the SAP Business Objects Infrastructure, but also to provide a solution to sit in a centralised data centre, with shared SharePoint applications that other regions can leverage. This would create an infrastructure where all regions call share their work and designs, thereby stopping all 18 regions doing the exact same thing. Here is the model design:
RJ: So the solution uses this design.
CM: Yes. The aim of the project is to have all the 18 districts leverage the other districts framework and eventually going into the government data center, being leveraged as internal products.
RJ: There are 18 districts that need a solution. How many districts have been completed so far?
CM: Three districts. Northern NSW, Northern Sydney and Murrumbidgee. Sara worked on Northern NSW and Murrumbidgee. I worked on Northern Sydney.
RJ: Let’s start with Northern NSW.
Northern NSW Health District
RJ: Sara, what were the project objectives?
SB: The objectives of the project were:
– Supporting NSW Local Health Districts in the migration, from SAP Business Object reporting tool to Microsoft SQL Server and SharePoint Performance Point BI Centre, utilising the existing Enterprise Microsoft licenses, and opening the door to a centralised data warehousing and application based solution that could give all local health districts, the ability to leverage the work done by other health areas.
– deliver KPSs reporting to the Ministry of Health.
RJ: As a Business Analyst what was your role in this project?
SB: My tasks included:
– Perform gap analysis from the legacy Business Objects reports to the Microsoft PowerView reports.
– Map the requirements from SAP Business Objects Universes to SQL Server equivalent.
– Document the requirements for the finance budget, forecast and revenue Business Intelligence dashboards.
– Define requirements for BI centre: menu navigation, dashboards, scorecards, analytic graph, additional actions from the graph, annotations, and exception reports.
– Create wireframes for Emergency Department and Admitted Patients.
– Create personas for different user groups.
– Implement Scrum methodology with two weeks iterations.
– Implement Team Foundation Server to manage the sprint deliverables.
– Manage product backlog from TFS and prioritise tasks in the sprint.
– Participation in daily stand up meetings.
– Monitor sprint status with use of Burn Down Charts.
– Write Test Cases.
RJ: What difficulties did you face to arrive at a solution?
SB: Some of the difficulties were:
– PerformancePoint limitations (e.g. additional actions reachable from analytics graph, not from scorecards; annotations, etc. )
– SharePoint limitations (e.g. the standard out of the box SharePoint site has a left navigation that doesn’t suit the dashboards layout)
– The WordPress template applied messed up all the layout of the PerformancePoint reports
– Making site responsive for tablets and mobiles
– Security requirements for sensitive data
– Data integrity
– The clients wanted a persistent filter capability through the dashboards (sub sites)
– Finance department wanted excel reports capability incorporated in PerformancePoint
RJ: So all the difficulties were overcome and you offered a solution. What was the solution?
SB: BI centre in SharePoint 2013 (with a customised WordPress theme), where the users could navigate to different areas of business, and find business intelligence PerformancePoint dashboards, with key performance indicators and analytics graphs.
RJ: Did Northern NSW have any specific requirements?
SB: Northern NSW asked us to create scorecards with KPIs for Admitted Patients department, Emergency Department and Finance department. They want us to deliver these KPIs as a proof of concept, to demonstrate that PerformancePoint Services in SharePoint 2013 would give them the reporting capability that they needed, aggregating data from multiple sources and create rich dashboards, scorecards, and key performance indicators. They also didn’t have a SharePoint infrastructure so we started the development of the reports in a virtual server, and at the same time, BICG provided a SharePoint architect to create a SharePoint environment for them to use.
RJ: Allright, now let us move on to Murrumbidgee.
Murrumbidgee Health District
RJ: Were there any specific requirements?
SB: They already had a SharePoint site. They wanted a BI centre, customising the look and feel and apply a template. They wanted BICG to develop their finance module and create digital.
RJ: But the solution offered was the same?
CM: Yes, that’s correct. Northern NSW and Murrumbidgee had the exact same solution proving that the model is reusable.
RJ: Did Northern Sydney also have the same solution?
CM: A little different than Northern NSW and Murrumbidgee.
RJ: Allright. Let us move on to Northern Sydney.
Northern Sydney Health District
RJ: How was the solution at Northern Sydney different from Northern NSW and Murrumbidgee?
CM: Because of the State Wide License being dropped for SAP, and because BICG was successful with Northern NSW and Murrumbidgee delivery of the their SybaseIQ to SQL Server, we were engaged to build a SybaseIQ from Linux to SQL Server on a Windows server. The solution was a little different because the majority of the local health districts had Windows servers and not Linux servers, so we could not use our standard model.
RJ: So another model was created?
CM: We had to redesign the model and deliver a new system that interacted with Linux SybaseIQ and SQL Server. Northern Sydney decided to stay with SAP Business Objects and purchase their own license, keeping the legacy reports but changing their Sybase universes to SQL Server driven universe. We built a custom utility in C#. This utility was built to be completely modular and reusable. The utility drives the SybaseIQ BCP extract routines pulling the data across the network at super speeds, migrating the entire data into SQL Server every night.
RJ: How large was the migrating data, approximately?
CM: About 90 gigabytes. Approximately 400 tables.
RJ: How did the BICG team coordinate the work? Any centralized server?
CM: This is where we were limited. The IT team had already done the evaluation on the server license and infrastructure before BICG was allocated the work. This meant we were stuck with what we had and had to come up with ways to make this work. The limitations were:
– Standard SQL Server 2012. This limited the machine to a maximum of 64 gig of RAM.
– There was no Tabular Analysis Server.
– There was no Column Store Indexes.
– There was no Parallelism.
All this meant that we could only create one index at a time on one core at a time. And we had 400 tables and 90 gig of data to be loaded in under 90 minutes. We split the drives to 2 physical drives. One for database writing, the other for BCP extract loading. We formed data partitions and limited the indexes. The utility ran the process on 10 threads at a time so that the load balanced.
SB: Excuse me Cody. I would like to share the diagram created for Northern Sydney.
RJ: This flowchart is self-explanatory. Thank you Sara.
SB: You are welcome.
RJ: Well, that completes this study so far. We will continue with it as and when the other districts are completed. Thank you Cody, and Thank you Sara, for giving your precious time for this interview.
SB: Thank you Rajen.
CM: Thank you Rajen.
The BICG Team will provide the solutions for all the 18 Health Districts. So far, solutions have been provided for 3 districts, which have been benefited by their respective solutions.
1. The data was migrated from SAP Business Object reporting tool to Microsoft SQL Server and SharePoint Performance Point BI Centre.
2. All districts could utilise centralised data warehousing.
3. All districts could use application based solutions, which gave all local health districts the ability to leverage the work done by other health districts.
4. Effective KPSs reporting was done to the Ministry of Health.
For images, technical drawings and further information:
1. Cody Middlebrook (firstname.lastname@example.org)
2. Sara Balsamini (email@example.com)