isqs 3358, business intelligence creating data marts

ISQS 3358, Business IntelligenceISQS 3358, Business Intelligence

Creating Data MartsCreating Data MartsZhangxi Lin

Texas Tech University

1

Why need Data Mart?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

2

Data Mart StructuresData Mart StructuresMeasuresDimensions and HierarchiesAttributes (or columns)Dimensional modeling – Stars and

Snowflakes

3

Dimensional Modeling Dimensional Modeling ProcessProcessHigh level dimensional model design

◦ Choosing business model◦ Declaring the grain◦ Choosing dimensions◦ Identifying the facts

Detailed dimensional model developmentDimensional model review and validation

◦ IS◦ Core users◦ Business community

Final design iteration

4

Maximum Miniatures Maximum Miniatures Manufacturing – Designing Data Manufacturing – Designing Data MartMartGeneral 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

5

Maximum Miniatures Maximum Miniatures Manufacturing – Creating Data MartManufacturing – Creating Data MartSpecific 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

6

Data RequirementsData 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

7

Business Need of SalesBusiness Need of SalesThe 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

8

Snowflake Schema of the Data Snowflake Schema of the Data MartMart

9

Manufacturingfact

DimProduct

DimProductSubType

DimProductType

DimBatch

DimMachine

DimMachineType

DimMaterial

DimPlant

DimCountry

Exercise 3 – Creating a data Exercise 3 – Creating a data martmart 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”

10

Hints for Deploying the OLAP Hints for Deploying the OLAP CubeCube

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

DemonstrationDemonstration◦ Create data mart MaxMinSalesDM with

BIDS from a cube template◦ Deploy the data mart

12