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!

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!
>
>

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!
> >
> >

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);

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