Hide Notes 

Slide 1: Sales Data Warehouse Advisor : Dr. Irwin Levinstein Presented By : Kalyan Yadavalli
Slide 2: Contents      Mission of the project Need For a Data Warehouse Data Warehouse - Overview Sales Data Warehouse Conclusion
Slide 3: Mission of the project  The mission of this project is to provide strategic and tactical support to the Marketing-Sales and Advertising departments of a media company through the acquisition and analysis of data pertaining to their customers and markets. This project helps to identify areas of readership and marketing through creation of a Data Warehouse that will provide a media company with a better understanding of its customers and markets. 
Slide 4: Need For a Data Warehouse   To provide an environment where a relatively small amount of knowledge of the technical aspects of database technology is required to write and maintain queries and reports . To provide a means to speed up the writing and maintaining of queries and reports by technical personnel. For example, a query that requests the total sales income and quantity sold for a range of products in a specific geographical region for a specific time period can typically be answered in a few seconds or less regardless of how many hundreds of millions of rows of data are stored in the data warehouse database.   To make it easier, on a regular basis, to query and report data from multiple transaction processing systems ,external data sources for querying or reporting purposes. To prevent persons who only need to query and report transaction processing system data from having any access whatsoever to transaction processing system databases and logic used to maintain those databases.
Slide 5: Data Warehouse – Overview   A data warehouse is a copy of data combined from different data sources specifically structured for querying and reporting. Data warehouses support business decisions by collecting, consolidating, and organizing data for reporting and analysis with tools such as online analytical processing (OLAP) and data mining. Dimensional Modeling VS Entity-Relationship Modeling An OLTP system requires a normalized structure to minimize redundancy, provide validation of input data, and support a high volume of fast transactions. A transaction usually involves a single business event, such as placing an order or posting an invoice payment. An OLTP model often looks like a spider web of hundreds or even thousands of related tables. In contrast, a typical dimensional model uses a star design that is easy to understand and relate to business needs, supports simplified business queries, and provides superior query performance by minimizing table joins. 
Slide 6: Dimensional Modeling VS Entity-Relationship Modeling  This project uses Dimensional modeling, which is the name of the logical design technique often used for data warehouses. It is different from entity-relationship modeling. Entity relationship modeling is a logical design technique that seeks to eliminate data redundancy while Dimensional modeling seeks to present data in a standard framework that is intuitive and allows for high-performance access. For example, a query that requests the total sales income and quantity sold for a range of products in a specific geographical region for a specific time period can typically be answered in a few seconds or less regardless of how many hundreds of millions of rows of data are stored in the data warehouse database.  
Slide 7: Entity–Relationship Modeling Customer Demographics CustomerSubscriptions Salesperson Payment SalesConditions Channel Campaign Offer Campaign History District City Carrier Master Carrier History Zones
Slide 8: Dimensional Modeling Dimensions Customer Fact Table Subscription Sales EffectiveDateKey CustomerKey SubscriptionsKey PaymentKey CampaignKey SalesPersonKey RouteKey Demographics Key UnitsSold DollarsSold DiscountCost PremiumCost Dimensions Date Payment Subscriptions Campaign Salesperson Route Demographics
Slide 9: Kimball- Dimensional life cycle diagram Technical Architecture Design Product Selection & Installation Business Project Planning Requirement Definition Dimensional Modeling Physical Design Data Staging Design & Development Deployment Maintenance and Growth End-User Application Specification End-User Application Development Project Management
Slide 10: Sales Data Warehouse           Business Users Requirements Technical Architecture Product [ Software] Selection Dimensional Modeling Logical Design Data Staging Design & Development Building Data Cube using SQL Analysis Services End User Application Specification & Development Deployment Maintenance & Growth
Slide 11: Requirements Gathering This phase involves the following steps:  Collect some business questions the users want an answer for.  Gather details/requirements from the business users  Get user sign off on the business questions. Business Questions:  Can we profile our "best subscribers" to pull lists of "like" non-subscribers that we could touch in some way?  Who exists in the marketplace and have we touched them?  Can we build a loyalty model based on a subscriber's payment history?
Slide 12: Sales Data Warehouse High Level Technical Architecture Source Systems Data Staging Area Presentation Area Subscription Sales Dimensional. Atomic and summary data. Business process. Design Goals Ease-of-use. Query performance. Dimensional Bus: Conformed facts and dimensions Data Access Tools Marketing/Sales Data Extract Services Transform from source to target. Maintain conformed dimensions. Load Data Storage Flat files or relational tables Design Goals Staging throughput. Integrity and consistency. SQL Reporting Services Excel Access Access Demographics Extract Name Phone Data Extract
Slide 13: Product Selection Hardware Specs:  AMD Opteron Processor 252  2.6 GHz, 3.83 GB RAM  Operating System: Windows Server 2003 Software Specs:  Kimball Data Warehouse Tool [Create staging and production databases]  Microsoft ® SQL Server™ 2000 [ETL { Extract Transform Load} ]  Microsoft ® SQL Server™ 2005 Integration Services [ Nightly Automation]  Microsoft ® SQL Server™ 2005 Analysis Services [ Create OLAP Data Cube]  Microsoft ® SQL Server™ 2005 Reporting Services[ End User Reports]  Internet Information Services [ IIS 6.0] [ Web Server to Host the Reports]
Slide 14: Dimensional Modeling  Design Dimensions   Attributes of the dimension Hierarchy in the dimension   Dimensional Bus Matrix Design Fact Tables
Slide 15: Dimension Hierarchy-Subscriptions Subscription Name Rate Service Term Publication Rate Year Rate Area Rate Type Frequency Groups Term Length Groups Business Group Discount Category Short or Long Term
Slide 16: Dimensional Bus Matrix Dimensions Date Demographics Sales Salesperson Conditio ns Campaign …… Business Processes Subscription Sales ( starts) Subscription Tracking Complaints Stops Upgrades And Downgrades X X X X X X X X X X X X X X X X X X X X
Slide 17: SubscriptionSales Fact Table Design Fact Tables •Choose the Business Process as the fact table •Declare the grain •Choose the dimensions •Choose the facts EffectiveDateKey CampaignKey SalespersonKey CustomerKey DempgraphicsKey SubscriptionKey ……………….. Grain: Each subscription sold Facts Units Sold Number of Sales (=1) Dollars Sold Discount Cost Premium Cost
Slide 18: Dimension Model: SubscriptionSales Date Sales Conditions Customer Subscription Sales Campaign Grain: Each subscription sold LoyaltyPayment Subscr. Units Sold Sales Number of Sales (=1) Dollars Sold (starts) Demographics Discount Cost Premium Cost Measures Subscription Address Route Salesperson
Slide 19: Logical Design  Fact Table Design Dimension Table Design Slowly Changing Dimensions Type 1: The new record replaces the original record. No trace of the old record exists.  Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two people.   
Slide 20: Fact Table -SubscriptionSales Column Names Data Type N Ke U y? L L ? N FK N FK N FK N FK N FK N FK N FK N FK N FK N FK FK TO Dimension Description EffectiveDateKe y EnteredDateKey CustomerKey LoyaltyKey PaymentKey SalesPersonKey CampaignKey SalesConditions Key SubscriptionKey PersonKey int int int int int int int int int int DimDate DimDate DimCustomer DimLoyalty DimPaymentHistory DimSalesPerson DimCampaign DimSalesConditions DimSubscription dimPerson Key of effective date Key of date entered in the system Key of customer Key of loyalty score Key of payment behavior Key of sales person for the change Key of campaign Key of sales conditions Key of subscription Key of person on the subscription
Slide 21: Dimension Table - CustomerSubscriptions Column Names Data Type N U L L ? N Key? FK TO Dimension Description Slowly changin g dimensio n type CustomerSubscriptionK ey AddressNum int PK ID Surrogate Primary Key int Y BK Business key of the subscription summary record address ` Business key of the numbered subscription at the address Concatenated business key SubscriptionNum int Y BK BusinessKey int Y BK CustomerID int N Unique identifier for this customer 1 BillingMethod int The method of delivery for the customers bill 2 OriginalStartDateKey int FK DimDate The earliest start date on record for this customer The start date of this customer’s current subscription The most recent stop date for this customer 1 StartDateKey int FK DimDate 2 StopDateKey int FK DimDate 2 ExpireDateKey int FK DimDate The expiration date of this customer’s current or most recent subscription 2
Slide 22: Data Staging The following are the sub processes of Data Staging process. Extracting : Reading and understanding the source data, copying the parts that are needed to the staging area. Transforming: Possible transformation steps in the data staging area      Cleaning the data – correct misspellings, deal with missing data elements, parsing into standard formats. Purging selected data which is not required Combining data sources, by matching exactly on key values or performing fuzzy matches on non-key attributes. Creating surrogate keys for each dimensional record. Building aggregates to boost performance of common queries. Loading – Loading the transformed data into the production database.
Slide 23: Data Staging -ETL Architecture Source Schema Creates the tables for the common source database Source Queries Creates views (queries) that feed data to production Kimball Data Modeling tool Creates the staging and production database tables and metadata. Common Source Database Staging Database Production Data Warehouse Database Setup files on SQL server that is running the DTS packages. DTS Data Transformation Services Package Database Configuration File Database connection information for the ETL process Dates Configuration File Important date info for the ETL process
Slide 24: Data Staging –Development for Sales Data Warehouse The ETL packages perform the following work.  Extract the full sets of dimension rows  Most transformation logic occurs in the extract query, using SQL  Extracted rows are stored in a staging table until the ETL package is run.  There are steps for the staged rows to be fixed up, via SQL statements. There’s a statement for deleting bogus rows, and a separate statement for updating rows.  Find rows that are new; insert them into the target table.  Use a checksum to find rows that have seen a Type 1 change. Update the appropriate columns in the target table.  Use a checksum to find rows that have seen a Type 2 change. Propagate a new dimension row.  Log the number of rows extracted, staged, deleted and updated from the staging tables, inserted into target, Type 1 and Type 2 rows updated in target.
Slide 25: Dimension-Customer Subscription
Slide 26: Building Data Cube using SQL Analysis Services  SQL Server Analysis Services 2005 provides tools for developing OLAP applications OLAP [ Online Anlytical Processing ] organizes data warehouse data into multidimensional cubes based on the dimensional model, and then preprocesses these cubes to provide maximum performance for queries that summarize data in various ways. Build the cube using SQL Analysis Services and deploy it to SQL Analysis Services Server.  
Slide 28: End User Application Development Reporting Services - Uses the Analysis Services Data Cube as Data Source.  Sample Report Screenshots 
Slide 29: Solicitor Sales Sales Type Carrier Sales Carrier Sales Carrier Sales DM Sales DM Sales DM Sales NonSolicited Other 2005 THISISHAMPRDS FREE2WKSAMP AD CONTRACT START ALLCONNECT CAN'T AFFORD DM "2 WKS FREE" COLLECTIONS TEAM COOLSAVINGS.COM CUSTOMER SERVICE PROMO STARTS DATA ENTRY STARTS 8 8 1,914 7 15 9,415 1,934 49 $0.00 $0.17 $0.04 $0.10 $0.14 $21.55 $0.15 $0.14 12.5% 62.5% 43.5% 71.4% 73.3% 7.1% 73.7% 59.2% 124 $0.13 13.7% 2 $0.00 50.0% Sales Channel Sales Agent Number Of Sales Cost Per Unit Retention
Slide 30: Sales Agent Details Sales Agent AD CONTRACT START AD CONTRACT START AD CONTRACT START AD CONTRACT START AD CONTRACT START AD CONTRACT START AD CONTRACT START AD CONTRACT START Source Name ULTIMATE TAN OF SMITHFIELD CHOREY & ASSOCIATE CHOREY AND ASSOCIATE VIRGINIA STAGE CO SPINE & ORTHAPEDIC CTR, PC COUNTRYWID E HOME LOAN WHITE, E.D. Phone Address St City ZI P 234 30 234 34 234 34 235 10 235 02 234 34 234 51 234 34 # Sale s 1 Units Sold 260 $ Sold $30.9 9 $30.9 9 $30.9 9 $30.9 9 $30.9 9 $30.9 9 $30.9 9 $30.9 9 Discou nt Cost $37.91 Premiu m Cost $5.00 (757) 3659400 (757) 5397451 (757) 5397454 0 13412 BENNS CHURCH BLVD 330 W CONSTANCE RD # 100 804 W WASHINGTON ST 254 GRANBY ST VA SMITHF IELD SUFFOL K SUFFOL K NORFO LK NORFO LK SUFFOL K VIRGINI A BEACH SUFFOL K VA 1 260 $37.91 $5.00 VA 1 260 $37.91 $5.00 VA 1 260 $37.91 $5.00 0 6160 KEMPSVILLE CIR # 303A 3000 WOODLAWN DR 730 10TH ST VA 1 260 $37.91 $5.00 0 VA 1 260 $37.91 $5.00 0 VA 1 260 $37.91 $5.00 PERMANENT COATING SOLUTIONS IN (757) 5394366 434 N MAIN ST # D VA 1 260 $37.91 $5.00 8 2,080 $247. 94 $303.26 $40.00
Slide 31: Deployment Deploy the Reports to SQL Reporting Services 2005 server.  Give Access to the users to view the reports.  Desktop Installation – Dot Net Framework 2.0 For access to Report Builder. 
Slide 32: Maintenance & Growth Training the End Users.  Automated Nightly Updates to Data Warehouse. 
Slide 34: Conclusion The reports generated from the data warehouse answered the following questions collected form the business users during the requirement gathering phase of the project. Identify their best customers/loyal customers [ customer subscriptions /subscription sales]  Non-subscribers who can be reached  Contact history of customers in market place [ Demographic data]  Benefits to Marketing Increased telemarketing close rates and increased direct mail response rates  Reduced cost and use of outside telemarketing services and reduced print and mailing costs  Identification of new product bundling and distribution opportunities  Increased acquisition and retention rates, and reduced cost of acquisitions  Benefits to Advertising An increase in the annual rate of revenue growth.  Increase in new advertisers  Improved targeting capabilities  .
Slide 35: QUESTIONS ?