MMIS642 Assignment: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making

Name: MMIS642 Assignment: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making
Brand: Nova University
Price: 2 USD
Availability: InStock
Author: Eleanor Gray

A solved assignment on data warehousing and sales analysis for decision-making.

Eleanor Gray

Contributor

4.7

about 1 month ago

Preview (14 of 44)

MMIS642 ASSIGNMENT 1
MMIS642 ASSIGNMENT
Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making
Student
1/30/2015

MMIS642 ASSIGNMENT 2
You are the vice president of marketing for a nation-wide appliance manufacturer with
three production plants. Describe any three different ways you will tend to analyze your
sales. What are the business dimensions for your analysis?
If I am the Vice President of marketing for a company with three production plants, my first
and obvious dimension would be the “Product”. Analysis of sales must include analysis by
breaking the sales down by dealers, hence “Dealer”, is another important dimension for
analysis. As an Appliance manufacturer, I would want to know how sales break down along
customer demographics. I would want to know who is buying appliance and in what
quantities, so “Customer demographics” would be another useful business dimension for
analysis. How do the customers pay for the appliances? What effect does financing for the
purchases have on the sales? These questions can be answered by including the “method of
payment” as another dimension for analysis. There are three production plants means
“distribution and transportation” are also the important dimensions for analysis. All the
business dimensions involve time management importantly hence “Time” is also the factor
for analysis.
You are the manager for the data warehouse project at a retail chain with stores all
across the country and users in every store. How will you ensure that all the details
necessary to decide on the DBMS are gathered during the requirements phase? Write a
memo to the Senior Analyst directly responsible to coordinate the requirements
definition phase.
BUSINESS MEMO
DATE : February 16, 2015
TO : Senior Analyst

MMIS642 ASSIGNMENT 3
FROM : Manager
RE : Data storage specifications to decide on the DBMS and examine the impact of
business requirements on the selection of the DBMS and on estimating storage for the data
warehouse.
Please ensure following business requirements are met in the requirement analysis of the
DBMS. Broadly, the following elements of business requirements affect the choice of the
DBMS:
Level of User Experience: If the users are totally inexperienced with database
systems, the DBMS must have features to monitor and control runaway queries. On
the other hand, if many of your users are power users, then they will be formulating
their own queries. In this case, the DBMS must support an easy SQL-type language
interface. Formulate your questionnaire to check user experience level properly.
Types of Queries: The DBMS must have a powerful optimizer if most of the queries
are complex and produce large result sets. Alternatively, if there is an even mix of
simple and complex queries, there must be some sort of query management in the
database software to balance the query execution.
Need for Openness: The degree of openness depends on the back-end and front-end
architectural components and those, in turn, depend on the business requirements.
Data Loads: The data volumes and load frequencies determine the strengths in the
areas of data loading, recovery, and restart.
Metadata Management: If your metadata component does not have to be elaborate,
then a DBMS with an active data dictionary may be sufficient. Let your requirements
definition reflect the type and extent of the metadata framework.

MMIS642 ASSIGNMENT 4
Data Repository Locations: Is your data warehouse going to reside in one central
location, or is it going to be distributed? The answer to this question will establish
whether the selected DBMS must support distributed databases.
Data Warehouse Growth: Your business requirements definition must contain
information on the estimated growth in the number of users, and in the number and
complexity of queries. The growth estimates will have a direct relation to how the
selected DBMS supports scalability.
You also need to estimate the storage sizes for the following in the requirements definition
phase:
Data Staging Area: Calculate storage estimates for the data staging area of the overall
corporate data warehouse from the sizes of the source system data structures for each
business subject. Figure the data transformations and mapping into your calculation.
For the data marts, initially estimate the staging area storage based on the business
dimensions and metrics for the first data mart.
Overall Corporate Data Warehouse: Estimate the storage size based on the data
structures for each business subject. You know that data in the data warehouse is
stored by business subjects. For each business subject, list the various attributes,
estimate their field lengths, and arrive at the calculation for the storage needed for that
subject.
Data Marts: Conformed, Independent, Dependent, or Federated. While defining
requirements, you create information diagrams. A set of these diagrams constitutes a
data mart. Each information diagram contains business dimensions and their
attributes. The information diagram also holds the metrics or business measurements

MMIS642 ASSIGNMENT 5
that are meant for analysis. Use the details of the business dimensions and business
measures found in the information diagrams to estimate the storage size for the data
marts. Begin with your first data mart.
Multidimensional Databases: These databases support OLAP or multidimensional
analysis. How much online analytical processing (OLAP) is necessary for your users?
The corporate data warehouse or the individual conformed or dependent data mart
supplies the data for the multidimensional databases. Work out the details of OLAP
planned for your users and then use those details to estimate storage for these
multidimensional databases.
You have recently been promoted to administrator for the data warehouse of a
nationwide automobile insurance company. You are asked to prepare a checklist for
selecting a proper vendor tool to help you with the data warehouse administration.
Make a list of the functions in the management and control component of your data
warehouse architecture. Use this list to derive the tool-selection checklist.
If I am Administrator for the data warehouse of a nationwide automobile insurance company
my checklist for selecting a proper vender is as given below:
• Preliminary complete list of affected user groups prior to interviews
• Preliminary complete list of legacy data sources prior to interviews
• Data warehouse implementation team identified
• Data warehouse manager identified
• Interview leader identified
• Extract programming manager identified

Loading page 6...

MMIS642 ASSIGNMENT 6
• End user groups to be interviewed identified
• Data warehouse kickoff meeting with all affected end user groups
• End user interviews
• Marketing interviews
• Finance interviews
• Logistics interviews
• Field management interviews
• Senior management interviews
• Six-inch stack of existing management reports representing all interviewed groups
• Legacy system DBA interviews
• Copy books obtained for candidate legacy systems
• Data dictionary explaining meaning of each candidate table and field
• High-level description of which tables and fields are populated with quality data
• Interview findings report distributed
• Prioritized information needs as expressed by end user community
• Data audit performed showing what data is available to support information needs
• Data warehousing design meeting
• Major processes identified and fact tables laid out
• Grain for each fact table chosen

Loading page 7...

MMIS642 ASSIGNMENT 7
• Choice of transaction grain Vs time period accumulating snapshot grain
• Dimensions for each fact table identified
• Facts for each fact table with legacy source fields identified
• Dimension attributes with legacy source fields identified
• Core and custom heterogeneous product tables identified
• Slowly changing dimension attributes identified
• Demographic minidimensions identified
• Initial aggregated dimensions identified
• Duration of each fact table (need to extract old data upfront) identified
• Urgency of each fact table (e.g. need to extract on a daily basis) identified
• Implementation staging (first process to be implemented...)
• Block diagram for production data extract (as each major process is implemented)
• System for reading legacy data
• System for identifying changing records
• System for handling slowly changing dimensions
• System for preparing load record images
• Migration system (mainframe to DBMS server machine)
• System for creating aggregates
• System for loading data, handling exceptions, guaranteeing referential integrity

Loading page 8...

MMIS642 ASSIGNMENT 8
• System for data quality assurance check
• System for data snapshot backup and recovery
• System for publishing, notifying users of daily data status
• DBMS server hardware
• Vendor sales and support team qualified
• Vendor reference sites contacted and qualified as to relevance
• Vendor on-site test (if no qualified, relevant references available)
• Vendor demonstrates ability to support system startup, backup, debugging
• Open systems and parallel scalability goals met
• Contractual terms approved
• DBMS software
• Vendor sales and support team qualified
• Vendor team has implemented a similar data warehouse
• Vendor team agrees with dimensional approach
• Vendor team demonstrates competence in prototype test
• Ability to load, index and quality assure data volume demonstrated
• Ability to browse large dimension tables demonstrated
• Ability to query family of fact tables from 20 PCs under load demonstrated
• Superior performance and optimizer stability demonstrated for star join queries

Loading page 9...

MMIS642 ASSIGNMENT 9
• Superior large dimension table browsing demonstrated
• Extended SQL syntax for special data warehouse functions
• Ability to immediately and gracefully stop a query from end user PC
• Extract tools
• Specific need for features of extract tool identified from extract system block
diagram
• Alternative of writing home-grown extract system rejected
• Reference sites supplied by vendor qualified for relevance
• Aggregate navigator
• Open system approach of navigator verified (serves all SQL network clients)
• Metadata table administration understood and compared with other navigators
• User query statistics, aggregate recommendations, link to aggregate creation tool
• Sub-second browsing performance with the navigator demonstrated for tiny
browses
• Front end tool for delivering parameterized reports
• Saved reports that can be mailed from user to user and run
• Saved constraint definitions that can be reused (public and private)
• Saved behavioral group definitions that can be reused (public and private)
• Dimension table browser with cross attribute sub-setting
• Existing report can be opened and run with one button click

Loading page 10...

MMIS642 ASSIGNMENT 10
• Multiple answer sets can be automatically assembled in tool with outer join
• Direct support for single and multi-dimension comparisons
• Direct support for multiple comparisons with different aggregations
• Direct support for average time period calculations (e.g. average daily balance)
• STOP QUERY command
• Extensible interface to HELP allowing warehouse data tables to be described to
user
• Simple drill-down command supporting multiple hierarchies and no hierarchies
• Drill across that allows multiple fact tables to appear in same report
• Correctly calculated break rows
• Red-Green exception highlighting with interface to drill down
• Ability to use network aggregate navigator with every atomic query issued by tool
• Sequential operations on the answer set such as numbering top N, and rolling
• Ability to extend query syntax for DBMS special functions
• Ability to define very large behavioral groups of customers or products
• Ability to graph data or hand off data to third-party graphics package
• Ability to pivot data or to hand off data to third-party pivot package
• Ability to support OLE hot links with other OLE aware applications
• Ability to place answer set in clipboard or TXT file in Lotus or Excel formats

Loading page 11...

MMIS642 ASSIGNMENT 11
• Ability to print horizontal and vertical tiled report
• Batch operation
• Graphical user interface user development facilities
• Ability to build a startup screen for the end user
• Ability to define pull down menu items
• Ability to define buttons for running reports and invoking the browser
• Consultants
• Consultant team qualified
• Consultant team has implemented a similar data warehouse
• Consultant team agrees with the dimensional approach
• Consultant team demonstrates competence in prototype test
As the data acquisition specialist, what types of metadata can help you? Choose one of
the data acquisition processes and explain the role of metadata in that process.
Exercise #5 pg. 221 Data Warehousing Text
If I am the data acquisition specialist the metadata types by the functional areas in the data
Warehouse which could help me are:
1. Data acquisition 2. Data storage 3. Information delivery
Data Acquisition
In Data Acquisition, the data warehouse processes relate to the following functions:

Loading page 12...

MMIS642 ASSIGNMENT 12
➢ Data extraction
➢ Data transformation
➢ Data cleansing
➢ Data integration
➢ Data staging
As tools are used for the various data warehouse processes, metadata gets recorded as a
byproduct. The data acquisition process Data Extraction has following roles in the process
Data Extraction
➢ Data on source platforms and connectivity
➢ Layouts and definitions of selected data sources
➢ Definitions of fields selected for extraction
➢ Criteria for merging into initial extract files on each platform
➢ Rules for standardizing field types and lengths
➢ Data extraction schedules
➢ Extraction methods for incremental changes
➢ Data extraction job streams

Loading page 13...

MMIS642 ASSIGNMENT 13
What is a factless fact table? Design a simple STAR schema with a factless fact table to
track patients in a hospital by diagnostic procedures and time.
Exercise #3 pg. 248 Data Warehousing Text
The Factless Fact Table
Apart from the concatenated primary key, a fact table contains facts or measures. Let us say
we are building a fact table to track patients in a hospital by diagnostic procedures and time.
For analyzing patients in a hospital, the possible dimensions are Patient, Visit, Diagnostic
procedure, consumption of drugs, material and Time.
Patient Key
Visit Key
Procedure Key
Time Key
Drugs Key
Material Key
PATIENT FACTS
Patient Key
Patient No.
Patient Name
Address
Contact
PATIENT
Visit Key
Visit No.
Patient Name
Address
Contact
VISIT
Procedure Key
Procedure No
Type
Result
Remarks
PROCEDURE
Time Key
Date
Day of Month
Month
Year
TIME
Drug Key
Drug Name
Mfg Date
Exp Date
Qty
DRUGS
Material Key
Name
Type
Qty
MATERIAL

Loading page 14...

13 more pages available. Scroll down to load them.

Preview Mode

100%

Study Now!

XY-Copilot AI

Unlimited Access

Secure Payment

Instant Access

24/7 Support

Document Chat

Document Details

University

Nova University

Subject

Information Technology

MMIS642 Assignment: Data Warehouse and Sales Analysis Strategies for Enterprise Decision-Making

Study Now!

Document Details

Related Documents

Practice Questions

Lab Create a Home Wireless Network

Network Configuration and Troubleshooting Practice

Post Test AntiTerrorism

Master Machine Learning Concepts

761541663-Wordly-Wise-Book-8-Answer-Key

Level 1 Anti-terrorism Awareness Training

Understanding Machine Instructions Elements, Types

MGBA-325QRWorkforceAssociation

DAT 375 Module 3-1 Assignment

Company

Explore

Study Tools