OASUS FALL 2008 Introduction to SAS OLAP A

OASUS: FALL 2008 Introduction to SAS OLAP: A Solution for the Curious and Impatient Presented by: Josée Ranger-Lacroix SAS Institute (Canada) Inc.

Introduction to SAS OLAP - Agenda What is OLAP? OLAP TERMINOLOGY SAS OLAP Tools SAS OLAP Applications: A demonstration 2

Introduction to SAS OLAP What is OLAP? OLAP Terminology SAS OLAP Tools SAS OLAP Applications: A demonstration 3

What Is OLAP? Online Analytical Processing (OLAP) is an industry-accepted reporting technology that provides high -performance analysis and easy reporting on large volumes of data. OLAP applications provide the following features: n offer high-performance access to large amounts of presummarized data n give users the power to retrieve answers to multidimensional business questions quickly and easily n provide slice-and-dice views of multiple relationships in large quantities of presummarized data 4

Why Use Presummarized Data? When you create and use summarized tables, application performance can be substantially improved. ØShort response time for analyzing and modeling complex relationships The summarized tables eliminate the need to run summaries at execution time. The presummarized data must be refreshed only when the underlying data changes. ØAbility to provide “just-in-time” information for effective decision-making 5

OLAP Cubes Central to the OLAP storage process are cubes. A cube is a set of data that is organized and structured in a hierarchical, multidimensional arrangement, often with numerous dimensions and levels of data. 6

What Is an OLAP Cube? An OLAP cube has the following characteristics: n is a specialized storage facility that enables data to be pulled for storage in a matrix type of format n enables users to quickly retrieve multiple levels of presummarized data through a multidimensional view n organizes data in a hierarchical arrangement n cubes also contain measures, which are based on numeric analysis columns in the input tables. These numeric values are summarized and stored in the cube for quick access and analysis during a query. n MDX is the standard language to query a multi-dimensional database and it’s cubes. n the output of an OLAP query is typically displayed in a matrix (or pivot) format. The dimensions form the rows and columns of the matrix; the measures form the values. 7

Aggregation An aggregation is the summarization of one or more levels. Category Clothes Group Year Quantity Eclipse 2002 Clothing 7474 Green Tomato 2002 480 Shoes Eclipse Shoes 2002 8284 Shoes Tracker Shoes 2002 4692 Clothes 8 Category Group Year Quarter Quantity Clothes Eclipse Clothing 2002 1 881 Clothes Eclipse Clothing 2002 2 2015 Clothes Eclipse Clothing 2002 3 2454 Clothes Eclipse Clothing 2002 4 2124 Clothes Green Tomato 2002 1 53 Clothes Green Tomato 2002 2 131 Clothes Green Tomato 2002 3 162 Clothes Green Tomato 2002 4 134 Shoes Eclipse Shoes 2002 1 1508 Shoes Eclipse Shoes 2002 2 2421 Shoes Eclipse Shoes 2002 3 2498 Shoes Eclipse Shoes 2002 4 1857 Shoes Tracker Shoes 2002 1 814 Shoes Tracker Shoes 2002 2 1375 Shoes Tracker Shoes 2002 3 1406 Shoes Tracker Shoes 2002 4 1097 . . .

SAS OLAP Cubes A SAS OLAP cube contains summarized information. Aggregations OLAP Cube NWAY Crossing 9

OLAP Terminology Dimensions consist of The Time Dimension Topic of a dimension Name YQ Time 2003 2004 2005 Q 3 Q 4 Q 1 Q 2 10 . . .

OLAP Terminology Dimensions consist of The Time Dimension YQ Time 2003 2004 YEAR Q 1 Q 2 Q 3 Q 4 QUARTER Q 3 Q 4 11 Dimension Topic of a dimension Name Level A level of detail within a dimension 2005 Q 1 Q 2 . . .

OLAP Terminology Dimensions consist of The Time Dimension YQ Time 2003 2004 2005 Q 3 Q 4 Q 1 Q 2 Dimension Topic of a dimension Name Level A level of detail within a dimension Hierarchy Order of the levels of detail within a dimension Increased Level of Detail 12 . . .

OLAP Terminology Dimensions consist of The Time Dimension YQ Time 2003 2004 2005 Q 3 Q 4 Q 1 Q 2 13 Dimension Topic of a dimension Name Level A level of detail within a dimension Hierarchy Order of the levels of detail within a dimension Member Individual category values . . .

Dimensions and Hierarchies A dimension can have multiple hierarchies. The Time Dimension YM Time Hierarchy YQ Time Hierarchy 2003 2004 2005 Jan Feb Mar Apr May Jun Q 3 Q 4 Q 1 Q 2 Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun 14 Jul Aug Sep Oct Nov Dec . . .

SAS OLAP Cube Components The components of a SAS OLAP cube can be logically organized into dimensions, hierarchies, and levels. Hierarchy Dimension Level Cube Measure 15

OLAP Operations Slice: A slice is a subset of a multi-dimensional array corresponding to a single value for one or more members of the dimensions not in the subset. Dice: The dice operation is a slice on more than two dimensions of a data cube (or more than two consecutive slices). Drill Down/Up: Drilling down or up is a specific analytical technique whereby the user navigates among levels of data ranging from the most summarized (up) to the most detailed (down). Roll-up: A roll-up involves computing all of the data relationships for one or more dimensions. To do this, a computational relationship or formula might be defined. Pivot: To change the dimensional orientation of a report or page display 16

Data Warehouse and OLAP systems ØUsually based on relational technology ØUses a multidimensional view of aggregate data ØStores tactical information that answer “who” and “what” ØProvide quick access to strategic information to answer “who” and “what” but also “what if” and “why” ØGives you information about the past events ØEnables decision-making about future decision OLAP and Data Warehouse are complementary. Data Warehouse stores and manages data; OLAP transforms Data Warehouse data into strategic information. 17

Who uses OLAP and What for? 18 Finance üActivity-based costing (allocations) üFinancial performance analysis üFinancial modeling Sales üSales analysis and forecasting Marketing üMarket research analysis üSales forecasting üPromotions analysis üCustomer analysis üMarket/Customer segmentation Manufacturing üProduction planning üDefect analysis

OLAP 3 Key Features v. Multidimensional views of data v. Calculation-intensive capabilities v. Time Intelligence 19

OLAP Benefits üIncrease the productivity of business managers, developers and whole organizations ü Flexibility ü Enables managers to model problems More control and timely access to strategic information=more effective decision-making üIT developpers can deliver applications faster ü Reduces application backlog ü Reduced traffic on transaction systems üWith the ability to model real business problem, businesses are able to respond more quickly to market’s demands = improved revenue and profitability 20

Codd’s 12 Rules 1. Multidimensional conceptual view 2. Transparency 3. Accessibility 4. Consistent reporting performance 5. Client/server architecture 6. Generic dimensionality 7. Dynamic sparse matrix handling 8. Multi-user support 9. Unrestricted cross-dimensional operations 10. Intuitive data manipulation 11. Flexible reporting 12. Unlimited dimensions and aggregation levels 21

Introduction to SAS OLAP What is OLAP? OLAP Terminology SAS OLAP Tools SAS OLAP Applications: A demonstration 22

SAS OLAP Server As part of the SAS Intelligence Platform, the SAS OLAP Server is a standards-compliant OLAP data source that retrieves results for Multidimensional e. Xpression (MDX) queries. The SAS OLAP Server does the following: n quickly delivers summarized data to business intelligence applications in a format that enables on-the-fly processing and reporting n lowers the burden on IT resources by simplifying ad hoc reporting and data summaries n uses common metadata for efficiently developing and delivering reports across the enterprises 23

For the Modeler: SAS OLAP Cube Studio is a Java interface for defining and building OLAP cubes in SAS® 9. The Cube Designer wizard, which guides the user through the process of creating and registering a cube, is the main feature of SAS OLAP Cube Studio and is also available in SAS Data Integration Studio. 24

Using the Cube Designer Wizard The Cube Designer wizard is used for the following: n create and edit cube definitions that are stored in the active metadata repository n build cubes based on the stored definitions 25

For the User: Viewing Cube Data In SAS® 9 there are several options for viewing cube data, including the following: n SAS Enterprise Guide n SAS Add-In for Microsoft Office n SAS Information Delivery Portal’s Visual Data Explorer n SAS Web OLAP Viewer for Java n SAS Web OLAP Viewer for. NET n SAS Web Report Studio SAS OLAP cubes can also be viewed in third-party OLAP viewers such as Microsoft Excel. 26

For the User: SAS Web Report Studio is a reporting application designed for business users who want to view, author, and share reports on the Web. 27

OLAP Data in SAS Web Report Studio Multidimensional data sources provide different ways to interact with reports. If you want to… Then… Rotate the table Select the data item and select Rotate table. Drill down Select next to the data item. Expand Select next to the data item. View detail data Select the value, row heading, represented by a column heading, or name of the value, row, or column innermost member. 28

For the User: SAS OLAP Analyzer SAS Enterprise Guide includes an OLAP Analyzer that enables you to view and analyze data Task that is stored in a SAS OLAP cube. Buttons Table View Graph View Cube View Manager 29

The OLAP Analyzer Using the OLAP Analyzer, you can accomplish the following: n interact with a cube using tables and/or graphs n display, create, and edit measures n filter the data n create bookmarks n set preferences n use the View Designer Wizard to create a new OLAP view n edit, check, and submit MDX queries 30

For the User: SAS Web OLAP Viewer for Java has the following characteristics: n is a Web-based application for viewing SAS OLAP cubes n provides an easy-to-use interface from which you can select a data source, view the data, and customize your view n is similar to the Visual Data Explorer found in the SAS Information Delivery Portal 31

The Main SAS Web OLAP Viewer Page The main SAS Web OLAP Viewer page can contain the following elements: Main Menu and Toolbar Drill Path and Applied Filter Viewers Data Viewer(s) 32 Query, Bookmarks, or Navigator Panel

For the User: Microsoft Excel Pivot. Table Reports The SAS OLAP Server exposes OLAP data using the OLE DB for OLAP standard. SAS OLAP cubes are read into Microsoft Excel using Excel Pivot. Table reports. To read a SAS OLAP cube, the following conditions must exist: n The SAS OLAP Server must be running and accessible. n Read and Read. Metadata permissions must be granted. 33

Populating the Pivot. Table Report The Pivot. Table Field list is used to populate the table with data from the SAS OLAP cube. 34

Introduction to SAS OLAP What is OLAP? OLAP Terminology 6. 2: SAS OLAP Tools SAS OLAP Applications: A demonstration 35

SAS OLAP Benefits ØEasy-to-use interfaces for building and maintaining OLAP cubes. ØCubes can be created based on any data stored in any database on any operating system. ØOLAP cubes can be stored on any major hardware platform, from Microsoft Server 2003, HP/UX, AIX and Solaris, up to z/OS on mainframes. ØOLAP data storage and navigation are integrated into the SAS BI reporting environment. ØFully compliant with Microsoft's OLE DB for OLAP standard so users can choose their favorite front-end for accessing summarized information. ØFull-featured interactive OLAP client applications allow users to take advantage of OLAP functions as needed to match their needs and skills. 36

Conclusion üThank you! üQuestions? www. sas. com 37