ata Warehouses and Data Marts Write a one to two page (250-500 word) paper that discusses the differences between data warehouses and data marts. Also, discuss how organizations can use data warehouses and data marts to acquire data. You must use the CSU Online Library to locate at least two sources for your paper. (2 articles attached) APA rules for formatting, quoting, paraphrasing, citing, and listing of sources are to be followed. Reference for attached articles Computers. (2013). Michigan Academician, 41(2), 133-135. Pipe, P. (1997). The Data Mart: A New Approach to Data Warehousing. International Review Of Law, Computers & Technology, 11(2), 251-262. doi:10.1080/13600869755695INTERNATIONAL REVIEW OF L AW C OMPUTERS& TECHNOLOGY , VOLUME 11, N UMBER 2, P AGES 251261, 1997The Data Mart: A New Approach to DataWarehousingPAM ELA PIPEIntroductionVendors have recently begun to deliver low-cost and integrated data warehouse packagesintended for the rapid development of departmental data warehouses, or so-called datamarts. The availability of these packages requires organizations to consider the role of adata mart in a data warehousing system, and whether a data mart should be built before,after, or in parallel with a corporate enterprise data warehouse. In some situations a set ofdistributed data marts may even eliminate the need for an enterprise-le vel data warehousesolution. This paper discusses the role of data marts, reviews the pros and cons of thedifferent approaches to building a data warehousing system involving data marts, and alsolooks at data mart product requireme nts. Throughout the paper, the SmartM art packagefrom Information Builders Inc. is used to describe the characteristics of an integrated datamart package.Types of Data W arehouse·Data warehouses come in all shapes and sizes, but essentially they can be considered to fallinto one of the following categories (see Figure 1):·An enterprise data warehouse (EDW) contains integrated subject-oriented data capturedfrom one or more operational systems or external information providers, and loaded intoa separate data warehouse database. An EDW contains summarized as well as detaileddata recording business operations over a period of time that may range from a fewmonths to many years. This historical and summarize d data allows detailed analysis ofbusiness activity for both tactical and strategic business decision-making.An operational data store (ODS) contains current (or near-current) detailed data forregular day-to-day business querying and reporting. As with an EDW, data in an ODSis captured from operational systems and integrated into a separate subject-oriented datawarehouse. Unlike an EDW, an ODS does not contain summarized or historical data.Organizations that deploy an ODS may evolve the ODS to an EDW as they gainexperience with data warehousing.This article is based upon a paper prepared for Information Builders, by Colin W hite, DataBaseAssociates International, California, USA.Correspondence: Ms Pamela Pipe, Information Builders, Wembley Point, Harrow Road, Wembley,Middx HA9 6DE, UK. , http://www.ib i.comm .1360-0869/97/020251-1 1Ó1997 Carfax Publishing Ltd252·FIGUREPamela Pipe1.Types of data warehouse.A data mart contains a subset of corporate data that is of value to a speci c departmentor set of users. This data subset may be captured from one or more operational systems,or from an EDW. Data marts usually contain summ arized historical data for a speci cfunctional area of a corporation, and have the bene t of being faster and cheaper to buildthan an EDW. Unlike an EDW , however, they do not provide the capability to analyzecorporate data across functional areas of the business. It is important to realize that adata mart is de ned by the functional scope of its users, and not by the size of thedatabases involved. Most data marts today involve less than 100 GB of data; some arelarger, however, and it is expected that as data mart usage increases they will rapidlyincrease in size. Data marts can be developed prior to, in parallel with, or after thedeployment of an EDW for a particular subject area. There is considerabl e debate aboutthe role of data marts with respect to an EDW and how data marts should bedeployed this topic is discussed in the next section.Approaches to Data Warehouse Development·Early proponents of data warehousing recommended building an EDW using a top-downapproach driven primarily by the IT department. This approach involves developing abusiness case, assembling a project team, identifying source data of interest in operationalsystems, developing a data model, and then building an enterprise data warehouse. Withthis approach, data marts are seen as a follow-on to the construction of an EDW in amulti-tier topology (see Figure 2).The top-down technique follows the traditional waterfall approach to IT developmentand has two key bene ts:It provides a rigorous and familiar methodology for modelling and implementingend-user DSS requirements.The Data Mart·FIGURE2.253Multi-tier data warehousing system.It creates a data warehousing system that gives end users the ability to have acorporate-wide perspective of business operations, issues and opportunities for businessdevelopment. A corporate-wide view of data based on a subject-oriented data modelminimizes integration problems between data warehouse projects, regardless of whetherthey are developed serially or in parallel.·A multi-tier warehousing system involving an EDW and underlying data marts is probablythe optimal one for most organizations. With this topology data marts are fed from anEDW, rather than operational systems, and data is located where it can deliver the highestavailability and performance, without sacri cing integrity or control over the managementof corporate data for business decision-making:····Data marts improve availability by reducing dependency on network access to a remoteEDW.Performance is improved by storing the data closer to the user in the data mart.Costs are reduced by the use of low-cost data mart software and hardware.Capacity planning is easier since data marts reduce EDW workload and also in somecases database size.Data control and integrity is maintained, since the EDW is the single source of data forfeeding the data marts.··This vision of a rigorous top-down approach to data warehouse construction is not sharedby everyone. Many argue that a top-down approach, where data marts are developed afteran EDW is built, has several drawbacks:IT-driven top-down projects in the past have led to long delivery schedules, highcapitalization, cost overruns, and poor end-user functionality, even though adequate costjusti cation was done prior to the project.Cost justi cation for a data warehouse may be somewhat elusive, since it is dif cult toquantify return on investment (ROI) on a project whose major bene t is the potential forbetter business decision making. Executive management may nd it dif cult to approvesuch projects given the track record of IT departments.·254Pamela PipeThe current economic climate and the global nature of competition demand solutionsthat enable organizations not only to respond rapidly to changing business conditions,but also to be able to predict and quickly exploit new business opportunities. Datawarehouse approaches that have long delivery cycles cannot deliver solutions quicklyenough to address these challenges. Top-down advocates argue that an iterative approach to EDW development, where the EDW is built subject area by subject area,reduces development time. Even the most optimistic supporters of this approach,however, acknowledge that EDW developme nt time may be closer to a year (or possiblylonger).·An alternative to the top-down development of an EDW and underlying data marts is rstto build the data marts, and then an EDW. Some advocates of this bottom-up approacheven argue that an EDW is not necessary, since a corporate view of data can be achievedby combining data marts together in a distributed data warehouse using database hubserver middleware . The middleware provides a seamless interface between end user DSStools and the data marts by insulating the user from the physical locations of the datamarts. A hub server can also provide other services, for example, enhanced security,business views of data, and facilities for monitoring and controlling end-user access to data.People who favour top-down warehouse development can rightly point out severaldisadvantages of the bottom-up approach:··An uncontrolle d proliferation of data marts can result in integration problems betweendata marts and a future EDW. Most of these potential issues would be due to differencesin business terminology, data formats and representations, and required attributes notbeing present in the data mart data models. These problems are the primary, if not theonly, objection that top-down advocates have to the implementation of data marts priorto the construction of the EDW. These problems are caused by the lack of a corporatedata model for the warehouse.As data marts proliferate, users will want to access data marts belonging to otherdepartments for cross-business function analysis. Seamless access to data marts may bedif cult without appropriate hub-server middleware . Needless to say, such an environment is complex to administer and manage, and can also lead to poor performance whenend-user queries access and join data from multiple data marts.Data mart technology at present is often unable to scale-up to support the increased datavolumes and numbers of users that would exist in a distributed data mart environment.The current state of technology often limits data marts to less than 100GB of data anda maximum of 25 to 50 users. Of course these limits will vary by vendor product.Another important consideration here is the ability of a data mart product to beintegrated with an EDW in a multi-tier data warehouse topology at some future date ifso desired.It is apparent that both the top-down and bottom-up approaches to data warehousedevelopment have their strengths and weaknesses. What most organizations require is adata warehouse strategy that provides exibility, low capitalization, and a rapid ROI,without unduly sacri cing control or introducing potential integration problems in thefuture. An ideal solution, therefore, would be a synergistic marriage of the two approachesthat maximize the strengths, and minimize the weaknesses, of each approach. The parallelstrategy suggested below uses a combination of the top-down and bottom-up approachesThe Data Mart255and supports incremental and evolutionary data warehouse development, while at the sametime attempting to solve the issues with other approaches that this paper raises:1 Consider the use of an ODS for tactical day-to-day business decision-m aking whenexisting operational systems cannot supply integrated and consistent data, or when directaccess by end users to operational data would adversely affect the performance of theoperational environment.2 Develop data marts as required for complex data analysis and strategic businessdecision-m aking by business functional area. This developme nt should be based on ahigh-level subject data model, which documents the boundaries and data relationshipsthat exist between functional areas. This data model will evolve and will form the basisfor a future EDW. In fact, an EDW can be developed in parallel with the data martsusing this approach. A data warehouse project team and appropriate checkpoint mechanisms should be put in place to coordinate the efforts of data mart and EDW development. The critical consideration here is the development of the high-level datamodelthe initial effort should not exceed more than a few months of elapsed time. Thedevelopme nt of such a model will reduce, but not necessarily eliminate, future integration problems between projects.3 Connect data marts together in a distributed warehouse environment using appropriatedatabase hub-server middleware . A distributed data warehouse should be viewed as atactical solution en route to a multi-tier data warehouse, unless usage, cost andperformance considerations do not warrant an EDW.4 Move toward a multi-tier data warehouse topology where an EDW acts as the singlesource of end-user data for supplying underlying data marts.Business conditions and priorities will determine whether or not this evolutionary parallelapproach is a viable solution for an organization. The features and capabilities of data martproducts will also in uence the success of this approach. The criterion for selecting datamart products is the subject of the rest of this paper.Data Mart Product Requireme nts········A data warehousing system provides a complete end-to-end solution for supplying endusers with business information. The main components of such a system consist of (seeFigure 3):Design tools to design warehouse databases.Source data acquisition tools to capture data from source les and databases; and clean,enhance, transport and apply it to data warehouse databases.A data manager to manage and access warehouse data.GUI and Web-based data access tools to provide business end-users with the tools theyneed to access and analyze warehouse data.A delivery manager to distribute warehouse data and other information objects to otherdata warehouses, desktop applications, and Web servers on a corporate Intranet.Middleware to connect data access tools to warehouse databases, and the deliverymanager to target systems.An information directory to provide administrators and business users with informationabout the contents and meaning of data stored in warehouse databases.Warehouse management tools to administer data warehouse operations.256FIGUREPamela Pipe3.Data warehousing system architecture.In addition to supplying products that support these components, vendors may also provideconsulting services to train IT staff to install and deploy data warehousing products.When building ODS and EDW systems, organizations typically select and integrate,based on application requirements, best-of-breed products for each of the componentsshown in Figure 3. When building a data mart, however, the cost and time to integratebest-of-breed products from multiple vendors is not acceptable, and instead organizationstend to select a data mart package (or set of integrated products) from a single vendor,which supports the components shown in the gure. In fact it is the move toward low costand integrated data warehouse product sets that has encouraged the growth of data marts.Potential buyers should, however, consider long-term data warehouse requirements whenselecting a data mart package. Of particular importance is the ability of the data mart packageto support growth as database size, numbers of users, and query volume increase withdata mart usage and experience. Also of importance here with respect to future growth isthe ability of the product to support distributed and multi-tier data warehousing topologies.The next section of this paper looks at each of the components of a data warehousingsystem in more detail.Design toolsDesign tools are used by data warehouse designers and administrators to design and de nedata warehouse databases. In general, any database design tool can be used for designinga warehouse database. Compared with operational database design, there are someadditional factors that need to be taken into account when designing a warehouse database,such as the handling of summary and temporal data, for example. Another factor is the useof star schemas, which are employed by some warehouse databases, particularly those usedfor multidimensional data analysis.The Data Mart257Source data acquisition tools····Source data acquisition tools are used to develop and run data aquisition applications thatcapture data from source systems for applying to warehouse databases. Data acquisitionapplications are developed based on rules de ned by the warehouse developer. These rulesde ne the data sources from which the warehouse data will be obtained, and the cleanupand enhanceme nt to be done to this data before it is applied to warehouse databases.Data cleanup may involve the restructuring of records or elds, removal of operationalonly data, the supply of missing eld values, and the checking of data integrity andconsistency. Data enhancemen t may involve decoding and translating eld values, addinga time attribute (if one is not present in the source data) to re ect the currency of data, datasummarization, or the calculation of derived values.Once the source data has been cleaned and enhanced it is mapped to the targetwarehouse databases, transported to the data warehousing system, and applied to theappropriate warehouse databases. The applying of data to the warehouse databases is doneusing data manipulation language statements (SQL, for example, in the case of a relationalDBMS), or the load utility of the DBMS used to manage the warehouse.The rules de ned to data acquisition tools are often stored as metadata in the warehouseinformation directory. Some products use this metadata to generate customized 3GL/4GLdata acquisition programs. Other products use this metadata dynamically during dataacquisition operations to manage the ow of data from source systems to the target datawarehouse.There are four main types of product that support data acquisition:Code generators create tailored 3GL/4GL data acquisition programs based on sourcedata de nitions, and cleanup and enhancemen t rules de ned by the warehouse developer.This approach reduces the need for an organization to write its own data acquisitionprograms. Most code generator products generate query language statements to capturedata from the source systems. Some products also support the capturing of changes tosource data by using, for example, the recovery log les of the source system. Codegenerators are used for building an EDW that acquires data from a large number ofdifferent data sources and where there is signi cant data cleanup to be done.Database data replication tools capture changes to a source database on one system andapply the changes to a copy of the source database located on a different system. Thesereplication products often do not support the capture of changes to non-relational lesand databases, and often do not provide facilities for signi cant data cleanup andenhanceme nt. These tools are used to build an ODS, EDW or data mart when thenumber of data sources and the amount of data cleanup required are small.Rule-driven data movers capture data from the source system at user-de ned intervals,clean and enhance the data, and then send and apply the results to the target warehousedatabase. Data to be captured from the source system is usually de ned using querylanguage statements, and cleanup and enhanceme nt are done based on a script orfunction logic de ned to the data pump. Depending on the product, the data mover mayreside on the source system, the target system, or on a separate server. These productsare used to build data marts, rather than an ODS or EDW.Data re-engineering tools are designed to perform data cleanup and enhanceme nt. Someof these focus on structural changes to data, while others are designed to handle thecleanup of data content, such as name and address data, for example. These products are258Pamela Pipe·often used in conjunction with other data acquisition tools for building an ODS or anEDW.Generalized data acquisition tools and utilities copy data from a source system to atarget system. There are many tools and utilities that do not t into any of the fourcategories outlined above for moving data from a source system to a target system. Theseproducts tend to focus on the fast movement of data, rather than on supporting the dataintegration, cleanup, and enhanceme nt requirements of a data warehousing system.Data managerThe data manager is used by other components in the data warehousing system to create,manage and access warehouse data (and possibly metadata). The data manager employedby a data warehousing system is usually either a relational DBMS (RDBMS) or amultidimensional DBMS (MDBMS). An RDBM S is used for building either an ODS, anEDW or a data mart, while an MDBMS is used for building a data mart for doingmultidimensional analysis. The pros and cons of using an RDBMS or MDBMS for buildinga data mart are discussed in more detail below in the section on data access tools.Warehouse DBMSs have requireme nts over and above those for operational OLTPapplications. Key factors to consider here are scalability (database size, query complexity,number of users, number of dimensions, software exploitation of underlying hardware),and performance (utility operations and complex query processing). As query complexityand database size increases, data warehouse designers will need to consider the use ofparallel hardware and parallel database software in order to obtain satisfactory performance.Data access toolsData access tools support three main user tasks: querying and reporting of known facts,analysis of known facts, and the discovery of unknown facts.Querying and reporting involves displaying data stored in a data warehouse in a visualform. This visual form may, for example, be a printed report, or information displayed ona desktop computer. The processing may be done on-line or in batch, using ad hoc orpre-de ned queries and reports. Tools supporting this type of task have existed for manyyears. Modern tools, however, often offer more sophisticated facilities like graphical userinterfaces, support for Web browsers, the ability to pass retrieved data to desktopapplications using techniques such as Microsoft OLE, and a semantic layer to provide abusiness view of the data. Query and reporting tools are most often used to provideinformation for tracking day-to-day business operations, i.e., for making tactical businessdecisions. The bene t a data warehouse offers here is data that has been cleaned andintegrated from multiple operational systems.Data analysis tools provide capabilities like mathematical and statistical functions,multidimensional modeling, and forecasting. These tools are used to analyze and forecasttrends, and to measure the ef ciency of business operations over a period of time. Theresults of this work are used for strategic business decision-m aking, and to look for waysof improving the ef ciency of business operations and reducing costs. This type ofprocessing is sometimes called On-Line Analytical Processing, or OLAP. Many of the toolsproviding this type of processing have existed for many years, but like query and reportingtools, OLAP products are now taking advantage of graphical user interfaces, Web technol-The Data Mart259ogy, and client/server computing. The bene t of data warehousing for OLAP is not onlyclean and integrated data, but also the provision of the historical data that is essential forforecasting and trend analysis.OLAP is a hot topic, and there is much debate concerning the use of OLAP tools fordoing multi-dimensional data analysis (MDA). These tools allow users to analyze and sliceand dice data across multiple dimensions, for example, by time, by market, and/or byproduct category. Vendors offer two kinds of tool: MDA client tools that access data storedin multi-dimensional database systems (MDBM Ss), and MDA client tools that access datastored in relational DBM Ss. The debate centres around which type of DBMS is best suitedto store and maintain multi-dimensional data. Much of what is written about the pros andcons of each approach is super cial and lacks strong technical arguments, often confusinguser needs with technical issues.When analyzing MDA products, the discussion should focus on two key, but separate,issues. The rst is the requirements of the user, i.e., the functionality provided by the MDAclient tool. The second issue is performance and scalability, and this is where the MDBMSvs. RDBMS issues have to be considered. There is not suf cient space in this paper to gointo the arguments being put forward for each of the two MDA approaches. Suf ce it tosay that both approaches have som e merit, and the type of MDA tool should be selectedbased on the kind of processing being done by the end-user and the type of data requiredto support this processing.Data analysis tools typically work with summarized, rather than detailed data. Thesesummaries can be built during analytical processing, but it is far more ef cient to pre-buildthe summaries whenever possible. This reduces processing overheads and makes the toolseasier to use. Data marts are ideally suited for the storing of summarized data that has beentailored to suit speci c sets of users and applications.Query, reporting and data analysis tools are used to process or to look for known facts,i.e. users of these tools know what kind of information they want to access and analyze.Starting to appear on the market is a new breed of business intelligence tool that is usedto explore data for unknown facts, i.e. information that is not known to the business user.This style of processing allows business users to seek…