ISQS 3358, Business Intelligence Creating Data Marts - PowerPoint PPT Presentation

1 / 12
About This Presentation
Title:

ISQS 3358, Business Intelligence Creating Data Marts

Description:

Filling a mold with the raw material. Aiding the hardening of this materials ... Create data mart MaxMinSalesDM with BIDS from a cube template. Deploy the data mart ... – PowerPoint PPT presentation

Number of Views:185
Avg rating:3.0/5.0
Slides: 13
Provided by: zlinB
Category:

less

Transcript and Presenter's Notes

Title: 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_at_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
Write a Comment
User Comments (0)
About PowerShow.com