oasus: fall 2008 introduction to sas olap: a solution for the curious and impatient presented by:...

OASUS: FALL 2008

Introduction to SAS OLAP:

A Solution for the

Curious and Impatient

Presented by:

Josée Ranger-Lacroix

SAS Institute (Canada) Inc.

2 2

Introduction to SAS OLAP - Agenda

What is OLAP?

OLAP TERMINOLOGY

SAS OLAP Tools

SAS OLAP Applications: A demonstration

3 3

Introduction to SAS OLAP

What is OLAP? What is OLAP?

OLAP TerminologyOLAP Terminology

SAS OLAP Tools


4 4

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: offer high-performance access to large amounts

of presummarized data give users the power to retrieve answers to

multidimensional business questions quickly and easily

provide slice-and-dice views of multiple relationships in large quantities of presummarized data

5 5

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

Why Use Presummarized Data?

6 6

OLAP CubesCentral 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.

7 7

What Is an OLAP Cube?

An OLAP cube has the following characteristics: is a specialized storage facility that enables data

to be pulled for storage in a matrix type of format enables users to quickly retrieve multiple levels of

presummarized data through a multidimensional view organizes data in a hierarchical arrangement 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.

MDX is the standard language to query a multi-dimensional database and it’s cubes.

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.

8 8

Category Group Year Quarter Quantity

Clothes Eclipse Clothing 2002 1 881




Clothes Green Tomato 2002 1 53




Shoes Eclipse Shoes 2002 1 1508




Shoes Tracker Shoes 2002 1 814




Category Group Year Quantity

Clothes Eclipse Clothing

2002 7474

Clothes Green Tomato

2002 480

Shoes Eclipse Shoes

2002 8284

Shoes Tracker Shoes

2002 4692

AggregationAn aggregation isthe summarizationof oneor more levels.

...

9 9

SAS OLAP CubesA SAS OLAP cube contains summarized information.

NWAY Crossing

Aggregations

OLAP Cube

1010

The Time Dimension

OLAP TerminologyDimensions consist of

2003 2004 2005

Q1 Q2 Q3 Q4 Q1 Q2Q3 Q4

...

YQ Time

Dimension Name

Topic of a dimension

1111


2003 2004 2005

Q1 Q2 Q3 Q4 Q1 Q2Q3 Q4

...

YEAR

QUARTER

YQ Time

The Time Dimension Dimension Name


Level A level of detail within a dimension

1212


YQ Time

2003 2004 2005

Q1 Q2 Q3 Q4 Q1 Q2Q3 Q4

...

Increased Level of Detail




Hierarchy Order of the levels of detail within a dimension

1313


2003 2004 2005

Q1 Q2 Q3 Q4 Q1Q3 Q4

...

YQ Time




Hierarchy Order of the levels of detail within a dimension

Member Individual category values

Q2

1414

Jul Aug Sep Oct Nov Dec

Apr May JunJan Feb Mar Jul Aug Sep Oct Nov Dec

Dimensions and HierarchiesA dimension can have multiple hierarchies.

YQ Time Hierarchy

2003 2004 2005

Q1 Q2 Q3 Q4 Q1 Q2Q3 Q4

The Time Dimension

YM Time Hierarchy

2003 2004 2005

Apr May JunJan Feb Mar

...

1515

SAS OLAP Cube ComponentsThe components of a SAS OLAP cube can be logically organized into dimensions, hierarchies, and levels.

Cube

Dimension

Measure

Hierarchy

Level

1616

OLAP OperationsSlice: 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

1717

Data Warehouse and OLAP systemsUsually based on relational technology

Stores tactical information that answer “who” and “what”

Gives you information about the past events

Uses a multidimensional view of aggregate data

Provide quick access to strategic information to answer “who” and “what” but also “what if” and “why”

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.

1818

Who uses OLAP and What for?Finance

Sales

Marketing

Manufacturing

Activity-based costing (allocations)Financial performance analysisFinancial modeling

Sales analysis and forecasting

Market research analysisSales forecastingPromotions analysisCustomer analysisMarket/Customer segmentation

Production planningDefect analysis

1919

OLAP 3 Key Features

Multidimensional views of data

Calculation-intensive capabilities

Time Intelligence

2020

OLAP Benefits

Increase the productivity of business managers, developers and whole organizations

Flexibility Enables managers to model problemsMore 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

2121

Codd’s 12 Rules1. Multidimensional conceptual view2. Transparency3. Accessibility4. Consistent reporting performance5. Client/server architecture6. Generic dimensionality7. Dynamic sparse matrix handling8. Multi-user support9. Unrestricted cross-dimensional operations10. Intuitive data manipulation11.Flexible reporting12.Unlimited dimensions and aggregation levels

2222




SAS OLAP ToolsSAS OLAP Tools


2323

SAS OLAP ServerAs part of the SAS Intelligence Platform, the SAS OLAP Server is a standards-compliant OLAP data source that retrieves results for Multidimensional eXpression (MDX) queries.

The SAS OLAP Server does the following: quickly delivers summarized data to business

intelligence applications in a format that enables on-the-fly processing and reporting

lowers the burden on IT resources by simplifying ad hoc reporting and data summaries

uses common metadata for efficiently developing and delivering reports across the enterprises

2424

For the Modeler: SAS OLAP Cube StudioSAS 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.

2525

Using the Cube Designer WizardThe Cube Designer wizard is used for the following: create and edit

cube definitions that are stored in the active metadata repository

build cubes based on the stored definitions

2626

For the User: Viewing Cube DataIn SAS®9 there are several options for viewing cube data, including the following: SAS Enterprise Guide SAS Add-In for Microsoft Office SAS Information Delivery Portal’s Visual Data Explorer SAS Web OLAP Viewer for Java SAS Web OLAP Viewer for .NET SAS Web Report Studio

SAS OLAP cubes can also be viewed in third-party OLAP viewers such as Microsoft Excel.

2727

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

2828

OLAP Data in SAS Web Report StudioMultidimensional 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 represented by a value, row, or column

Select the value, row heading, column heading, or name of the innermost member.

2929

For the User: SAS OLAP AnalyzerSAS Enterprise Guide includes an OLAP Analyzer that enables you to view and analyze data that is stored in a SAS OLAP cube.

Table View

Graph View

Cube View Manager

Task Buttons

3030

The OLAP AnalyzerUsing the OLAP Analyzer, you can accomplish the following: interact with a cube using tables and/or graphs display, create, and edit measures filter the data create bookmarks set preferences use the View Designer Wizard to create a new

OLAP view edit, check, and submit MDX queries

3131

For the User: SAS Web OLAP Viewer for JavaSAS Web OLAP Viewer for Java has the following characteristics: is a Web-based application for viewing SAS OLAP

cubes provides an easy-to-use interface from which you can

select a data source, view the data, and customize your view

is similar to the Visual Data Explorer found in the SAS Information Delivery Portal

3232

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)

Query, Bookmarks, or Navigator Panel

3333

For the User: Microsoft Excel PivotTable ReportsThe SAS OLAP Server exposes OLAP data using the OLE DB for OLAP standard.

SAS OLAP cubes are read into Microsoft Excel using Excel PivotTable reports. To read a SAS OLAP cube, the following conditions must exist: The SAS OLAP Server must be running and

accessible. Read and ReadMetadata permissions must be

granted.

3434

Populating the PivotTable ReportThe PivotTable Field list is used to populate the table with data from the SAS OLAP cube.

3535




6.2: SAS OLAP Tools

SAS OLAP Applications: A demonstrationSAS OLAP Applications: A demonstration

3636

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.

3737

Conclusion

Thank you!

Questions?www.sas.com

oasus: fall 2008 introduction to sas olap: a solution for the curious and impatient presented by:...

Documents

sas olap cube

olap query

sas olap agenda

olap cubes central

olap storage process

analytical processing

levels of data

use presummarized data