Better Decisions from Trusted Data

• Better decisions from trusted data
• Greater efficiency
• One-month development
• Easy to enhance

Situation

When the insurance industry slipped into the doldrums in 2007 and 2008 due to a global economic slump, GCAN Insurance Company of Toronto, Ontario, saw a good opportunity to get ahead. It decided to use the soft market to deepen its distribution platform, better educate brokers on its products, and create new business applications to help staff work more effectively. “When the market does turn around, we want to be ready to hit the ground running,” says Jason Mancini, Vice President of Finance for Glenstone Capital.

GCAN is a commercial property and casualty insurance company in Canada that operates in every Canadian province. It serves Canadian businesses through an independent broker community and has more than 130 employees working in four offices in Canada. GCAN is a wholly owned subsidiary of Glenstone Capital, a portfolio company of the Ontario Teachers’ Pension Plan.

Financial reporting was one of the business processes that GCAN set out to strengthen. Since the company lacked a central general ledger program, it depended on a largely manual process of compiling spreadsheet-based sales data on its product lines from business units in different regions. However, the spreadsheets sometimes contradicted one another, and managers did not have the depth of information needed to drill down and discover the background for figures—what caused the spike in third-quarter results, or why were this broker’s sales way off? Managers resorted to asking the IT department for help in merging data and building reports, which created extra work for the busy IT staff and forced managers to wait for answers.

“We wanted to create a business intelligence solution that our managers could use to access business data from their own PCs on a more timely basis so that they could make more proactive, on-target decisions about the business,” Mancini says.

Every quarter, the company treasurer worked with the IT staff to pull together high-level financial reports for managers, but these, too, were often built from a variety of data sources that did not always agree. Plus, managers felt that the data in quarterly reports was not current enough to use in making decisions about the business, which shifted every day.

“Our regional managers and product-line managers needed to forecast, track, and monitor the sale of new products,” says Mark Cairns, Vice President and Chief Information Technology Officer for GCAN Insurance Company. “Using stale data, they could not make the kind of timely decisions that they needed to make. Our managers needed this data to make better decisions, but also to perform more complete analyses of their piece of the business. They were wasting time digging up information and trying to analyze it using spreadsheets.”

Solution

GCAN wanted to implement a business intelligence (BI) solution that would give the company a single repository of financial data and an easy way of reporting from it, one that managers could use themselves. “With rich BI tools, our managers could be more self-sufficient, and we could eliminate our finance and IT departments as bottlenecks in running the business,” Cairns says.

Use Software Already Owned

The company depended on Microsoft software to support many of its business needs: it used Microsoft SQL Server 2008 data management software, had deployed Microsoft Office SharePoint Server 2007 as the foundation for its corporate intranet, and used Microsoft Office Professional 2007 for productivity software. Because management set an aggressive timeline for developing the BI tool—by the end of 2009, which was just a couple of months away—the IT staff recommended using software that the company already owned and the IT staff already knew—Office SharePoint Server 2007.

“We wanted to deliver something on Office SharePoint Server, because we thought that it was the only viable solution in terms of rapid delivery,” Cairns says. “Office SharePoint Server is a great portal with built-in workflows, and we had a lot of experience with it. We don’t have a large IT staff, and we wanted to contain the number of tools we had to maintain. Plus, we had also made the decision to deploy Microsoft Dynamics GP as our general ledger and central financial repository and to use SQL Server 2008 as the storage mechanism. We felt that Office SharePoint Server would work well with these other Microsoft programs.”

Build with Beta Version

In October 2009, one of Cairns’s team members attended the Microsoft SharePoint conference in Las Vegas, Nevada, and came back excited about what he had heard of Microsoft SharePoint Server 2010. Cairns, too, was impressed with the announced enhancements, specifically to Excel Services and PerformancePoint Services. Excel Services provides access to real-time, up-to-date Microsoft Office Excel spreadsheets from a Web browser. PerformancePoint Services provides dynamic dashboard and scorecard capabilities that enable business users to create highly visual, interactive dashboards for displaying complex business data.

Microsoft improved PerformancePoint Services in SharePoint Server 2010 by providing a greater variety of visualization options, such as a Decomposition Tree, which enables users to graphically interact with their data to understand the most important aspects of their business. Also, scorecards contain key performance indicator (KPI) details that highlight accountability, thresholds, and collaboration, tied to strategic processes.

GCAN uses PerformancePoint Services to aggregate content from Office Excel spreadsheets, and Excel Services to publish those spreadsheets to a browser and keep them up-to-date, providing all managers with a single version of the truth. “Office Excel is core to our analytical capabilities, but now we can move Excel into the browser and streamline the process of collecting and managing the data,” Cairns says.

With these improvements, “It made more sense to build our BI solution in SharePoint Server 2010 than to build it in Office SharePoint Server 2007 and migrate it,” Cairns says. “The catch was, SharePoint Server 2010 wouldn’t be released until later in 2010, but we needed our tool finished by the end of 2009. I spoke with TGO Consulting, our technology partner, and proposed that we proceed with the public beta version of SharePoint Server 2010.”

TGO Consulting is a Microsoft Gold Certified Partner based in Toronto that had helped GCAN with previous technology projects. GCAN proposed the SharePoint Server 2010 idea to TGOon a Thursday, and a TGO developer went home and installed SharePoint Server 2010 over the weekend. On Monday morning he called GCAN and said, “Let’s move forward.”

Mancini provided TGO with a spreadsheet specifying all the reports that GCAN would need and the data sources from which they would come. “Jason [Mancini] and TGO spent three days going over our requirements and looking at the analysis data from Microsoft Dynamics GP to determine how best to present the information through SharePoint Server,” Cairns says. “A week later, TGO came back with the basic solution framework and site design, showing our data in the format we needed.”

TGO began development in November 2009, and by early December, GCAN began validating the data and fine-tuning the KPI metrics. “It took one developer just one month to build the BI solution from start to finish,” Cairns says. “We customized some of the analysis cubes coming out of Microsoft Dynamics GP, but we used everything out of the box from SharePoint Server—all the KPIs, graphs, Decomposition capabilities, and so forth.”

GCAN runs its BI dashboard on three virtual machines that reside on one physical server. One virtual machine runs a SQL Server 2008 database, one runs SharePoint Server 2010 Enterprise, and one is a file server. All three virtual machines run the Windows Server 2008 R2 Standard operating system.

Managers Serve Themselves

The new GCAN BI dashboard gives about 25 managers browser-based access to fresh business data from their own desktop computers, without the need to submit requests to the finance or IT staffs. They can drill down to see the numbers beneath the numbers, with all data coming from the new general ledger in Microsoft Dynamics GP.

“All the information that was floating around on myriad desktop Office Excel spreadsheets is now in Microsoft Dynamics GP and accessible through our BI dashboard,” Mancini says. “Managers have access to underwriting data, business results reports, total company results, and expense reports, with the ability to drill down by region, division, and product line. We have also integrated nonfinancial data included in the Microsoft Dynamics GP ledger (headcount, vacancies, and expected salaries), corporate strategy objectives, and competitive analysis data, which still lives in spreadsheets. With SharePoint Server, it’s easy to integrate any data source.”

GCAN is also building a data warehouse that will contain more detailed insurance information from some of its key business systems, such as a policy administration system. This will give managers an even greater drill-down capability, to the policy or claim level, all presented through the dashboard.

GCAN rolled out its BI dashboard to a small group of senior executives at the end of 2009. In the first quarter of 2010, it rolled out the dashboard to executive managers and product-line managers. “Adoption of the dashboard has been rapid,” Cairns says. “It takes about eight minutes to learn how to use it.”

With the BI dashboard in place, GCAN is now free to plan enhancements. Using the improved linkage between SharePoint Server 2010 and the Active Directory service, GCAN can easily update and add new features.

Benefits

By building its BI dashboard on SharePoint Server 2010, GCAN enjoyed a short development process—just one month from prototype to production—and delivered a tool that is easy to use and provides rich, graphical data depiction and drill-down capabilities. Business managers and executives can now meet their own reporting needs using up-to-the-minute data contained in Microsoft Dynamics GP. Taking advantage of technical improvements to SharePoint Server 2010, the IT staff can regularly fine-tune the dashboard as required, without being a bottleneck to business users.

Better Decisions from Trusted Data

By combining Microsoft Dynamics GP and SharePoint Server 2010, GCAN has created one financial repository and an easy-to-use mechanism for accessing that data. “With our BI dashboard, we are all looking at the same current data, so we can make better-informed decisions every day of the year. We’re no longer dependent on a monthly reporting cycle,” Mancini says. “Also, with the ability to drill down into Microsoft Dynamics GP data, our managers can better prepare their analyses and investigations to see where they are going off budget or why the sales in a particular region are down. With this kind of insight, we can steer the business more intelligently.”

Because the data coming out of its core repository is accurate, GCAN managers trust it. “We like to say ‘one source, one truth,’” Mancini says. “By moving our Office Excel spreadsheets into SharePoint Server, we’ve eliminated the guesswork about data sources and accuracy.” Also, the fact that more people are looking at the company’s financial data is improving the quality and the collective insight into business performance. “There’s a better chance now that we’ll notice if trends are awry because of the ability to graphically digest complex financial data,” says Mancini.

Greater Efficiency

Managers and executives no longer need to spend approximately four hours each month compiling, merging, and verifying data across spreadsheets, which enables each underwriting manager to apply this recaptured time to underwriting and sales functions. “Our BI dashboard is easy to use, and managers can use it proactively rather than waiting for information to be given to them,” Cairns says. “With SharePoint Server 2010, we took advantage of knowledge that both our IT staff and our end users already had. We moved from a ‘3’ to a ‘9’ in terms of capabilities that we provided our end users.”

GCAN has also freed up its Senior Vice President and Treasurer from doing the quarterly financial reporting that used to consume about one weekper month of his time. Now he can better focus on enterprise risk management, compliance issues with ratings agencies, and other ways to provide strategic value to the business.

One-Month Development

A key design goal was to be up and running in a month. “Our goal was to deliver a BI solution by year-end,” Cairns says. “By choosing SharePoint Server 2010, we not only reached that goal but delivered a solution that was populated with extensive content by production.”

Easy to Enhance

Another advantage of building its BI dashboard on SharePoint Server 2010 is the ability to regularly enhance it. “We can now independently enhance the dashboard frequently going forward, without any external assistance,” Cairns says. “We should be able to cut enhancement time in half, from eight weeks to four weeks, thanks to improvements in SharePoint Server 2010. It’s easier to update and maintain features; the design tools are much more advanced. We only have five people on our development staff, and they are busy maintaining our basic infrastructure applications. With SharePoint Server 2010, development is much easier, so our staff can quickly turn changes around.”

GCAN wants to make improvements frequently, creating new capabilities every couple of months. “With SharePoint Server, that will be easy to do,” Cairns says. “Plus, the software is just so cool! I’d rather let my team play with the latest, innovative software products, and the coolest tool on the market right now is SharePoint Server 2010.”

Microsoft SharePoint Server 2010

Microsoft SharePoint Server 2010 is the business collaboration platform for the Enterprise and the Internet.

For more information about Microsoft SharePoint Server 2010, go to: