The Data Warehousing Institute based in Seattle came to the conclusion in its August 2005 report, "Strategies and Technologies for Deploying Business Intelligence," that 66% of organizations in different industries are trying to transform business intelligence (BI) from a departmental solution to an enterprise one. Only 17% of organizations have completed the task, while the remaining 17% will continue to deploy BI departmentally. This data shows that BI has a ways to go until it reaches maturity in most organizations.Newport Beach, Calif.-based Pacific Life Insurance Co. seems to have the maturity factor under control. It has implemented an enterprise server equipped with BI features.
Prior to the implementation, accessing corporate data was a long and tedious process for Pacific Life. Business groups would request ad hoc reports from the IT staff, which would sometimes take up to three days to produce, according to Cameron Cosgrove, vice president of information technology at Pacific Life.
The company was using a reporting tool, custom-built mainframe reports and other application-specific reporting tools that required specific skill sets, says Cosgrove, which limited the number of people who could create, augment and use the tools.
Other barriers prohibited the company from quickly and accurately creating reports using the current process. Five of the organization's 70 databases, which contain complex data schema with more than 20 tables and multiple data fields each, were reserved for reporting-housing data related to policies, agents, finances, transactions and new business opportunities.
When looking for a solution to these barriers, Cosgrove looked to the past. Ten years ago, the carrier made an investment in Microsoft technology.
"My philosophy has been to pick a technology standard and then leverage that as much as possible to get traction around savings and speed to market-rather than doing an assessment every time you want to do something you're currently not doing," says Cosgrove.
Pacific Life took advantage of its ongoing, mutually beneficial relationship with Redmond, Wash.-based Microsoft Corp. The life division of Pacific Life planned to standardize its data warehouse using Microsoft SQL Server 2005 Enterprise Edition and deployed the server's BI features.
SQL Server 2005 Enterprise Edition is designed to scale to the performance levels required to support enterprise online transaction processing, complex data analysis, data warehousing systems and Web sites. Available on 32-bit and Windows x64 platforms, as well as Itanium-based systems, SQL Server Enterprise is designed to drive new levels of performance for data-intensive applications.
"When SQL 2005 was being developed, we were approached by Microsoft to be an early adopter of the technology to not only give us a competitive advantage but (for them) to understand real-life business applications that this product would be used for," says Cosgrove.
"We were very interested because we needed to address reporting at a high level regarding how we create an infrastructure upon which we cannot just leverage our Microsoft stack (visualstudio.net and developer tools) but also to leverage the knowledge we have around SQL server. So we saw single reporting services as an ideal fit for creating a reporting platform, and that's when we started working on (implementation) back in 2003." SQL Server 2005 went into production December 2004.
The implementation went smoothly, and Microsoft was able make some adjustments, says Cosgrove. "We didn't change our back-ends at all; if anything, as we began running these different reports under their control, they began to see patterns of data that they wanted to clean up or look at for other reasons, but we didn't have to change the databases at all."
Many BI features are included in the Enterprise Edition. Some are:
* Integration Services: Integrate multiple data sources.
* Analysis Services: Enrich data and build complex business.
* Reporting Services: Write, manage and deliver rich reports.
* Data Mining Services: Explore data, discover patterns and apply patterns to business operations.
The server's BI features, particularly SQL Server 2005 Integration Services and SQL Server 2005 Reporting Services, enable Pacific Life to connect its disparate data sources, improve information access and generate dynamic reports that contain business-critical information from client and policy data to finances and transactions data.
DATA FROM MULTIPLE SOURCES
SQL Server 2005 can consume data from multiple sources simultaneously, perform multiple complex transformations and then concurrently distribute the data to multiple destinations, thereby enabling Pacific Life to use it for complex data flows. As the information flows from source to destination, the stream of data is split, merged, combined and otherwise manipulated with other data streams.
Prior to using the Reporting Services feature, the application developer would need to create a report after a business group member would request one. Now the application developer can link into an executable or a component that goes against the SQL reporting database and automatically create the report. When the business group member clicks on the reporting database, up to a dozen reports are ready to go. The developers are reusing and reporting service that's already built.
Pacific Life also used a feature called report builder within Reporting Services to enable end users to create their own reports. "It works sort of like a super pivot table. You've got columns of data fields that (end users) can click on and drag over to their report palettes, name the report and save the report with the data fields they need," says Cosgrove.
"They can create the sort sequence they want and create their own reports on the fly. And we've done that by (presenting) these data fields through Report Builder.
In the past, that just wasn't possible because you needed too much knowledge about the table structure for the underlying database to do that."
Pacific Life's new business department has a set of 20 operational reports that monitor processing activity and error conditions, such as the number of policies applied for but not purchased, or the amount of time an application spent in processing before Pacific Life made an offer.
Prior to SQL Reporting all these reports were ad-hoc requests that went into the IT department or held as SQL command lines statements that were run each month. Now they are defined via SQL Reporting and scheduled to run and to be automatically delivered to the right set of business analysts.
Prior to implementing the BI features, Pacific Life business personnel never generated a report. But, in less than two months after deployment, the business groups generated more than 50 reports on their own.
FUTURE USE
Pacific Life regularly adds more data fields from its different databases into the server and builds more reporting components that can be accessed. "It's just growing, and instead of having our data services team be the one-stop shopping for reports, it's open to all application developers; and it's open to a lot of our power users and end users," says Cosgrove.
The new data infrastructure is expected to reduce the time the IT staff spends on developing mainframe and custom reports by 80%, according to Cosgrove.
"The real time benefit there is portability of SQL reporting to other applications," he says. "And we reuse the components we built in SQL reporting in many different applications. We simply keep adding data fields to the capability so it just expands over time."
AT A GLANCE
Company: Pacific Life Insurance Co.
Location: Newport Beach, Calif.
Business: Provider of life insurance products, annuities and mutual funds, and a variety of investment products and services to individuals, businesses and pension plans.
Employees: 3,000
Revenue: More than $4 billion in 2005
At issue: The process of accessing and creating business analytical reports was inefficient and slow.
Solution: The company deployed a server equipped with business intelligence features to enable its business group-rather than IT personnel-to generate reports.
