Retrieving Partition Properties Script from SQL Server DB

Question

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?

user9517769 · Accepted Answer · 2023-07-10 17:54:43Z

0

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.

answered Jul 10, 2023 at 17:54

user9517769

Sign up to request clarification or add additional context in comments.

1 Comment

James Over a year ago

Thank you, unfortunately that did not work for me. I used this in the end: SELECT * FROM $system.TMSCHEMA_PARTITIONS; And one of the fields had all of the queries.

Collectives™ on Stack Overflow

Retrieving Partition Properties Script from SQL Server DB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related