SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: How and where do I run queries on SSAS 2008 DMVs?

A: There are few places you can run queries on Analysis Services 2008 schema rowsets or DMVs:

1. You can query DMVs from SQL Server Management Studio (SSMS).
Start SSMS and connect to the instance of SSAS 2008. Select menu item "File"->"New"->"Analysis Services MDX Query". In the new query window enter query on DMV, for example:

SELECT table_schema, table_name
  FROM $system.dbschema_tables
 ORDER BY table_name

Click "Execute" button.
This is the simpliest and fastest way to run queries on SSAS DMVs.
Please note that there are quite a few restrictions on the SELECT statement. For example:

• JOINs do not work
• string fuctions like LEFT does not work
• COUNT, SUM functions do not work
• etc.

2. You can query DMVs from the SQL Server using linked server.
Start SSMS and connect to the instance of SQL Server 2008. Execute following command to create linked server to the instance of SSAS 2008:

EXEC master.dbo.sp_addlinkedserver
   @server = N'SSAS2008Test'
 , @srvproduct=N'MSOLAP'
 , @provider=N'MSOLAP'
 , @datasrc=N'VirtualPC1'
 , @catalog=N'Adventure Works DW'
 go
 
EXEC master.dbo.sp_addlinkedsrvlogin
   @rmtsrvname=N'SSAS2008Test'
 , @useself=N'False'
 , @locallogin=NULL
 , @rmtuser=NULL
 , @rmtpassword=NULL
go

Now you can execute queries on SSAS DMVs using OpenQuery function. For example:

SELECT *
 FROM OPENQUERY(SSAS2008Test, 'SELECT table_schema, table_name
    FROM $system.dbschema_tables
   ORDER BY table_name')

This approach is a bit more complicated, but it gives you much more flexibility on what you can do with queries - for example now you can join results from different DMVs.

3. Run queries from PowerShell or VBScript.

You can execute query on SSAS DMV from PowerShell or VBScript. Here is example of PowerShell script that runs the same query as above statements or accepts parameter where you can specify any query:

param(
    [string] $ServerName = 'vidas-lenovo',
    [string] $DBName   = 'Adventure Works DW 2008',
    [string] $sqlCommand = 'SELECT table_schema, table_name FROM $system.dbschema_tables'
  )

## Prepare the connection string based on information provided
$connectionString = "Provider=msolap;Data Source=$server;Initial Catalog=$DBName;"

## Connect to the data source and open
$connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
$command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand, $connection
$connection.Open()

## Fetch the results, and close the connection
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object System.Data.DataSet
[void] $adapter.Fill($dataSet)
$connection.Close()

## Return all of the rows from their query
$dataSet.Tables[0]

More samples on PowerShell scripts on DMVs can be found here.