Dynamic Management Views for SSAS

Analysis Services (SSAS) Dynamic Management Views (DMV) are query structures that expose information about local server operations and server health. The query structure is an interface to schema row sets that return metadata and monitoring information about an Analysis Services instance.

SSAS DMVs can be used to monitor the server resources (e.g. connections, memory, CPU, users, aggregation etc. ) and find out the structure of SSAS databases (e.g. hierarchy , dimensions, hierarchies, measures, measure groups, data sources, cubes, actions and KPIs etc.) .

SQL statements can be used to query the row sets, but have following limitation in SQL 2008 version.

• SELECT DISTINCT does not return DISTINCT values
• ORDER BY clause accepts just one field to order by. Only one order expression is allowed for TOP Expression at line 1, column 1″
• COUNT, SUM does not work
• ORDER BY <number> does not ORDER, but no error
• JOINS appear not to work
• LIKE does not work
• String functions like LEFT do not work

Usefull DMVs

SELECT * FROM $system.DBSCHEMA_CATALOGS -- list of the Analysis Services databases on the current connection.

SELECT * FROM $system.DBSCHEMA_COLUMNS

SELECT * FROM $system.DBSCHEMA_PROVIDER_TYPES

SELECT * FROM $system.DBSCHEMA_TABLES

SELECT * FROM $system.DISCOVER_SCHEMA_ROWSETS

SELECT * FROM $system.DISCOVER_COMMANDS

SELECT * FROM $system.DISCOVER_CONNECTIONS

SELECT * FROM $system.DISCOVER_JOBS

SELECT * FROM $system.DISCOVER_LOCKS

SELECT * FROM $system.DISCOVER_MEMORYUSAGE ORDER BY MemoryUsed DESC

SELECT * FROM $system.DISCOVER_OBJECT_ACTIVITY

SELECT * FROM $system.DISCOVER_OBJECT_MEMORY_USAGE 

    ORDER BY OBJECT_MEMORY_NONSHRINKABLE DESC

SELECT * FROM $system.DISCOVER_SESSIONS

SELECT * FROM $system.MDSCHEMA_CUBES

SELECT * FROM $system.MDSCHEMA_DIMENSIONS

SELECT * FROM $system.MDSCHEMA_FUNCTIONS

SELECT * FROM $system.MDSCHEMA_HIERARCHIES

SELECT * FROM $system.MDSCHEMA_INPUT_DATASOURCES

SELECT * FROM $system.MDSCHEMA_KPIS

SELECT * FROM $system.MDSCHEMA_LEVELS

SELECT * FROM $system.MDSCHEMA_MEASUREGROUP_DIMENSIONS 

    -- WHERE MEASUREGROUP_NAME = 'NSA NZ Sales'

SELECT * FROM $system.MDSCHEMA_MEASUREGROUPS

SELECT * FROM $system.MDSCHEMA_MEASURES

SELECT * FROM $system.MDSCHEMA_MEMBERS

SELECT * FROM $system.MDSCHEMA_PROPERTIES

SELECT * FROM $system.MDSCHEMA_SETS

Using DMV Queries to get Cube Metadata.

Referenceshttps://msdn.microsoft.com/en-us/library/hh230820(v=sql.110).aspxhttps://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/
http://blogs.microsoft.co.il/yanivmor/2010/01/27/dmvs-for-analysis-services/
https://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

Backup and Restore Multiple Databases and Cubes

SSAS Cubes
1. List all the cube names in a MS Analysis Server instance.
Open a MDX / DAX query window and run followoing DMVquery.
    SELECT * FROM $system.dbschema_catalogs
    ORDER BY [catalog_name] ASC

2. Backup multiple databases in a MS Analysis Server instance.
Sometimes, database name may not be same as database ID. In that case, DatabaseID needs to be found by going to properties of the database.
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="false" >
   <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
       <Object>
       <DatabaseID>Cube 2</DatabaseID>
       </Object>
       <File>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube 1.abf</File>
       <AllowOverwrite>true</AllowOverwrite>
   </Backup>
   <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
       <Object>
       <DatabaseID>C:\Data\SQL Server Data\MSSQL.2\OLAP\Backup\Cube 2</DatabaseID>
       </Object>
       <File>Cube 2.abf</File>
       <AllowOverwrite>true</AllowOverwrite>
   </Backup>
</Batch>
Ref: http://blog.sqltechie.com/2010/01/how-to-backup-multiple-database-using.html

SQLServer Databases
1. Backup all the databases in a SQLServer  instance can be done using following script (you can exclude any database by defining it within the where clause of select statement).

DECLARE @name VARCHAR(50) -- Database name 
DECLARE @path VARCHAR(256) -- Backup files' path
DECLARE @fileName VARCHAR(256) -- Backup files' filename
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'D:\Microsoft SQL Server 2005\Analysis Services\MSSQL.2\OLAP\Backup\DatabaseMigration-09-06-2014\' 

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE cursor1 CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','Report Administration','ReportServer','ReportServerTempDB' )

OPEN cursor1  
FETCH NEXT FROM cursor1 INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM cursor1 INTO @name  
END  
CLOSE cursor1  
DEALLOCATE cursor1

2. Restore database using following command
Back files need to be copied to MS SQL Server\MSSQL.1\MSSQL\data Folder.
RESTORE DATABASE [AnalyticsPortal]
   FROM DISK = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AnalyticsPortal.BAK'
   WITH MOVE 'AnalyticsPortal' TO 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\data\AnalyticsPortal.mdf',
   MOVE 'AnalyticsPortal_log' TO 'd:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AnalyticsPortal_1.LDF'