{"id":70,"date":"2008-07-22T22:01:12","date_gmt":"2008-07-23T03:01:12","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/?p=70"},"modified":"2008-07-24T21:00:01","modified_gmt":"2008-07-25T02:00:01","slug":"ssas-2008-dmvs-querying-from-the-sql-server-and-database-diagrams","status":"publish","type":"post","link":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/70_ssas-2008-dmvs-querying-from-the-sql-server-and-database-diagrams","title":{"rendered":"SSAS 2008 DMVs – querying from the SQL Server and database diagrams"},"content":{"rendered":"

In Analysis Services 2008 Microsoft introduced many\u00a0schema rowsets that are very similar to SQL Server Data Management Views (DMVs). Although in Books Online Microsoft refers to these new tables as “schema rowsets”, it is just easier for now to call them DMVs. You can use these DMVs to query SSAS 2008 metadata and actual data. Here is the link<\/a>\u00a0 to my original post about DMVs.<\/p>\n

You can execute queries on DMVs directly in Analysis Services. For example you can connect to Analysis Services from SQL Server Management Studio, choose menu item “File”->”New”->”Analysis Services MDX Query” and write your query to select data from DMV.<\/p>\n

You can also execute queries against SSAS DMVs from SQL Server environment.\u00a0At this point I believe this is going to be preferred method to query SSAS DMVs. This is because queries on DMVs in Analysis Services has many limitations, yet these limitations do not exists for queries\u00a0from SQL Server linked server. Example of such very important limitation – in Analysis services you cannot join 2 DMVs.<\/p>\n

To query DMVs from SQL Server\u00a0you will need to create linked server that points to Analysis Services database. Here is example of the code that creates linked server named SSAS2008Test:<\/p>\n

EXEC master.dbo.sp_addlinkedserver
\n\u00a0\u00a0 @server = N’SSAS2008Test’
\n\u00a0, @srvproduct=N’MSOLAP’
\n\u00a0, @provider=N’MSOLAP’
\n\u00a0, @datasrc=N’VirtualPC1′ — Server Name
\n\u00a0, @catalog=N’Adventure Works DW’ — Database Name
\n\u00a0go
\n\u00a0
\n\u00a0EXEC master.dbo.sp_addlinkedsrvlogin
\n\u00a0\u00a0 @rmtsrvname=N’SSAS2008Test’
\n\u00a0, @useself=N’False’
\n\u00a0, @locallogin=NULL
\n\u00a0, @rmtuser=NULL
\n\u00a0, @rmtpassword=NULL
\nGO<\/p><\/blockquote>\n

After linked server is created, you can query DMVs using OpenQuery function. For example, this is the query executed from SQL Server to get Analysis Services connection information:<\/p>\n

SELECT *
\n\u00a0 FROM OPENQUERY(SSAS2008Test, ‘SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS’)<\/p><\/blockquote>\n

After setting up SSAS linked server I decided to create database diagram from SSAS DMVs. This looked like an easy task.\u00a0For each DMV I\u00a0create a statement that selects data from DMV and selects data into SQL Server table. I actually created statement that creates these statements:<\/p>\n

SELECT ‘SELECT * INTO ‘ + SUBSTRING(TABLE_NAME,1,100)
\n+ ‘ FROM OPENQUERY(SSAS2008Test, ”SELECT * FROM $SYSTEM.’ + SUBSTRING(TABLE_NAME,1,100) + ”’)’
\n\u00a0FROM OPENQUERY(SSAS2008Test, ‘SELECT * FROM $system.dbschema_tables’) as s
\nWHERE SUBSTRING(TABLE_SCHEMA,1,100) = N’$SYSTEM’\u00a0<\/p><\/blockquote>\n

Result of the query above is:<\/p>\n

SELECT * INTO DBSCHEMA_CATALOGS
\n\u00a0 FROM OPENQUERY(SSAS2008Test, ‘SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS’)
\nSELECT * INTO DBSCHEMA_TABLES
\n\u00a0 FROM OPENQUERY(SSAS2008Test, ‘SELECT * FROM $SYSTEM.DBSCHEMA_TABLES’)
\nSELECT * INTO DBSCHEMA_COLUMNS
\n\u00a0 FROM OPENQUERY(SSAS2008Test, ‘SELECT * FROM $SYSTEM.DBSCHEMA_COLUMNS’)
\nSELECT * INTO DBSCHEMA_PROVIDER_TYPES
\n\u00a0 FROM OPENQUERY(SSAS2008Test, ‘SELECT * FROM $SYSTEM.DBSCHEMA_PROVIDER_TYPES’)
\nSELECT * INTO MDSCHEMA_CUBES
\n\u00a0 FROM OPENQUERY(SSAS2008Test, ‘SELECT * FROM $SYSTEM.MDSCHEMA_CUBES’)
\n….<\/p><\/blockquote>\n

Then I executed each query. It was a bit more complicated then I thought it is going to be:<\/p>\n