This project has moved and is read-only. For the latest updates, please go here.

Cube documentation based on ASSP

Topics: User Forum
Nov 14, 2008 at 8:47 PM
Hello,
I find this procedure very helpful. It will take me a while to exploit its full potential.
Right now, I try to build a report that is used to document a cube, extract descriptions for measures, hierarchies and dimensions for any cube.
I have a couple questions:
- For a command like this one:
CALL ASSP.DiscoverXmlMetadataFull("\Databases\Database","<ObjectExpansion>ExpandObject</ObjectExpansion>")
Is it possible to sort the result?

- Next I use commands like that to pull the descriptions and more
="CALL ASSP.Discover(""MDSCHEMA_DIMENSIONS"",""<CUBE_NAME>" &  Parameters!DatabaseId.Value & "</CUBE_NAME>"",""<CATALOG>" &  Parameters!DatabaseId.Value & "</CATALOG>"")"

="CALL ASSP.Discover(""MDSCHEMA_HIERARCHIES"",""<CUBE_NAME>" &  Parameters!DatabaseId.Value & "</CUBE_NAME>"",""<CATALOG>" &  Parameters!DatabaseId.Value & "</CATALOG>"")"
="CALL ASSP.Discover(""MDSCHEMA_HIERARCHIES"",""<CUBE_NAME>" &  Parameters!DatabaseId.Value & "</CUBE_NAME>"",""<CATALOG>" &  Parameters!DatabaseId.Value & "</CATALOG>"")"

Here What would be optimal would be to be able to "Hierachize" the result so we would have each dimension immediately followed by its hierarchies.

Next, I am in trouble with the Dimension Health check. It does seem to work when I map it to another cube however, I have got an error when I try to access the data tab for the dimensions. When looking at the code I can see that there is a default value for "Geography" which I cannot seem to be able to remove.

If you are interested in the Report I am developing you can download both the rdl and an example of pdf print out for there:

http://cid-9d609a31c1f627c2.skydrive.live.com/browse.aspx/SQL%20ServerPhilippe







Nov 16, 2008 at 12:48 PM
> - For a command like this one:
> CALL ASSP.DiscoverXmlMetadataFull("\Databases\Database","<ObjectExpansion>ExpandObject</ObjectExpansion>")
> Is it possible to sort the result?

It might be possible to add a parameter for sorting. I will add a work item to the issue list and have a look at it.
Another option would be to use the DMV function
eg.

call ASSP.DMV("SELECT * from $System.DBSCHEMA_CATALOGS ORDER BY CATALOG_NAME")

> - Next I use commands like that to pull the descriptions and more
> ="CALL ASSP.Discover(""MDSCHEMA_DIMENSIONS"",""<CUBE_NAME>" &  Parameters!DatabaseId.Value & "</CUBE_NAME>"",""<CATALOG>" &  Parameters!DatabaseId.Value & "</CATALOG>"")"
>
> ="CALL ASSP.Discover(""MDSCHEMA_HIERARCHIES"",""<CUBE_NAME>" &  Parameters!DatabaseId.Value & "</CUBE_NAME>"",""<CATALOG>" &  Parameters!DatabaseId.Value & "</CATALOG>"")"
> ="CALL ASSP.Discover(""MDSCHEMA_HIERARCHIES"",""<CUBE_NAME>" &  Parameters!DatabaseId.Value & "</CUBE_NAME>"",""<CATALOG>" &  Parameters!DatabaseId.Value & "</CATALOG>"")"
>
> Here What would be optimal would be to be able to "Hierachize" the result so we would have each dimension immediately followed by its hierarchies.

You could achieve this with a sub report. This is what I did for the Role report sample that is currently in ASSP

The DiscoverXmlMetadata() function might  be another option as I have a sort of XPath style syntax with an option to extract information from different nodes in the hierarchy, but this function is "heavier" and harder to get the hang of than Discover() or DMV().