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. It stores and organizes operationalenterprise-wide data consisting of a mix of olderunintegrated "legacy" application systems.

2 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. 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.

3 " 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. 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 .

4 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. 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.

5 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. 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.

6 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 ? 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?

7 A. Daily, weekly, and monthly reports. Figure 2. Questions and Answers - THE data WAREHOUSEA ccess to data Warehouse data is usually accomplished witha query. 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. End-user knowswhat is being QueryIterative process of submittinga query, looking at the results,modifying the query, andresubmitting it.

8 Figure 4. Planned versus Ad-hoc TutorialsAdvanced Tutorials3 EXAMPLES OF SQL QUERIESR etrieve and Display DataTo extract and retrieve data , the SQL Procedure's SELECT statement is used. In the next example the desired columnsor variables are displayed in the order indicated in theSELECT SQL; SELECT SSN, SEXFROM ;QUIT;In this example, the columns SSN (Social SecurityNumber) and SEX from the PATIENTS data set areselected and and Display data in GroupsTo sum and display data in groups, the GROUP BYstatement is used. The GROUP BY clause is used whena summary function is used in a query. The column beingsummed is WEIGHT (Patient's Weight) with the results ofthe query being grouped by SEX (Patient's Gender).PROC SQL; SELECT SEX, SUM(WEIGHT) AS TOTWEIGH FROM /* Shortness of Breath */WHERE SYMPTOM='10'GROUP BY SEX;QUIT;In this example, let's first examine the significance of theWHERE clause.

9 It tells the SQL processor to extract onlythose rows (records) that contain a value of '10' (Shortnessof Breath) in the SYMPTOM column. Rows not meetingthis criteria are automatically excluded from the , the columns SEX (gender) and WEIGHT areselected from the PATIENTS data set. Next, the SQLprocessor groups rows (in ascending order) by the valuefound in the column SEX and totals each patient's weightstoring the results in TOTWEIGH. Finally, the columnsSEX and TOTWEIGH from the PATIENTS data set areselected and Results in Ascending OrderTo arrange results in ascending order, we will direct SQLby using the ORDER BY clause. One or more columnscan be selected for sorting. One or more columns can beordered in either ascending and/or descending order. Thedefault sort order is ascending (lowest to highest). Tooverride the default order (ascending order), you need tospecify DESC (arrange in descending order) following thecolumn-name that is SQL; SELECT LASTNAME, EDUCFROM BY EDUC;QUIT;In this example, all rows are first arranged in ascendingorder by the column EDUC (patient's years of education).

10 Then the columns LASTNAME and EDUC from thePATIENTS data set are selected and DataIn the following query, the patients weight is summed andthen are grouped according to the region of the countrythey live SQL; SELECT REGION, SUM(WEIGHT)FROM BY REGION;QUIT;Using Summary FunctionsThe following example illustrates how the MAX summaryfunction can be used to obtain the maximum (largest) datevalue in each row within the Patients SQL; SELECT SSN, SEX,MAX(DOB,DOD,EXAM_DT,DIAG_DT,ADMIT_DT ,REL_DT) ASROWDATE FORMAT= ;QUIT;Using the BETWEEN ConditionThe BETWEEN condition selects rows having columnvalues within a designated range of data values. Thefollowing example illustrates how patients residing inRegion '1' through '3' can be SQL; SELECT REGION, SSNFROM REGION BETWEEN '1' AND '3';QUIT;Advanced TutorialsAdvanced Tutorials4 Using the IN ConditionThe IN condition selects one or more rows based on thematching of a column value to a defined set of values.