How to find all tables and views used in a SQL Server Analysis services cube?

Someone asked me today if there is an easy way to find all tables, views used in a SSAS cube. You can always see XMLA file to find the tables or check the data source view in the cube definition. But we have some cube databases which have more than 100 tables and going through XMLA or DSV is time consuming.

Good thing is Microsoft provides DMVs explore metadata of a cube database. Here is a sample query to get meta data for a cube

1. Connect to the SSAS instance via SSMS.
2. Open new MDX query
3. Copy paste the query below
   SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
   
   WHERE CUBE_NAME = 'cube_name'AND DIMENSION_NAME <> 'Measures'

4. Change the cube name and execute the query

SQL Server Integration Services name for clustered SQL Server installation or named instance of SQL

After you install SQL Server on an Active/passive cluster you need to provide cluster name for SQL Server Integration Services instance. Same is case for named instance of SQL server where you have to provide

Lets assume for a cluster with 2 nodes Server1N1 and Server1N2; the cluster name is Server1. Here is how your MsDtsSrvr.ini.xml should look like.

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB</Name>

      <ServerName>Server1</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

For a named instance is should be

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB</Name>

      <ServerName>Server1\InstanceName</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

MsDtsSrvr.ini.xml can be found under SQL server installation directory

C:\Program Files\Microsoft SQL Server\100\DTS\Binn

Once INI file is changed you need to restart SSIS service to it to take effect