Transcript Module 5 Data Warehousing - University of Colorado Boulder

Module 5
Strategic Enterprise Management and
Reporting Tools
Important Points of the Assignment
These slides are designed to introduce the students
to the concepts involved in advanced reporting and
analysis of financial and other data that is typically
found in a data warehouse. Since most AIS
textbooks do not pay very much attention to these
concepts, this slide set is intended to cover the
material in substantially more depth.
Accounting Information Systems
Slide Set Contents
1.
2.
3.
4.
5.
6.
Learning Objectives
OLTP and OLAP Systems
The Role of the Data Warehouse
Data Warehousing Analysis Tools
Control Aspects of a Data Warehouse
The Data Warehouse Assignment
Accounting Information Systems
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 2
Primary Learning Objectives
Understanding
the differences between an operational system (OLTP system)
and a data warehousing system (OLAP system)
Understand
the relationship between an ERP system (SAP ERP) and a data
warehouse system (SAP BW)
Exposure
to some of the tools used in a data warehouse environment
Look
into the use of the balanced scorecard and the management cockpit in
the SAP SEM system
Accounting Information Systems
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 3
Data Warehousing a Reporting and
Analysis Platform
Reporting was traditionally done out of the operational system (Termed the
online transaction processing system (OLTP))
However, the operational system (such as SAP ERP) is used to run the dayto-day operations of the company
Performance of the operational system is extremely important since the
performance of the system can significantly influence the performance of
the entire organization
In fact, it is said with respect to the operational system “Performance is
King”
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 4
Data Warehousing a Reporting and
Analysis Platform
The problem with operational systems is that because the way they are
designed, the response time of the system degrades in a quadratic manner
with the growth of the stored data
This means storing historical data in the operational system is not optimal
Removing inactive data from the operational system becomes a necessity
So the large majority of the historical data must be purged from the
operational system
This data contains valuable information that cannot be simply “thrown
away”
This has led most organizations to place this data into a data warehouse
system this is separate from the OLTP system
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 5
Data Warehousing a Reporting and
Analysis Platform
The data warehouse system is referred to as the online analytical
processing system (OLAP)
This system stores substantially more data than the operational system
The physical structure of the system is different than the OLTP system
While reducing data redundancy is a prime objective of the OLTP system,
the data warehouse has substantial redundancy
Also, unlike the OLTP system the data warehouse does not store primarily
transactional data, but instead stores summarized data
Data warehouses store substantially more data than an OLTP system (as
much as 100 times the size)
However, performance is not as important as in the OLTP system and
because of the different structure of the system, even though the system is
large the performance is acceptable
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 6
Data Warehousing a Reporting and
Analysis Platform
While the operational system is designed to run the company on a day-today basis, the function of a data warehouse is to support medium and long
term decision making
An example of a decision made on an operational system is whether to sell
a customer goods on credit
The operational system can check the credit limit of the customer and the
amount of the potential sale and can make the decision
The decisions made using data warehouse data rely much more on
historical data analysis
An example of a decision made using this data is whether to build a new
plant
In addition, because of the increased reporting requirements and the
demand for more timely reports, the reporting function is moving from the
operational system to the data warehouse
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 7
Data Warehousing a Reporting and
Analysis Platform
The question you may have asked when we were doing the SAP ERP
exercises may have been:
 Why
do we need to deal with the complexity of an ERP system when there
are many other accounting packages that are not as complex?
The answer is complex, but some reasons include:
 Improved business processes
 Increased
internal controls
 Much higher quality data
Most of the reasons are not readily obvious, but the analysis that we
discuss when dealing with data warehousing is impossible without having
an ERP system to feed the warehouse quality data
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 8
Data Warehousing a Reporting and
Analysis Platform
The data warehouse environment supports a wide range of tools that can
be used for sophisticated data analysis
This includes
 Multi-dimensional analysis
 Digital dashboards
 Balanced scorecards
 Data
mining
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 9
Data Warehousing a Reporting and
Analysis Platform
Multi-dimensional analysis
 This is what Excel pivot tables enable us to do
A
data warehouse provides these types of tools, but with massive
amounts of data that has been thoroughly checked for quality and proper
integration
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 10
Data Warehousing a Reporting and
Analysis Platform
Digit dashboards
 These include a series of measures that can be used to indicate the
performance of areas within the company
 These are short to medium range indicators that are analogous to the
instruments on your car
 The theory is the company can be “tuned” based on these measures
 SAP’s digital dashboard is called the management cockpit
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 11
Data Warehousing a Reporting and
Analysis Platform
Balanced scorecards
 These are measures that can be used for developing medium to long
range organizational goals
 A scorecard will set a series of organizational objectives with respect to a
group of goals
 Performance can then be measured against those objectives
 You probably discussed this in your managerial accounting course
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 12
Data Warehousing a Reporting and
Analysis Platform
Balanced scorecard usually has four different dimensions (also termed
perspectives) including
– the traditional financial and managerial accounting measures
 Customer – Looks at customer relationships and satisfaction
 Internal – Deals with the internal operations of the organization
 Learning and Growth – Many times focuses on employees
 Financial
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 13
Data Warehousing a Reporting and
Analysis Platform
The use of a balanced scorecard is designed to remove the more traditional
focus on only financial measures to determine success
Aimed at developing long term strategies that will ensure success beyond
the short term
Incentives can then be centered around these more balanced measures
Many companies have adopted this approach
A data warehouse is an enabler for using this tool
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 14
Data Warehousing a Reporting and
Analysis Platform
Data Mining
 Sophisticated set of tools that can be used to determine patterns in data
that are not logically apparent
 Can result in very interesting and profitable situations
 To many companies, this is currently an advanced approach, but with the
heavier use of data warehousing, it will become more widely used
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 15
Data Warehousing a Reporting and
Analysis Platform
Note that all of the above analysis would not be possible using only the
data from an operational system
The data warehouse drives this process
The data warehouse is rapidly changing from a tool that can be used as a
competitive weapon to one that will become an absolute necessity
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 16
Control in a Data Warehouse Environment
With respect to data warehousing, one of the biggest areas of concern is
that the data rolling into the data warehouse is consistent and accurate
Due to the complexity of data loads, this is not a minor problem
Substantial testing must be done to guarantee that the data loads are being
done properly
This can be partly the I.T. audit team’s responsibility
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 17
Data Warehouse Assignment
The
data warehousing assignment illustrates two of the tools discussed in
this lecture
You
will use the SAP BW data warehouse system and the SAP strategic
enterprise management system (SEM) in this assignment
The
SAP SEM system must be installed on top of the SAP BW data
warehouse
The
SEM system provides a series of tools that employ the data that is
held in the BW system to do business analysis
Hence,
BW can be a standalone system, but SEM cannot
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 18
Data Warehouse Assignment
In
the first part of the assignment you will analyze data within a balanced
scorecard
In
the second part of the assignment you will analyze data in a
management cockpit
© 2009 by SAP AG. All rights reserved. / SAP University Alliances Page 19