Part I :
Provide your responses to the following:
- List the differences data warehouses and datamarts?Briefly discuss the reasons Data Warehouses are used?
- Explain the concept "Dimensional Modeling". Differentiate between dimensions vs facts; star vs snowflake schemas
Part II:
Review the attached file. Lab 4 description and instructions begin on Slide 14.
Using the details provided,create the data model
- What are the dimension tables?
- What are the fact table?
- Design the table schemas
What to Submit:
A Word document with all three components of the data model
Naming convention of the file:firstnameLastname_Lab4.docx
CIS 345 Java Programming BCS 425 Business Intelligence & Data Warehousing - Lab 4 - Scenario Mid—size company that manufactures sporting goods and related accessories. More recently, sports-related clothing Their customers are large retail companies around the world. Business Problem Problems With Current System Info Restricted Available information is restricted by the current enterprise resource planning systems’ functionality. Difficult Difficult for end users to access operational information. Slow and Costly Building new reports is slow and costly. Business Problem Problem With Current System (continued) No “end-user” reporting. The promise of “end-user reporting” has never been realized because there are multiple systems. Complex Each system has information spread across multiple tables and requires multiple tables and expert knowledge to answer simple business questions. Business Problem Because of these problems… They Are Falling Behind Falling behind competitively because business processes cannot keep up with change. It is Difficult to Give People Info IT is finding it difficult to give people the information that they need to keep up with a changing business environment. Business Problem Because of these problems…(continued) Long Time For Solutions Takes long time for IT to deliver solutions due to the complexity of the system. Expensive Expensive to answer simple one-off, what-iif questions. Poor Performance Using the ERP system for reporting is hurting the transaction systems’ performance during peak reporting periods. Solution Overview Solution Build a data warehouse The DW should consolidate data from the ERP system and other sources DW should focus on functionality to support the highest-priority business objectives while providing a platform for future enhancements. DW should allow for future enhancements. Business Requirements The High-level requirements to support the key business objectives are: Sales reporting and performance tracking Profitability Business Requirements 1. Sales reporting and performance tracking. Timely and Flexible Need timely and flexible access to sales information (units shipped, revenue). Understand Sales Performance Need the ability to understand sales performance by territory, by product, and by customer. Goal: Track the actual sales achieved against the targets for each sales territory. Business Requirements 2. Profitability Profitability Analysis The solution needs to include enough information to enable profitability analysis. We need: Manufacturing costs Shipping costs Any discounts offered to customers. This will enable the business to understand profitability of product lines as well as types of customers. Business Requirements High-Level Architecture Primary component of our solutions is a data warehouse. Need to build a data warehouse database that will support our business requirements. Business Benefits How will the business benefit? Supports Business Objectives IT will be better able to support business objectives by delivering new types of information and analyses quickly and cost-effectively. Better Performance The solution will provide better performance for the ERP system by moving reporting to the data warehouse. Data Model We know the problem. We have a general idea about the solution. Now we need to come up with a data model for our solution. Lab 4 I am the business. I am frustrated by my inability to make business decisions. I have come to you for a solution. You are the Decision Support Department. You will be creating the data model and the database schema for a data warehouse that will meet my needs. Some things to consider… Lab 4 Dimensional Data Modeling Goal Produce a simple consistent model that is expressed in the users’ own language and will enable them to easily access information. Key Tenets of Approach To Data Model: You should… … carefully define the areas you will be focusing on delivering so that they align with the objectives. … deliver actual business value. Lab 4 Lab 4 The company: A Manufacturing system– The manufacturing department has information about how much it costs to create our products. Ordering and Invoicing systems – Information about much we charged this customer, taking into account discounts and other contract terms. Delivery system – Tracking information about when the goods were shipped, when they were delivered, and the condition upon delivery. This data is in different tables and in totally separate database systems. Lab 4 Sales and profitability reporting is the focus of my business needs The data model should support this business requirement. You will also design the dimensions so that they can become the “one version of the truth” . If you design this way then the business can eventually use the solution in other areas. Lab 4 IMPORTANT TIP Do not constrain the DW Design by the OLTP design You should design the data warehouse from the perspective of a business user. What granularity is needed for the fact table? What measures are needed in the fact table? How many fact tables will there be? What dimensions are needed? What attributes are needed in each dimension? Granularity Lab 4 – Did you consider…? Do not mix granularities in a Fact table!!! A single fact table must never contain measures at different levels of granularity. Sometimes business users want to compare the actual measure vs. the forcast (or budget) measure. Budgets are usually produced at a higher level of granularity (for example, planned monthly sales per product group). Measures of different granularity must be stored in different fact tables. Lab 4 – Questions to Consider What level of detail do we need? Daily or monthly totals? Individual products or summary by category? Rule of thumb Strive to use the most detailed level of information that is available. Lab 4 – Questions to Consider How do you identify the facts and dimensions? Take note of every time the business user says the word “by”. For example, “I need to see sales by manufacturing plant, and I need to look at deliveries by the method they were shipped to see which method is more cost-effective”. Dimensions 1. Manufacturing plant 2. Delivery method Lab 4 – Did you consider…? Facts What are we measuring? Measurable information will end up in a fact table. Get measures by thinking about the business questions that need to be answered. What measures do we need to answer the business questions? For example: Sales amount, costs, discounts. Lab 4 – Dimensions Using the details provided, create the data model What are the dimension tables? What are the fact table? Design the table schemas In the next few slides, I have provided examples of each What to Submit: A Word document with all components of the data model Be sure to properly name the file Examples to help with the Lab Lab 4 – Dimensions Dimensions Plant Ship Method . . . Lab 4 – Facts FACTS SalesFact . Lab 4 – Facts FactShipments PlantKey(FK1) ShipMethodKey(FK2) ProductKey(FK3) ShippingCustomerKey(FK4) BillingCustomerKey(FK5) OrderDateKey(FK6) DueDateKey(FK7) ShipDateKey(FK5) SalesOrderNumber SalesOrderLineNumber InvoiceNumber InvoiceLineNumber OrderQuantity DeliveryQuantity DiscountAmount TotalCost SalesAmount TaxAmount Freight Sales Fact Table Attributes Lab 4 – Table Schemas Assume these interviews have produced the following two dimensions: DimPlant PlantKey (PK) PlantBusinessKey PlantName DivisionName DimShipMethod ShipMethodKey (PK) ShipMethodBusinessKey ShipMethodName ShipBaseCost ShipRate Lab 4 – DimPlant Table DimPlant Column NameData TypeAllows Nulls PlantKey (PK)TinyintNo PlantBusinessKeyNvarchar(10)No PlantNameShortDescripton:Nvarchar(50)No DivisionNameLongDescription:Nvarchar(100)Yes Lab 4 – DimShipMethod Table DimShipMethod Column NameData TypeAllows Nulls ShipMethodKey (PK)IntNo ShipMethodBusinessKeyChar(3)No ShipMethodNameNvarchar(50)Yes ShipBaseMoneyYes ShipRateMoneyYes Lab 4 – DimTime Table DimTime Column NameData TypeAllows Nulls TimeKey (PK)DatetiimeNo Date_NameNvarchar(50)Yes YearDatetimeYes Year_NameNvarchar(50)Yes QuarterDatetimeYes Quarter_NameNvarchar(50)Yes MonthDatetimeYes Month_NameNvarchar(50)Yes Day_of_YearIntYes Day_of_Year_NameNvarchar(50)yest Things to Consider Technical Solution Some Data Warehouse Naming Standards: Table name prefix Use a prefix that describes the type of table. For example, DimShipMethod or FactShipping. Use “camel casing” Capitalize each word in a column or table name (no spaces or underscores). For example, ShipMethod rather than ship_method or ship method. Technical Solution Data Warehouse Naming Standards (con.) Suffix for surrogate key columns Each surrogate key column should have a “Key” suffix in its name. For example, ShipMethodKey. OR Each surrogate key should have the sk prefix in its name. For example, skShipMethod. Suffix for business key columns Each business key column could have a “BusinessKey” suffix in its name. For example, ShipMethodBusinessKey. OR Each business key column could have the key prefix in its name. For example, keyShipMethod. Managing the Solution Planning Test and Audit There should be a test and audit of both the numbers and dimension structures. Unverifiable Data If there is data that you cannot verify then leave it out of the data warehouse completely for the first release. Add Later Try and add any excluded data in a second release. Managing the Solution Database Administration Simple Recovery Use the simple recovery model in SQL Server (saves only data files). No need for the full recovery model that stores a backup of ALL transactions that occurred on the database. Managing the Solution Database Administration (con.) Separate Data and Log Store data and log files on physically separate disks. Improves the performance of loading the data warehouse. Managing the Solution Database Administration (con.) Create a maintenance plan that will: Create backups Check database integrity Rebuild indexes A maintenance plan can be scheduled to run automatically. We have only scratched the surface of a full manufacturing plan. Our goal is to achieve business value by focusing on delivering solutions that work. No need to try and write the all encompassing solution in one shot. An iterative solution – bite off small chunks that will deliver business value while creating a flexible solution that can be improved upon and added to over time. End of Lab 4