Discovering partition names SSAS

Question

Is there a way I can dynamically discover if a partition with a specific name already exists in my cube using DMV or XMLA?

Thanks

Answer 1

If you want a DMV that you can query via MDX you can use the $System.DISCOVER_OBJECT_ACTIVITY schema and then filter it down based on the OBJECT_PARENT_PATH which will be something like:

SELECT OBJECT_ID
FROM $System.DISCOVER_OBJECT_ACTIVITY
[Server].Databases.[DatabaseID].Cubes.[CubeID].Measure Groups.[MeasureID].Partitions

Answer 2

There is no DMV that lists this information. You would have to issue a DISCOVER_XML_METADATA request and parse through the returned XMLA which will be in a similar format to what you get when you script an object definition.

If you don't mind using an external assembly there is a function in the assembly at http://asstoredprocedures.codeplex.com called DiscoverXmlMetadata which can do this.

The following query will return a list of all the partitions in the database

call assp.DiscoverXmlMetadata("Partition")