Slide 1BIBusiness IntelligenceInmon's Definition ExplainThe Data Warehouse ProcessPerformance Dashboards for Information DeliveryOLTP Normalized DesignOLTP Versus OLAPOLTP vs. OLAPDimensional Design ProcessSelect a business process to modelRequirementsIdentifying Measures and DimensionsA Dimensional Model for a Grocery Store SalesProduct DimensionInside a Dimension TableFact TablesFacts TableHierarchyOperations in Multidimensional Data ModelA Visual Operation: Pivot (Rotate)Date Dimension of the Retail Sales ModelStore DimensionETLPivot Table in ExcelOLAP and Data Mining Address Different Types of QuestionsUse of Data MiningAssociatesSequential PatternsClassificationsModelingData Warehouse and Business Intelligence Dr. Minder [email protected] 2010Spring 2010Data Warehouse - 2 © Minder Chen, 2004-2008BIBusiness Intelligence (BI) is the process of gathering meaningful information to answer questions and identify significant trends or patterns, giving key stakeholders the ability to make better business decisions.“The key in business is to know something that nobody else knows.”-- Aristotle OnassisPHOTO: HULTON-DEUTSCH COLL“To understand is to perceive patterns.” — Sir Isaiah Berlin"The manager asks how and when, the leader asks what and why." — “On Becoming a Leader” by Warren BennisData Warehouse - 3 © Minder Chen, 2004-2008Increasing potentialto supportbusiness decisions (MIS)Manager/executiveBusiness Analyst DataAnalystDBA MakingDecisionsData PresentationVisualization TechniquesData MiningInformation DiscoveryData ExplorationOLAP, MDA,Statistical Analysis, Querying and ReportingData Warehouses / Data MartsData Sources(Paper, Files, Information Providers, Database Systems, OLTP)Business IntelligenceData Warehouse - 4 © Minder Chen, 2004-2008Inmon's Definition Explain•Subject-oriented: They are organized around major subjects such as customer, supplier, product, and sales. Data warehouses focus on modeling and analysis to support planning and management decisions vs. operations and transaction processing.•Integrated: Data warehouses involve an integration of sources such as relational databases, flat files, and on-line transaction records. Processes such as data cleansing and data scrubbing achieve data consistency in naming conventions, encoding structures, and attribute measures.•Time-variant: Data contained in the warehouse provide information from an historical perspective.•Nonvolatile: Data contained in the warehouse are physically separate from data present in the operational environment.Data Warehouse - 5 © Minder Chen, 2004-2008The Data Warehouse ProcessData Marts Data Marts and cubesand cubesDataDataWarehouseWarehouseSourceSourceSystemsSystemsClientsClients Design theDesign the Populate PopulateCreateCreateQueryQuery Data Warehouse Data Warehouse Data Warehouse Data WarehouseOLAP CubesOLAP CubesDataData3344Query ToolsQuery ToolsReportingReportingAnalysisAnalysisData MiningData Mining2211Data Warehouse - 6 © Minder Chen, 2004-2008Performance Dashboards for Information DeliveryData Warehouse - 7 © Minder Chen, 2004-2008OLTP Normalized DesignOrdering Ordering ProcessProcessWare- Ware- househousePOS POS ProcessProcessChain Chain RetailerRetailerRetailer Retailer ReturnsReturnsRetailer Retailer PaymentsPaymentsStoreStoreProductProductBrandBrandGLGLAccountAccountClerkClerkRetail Retail CustCustCash Cash RegisterRegisterRetail Retail PromoPromoData Warehouse - 8 © Minder Chen, 2004-2008OLTP Versus OLAPOLTP Questions•When did that order ship?•How many units are in inventory?•Does this customer haveunpaid bills?•Are any of customer X’s line items on backorder?OLAP Questions•What factors affect order processing time?•How did each product line (or product) contribute to profit last quarter?•Which products have the lowest Gross Margin?•What is the value of items on backorder, and is it trending up or down over time?Data Warehouse - 9 © Minder Chen, 2004-2008OLTP vs. OLAPSource: http://www.rainmakerworks.com/pdfdocs/OLTP_vs_OLAP.pdf#search=%22OLTP%20vs.%20OLAP%22Data Warehouse - 10 © Minder Chen, 2004-2008Dimensional Design Process•Select the business process to model •Declare the grain of the business process/data in the fact table •Choose the dimensions that apply to each fact table row•Identify the numeric facts that will populate each fact table rowBusinessRequirementsData RealitiesData Warehouse - 11 © Minder Chen, 2004-2008Select a business process to model•Not business departments or business functions•Cross-functional business processes•Business events•Examples: –Raw materials purchasing –Order fulfillment process–Shipments–Invoicing –Inventory –General ledgerData Warehouse - 12 © Minder Chen, 2004-2008RequirementsData Warehouse - 13 © Minder Chen, 2004-2008Identifying Measures and DimensionsThe attribute variescontinuously: •Balance•Unit Sold•Cost•SalesThe attribute is perceived asa constant or discrete value:•Description•Location•Color•SizeDimensionsMeasuresPerformance Measures for KPIPerformance DriversData Warehouse - 14 © Minder Chen, 2004-2008A Dimensional Model for a Grocery Store SalesData Warehouse - 15 © Minder Chen, 2004-2008Product Dimension•SKU: Stock Keeping Unit •Hierarchy: –Department  Category  Subcategory  Brand  ProductData Warehouse - 16 © Minder Chen, 2004-2008Inside a Dimension Table•Dimension table key: Uniquely identify each row. Use surrogate key (integer). •Table is wide: A table may have many attributes (columns). •Textual attributes. Descriptive attributes in string format. No numerical values for calculation. •Attributes not directly related: E.g., product color and product package size. No transitive dependency. •Not normalized (star schemar). •Drilling down and rolling up along a dimension. •One or more hierarchy within a dimension. •Fewer number of records.Data Warehouse - 17 © Minder Chen, 2004-2008Fact TablesFact tables have the following characteristics:•Contain numeric measures (metric) of the business•May contain summarized (aggregated) data•May contain date-stamped data•Are typically additive•Have key value that is typically a concatenated key composed of the primary keys of the dimensions•Joined to dimension tables through foreign keys that reference primary keys in the dimension tablesData Warehouse - 18 © Minder Chen, 2004-2008Facts