Case Study Report On Star Schema 4k1n4f

Case study report

By : Krishna Nandan kumar USN:2SD12CS044 Under guidance of: Prof.Vandana Bhat

Problem definition: ABC Auto Insurance is under immense pressure from competitors due to reduce Auto Insurance prices and high risk underwriting. ABC Company has huge data resources from business operation, however, it is difficult to get required information in timely manner. ABC Company has to make major steps for informed decision making and important data analysis.

Objective ABC Company has an OLTP database which keeps records on motor vehicle insurance information. This database contains detailed information in respect of drivers, vehicle and claim information. Current database model has been designed for fast data entry and is sufficient for individual client’s specific information as well as fast transaction processing. Company critically needs to make comprehensive analysis like identification of contracts with a high loss ratio and low overall customer value. Appropriate actions needed to be taken with high risk customers, such as adjustment, loss prevention measures and in some cases contract cancellations and reduce gross claim expenditures. By making evidenced based management and informed decision, company will focus on profitable customers by lowering their s and overcoming competitive pressure. It will help company make better risk management and overall profitability for the company. ABC Company is in urgent need to utilize the existing data resources efficiently for better risk management and obtain competitive advantage in Auto Insurance Industry.

Solution ABC Company has decided to implement a Data Warehouse to leverage its data resources. ABC Company needs to reorganize the existing process of information delivery and to establish one single, unified and integrated data warehouse. A data warehouse is an integrated subject oriented, time-variant, non-volatile database that provides for decision making. In order to decision making ABC Company decided to reorganize the data into Star Schema in Data warehouse. In effect, the star schema creates near equivalent of multidimensional database schema from the existing OLTP relational database . It will help in advance data analysis for Risk management and overcoming competitive pressure.

Structure of Star Schema Star schema yield an easily implemented model for multidimensional data analysis while still preserving the relational structure on which the operation database is built. The basic star schema has four components: facts, dimensions, attributes and attributes hierarchies. The STAR schema would most likely be a read-only database due to the widespread redundancy introduced into the model.

Fact Table ABC Company has a factual data in Claim Information such as date, location, type of accident, cause of accident, liability, recovery cost. Fact tables contain the quantitative data or factual data about a business. This information is numerical, additive measurements and can consist of many columns and millions or billions of rows.

Dimensions Claim Information facts can be analyzed by dimensions such as Driver, Location, Time, and Automotive. Dimension tables are usually smaller and hold descriptive data that reflects the dimensions.

Attributes For example Driver name, Driver ID, gender, age group, race, and other attributes. Some of these attributes might relate to each other hierarchically.

Attribute Hierarchies Provide top down data Aggregation, Drill down or roll up data analysis. For example in time dimension there are Attribute hierarchies such as day, week, month, quarter, and year. When decision maker want to see company yearly claim information, then they are using year hierarchy level, they can further drill down to quarter level sales quantity, as per there needs. Same as in Location dimension is data can be analyzed by Country, Region, Province City and town.

Benefits of Data warehouse to ABC Company By organizing the ABC Company data around star scheme company can analyze information like what customers are high risk and what group of customers is profitable. What cities have more accidents ratios and what time of the year accident happens? What habit of drivers is may be considered high risk? What vehicles are considered low risk and so on.