Run MDX Query against different Databases on the same server

• ktflash

  Ten Centuries

  Points: 1397

  More actions

  January 19, 2023 at 2:57 pm

  Go to Answer

  #4139583

  Ahoy,

  i have few ssas server instances and many tabular models on each of the server.

  Now i want to gather structural metadata of these models.

  There are some DMVs which seem to get what i need:

  • $System.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS
  • $System.DISCOVER_STORAGE_TABLE_COLUMNS
  • $System.DISCOVER_STORAGE_TABLES

  But the problem seems to be that these DVMs can not get data across multiple models/databases.

  When executed against the server its only running against the default database.

  What i need is a way to get the information from all the databases.

  The name of the databases can easily be aquired with the following DMV:

  select *
  from $SYSTEM.DBSCHEMA_CATALOGS

  Now i need a way to run the same 3 DMVs from above against the list of databases.

  • MDX has no "USE XXXX" to switch DB
  • XMLA apprently can run against multiple databases, but i could not find a sulation that returns the MDX results from a query i passed
  • Any SSIS specific solutions?
  • creating a linked server for every Model is not an option

  I want to be the very best
  Like no one ever was

  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

• ktflash

  Ten Centuries

  Points: 1397

  More actions

  January 20, 2023 at 9:01 am

  Answer

  #4139900

  Ok found solution to my own question, using SSIS

  1 Import all relevanten Database names

  select *
  from $SYSTEM.DBSCHEMA_CATALOGS

  2 Use a loop container to edit the connection string and replace the database name for each execution

   

  I want to be the very best
  Like no one ever was

  https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help