Technical Solutions Using Oracle and SAS
Andrew Holdsworth, Director World and ISV Performance, Server Technologies, Oracle Corporation and Howard Plemmons, Senior Software Manager, Database Interface Development, SAS Institute
This paper attempts to communicate to both Oracle and SAS users both product specific technical information and the nature of the Engineering relationships between the two companies. If also provides some case study information concerning Oracle and SAS questions along with performance considerations when using the two products.
Oracle and SAS Development Relationship
Oracle has been supporting SAS Institute directly out of the database development organization for just over two years now. What this means is that SAS developers have direct access to the database development organization for technical questions and advice, rapid bug escalation and early availability of software.
The goal of this activity is to optimize the SAS/Oracle stack for SAS analytical data processing and SAS applications. As this relationship matures it is hoped that the SAS analytical engine is able to generate more complex, efficient and scalable SQL for the Oracle Database Server to process.
Both Oracle and SAS are fully committed to working on engineering issues to improve the user productivity of the Oracle/SAS product stack. Both Oracle and SAS have and will continue to attend each other’s user conferences on joint platform to give and receive user feedback.
Oracle and SAS Support Matrix
SAS has supported Oracle data access for many years on many different platforms and OS/Oracle combinations. For the new release of SAS 9, GA 2003, Oracle release support will be Oracle 8.1.7 and up, Oracle 9i and Oracle 10G, if supported by Oracle, on the following platform/OS combinations:
Windows – NT, XP, 2000, W64
AIX 64 bit running OS 5L
HPUX 11i 64 bit
Solaris V8 64 bit
MVS – ZOS and OS390
SAS provides information off their technical support web site that shows specific Oracle versions that have been tested with specific SAS releases. To view this support matrix go to the following link:
This web application provides you with release/platform lookup capabilities for SAS/ACCESS by DBMS by Platform. If you have any support issues you can contact SAS Technical Support.
Current Oracle Technology Adoption at SAS
Partitioning and Parallelism
Oracle Database 10g New Features Applicable to SAS
Wide Table Selects
The feature is enabled automatically. It improves performance of large data set selects.
New CBO algorithms and costing model
LOBS performance improvements
Data pump Export and Import Utilities
The utilities provide very high-speed bulk movement of data and metadata from one database to another.
Enhanced Wait Model
Enhanced Database Time Model
End-to-End Application Tracing
Automatic Workload Repository
OLAP support for Analytic Applications
Grouped table outer join.
Increased number of aggregates per query
Globalization and Unicode improvements
CLOB and NCLOB implicit conversions
Globalization Development kit
Unicode 3.2 support
Case-Insensitive and Accent-Insensitive Query and Sort
Enhanced CONNECT BY support
SQL Regular Expressions
.NET and 64 bit Windows support
SAS System Overview
At the core of the SAS System is the Intelligence Architecture, the foundation of the SAS Intelligence model as shown above in Figure 1. The Intelligence Architecture contains the technology to perform ETL, business intelligence and analytical intelligence, leveraging the manageability, interoperability, usability, and scalability of the SAS system. Over 60 products are included in the Intelligence Architecture and are fully integrated to offer robust and portable functions of the SAS system.
Below the Intelligence Architecture is the infrastructure layer that consists of ERP, DBMS, mainframes, and various data sources. Regardless of the infrastructure, SAS can complement the existing infrastructure and leverage the IT investment to optimize total cost of ownership. For example, if the infrastructure consists of Oracle 9i running on HPUX 11i 64 bit, SAS provides the technology to access the data using SAS/ACCESS Interface to Oracle product. In addition, SAS fully integrates and cleanses the Oracle data, preparing the data to be analyzed with SAS software.
As described above the SAS/ACCESS Interface to Oracle product, it is one of many components of the Intelligence Architecture. In order to apply and gain any intelligence, accessing the data is the first critical step. Once the SAS system is plugged into your infrastructure, the power of SAS can be optimized. Leveraging 27 years of SAS knowledge and experience, the Intelligence Architecture also delivers a comprehensive suite of analytical and business intelligence software. Bundled into the SAS solutions, the technology provides you with insight and the power to know your customers, organization and suppliers. SAS gives you the intelligence and “The Power to Know.”
SAS Intelligence Value Chain SAS 9
The SAS Intelligence Value Chain is a framework for delivering consistent, quality enterprise intelligence, emphasizing the incremental value that each component brings and the additional value gained from these components being integrated. The SAS/ACCESS to Oracle product fits into the ETL(q) link in the chain. The SAS/ACCESS engine provides extraction and loading services (EL components of ETL) for applications, products and solutions in SAS.