This topic has not yet been rated - Rate this topic

Retrieving Metadata from an Analytical Data Source

SQL Server 2012

Metadata is important to applications that retrieve and work with analytical data. When retrieving data from a relational data source, the dimensionality of such data is predictable, even with nested datasets. Result sets from a relational database are typically two-dimensional or scalar in structure. However, data retrieved from analytical data sources can be of variable dimensionality, organized along potentially deep hierarchies.

To handle the complexity of metadata retrieval from analytical data sources, ADOMD.NET provides two forms of metadata retrieval:

The Object Model

The ADOMD.NET object model is generally easier to use than schema rowsets. For most scenarios, you can access the metadata of various database objects just by using the object model. ADOMD.NET exposes the object model through the AdomdConnection.

For more information: Working with the ADOMD.NET Object Model

Schema Rowsets

A complete, but more difficult approach to retrieving metadata is through using schema rowsets. A schema rowset is an OLE DB rowset that encapsulates the description for all objects of a particular type in the database. Schema information in an analytical data source includes databases or catalogs available from the data source, cubes and mining models in a database, roles that exist for cubes at the data source, and so on. This metadata can be retrieved by using the GetSchemaDataSet method, passing in either a GUID or an XML for Analysis (XMLA) name.

For more information: Working with Schema Rowsets in ADOMD.NET

Each of these metadata retrieval methods access different types of metadata. The following table describes the different metadata available for each method, and the methods used to access it.

GUID (used in Schema Rowsets)	XMLA Name (used in Schema Rowsets)	ADOMD.NET Object Model
Actions	MDSCHEMA_ACTIONS Rowset
Catalogs	DBSCHEMA_CATALOGS Rowset
Columns	DBSCHEMA_COLUMNS Rowset
Connections	DISCOVER_CONNECTIONS
Cubes	MDSCHEMA_CUBES Rowset	AdomdConnection.Cubes
DataSources	DISCOVER_DATASOURCES Rowset
DBConnections	DISCOVER_DB_CONNECTIONS
Dimensions	MDSCHEMA_DIMENSIONS Rowset	AdomdConnection.Cubes[].Dimensions
DimensionStat	DISCOVER_DIMENSION_STAT
Enumerators	DISCOVER_ENUMERATORS Rowset
Functions	MDSCHEMA_FUNCTIONS Rowset
Hierarchies	MDSCHEMA_HIERARCHIES Rowset	AdomdConnection.Cubes[].Dimensions[].Hierarchies
InputDataSources	MDSCHEMA_INPUT_DATASOURCES Rowset
Instances	DISCOVER_INSTANCES Rowset
Jobs	DISCOVER_JOBS
Keywords	DISCOVER_KEYWORDS Rowset (OLE DB for OLAP)
Kpis	MDSCHEMA_KPIS Rowset	AdomdConnection.Cubes[].KPIs
Levels	MDSCHEMA_LEVELS Rowset	AdomdConnection.Cubes[].Dimensions[].Hierarchies[].Levels
Literals	DISCOVER_LITERALS Rowset
Locations	DISCOVER_LOCATIONS
Locks	DISCOVER_LOCKS
MasterKey	DISCOVER_MASTER_KEY
MeasureGroupDimensions	MDSCHEMA_MEASUREGROUP_DIMENSIONS Rowset
MeasureGroups	MDSCHEMA_MEASUREGROUPS Rowset
Measures	MDSCHEMA_MEASURES Rowset	AdomdConnection.Cubes[].Measures
MemberProperties	MDSCHEMA_PROPERTIES Rowset	PropertyCollection available from most major ADOMD.NET objects.
Members	MDSCHEMA_MEMBERS Rowset	AdomdConnection.Cubes[].Dimensions[].Hierarchies[].Levels[].GetMembers()
MemoryGrant	DISCOVER_MEMORYGRANT
MemoryUsage	DISCOVER_MEMORYUSAGE
MiningColumns	DMSCHEMA_MINING_COLUMNS Rowset	AdomdConnection.MiningModels[].MiningModelColumns
MiningFunctions	DMSCHEMA_MINING_FUNCTIONS Rowset
MiningModelContent	DMSCHEMA_MINING_MODEL_CONTENT Rowset	AdomdConnection.MiningModels[].MiningContentNodes
MiningModelContentPmml	DMSCHEMA_MINING_MODEL_CONTENT_PMML Rowset
MiningModels	DMSCHEMA_MINING_MODELS Rowset	AdomdConnection.MiningModels
MiningModelXml	DMSCHEMA_MINING_MODEL_XML Rowset
MiningServiceParameters	DMSCHEMA_MINING_SERVICE_PARAMETERS Rowset	AdomdConnection.MiningServices[].MiningServiceParameters
MiningServices	DMSCHEMA_MINING_SERVICES Rowset	AdomdConnection.MiningServices
MiningStructureColumns	DMSCHEMA_MINING_STRUCTURE_COLUMNS Rowset	AdomdConnection.MiningStructures[].MiningStructureColumns
MiningStructures	DMSCHEMA_MINING_STRUCTURES Rowset	AdomdConnection.MiningStructures
PartitionDimensionStat	DISCOVER_PARTITION_DIMENSION_STAT
PartitionStat	DISCOVER_PARTITION_STAT
PerformanceCounters	DISCOVER_PERFORMANCE_COUNTERS
ProviderTypes	DBSCHEMA_PROVIDER_TYPES Rowset
SchemaRowsets	DISCOVER_SCHEMA_ROWSETS Rowset
Sessions	DISCOVER_SESSIONS
Sets	MDSCHEMA_SETS Rowset	AdomdConnection.Cubes[].NamedSets
Tables	DBSCHEMA_TABLES Rowset
TablesInfo	DBSCHEMA_TABLES_INFO
TraceColumns	DISCOVER_TRACE_COLUMNS
TraceDefinitionProviderInfo	DISCOVER_TRACE_DEFINITION_PROVIDERINFO
TraceEventCategories	DISCOVER_TRACE_EVENT_CATEGORIES
Traces	DISCOVER_TRACES
Transactions	DISCOVER_TRANSACTIONS
XmlaProperties	DISCOVER_PROPERTIES Rowset
XmlMetadata	DISCOVER_XML_METADATA Rowset

Reference

ADOMD.NET Client Programming

Analysis Services Schema Rowsets

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD