+ Reply to Thread
Results 1 to 6 of 6

Scripting cube role via XMLA or AMO?

  1. Scripting cube role via XMLA or AMO?

    Greetings,

    I am looking for some direction here, and potentially a little sample
    code. I am needing the ability to create roles with dimensional
    security inside of a cube. I am needing the most efficient way to do
    this (believe XMLA). But my issue is if I try to script out an
    existing cube role in SSAS 2005, it only gives me the "shell" of the
    information - I don't see a way to have it include the excluded /
    filtered sets inside of the XMLA Definition.

    Can anyone lend a hand in hand in where to find how to get all of the
    specifics. I see if I browse the cube definition file, there is a
    section for Is there a way to wrap that into an XLMA
    script?

    Looking for some direction here!

    Thanks!


  2. Re: Scripting cube role via XMLA or AMO?

    This is one example of setting up the cube permission xmla script:

    xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

    Adventure Works DW
    Adventure Works DW


    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    CubePermission
    CubePermission
    Role
    Allowed
    None


    Read
    Not ([Employee].[Employees].CurrentMember
    IS [Employee].[Employees].&[112])







    dharper@quilogy.com wrote:
    > Greetings,
    >
    > I am looking for some direction here, and potentially a little sample
    > code. I am needing the ability to create roles with dimensional
    > security inside of a cube. I am needing the most efficient way to do
    > this (believe XMLA). But my issue is if I try to script out an
    > existing cube role in SSAS 2005, it only gives me the "shell" of the
    > information - I don't see a way to have it include the excluded /
    > filtered sets inside of the XMLA Definition.
    >
    > Can anyone lend a hand in hand in where to find how to get all of the
    > specifics. I see if I browse the cube definition file, there is a
    > section for Is there a way to wrap that into an XLMA
    > script?
    >
    > Looking for some direction here!
    >
    > Thanks!



  3. Re: Scripting cube role via XMLA or AMO?

    One way to capture the XMLA would be to set up your project in BIDS to
    only deploy changes. Then start up SQL Profiler and set it to profile
    Analysis Services. Once you have done that, change or add a role and
    deploy the project. You will then be able to see the XMLA commands that
    BIDS sends to the server.

    Another approach, if you would prefer to work with .Net code as opposed
    to working with raw XMLA would be to use the AMO library to create your
    roles.

    The following is a snippet from the AMOAdventureWorks that is part of
    the samples that come with SQL Server.

    //====================================

    Cube cube = db.Cubes[0];
    Role role;
    DatabasePermission dbperm;
    CubePermission cubeperm;

    // Create the Readers role.
    role = db.Roles.Add("Readers");
    role.Members.Add(new RoleMember("")); // e.g. redmond\johndoe
    role.Update();

    // Assign Read permissions to this role.
    // Members of this role can discover and query the Adventure Works cube.
    // However the Customer dimension is restricted to the United States.
    dbperm = db.DatabasePermissions.Add(role.ID);
    dbperm.Read = ReadAccess.Allowed;
    dbperm.Update();

    cubeperm = cube.CubePermissions.Add(role.ID);
    cubeperm.Read = ReadAccess.Allowed;
    Dimension dim = db.Dimensions.GetByName("Customer");
    DimensionAttribute attr = dim.Attributes.GetByName("Country-Region");
    CubeDimensionPermission cubedimperm = cubeperm.DimensionPermissions.Add
    (dim.ID);
    cubedimperm.Read = ReadAccess.Allowed;
    AttributePermission attrperm = cubedimperm.AttributePermissions.Add
    (attr.ID);
    attrperm.AllowedSet = "{[Customer].[Country-Region].[Country-Region].&
    [United States]}";
    cubeperm.Update();

    //====================================


    --
    Regards
    Darren Gosbell - SQL Server MVP
    Blog: http://www.geekswithblogs.net/darrengosbell

    In article <1158785916.725216.20710@k70g2000cwa.googlegroups.com>,
    iloveseahawks@gmail.com says...
    > This is one example of setting up the cube permission xmla script:
    >
    > > xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    >
    > Adventure Works DW
    > Adventure Works DW
    >

    >
    > > xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    > CubePermission
    > CubePermission
    > Role
    > Allowed
    > None
    >
    >
    > Read
    > Not ([Employee].[Employees].CurrentMember
    > IS [Employee].[Employees].&[112])

    >

    >

    >
    >

    >

    >
    > dharper@quilogy.com wrote:
    > > Greetings,
    > >
    > > I am looking for some direction here, and potentially a little sample
    > > code. I am needing the ability to create roles with dimensional
    > > security inside of a cube. I am needing the most efficient way to do
    > > this (believe XMLA). But my issue is if I try to script out an
    > > existing cube role in SSAS 2005, it only gives me the "shell" of the
    > > information - I don't see a way to have it include the excluded /
    > > filtered sets inside of the XMLA Definition.
    > >
    > > Can anyone lend a hand in hand in where to find how to get all of the
    > > specifics. I see if I browse the cube definition file, there is a
    > > section for Is there a way to wrap that into an XLMA
    > > script?
    > >
    > > Looking for some direction here!
    > >
    > > Thanks!

    >
    >



  4. Re: Scripting cube role via XMLA or AMO?

    Thanks for the great start....would anyone happen to have the VB.NET
    code vs. the c# example for AMO...I am getting close, but not clear on
    how to bind the role to the AttributePermisions

    Thanks in advance!

    Dave

    Darren Gosbell wrote:
    > One way to capture the XMLA would be to set up your project in BIDS to
    > only deploy changes. Then start up SQL Profiler and set it to profile
    > Analysis Services. Once you have done that, change or add a role and
    > deploy the project. You will then be able to see the XMLA commands that
    > BIDS sends to the server.
    >
    > Another approach, if you would prefer to work with .Net code as opposed
    > to working with raw XMLA would be to use the AMO library to create your
    > roles.
    >
    > The following is a snippet from the AMOAdventureWorks that is part of
    > the samples that come with SQL Server.
    >
    > //====================================
    >
    > Cube cube = db.Cubes[0];
    > Role role;
    > DatabasePermission dbperm;
    > CubePermission cubeperm;
    >
    > // Create the Readers role.
    > role = db.Roles.Add("Readers");
    > role.Members.Add(new RoleMember("")); // e.g. redmond\johndoe
    > role.Update();
    >
    > // Assign Read permissions to this role.
    > // Members of this role can discover and query the Adventure Works cube.
    > // However the Customer dimension is restricted to the United States.
    > dbperm = db.DatabasePermissions.Add(role.ID);
    > dbperm.Read = ReadAccess.Allowed;
    > dbperm.Update();
    >
    > cubeperm = cube.CubePermissions.Add(role.ID);
    > cubeperm.Read = ReadAccess.Allowed;
    > Dimension dim = db.Dimensions.GetByName("Customer");
    > DimensionAttribute attr = dim.Attributes.GetByName("Country-Region");
    > CubeDimensionPermission cubedimperm = cubeperm.DimensionPermissions.Add
    > (dim.ID);
    > cubedimperm.Read = ReadAccess.Allowed;
    > AttributePermission attrperm = cubedimperm.AttributePermissions.Add
    > (attr.ID);
    > attrperm.AllowedSet = "{[Customer].[Country-Region].[Country-Region].&
    > [United States]}";
    > cubeperm.Update();
    >
    > //====================================
    >
    >
    > --
    > Regards
    > Darren Gosbell - SQL Server MVP
    > Blog: http://www.geekswithblogs.net/darrengosbell
    >
    > In article <1158785916.725216.20710@k70g2000cwa.googlegroups.com>,
    > iloveseahawks@gmail.com says...
    > > This is one example of setting up the cube permission xmla script:
    > >
    > > > > xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    > >
    > > Adventure Works DW
    > > Adventure Works DW
    > >

    > >
    > > > > xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    > > xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    > > CubePermission
    > > CubePermission
    > > Role
    > > Allowed
    > > None
    > >
    > >
    > > Read
    > > Not ([Employee].[Employees].CurrentMember
    > > IS [Employee].[Employees].&[112])

    > >

    > >

    > >
    > >

    > >

    > >
    > > dharper@quilogy.com wrote:
    > > > Greetings,
    > > >
    > > > I am looking for some direction here, and potentially a little sample
    > > > code. I am needing the ability to create roles with dimensional
    > > > security inside of a cube. I am needing the most efficient way to do
    > > > this (believe XMLA). But my issue is if I try to script out an
    > > > existing cube role in SSAS 2005, it only gives me the "shell" of the
    > > > information - I don't see a way to have it include the excluded /
    > > > filtered sets inside of the XMLA Definition.
    > > >
    > > > Can anyone lend a hand in hand in where to find how to get all of the
    > > > specifics. I see if I browse the cube definition file, there is a
    > > > section for Is there a way to wrap that into an XLMA
    > > > script?
    > > >
    > > > Looking for some direction here!
    > > >
    > > > Thanks!

    > >
    > >



  5. Re: Scripting cube role via XMLA or AMO?

    That's not hard. I'm multi-lingual :) You just need to be careful of the
    wrapping.

    '//======================================

    Dim cube As Cube = db.Cubes(0)
    Dim role As Role
    Dim dbperm As DatabasePermission
    Dim cubeperm As CubePermission

    ' Create the Readers role.
    role = db.Roles.Add("Readers")
    role.Members.Add(New RoleMember("")) ' e.g. redmond\johndoe
    role.Update()
    ' Assign Read permissions to this role.
    ' Members of this role can discover and query the Adventure Works cube.
    ' However the Customer dimension is restricted to the United States.
    dbperm = db.DatabasePermissions.Add(role.ID)
    dbperm.Read = ReadAccess.Allowed
    dbperm.Update()
    cubeperm = cube.CubePermissions.Add(role.ID)
    cubeperm.Read = ReadAccess.Allowed
    Dim objDim As Dimension = db.Dimensions.GetByName("Customer")

    Dim attr As DimensionAttribute = objDim.Attributes.GetByName("Country-
    Region")

    Dim cubedimperm As CubeDimensionPermission =
    cubeperm.DimensionPermissions.Add(objDim.ID)

    cubedimperm.Read = ReadAccess.Allowed

    Dim attrperm As AttributePermission =
    cubedimperm.AttributePermissions.Add(attr.ID)

    attrperm.AllowedSet = "{[Customer].[Country-Region].[Country-Region].&
    [United States]}"

    cubeperm.Update()

    '\\ =========================

    PS. sites like the following:

    http://authors.aspalliance.com/aldot...translate.aspx
    http://www.carlosag.net/Tools/CodeTr...r/Default.aspx

    ....are great for converting small code snippets from C# to VB.NET

    --
    Regards
    Darren Gosbell - SQL Server MVP
    Blog: http://www.geekswithblogs.net/darrengosbell

    In article <1158875383.801304.151460@d34g2000cwd.googlegroups.com>,
    dharper@quilogy.com says...
    > Thanks for the great start....would anyone happen to have the VB.NET
    > code vs. the c# example for AMO...I am getting close, but not clear on
    > how to bind the role to the AttributePermisions
    >
    > Thanks in advance!
    >
    > Dave
    >
    > Darren Gosbell wrote:
    > > One way to capture the XMLA would be to set up your project in BIDS to
    > > only deploy changes. Then start up SQL Profiler and set it to profile
    > > Analysis Services. Once you have done that, change or add a role and
    > > deploy the project. You will then be able to see the XMLA commands that
    > > BIDS sends to the server.
    > >
    > > Another approach, if you would prefer to work with .Net code as opposed
    > > to working with raw XMLA would be to use the AMO library to create your
    > > roles.
    > >
    > > The following is a snippet from the AMOAdventureWorks that is part of
    > > the samples that come with SQL Server.
    > >
    > > //====================================
    > >
    > > Cube cube = db.Cubes[0];
    > > Role role;
    > > DatabasePermission dbperm;
    > > CubePermission cubeperm;
    > >
    > > // Create the Readers role.
    > > role = db.Roles.Add("Readers");
    > > role.Members.Add(new RoleMember("")); // e.g. redmond\johndoe
    > > role.Update();
    > >
    > > // Assign Read permissions to this role.
    > > // Members of this role can discover and query the Adventure Works cube.
    > > // However the Customer dimension is restricted to the United States.
    > > dbperm = db.DatabasePermissions.Add(role.ID);
    > > dbperm.Read = ReadAccess.Allowed;
    > > dbperm.Update();
    > >
    > > cubeperm = cube.CubePermissions.Add(role.ID);
    > > cubeperm.Read = ReadAccess.Allowed;
    > > Dimension dim = db.Dimensions.GetByName("Customer");
    > > DimensionAttribute attr = dim.Attributes.GetByName("Country-Region");
    > > CubeDimensionPermission cubedimperm = cubeperm.DimensionPermissions.Add
    > > (dim.ID);
    > > cubedimperm.Read = ReadAccess.Allowed;
    > > AttributePermission attrperm = cubedimperm.AttributePermissions.Add
    > > (attr.ID);



  6. AMO

    I have tried executing following sample code, same based on sample provided in discusion.



    role = db.Roles.Add("Readers");
    role.Members.Add(new RoleMember(@"cibna\x453537")); // e.g. redmond\johndoe
    role.Update();

    // Assign Read permissions to this role.
    // Members of this role can discover and query the Adventure Works cube.
    // However the Customer dimension is restricted to the United States.
    dbperm = db.DatabasePermissions.Add(role.ID);
    dbperm.Read = ReadAccess.Allowed;
    dbperm.Update();

    cubeperm = cube.CubePermissions.Add(role.ID);
    cubeperm.Read = ReadAccess.Allowed;
    Dimension dim = db.Dimensions.GetByName("MRO Org Parent-Child");
    DimensionAttribute attr = dim.Attributes.GetByName("MRO Hierarchy");
    CubeDimensionPermission cubedimperm = cubeperm.DimensionPermissions.Add
    (dim.ID);
    cubedimperm.Read = ReadAccess.Allowed;
    AttributePermission attrperm = cubedimperm.AttributePermissions.Add(attr.ID);
    attrperm.AllowedSet = " {[MRO Org Parent-Child].[MRO Hierarchy].&[D3659]}";
    cubeperm.Update();


    But I have not seen dimention attribute reciving specified AllowedSet. Could you please let me know if the code snippet has worked for you. If so, could you please temm, What I have missed in my code.

    Thanks a lot.

    Vijay Annem

    EggHeadCafe.com - .NET Developer Portal of Choice
    http://www.eggheadcafe.com

+ Reply to Thread