Provides an API that enables an application to present information in business terms rather than database schema terms and to create queries to retrieve the underlying physical data.

Query services that support modeling, running and persisting queries based on relational and multidimensional metadata.

# SAS Query Services

SAS Query Services is made up of two major components. One component supports modeling and persisting of logical or business queries. The other component provides support for running those business queries and returning result sets for API users to consume the output data.

## SAS Query Services' Model Component

The first component, the model portion , is made up of the following: the physical model, the business model, the expression model and the step model. These subcomponents model metadata for the various parts of a business query, and that metadata modeling enables the API user to run the query and retrieve data.

The physical model that SAS Query Services provides gives a physical view of the metadata (such as tables, libraries, and cubes) that the logical business model is built upon. Methods on the SAS Query Services' metadata service serve as the starting point for obtaining physical model objects that are contained in a SAS Metadata Server repository upon which information maps are built. The methods on the metadata service and the physical model classes are currently not published or supported.

The business model is a logical layer that is built upon the physical model. The business model is used to model business items that are used in business queries. There are two types of business models: information maps are persistable models that contain a set of business items that is organized into folders and subfolders. Data selections are persistable models that contain a query specification that is based on business items in its own model or in a model that it is based upon, such as an information map.

The expression model is a collection of classes that are used in conjunction with the business model in order to model arbitrarily complex expressions used to create the core definition of data items and filters. Similarly, the step model is a collection of classes that model "steps". These steps are applied to data items in order to provide step-wise filtering capabilities, such as ranking and further filtering for OLAP-related data items.

## SAS Query Services' Data Services Component

The second major component, the data services , supports

- connecting to a relational or OLAP data server

- generating and sending the query statements to the server

- returning result sets to the API user

Most of the work done by data services involves using the business query specification (a DataSelection object) to generate and perform one or more queries or other data server operations in order to run the modeled business query.

Query specifications are stored in business form (as persisted data selections) and not in SQL or any other physical or translated form. Data services performs SQL or MDX language generation and other data server operations each time a query is run. The specific physical query syntax and the implementation of the operations that SAS Query Services' data services component perform for a given data selection do change through the releases as better query generation algorithms and server capabilities are leveraged.

The data services component uses the Connection Service component (com.sas.services.connection) of SAS Foundation Services to connect to SAS data servers in order to execute queries and data operations. The connection service leverages user metadata, such as user ID and password information, from the SAS Metadata Server when attempting to make a connection to a given data server.

SAS Query Services' is integrated with the SAS Foundation Services in that any queries that are modeled or executed must be in the context of a user that is logged-in to the BI Platform. Each user's session context is required when creating or reading in any SAS Query Services models from persistent storage. SAS Query Services also uses the Logging Service component of SAS Foundation Services in order to log errors and other meaningful messages.

## Summary of SAS Query Services' Component Capabilities

The following list contains links to more information about the major functional components of the SAS Query Services API.

- SAS Query Services Models
  - The following two major logical or business models are supported:
    Information Map
        a mapping or set of business items that is based on physical model objects that are populated in a SAS Metadata Server by products such as SAS Management Console, SAS ETL Studio and SAS OLAP Cube Studio.
    Data Selection
        the modeling of a query specification based on business items which can be run by using IQ Data Services in order to obtain output result sets.
  - Support is provided for persisting information maps in the SAS Metadata Server.
  - Support is provided for persisting information maps and data selections via XML.
  - The common portion of the two major models, called the Business Model, provides support for modeling business items. The following types of business items that can be defined in a business model:
    Data Item
        arbitrarily complex logical relational "column-like" item or OLAP measure, dimension, hierarchy or level-related item that potentially has filtering steps applied to it.
    Filter
        arbitrarily complex object that causes less data to be returned with the results of a query (that is, the results of the query are filtered). A filter can be thought of as a portion of a WHERE or HAVING clause in the end query that is run.
    Prompt
        pre-defined parameter that can be used within the expression portion of data items and filters.
  - Business Items are defined in terms of physical model objects that are related to defined data sources. Each data source defines which table or cube is accessible, the schema through which it is to be accessed, and which data server is used to access the data at query time. Thus, a data item can be simply defined as a column in a table. The table is used to define a data source.
  - The expression model and step model are used for specifying core definitions to data items and filters.
  - You can associate a stored process with an information map which causes the stored process to run just prior to the execution of any query that is based on that information map
- SAS Query Services' Data Services
  - Connections to data on SAS IOM data servers are made via the Connection Service and API users can reuse those connections.
  - SQL, MDX and certain SAS PROC Language syntax is generated and sent to SAS data servers so that requests from modeled business queries can be fulfilled.
  - Data access is provided to relational tabular data and OLAP cube data via business queries.
  - Output JDBC result sets are provided when the input is relational data, and OLAP result sets are provided when the input is cube data. OLAP result sets are provided when input is relational data and crosstab functionality is specified in the business query.

For definitions of terms that are used in the package document, see the SAS Query Services' glossary. No APIs currently exist in the com.sas.iquery package; there are only subpackages. This is likely to change in the future.

## SAS Query Services API Examples

The following table has links to code examples of SAS Query Services functionality. The example that is highlighted in yellow contains common code applicable to setting up both relational and multi-dimensional queries. This code shows how to access information maps and their data items, and set up a data selection. The example that is highlighted in blue shows how to display data in a tabular format. The example that is highlighted in green is a complete code flow that uses the common setup, the standard table method, and the optional visual display. The other examples show how to use specific types of functionality.

Multi-dimension Data Table Examples | Relational Data Table Examples
Common Setup | Common Setup
End to end example | End to end example
Standard Table | Standard Table
Table with Totaling | Table with Totaling
Table with Ranking | Table with Ranking
Table with Filtering | Table with Filtering
Table with Calculated Measure | Table with Calculated Column
Sorted Table | Sorted Table
View Output as Table | View Output as Table