erp final paper

8/2/2019 ERP Final Paper

1/12

Mudassar rauf

1. What is and ERP System?

Enterprise resource planning (ERP) is a company-wide computer software system

used to manage and coordinate all the resources, information, and functions of a

business from shared data stores.

2. What are the significant limitations of ERP Systems?

ERP system has four significant limitations.

Managers and decisions makers cannot generate custom reports or querieswhen they need them. There by preventing them from acting quickly and

effectively.

ERPsystem provides current status only such as open orders. The data in ERP application is not integrated with other enterprise system

and does not include external intelligence.

ERP system cannot meet all of the analytical and reporting needs oforganizations.

3. Also describe the reasons for these limitations.

There are many reasons for the above limitations. First, the ERP system was not

designed with the objective of information integration from multiple sources in

mind. Second, ERP database were designed to optimize performance and therefore

lack the constructs required for multi-dimensional analysis. Third, most ERP

solutions lack the advance functionality of todays leading reporting and analytical

tools. Lastly, performing complex analysis on the ERP database will impact the

performance of the operational system.

4. List the major technologies that can overcome these limitations.

1.Business-Process-Reengineering (BPR)

2.Management-Information-System (MIS)

3.Decision-Support-Systems (DSS)

4.Executive-Information-Systems (EIS)


2/12

Mudassar rauf

5.Data-warehousing

6.Data-Mining

7.On-line-Analytical-Processing (OLAP)

8.Supply Chain Management (SCM)

5.DIFFERENTIATE BETWEEN BPR,MIS,DSS AND EIS.

1.Business process reengineering(BPR)the analysis and redesign of workflowwithin and between enterprise. The purpose of BPR is to make all business

processes best-in-class

2. Executive information system (EIS) a specialized DSS that supports seniorlevel executives within the organization

Most EISs offering the following capabilities:

a. Consolidation involves the aggregation of information and features

simple roll-ups to complex groupings of interrelated information

b. Drill-down enables users to get details, and details of details, ofinformation

c. Slice-and-dicelooks at information from different perspectives

Decision support system (DSS)

models information to support managers andbusiness professionals during the decision-making process

Three quantitative models used by DSSs include:

Sensitivity analysisthe study of the impact that changes in one (or more) parts of

the model have on other parts of the model

What-if analysischecks the impact of a change in an assumption on the proposed

solution

Goal-seeking analysis finds the inputs necessary to achieve a goal such as a

desired level of output..

MIS (Management Information System).


3/12

Mudassar rauf

Combination of human and computer-based resources that results in collection,

storage, retrieval, communication and use of data for efficient management of

systems.

Functions of management

Planning (Strategies)

Organizing (People and activities)

Controlling (Check plans and correct deviations)

Directing (Plan implementation, leadership, motivation.

6.DIFFERENTIATE BETWEEN OLTP AND OLAP.

OLTP OLAP

1. Current data Current and historical data

2. Short database transactions Long database transactions

3. Online update/insert/delete Batch update/insert/delete

4. Normalization is promoted Denormalization is promoted

5. High volume transactions Low volume transactions

6. Transaction recovery is necessary Transaction recovery is not

necessary

7.DIFFERENTIATE BETWEEN DATA WAREHOUSE AND OLAP.

Data Warehouse

1. Data from different data sources is stored in a relational database for end useanalysis.

2. Data is organized in summarized, aggregated, subject oriented, non volatile

patterns.

3. Data is a data warehouse is consolidated, flexible collection of data Supports

analysis of data but does not support online analysis of data.


4/12

Mudassar rauf

Online Analytical Processing

1. A tool to evaluate and analyze the data in the data warehouse using analytical

queries.

2. A tool which helps organizes data in the data warehouse using multidimensional

models of data aggregation and summarization.

3. Supports the data analyst in real time and enables online analysis of data with

speed and flexibility.

8. Differentiate between OLAP and Data Mining.

OLAP and data mining are used to solve different kinds of analytic problems:

1. OLAP summarizes data and makes forecasts. For example, OLAP answersquestions like "What are the average sales of mutual funds, by region and by

year?"

2. Data mining discovers hidden patterns in data. Data mining operates at adetail level instead of a summary level. Data mining answers questions like

"Who is likely to buy a mutual fund in the next six months, and what are the

characteristics of these likely buyers?"

9. Differentiate between BI and Data Warehouse.

1. Data Warehousing helps you store the data.

2. Business intelligence (BI) helps you to control the data for decision

making, forecasting etc.

10. Differentiate between Star Schema and Snowflake Schema?

Star Schema Snowflake Schema

De-Normalized Data Structure Normalized Data Structure

Category wise Single Dimension

Table

Dimension table split into many

pieces

More data dependency and

redundancy

less data dependency and No

redundancy

No need to use complicated join Complicated Join

Query Results Faster Some delay in Query Processing


5/12

Mudassar rauf

No Parent Table It May contain Parent Table

Simple DB Structure Complicated DB Structure

View Star Schema Structure View Snowflake Schema Structure

11.DIFFERENTIATE BETWEEN DATA MART AND DATA WAREHOUSE.

Data warehouse is made up of many data marts. DWH (data warehouse)

contain many subject areas. However, data mart focuses on one subject area

generally.

12.DIFFERENTIATE BETWEEN DIMENSION TABLE AND FACT TABLE.

Dimension Table features

1. It provides the context /descriptive information for a fact table measurement.

2. Provides entry points to data.

3. Structure of Dimension - Surrogate key, one or more other fields that compose

the natural key (nk) and set of Attributes.

4. Size of Dimension Table is smaller than Fact Table.
http://blog-mstechnology.blogspot.com/2010/06/bi-dimensional-model-star-schema.htmlhttp://blog-mstechnology.blogspot.com/2010/06/bi-dimensional-model-snowflake-schema.htmlhttp://blog-mstechnology.blogspot.com/2010/06/bi-dimensional-model-snowflake-schema.htmlhttp://blog-mstechnology.blogspot.com/2010/06/bi-dimensional-model-snowflake-schema.htmlhttp://blog-mstechnology.blogspot.com/2010/06/bi-dimensional-model-star-schema.html


6/12

Mudassar rauf

5. In a schema more number of dimensions are presented than Fact Table.

6. Surrogate Key is used to prevent the primary key (pk) violation (store historical

data).

7. Values of fields are in numeric and text representation.

FACT TABLE FEATURES

1. It provides measurement of an enterprise.

2. Measurement is the amount determined by observation.

3. Structure of Fact Table - foreign key (fk), Degenerated Dimension and

Measurements.

4. Size of Fact Table is larger than Dimension Table.

5. In a schema less number of Fact Tables observed compared to Dimension

Tables.

6. Compose of Degenerate Dimension fields act as Primary Key.

7. Values of the fields always in numeric or integer form.

13.WHAT IS DIFFERENCE BETWEEN E-RMODELING AND DIMENSIONAL

MODELING?

ER modeling is used for normalizing the OLTP database design.

Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design

14. WHAT IS SCM?HOW IT CAN BE USED TO COPE WITH THE CURRENTDEMAND.

Asupply chainis the collection of steps that a company takes to transform raw

components into final products and deliver them to customers. Supply chain

management (SCM) is the process that is used by a company to ensure that its

supply chain is efficient and cost effective.
http://www.wisegeek.com/what-is-a-supply-chain.htmhttp://www.wisegeek.com/what-is-a-supply-chain.htmhttp://www.wisegeek.com/what-is-a-supply-chain.htmhttp://www.wisegeek.com/what-is-a-supply-chain.htm


7/12

Mudassar rauf

15.WHAT ARE MEASURE, DIMENSION AND MEMBER?WHERE ARE MEASURE

AND DIMENSION STORED?

Dimensions allow data analysis from various perspectives. For example, timedimension could show you the breakdown of sales by year, quarter, month, day

and hour.

Measures are numeric representations of a set of facts that have occurred.

Examples of measures include dollars of sales, number of credit hours, store profit

percentage, dollars of operating expenses, number of past-due accounts and so

forth.

Member is a value within a dimension level that can be used for aggregating andreporting data. For example each product category such as beverage, non-

consumable, food, clothing, etc is a member. Each product class such as beer,

wine, coke, bottled water would represent a member.

16.WHAT ARE DRILL DOWN, ROLL UP AND DRILL THROUGH OPERATIONS?

Drill-down refers to the process of viewing data at a level of increased detail,

While roll-up refers to the process of viewing data with decreasing detail.

17.WHAT ARE SLICING AND DICING?WHY ARE THEY USED?

Slicing means taking out the slice of a cube.

Dicing means viewing the slices from different angles.

Additional Functionality that can be thought of as viewing a slice of the data cube,particularly when values for multiple dimensions are fixed. Slicing/Dicing simply

consists of selecting specific values for these attributes, which are then displayed

on top of the cross-tab

18.WHAT IS SURROGATE KEY?WHERE WE USE IT EXPLAIN WITH EXAMPLES?


8/12

Mudassar rauf

A surrogate key as an artificial column added to a relation to serve as a primary

key.

NOTE: The primary key of the relation is underlined below:

RENTAL_PROPERTY without surrogate key:

RENTAL_PROPERTY

(Street,City,

State/Province, Zip/PostalCode, Country, Rental_Rate)

RENTAL_PROPERTY with surrogate key:

RENTAL_PROPERTY (PropertyID, Street, City,

State/Province, Zip/PostalCode, Country, Rental_Rate)

19.DIFFERENCE BETWEEN SNOW FLAKE AND STAR SCHEMA,WHAT ARE

SITUATIONS WHERE SNOW FLAKE SCHEMA IS BETTER THAN STAR SCHEMA TO

USE AND WHEN THE OPPOSITE IS TRUE?

Star schema and snowflake both serve the purpose of dimensional modeling

when it comes to data warehouses.

Star schema is a dimensional model with a fact table (large) and a set of

dimension tables (small). The whole set-up is totally denormalized.

However in cases where the dimension tables are split to many tables that

are where the schema is slightly inclined towards normalization (reduceredundancy and dependency) there comes the snowflake schema.

The nature/purpose of the data that is to be feed to the model is the key to

your question as to which is better.


9/12

Mudassar rauf

STAR SCHEMA

Contains the dimension tables mapped around one or more fact tables.

It is a denormalized model.

No need to use complicated joins.

Queries results fastly.

Snowflake schema

It is the normalized form of Star schema.

Contains in depth joins, because the tables are splited in to many pieces. Wecan easily do modification directly in the tables.

We have to use complicated joins, since we have more tables.

There will be some delay in processing the Query.

20.HOW ARE THE DIMENSION AND FACT TABLES DESIGNED?

Here is overview of four steps to design

Choosing business process to model The first step is to decide what businessprocess to model by gathering and understanding business needs and available data

Declare the grainby declaring a grain means describing exactly what a fact table

record represents

Choose the dimensions once grain of fact table is stated clearly, it is time to

determine dimensions for the fact table.

Indentify factsidentify carefully which facts will appear in the fact table.

21.WHAT ARE DATA WAREHOUSING HIERARCHY,LEVEL AND MEMBER?

22.WHAT ARE AGGREGATE TABLES?


10/12

Mudassar rauf

Aggregate table contains summarized data. The materialized views are aggregated

tables.

23.DESCRIBE BRIEFLY ETL.

It is a data warehousing process that consists of:

extraction (i.e., reading data from a database),

transformation (i.e., converting the extracted data from its previous form into the

form in which it needs to be so that it can be placed into a data warehouse or

simply another database)

load (i.e., putting the data into the data warehouse)

24.WHY FACT TABLE IS IN NORMAL FORM?

Basically the fact table consists of the Index keys of the dimension/ook up tables

and the measures. So whenever we have the keys in a table .that itself implies that

the table is in the normal form.

25.WHAT ARE THE STEPS TO BUILD THE DATA WAREHOUSE?

Gathering business requirements>>Identifying Sources>>Identifying

Facts>>Defining Dimensions>>Define Attributes>>Redefine Dimensions /

Attributes>>Organize Attribute Hierarchy>>Define Relationship>>Assign

Unique Identifiers

26.WHY SHOULD YOU PUT YOUR DATA WAREHOUSE ON A DIFFERENT SYSTEM

THAN YOUR OLTP SYSTEM?


11/12

Mudassar rauf

OLTP system stands for on-line transaction processing.

These are used to store only daily transactions as the changes have to be made

in as few places as possible. OLTP do not have historical data of the

organization

Data warehouse will contain the historical information about the organization.

27.WHICH COLUMNS GO TO THE FACT TABLE AND WHICH COLUMNS GO THE

DIMENSION TABLE?

The Aggreation or calculated value Colum will go to Fact Table and details

information will go to diamensional table.

28.WHAT IS A CUBE IN DATA WAREHOUSING CONCEPT?

Cubes are logical representation of multidimensional data. The edge of the cube

contains dimension members and the body of the cube contains data values.

28.WHAT DOES LEVEL OF GRANULARITY OF A FACT TABLE SIGNIFY?

Granularity

The first step in designing a fact table is to

determine the granularity of the fact table. By

granularity, we mean the lowest level of information

that will be stored in the fact table.This


12/12

Mudassar rauf

constitutes two steps:

1- Determine which dimensions will be included.2- Determine where along the hierarchy of each dimension

the information will be kept. The determining factors usually go back.

29.DESCRIBE DIFFERENT TYPES OF DATA MARTS.

Dependent, Independent, and Hybrid Data Marts

Dependent Data MartsA dependent data mart allows you to unite your organization's data in one data

warehouse. This gives you the usual advantages of centralization.

Independent Data MartsAn independent data mart is created without the use of a central data warehouse.

This could be desirable for smaller groups within an organization. It is not,

however, the focus of this Guide. See the Data Mart Suites documentation for

further details regarding this architecture.

Hybrid Data MartsA hybrid data mart allows you to combine input from sources other than a data

warehouse. This could be useful for many situations, especially when you need ad

hoc integration, such as after a new group or product is added to the organization.

erp final paper

Documents