Add role to Analysis Services Database

Does anyone know if there is a way to use a SSIS task to add a role to SSAS cube? At the moment I use Management Studio, Right Click "Roles" under the cube and go through New Role wizard, but I'd like to be able to include this in a SSIS package instead.

Thanks

Richard

You can do it through the Script task, employing the AMO (Analysis Management Objects) library. I recommend creating the role in SSMS first but instead of submitting the change to the system, clicking the Script button at the top of the New Role dialog box to view the XMLA\ASSL script it creates. The script can be a guide for your AMO development (as AMO is just a wrapper for the XMLA\ASSL languages).

Good luck,

Bryan

|||

Following along with what Bryan had mentioned you could script a role using Management Studio to use as a template then use and expression variable and an Analysis Services Execute DDL Task in SSIS to accomplish this as an ongoing solution. The Analysis Services Execute DDL Task will allow you to customize and execute the role creation/modification command and avoid having to use the script task.

Hope that helps!

|||

Thanks! Wow. Cool. Kind of got it to work but don't know quite how!

In SSMS scripting the role worked nicely and was able to run it in a query window and it did exactly what was needed.

I'm pretty useless at VB.NET so trying the Script Task in SSIS, I hit design script, added AMO as a reference and pasted the SSMS script where it says "Add your code here" inside of Public Sub Main(). Of course it doesn't work that simply. But is there a really simple answer to what I need or am I going to have to go off and learn some VB.NET ?

So then I tried Execute DDL task, and muddled my way around it. I save the original script as a .xmla file in the file system. Then in DDL task under DDL selected File Connection as SourceType and connected to this file. Executed task and it did what was needed.

I guess this is good enough as it works, but I wouldn't mind understanding better what I am doing!!

Thanks

Richard

|||

Here is a code sample from a past project where we created a role for each entry in a table. (Just sharing that last part so you understand the database query in the code.)

Code Snippet

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Data.SqlClient

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public Class ScriptMain

Public Sub Main()

Dim server As New Microsoft.AnalysisServices.Server

Try

' CONNECT TO THE SQL SERVER ANALYSIS SERVICES (SSAS) SERVER

server.Connect("localhost")

' THE SSAS DATABASE TO CONNECT TO

Dim database As New Microsoft.AnalysisServices.Database

database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' GET A LIST OF ROLES TO CREATE

' Connection String comes from the 'METADATA' Connection Manager

Dim myConnection As New SqlConnection(Dts.Connections.Item(0).ConnectionString.ToString())

Dim myCommand As SqlCommand = New SqlCommand("SELECT ID FROM dbo.Roles (NOLOCK) WHERE ID > 0", myConnection)

myConnection.Open()

' FILL THE DATAREADER

Dim dr As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

' LOOP THROUGH THE DATAREADER AND BUILD A ROLE

If (dr.HasRows) Then

While (dr.Read())

CreateRole("Role for ID ", dr("ID").ToString, database)

End While

End If

' CLOSE THE DATAREADER

dr.Close()

' CLOSE THE SQL SERVER DB CONNECTION

myConnection.Close()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception

Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)

Finally

' DISCONNECT THE SSAS SERVER

server.Disconnect()

End Try

Dts.TaskResult = Dts.Results.Success

End Sub

Private Sub CreateRole(ByVal rolePrefix As String, ByVal dsNumber As String, ByVal asDatabase As Database)

Try

' WILL CREATE A ROLE IN THE ASSIGNED DB WITH THE NAME AND KEY VALUE

Dim newRole As Role

' CREATE THE NEW ROLES NAME

newRole = asDatabase.Roles.Add(rolePrefix + " " + dsNumber)

newRole.Description = "Role for " + rolePrefix + " " + dsNumber

newRole.Update()

Catch ex As Exception

Throw New Exception(ex.Message.ToString())

End Try

End Sub

End Class

Add Parameters!

Please visit
http://msdn.microsoft.com/library/e...tegrity_topic05
& click the link 'Implementing Cascading Operations Using Stored
Procedures'. Please refer to the sub-topic titled 'Inserting a Row into
the Primary Table'. There are 2 scripts under this sub-topic. Both the
scripts create a stored procedure named usp_OrdersInsert. After the
second script, (which creates the procedure usp_OrdersInsert using
defaults), it is stated that:
---
If the default values for the columns had been expressions, such as a
system function like GETDATE(), this modification wouldn't have been so
simple, because a default value for a stored procedure's parameter can
only be a constant. In such a situation, you need to add parameters to
indicate that a default value is desired for a column and then issue
the INSERT using the DEFAULT keyword instead of using a specific value
for the column.
---
I couldn't exactly follow the last line in the above paragraph (which
starts with "In such a situation...."). Can someone explain me this
preferably with an example?
Sorry for the inconvenience caused in navigating to the article in the
above-mentioned URL.
Thanks,
ArpanLet's assume you want to execute a stored proc omitting one of its
parameters (thereby using the parameter default value, assuming it was
specified). If you want that parameter to be variable (such as the
SYSTEM_USER function) then you couldn't use the parameter default value
because a stored proc parameter can only be a constant.
The way you'd do it would be to define an additional parameter to the
proc to indicate this scenario (or perhaps use a special value for the
existing parameter, such as NULL, if you're sure it has no other meaning
in the context). The T-SQL in your proc would then check this
additional parameter and if it has a particular value it would execute
the INSERT statement slightly differently (using the DEFAULT keyword in
the insert statement).
Let me explain with an example...
create proc MyProc
(
@.Param1 int
@.Param2 varchar(128) = 'zzz'
)
as
insert into MyTable (col1, col2)
values (@.Param1, @.Param2)
go
exec MyProc @.Param1=7, @.Param2='abc'
exec MyProc @.Param1=8
OK, everything is fine. The 2nd call would use the value 'zzz' for
@.Param2 because we omitted that parameter when we called MyProc the 2nd
time. But what if we wanted a variable value for @.Param2 instead of
'zzz', like the result of the SYSTEM_USER function for instance? The
proc would look like:
create proc MyProc
(
@.Param1 int
@.Param2 varchar(128) = SYSTEM_USER
)
as
insert into MyTable (col1, col2)
values (@.Param1, @.Param2)
go
But that's invalid syntax! The proc won't even compile. There are a
couple ways to get around that. They both require that the default
value be specified at the table constraint level, so that the DDL for
MyTable looks like:
create table MyTable
(
col1 int,
col2 varchar(128) *DEFAULT SYSTEM_USER*
)
Then you could either use a special value (eg. NULL) for @.Param2 like this:
create proc MyProc
(
@.Param1 int,
@.Param2 varchar(128) = null
)
as
if (@.Param2 is null)
insert into MyTable (col1, col2)
values (@.Param1, *DEFAULT*) -- Use the table default for col2
else
insert into MyTable (col1, col2)
values (@.Param1, @.Param2)
go
which would force SQL Server to use the default defined at the table
constraint level when the insert statement was called. Alternately, if
NULL was a valid value that you wanted to allow for @.Param2, you could
have a 3rd parameter to the proc that would represent this condition,
like this:
create proc MyProc
(
@.Param1 int,
@.Param2 varchar(128) = null,
@.UseDefaultValue bit = 0
)
as
if (@.UseDefaultValue = 1)
insert into MyTable (col1, col2)
values (@.Param1, *DEFAULT*) -- Use the table default for col2
else
insert into MyTable (col1, col2)
values (@.Param1, @.Param2)
go
This would do the same as in the previous schema for MyProc except that
it would also allow MyTable.col2 to be NULL. For example
exec MyProc @.Param1=7, @.Param2='abc' -- MyTable.col2 would be 'abc'
exec MyProc @.Param1=8 -- MyTable.col2 would be NULL
exec MyProc @.Param1=9, @.UseDefaultValue=1 -- MyTable.col2 would be
the result of the SYSTEM_USER function
I hope this make it a little clearer and that I haven't just
you even more.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Arpan wrote:

>Please visit
>http://msdn.microsoft.com/library/e...tegrity_topic05
>& click the link 'Implementing Cascading Operations Using Stored
>Procedures'. Please refer to the sub-topic titled 'Inserting a Row into
>the Primary Table'. There are 2 scripts under this sub-topic. Both the
>scripts create a stored procedure named usp_OrdersInsert. After the
>second script, (which creates the procedure usp_OrdersInsert using
>defaults), it is stated that:
>---
>If the default values for the columns had been expressions, such as a
>system function like GETDATE(), this modification wouldn't have been so
>simple, because a default value for a stored procedure's parameter can
>only be a constant. In such a situation, you need to add parameters to
>indicate that a default value is desired for a column and then issue
>the INSERT using the DEFAULT keyword instead of using a specific value
>for the column.
>---
>I couldn't exactly follow the last line in the above paragraph (which
>starts with "In such a situation...."). Can someone explain me this
>preferably with an example?
>Sorry for the inconvenience caused in navigating to the article in the
>above-mentioned URL.
>Thanks,
>Arpan
>
>|||Thank you very much, Mike, for your input & for devoting your precious
time in helping me out. Your explanation with the appropriate examples
has really made things clearer. I doubt if anyone else could have
clarified my doubts in a better way.
Thanks once again,
Regards,
Arpan

Add database table

Hello everyone, I was wondering if there is a way to dynamically add a table to a SQL database off of a button click coded in C#. I'm not asking for all of the code, I just need a small push in the right direction.

Thank you,
Ryan

You can write up some dynamic SQL in a proc passing the table name as parameter and create the table. But I would be concerned about letting users create objects on the fly. Can you explain more about the business logic? Do you have any process in place to clean up such tables on a periodic basis?

|||

Ok, well I am making a quiz engine and with this I want each client that makes an account on the website to be able to create, edit, and deploy their own quiz on my site. By allowing users to create their quiz in separate tables they will be able to easily edit their quiz because of the separate tables. If I mashed many quizzes into one table I feel it would get too unorganized. The number of quizzes/tables each user can make will be limited so a client cannot easily flood the database. An admin account will be able to edit and or delete every quiz made by any user as well.

Would you mind elaborating more on writing dynamic SQL like you said above. Maybe some code is required here because I'm not very experienced in anything SQL. I understand what you said, but I have no idea where to start.

|||

That doesnt sound like a very good design. You can create the records in one table and identify them by userId or username so when you have to pull out the records you can do so by username. Otherwise there will be LOT of dynamic SQL and it will make the server crawl.. because you dont know what table to select from.

This is an important read before you think of dynamic SQL:The Curse and Blessings of Dynamic SQL

|||

Thank you for pointing out the flaws in my design, I have taken your advice and will be calling rows based on each username. I've already had issues with my new way but I'll make a new post in the correct section. Thank you for the read as well.

Add data file to filegroup

I tried to add new file to new filegroup:
Created a new database in ER, successful.
Then, right click this new database, select propertied.
Goto Data Files tab, add a new data file at the second row and select a new
Location. Set Space Allocated, BUT I cannot set Filegroup to Table, it has
the only option PRIMARY.
Alan
Here's a way I am doing
CREATE DATABASE test
GO
ALTER DATABASE test SET RECOVERY FULL
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uM8cNRqqEHA.3252@.TK2MSFTNGP14.phx.gbl...
> I tried to add new file to new filegroup:
> Created a new database in ER, successful.
> Then, right click this new database, select propertied.
> Goto Data Files tab, add a new data file at the second row and select a
new
> Location. Set Space Allocated, BUT I cannot set Filegroup to Table, it has
> the only option PRIMARY.
>

Add data file to filegroup

I tried to add new file to new filegroup:
Created a new database in ER, successful.
Then, right click this new database, select propertied.
Goto Data Files tab, add a new data file at the second row and select a new
Location. Set Space Allocated, BUT I cannot set Filegroup to Table, it has
the only option PRIMARY.Alan
Here's a way I am doing
CREATE DATABASE test
GO
ALTER DATABASE test SET RECOVERY FULL
ALTER DATABASE test
ADD FILEGROUP ww_Group
GO
ALTER DATABASE test
ADD FILE
( NAME = ww,
FILENAME = 'D:\wwdat1.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP ww_Group
create table test..test(id int identity) on [primary]
create table test..test_GR(id int identity) on ww_Group
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:uM8cNRqqEHA.3252@.TK2MSFTNGP14.phx.gbl...
> I tried to add new file to new filegroup:
> Created a new database in ER, successful.
> Then, right click this new database, select propertied.
> Goto Data Files tab, add a new data file at the second row and select a
new
> Location. Set Space Allocated, BUT I cannot set Filegroup to Table, it has
> the only option PRIMARY.
>