SUGI 23: Querying the Data Warehouse with the …

1 1 Querying the data Warehouse with the SQL Procedure SELECT StatementKirk Paul Lafler, Software Intelligence CorporationABSTRACTTo appreciate the wealth of information a data warehousehas to offer, you need to understand how to communicatewith it. Information is typically accessed using an SQLquery. The statement responsible for Querying the manytables and returning the requested results in a datawarehouse environment is the SELECT statement. It is anindispensable piece of the data retrieval process. Thispaper looks at the structure of the typical data Warehouse ,the types of information stored in it, and how users accessand retrieve data from data Warehouse plays an important role in moderncomputer systems.

2 It stores and organizes operationalenterprise-wide data consisting of a mix of olderunintegrated "legacy" application systems. data warehousedata is accurate as of some moment in time. Its purpose isto support management and end-user analysis and data passes into a data Warehouse after beingcaptured and filtered. Only data meeting the needs ofmanagement and decision support activities is passed. Asit is being passed from the operational environment, datausually undergoes a transformation process prior to beingstored in the data Warehouse . Structurally, data warehousedata bares no resemblance to data in the operationalenvironment.

3 Consequently, data redundancy is normallynot a data Warehouse STRUCTUREA data Warehouse has a unique structure as illustrated inFigure 1. It consists of current and older operational datacombined with varying levels of summarizations anddetailed vantage points. In Inmon and Hackathorn's book"Using the data Warehouse ", they describe data warehousedata as " data spanning a spectrum of time and the datarelationships between two or more tables may be many." Adata Warehouse is organized with data consisting of majorbusiness areas such as customer, product, vendor, types of data may be stored in a data Warehouse : 1)older detail data , 2) current detail data , 3) lightlysummarized data , and 4) highly summarized detail data represents data that is not very recent,maybe as old as ten years or longer.

4 It is voluminous andmost frequently stored on mass storage such as tape,although more expensive disk storage may be used. Itslevel of detail is consistent with current detail data (seelater description), but due to its longer time horizon istypically migrated to a "less-expensive" alternate detail data represents data of a recent nature andalways has a shorter time horizon than older detail it can be voluminous, it is almost always storedon disk to permit faster summarized data represents data distilled fromcurrent detail data .

5 It is summarized according to some unitof time and always resides on summarized data represents data distilled fromlightly summarized data . It is always compact and easilyaccessible and resides on final component of the data Warehouse is that ofmetadata. Metadata is best described as data about put, it provides information about the structure ofa data Warehouse as well as the various algorithms used indata summarizations. It provides a descriptive view, or"blueprint", of how data is mapped from the operationallevel to the data TutorialsAdvanced Tutorials2 data Warehouse FLOW OF DATAA typical data Warehouse is populated with data from theoperational environment.

6 Before data is physically storedin a data Warehouse , it is first verified for correctness, thentransformed through a process called integration. Withintegration, data becomes more organized and manageableby enforcing consistent data rules such as namingconventions, assigning consistent physical attributes, andadhering to consistent has been said that data entering a data warehouserepresents a moment in time. Put another way, datawarehouse data represents a collection of moments in changes are usually permitted once data is captured ina data Warehouse , as long as the underlying enterprise-wideoperational data is correct.

7 This assures a "safe" andnonvolatile environment to better enable analysis anddecision THE data WAREHOUSEA dministering and tuning activities in a data warehouseenvironment are sometimes necessary for improving accessand performance. Indexing is a good example, since thenumber of indexes usually assigned to data at higher levelsof summarization are much greater than those at lowerlevels of detail. This is due to the sheer volume of datafound at lower levels. It is simply not desirable to assignindexes at lower efforts, such as those found with indexingoperations, require greater resources with lower levels ofdetail compared to data found at higher levels ofsummarization.

8 data administration activities are reservedmostly for data at higher levels of , data at lower levels of detail are usually INFORMATION HIERARCHYI nformation is vital to any organization. End-users accessinformation for analysis and decision-making activities. Asthe needs of the enterprise require, access to a datawarehouse range from infrequent and vague requests tofrequent, repetitive, and specific requests. Questions andanswers about end-users as well as the information mostoften requested appear in Figure Who requests information from a data Warehouse ?

9 A. End-users with varying degrees of At what levels of the organization do end-users support?A. Top, middle, and line What type of information is provided to top management?A. Summary and strategic What type of information is provided to middle management?A. Specialized reports, spreadsheet analysis, and informational What type of information is provided to line management?A. Daily, weekly, and monthly reports. Figure 2. Questions and Answers - THE data WAREHOUSEA ccess to data Warehouse data is usually accomplished witha query.

10 In Inmon and Hackathorn's book, they define aquery as, "a request for access to information in the datawarehouse, with possibly some processing of that databefore the results of the query are returned to the end user."They have classified end-user queries using the followingtaxonomy, see Figure at a lot of data . < or >Looks at only a record or repeatedly. < or >Submitted only once. Figure 3. Taxonomy of provide greater insight by describing unique end-usercharacteristics and how queries are typically used in a datawarehouse environment as illustrated in Figure QueryPrecanned.