Database Reference
In-Depth Information
DB for Analysis Services data provider, which is also used to run DAX and MDX
queries against Analysis Services.
A complete list of DMVs is available in the MSDN Library under the topic
Use Dy-
namic Management Views (DMVs) to Monitor Analysis Services
, which is loc-
There are 59 DVMs related to SQL Server Analysis Services in SQL Server 2012.
Most, but not all, of these views apply to both multidimensional and Tabular SSAS
databases since both platforms expose objects through XML/A interfaces.
To query a DMV, open an MDX query window in the SQL Server Management Studio
with a connection to your Tabular instance. Even though you are using the same
query interface to write MDX and DAX queries, the query syntax for DMVs is very
similar to T-SQL. Because of the lack of support for advanced query operators for
DMVs, it's often easiest to copy the results of a query into Excel or a SQL Server
table and then use other methods to filter and examine the results. For example, to
get the memory use of all the tables in a database, you would first need to use the
following query to return information for all the objects on the server:
select
ObjectParentPath,
ObjectID,
MemoryUsed
from $system.DISCOVER_MEMORYUSAGE
The
ObjectParentPath
column contains a complete reference to each object
in the form of
<Server\Instance>.Databases.<Database Name>.<Object
Name>
. This string can be parsed to filter and discriminate the objects of interest.
One of the most convenient ways to discover and query SSAS Dynamic Manage-
ment Views is to use the DAX Studio tool. This is an installable add-in for Excel that
you can download from the Microsoft CodePlex community code sharing site at
ht-
DAX Studio has many other useful features and is, in many ways, a superior query
tool to SSMS, with features such as code completion, syntax checking, and query
keyword color coding.
Search WWH ::
Custom Search