Iocor And Instantinsurance Olap System

1              Executive Summary

Among other benefits, the implementation of an OLAP system at IOCOR and InstantInsurance will result in the following business requirements being supported consistently at any time:

1. Analyze data related to the core business
2. Analyze data related to the Policies and Claims
3. Find out how many employees will be required to handle the current Claimants in a better way
4. Introducing new types of Claims and arranging special promotions.
5. Cutting down extra costs
6. Compare and analyze the data of different financial years

IOCOR currently utilize a data warehouse which allows them to combine data from a variety of data sources such as their Enterprise Resource Planning (ERP) and Customer Relationship Management (CRM) systems to support decision making. However according to Darrow (2003), OLAP applications can further aid them by enabling them to make decisions about future actions that are required to improve the business and perform trend analysis to understand the "why" aspect of business conditions. On-line Analytical Processing can be defined as "a category of applications and technologies for collecting, managing, processing and presenting multidimensional data for analysis and management purposes", (OLAP Report, 2003). The aim of developing an OLAP system at IOCOR and InstantInsurance is to improve their business operations by allowing them to appropriately analyze their business data.

The report contains the following sections:

Design of the proposed system: This section provides the system architecture and design of the OLAP cubes, presented by using Thomson diagram and description of each analysis reports.

How the OLAP system can address the business requirements at Fishy Business: a short discussion of each business requirements and how the OLAP system will support them.

Future development: summarizes the actions that need to occur in order to implement the data warehouse design and related justifications.

2              Design of the Proposed System

2.1 System Architecture

In order to provide appropriate information to help the management team making better decisions, the IOCOR and InstantInsurance OLAP system has been proposed. This section provides the system architecture and functional descriptions of the IOCOR OLAP system. OLAP uses a multidimensional view of aggregate data to provide quick access to strategic information for further analysis, (Coffing, 1998). Subsequently, organizations like IOCOR can gain better insight into data through consistent, fast, interactive access to a wide range of possible views of information that can be shared across the enterprise (Coffing, 1998).

The system architecture is shown in figure 1.

The first part of this system is source systems. Source systems can be considered as Online Transaction Processing (OLTP) or legacy information systems that operate daily business transactions at IOCOR (citation).

According to Ross and Kimball (2004), data is gathered from different source systems into a Data warehouse using ETL methods. Some appropriate data cleaning and ETL tools are used to automate this ETL process. Microsoft SQL Server has been used to build this data warehouse and all the data gathered from source systems is loaded onto this Microsoft SQL Server.

Ross and Kimball (2004) depicts that the second part is a data warehouse. Data warehousing consists of a set of processes and databases that provide data infrastructure to support decision making. A data warehouse is subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decisions (citation). The data warehouse has been designed and implemented by the lecturer.

Kimball (1997) at another place explains that this data warehouse is using the database which has many tables in it. Some of these tables are kept there to represent the dimensions and store data which is related to those dimensions like Automobiles, Claim, Claimant, Insured Party, Employee etc. Apart from these tables there are eight other tables which are helping us to store the information related to the fact tables like Claim Snapshot, Claim Transaction Fact, and Policy Transitions etc

The next part is an OLAP engine implemented by using Microsoft SQL Server Analysis Services, which enables its users to create and manipulate multidimensional data structures or cubes. The design of developed cubes is presented by using Thomson diagram in the next section.

Pearson (2005) describes that Microsoft® SQL ServerTM Analysis Services are used to process our cubes in reoccurring manner. Processing is essential to keeping our OLAP data sources in sync with the data sources that they summarize. We can use it to automate the processing cycles, and thus keep our cubes updated to reflect the latest data. Rabeler (2003) at another place depicts that in a relational database users can analyse data stored in Analysis Services with greater speed and flexibility than users can querying the same data. Analysis Services queries data more efficiently because it organizes data into multidimensional structures, stores aggregations of fact data, and stores frequently used results in a memory cache for quick access.

Sullivan (2000) says that the final part is client tools. Through the used of intranet and the client/server architecture of the system, multidimensional data cubes in MS Analysis Services, can be shared and accessed across the computer network. This enables the users view the data as a single point, which can reduce redundancy and inconsistency in order to make effective decisions from the enterprise-wide view rather than one part of the organization that might leads to misguided decision. Crystal Analysis, ProClarity and Microsoft Excel are some of client software, which provides fast and flexible query performance.

Kyd (2004) explains that after comparing Microsoft Excel with other OLAP tools we came to knew that Excel is relatively cheaper than other tools available in the market. Excel is commonly used by the computer users and it has most of the functionalities users need to display data. This reduces the cost of user training. Fielden (1999) says that Excel comes with the Microsoft Office suite. So there is no need to buy a separate application for OLAP reporting. Excel is best used for business operations reporting and goals tracking.

Figure1: the architecture of the IOCOR and InstantInsurance OLAP systems

2.2 Dimensional Model

Kimball (1997) says that a dimension model is used to show a de-normalized view of data for faster access to be used for decision making applications. This is different from the 3rd normal form. Generally the dimensional model is used for transactional (OLTP) type systems. This model consists of different Dimensions which are used to store information about same category of information e.g. Time Dimension. These dimensions have got Attributes in it like Year, Quarter and Months. In the current scenario, the dimension model consists of fifteen dimension tables, which are as follows:

• Automobile
• Claim
• Claim Status
• Claim Transaction
• Claimant
• Coverage
• Covered Item
• Employee
• Insured Party
• Month
• Policy
• Status
• Third Party
• Time
• Transaction

Apart form these fact tables our current scenario consists of eight fact tables which are:

• Claim Snapshot
• Claim Transaction Fact
• Custom Claim Snapshot
• Custom Claim Transaction
• Custom Snapshot
• Custom Transactions
• Policy Snapshot
• Policy Transactions

2.2.1            Claim Snapshot

The Claim Snapshot multidimensional structure is comprised up of Month, Insured Party, Employee, Covered Item, Coverage, Claim Status, Claim and Policy. Apart from the keys of each dimension, this cube has got some measures in it. These measures are Reserve Amount, Paid This Month, Received This Month, Number Of Transactions. This cube does not have any calculated members and cells. The physical size which this cube has occupied is 0.05Mb.

2.2.2            Claim Transaction Fact

The Claim Transaction multidimensional structure is comprised up of Claim Transaction Fact, Time, Insured Party, Employee, Covered Item, Coverage, Third Party, Claimant, Policy and Claim Transaction. Apart from the keys of each dimension, this cube has got Amount as a measure. This cube does not have any calculated members and cells. The physical size which this cube has occupied is 0.04Mb.

2.2.3            Custom Claim Snapshot

The Custom Claim Snapshot multidimensional structure is comprised up of Custom Claim Snap, Month, Insured Party, Employee, Coverage, Claim Status, Claim and Policy. Apart from the keys of each dimension, this cube has got some measures in it. These measures are Reserve Amount, Paid This Month, Received This Month, Number Of Transactions, and Total Damage Claimed. This cube does not have any calculated members and cells. The physical size which this cube has occupied is 0.03Mb.

2.2.4            Custom Claim Transaction

The Custom Claim Transaction multidimensional structure is comprised up of Custom Claim Trans, Automobile, Time, Insured Party, Employee, Claim Transaction, Coverage, Third Party, Claimant, and Policy. Apart from the keys of each dimension, this cube has got Amount as a measure in it. This cube does not have any calculated members and cells. The physical size which this cube has occupied is 0.03Mb.

2.2.5            Custom Snapshot Fact

The Claim Transaction multidimensional structure is comprised up of Custom Snapshot, Status, Automobile, Policy, Coverage, Employee, Insured Party and Month. Apart from the keys of each dimension, this cube has got some measured facts like Written Premium, Earned Premium, Primary Limit, Primary Deductible, Number of Transactions and Auto Replacement Value. This cube does not have any calculated members and cells. The physical size which this cube has occupied is 0.10Mb.

2.2.6            Custom Transactions Fact

The Custom Transactions multidimensional structure is comprised up of Custom Transactions, Time, Insured Party, Employee, Coverage, Automobile, Transaction and Policy. Apart from the keys of each dimension, this cube has got Amount as a measure in it. This cube does not have any calculated members and cells. The physical size which this cube has occupied is 0.05Mb.

2.2.7            Policy Snapshot Fact

The Policy Snapshot multidimensional structure is comprised up of Policy Snapshot, Month, Insured Party, Employee, Covered Item, Policy, Status and Coverage. Apart from the keys of each dimension, this cube has got some measures like Written Premium, Earned Premium, Primary Limit, Primary Deductible and Number of Transactions. This cube does not have any calculated members and cells. The physical size which this cube has occupied is 0.11Mb.

2.2.8            Policy Transactions Fact

The Policy Transactions multidimensional structure is comprised up of Policy Transactions AS Policy Transactions, Transaction, Time, Policy, Coverage, Covered Item, Employee and Insured Party. Apart from the keys of each dimension, this cube has got Amount as a measure in it. This cube does not have any calculated members and cells. The physical size which this cube has occupied is 0.07Mb.

3              Thomsen's Diagram

Thomsen's Diagram or multi dimensional domain structure (MDS) is a diagramming technique developed by Eric Thomsen. MDS is a metaphor for representing the multiple-dimensional information spaces of an OLAP system. Unlike traditional cube, which is limited to represent as maximum as three independent dimensions, MDS can represent any number of dimensions. Each dimension is depicted by a vertical line and each member of a dimension is represented by a unit interval on the line.

The multidimensional structures of IOCOR and InstantInsurance created on the basis of Eric Thomson diagramming technique is follows:

3.1 Claim Snapshot Structure

3.2 Claim Transaction Structure

3.3 Custom Claim Snapshot Structure

3.4 Custom Claim Transaction Structure

3.5 Custom Snapshot Structure

3.6 Custom Transactions Structure

3.7 Policy Snapshot Structure

3.8 Policy Transactions Structure

4              Analysis Reports

According to Howson (2004), the Analysis reports support decision makers by providing accurate information for making faster and more effective decisions. Decision makers are able to slice and dice data to gain insights from multidimensional view. Decision makers are able to conduct a quick analysis such as finding significant business problems or opportunity trends.

Ellis (2004) points out that the reports for the current system have been generated using Microsoft Excel. MS Excel delivers OLAP-powered guided analysis that enables end users to easily gain insight into business data and make intelligent decisions that impact enterprise performance. Willet (1998) explains that MS Excel is powered with the PivotTables and Pivot Charts using which a drag and drop design environment and making use of rich visualization techniques is possible. Using these PivotTables users have got the ability to select any combination of Fields and then drag and drop them into their reports

The reports in the current system have been formed as to allow IOCOR and InstantInsurance to make better decisions and have a greater understanding of the performance of their business functions. These reports identify potential problems and significant trends which was not possible through the company's past reporting methods, causing the company to loose financially. Furthermore the reports enable senior executives to effectively analyze the presented information by allowing them to drill up, down, across or through the data as they deem necessary.

4.1 Claims Snapshot Analysis

The purpose of Claims Paid and Received Analysis is to provide information about all the claims which are received and paid during the year 2004-2005. The report provides a hierarchical view for analyzing. Users are able to drill down the Year into Quarter and Months. Even the Claims can be drilled down into claims type and         claim description. Apart from that users are able to drill down the data in the basis on other dimensions like Claims, Employee, Covered Items, Policy, Insured Party and Coverage.

Based on the current data it is obvious that IOCOR is doing its major business related to the Automobiles claims. Also in the year 2005 IOCOR did more business as compared to the year 2004. The management of IOCOR should develop some business strategies to do more business in the other claims like Home owner and Personal Article.

Using another report which is based on the Insured Parties we can see that Females who are employed and are married are the major customers of IOCOR. While the females which are from Military are the ones whose paid claims in a month are the lowest from other segments.

Another report shows that the number of Transactions per Employee is more on those types of employees who are Brokers and work in the field. If this report is further drilled down on the basis of employee name then we can see that an employee named as Art Dodger has got the maximum number of transactions in 2004, while Betty Able has got the maximum transaction in the year 2005. This report helps the IOCOR management to see that out of Filed and Headquarter Employees are the ones which are performing well.

4.2 Claims Transactions Analysis

The purpose of Claims Transaction Analysis is to provide information about all Amounts of all the claims which are processed during the year 2004-2005. The report provides a hierarchical view for analyzing data. Users are able to drill down the Year into Quarter and Months and even into Day. Claims Transactions can be drilled down into Claims Transactions Types and Claim Transaction description. Users are also able to drill down the data in the basis on other dimensions like Insured Party, Employee, Covered Items Coverage, Third Party and Claimants.

This should be noted that this analysis is based only on the information is only for the Quarter Four of the year 2004-05. This report shows that the Amount which is paid to Claimants increase in the month on December is more than the other months in this Quarter. In 2005 IOCOR paid in claims about four times more than last year, which is not a good sign for the company.

To view this data we can also generate a PivotChart in Excel. This chart shows us the 3D bar chart showing different values for each year.

We can further analyze the above given data by the Type of Covered Item using the following report, which shows that Automobile Covers were the one for which major portion of the Payment (Claim Type) was made.

4.3 Custom Claims Snapshot Analysis

The purpose of Custom Claims Snapshot Analysis is to provide information about amount paid and received, number of transactions done and Total Damage Claimed during 2004-2005. The report provides a hierarchical view for analyzing data. Users are able to drill down the Year into Quarter and Months. We are able to drill down the data in the basis on dimensions like Month, Insured Party, Employee, Claim Status, Coverage, Claims and Policy. However this analysis can only be done on the Claim Type Automobile as we do not have any data for other claims type in our data warehouse. Another possibility is that during 2004-05 no Damage was claimed against Home Owner and Personal Article which as other type of claims. So therefore to some extent this analysis can not be utilized accurately.

Following report helps the users to analyze the information related to the claims. It shows that in 2004 total damage claimed against the Collision Minor with injuries, while in 2005 Collision Minor with injuries was on top.

4.4 Custom Claims Transaction Analysis

The purpose of Custom Claims Transaction Analysis is to provide information about amount paid and received, number of transactions done and Total Damage Claimed during 2004-2005. The report provides a hierarchical view for analyzing data. Users are able to drill down the different dimensions like Time, Insured Party, Employee, Coverage, Third Party, Claimant, Automobile, Claim Transactions and Policy. This analysis is pretty much similar to the other analysis done earlier in Claim Transactions Analysis. The only difference is that within this analysis users are able to browse information related to the Automobile dimensions and can further drill it down by their Types.

The report which is shown below confirms that Studebaker had the highest Amount figures in 2004, while this dramatically fell down in 2005 which made Studebaker as lowest in year 2005. On the other hand in year 2005, Lexus automobiles were the one which got the highest figures among all other Automobile Types.

4.5 Custom Snapshot Analysis

The purpose of Custom Snapshot Analysis is one of the major analyses done for IOCOR. In this analysis we can measure facts to provide information like Written Premium, Earned Premium, Primary Limit, Primary Deductible, Number of Transactions and Auto Replacement Value. The report provides a hierarchical view for analyzing data. Users are able to drill down the different dimensions like Month, Insured Party, Employee, Coverage, Status, Automobile and Policy. This analysis is very important for IOCOR's management as it has some important measures covered in it.

The following report shows that the Collision 1000 deductible is the mostly used Coverage within the year 2003-05. It should be noted that although we ca measuring data for year 2003 but there is no information available for this year and hence it's not displayed here. On the other hand Uninsured motorist type carries the lowest figures of Auto Replacement value. Family market segment has used both the Uninsured Motorists and Collision 1000 deductible coverage types.

4.6 Custom Transactions Analysis

The purpose of Custom Transactions Analysis is to measure the Amount by making use of the IOCOR dimensions like Time, Insured Party, Employee, Coverage, Automobile, Transactions and Policy. The report provides a hierarchical view for analyzing data as all of these dimensions can be further drilled down to see any combination of information. As this analysis is capturing the measure of Amount therefore this analysis is helping the managers of IOCOR's to make some important decisions related to the company. This analysis is a little bit similar to the Claim Transaction Analysis and Custom Claims Transactions, which haven been discussed earlier. But this analysis is also covering information about Transaction, which makes it different from the other two analyses done earlier.

The above report shows that in the fourth quarter of year 2005 Transaction type of Create Coverage has increased dramatically, which means that large number of new customers have joined the company and have taken different policies. Also in the same quarter IOCOR lost many business customers, which is not a good sign for the company. This can help IOCOR's management to develop new promotions for the companies/business clients so that they won't cancel their policies. This report also depicts that most amount of Transactions in IOCOR's are in Modifying the Coverage.

If the users want to know the information related to the Employees who are doing most of these Transactions then they can do this by dragging and dropping the Employees filed into this PivotTable. The above report displays separate figures each type of employees. By this report users can see that mainly Transactions are for those employees which are working Field and are Brokers.

4.7 Policy Snapshot Analysis

The purpose of Policy Snapshot Analysis is to measure the some measures like Written Premium, Earned Premium, Primary Limit, Primary deductible and Number of Transactions. Like other analysis this analysis also provides a hierarchical view for analyzing data as all of these dimensions can be further drilled down to see any combination of information. In this analysis we can see the information related to the Month, Insured Party, Employee, Covered Item, Policy, Status and Coverage dimensions. As this analysis is capturing the measure of Amount therefore this analysis is helping the managers of IOCOR's to make some important decisions related to the company. This analysis covers same measures as of Custom Snapshot Analysis. The different thing here is that it is measuring the information about the Status dimension.

The above report shows that the Status of Earned Premiums and Written premium by the Filed employees nearly double to the employees of Headquarters. The Written premium of the Regular Status type is highest for both of the Employee types.

4.8 Policy Transactions Analysis

The purpose of Policy Transactions Analysis is to measure the Amount by making use of the IOCOR dimensions like Time, Insured Party, Employee, Covered Items, Coverage, Transactions and Policy. The report provides a hierarchical view for analyzing data as all of these dimensions can be further drilled down to see any combination of information. As this analysis is capturing the measure of Amount therefore this analysis is helping the managers of IOCOR's to make some important decisions related to the company. This analysis is a little bit similar to the Claim Transaction Analysis and Custom Claims Transactions, which haven been discussed earlier. But this analysis is also covering information about Transaction, which makes it different from the other two analyses done earlier.

5              How the OLAP System Can Address the Business Problems at IOCOR InstantInsurance.

In general the information system issues faced by IOCOR decision makers include:

• Systems are slow is retrieving data.
• Inconsistent and inaccurate information flow affecting decision making
• Historical data analysis is time consuming and complicated to perform.
• Inconsistent grouping of product groups and subgroups, which makes analyzing reports difficult and inaccurate. Therefore, there is a need to categorize them correctly.
• The lack of correlation between the data sources (i.e. ERP and MIS systems)
• Poor quality of data leading to faulty decisions.
• The lack of a user-friendly interface to generate reports and analyze data
• The inability to perform in-depth analysis of data as there was not technology in place to support this.

Ross and Kimball (2004) depicts that The business problems will generally be overcome with the implementation of the OLAP system through the reporting of timely, accurate data captured in legacy systems and periodically updated in the data warehouse. All users will then be looking at reports with the same information at any given point in time.

According to Ross and Kimball (2004) the Users of the OLAP system will interface with it seamlessly via the IOCOR InstantInsurance OLAP System. This will allow for the creation of graphs and drill down functionality of the information to report on various aggregates of data. The system will ensure that data from the source systems are accurately captured and loaded into the data warehouse to allow the formation of timely reports to support decision making. In addition to the described problems many other unknown problems can also be solved by using the virtual cubes and doing ad hoc queries.

6              Future development

Thomsen (1998) says that OLAP system development is an evolutionary process. While building OLAP It is necessary to look at the business as a whole - applications, hardware, software, strategic initiatives, product directions, evolution paths for the technology etc. to coordinate and build a system architecture that will carry the company well into the future. The OLAP system will change the understanding of users about their work. As their understanding of their work change, their information needs change. Mael, (1997) at one place further explains that when their information needs change the OLAP system must change. It is obvious that this is a continuous process. The OLAP system will evolve overtime to handle such change.

Managers of IOCOR need some advanced instruments which depict dependencies between process management goals. The Balanced Scorecard is a widely used instrument for strategic management. Carickhoff (1997) says that users of IOCOR may need to analyze data multidimensionally while they are disconnected from the corporate network, such as when traveling with a laptop computer. In such cases users might want to view the cubes in the web browser, which needs implementation of Web based version of OLAP tools. Looking to the future Mael (1997) depicts that the largest hurdles faced by OLAP vendors in delivering functionality over the Web will be the browser's procedural environment and the volumes of data that could potentially be needed on the client. In the future IOCOR might have to purchase some more sophisticated OLAP tools like ProClarity, Crystal Analysis, and Cognos etc.

Pendsel (2005) explains at one place that the OLAP implementations are fraught with difficulties. OLAP managers have a hard time completing implementations, with problems appearing in batch windows, legacy system integration, query performance and data quality. Also once a system is implemented, OLAP managers struggle to accurately predict its performance or adequately explain irregular outages.

7              References

• Darrow, B. (2003). Cognos Revise Series 7, ProQuest Computing
• Ellis, D. (2004). Data Mining and Business Intelligence: Where will it lead us?. InfoTech Update.
• Fielden, T. (1999). Excel add-in eases OLAP. InfoWorld
• Howson, C. (2004). BI Scorecard OLAP. Intelligent Enterprise
• Kimball, R. (1997). A Dimensional Modeling Manifesto. ProQuest Computing
• Kyd, C. (2004). Use Business tools with Excel to save time and money : www.exceluser.com
• Mael, S. (1997). Business objects tiers up. ProQuest Computing
• Pearson, W. (2005). Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS : www.dbreview.com
• Pendsel, N. (2005). OLAP products and applications have been around for much longer than most people think: www.olapreport.com/origins.htm
• Rabeler, C. (2003). Microsoft SQL Server 2000 Analysis Service Performance Guide : www.microsoft.com
• Ross, M., & Kimball, R. (2004). Surrounding the ETL Requirements. ProQuest Computing
• Sullivan, T. (2000). Microsoft adds data mining to its SQL Server OLAP services. FortWashington
• Thomsen, E. (1998). Music of the Cubes revisited. USA: Database Programming and Design
• Thomsen, E. (1997). OLAP Solutions: Building Multidimensional Information Systems, NY: John Wiley & Son.
• Willet, S. (1998). Microsoft plans OLAP assault with Excel 98, ISV products. Computer Reseller News