XMLA discover database roles in SSAS multidimensional

  • Hi Colleagues,

    I am breaking my head against XMLA that will list all roles existing in a database. I know I need to use Discover. There is <RequestType>DISCOVER_XML_METADATA</RequestType>

    <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">  
    <RequestType>DISCOVER_XML_METADATA</RequestType>
    <Restrictions>
    <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
    <DatabaseID>SSAS_DB_ID</DatabaseID>
    </RestrictionList>
    </Restrictions>
    <Properties>
    <PropertyList>
    </PropertyList>
    </Properties>
    </Discover>

    But it runs too long and returns several megabytes of information. Yes, it also contains the roles, but the way to get to it is not optimal.

    Does anyone know how to get only the <Roles></Roles> part of this XML?

    Very unfortunately I cannot make sense out of the MS documentation for my requirement.

    Would appreciate any insights on this.

    I have also posted the same question on stackoverflow.

    • This topic was modified 2 years, 5 months ago by  iz.
  • Have you tried the ASSP project? Namely the DiscoverXMLMetaData function: https://asstoredprocedures.github.io/functions/DiscoverXmlMetadata/

    Makes all of this easier.


    I'm on LinkedIn

  • Thanks Paul. The ASSP is a top 1 answer for this problem. However, I needed to do this with bare SQL. Which I managed.

    On a side note, I really struggled a lot to understand the MS XMLA documentation. I needed to be able to get just roles for DB, without additional info, and I could not get any hint from the documentation how to do that. At the end, I managed with this one-size-fits-all DISCOVER_XML_METADATA solution - albeit it returned much more information than I actually needed.

    declare @SSASDBName nvarchar(255) = 'SSAS_DB_NAME'

    -- when executed from SQL, XMLA Discover has to be wrapped in <Batch>
    declare @XMLA nvarchar(MAX)
    select @XMLA = '<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
    <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
    <RequestType>DISCOVER_XML_METADATA</RequestType>
    <Restrictions>
    <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
    <DatabaseID>'+@SSASDBName+'</DatabaseID>
    </RestrictionList>
    </Restrictions>
    <Properties/>
    </Discover></Batch>'

    -- get the XML response from SSAS
    declare @t table (x XML)
    insert @t
    EXEC(@XMLA) AT LINKED_SSAS_SERVER -- linked SSAS server

    declare @XMLAResult xml
    select @XMLAResult = x from @t

    -- expand XML response
    ;with XMLNAMESPACES (default 'urn:schemas-microsoft-com:xml-analysis:rowset')
    select
    SSAS_DB = X_DB.x.query('./ID').value('.','nvarchar(255)'),
    RoleID = X_Roles.x.query('./ID').value('.','nvarchar(255)'),
    RoleMember = X_Members.x.query('.').value('.','nvarchar(255)')
    from @XMLAResult.nodes('//Database') X_DB(x)
    cross apply @XMLAResult.nodes('//Roles/Role') X_Roles(x)
    cross apply X_Roles.x.nodes('./Members/Member/Name') X_Members(x)

     

  • iz wrote:

    Thanks Paul. The ASSP is a top 1 answer for this problem. However, I needed to do this with bare SQL. Which I managed.

    On a side note, I really struggled a lot to understand the MS XMLA documentation. I needed to be able to get just roles for DB, without additional info, and I could not get any hint from the documentation how to do that. At the end, I managed with this one-size-fits-all DISCOVER_XML_METADATA solution - albeit it returned much more information than I actually needed.

    declare @SSASDBName nvarchar(255) = 'SSAS_DB_NAME'

    -- when executed from SQL, XMLA Discover has to be wrapped in <Batch>
    declare @XMLA nvarchar(MAX)
    select @XMLA = '<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" Transaction="true">
    <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
    <RequestType>DISCOVER_XML_METADATA</RequestType>
    <Restrictions>
    <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
    <DatabaseID>'+@SSASDBName+'</DatabaseID>
    </RestrictionList>
    </Restrictions>
    <Properties/>
    </Discover></Batch>'

    -- get the XML response from SSAS
    declare @t table (x XML)
    insert @t
    EXEC(@XMLA) AT LINKED_SSAS_SERVER -- linked SSAS server

    declare @XMLAResult xml
    select @XMLAResult = x from @t

    -- expand XML response
    ;with XMLNAMESPACES (default 'urn:schemas-microsoft-com:xml-analysis:rowset')
    select
    SSAS_DB = X_DB.x.query('./ID').value('.','nvarchar(255)'),
    RoleID = X_Roles.x.query('./ID').value('.','nvarchar(255)'),
    RoleMember = X_Members.x.query('.').value('.','nvarchar(255)')
    from @XMLAResult.nodes('//Database') X_DB(x)
    cross apply @XMLAResult.nodes('//Roles/Role') X_Roles(x)
    cross apply X_Roles.x.nodes('./Members/Member/Name') X_Members(x)

    That's quite the mouthful for such a small amount of data! I agree the documentation of XMLA Discover is a bit lacking, that's why someone created ASSP I suppose. Thanks for posting back the resolution 🙂


    I'm on LinkedIn

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

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