How to pull metadata from Analysis Services (or cubes)

Check list of all posts

As known, you can retrieve metadata dictionary from SQL server database engine, such as tables, views, functions etc.  Unfortunately, there is no metadata dictionary available for analysis services. (It is said to be available in next version). Therefore, there is NO easy way to pull meta data from analysis services, such as cube, dimensions, hierarchies and etc.  Based on current status, there are two solutions to pull meta data: 1) using ADOMD.NET; 2) using XML meta data (either .asdatabase or .xmla).  

1. ADOMD.NET is a .NET object model, used for building client applications that access a XML for Analysis 1.1 compliant data provider, such as that provided by the XML for Analysis 1.1 SDK.  This object model is so powerful that you can do almost everything that can be done manually.  The effort is relatively big, which may not be a preferred solution.

2. There are two kinds of XML meta data: .asdatabase metadata is generated by BIDS, which is used to deploy Analysis services; while   .xmla meta data can be generated either by AS deployment wizard or SSMS.


The solution below is using InsuranceMart.asdatabase using OPENXML Method; assume that InsuranceMart.asdatabase is saved under C:\ drive. (Alternatively, you can use Nodes or query method as well, dependant on your needs)


--OPENXML

--1. Save Meta data as xml data type in database

GO
--1.1. create table to hold xml
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MartMetadata]') AND type in (N'U'))
DROP TABLE [dbo].[MartMetadata]
GO
CREATE TABLE [dbo].[MartMetadata](
[asdatabase] [xml] NULL
)
GO
--1.2. Import file to table
insert into [MartMetadata]
SELECT CAST(x AS XML)
FROM OPENROWSET( BULK 'C:\InsuranceMart.asdatabase', SINGLE_BLOB) AS T(x)

Go
--1.3. Rip out the xmlns generated by [MartMetadata], because OPENXML cannot handle XML with a namespace designation
update [MartMetadata] SET asdatabase = CAST(REPLACE(CAST(asdatabase AS VARCHAR(MAX)),
' xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"','') AS XML)
            
Go


--2. Query Meta data as requested

--2.1. query Cubename using openXML
declare @x xml
select  @x = asdatabase from MartMetadata
declare @idoc int
execute sp_xml_preparedocument @idoc output, @x
SELECT *
FROM  OPENXML (@idoc, 'Database/Cubes/Cube',2)
WITH (
CubeName  varchar(50) 'Name'
)
execute sp_xml_removedocument @idoc

GO
--2.2. query Dimension using openXML
declare @x xml
select  @x = asdatabase from MartMetadata
declare @idoc int
execute sp_xml_preparedocument @idoc output, @x
SELECT *
FROM  OPENXML (@idoc, 'Database/Cubes/Cube/Dimensions/Dimension',2)
WITH (
CubeName  varchar(50) '../../Name'
,DimensionName  varchar(50) 'Name'
)
execute sp_xml_removedocument @idoc



--NODES
go
declare @x xml
select  @x = asdatabase from MartMetadata
select x.c.value('Name[1]','nvarchar(50)') as CubeName
from @x.nodes('Database/Cubes/Cube') as x(c)

--QUERY
go
declare @x xml
select  @x = asdatabase from MartMetadata
SELECT @x.query('
for $Name in Database/Cubes/Cube/Name
return $Name
')