1 / 139

NIC Exposure Level Training

NIC Exposure Level Training. Vijayendra Gururao Business Intelligence Consultant. Agenda. Data warehousing Concepts - Day 1 Govt Case Study - Day 2 Defense HR Case study - Day 3 Manufacturing Case Study - Day 4 Data mining - Day 5. What to do about it . 2001. Active. Act.

roger
Download Presentation

NIC Exposure Level Training

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.

E N D

Presentation Transcript

  1. NIC Exposure Level Training Vijayendra Gururao Business Intelligence Consultant

  2. Agenda • Data warehousing Concepts - Day 1 • Govt Case Study - Day 2 • Defense HR Case study - Day 3 • Manufacturing Case Study - Day 4 • Data mining - Day 5

  3. What to do about it 2001 Active Act (Intelligent Agents) What it means Recommend 1996 (Data Mining) Action What’s important Analyze (OLAP) 1991 Focus (EIS) 1986 Summarize (MIS) Report Passive Technology Human Type of Analysis The Evolution of Business Intelligence

  4. Data Warehousing

  5. Introduction: • Definitions • Legacy Systems • Dimensions • Data Dependencies Model • Dimensional Model

  6. ShipType Shipper Ship To Product DistrictCredit OrderItem ContactLocat. ProductLine SalesOrder Cust.Locat. ProductGroup Contract ContractType Customer Contact SalesRep SalesDistrict SalesRegion SalesDivision An ER Model

  7. Why Data Warehouses? • To meet the long sought after goal of providing the user with more flexible data bases containing data that can be accessed “every which way.”

  8. OLTP vs. OLAP • OLTP (Online transaction processing) has been the standard reason for IS and DP for the last thirty years. Most legacy systems are quite good at capturing data but do not facilitate data access. • OLAP (Online analytical processing) is a set of procedures for defining and using a dimension framework for decision support

  9. The Goals for and Characteristics of a DW • Make organizational data accessible • Facilitate consistency • Adaptable and yet resilient to change • Secure and reliable • Designed with a focus on supporting decision making

  10. The Goals for and Characteristics of a DW • Generate an environment in which data can be sliced and diced in multiple ways • It is more than data, it is a set of tools to query, analyze, and present information • The DW is the place where operational data is published (cleaned up, assembled, etc.)

  11. Data Warehousing is Changing! Application requirements--not just data requirements--are now driving need. Customer Relationship Mgmt. Campaign Management ERP Call Center Knowledge Management ERP Target Marketing Supply Chain E-commerce

  12. Organization of data in the presentation area of the data warehouse • Data in the warehouse are dimensional, not normalized relations • However, data that are ultimately presented in the data warehouse will often be derived directly from relational DBs • Data should be atomic someplace in the warehouse; even if the presentation is aggregate • Uses the bus architecture to support a decentralized set of data marts

  13. Updates to a data warehouse • For many years, the dogma stated that data warehouses are never updated. • This is unrealistic since labels, titles, etc. change. • Some components will, therefore, be changed; albeit, via a managed load (as opposed to transactional updates)

  14. Basic elements of the data warehouse Operational Source Systems DataStaging Area DataPresentation Area DataAccessTools • Services: • Clean, combine, and standardizeConform DimensionsNo user query services • Data Store: • Flat files and relational tables • Processing: • Sorting and sequential processing • Data Mart #1 • DimensionalAtomic and summary dataBased on a single business process Ad hoc query tools Report Writers Analytical Applications Modeling: Forecasting Scoring Data Mining Extract Load Access DW Bus:Conformed facts and dimensions Extract • Data Mart #2 • Similar design Extract Load Access

  15. Data Staging Area • Extract-Transformation-Load • Extract: Reading the source data and copying the data to the staging area • Transformation: • Cleaning • Combining • Duplicating • Assigning keys • Load: present data to the bulk loading facilities of the data mart

  16. Dimensional Modeling Terms and Concepts • Fact table • Dimension tables

  17. Fact Tables • Fact table: a table in the data warehouse that contains • Numerical performance measures • Foreign keys that tie the fact table to the dimension tables

  18. Fact Tables • Each row records a measurement describing a transaction • Where? • When? • Who? • How much? • How many? • The level of detail represented by this data is referred to as the grain of the data warehouse • Questions can only be asked down to a level corresponding with the grain of the data warehouse

  19. Dimension tables • Tables containing textual descriptors of the business • Dimension tables are usually wide (e.g., 100 columns) • Dimension tables are usually shallow (100s of thousand or a few million rows) • Values in the dimensions usually provide • Constraints on queries (e.g., view customer by region) • Report headings

  20. Dimension tables • The quality of the dimensions will determine the quality of the data warehouse; that is, the DW is only as good as its dimension attributes • Dimensions are often split into hierarchical branches (i.e., snowflakes) because of the hierarchical nature of organizations • Product part  Product  Brand • Dimensions are usually highly denormalized

  21. Dimension tables • The dimension attributes define the constraints for the DW. Without good dimensions, it becomes difficult to narrow down on a solution when the DW is used for decision support

  22. Bringing together facts and dimensions – Building the dimensional Model • Start with the normalized ER Model • Group the ER diagram components into segments based on common business processes and model each as a unit • Find M:M relationships in the model with numeric and additive non-key facts and include them in a fact table • Denormalize the other tables as needed and designate one field as a primary key

  23. Sales Fact Time Dimension Product Dimension time_key product_key store_key dollars_sold units_sold dollars_cost time_key day_of_Week month quarter year holiday_flag product_key description brand category Store Dimension store_key store_name address floor_plan_type A Dimensional Model

  24. Kimball Methodology Conformed Dimensions

  25. Review: A Private Data Mart • A data mart containing one fact table and three dimension tables. We delivered all the tables by executing a fact build. • What if we want to add another fact table called F_Sales that will reference the three existing dimension tables?

  26. Conformed Dimensions Understand Conformed Dimensions • A conformed dimension is a dimension that is standardized across all data marts. Time Location Sales Fact Conformed Dimensions Customer Distribution Fact Distributor Promotion Product Order Fact

  27. Advantages of Conformed Dimensions • Deliver incremental data marts in a short period of time. • Independent data marts become part of a fully integrated data warehouse. • Deliver a consistent view across your business.

  28. Dimensions Promotion Distributor Customer Location Product Time Facts X X X X X Sales Fact Distribution Fact X X X X X X X Order Fact Conformed Dimensions Within Bus Architecture • Identifying and designing the conformed dimensions is a critical step in the architecture phase of data warehouse design.

  29. Design of Conformed Dimensions • A commitment to using conformed dimensions is more than just a technical consideration. It must be a business mandate. • Lay out a broad dimensional map for the enterprise. • Define conformed dimensions at the most granular (atomic) level possible. • Conformed dimensions should always use surrogate keys. • Define standard definitions for dimension and fact attributes.

  30. Granularity in Conformed Dimensions • Conformed dimensions should be defined at the most granular (atomic) level so that each record in these tables corresponds to a single record in the base-level fact table. Order FactDay IdProduct IdCustomer IdCostNumberOrdered D_TimeDayDay IdDayMonth Year D_ProductProduct IdDescriptionProduct TypeType DescriptionProduct LineLine Description D_CustomerCustomer IdLast NameFirst NameAddress

  31. Flexibility of Conformed Dimensions • Conformed dimensions are usually designed within star schema data marts. For multiple granularity fact tables, higher level views of dimensions can be used (or a snowflake table). View or Snowflake table Time Dimension Order FactDay IdProduct IdCustomer IdCostNumberOrdered CustomerCustomer IdLast NameFirst NameAddress Time(Day)Day IdDayMonth Id Period Sales FactMonth IdProduct IdCustomer IdAmountSoldRevenue ProductProduct IdDescriptionProduct TypeProduct Line Time(Month) ViewMonth IdMonthPeriod

  32. So, What is a DW? • A data warehouse is a subject-oriented, integrated, non-volatile, and time-variant collection of data in support of management’s decisionsW.H. Inmon (the father of DW)

  33. Subject Oriented • Data in a data warehouse are organized around the major subjects of the organization

  34. Integrated • Data from multiple sources are standardized (scrubbed, cleansed, etc.) and brought into one environment

  35. Non-Volatile • Once added to the DW, data are not changed (barring the existence of major errors)

  36. Time Variant • The DW captures data at a specific moment, thus, it is a snap-shot view of the organization at that moment in time. As these snap-shots accumulate, the analyst is able to examine the organization over time (a time series!) • The snap-shot is called a production data extract

  37. comparison of operational and informational systems Need for Data Warehousing • Integrated, company-wide view of high-quality information (from disparate databases) • Separation of operational and informational systems and data (for improved performance)

  38. Data Warehouse Architectures • Generic Two-Level Architecture • Independent Data Mart • Dependent Data Mart and Operational Data Store • Logical Data Mart and @ctive Warehouse • Three-Layer architecture All involve some form of extraction, transformation and loading (ETL)

  39. Generic two-level architecture L One, company-wide warehouse T E Periodic extraction  data is not completely current in warehouse

  40. Data marts: Mini-warehouses, limited in scope L T E Separate ETL for each independent data mart Data access complexity due to multiple data marts Independent Data Mart

  41. ODS provides option for obtaining current data L T E Simpler data access Single ETL for enterprise data warehouse (EDW) Dependent data marts loaded from EDW Dependent data mart with operational data store

  42. ODSand data warehouse are one and the same L T E Near real-time ETL for @active Data Warehouse Data marts are NOT separate databases, but logical views of the data warehouse  Easier to create new data marts Logical data mart and @ctive data warehouse

  43. Three-layer architecture

  44. DW Design Mainly consists of • Logical Design • Physical Design

  45. Logical Design of DW Identification of • Entities • Relationships • Attributes • Uniqe identifiers Conceptual and abstract. Results in Fact and dimension tables Created using Pen and Paper OR Modeling tools also

  46. Physical Design of DW • Conversion of data gathered in Logical design to Physical database structure • Mainly driven for query performance

  47. Logical  Physical

  48. Status Status Data CharacteristicsStatus vs. Event Data Example of DBMS log entry Event = a database action (create/update/delete) that results from a transaction

  49. Data CharacteristicsTransient vs. Periodic Data Figure 11-8: Transient operational data Changes to existing records are written over previous records, thus destroying the previous data content

More Related