Intelligent Storage

Using SAS OLAP Server 9 Gary Jackson SAS Institute EMEA 19 June 2003

Copyright © 2003, SAS Institute Inc. All rights reserved.

Intelligent Storage

Using SAS OLAP Server 9 OLAP Concepts

Copyright © 2003, SAS Institute Inc. All rights reserved.

What is OLAP? On-Line Analytical Processing or OLAP can be seen as a technology that attempts to predict the most asked questions – and give users answers fast via the web or through client based applications. It is also a powerful “ad-hoc” reporting tool that allows users to pick and choose what pre-defined data variables they want to report on.

Copyright © 2003, SAS Institute Inc. All rights reserved.

3

How does OLAP work? OLAP is the process of building and reporting on summarized data using differing types of data storage. In SAS 8, these structures include pre-summarized SAS tables – also called “cubes” or in SAS terminology MDDBs – multi-dimensional data bases. This was a SAS proprietary OLAP data structure. SAS OLAP could be either – MOLAP, ROLAP, or HOLAP.

Copyright © 2003, SAS Institute Inc. All rights reserved.

4

Multidimensional OLAP Or MOLAP, summarize transactions into multidimensional views ahead of time. Can consist of a single cube. Queries should be extremely fast, because the summarization has already been done.

Copyright © 2003, SAS Institute Inc. All rights reserved.

5

Relational OLAP Or ROLAP tools extract data from traditional relational databases. Using SQL against relational tables, ROLAP is able to create drillable views dynamically. Also used on data with varying attributes where it is difficult to build a cube.

Copyright © 2003, SAS Institute Inc. All rights reserved.

6

Hybrid OLAP Hybrid OLAP. HOLAP combines the functionality of both ROLAP and MOLAP. Uses a proxy or virtual cube to point to relational database, SAS tables, or multiple cubes and all are viewed seamlessly by the user.

Copyright © 2003, SAS Institute Inc. All rights reserved.

7

Intelligent Storage

Using SAS OLAP Server 9 What is SAS OLAP Server 9?

Copyright © 2003, SAS Institute Inc. All rights reserved.

What is SAS OLAP Server 9? SAS OLAP Server is a multidimensional database that significantly speeds up business intelligence and analytical applications, where they require interactive navigation through highly summarized data, generated from large sets of detailed data. ! Increased confidence in delivered information ! Maintains speed of business intelligence applications ! Open for all data sources, hosts, and consumers

Copyright © 2003, SAS Institute Inc. All rights reserved.

9

What does SAS® OLAP Server do? ! Data is automatically partitioned and utilizes fast querying with Intelligent Storage technology. ! Relies on the SAS Metadata Architecture which adheres to the Common Warehouse Metadata standards.

RDBMS Meta Data

SPDE Staging

! Lone administration API via the OLAP Cube Studio (called as Cube Designer from ETL OLAP Server Studio). ! Ability to query OLAP data using MDX. Copyright © 2003, SAS Institute Inc. All rights reserved.

SAS Metadata Server

Sa Cu les be

Schema

Cu Cu stom

be

er

OLAP Server

Sa Cu lar be y

Schema

10

How can you use SAS OLAP Server 9? ! You can view your SAS OLAP cubes immediately using Enterprise Guide, Excel, or any viewers that adhere to the Open OLAP standard. ! Build Information Maps to turn meaningless variable names into meaningful tags and descriptions for consumers. ! Then use the Information Maps within Web Report Studio and ID Portal where users can create their own queries and calculated fields. Copyright © 2003, SAS Institute Inc. All rights reserved.

OLAP Server

Sa Cu les be

Cu Cu stom

be

er

Sa Cu lar be y

OLAP Server Schema

Schema

Clients

Info Info Info Map 1 Map 2 Map 3 Information Map Meta

WebReport Studio

BI Viewers 11

Using the Intelligence Storage Family SPDE Staging User Data SPDS License

SPDS Name Server

SPDS Domain Data Server

OLAP Server

Sa Cu les be

Schema

Copyright © 2003, SAS Institute Inc. All rights reserved.

SPDS Domain Data Server

Cu Cu stom

be

er

OLAP Server

Sa Cu lar be y

Schema

12

How does SAS OLAP Server 9 work? CPU

! Queries and index

creation can also be kicked off in parallel.

C:\path\CUBENAME

data meta

hybrid index

IO Channel

a complex query, it first searches it’s data metadata which tells it what physical file partition holds their information.

IO Channel

! When a user submits

CPU

IO Channel

ETL Studio

CPU

disk1

disk2

disk3

1

2

3

4

5

6

7

8

9

SPDS Table Copyright © 2003, SAS Institute Inc. All rights reserved.

13

SAS OLAP Server 9 Important Points ! SAS 9 OLAP implementations now are MOLAP (but remember data is automatically broken up into data partitions much like HOLAP racking) solutions – which means a single large cube answers all user queries quickly.

! OLAP now is built utilizing specific functionality of Intelligent Storage technology. OLAP performance is limited only by the hardware configuration of the server.

! But when we say one cube, a SAS 9 cube actually consists of dozens (possibly hundreds) of Intelligent Storage files (metadata, hybrid index, and data) distributed across a data path (for best results, a striped data path).

Copyright © 2003, SAS Institute Inc. All rights reserved.

14

Intelligent Storage

Using SAS OLAP Server 9

Acting as a SAS OLAP Server User Copyright © 2003, SAS Institute Inc. All rights reserved.

Acting as the OLAP User within Classic SAS PROC OLAP statement: Metadata Connection PROC OLAP Data=sosstage.CURRINCIDS_STAGE cube=currincids Path=".\Data\Olap_data" Description="Uses currincids_stage"; METASVR host="localhost" port=7994 protocol=bridge userid=" " pw=" " repository="Project:Olapuser1_SOS" olap_schema="Main Olap Server - OLAP Schema";

! The Base Table must be registered in the metadata via ETL Studio or via the Metadata LIBNAME engine.

! A connection to the metadata server when submitting a PROC OLAP statement is mandatory. You must also specify the repository. Data location is transparent to the user.

! The OLAP Server must be assigned a schema that basically dictates to the OLAP Server what cubes it is responsible for. Copyright © 2003, SAS Institute Inc. All rights reserved.

16

Acting as the OLAP User within Classic SAS PROC OLAP statement: Dimensions, Hierachies, and Levels DIMENSION Organization hierarchies=(Org_Legacy) CAPTION='Organization' SORT_ORDER=ASCENDING ; HIERARCHY Org_Legacy levels=( ownctry ownloc owndiv owndept ownmgr ) CAPTION='Organization Legacy'; LEVEL LEVEL LEVEL LEVEL LEVEL

ownctry ownloc owndiv owndept ownmgr

CAPTION='Country code for empno' SORT_ORDER=ASCENDING; CAPTION='Location code' SORT_ORDER=ASCENDING; CAPTION='Owner division' SORT_ORDER=ASCENDING; CAPTION='Owner department' SORT_ORDER=ASCENDING; CAPTION='Mgr# of empno' SORT_ORDER=ASCENDING;

! A Dimension can consist of multiple Hierarchies. ! A Hierarchy consists of different listings of Levels. But a ! !

Hierarchy name cannot be the same name as a Level. A Level is the variable a user drills down on. In the OLAP cube they must be in drill down order (broad to specific). All Levels used within all Hierarchies must be declared.

Copyright © 2003, SAS Institute Inc. All rights reserved.

17

Acting as the OLAP User within Classic SAS PROC OLAP statement: Measures and Aggregations MEASURE incid_tothoursAVG STAT=AVG COLUMN=incid_tothours CAPTION='incid_tothoursAVG'; AGGREGATION ownctry ownloc owndiv owndept ownmgr open_year open_month open_qtr open_weekstart incidtype RUN;

Copyright © 2003, SAS Institute Inc. All rights reserved.

/ NAME='Default';

18

Acting as the OLAP User within ETL Studio

Copyright © 2003, SAS Institute Inc. All rights reserved.

19

Acting as the OLAP User within Cube Studio

Copyright © 2003, SAS Institute Inc. All rights reserved.

20

Intelligent Storage

Using SAS OLAP Server 9 Demo

Copyright © 2003, SAS Institute Inc. All rights reserved.

Using SAS 9 OLAP Server Summary

! Usability End users use OLAP Cube Studio (also available through ETL Studio as Cube Designer). User not required to know PROC OLAP code only data they are building from.

! Scalability Utilizes Intelligent Storage technology for fast querying and automatically partitions data (but is not HOLAP).

! Manageability Integrated with the SAS Metadata Architecture and security authorization is controlled via the SAS Management Console.

! Interoperability Queries are performed by industry standardized MDX query language, much like SQL. Copyright © 2003, SAS Institute Inc. All rights reserved.

22

More Information or Feedback ! Demo Room • Thursday

! Presentations • “Intelligent Storage,” Thursday, 8:35 • “Intelligent Storage: Results from real life testing,” Thursday, 10:50 • “Strategic business decisions powered by SAS OLAP,” Thursday, 2:15

! mailto:[email protected] or mailto:[email protected]

Copyright © 2003, SAS Institute Inc. All rights reserved.

23

Copyright © 2003, SAS Institute Inc. All rights reserved.

24