CODEWITHSUNDEEP
ssas-tabular
user5226582
user5226582

Reputation: 1986

List Analysis Services Tabular instance tables in PowerShell

I need to list Tabular SSAS (Compatibility version 1500) tables from PowerShell.

Invoke-ASCmd command in Sql Server PowerShell package looks promising, however I'm a bit lost in documentation.

I can see that the following query from examples lists datasources of a tabular instance:

Invoke-ASCmd -Database:"Adventure Works DW 2008R2" -Query:
"<Discover xmlns='urn:schemas-microsoft-com:xml-analysis'>
  <RequestType>DISCOVER_DATASOURCES</RequestType>
  <Restrictions></Restrictions><Properties></Properties>
</Discover>"

It looks like RequestType parameter is what I'm after; I didn't find any documentation on it so tried guessing DISCOVER_TABLES, LIST_TABLES and TABLES which were rejected.

TMSL (which is what 1500 supports according to this link) has commands for altering and deleting tables, however I cannot find anything on querying or listing.

Dynamic Management Views sound like a possible solution however I cannot figure out the syntax.

From "Script Administrative Tasks in Analysis Services":

You can create a standalone MDX script file that queries data or system information. For example, Dynamic Management Views (DMV) that expose information about local server operations and server health are accessed via the MDX Select statement.

Found this discussion and tried

Invoke-ASCmd -Server "localhost" -Database:"database" -Query:"SELECT * FROM DBSCHEMA_TABLES"

however am getting an error

-1055522771 "Either the user X does not have permission to access the referenced mining model, DBSCHEMA_TABLES, or the object does not exist."

Upvotes: 0

Views: 1661

Answers (1)

mcjackson
mcjackson

Reputation: 23

I use this to show all tables in tabular model database:

<Discover xmlns='urn:schemas-microsoft-com:xml-analysis'>
<RequestType>TMSCHEMA_TABLES</RequestType>
<Restrictions>
    <RestrictionList>
        <SystemFlags>0</SystemFlags>
    </RestrictionList>
</Restrictions>
<Properties>
    <PropertyList>
        <CATALOG>YOUR_TABULAR_MODEL_DATABASE_NAME</CATALOG>
    </PropertyList>
</Properties>
</Discover>

Hope this helps. For full reference see here or here.

Upvotes: 1

Related Questions