Microsoft SQL Server Analysis Services is an OLAP database that exposes data as cubes, which you query with MDX (multidimensional expressions). The driver models these cubes in relational views that you can query with SQL-92, or MDX.

Accessing Cubes as Views

Microsoft SQL Server Analysis Services is fundamentally different from a relational database; reflecting these differences, the CData ODBC Driver for Microsoft SQL Server Analysis Services does not support many SQL-92 features found in a driver for an RDBMS. To bypass any limitations with query translation, you can submit pass-through MDX queries from the driver.

See Retrieving Analysis Services Data for query limitations and more information on querying Microsoft SQL Server Analysis Services.

Modeling Views Dynamically

You can query all of the cubes in your Microsoft SQL Server Analysis Services instance; when you connect, the driver retrieves the metadata from Microsoft SQL Server Analysis Services and displays the available dimensions and measures as columns for the views (cubes). The driver dynamically updates the schemas over subsequent connections.

Instead of retrieving metadata every connection, you can set CacheLocation.

Measure Attributes

By default, all measure attributes are listed in a 'Measures' view. However, you can set SplitMeasures to 'true' to split the measures view; the result is each measure attribute is included in its respective view based on the Measure Group value. Further classification based on 'Measure Directories' is not included.

Accessing Schema Information

Query the System Tables to access schema information and other driver metadata.

Dynamic Management Views

Dynamic Management Views (DMVs) are queries that return information about model objects, server operations, and the server health of your Analysis Services instance. These objects can be queried through the driver by setting UseMDX to 'true' and issuing the desired query:

SELECT * FROM $System.DBSchema_Tables   
WHERE TABLE_TYPE = 'SCHEMA'   
ORDER BY TABLE_NAME ASC  

Note that the DMV query syntax is based on a SQL SELECT statement, but does not support its full syntax. See the Analysis Services documentation on DMVs for more information.