Iocor And Instantinsurance OLAP System

1 Executive Summary				like Claims, Employee, Covered Items, Policy,
Among other benefits, the implementation of an				Insured Party and Coverage.
OLAP system at IOCOR and InstantInsurance will				Based on the current data it is obvious that
result in the following business requirements being				IOCOR is doing its major business related to the
supported consistently at any time:				Automobiles claims. Also in the year 2005 IOCOR
1. Analyze data related to the core business				did more business as compared to the year 2004.
2. Analyze data related to the Policies and Claims				The management of IOCOR should develop some
3. Find out how many employees will be required				business strategies to do more business in the
to handle the current Claimants in a better way				other claims like Home owner and Personal Article.
4. Introducing new types of Claims and arranging				Using another report which is based on the
special promotions.				Insured Parties we can see that Females who are
5. Cutting down extra costs				employed and are married are the major
6. Compare and analyze the data of different				customers of IOCOR. While the females which
financial years				are from Military are the ones whose paid claims
IOCOR currently utilize a data warehouse which				in a month are the lowest from other segments.
allows them to combine data from a variety of				Another report shows that the number of
data sources such as their Enterprise Resource				Transactions per Employee is more on those
Planning (ERP) and Customer Relationship				types of employees who are Brokers and work
Management (CRM) systems to support decision				in the field. If this report is further drilled down on
making. However according to Darrow (2003),				the basis of employee name then we can see
OLAP applications can further aid them by enabling				that an employee named as Art Dodger has got
them to make decisions about future actions that				the maximum number of transactions in 2004,
are required to improve the business and perform				while Betty Able has got the maximum
trend analysis to understand the "why" aspect of				transaction in the year 2005. This report helps the
business conditions. On-line Analytical Processing				IOCOR management to see that out of Filed and
can be defined as "a category of applications and				Headquarter Employees are the ones which are
technologies for collecting, managing, processing				performing well.
and presenting multidimensional data for analysis
and management purposes", (OLAP Report,				4.2 Claims Transactions Analysis
2003). The aim of developing an OLAP system at				The purpose of Claims Transaction Analysis is to
IOCOR and InstantInsurance is to improve their				provide information about all Amounts of all the
business operations by allowing them to				claims which are processed during the year
appropriately analyze their business data.				2004-2005. The report provides a hierarchical
The report contains the following sections:				view for analyzing data. Users are able to drill
Design of the proposed system: This section				down the Year into Quarter and Months and even
provides the system architecture and design of				into Day. Claims Transactions can be drilled down
the OLAP cubes, presented by using Thomson				into Claims Transactions Types and Claim
diagram and description of each analysis reports.				Transaction description. Users are also able to drill
How the OLAP system can address the business				down the data in the basis on other dimensions
requirements at Fishy Business: a short discussion				like Insured Party, Employee, Covered Items
of each business requirements and how the OLAP				Coverage, Third Party and Claimants.
system will support them.				This should be noted that this analysis is based
Future development: summarizes the actions that				only on the information is only for the Quarter
need to occur in order to implement the data				Four of the year 2004-05. This report shows that
warehouse design and related justifications.				the Amount which is paid to Claimants increase in
				the month on December is more than the other
2 Design of the Proposed				months in this Quarter. In 2005 IOCOR paid in
System				claims about four times more than last year,
2.1 System Architecture				which is not a good sign for the company.
In order to provide appropriate information to help				To view this data we can also generate a
the management team making better decisions,				PivotChart in Excel. This chart shows us the 3D
the IOCOR and InstantInsurance OLAP system				bar chart showing different values for each year.
has been proposed. This section provides the				We can further analyze the above given data by
system architecture and functional descriptions of				the Type of Covered Item using the following
the IOCOR OLAP system. OLAP uses a				report, which shows that Automobile Covers
multidimensional view of aggregate data to				were the one for which major portion of the
provide quick access to strategic information for				Payment (Claim Type) was made.
further analysis, (Coffing, 1998). Subsequently,
organizations like IOCOR can gain better insight				4.3 Custom Claims Snapshot Analysis
into data through consistent, fast, interactive				The purpose of Custom Claims Snapshot Analysis
access to a wide range of possible views of				is to provide information about amount paid and
information that can be shared across the				received, number of transactions done and Total
enterprise (Coffing, 1998).				Damage Claimed during 2004-2005. The report
The system architecture is shown in figure 1.				provides a hierarchical view for analyzing data.
The first part of this system is source systems.				Users are able to drill down the Year into Quarter
Source systems can be considered as Online				and Months. We are able to drill down the data in
Transaction Processing (OLTP) or legacy				the basis on dimensions like Month, Insured Party,
information systems that operate daily business				Employee, Claim Status, Coverage, Claims and
transactions at IOCOR (citation).				Policy. However this analysis can only be done on
According to Ross and Kimball (2004), data is				the Claim Type Automobile as we do not have
gathered from different source systems into a				any data for other claims type in our data
Data warehouse using ETL methods. Some				warehouse. Another possibility is that during
appropriate data cleaning and ETL tools are used				2004-05 no Damage was claimed against Home
to automate this ETL process. Microsoft SQL				Owner and Personal Article which as other type
Server has been used to build this data				of claims. So therefore to some extent this
warehouse and all the data gathered from source				analysis can not be utilized accurately.
systems is loaded onto this Microsoft SQL Server.				Following report helps the users to analyze the
Ross and Kimball (2004) depicts that the second				information related to the claims. It shows that in
part is a data warehouse. Data warehousing				2004 total damage claimed against the Collision
consists of a set of processes and databases				Minor with injuries, while in 2005 Collision Minor with
that provide data infrastructure to support				injuries was on top.
decision making. A data warehouse is
subject-oriented, integrated, time-variant and				4.4 Custom Claims Transaction Analysis
non-volatile collection of data in support of				The purpose of Custom Claims Transaction
management's decisions (citation). The data				Analysis is to provide information about amount
warehouse has been designed and implemented				paid and received, number of transactions done
by the lecturer.				and Total Damage Claimed during 2004-2005. The
Kimball (1997) at another place explains that this				report provides a hierarchical view for analyzing
data warehouse is using the database which has				data. Users are able to drill down the different
many tables in it. Some of these tables are kept				dimensions like Time, Insured Party, Employee,
there to represent the dimensions and store data				Coverage, Third Party, Claimant, Automobile,
which is related to those dimensions like				Claim Transactions and Policy. This analysis is
Automobiles, Claim, Claimant, Insured Party,				pretty much similar to the other analysis done
Employee etc. Apart from these tables there are				earlier in Claim Transactions Analysis. The only
eight other tables which are helping us to store				difference is that within this analysis users are
the information related to the fact tables like				able to browse information related to the
Claim Snapshot, Claim Transaction Fact, and Policy				Automobile dimensions and can further drill it
Transitions etc				down by their Types.
The next part is an OLAP engine implemented by				The report which is shown below confirms that
using Microsoft SQL Server Analysis Services,				Studebaker had the highest Amount figures in
which enables its users to create and manipulate				2004, while this dramatically fell down in 2005
multidimensional data structures or cubes. The				which made Studebaker as lowest in year 2005.
design of developed cubes is presented by using				On the other hand in year 2005, Lexus
Thomson diagram in the next section.				automobiles were the one which got the highest
Pearson (2005) describes that Microsoft® SQL				figures among all other Automobile Types.
ServerTM Analysis Services are used to process				4.5 Custom Snapshot Analysis
our cubes in reoccurring manner. Processing is				The purpose of Custom Snapshot Analysis is one
essential to keeping our OLAP data sources in				of the major analyses done for IOCOR. In this
sync with the data sources that they summarize.				analysis we can measure facts to provide
We can use it to automate the processing cycles,				information like Written Premium, Earned
and thus keep our cubes updated to reflect the				Premium, Primary Limit, Primary Deductible,
latest data. Rabeler (2003) at another place				Number of Transactions and Auto Replacement
depicts that in a relational database users can				Value. The report provides a hierarchical view for
analyse data stored in Analysis Services with				analyzing data. Users are able to drill down the
greater speed and flexibility than users can				different dimensions like Month, Insured Party,
querying the same data. Analysis Services queries				Employee, Coverage, Status, Automobile and
data more efficiently because it organizes data				Policy. This analysis is very important for IOCOR's
into multidimensional structures, stores				management as it has some important measures
aggregations of fact data, and stores frequently				covered in it.
used results in a memory cache for quick access.				The following report shows that the Collision 1000
Sullivan (2000) says that the final part is client				deductible is the mostly used Coverage within the
tools. Through the used of intranet and the client				year 2003-05. It should be noted that although we
server architecture of the system,				ca measuring data for year 2003 but there is no
multidimensional data cubes in MS Analysis				information available for this year and hence it's
Services, can be shared and accessed across the				not displayed here. On the other hand Uninsured
computer network. This enables the users view				motorist type carries the lowest figures of Auto
the data as a single point, which can reduce				Replacement value. Family market segment has
redundancy and inconsistency in order to make				used both the Uninsured Motorists and Collision
effective decisions from the enterprise-wide view				1000 deductible coverage types.
rather than one part of the organization that
might leads to misguided decision. Crystal Analysis,				4.6 Custom Transactions Analysis
ProClarity and Microsoft Excel are some of client				The purpose of Custom Transactions Analysis is
software, which provides fast and flexible query				to measure the Amount by making use of the
performance.				IOCOR dimensions like Time, Insured Party,
Kyd (2004) explains that after comparing				Employee, Coverage, Automobile, Transactions
Microsoft Excel with other OLAP tools we came				and Policy. The report provides a hierarchical view
to knew that Excel is relatively cheaper than				for analyzing data as all of these dimensions can
other tools available in the market. Excel is				be further drilled down to see any combination of
commonly used by the computer users and it has				information. As this analysis is capturing the
most of the functionalities users need to display				measure of Amount therefore this analysis is
data. This reduces the cost of user training.				helping the managers of IOCOR's to make some
Fielden (1999) says that Excel comes with the				important decisions related to the company. This
Microsoft Office suite. So there is no need to buy				analysis is a little bit similar to the Claim
a separate application for OLAP reporting. Excel is				Transaction Analysis and Custom Claims
best used for business operations reporting and				Transactions, which haven been discussed earlier.
goals tracking.				But this analysis is also covering information about
Figure1: the architecture of the IOCOR and				Transaction, which makes it different from the
InstantInsurance OLAP systems				other two analyses done earlier.
2.2 Dimensional Model				The above report shows that in the fourth
Kimball (1997) says that a dimension model is				quarter of year 2005 Transaction type of Create
used to show a de-normalized view of data for				Coverage has increased dramatically, which means
faster access to be used for decision making				that large number of new customers have joined
applications. This is different from the 3rd normal				the company and have taken different policies.
form. Generally the dimensional model is used for				Also in the same quarter IOCOR lost many
transactional (OLTP) type systems. This model				business customers, which is not a good sign for
consists of different Dimensions which are used				the company. This can help IOCOR's management
to store information about same category of				to develop new promotions for the companies
information e.g. Time Dimension. These dimensions				business clients so that they won't cancel their
have got Attributes in it like Year, Quarter and				policies. This report also depicts that most amount
Months. In the current scenario, the dimension				of Transactions in IOCOR's are in Modifying the
model consists of fifteen dimension tables, which				Coverage.
are as follows:				If the users want to know the information related
- Automobile				to the Employees who are doing most of these
- Claim				Transactions then they can do this by dragging
- Claim Status				and dropping the Employees filed into this
- Claim Transaction				PivotTable. The above report displays separate
- Claimant				figures each type of employees. By this report
- Coverage				users can see that mainly Transactions are for
- Covered Item				those employees which are working Field and are
- Employee				Brokers.
- Insured Party
- Month				4.7 Policy Snapshot Analysis
- Policy				The purpose of Policy Snapshot Analysis is to
- Status				measure the some measures like Written
- Third Party				Premium, Earned Premium, Primary Limit, Primary
- Time				deductible and Number of Transactions. Like other
- Transaction				analysis this analysis also provides a hierarchical
Apart form these fact tables our current scenario				view for analyzing data as all of these dimensions
consists of eight fact tables which are:				can be further drilled down to see any
- Claim Snapshot				combination of information. In this analysis we can
- Claim Transaction Fact				see the information related to the Month, Insured
- Custom Claim Snapshot				Party, Employee, Covered Item, Policy, Status
- Custom Claim Transaction				and Coverage dimensions. As this analysis is
- Custom Snapshot				capturing the measure of Amount therefore this
- Custom Transactions				analysis is helping the managers of IOCOR's to
- Policy Snapshot				make some important decisions related to the
- Policy Transactions				company. This analysis covers same measures as
2.2.1 Claim Snapshot				of Custom Snapshot Analysis. The different thing
The Claim Snapshot multidimensional structure is				here is that it is measuring the information about
comprised up of Month, Insured Party, Employee,				the Status dimension.
Covered Item, Coverage, Claim Status, Claim and				The above report shows that the Status of
Policy. Apart from the keys of each dimension,				Earned Premiums and Written premium by the
this cube has got some measures in it. These				Filed employees nearly double to the employees
measures are Reserve Amount, Paid This Month,				of Headquarters. The Written premium of the
Received This Month, Number Of Transactions.				Regular Status type is highest for both of the
This cube does not have any calculated members				Employee types.
and cells. The physical size which this cube has
occupied is 0.05Mb.				4.8 Policy Transactions Analysis
2.2.2 Claim Transaction Fact				The purpose of Policy Transactions Analysis is to
The Claim Transaction multidimensional structure is				measure the Amount by making use of the
comprised up of Claim Transaction Fact, Time,				IOCOR dimensions like Time, Insured Party,
Insured Party, Employee, Covered Item,				Employee, Covered Items, Coverage,
Coverage, Third Party, Claimant, Policy and Claim				Transactions and Policy. The report provides a
Transaction. Apart from the keys of each				hierarchical view for analyzing data as all of these
dimension, this cube has got Amount as a				dimensions can be further drilled down to see any
measure. This cube does not have any calculated				combination of information. As this analysis is
members and cells. The physical size which this				capturing the measure of Amount therefore this
cube has occupied is 0.04Mb.				analysis is helping the managers of IOCOR's to
2.2.3 Custom Claim Snapshot				make some important decisions related to the
The Custom Claim Snapshot multidimensional				company. This analysis is a little bit similar to the
structure is comprised up of Custom Claim Snap,				Claim Transaction Analysis and Custom Claims
Month, Insured Party, Employee, Coverage, Claim				Transactions, which haven been discussed earlier.
Status, Claim and Policy. Apart from the keys of				But this analysis is also covering information about
each dimension, this cube has got some measures				Transaction, which makes it different from the
in it. These measures are Reserve Amount, Paid				other two analyses done earlier.
This Month, Received This Month, Number Of
Transactions, and Total Damage Claimed. This				5 How the OLAP System
cube does not have any calculated members and				Can Address the Business Problems at IOCOR
cells. The physical size which this cube has				InstantInsurance.
occupied is 0.03Mb.				In general the information system issues faced by
2.2.4 Custom Claim Transaction				IOCOR decision makers include:
The Custom Claim Transaction multidimensional				- Systems are slow is retrieving data.
structure is comprised up of Custom Claim Trans,				- Inconsistent and inaccurate information flow
Automobile, Time, Insured Party, Employee, Claim				affecting decision making
Transaction, Coverage, Third Party, Claimant, and				- Historical data analysis is time consuming and
Policy. Apart from the keys of each dimension,				complicated to perform.
this cube has got Amount as a measure in it. This				- Inconsistent grouping of product groups and
cube does not have any calculated members and				subgroups, which makes analyzing reports difficult
cells. The physical size which this cube has				and inaccurate. Therefore, there is a need to
occupied is 0.03Mb.				categorize them correctly.
2.2.5 Custom Snapshot Fact				- The lack of correlation between the data
The Claim Transaction multidimensional structure is				sources (i.e. ERP and MIS systems)
comprised up of Custom Snapshot, Status,				- Poor quality of data leading to faulty decisions.
Automobile, Policy, Coverage, Employee, Insured				- The lack of a user-friendly interface to generate
Party and Month. Apart from the keys of each				reports and analyze data
dimension, this cube has got some measured				- The inability to perform in-depth analysis of data
facts like Written Premium, Earned Premium,				as there was not technology in place to support
Primary Limit, Primary Deductible, Number of				this.
Transactions and Auto Replacement Value. This				Ross and Kimball (2004) depicts that The business
cube does not have any calculated members and				problems will generally be overcome with the
cells. The physical size which this cube has				implementation of the OLAP system through the
occupied is 0.10Mb.				reporting of timely, accurate data captured in
2.2.6 Custom Transactions Fact				legacy systems and periodically updated in the
The Custom Transactions multidimensional				data warehouse. All users will then be looking at
structure is comprised up of Custom				reports with the same information at any given
Transactions, Time, Insured Party, Employee,				point in time.
Coverage, Automobile, Transaction and Policy.				According to Ross and Kimball (2004) the Users
Apart from the keys of each dimension, this cube				of the OLAP system will interface with it
has got Amount as a measure in it. This cube				seamlessly via the IOCOR InstantInsurance OLAP
does not have any calculated members and cells.				System. This will allow for the creation of graphs
The physical size which this cube has occupied is				and drill down functionality of the information to
0.05Mb.				report on various aggregates of data. The
2.2.7 Policy Snapshot Fact				system will ensure that data from the source
The Policy Snapshot multidimensional structure is				systems are accurately captured and loaded into
comprised up of Policy Snapshot, Month, Insured				the data warehouse to allow the formation of
Party, Employee, Covered Item, Policy, Status				timely reports to support decision making. In
and Coverage. Apart from the keys of each				addition to the described problems many other
dimension, this cube has got some measures like				unknown problems can also be solved by using
Written Premium, Earned Premium, Primary Limit,				the virtual cubes and doing ad hoc queries.
Primary Deductible and Number of Transactions.
This cube does not have any calculated members				6 Future development
and cells. The physical size which this cube has				Thomsen (1998) says that OLAP system
occupied is 0.11Mb.				development is an evolutionary process. While
2.2.8 Policy Transactions Fact				building OLAP It is necessary to look at the
The Policy Transactions multidimensional structure				business as a whole - applications, hardware,
is comprised up of Policy Transactions AS Policy				software, strategic initiatives, product directions,
Transactions, Transaction, Time, Policy, Coverage,				evolution paths for the technology etc. to
Covered Item, Employee and Insured Party.				coordinate and build a system architecture that
Apart from the keys of each dimension, this cube				will carry the company well into the future. The
has got Amount as a measure in it. This cube				OLAP system will change the understanding of
does not have any calculated members and cells.				users about their work. As their understanding of
The physical size which this cube has occupied is				their work change, their information needs change.
0.07Mb.				Mael, (1997) at one place further explains that
				when their information needs change the OLAP
3 Thomsen's Diagram				system must change. It is obvious that this is a
Thomsen's Diagram or multi dimensional domain				continuous process. The OLAP system will evolve
structure (MDS) is a diagramming technique				overtime to handle such change.
developed by Eric Thomsen. MDS is a metaphor				Managers of IOCOR need some advanced
for representing the multiple-dimensional				instruments which depict dependencies between
information spaces of an OLAP system. Unlike				process management goals. The Balanced
traditional cube, which is limited to represent as				Scorecard is a widely used instrument for
maximum as three independent dimensions, MDS				strategic management. Carickhoff (1997) says
can represent any number of dimensions. Each				that users of IOCOR may need to analyze data
dimension is depicted by a vertical line and each				multidimensionally while they are disconnected
member of a dimension is represented by a unit				from the corporate network, such as when
interval on the line.				traveling with a laptop computer. In such cases
The multidimensional structures of IOCOR and				users might want to view the cubes in the web
InstantInsurance created on the basis of Eric				browser, which needs implementation of Web
Thomson diagramming technique is follows:				based version of OLAP tools. Looking to the
				future Mael (1997) depicts that the largest hurdles
3.1 Claim Snapshot Structure				faced by OLAP vendors in delivering functionality
3.2 Claim Transaction Structure				over the Web will be the browser's procedural
3.3 Custom Claim Snapshot Structure				environment and the volumes of data that could
3.4 Custom Claim Transaction Structure				potentially be needed on the client. In the future
3.5 Custom Snapshot Structure				IOCOR might have to purchase some more
3.6 Custom Transactions Structure				sophisticated OLAP tools like ProClarity, Crystal
3.7 Policy Snapshot Structure				Analysis, and Cognos etc.
3.8 Policy Transactions Structure				Pendsel (2005) explains at one place that the
				OLAP implementations are fraught with difficulties.
4 Analysis Reports				OLAP managers have a hard time completing
According to Howson (2004), the Analysis reports				implementations, with problems appearing in batch
support decision makers by providing accurate				windows, legacy system integration, query
information for making faster and more effective				performance and data quality. Also once a
decisions. Decision makers are able to slice and				system is implemented, OLAP managers struggle
dice data to gain insights from multidimensional				to accurately predict its performance or
view. Decision makers are able to conduct a quick				adequately explain irregular outages.
analysis such as finding significant business
problems or opportunity trends.				7 References
Ellis (2004) points out that the reports for the				- Darrow, B. (2003). Cognos Revise Series 7,
current system have been generated using				ProQuest Computing
Microsoft Excel. MS Excel delivers OLAP-powered				- Ellis, D. (2004). Data Mining and Business
guided analysis that enables end users to easily				Intelligence: Where will it lead us?. InfoTech
gain insight into business data and make intelligent				Update.
decisions that impact enterprise performance.				- Fielden, T. (1999). Excel add-in eases OLAP.
Willet (1998) explains that MS Excel is powered				InfoWorld
with the PivotTables and Pivot Charts using which				- Howson, C. (2004). BI Scorecard OLAP.
a drag and drop design environment and making				Intelligent Enterprise
use of rich visualization techniques is possible. Using				- Kimball, R. (1997). A Dimensional Modeling
these PivotTables users have got the ability to				Manifesto. ProQuest Computing
select any combination of Fields and then drag				- Kyd, C. (2004). Use Business tools with Excel to
and drop them into their reports				save time and money :
The reports in the current system have been				- Mael, S. (1997). Business objects tiers up.
formed as to allow IOCOR and InstantInsurance				ProQuest Computing
to make better decisions and have a greater				- Pearson, W. (2005). Introduction to MSSQL
understanding of the performance of their				Server Analysis Services: Process Analysis
business functions. These reports identify potential				Services Cubes with DTS :
problems and significant trends which was not				- Pendsel, N. (2005). OLAP products and
possible through the company's past reporting				applications have been around for much longer
methods, causing the company to loose financially.				than most people think:
Furthermore the reports enable senior executives				- Rabeler, C. (2003). Microsoft SQL Server 2000
to effectively analyze the presented information				Analysis Service Performance Guide :
by allowing them to drill up, down, across or				- Ross, M., & Kimball, R. (2004). Surrounding
through the data as they deem necessary.				the ETL Requirements. ProQuest Computing
4.1 Claims Snapshot Analysis				- Sullivan, T. (2000). Microsoft adds data mining to
The purpose of Claims Paid and Received Analysis				its SQL Server OLAP services. FortWashington
is to provide information about all the claims which				- Thomsen, E. (1998). Music of the Cubes
are received and paid during the year 2004-2005.				revisited. USA: Database Programming and Design
The report provides a hierarchical view for				- Thomsen, E. (1997). OLAP Solutions: Building
analyzing. Users are able to drill down the Year				Multidimensional Information Systems, NY: John
into Quarter and Months. Even the Claims can be				Wiley & Son.
drilled down into claims type and claim				- Willet, S. (1998). Microsoft plans OLAP assault
description. Apart from that users are able to drill				with Excel 98, ISV products.
down the data in the basis on other dimensions

TeleClean

Iocor And Instantinsurance OLAP System

Articles