ISQS 3358, Business Intelligence Creating Data Marts

1. ISQS 3358, Business IntelligenceCreating Data Marts Zhangxi Lin Texas Tech University

2. Why need Data Mart? • Data mart complements the centralized data warehousing based on UDM model, for the situations where UDM cannot be used • Legacy databases • Data are from nondatabase sources • No physical connection the centralized data warehouse • Data are not clean

3. Data Mart Structures • Measures • Dimensions and Hierarchies • Attributes (or columns) • Dimensional modeling – Stars and Snowflakes

4. Dimensional Modeling Process • High level dimensional model design • Choosing business model • Declaring the grain • Choosing dimensions • Identifying the facts • Detailed dimensional model development • Dimensional model review and validation • IS • Core users • Business community • Final design iteration

5. Maximum Miniatures Manufacturing – Designing Data Mart • General business needs • To analyze the statistics available from the manufacturing automation systems. The VP would like an interactive analysis tool, rather than printed reports, for the analysis. • The manufacturing automation system controls all the machines to create figurines • Filling a mold with the raw material • Aiding the hardening of this materials • Removal from the mod when hardening is complete • Computerized painting of the figurines • Curing the paint if necessary

6. Maximum Miniatures Manufacturing – Creating Data Mart • Specific Business Needs • Analyzing the following numbers • Dollar value of products sold • Number of products sold • Sale tax charged on products sold • Shipping charged on products sold • These numbers should be viewable by: • Store • Sales Promotion • Product • Day, Month, Quarter, and Year • Customer • Sales Person

7. Data Requirements • Number of accepted products by batch by product by machines by day • Number of rejected products by batch by product by machines by day • Elapsed time for molding and hardening by product by machine by day • Elapsed time for painting and curing by curing type by product by machine by day • Product rolls up into product subtype, which rolls up into product type • Machine rolls up into machine type, which rolls up into country • Day rolls up into month, which rolls up into quarter, which rolls up into year • The information should be able to be filtered by machine manufacturer and purchase date of the machine

8. Business Need of Sales • The VP of sales for Max Min, Inc. would like to analyze sales information. This information is collected by three OLTP systems: the Order Processing System, the Point of Sale (POS) system, and the MaxMin.com Online system. • To analyze the following numbers • Dollar value of products sold • Number of products sold • Sales tax charged on product sold • Shipping charged on product sold • These number should be viewable by: store, sales promotion, product, time, customer, sales person

9. Snowflake Schema of the Data Mart Manufacturingfact DimBatch DimMachine DimProduct DimMachineType DimPlant DimProductSubType DimMaterial DimCountry DimProductType

10. Exercise 3 – Creating a data mart • Learning Objectives • How to design a dimensional model • How to create a data mart with SSMS • How to create a cube for a data mart. • Tasks • Manually create the fact table and DimProduct table using SSMS • Import remaining tables from oredb.lin.mmm.empty • Define the primary keys of tables and the relationships among them • Create a cube • Deploy the cube • Deliverable: • A Word file with the screenshot of the star schema and the success of the deployment will be emailed to zhangxi.lin@hotmail.com • The subject of the email is: “ISQS 3358 Exercise 3”

11. Hints for Deploying the OLAP Cube • Due to the security restrictions, you need to: • Create and save the project in the local drive • When defining the data source, check “Use the service account” in the Impersonation Information panel 11

12. Demonstration • Create data mart MaxMinSalesDM with BIDS from a cube template • Deploy the data mart