DW PPT2

aSGuest50009

Download

Post to :

URL :

Related Presentations :

Add to Flag Embed

Insert YouTube videos in PowerPont slides with aS Desktop

Views: 125

Category: Entertainment

Presentation Description

No description available.

Comments

Presentation Transcript

Business Intelligence & Data Warehousing Solutions- A Case Study :

Slide 2:

Agenda © 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Introduction to Business Case. Problems Faced DW deployment cycle Benefits © 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved

Slide 3:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved © 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Client : US based Jewelry major with operations in multiple countries. Situation : The client had been using large scale application that dealt with major business critical application like inventory management, daily transactions, payroll role etc. With advancement in technology and IT infrastructure the client leveraged on the options of E commerce and E retailing. This lead to a handsome increase in the transactions and related data. It was not long enough when they realized that enterprise wide data had a lot of potential and which needed to be brought out and cashed upon. The million dollar question was . How ? Solution : The best way to make your data speak is to arrange it in a way that one can get hidden insight to help make better business decisions. This can be efficiently achieved by using Business Intelligence and data warehousing solutions. Thus it was agreed upon to set up a data warehouse and use it as Decision support system. Business Case

Slide 4:

Challenge : The client had loads of data fed through different transactional systems, populating into various tables. TPS had to integrate this data from multiple sources in a single data warehouse and extract information from it in a way that it could act as a decision support system. Working Towards the solution The project began with a collaboration of Trans Pacific Software (TPS) team in Mumbai and Client IT team in the USA. The inception phase started by analyzing the existing workflows and generating an initial list of user requirements, all in close collaboration. To ensure that the business vision and requirements were fully understood, the work proceeded in small iterations. This delivery model fostered frequent feedback from the business stakeholders to developers, reducing the risk and impact of the miscommunications that frequently result under the typical offshore model. Business Case © 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved

Slide 5:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved The following process was followed during the Data Ware House development cycle Business Requirement Gathering and Process Mapping Data base Audit Data Modeling ETL Process and Cube Design Design Reporting Dashboard and Report the data

Slide 6:

Slide 7:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Business Requirement Gathering and Process Mapping Business Requirement gathering through Client interview/meeting Study the system development documents. User questionnaires. Process Mapping Documenting all processes. Gap analysis between As-Is and To-be processes.

Slide 8:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Data Base Audit Determine quality of Data. - A full operational DB study was done to determine the duplicity, table structure, Indexing parameters etc. Estimate the time required for data base cleaning. - DB study revealed insights in the DB and depending on the business requirement the time required to clean the DB and uplift the quality was estimated. Estimate the amount of Normalization/de normalization to be performed. -The study also gave insights that the DB was at a 3rd level of normalization and as per the requirements was to be de normalized for faster query performance during reporting. Finalizing the Dimensions and Facts. - With sales analysis as the business requirement the related dimension and facts were finalized.

Slide 9:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Data Modeling – Logical Model Data Modeling started with first creating a Logical Data model. A logical model also called as a Conceptual model is a representation of an organization's data, organized in terms entities and relationships. It involves all the business names of the entities with no interface with the DB. Basically two types of Schema designs are possible – Star Schema or De normalized Model Snow flake Schema or Normalized Model As the analysis was to be done to determine the sales the development team came up with a Snow flake schema design with the following dimensions

Slide 10:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Product ID Stone ID Pair ID Jewelry ID Pair Id Pair Color Pair Shape Pair Weight Pair Price Fiscal Period Year Quarter Month Day Customer ID Customer Name Customer Address Customer phone Stone Id Stone Color Stone Shape Stone Weight Stone Price Product Dim Customer Dim Time Dim Stone Dim Pair Dim Product ID Customer ID Product ID Sales Revenue Sales Quantity Sales Fact Jewelry ID Jewelry Type Metal Type Primary Stone ID Jewelry Dim A Snow flake Schema data model with 6 dimensions and one Fact table for Sales Analysis

Slide 11:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Data Modeling – Physical Data Model Following the Logical model we derived the Physical Data model. A physical data model is a representation of a data design which takes into account the facilities and constraints of a given database management system.

Slide 12:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Extract ,Transform and Load (ETL) The data modeling phase was followed by the ETL activity. The ETL activity is marked by three different stages namely Extract, Transform and Load. Extraction : This process involves extracting the data from the source systems. The data was extracted and parsed thus resulting in a check if the data meets an expected pattern or structure. Transformation : This is a major stage in the whole ETL activity and is specific to particular DB. E.g.. In some DB requires few changes and thus might not require more transformation. The situation can be different for a bad quality DB. This is a stage where we set specific rules for changes to be done in the Data. A simple example will be like the Source has a date format mm/dd/yy and DW has dd/mm/yy. So the transformation stage will take care for such transformations. Some activities taken up during transformation stage are cleansing, Translating, Joining, Transposing etc.

Slide 13:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Loading The Load stage loads the data into the data warehouse. The process is set to Overwrite or additive as per business requirement. The load process contribute to the overall data quality as it interacts with database, the constraints defined in the database schema as well as triggers (for example, uniqueness, referential integrity, mandatory fields) are activated upon data loading. The TPS team set the Loading to be incremental as heavy amount of data was to be populated and the load would take long time to execute. The load was run overnight thus making the reports available every morning. TPS team used the Microsoft Business Intelligence tool 2005 (SSIS, SSAS) for the ETL activity.

Slide 14:

Slide 15:

Slide 16:

Slide 17:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Cube Design When we say that the data is loaded in a Data warehouse , it is done in a particular pattern. This pattern is a 3 dimensional lattice structure called as a Cube.

Slide 18:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Cube Design Contd… The cube is a predefined multidimensional structure which stores data in both detailed and aggregated manner. It is these cubes which are then queried upon during reporting. One can fire multiple queries on the cube to get different kind on reporting insights. In DW terms this is called as slicing and dicing of the cube. The TPS team had come up with a sales cube structure for this project. The ETL activity fed data to this cube. Over 15 different reports were queried upon from a single sales cube itself.

Slide 19:

Slide 20:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Design Reporting Dashboard and Report the data. Reporting from a Data warehouse is done directly by querying the cube database. Various reporting services are used to represent reports in Table format or Dashboards. TPS used silver light based reporting dashboards. A plethora of reports were generated by slicing and dicing the sales cube. Some sample reports can be listed as: Region wise sales revenue Product wise sales revenue Sales during a particular time period in a particular region. Sales of a particular type of product a particular season.

Slide 21:

Slide 22:

Slide 23:

© 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Benefits The insights given by the DWH set up and reporting helped the client to : Get a Birds eye view of the sales activity. Find out trends in sales and investigate reasons behind low sales cycle. Stock up correct amount of inventory to save carrying cost during slack seasons and prevent stock outs during peak ones. Save cost by phasing out low demand products. Match the market demand. Tap new markets and increase reach to more customers. Increased customer delight thus increasing customer retention ratio. Help better predications and forecast.

Corporate Office:Transpacific Software Pvt Ltd207,Madhava,BKC,Mumbai-400051, IndiaTel: 91-22-64512445/32522901www.transpacific.in :

Corporate Office:Transpacific Software Pvt Ltd207,Madhava,BKC,Mumbai-400051, IndiaTel: 91-22-64512445/32522901www.transpacific.in © 2010 Transpacific Software Pvt Ltd,Inc. All rights reserved Thank You