Tuesday, August 21, 2007

AMO Script for Automating SSAS Database Backups

By Bryan C. Smith

I apologize in advance, but I'm afraid I need to get up on my soapbox for this one. Bear with me.

START SOAPBOX

I have not seen folks pursuing regular backups of multidimensional databases as aggressively as they do relational databases. Part of this is an attitude that multidimensional databases are simply redundant copies of their relational data sources. Still another part of this is a lack of familiarity with the SSAS technology and the tools available to support regular backups. Whatever the reason, the "zero data loss" mentality has not spread to the multidimensional landscape.

So, let's clearly state a few things. Multidimensional databases do occasionally get corrupted, developers do introduce errors, and systems do fail. Projects housing the database definition do get lost and corrupted as well, even in Visual Source Safe and other related systems.

Though you may have a good set of project files available with which to rebuild a multidimensional database, important changes can be introduced through management activities that are not reflected in the BIDS project. Furthermore, the time it takes to reprocess a cube following a failure is often not acceptable to end-users, especially when those databases support critical business processes.

It is important we insure regular backups are taken of our multidimensional databases and are managed in a manner commensurate with our relational data sources.

END SOAPBOX

So, I've taken a look at a few means for handling regular backups of multidimensional databases. In the end, I find AMO the most appealing tool for this.

Below is a sample script that you may wish to explore for your own purposes. This was implemented in the Script task of an SSIS package. If you wish to test the script out, be sure to add a reference to the Analysis Management Objects library in your script editor.

Also, I need to note that this is not intended to be deployed to production as is. If you wish to use this script in a production setting, please thoroughly test it, add appropriate error handling, etc. If you use all or part of this script, you assume responsibility for its behavior. Sorry, but I've got to say it.


Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public
Class ScriptMain



Public
Sub Main()



'Get TimeStamp in form YYYYMMDDhhmmss


'------------------------------------------------------


Dim MyTimestamp As
String

MyTimestamp = CStr(Year(Now()) * 10000 + _

Month(Now()) * 100 + Day(Now()))

MyTimestamp += CStr(Hour(Now()) * 10000 + _

Minute(Now()) * 100 + Second(Now()))


 

'Connect to SSAS Instance


'------------------------------------------------------


Dim MyServer As
New Server

MyServer.Connect("localhost")


 

'Backup Databases


'------------------------------------------------------


Dim BackupFileName As
String


Dim AllowOverwrite As
Boolean = True


Dim BackupRemotePartitions As
Boolean = False


Dim MyLocation() As BackupLocation


Dim ApplyCompression As
Boolean = True


 


For
Each MyDatabase As Database In MyServer.Databases

BackupFileName = "c:\temp\" + MyDatabase.Name + _

"_" + MyTimestamp + ".abf"

MyDatabase.Backup(_

BackupFileName, AllowOverwrite, _

BackupRemotePartitions, MyLocation, _

ApplyCompression)


Next


 


'Disconnect from SSAS Instance


'------------------------------------------------------

MyServer.Disconnect()


Dts.TaskResult = Dts.Results.Success


End
Sub



End
Class

Sunday, August 19, 2007

Implementing User-Specific Security in SSAS

By Bryan C. Smith


 

You often have a requirement to restrict access to members of a dimension on a user-specific basis. You can accomplish this through the use of what is referred to as "dynamic security". The purpose of this blog entry is to demonstrate a standard technique for implementing this.

In this demonstration, you will limit access to the Reseller dimension of the Adventure Works DW multidimensional database. You will implement structures in both this database and the associated AdventureWorksDW relational database. It is assumed these databases are hosted locally, and it is recommended you backup both databases prior to making any of the required changes.

In addition, you will leverage two local user accounts, TestUser1 and TestUser2, created for the purposes of this demonstration. The name of your local system is assumed to be MyLaptop so that the full names of these accounts are MyLaptop\TestUser1 and MyLaptop\TestUser2. When these exercises are completed, these two accounts should be dropped from your system.


 

The User Dimension

Your first objective is to create the User dimension. This dimension simply houses a list of end-user accounts. You start by implementing the DimUser table in the relational database:

create
table DimUser (

    UserKey int identity(1,1) not
null,

    UserName varchar(256)
not
null

    )

alter
table DimUser add

    constraint PK_DimUser primary
key
(userkey),

    constraint AK_DimUser unique
(username)


 

We then add entries for our two test accounts:

insert
into DimUser (UserName) values
('MyLaptop\TestUser1')

insert
into DimUser (UserName) values
('MyLaptop\TestUser2')


 

TestUser1 and TestUser2 should have received UserKey values of 1 and 2, respectively.

In the multidimensional database, you now add the DimUser table to the DSV and create a new dimension, User, from it. The User dimension has a single attribute hierarchy, User, whose key and name are the UserKey and UserName fields, respectively.


 

The ResellerUser Fact Table

With the User dimension in place, you now need to construct a list of which resellers your individual users are allowed to see. In the relational database, you implement this through the fact-less fact table, FactResellerUser:

create
table FactResellerUser (

    UserKey int
not
null,

    ResellerKey int
not
null

    )

alter
table FactResellerUser add

    constraint PK_FactRsellerUser

primary
key
(resellerkey,userkey),

    constraint FK_FactRsellerUser_UserKey

        foreign
key
(UserKey)

        references DimUser (UserKey),

    constraint FK_FactRsellerUser_ResellerKey

        foreign
key
(ResellerKey)

        references DimReseller (ResellerKey)


 

For the purposes of this demonstration, let's say TestUser1 (UserKey=1) may see reseller's with ResellerKey values of 1 to 5:

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,1)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,2)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,3)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,4)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,5)

Let's then say TestUser2 (UserKey=2) may see reseller's with ResellerKey values of 3 to 7:

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,3)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,4)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,5)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,6)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,7)

In the multidimensional database, you now add FactResellerUser to the DSV. The relationships between this table and the DimReseller and DimUser tables should be reflected in the DSV as well.

In the Adventure Works cube, you then create a new measure group, Reseller User, based on the FactResellerUser table. It will contain a single measure using the count aggregation function. The BIDS Cube Designer should automatically add the User dimension to the cube and associate the Reseller User measure group with it. The Cube Designer should also automatically associate our measure group with the Reseller dimension. You will need to confirm this and manually update the cube if necessary.


 

Cleaning Up the Cube

The Reseller User measure group and the User dimension will provide the basis for your user-specific security, but these do not represent items with which you intend to have your users directly interact. Your objective now is to hide these items.

To hide the measure group, set the Visible property on its single measure to False. With no visible measures, the measure group will not be displayed to end-users. To hide the User dimension in the cube, set the Visible property on the cube dimension to False.

It is important to keep in mind you have only hidden these items. End-user's aware of their presence can still query them.


 

Setting-Up the Role

Now it's time to set up the role in the multidimensional database. You create a new role, MyRole, with Read access to the Adventure Works cube. You add TestUser1 and TestUser2 as members.

Your next step is to limit access to the User dimension. You don't want end-users to query the User dimension and see a list of user accounts with access to the database. To secure it, you set an allowed set on the User attribute of the User database dimension as follows:

STRTOSET("[User].[User].["+Username+"]")

The Username function returns a string which is the full name of the end-user's user account. For example, when your TestUser1 user connects to the multidimensional database, the Username function will return "MyLaptop\TestUser1".

The Allowed Set requires you to specify a set of members in the User attribute hierarchy. You concatenate the string returned by the Username function with other strings to assemble a definition for a valid, one-member set. You then convert that string into an actual set with the STRTOSET function.

Now it's time focus your attention on the Reseller dimension. You will leverage the relationship between the User dimension and the Reseller dimension captured in the Reseller User measure group. Of course, this relationship exists within the Adventure Works cube so you will be working with the Reseller cube dimension.

For the Allowed Set on the Reseller attribute hierarchy, you specify the following:

EXISTS(

    [Reseller].[Reseller].Members,

    STRTOSET("[User].[User].["+Username+"]"),

    'Reseller User'

    )

The EXISTS function returns the set of members from the first set, [Reseller].[Reseller].Members, associated with the members in the second set, STRTOSET("[User].[User].["+Username+"]"), as determined by a specified measure group, 'Reseller User'. It is important to note that in indicating measure group, you are must state the measure group's name, not the name of a measure within the measure group, and that name must be enclosed in single-quotes. Many first time user's of this variation on the EXISTS function are frequently confused by these points.


 

Testing Security

Once the MyRole is deployed to the SSAS instance, you need to test the set up. To do this, you run SQL Server Management Studio as one of our test users, TestUser1. (From the Start menu, select Programs then Microsoft SQL Server 2005. Right-click the SQL Server Management Studio icon and select Run As from the context menu. Enter the appropriate account information to start SSMS as TestUser1.)

Once started, you open an MDX Query window and issue the following statement:

select

    {} on 0,

    [User].[User].Members
on 1

from    [Adventure Works]

;

All

MyLaptop\TestUser1


 

You then execute this statement:

with
member [Measures].[Reseller Key] as

    [Reseller].[Reseller].CurrentMember.UniqueName

select

    [Measures].[Reseller Key] on 0,

    [Reseller].[Reseller].Members
on 1

from    [Adventure Works]

;

 

Reseller Key

All Resellers

[Reseller].[Reseller].[All Resellers]

A Bike Store

[Reseller].[Reseller].&[1]

Advanced Bike Components

[Reseller].[Reseller].&[3]

Metropolitan Sports Supply

[Reseller].[Reseller].&[5]

Modular Cycle Systems

[Reseller].[Reseller].&[4]

Progressive Sports

[Reseller].[Reseller].&[2]

Monday, August 13, 2007

Non-Deterministic Functions in Named Sets

By Bryan C. Smith

Named sets are a great way to encapsulate complex or frequently used set definitions. There are three ways to create a named set:

  1. The WITH SET clause of an MDX SELECT statement
  2. The CREATE SESSION SET statement
  3. The CREATE SET statement within the cube calculations script

Which of these you select depends on your needs and determines how and when the set is resolved. This is critical to understand when using non-deterministic functions as demonstrated in the following exercises.

These exercises assume you have a copy of the Adventure Works DW database running on a local instance of SSAS 2005. It is also assumed you have the permissions needed to adjust the Date/Time settings of this system.


 

The WITH SET clause

Change your system's time to the year 2004. Open an MDX Query window in SSMS, connect to the local instance containing Adventure Works DW, and execute the following:

with
set [Current Year] as

    STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

select

    [Measures].[Reseller Sales Amount] on 0,

    [Current Year] on 1

from    [Adventure Works]

;

 

Reseller Sales Amount

CY 2004

$16,038,062.60


 

Change your system's time to the year 2003 and re-execute the same query:

 

Reseller Sales Amount

CY 2003

$32,202,669.43


 

The named set created with the WITH SET clause is query-scoped. It is compiled and resolved with each execution of the query but is not available to other queries unless the set definition is repeated.


 

The CREATE SESSION SET Statement

Set your system's time to the year 2004 and execute this query to create the named set:

create
session
set [Adventure Works].[Current Year] as

    STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

;

With execution completed, change your system's time to the year 2003. Now, execute this query:

select

    [Measures].[Reseller Sales Amount] on 0,

    [Current Year] on 1

from    [Adventure Works]

;

 

Reseller Sales Amount

CY 2004

$16,038,062.60


 

A couple things are happening here. First, the CREATE SESSION SET statement is compiled separate from the query. Any query utilizing the connection within which the set was compiled can leverage it. Once the connection is terminated, the set is lost.

Next, the set is resolved to its members when the CREATE SESSION SET is submitted. Set membership does not change until the set is dropped and recreated.


 

The CREATE SET Statement in the Calculations Script

Change your system's time back to the year 2004. In BIDS, add the following to the calculations script and redeploy the cube:

create
set [Adventure Works].[Current Year] as

    STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

;    


 

Change your system's time to the year 2003, reconnect to the Adventure Works DW database, and execute the following query.

select

    [Measures].[Reseller Sales Amount] on 0,

    [Current Year] on 1

from    [Adventure Works]

;

 

Reseller Sales Amount

CY 2003

$32,202,669.43


 

Now, reset your system's time to the year 2004 and re-execute the query from above:

 

Reseller Sales Amount

CY 2003

$32,202,669.43


 

Open a new query window and execute the SELECT statement again:

 

Reseller Sales Amount

CY 2003

$32,202,669.43


 

With your system's time still set to the year 2004, restart the SSAS 2005 service and reconnect. Execute the query again:

 

Reseller Sales Amount

CY 2004

$16,038,062.60


 

Unlike the session-scoped set, the named set is not resolved until the first time it is used. The members of that set are then defined and used across all queries until the SSAS service is restarted. With the first query submitted against the restarted service, the set's membership is defined again.

Before going further, please reset your system to the current time.


 

Additional Thoughts

I originally wrote this blog entry to use the RND function as my non-deterministic function. I would randomly select a Reseller to demonstrate the patterns shown above. I found an unusual pattern when I deployed the named set as part of the calculations script. Restarting the SSAS service resulted in a random member being returned with the first query, but redeployment of the database reset the membership of the set to the same member every time. I guess the take home message here is when using non-deterministic functions in your named sets, be sure to understand the basic patterns and test thoroughly.