Chp 3 Study Guide

What issues should be considered when deciding which architecture to use in developing a data warehouse? List the 10 most important factors.

1.Information interdependence between organizational units 2.Upper management's information needs 3.Urgency of need for a data warehouse 4.Nature of end-user tasks 5.Constraints on resources 6.Strategic view of the data warehouse prior to implementation 7.Compatibility with existing systems 8.Perceived ability of the in-house IT staff 9.Technical issues 10.Social/political factors

What is a data warehouse?

A data warehouse is defined in this section as "a pool of data produced to support decision making." This focuses on the essentials, leaving out characteristics that may vary from one DW to another but are not essential to the basic concept. The same paragraph gives another definition: "a subject-oriented, integrated, time-variant, nonvolatile collection of data in support of management's decision-making process." This definition adds more specifics, but in every case appropriately: it is hard, if not impossible, to conceive of a data warehouse that would not be subject-oriented, integrated, etc.

What is a performance measurement system? How does it work?

A performance measurement system is one component of a performance management system. The most popular performance measurement systems in use are some variant of Kaplan and Norton's balanced scorecard (BSC).

What are the three key components of a BPM system?

According to Colbert (2009), a BPM encompasses three key components. The first is a set of integrated, closed-loop management and analytic processes (supported by technology) that addresses financial as well as operational activities. The second involves tools for businesses to define strategic goals and then measure and manage performance against those goals. And the third component involves a core set of processes, including financial and operational planning, consolidation and reporting, modeling, analysis, and monitoring of key performance indicators (KPIs), linked to organizational strategy.

Differentiate among a DM, an ODS, and an EDW.

An ODS (Operational Data Store) is the database from which a business operates on an ongoing basis. Both an EDW and a data mart (DM) are data warehouses. An EDW (Enterprise Data Warehouse) is an all-encompassing DW that covers all subject areas of interest to the entire organization. A data mart is a smaller DW designed around one problem, organizational function, topic, or other suitable focus area.

Why do we need to define separate objectives, measures, targets, and initiatives for each of these four BSC perspectives?

BSC is designed to overcome the limitations of systems that are financially focused. An organization's vision and strategy should recognize the interrelation between financial and nonfinancial objectives, measures, targets, and initiatives. Therefore, nonfinancial objectives form a simple causal chain with "learning and growth" driving "internal business process" change, which produces "customer" outcomes that are responsible for reaching a company's "financial" objectives.

Compare BSC and Six Sigma as two competing performance measurement systems.

BSC is focused on improving overall strategy, whereas Six Sigma is focused on improving processes. BSC gives a longer-term view of the organization, whereas Six Sigma gives a snapshot at a particular point in time of its operational effectiveness. BSC focuses on long-term growth, whereas Six Sigma emphasizes current profitability. These are a few of the differences between the two. Some companies choose to give a more holistic performance assessment by combining elements of both approaches.

What is a balanced scorecard? Where did it come from?

Balanced Scorecard was first developed by Kaplan and Norton in their 1992 Harvard Business Review article, "The Balanced Scorecard: Measures That Drive Performance." It is a performance management system whose key feature is that it does not rely solely on financial measures of success. Over the past few years, BSC has become a generic term that is used to represent virtually every type of scorecard application and implementation, but it is intended to emphasize a strategic focus.

What are the key similarities and differences between a two-tiered architecture and a three-tiered architecture?

Both provide the same user visibility through a client system that accesses a DSS/BI application remotely. The difference is behind the scenes and is invisible to the user: in a two-tiered architecture, the application and data warehouse reside on the same machine; in a three-tiered architecture, they are on separate machines.

What is business performance management? How does it relate to BI?

Business performance management (BPM) refers to the business processes, methodologies, metrics, and technologies used by enterprises to measure, monitor, and manage business performance. It is also known as corporate performance management (CPM), enterprise performance management (EPM), and strategic enterprise management (SEM). It can be considered to be a type of BI tool/technique. The most significant differentiator of BPM from any other BI tools and practices is its strategy focus. BPM encompasses a closed-loop set of processes that link strategy to execution in order to optimize business performance.

List and briefly define the four most commonly cited operational areas for KPIs.

Customer performance. Metrics for customer satisfaction, speed and accuracy of issue resolution, and customer retention. Service performance. Metrics for service-call resolution rates, service renewal rates, service-level agreements, delivery performance, and return rates. Sales operations. New pipeline accounts, sales meetings secured, conversion of inquiries to leads, and average call closure time. Sales plan/forecast. Metrics for price-to-purchase accuracy, purchase order-to- fulfillment ratio, quantity earned, forecast-to-plan ratio, and total closed contracts.

What is DMAIC? List and briefly describe the steps involved in DMAIC.

DMAIC is a closed loop performance improvement model that involves the following steps: define, measure, analyze, improve, and control. First, you define the goals, objectives, and boundaries of the improvement activity. Next, you measure the existing system, in order to monitor its performance against the goals. Then, you analyze the system to identify ways to eliminate the gap between the current performance of the system or process and the desired goal. This leads to improvement, which involves initiating actions to reduce these gaps. Finally, control involves modifying compensation and incentive systems, policies, procedures, manufacturing resource planning, budgets, operation instructions, or other management systems.

Describe data integration.

Data integration is an umbrella term that covers three processes that combine to move data from multiple sources into a data warehouse: accessing the data, combining different views of the data, and capturing changes to the data.

Describe the major components of a data warehouse.

Data sources. Data are sourced from operational systems and possibly from external data sources. Data extraction and transformation. Data are extracted and properly transformed using custom-written or commercial software called ETL. Data loading. Data are loaded into a staging area, where they are transformed and cleansed. The data are then ready to load into the data warehouse. Comprehensive database. This is the EDW that supports decision analysis by providing relevant summarized and detailed information. Metadata. Metadata are maintained for access by IT personnel and users. Metadata include rules for organizing data summaries that are easy to index and search. Middleware tools. Middleware tools enable access to the data warehouse from a variety of front-end applications.

What is OLAP and how does it differ from OLTP?

Data stored in a data warehouse can be analyzed using techniques referred to as OLAP. OLAP is one of the most commonly used data analysis techniques in data warehouses. OLAP is an approach to quickly answer ad hoc questions that require data analysis. OLTP is concerned with the capture and storage of data. OLAP is concerned with the analysis of that data.

List the benefits of data warehouses.

Direct benefits include: Allowing end users to perform extensive analysis in numerous ways. A consolidated view of corporate data (i.e., a single version of the truth). Better and more timely information. A data warehouse permits information processing to be offloaded from costly operational systems onto low-cost servers; therefore, end-user information requests can be processed more quickly. Enhanced system performance. A data warehouse frees production processing because some operational system reporting requirements are moved to DSS. Simplification of data access. Indirect benefits arise when end users take advantage of these direct benefits.

What are the ingredients for an effective performance management system?

Effective performance management/measurement should focus on key factors. It should mix past, present, and future. Also, it should balance the needs of shareholders, employees, partners, suppliers, and other stakeholders. Performance measures should start at the top and flow to the bottom, and should involve targets that are based on research and reality rather than arbitrary.

What steps can an organization take to ensure the security and confidentiality of customer data in its data warehouse?

Effective security in a data warehouse should focus on four main areas: Step 1.Establishing effective corporate and security policies and procedures. An effective security policy should start at the top and be communicated to everyone in the organization. Step 2.Implementing logical security procedures and techniques to restrict access. This includes user authentication, access controls, and encryption. Step 3.Limiting physical access to the data center environment. Step 4.Establishing an effective internal control review process for security and privacy.

Describe the three steps of the ETL process.

Extraction: selecting data from one or more sources and reading the selected data. Transformation: converting data from their original form to whatever form the DW needs. This step often also includes cleansing of the data to remove as many errors as possible. Load: putting the converted (transformed) data into the DW.

What skills should a DWA possess? Why?

Familiarity with high-performance hardware, software, and networking technologies, since the data warehouse is based on those Solid business insight, to understand the purpose of the DW and its business justification Familiarity with business decision-making processes to understand how the DW will be used Excellent communication skills, to communicate with the rest of the organization

What recent technologies may shape the future of data warehousing? Why?

Following are some of the recently popularized concepts and technologies that will play a significant role in defining the future of data warehousing. Sourcing: Acquisition of data from diverse and dispersed sources Web, social media, and Big Data Open source software SaaS (software as a service) "The Extended ASP Model" Cloud computing Infrastructure: Architectural—hardware and software—enhancements Columnar (a new way to score and access data in the database) Real-time data warehousing Data warehouse appliances (all-in-one solutions to DW) Data management technologies and practices In-database processing technology (putting the algorithms where the data is) In-memory storage technology (moving the data in the memory for faster processing) New database management systems Advanced analytics As the world of business becomes more global and complex, the need for business intelligence and data warehousing tools also becomes more prominent. The fast improving information technology tools and techniques seem to be moving in the right direction to address the needs of the future business intelligence systems.

What is the meaning of and motivation for balance in BSC?

In BSC, the term "balance" arises because the combined set of measures is supposed to encompass indicators that are financial and nonfinancial, leading and lagging, internal and external, quantitative and qualitative, and both short term and long term.

List the alternative data warehousing architectures.

Independent data marts architecture Data mart bus architecture with linked dimensional data marts Hub-and-spoke architecture (corporate information factory) Centralized data warehouse architecture Federated architecture

Explain the importance of metadata.

Metadata, "data about data," are the means through which applications and users access the content of a data warehouse, through which its security is managed, and through which organizational management manages, in the true sense of the word, its information assets. Most database management systems would be unable to function without at least some metadata. Indeed, the use of metadata, which enable data access through names and logical relationships rather than physical locations, is fundamental to the very concept of a DBMS. Metadata are essential to any database, not just a data warehouse. (See answer to Review Question 2 of this section above.)

.Identify and discuss the role of middleware tools.

Middleware tools enable access to the data warehouse. Power users such as analysts may write their own SQL queries. Others may access data through a managed query environment. There are many front-end applications that business users can use to interact with data stored in the data repositories, including data mining, OLAP, reporting tools, and data visualization tools. All these have their own data access requirements. Those may not match with how a given data warehouse must be accessed. Middleware translates between the two.

What is Six Sigma? How is it used as a performance measurement system?

Most companies use Six Sigma as a process improvement methodology that enables them to scrutinize their processes, pinpoint problems, and apply remedies. It's not used much as a performance management or measurement methodology. As a performance tool, it is aimed at reducing the number of defects in a business process to as close to zero DPMO (defects per million opportunities) as possible.

What is an ODS?

Operational Data Store is the database from which a business operates on an on-going basis.

How has the Web influenced data warehouse design?

Primarily by making Web-based data warehousing possible.

What are the major DW implementation tasks that can be performed in parallel?

Reeves (2009) and Solomon (2005) provided some guidelines regarding the critical questions that must be asked, some risks that should be weighted, and some processes that can be followed to help ensure a successful data warehouse implementation. They compiled a list of 11 major tasks that could be performed in parallel: Establishment of service-level agreements and data-refresh requirements Identification of data sources and their governance policies Data quality planning Data model design ETL tool selection Relational database software and platform selection Data transport Data conversion Reconciliation process Purge and archive planning End-user support

What is scalability? How does it apply to DW?

Scalability refers to the degree to which a system can adjust to changes in demand without major additional changes or investments. DW scalability issues are the amount of data in the warehouse, how quickly the warehouse is expected to grow, the number of concurrent users, and the complexity of user queries. A data warehouse must scale both horizontally and vertically. The warehouse will grow as a function of data growth and the need to expand the warehouse to support new business functionality. Data growth may be a result of the addition of current cycle data (e.g., this month's results) and/or historical data.

Which data warehousing architecture is the best? Why?

See Table 3.1 Average Assessment Scores for the Success of the Architectures. What is interesting is the similarity of the averages for the bus, hub-and-spoke, and centralized architectures. The differences are sufficiently small that no claims can be made for a particular architecture's superiority over the others, at least based on a simple comparison of these success measures.

List and discuss the most pronounced DW implementation guidelines.

Senior management must support development of the data warehouse. The DW needs a project champion at a high position in the organization chart. Benefits of a DW project may be difficult to measure, so management support makes it more likely the project will receive funding. Web-based data warehouses may need special security requirements. These ensure that only authorized users have access to the data. Users should participate in the development process. Their participation is essential for data modeling and access modeling. User participation ensures that the DW includes the needed data and that decision makers can retrieve the data they need. DW implementation requires certain skills from members of the development team: in-depth knowledge of database technology and the development tools used.

Why is the ETL process so important for data warehousing efforts?

Since ETL is the process through which data are loaded into a data warehouse, a DW could not exist without it. The ETL process also contributes to the quality of the data in a DW.

List several criteria for selecting a data warehouse vendor, and describe why they are important.

Six important criteria are: financial strength, ERP linkages, qualified consultants, market share, industry experience, and established partnerships. These are important to indicate that a vendor is likely to be in business for the long term, to have the support capabilities its customers need, and to provide products that interoperate with other products the potential user has or may obtain. One could add others, such as product functionality (Does it do what we need?), vendor strategic vision (Does their direction make sense for our future plans and/or is it consistent with industry trends?) and quality of customer references (What do their existing customers think of them?).

When developing a successful data warehouse, what are the most important risks and issues to consider and potentially avoid?

Starting with the wrong sponsorship chain Setting expectations that you cannot meet Engaging in politically naive behavior Loading the data warehouse with information just because it is available Believing that data warehousing database design is the same as transactional database design Choosing a data warehouse manager who is technology oriented rather than user oriented Focusing on traditional internal record-oriented data and ignoring the value of external data and of text, images, and, perhaps, sound and video Delivering data with overlapping and confusing definitions Believing promises of performance, capacity, and scalability Believing that your problems are over when the data warehouse is up and running Focusing on ad hoc data mining and periodic reporting instead of alerts

Why is strategy the most important part of a BPM implementation?

Strategy is the art and the science of crafting decisions that help businesses achieve their goals. More specifically, it is the process of identifying and stating the organization's mission, vision, and objectives. Business strategy provides an overall direction to the enterprise, which is why it is so important.

How does a data warehouse differ from a database?

Technically a data warehouse is a database, albeit with certain characteristics to facilitate its role in decision support. Specifically, however, it is (see previous question) an "integrated, time-variant, nonvolatile, subject-oriented repository of detail and summary data used for decision support and business analytics within an organization." These characteristics, which are discussed further in the section just after the definition, are not necessarily true of databases in general—though each could apply individually to a given one. As a practical matter most databases are highly normalized, in part to avoid update anomalies. Data warehouses are highly denormalized for performance reasons. This is acceptable because their content is never updated, just added to. Historical data are static.

List and briefly describe the four phases of the BPM cycle.

The BPM cycle contains four main phases. First is to strategize. This involves answering the question, "Where do we want to go?", and involves a high-level, long-term plan. Missions, visions, and objectives are key components of this phase. The second phase is to plan, which answers the question, "How do we get there?" Key elements here are a detailed operational plan and a financial plan including budget. The next phase is to monitor and analyze, which answers the question, "How are we doing?" Here is where KPIs, dashboards, reporting, and analytics are helpful. Finally come action and adjustment, based on comparing our analysis results against our plans. Sometimes this means changing the way we operate, and sometimes it means adjusting our strategy.

Describe the data warehousing process.

The data warehousing process consists of the following steps: 1.Data are imported from various internal and external sources 2.Data are cleansed and organized consistently with the organization's needs 3.a.Data are loaded into the enterprise data warehouse, or b.Data are loaded into data marts. 4.a.If desired, data marts are created as subsets of the EDW, or b.The data marts are consolidated into the EDW 5.Analyses are performed as needed

What are the four perspectives that BSC suggests to view organizational performance?

The four perspectives are: customer, financial, internal business processes, and learning and growth. If customers are not satisfied, they will eventually find other suppliers that will meet their needs. Poor performance from this perspective is thus a leading indicator of future decline, even though the current financial picture may look good. Timely and accurate funding data will always be a priority, and managers will do whatever is necessary to provide it. This should include risk analysis. In the current climate of rapid technological change, it is becoming necessary for knowledge workers to be in a continuous learning and growing mode. Metrics based on this perspective allow the managers to know how well their internal business processes and functions are running, and whether the outcomes of these processes (i.e., products and services) meet and exceed the customer requirements (the mission).

What are the distinguishing features of KPIs?

The key features described in the book are strategy, targets, ranges, encodings, time frames, and benchmarks. KPIs embody strategic objectives and measure performance against specific targets, based on specified ranges of values. Encodings provide visual cues (e.g., color) to indicate how close or far from a target we are on a particular metric. Benchmarks provide something to compare against.

What is a cube? What do drill down, roll up, and slice and dice mean?

The main operational structure in OLAP is based on a concept called cube. A cube in OLAP is a multidimensional data structure (actual or virtual) that allows fast analysis of data. Using OLAP, an analyst can navigate through the database and screen for a particular subset of the data (and its progression over time) by changing the data's orientations and defining analytical calculations. These types of user-initiated navigation of data through the specification of slices (via rotations) and drill down/up (via aggregation and disaggregation) are sometimes called "slice and dice." Commonly used OLAP operations include slice and dice, drill down, roll up, and pivot. Slice: A slice is a subset of a multidimensional array (usually a two-dimensional representation) corresponding to a single value set for one (or more) of the dimensions not in the subset. Dice: The dice operation is a slice on more than two dimensions of a data cube. Drill Down/Up: Drilling down or up is a specific OLAP technique whereby the user navigates among levels of data ranging from the most summarized (up) to the most detailed (down).

What is a performance management system? Why do we need one?

The purpose of a performance management system is to (a) identify and articulate the strategic mission, goals, and objectives of an organization, and (b) assist managers in tracking the implementations of business strategy by comparing actual results against these strategic goals and objectives. The latter task is accomplished by a performance measurement system, which can be considered a subset of the overall performance management system. A performance measurement system typically comprises systematic methods of setting business goals together with periodic feedback reports that indicate progress against goals. This is a key and necessary element of the BPM process.

What are the differences and commonalities between dashboards and scorecards?

These terms are often used interchangeably, and they share many common features. The main difference is that scorecards are used by executives, managers, and staff to monitor strategic alignment and success with strategic objectives and targets. By contrast, dashboards are used at the operational and tactical levels. Managers, supervisors, and operators use operational dashboards to monitor detailed operational performance on a weekly, daily, or even hourly basis.