Is there a method of executing ASSP.Discover across all the CATALOGS?

Topics: User Forum
May 9, 2014 at 5:47 PM
A bit like DiscoverXMLMetaDataFull

We have a set of SSRS reports that use MDSCHEME_MEASUREGROUP_DIMENSIONS. This works fine when we only have one catalog but we have to spin up multiple instances fo the reports and multiple linked servers

We have looked at replacing it with DiscoverXMLMetaDataFull but for the BUS MATRIX report it's a kludge and we don't seem to have access to the DIMENSION_GRANULARITY attributes/column by this method. It doesn't seem to be present directly in the XML and running two queries and inferring the result seems a very obtuse approach.

We have tried using CALL ASSP.Discover("MDSCHEMA_MEASUREGROUP_DIMENSIONS","","<CATALOG>XXXX</CATALOG>")
UNION
....

For all the catalogs but it does mean we are hard coding catalogs into the stored procs we use.

But we can't seem to figure out how to use ASSP.Discover across multiple catalogs easily

This is a pretty minor gripe. ASSP has saved our bacon so many times its untrue. I can't thank you enough.



Steve
Coordinator
May 10, 2014 at 4:42 AM
Edited May 10, 2014 at 4:52 AM
You should be able to get pretty close with a query like the following in SSRS (you'll need to change your query to DMX to avoid SSRS trying to validate it as MDX)

call assp.discoverxmlmetadatafull(
"Cube|Name\MeasureGroups\MeasureGroup|Name\Dimensions\Dimension|CubeDimensionID\Attributes\Attribute"
,"Type='Granularity'")

We have also been experimenting with a ForEachMeasuregroup command, which is in the current source code so you could compile it yourself if you have a full copy of Visual Studio, but it's not part of a released version yet.

Otherwise you should be able to do this using sub-reports. Having a main report that lists all the databases and then a sub-report that does a bus matrix for each DB.