Retrieving Partition Properties Script from SQL Server DB

wztqucjr 于 2023-08-02 发布在 SQL Server

关注(0)|答案(1)|浏览(117)

I am attempting to catalogue a set of olap-cubes from a SQL Server DB. In the analysis services (SSMS), I can right click a measure_group/table, click partitions, properties, and then use "Script Action to New Query Window". This yields me a script with the various sources used to populate this measure_group.

In the interest of automation, I was wondering if one could retrieve this script through a query. The closest I've gotten is this:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
    <RequestType>DISCOVER_PARTITION_STAT</RequestType>
    <Restrictions>
        <RestrictionList>
            <DATABASE_NAME>database_name</DATABASE_NAME>
            <CUBE_NAME>cube_name</CUBE_NAME>
            <MEASURE_GROUP_NAME>measure_group_name</MEASURE_GROUP_NAME>
            <PARTITION_NAME>partition_name</PARTITION_NAME>
        </RestrictionList>
    </Restrictions>
    <Properties>
        <PropertyList>
            <Catalog>database_name</Catalog>
            <Format>Tabular</Format>
        </PropertyList>
    </Properties>
</Discover>

However, the requirement to provide the Partition_Name as a restriction is problematic, because it differs from measure to measure, and would thus require manual entries, defeating the purpose of the script.

Is there a simple query that can be used to get the whole script, without needing to specify more than the database_name and measure_group?

sql-server

来源：https://stackoverflow.com/questions/76652579/retrieving-partition-properties-script-from-sql-server-db

1条答案

按热度按时间

t8e9dugd1#

SELECT 
    MGP.TMEASUREGROUP_NAME AS MeasureGroupName,
    MGP.TMEASUREGROUP_SOURCE AS MeasureGroupSource
FROM 
    $SYSTEM.MDSCHEMA_MEASUREGROUPS AS MGP
WHERE 
    MGP.CUBE_NAME = 'your cube'
    AND MGP.DATABASE_NAME = 'your db'
    AND MGP.TMEASUREGROUP_NAME = 'your measure group'

The $SYSTEM.MDSCHEMA_MEASUREGROUPS schema rowset provides metadata information about measure groups in SSAS. The TMEASUREGROUP_SOURCE column contains the script used to populate the measure group.

赞(0）回复(0）举报 2023-08-02

我来回答

Retrieving Partition Properties Script from SQL Server DB

1条答案

相关问题

热门标签

最新问答