locked
How to Document used cube dimensions,members andmeasures ? RRS feed

  • Question


  • Has anyone found a easy way to document ProClarity usage of available objects in a cube?
    I want to document which book/view uses which measures, dimensions.

    I am thinking of XQUERY statements on column XMLdata in Table bookelements.

    Like: myXML.nodes('/BriefingPage3/Commands/Command/DSH/Axes/Background/Dimensions')

    I am no XML guru and saw that '/BriefingPage3/Commands/Command/DSH/Axes/Background' contains ALL dimensions even NOT filtered ones.

    Is there an easier way or has someone already generated some SQL / XQUERIES i can use ?

    Ultimate solution after this is offcourse to combine it with AMO.
    To document everything from back to front like BI documenter, but also including ProClarity.




    Thursday, February 21, 2008 4:06 PM

Answers

  • I think you've hit upon the challenge of a task like the one you describe when it comes to OLAP queries, and that is the need for an OLAP query to reference every dimension in a cube.  In a relational database a query need only reference the tables of interest to gain the data it needs.  In an OLAP cube however, every piece of data is arrived at by constructing a tuple that consists of a value from every dimension of the cube.  If you did not include a value from a single dimension (even the All or default value), the engine would have no way of knowing how that dimension's value influences the tuple value being requested, and so a value for each dimension must be present for each query.

     

    So, following the methodology you outline above, you would need to take the extra step of examining each dimension value and deciding if you want to include it based on its member selection (for instance, remove the dimensions that have "All" as the selection).

     

    The only other way I can think to do this is through the ProClarity object model.  You could perhaps open books with the object model, read the information out of them, and then store that information somewhere.  I think all this would really provide though is objects to read the XML for you.  It may be faster and more intuitive to just read the XML straight from the database yourself.

     

    Friday, February 22, 2008 11:04 PM

All replies

  • Michel,

     

    This is a new request that I've yet to see - but, I'll post any options if I run across them.

     

    -Joey

     

    Friday, February 22, 2008 4:44 PM
  • I think you've hit upon the challenge of a task like the one you describe when it comes to OLAP queries, and that is the need for an OLAP query to reference every dimension in a cube.  In a relational database a query need only reference the tables of interest to gain the data it needs.  In an OLAP cube however, every piece of data is arrived at by constructing a tuple that consists of a value from every dimension of the cube.  If you did not include a value from a single dimension (even the All or default value), the engine would have no way of knowing how that dimension's value influences the tuple value being requested, and so a value for each dimension must be present for each query.

     

    So, following the methodology you outline above, you would need to take the extra step of examining each dimension value and deciding if you want to include it based on its member selection (for instance, remove the dimensions that have "All" as the selection).

     

    The only other way I can think to do this is through the ProClarity object model.  You could perhaps open books with the object model, read the information out of them, and then store that information somewhere.  I think all this would really provide though is objects to read the XML for you.  It may be faster and more intuitive to just read the XML straight from the database yourself.

     

    Friday, February 22, 2008 11:04 PM