1 / 43

Data WareHouse Introduction by InformaticaTrainingClasses

We offer online IT training with placements, project assistance in different platforms with real time industry consultants to provide quality training for all it professionals, corporate clients and students etc. Special features by INFORMATICA TRAINING CLASSES are Extensive Training will be in both Informatica Online Training and Placement. Emphasis is given on important topics which are essential and mostly used in real time projects. INFORMATICA TRAINING CLASSES is an Online Training Leader when it comes to high-end effective and efficient I.T Training. We have always been and still are focusing on the key aspects which are providing utmost effective and competent training to both students and professionals who are eager to enrich their technical skills. Training Features at INFORMATICA TRAINING CLASSES: We believe that online training has to be measured by three major aspects viz., Quality, Content and Relationship with the Trainer and Student. Not only our online training classes are important but apart from that the material which we provide are in tune with the latest IT training standards, so a student has not to worry at all whether the training imparted is outdated or latest. Course content: • Basics of data warehousing concepts • Power center components • Informatica concepts and overview • Sources • Targets • Transformations • TYPES Of LookUp Caches • Task Types • Advanced Informatica concepts • Informatica Coding Standards & Best Practices used in real time development Please Visit us for the Demo Classes, we have regular batches and weekend batches. INFORMATICA ONLINE TRAINING CLASSES Phone: (404)-900-9988 Email: info@informaticatrainingclasses.com Web: http://informaticatrainingclasses.com

Download Presentation

Data WareHouse Introduction by InformaticaTrainingClasses

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.


Presentation Transcript

  1. Introduction to Data WarehousingBYInformaticaTrainingClassesphone :(404)-900-9988email : info@informaticatrainingclasses.comwebsite :www.informaticatrainingclasses.com

  2. Data Warehouse • Maintain historic data • Analysis to get better understanding of business • Better Decision making • Definition: A data warehouse is a • subject-oriented • integrated • time-varying • non-volatile collection of data that is used primarily in organizational decision making. -- Bill Inmon, Building the Data Warehouse 1996

  3. Subject Oriented • Data warehouse is organized around subjects such as sales, product, customer. • It focuses on modeling and analysis of data for decision makers. • Excludes data not useful in decision support process.

  4. Integrated • Data Warehouse is constructed by integrating multiple heterogeneous sources. • Data Preprocessing are applied to ensure consistency. RDBMS Data Warehouse Data Processing Data Transformation Legacy System Data Processing Data Transformation Flat File

  5. Non-volatile • Mostly, data once recorded will not be updated. • Data warehouse requires two operations in data accessing • Incremental loading of data • Access of data load access

  6. Time Variant • Provides information from historical perspective e.g. past 5-10 years • Every key structure contains either implicitly or explicitly an element of time

  7. Why Data Warehouse? Problem Statement: • ABC Pvt Ltd is a company with branches at USA, UK,CANADA,INDIA • The Sales Manager wants quarterly sales report across the branches. • Each branch has a separate operational system where sales transactions are recorded.

  8. Why Data Warehouse? USA UK Get quarterly sales figure for each branch and manually calculate sales figure across branches. Sales Manager CANADA INDIA What if he need daily sales report across the branches?

  9. Why Data Warehouse? Solution: • Extract sales information from each database. • Store the information in a common repository at a single site.

  10. Why Data Warehouse? USA Data Warehouse UK Query & Analysis tools Sales Manager CANADA INDIA

  11. Characteristics of Data Warehouse • Relational / Multidimensional database • Query and Analysis rather than transaction • Historical data from transactions • Consolidates Multiple data sources • Separates query load from transactions • Mostly non volatile • Large amount of data in order of TBs

  12. When we say large - we mean it! • Terabytes -- 10^12 bytes: • Petabytes -- 10^15 bytes: • Exabytes -- 10^18 bytes: • Zettabytes -- 10^21 bytes: • Zottabytes -- 10^24 bytes: Yahoo! – 300 Terabytes and growing Geographic Information Systems National Medical Records Weather images Intelligence Agency Videos

  13. OLTP Vs Data Warehouse (OLAP)

  14. Data Warehouse Architecture Operational System ETL (Extract Transform and Load) Data Warehouse Sales Data Mart Analysis Operational System Generic Data Mart Flat Files Data Mining Inventory Data Mart Flat Files Reporting

  15. ETL ETL stands for Extract, Transform and Load • Data is distributed across different sources • Flat files, Streaming Data, DB Systems, XML, JSON • Data can be in different format • CSV, Key Value Pairs • Different units and representation • Country: IN or India • Date: 20 Nov 2010 or 20101020

  16. ETL Functions • Extract • Collect data from different sources • Parse data • Remove unwanted data • Transform • Project • Generate Surrogate keys • Encode data • Join data from different sources • Aggregate • Load

  17. ETL Steps • The first step in ETL process is mapping the data between source systems and target database. • The second step is cleansing of source data in staging area. • The third step is transforming cleansed source data. • Fourth step is loading into the target system. • Data before ETL Processing: • Data after ETL Processing:

  18. ETL Glossary Mapping: Defining relationship between source and target objects. Cleansing: The process of resolving inconsistencies in source data. Transformation: The process of manipulating data. Any manipulation beyond copying is a transformation. Examples include aggregating, and integrating data from multiple sources. Staging Area: A place where data is processed before entering the warehouse.

  19. Dimension • Categorizes the data. For example - time, location, etc. • A dimension can have one or more attributes. For example - day, week and month are attributes of time dimension. • Role of dimensions in data warehousing. • Slice and dice • Filter by dimensions

  20. Types of dimensions • Conformed Dimension - A dimension that is shared across fact tables. • Junk Dimension - A junk dimension is a convenient grouping of flags and indicators. For example, payment method, shipping method. • De-generated Dimension - A dimension key, that has no attributes and hence does not have its own dimension table. For example, transaction number, invoice number. Value of these dimension is mostly unique within a fact table. • Role Playing Dimensions - Role Playing dimension refers to a dimension that play different roles in fact tables depending on the context. For example, the Date dimension can be used for the ordered date, shipment date, and invoice date. • Slowly Changing Dimensions - Dimensions that have data that changes slowly, rather than changing on a time-based, regular schedule.

  21. Types of Slowly Changing Dimension • Type1 - The Type 1 methodology overwrites old data with new data, and therefore does not track historical data at all. • Type 2 - The Type 2 method tracks historical data by creating multiple records for a given value in dimension table with separate surrogate keys. • Type 3 - The Type 3 method tracks changes using separate columns. Whereas Type 2 had unlimited history preservation, Type 3 has limited history preservation, as it's limited to the number of columns we designate for storing historical data. • Type 4 - The Type 4 method is usually referred to as using "history tables", where one table keeps the current data, and an additional table is used to keep a record of all changes. Type 1, 2 and 3 are commonly used. Some books talks about Type 0 and 6 also. http://en.wikipedia.org/wiki/Slowly_changing_dimension

  22. Facts • Facts are values that can be examined and analyzed. • For Example - Page Views, Unique Users, Pieces Sold, Profit. • Fact and measure are synonymous. • Types of facts: • Additive - Measures that can be added across all dimensions. • Non Additive - Measures that cannot be added across all dimensions. • Semi Additive - Measures that can be added across few dimensions and not with others.

  23. How to store data? Facts and Dimensions: • Select the business process to model • Declare the grain of the business process • Choose the dimensions that apply to each fact table row • Identify the numeric facts that will populate each fact table row

  24. Dimension Table • Contains attributes of dimensions e.g. month is an attribute of Time dimension. • Can also have foreign keys to another dimension table • Usually identified by a unique integer primary key called surrogate key

  25. Fact Table • Contains Facts • Foreign keys to dimension tables • Primary Key: usually composite key of all FKs

  26. Types of schema used in data warehouse • Star Schema • Snowflake Schema • Fact Constellation Schema

  27. Star Schema • Multi-dimensional Data • Dimension and Fact Tables • A fact table with pointers to Dimension tables

  28. Star Schema

  29. Snowflake Schema • An extension of star schema in which the dimension tables are partly or fully normalized. • Dimension table hierarchies broken down into simpler tables.

  30. Snowflake Schema

  31. Fact Constellation Schema • A fact constellation schema allows dimension tables to be shared between fact tables. • This Schema is used mainly for the aggregate fact tables, OR where we want to split a fact table for better comprehension. • For example, a separate fact table for daily, weekly and monthly reporting requirement.

  32. Fact Constellation Schema In this example, the dimensions tables for time, item, and location are shared between both the sales and shipping fact tables.

  33. Operations on Data Warehouse • Drill Down • Roll up • Slice & Dice • Pivoting

  34. Drill Down Product Category e.g Home Appliances Sub Category e.g Kitchen Appliances Product e.g Toaster Region Time

  35. Roll Up Year Fiscal Year Quarter Fiscal Quarter Month Fiscal Month Fiscal Week Day

  36. Slice & Dice Product = Toaster Product Region Region Time Time

  37. Pivoting Product Product • Also called rotation • Rotate on an axis • Interchange Rows and Columns Time Region Region Time

  38. Advantages of Data Warehouse • One consistent data store for reporting, forecasting, and analysis • Easier and timely access to data • Scalability • Trend analysis and detection • Drill down analysis

  39. Disadvantages of Data Warehouse • Preparation may be time consuming. • High associated cost

  40. Case Study: Why Data Warehouse • G2G Courier Pvt. Ltd. is an established brand in courier industry which has its own network in main cities and also have sub contracted in rural areas across the country to various partners. • The President of the company wants to look deep into the financial health of the company and different performance aspects.

  41. Challenges • Apart from G2G’s own transaction system, each partner has their own system which make the data very heterogeneous. • Granularity of data in various systems is also different. For eg: minute accuracy and day accuracy. • To do analysis on metrics like Revenue and Timely delivery across various geographical locations and partner, we need to have a unified system.

  42. Data warehouse model Product Product Category Time Sales Fact Region

  43. Thank You

More Related