Working with Schema Rowsets in ADOMD.NET


Applies To: SQL Server 2016 Preview

When you need more metadata than is available in the ADOMD.NET object model, ADOMD.NET provides the capability to retrieve the full range of XML for Analysis (XMLA), OLE DB, OLE DB for OLAP, and OLE DB for Data Mining schema rowsets:

XML for Analysis metadata
The XML for Analysis schema rowsets provide a method for retrieving low-level information about the server. Information available includes the data sources available on the server, the keywords reserved by the provider, the literals supported by the provider, and more. You can even use an XML for Analysis schema rowset to discover all schema rowsets supported by the provider.

For more information: XML for Analysis Schema Rowsets

OLE DB metadata
The OLE DB schema rowsets provide an industry-standard method for retrieving information from a variety of providers.

For more information: OLE DB Schema Rowsets

OLAP metadata
Schema information provided for an analytical data source includes databases or catalogs available from the analytical data source, cubes and mining models in a database, roles that exist for cubes at the data source, and more.

For more information: OLE DB for OLAP Schema Rowsets

Data Mining metadata
In addition to OLAP metadata, data mining metadata can be retrieved using schema rowsets. The available rowsets expose information on the available data mining models in the database, the available mining algorithms, the parameters that the algorithm require, mining structures, and more.

For more information: Data Mining Schema Rowsets

For each of these various schema rowsets, you retrieve metadata from the rowset by passing either a GUID or XMLA name with the GetSchemaDataSet method of the AdomdConnection object.

The AdomdSchemaGuid class contains a list of fields that represent the schema rowsets most commonly supported by providers and analytical data sources. To retrieve both general and provider-specific metadata from a provider or analytical data source, you use the GUIDs contained within the AdomdSchemaGuid object with the either of the following methods:

System_CAPS_ICON_note.jpg Note

The ADOMD.NET data provider exposes schema information through functionality made available by your specific provider and analytical data source. Each provider and data source may provide different metadata.

The following methods take as arguments the XMLA schema name that identifies which schema information to return, and an array of restrictions on those returned columns:

Each of these methods returns an instance of a DataSet object that is populated with the schema information. The DataSet object is from the System.Data namespace of the Microsoft .NET Framework Class Library.

In the following example, the GetActions function takes a connection, the cube name, a coordinate, and a coordinate type, retrieves an MDSCHEMA_ACTIONS Rowset, and returns the actions available on the selected coordinate.

        //The following function can be called with the following data:
        //ae.GetActions(conn, "Adventure Works","[Geography].[City]",6 );

        //This would return a DataSet containing the actions available for cells
        //in the Adventure Works cube on [Geography].[City].
        private System.Data.DataSet GetActions(AdomdConnection Connection, string Cube, string Coordinate, int CoordinateType)
            //Create a restriction collection to restrict the schema information to be returned.
            AdomdRestrictionCollection restrictions= new AdomdRestrictionCollection();
            restrictions.Add("CUBE_NAME", Cube);
            restrictions.Add("COORDINATE", Coordinate);
            restrictions.Add("COORDINATE_TYPE", CoordinateType); //6 = Cell coordinate

            //Open and return a schema rowset, given the correct restictions
            return Connection.GetSchemaDataSet("MDSCHEMA_ACTIONS", restrictions);

Retrieving Metadata from an Analytical Data Source

Community Additions