Wednesday, July 11, 2012

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'
  1. Change the cube name and execute the query
Posted by at
Labels: , ,

2 comments:

  1. UnknownFebruary 2, 2017 at 3:43 AM

    Hi Yogesh,

    Just want to ask if there is a way to extract the table source (physical tables/views/name query) used by the cube measures/dimensions from data source view? If yes may i ask your help to formulate as I’ve been searching on this but found no answer.

    Thanks!
    Bernard

    ReplyDelete
  2. Nikita AgrawalSeptember 24, 2019 at 11:27 PM

    Hi Yogesh,

    Did you found a way to extract table source used by cube from data source view.

    ReplyDelete

Subscribe to: Post Comments (Atom)