Unit 5 Scholarly Activity: Information Systems Management

DataMart.pdf

Home >Information Systems homework help >Unit 5 Scholarly Activity: Information Systems Management

Decision-oriented dimensional data marts are fundamentally different than transaction-oriented relational databases. A distinctive methodology and a different set of tools are required for their effective development.

A METHOD FOR DEVELOPING DIMENSIONAL DATA MARTS

E X P E N D I T U R E S on data warehouses and related business intelligence technologies are expected to increase from $60 billion in 2001 to $150 billion in 2005 [6], an increase of over 20% per year. To make effective use of these expenditures, information systems professionals need methodologies for developing data warehouses. The most widely used tool for developing information systems applications is the systems development life cycle (SDLC)—a general application develop- ment tool tailored to meet the requirements of a number of diverse applications, including database development.

Here, we tailor the SDLC to data mart development, and show how the steps in the SDLC must be transformed in order to be applicable for developing data marts. We also clarify the differ- ences between the methods needed to develop transaction- oriented relational databases and those needed to develop decision-oriented dimensional data marts. Dimensional data marts are fundamentally different than relational databases—a different methodology and a different set of tools are required for their effective development. By contrasting the steps required to develop relational databases with the steps required to develop dimensional data marts, this article clarifies the differences in the methodologies and tools in a manner readily understandable to information systems professionals.

B Y TIM CHENOWETH, DAVID SCHUFF, AND ROBERT ST. LOUIS

COMMUNICATIONS OF THE ACM December 2003/Vol. 46, No. \1 9 3

Ricardo [7] adapted the SDLC to transaction-ori- ented database development. Her adaptation is called the Staged Database Design Methodology. Steps unique to database development, such as constructing a logical data model, choosing a database management sy5tem, and performance tuning, are addressed directly in Ricardo's methodology.

Using this methodology as a starting point, we develop an analogous procedure tor dimensional data marts. Although the steps of our Dimensional Data Mart Development Methodology are essentially the same as Ricar- do s, the operationalization ot those steps is quite different. These differences exist for two reasons: Relational databases (RDBs) are generally used to support transac- tion processing, whereas dimen- sional data marts (DDMs) are generally used to support decision making. Also, different tools and methods are required to design, build, and tune dimensional ver- sus relational databases. Figure I highlights these differences.

Step 1. Analyze the user envi- ronment. The manner in which the user environment is analyzed differs grcady for a transaction- oriented relational database versus a decision-oriented dimensional data mart. In the development of a relational database, the goal is to support all the trans- action-processing needs of the org-anization. The focus is on identifying the functions the organization must perform and the data required to perform them. Vari- ous tools such as process decomposition charts and business function-to-data entity matrices have been developed to aid database designers with this task [5].

In the development of a dimensional data mart, the goal is to support the analysis of a specific business process. Von Winterfeldt and Edwards [8] point out that "good problem structuring is the key to successful analy- sis." Accordingly, we present the following five-step structured process for analyzing the user environment.

1. Focus on a specific business process or problem area. Determining the sales performance of a com- pany's retail outlets is a problem area on which a data mart could focus.

2. Defme the performance measures needed to evalu- ate and improve that process. Dollar sales per square foot of floor space is a possible performance measure for a sales data mart.

3. Identify the relevant dimensions along which the performance measures should be analyzed. Store, brand, and time are examples of dimensions along which the performance measures could be analyzed in a sales data mart.

4. Determine the facts needed to operationalize the performance measures. Dollars sold or units sold are examples of facts needed to operationalize the performance measures for a sales data mart.

5. Decide whether the granularity should be the

Tra nsaction-Oriented RDB Operationolization

Operations perspective

Entity-relationship diagram

R«IUiona1

Set of normalized tables

Maintain data Integrity

Imficcsand storage structures

Track queries for speed and frequency

Ricardo't Staged Database Oejrgn

I.Analyze user environment

2. Develop Logical Model

3. Choose DBMS

4. Map Logical Modei to DBMS

S, Develop Physlcai Modei

6. Evaluate Physical Model

7.Tune System

8. Implement/Monitor System

Decision-Oriented DDM Operation of jzation

Decision perspective

Star-schema diagram

Relational or dimensional

One or more non-normalized tabiM

Preserve relevant analytical cHteHa

Indlcei, storage structurM, and summarie*

Track queries for speed, frequency,

and content

Figure l . Dimensional vs. relational design

methods.

intersection of the dimensions, or even fmer to allow for "graceRi! extensions" [4] at a later time. Deciding whether to store sales by transaction, hour, day, week, or month is an example of a gran- ularity decision for a sales data mart.

This structure forces analysts to identify the relevant performance measures, and the dimensions along which those performance measures should be analyzed. Our emphasis on performance measures also highlights the difference in the way the user environment must be analyzed for dimensional data marts versus transaction- oriented databases. Figure 1 uses the phrases "Decision Perspective" and "Operations Perspective" to summa- rize those differences

Step 2. Develop the logical model. Relational and dimensional databases also use very different logical design techniques. Almost all relational databases use entity-relationship diagrams (ERDs) to develop the logical model. The intent is to represent all of the enti- ties that impact an organizations operations, their rele- vant data characteristics, and the relationships among those data characteristics. The goal is to capture all of the information needed for the transaction processing required to run the organization. The entities identified

9 4 Docembcr 2CIOB/Voi. 46.No \7 COMMUNICATIONS OF THE ACM

In the development of a dimensional data mart, the goal is to support the analysis of a specific business process.

during the analysis phase, as well as their relationships, are mapped in the ERD.

A completely different logical design technique is used for data marts. The predominant tool for the

ScoreJD Time

ORDER LINE

PK,FKI PK.FK2 PK.FK3

Sale ID item_iD Promotion iD

Qu.iiicy

[> \

PROMOTION PK Promotion ID

Description Vendor Start date End dace

Address Ci.y

Zip _ Code Telephone

Description Vendor Price

development of the logical model for data marts is the star-schema diagram. The intent is for the model to represent all of the relevant questions that must be answered in order to make appropriate decisions in a specific business area. If the five-step process presented here is used to analyze the user environment, then the measured f̂ cts will map directly to the fact table, and the dimensions will map directly to the dimension tables. Figure I uses the phrases "Star-Schema Dia- gram" and "Entity-Relationship Diagram" to summa- rize the differences in the way that logical modeling must be done for data marts versus relational databases.

Figure 2 shows an ERD for a sales database, while Figure 3 shows a star-schema diagram (SSD) for a sales

database. Note the ERD focuses on the transactions while the SSD focuses on measuring the impact of the transactions. In figures 2 and 3, PK indicates primary key and FK indicates foreign key.

Step 3. Choose the database management system (DBMS). As described by Ricardo [7], the best DBMS is "the system that best satisfies the specifications for the environment, within the con- straints given." The central issue is performance. For a transaction- oriented database, there are a wide variety of choices, including prod- ucts by IBM, Oracle, Microsoft, Sybase, and Informix. All of these products use a relational engine to process the tables that correspond to the entities and relationships in the ERD.

The choice is more complex for a data mart. A dimensional data- base can be stored as a set of tables

(one table for each dimension plus one fact table), as a single data cube (the join of each dimension table with the fact table), or as both a data cube and a set of tables (the data cube for storing aggregated data and the fact and dimension tables for storing nonaggregated data). If the dimensional database is stored as a set of dimen- sion tables and a fact table, then a relational engine must be used to process the tables (relational online analytical processing or ROLAP). If the dimensional database is stored as a data cube, then a dimensional engine must be used to process the data cube (multidi-

Figure 2. Entity- relationship diagram for a sales database.

COMMUNICATIONS OF THE ACM December 2003/Voi 46, No 9S

mensional online analytical ptocessing or MOLAP). If the dimetisional database is stored using both a data cube and a set of dimension tables and a fact table, then both a dimensional engine and a relational engine must be used to process the data (hybrid online analytical processing or HOLAP). Dimensional engines are available from a number of vendors, including SAS, Oracle, IBM, and Microsoft.

Figure 1 indicates that only a relational engine may be used with a transaction-oriented rela- tional database, but either a rela- tional or a dimensional engine may be used with a decision-ori- ented dimensional data mart. Although benchmarks are offered regarding the performance of spe- cific DBMS products, there is lit- de information available regarding which dimensional engine offers the best performance. In fact, it is not even known whether a dimen- sional engine operating ag;iinst a data cube always offers better per- formance than a relational engine operating on a set of dimension tables and a fact table. We believe this is an important area for additional research.

Step 4. Map the logical model to the database management system. This is the process of fitting the logical model for the database to the DBMS software chosen to implement the system. In transacrional data- bases, a major component of this step is normalizing the tables. This is done primarily to ensure the integrity of the data. Because transactional databases are con- stantly updated, it is almost impossible to maintain the integrity of the data if the tables are not normalized. Because dimensional databases are read-only, preserv- ing data integrit)' is not a critical concern. Instead, the focus is on enhancing performance and maintaining an intuitive user interface. Normalizing the dimension tables would interfere with both of these objectives.

The result of Step 4 is a large number of normalized tables in the relational database case, but only one or a few non-normalized tables in the dimensional data mart case. Figure 1 uses the phrases "One or More No n-Normal ized Tables" and "Set of Normalized Tables" to summarize this difference. Removing ;ill redundancy in the data is immensely beneficial to transaction processing, both for preserving data integrity and speeding updates. However, the goal of dimensional databases is user tinderstandability and

ease of querying. Removing redundancies does not improve the performance of dimensional data marts.

Steps 5 and 6. Develop and evaluate the physical model. Once the tables have been defined using the

PK Store ID

Address Cit/ Stale Zip Code Telephone

/ \

PK PK

SALES FACT F K I FK2

FK4

Item ID Swre ID Time ID PromotiotA ID

Qu.-in,.t/ Tolal Sale

PROMOTION Promotion ID Desrnption Discoufit

Time ID

Month Day Year Holiday

Figure 3. star-schema for a sales database.

DBMS s data definition language, the DBMS generally creates the physical model. This is true for both dimen- sional and relation:il DBMSs. Evaluation of the physi- cal model involves an assessment of its ability to meet the objectives ofthe system, which were defined in Step 1. Typically, this includes benchmarking a ptototype of the system.

The criteria used to evaluate the physical model dif- fer for dimensional data marts and transaction-oriented databases. Figure 1 uses the phrases "Preserve Relevant Analytical Criteria" and "Maintain Data Integrity" to summarize these differences. The transaction-oriented database is evaluated on its ability to process transac- tions over time without becoming corrupt. The focus is on the ability to correctly process all of the transac- tions required to support the otganizations operations.

The dimensional data mart, on the other hand, is evaluated on its ability to help managers understand, evaluate, and improve a business process. The focus is on the understandability of the user interface and the ability ofthe system to accurately provide the informa- tion that managers need to improve the business process. T h e physical model also is evaluated for speed. If the queries cannot be processed in a timely manner, then the database will not be used. Step 7 addresses how to modify the physical model to increase query- proces.sing speed.

Step 7. Perform timing. For transaction-oriented databases, adding indices, denormalizing tables, and

9 6 1003/Vol -IC. No \1 COMMUNICATIONS OF THE ACH

Aggregates are the most powerful tool for increasing query processing speed in dimensional data marts.

physically repositioning tables (for example, putting them on the same disk pack, or the same cylinder within a disk pack) are the most effective tools for increasing query processing speed. Indices, denormal- ized tables, and physical restructuring ;iiso are used to improve the performance of dimensional data marts. However, aggregates are the most powetfxil tool for increasing query processing speed in diniension;il data marts. Kimball [4] states, "If you do not have aggre- gates, then you are potentially wasting millions of dol- lars on hardware upgrades to solve performance problems that could be otherwise addressed by aggre- gates."

Although storing aggregates can dramatically increase performance, caution is required. Kimball [3] warns against the explosion of aggregates, and shows that storing a^regates can easily increase the size ofthe data mart by 400%. Selection ot aggregates is a com- plex and important decision. Although it is impossible to provide a single, simple answer to this question, it is possible to provide two useful guidelines.

The first guideline is provided by Kimball [3]: "The single most effective way to control an a^regate explo- sion, but still benefit from the value of aggregates, is to make sure that each aggregate summarizes at least 10 and preferably 20 or more lower-level items." Kimball is addressing the problem from a storage perspective. As long as each aggregate summarizes a large number of lower-level items, the number of records in the aggre- gate fact table is likely to be less than the number of records in the base fact table. Also, the greater the num- ber of lower-level items summarized in the aggregate, the less sparse (more dense) the data cube.

Storage costs are an important component of data mart cost, but so are processing costs. Each time the data mart is refreshed, processing time is required to create each a^regate. If an aggregate is never used, that processing time was wasted. If an a^regate is used more than once, a net saving in processing time occurs (assuming the overhead cost ot maintaining that aggre- gate in the data mart is negligible). This means it is effi- cient to store almost any a^regate that will be used frequently.

Therefore, we s u r e s t a second guideline for storing aggregates: Store any a^regate that, on average, is likely to be used at least once per refresh period. Given the dramatic decrease in storage costs taken place (from $37.25 per gigabyte in 1999 to $5.68 per gigabyte in 2003 to $0.21 per gigabyte by 2010 [2]), this guideline is likely to become even more important in the future.

Figure 1 summarizes the difference in bow tuning must be done for dimensional data marts versus trans- action-oriented databases by noting that both indices, storage structures, and aggregates can be used to tune data marts, but only indices and storage structures can be tised to tune transactional databases. The use of stored a^regiites in a volatile transaction-oriented data- base is very impractical, since the stored aggregates must be updated almost continuously. However, the nonvolatile nature of the dimensional data mart makes it ideal for storing aggregates.

Step 8. Implementation/monitoring. Implemen- tation involves putting the system into production. O n e ofthe duties ofthe database administrator (DBA) is to monitor the queries run against the database. The way in which this monitoring is done differs consider-

COMMUNICATIONSOFTHE ACM December 2003/Vo) 46. No 12 9 7

ably between transactlon-orienced relational databases and dimensional data marts. In transaction databases, queries are monitored primarily for their processing speed. The methods for increasing speed are assigning indices and restructuring tables. The content of the queries is of little interest to the DBA.

In dimensional data marts queries are monitored for both speed and content. It the same query is run repeatedly to create an aggregate, then that a^regate should be stored in a faa table. Since it is much quicker to retrieve a stored aggregate than to process the query that generates the aggregate, storing frequently requested aggregates In the data warehouse speeds the retrieval of summar>' information. As stated in Step 7, this is unique to data marts because these databases are designed to store non-volatile data.

Figure 1 uses the phrases "Track Queries for Speed, Frequency, and Content" and "Track Queries for Speed and Frequency" to highlight the differences in the way monitoring must take place for dimensional data marts versus transaction-oriented databases. In both cases the DBA must monitor for queries that take ;ui unustially long time to run, and queries that are run very treqtiently. However, the DBA for a dimensional data mart mtist also monitor for summary queries. If the query involves an a^regate function, then the DBA must consider whether the creation of a stored a^regate would be beneficial.

Summary and Conclusion 1 his article makes tour major contributions to the data warehousing practice and literature. First, it pre- sents a methodology for data mart development. Our Dimensional Data Mart Development Methodology integrates the information systems development liter- ature with the data warehouse literature to create an easy-to-follow, structured process for the data mart practitioner.

Second, we contrast each step in the dimensional data mart development process with its corresponding step in the relational database development process. Figure 1 highlights the differences in the development methodologies. This diagram is a valuable reference tool for understanding the differences in the develop- ment methodologies tor rhe two types ot databases.

Third, practical guidelines are presented for analyz- ing the user environment tor dimensional data marts. We present a five-step process for analyzing the user environment. By tocusing on performiuice measures and the ways in which those measures need to be ana- lyzed, data mart designers can protect against providing what Ackot̂ [1] aills an "overabundance ot irrelevant data," and yet still provide all the information needed ro effectively manage a business process.

Fourth, guidelines are presented for determining which aggregates should be stored in a data mart. The use of stored aggregates can greatly enhance the perfor- mance of the data mart, but can also cause the size of the data mart to explode. Building on the work of Kimball [3], we present guidelines for storing aggre- gates.

In total, these contributions highlight the unique characteristics of data mart development. Beoiuse such systems are intended to support management deci- sions, developers must think in terms of business processes and the performance measures needed to monitor and improve those processes. Given the grow- ing importance of these systems, it is critical that data mart implementations be undertaken in an efficient manner, and the methodology described in this article provides a structure that should contribute toward more successful implementations. B

REFERENCES 1. Aclcotr, R.I,. Maiiagemein misiiiformatlon systems. Management Science

l4.4lDtc. 1967), B147-B156. 2. Gilheany, S. Projecting the cost of magnetic disk storage over the nexi 10

years. White paper 22()llvO39h. Archiwbuilders.ami. Manhattan Beadi, CA, 2000; www.ariJiivcbuiide[5.coni/whi[epat>ers/220l lvO39h.html.

3. Kimball, R. The Data Warehouse Toolkit, John Wiley. New York, 1996. 4. Kimball, R. A dimensional modeling manifesto. Database Magazine 10, 9

(Aug. 1997). 59-68. 5. McFadden. F., Hoffer, J., and Prescott. M. Madct7i Datahase Management,

5th Edition. Addison-Westey, Reading, MA, 1999. 6. Parkes, C. Data Warehousing: The economy isn't the only reason the data

warehouse indmtry is srumbling. Enterprise Systerru (Mar. 1. 2002); www .esj.com/Depart ments/amde.asp?EditorialslD=51

7. Ricardo. C. Database Systems: Principles, Design. & Implementation. Macmitlian. New York, 1990.

8. Von Wiiitcrfeldt. D. and Edwards, W. Decision Analysis and Behavioral Reseiirirh. C âmbndgc University Press, New York, 1986.

T I M C H E N O W E T H ([email protected]} is an assistant professor in the College of Business and Economics ar Boise University, Boise, ID. D A V I D S C H U F F (,[email protected]) is an assistant professor in the Fox School ot Business at Temple University, Philadelphia. PA. R O B E R T S T . L O U I S ([email protected](lu) is a profiasot in the w.l*.

Carey School of Btisiness at Arizotia State University, Tempe, AZ.

Pf rniission to make diKiral or hard copies of all or pan of this w(irk fur pttsonal or class- room ust is j;runtnl without Ice prtividnl char c i t i e s art nur made or disiriburnl lor protlf or cummercial advaniagt and rhat copies bear this notice and the full ciiation on (be first paf;e. To copy ochefwise. lo repiiblish, co posi on servers or co rediscribucc co lists, requires prior specific permission and/or a fee.

9 8 December 2003/Val. 46. No I? COMMUNICATIONS OF THE ACM