Post reply

How to find schema information using XMLA in SSAS?

  • January 2, 2009 at 4:35 am

    #197644

    Hi,

    Is there any way to find the schema information quickly in SQL Server Analysis Services. For example in SQL Server Database Engine the tables/views and functions/procedures schema information can be fetched by executing following statements respectively.

    select * from information_schema.tables

    select * from information_schema.routines

    Similarly I would like to find the fact, dimension, calculated members, KPI, perspectives, actions and other informaiton in tabular view by running XMLA... Is there any way to find this?

    Thanks,

    Regards - JL

  • January 2, 2009 at 5:55 am

    #920214

    You will want to take a look at the Analysis Stored Procedure project on CodePlex for SSAS 2005 with the XmlaDiscover

    http://www.codeplex.com/ASStoredProcedures

    or in SSAS 2008 you can access the DMVs now to get at the schema information through the rowsets that are available to query.

    If you are just looking for a documentation tool you could look at SqlAsDoc - http://www.u2u.be/res/SQL05_ASDoc.aspx which is one of many documentation tools that you can use to get the information.

    ----------------------------------------------------------------------------------------
    Dan English - http://denglishbi.wordpress.com

  • January 5, 2009 at 1:26 am

    #920988

    This is exactly what I was looking for 😀 !

    The following statement provides all the schemas

    CALL ASSP.Discover("DISCOVER_SCHEMA_ROWSETS");

    And if any of the schemas is executed then I get schema details

    CALL ASSP.Discover("MDSCHEMA_HIERARCHIES");

    Thanks a lot!

    Regards - JL

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply