Information Management and BI 1(b)

Question 1

What is “Data Modeling”?

Answer 1

• Process of prescribing data format, must be standardized for efficient processing
• Provides means for defining and analyzing data
• Dev process incorporate business rules into data model
• Data Modeling s/b foundation for reporting, lead to effective design of data to be captured, stored and reported

Question 2

What is the difference btwn “Unstructured” vs “Structured” Data?

Answer 2

(1) Unstructured data:
- No prescribed format
- Capture, store and process in free form
(2) Structured data:
- Prescribed format
- Data organized and stored into files, records and fields w/ criteria for data entered into that format
- Ex: ERP transaction data
- Data must be Structured for computers to process data properly

Question 3

Name 3 “Data Types”:

Answer 3

(1) Numbers - used for quantities, amts and
dollar amts
(2) Alphanumeric - Address( letters, numbers, characters used in data value)
(3) Dates - describe specific date format

Question 4

What is “Data Normalization”?

Answer 4

• Transactional flat file
• Remove certain
  factors to eliminate data anomalies
• Outcomes of normalized data is to maximize efficiency of data model, increases integrity associated w/ capturing and managing data
• Eliminate data redundancy, leave any data field only once, except for duplication of key fields to establish relationships btwn files
• Integrating data from diff systems more efficient if integration data model is normalized
• Data files susceptible to data anomalies represents significant IT risk in that system

Question 5

What are 3 types of “Conceptual Data Modeling” tools?

Answer 5

(1) Entity-relationship (ER) model
(2) Relational model
(3) Unified Modeling Language (UML)

Question 6

What is the “ER Model” and what does it depict?

Answer 6

• A Conceptual Data Modeling tools
• Depicts relationship btwn files and records data files in a top-down fashion
• Results = ER diagrams

Question 7

What are “ER diagrams”?

Answer 7

• Depicts file, type of interaction w/ another file and relationship btwn records in one file to the other

Question 8

What is a “Relational Model”?

Answer 8

• A Conceptual Data Modeling tool
• Illustrates data files as tables w/ arrows from primary to foreign keys to illustrate relationship btwn data files
• Advantage of a Relational Model is that files are depicted as a table, similar to electronic spreadsheet and fields are depicted as columns, records as rows

Question 9

What is Unified Modeling Language (UML)?

Answer 9

• Most recent Conceptual Data Modeling tool
• Associated w/ object-oriented programming (OOP), newer programming languages like C sharp and Java
• Both static and dynamic aspects can be depicted in a UML diagram
• UML is more technical than
  ER and Relational Models

Question 10

Name 2 “Data File” forms:

Answer 10

(1) Flat file
- All transactional data in a single record (row)
(2) Databases
- Transactional data in separate files using a structured process (outcome of data normalization)

Question 11

What are 2 kinds of structure for data in a DW?

Answer 11

1. Dimensional Data (“Star”)

2. Normalized Data (“Snowflake”)

Question 12

What is the “Snowflake” Approach?

Answer 12

• Normalization of Data
• Tables grouped by subject areas (customers, products, employees, purchases)
• Dozens of tables then linked by foreign/primary keys
• Advantage: Adding data from systems to a Snowflake DW is straightforward

Question 13

What are the Disadvantages of the “Snowflake” Approach?

Answer 13

• Disadvantages:
  (1) Difficult to join data from different sources to obtain more meaningful info
  (2) Difficult to access info w/out thorough knowledge of data sources and DW structure

Question 14

What is the “Star” Approach?

Answer 14

• Dimensional Data
• Transactional data designed and defined as facts (numeric data) or dimensions (ref info that adds meaning to the facts)
• Includes various relevant data that describes the numeric data (dimensions)
• Ex: Sale transaction has quantities sold and dollar amts (“facts”)
• Includes sale date, region sold, sales rep, inv category
• Advantage: Easier for users to understand than Snowflake Approach

Question 15

What are Disadvantages of the “Star” Approach?

Answer 15

• Disadvantages:
  (1) Load data from diff systems and databases complicated to get it into dimensional form
  (2) Difficult to modify DW structure if entity changes business processes or captures data
• Struggle w/ “one way” dimension definitions
• Must refer to and use city, state and zip code as CSZ, and must be 1 field, not 3