Deployment settings for SSRS 2008 R2 in SP2010 Integrated mode

September 25, 2010

If you have problems deploying reports/data sources etc to Sharepoint 2010 and are certain that permissions etc are all setup correctly it may help to check your project deployment settings. If these are not correct you can get the SSRS login prompt coming up indefinitely when you try to deploy, regardless of the correct user/pass being supplied.

When deploying to Sharepoint integrated reporting servers (and this applies to WSS/MOSS07 as well) you need to explicitly specify the full path to the sharepoint site AND the other deployment folders. As an example, i have a sample BI site running under http://2010VM/sites/BI and have a report library Reports sitting under there. I want to deploy the Adventureworks samples there, so here is a screen shot of basic settings that should work:


Adding SSAS roles via Powershell

September 6, 2010

I needed to delve into Powershell again recently as a means to automate the creation of a long list of roles and the addition of AD groups to them.

Since there were so many i thought it would be easier to have a list of roles in a text file and just parse that to create all your roles automatically. The script posted here is a first pass that was used during development and does the following:

  • iterate through a text file containing a list of roles.
  • For each line found:
    • check if the role exists and then delete it. (the script caters for an initial role creation so the delete was more for testing)
    • Create a new role object from the current line in the source text
    • Add role to SSAS database
    • Add database permission for role
    • Add cube permissions for role to all cubes in database
    • Associated an AD group and allowed set with the role – my example uses [Organisation].[Cost Centre Code] where the member names are the same as the role names. Another assumption for is that the AD group has the same name as the role. One could extend this script easily to parse a CSV for more complex requirements)
    • log any errors found when adding new objects to the database


I got the basic code from Darren’s post and made a bunch of changes to suit my requirements. So here it is….

# Script to create roles in EIS cube databases via a text file

[System.reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)
[System.reflection.Assembly]::LoadWithPartialName(“System.IO”)

$logFile = “PATH TO LOG FILE”
$sw = new-object System.IO.StreamWriter($logFile)

$svr = new-Object Microsoft.AnalysisServices.Server
$svr.Connect(“localhost”)

$dbname = “YOUR_SSAS_DB_NAME”
$db = $svr.Databases.item($dbname)

# Print the Database Name
“Adding Roles to Database: ” + $db.Name

#read file here
$roleLines = Get-Content “PATH TO TEXT FILE CONTAINING LIST OF ROLE NAMES”

#repeat for every line in the file
foreach ($roleName in $roleLines)
{
“Processing Role for: ” + $roleName
$groupname = “DOMAIN_NAME\” + $roleName

#check if exists and delete if so
$roleToDelete = $db.Roles.FindByName($roleName)

if ($roleToDelete)
{
“Existing role found… deleting!”
$roleToDelete.Drop(“AlterOrDeleteDependents”)

$error.clear()
if ($error[0])
{
$sw.WriteLine($rolename + “: Error deleting existing : ” + $error[0])
}

}
else
{

#Create Role and add AD group
[Microsoft.AnalysisServices.Role] $roleToCreate = new-Object([Microsoft.AnalysisServices.Role])($rolename)
$roleToCreate.Members.Add($groupname)

#Add to db
“… ‘” + $roleToCreate.Name + “‘ adding to database”
$db.Roles.Add($roleToCreate)

$error.clear()
$roleToCreate.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error Adding to DB: ” + $error[0])
}
else
{

#Create Database permission
“… ‘” + $roleToCreate.Name + “‘ updating database permissions”
$dbperm = $db.DatabasePermissions.Add($roleToCreate.ID)
$dbperm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed

$error.clear()
$dbperm.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error updating DB Perms: ” + $error[0])
}
else
{

# add role to each cube
foreach ($cube in $db.Cubes)
{

#create Cube permission
“… ‘” + $roleToCreate.Name + “‘ adding to permissions in : ‘” + $cube.Name + “‘ cube”
$cubeperm = $cube.CubePermissions.Add($roleToCreate.ID)
$cubePerm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed

$error.clear()
$cubeperm.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error updating Cube Perms: ” + $error[0])
}
}

if(!$error[0])
{

#create dimension permission
“… ‘” + $roleToCreate.Name + “‘ adding to Org Structure Dim permissions”
$dim = $db.Dimensions.FindByName(“Organisation”)
$att = $dim.Attributes.FindByName(“Cost Centre Code”)
$dim.DimensionPermissions.Add($roleToCreate.ID)

$dimperm = $dim.DimensionPermissions.add($roleToCreate.ID)
$dimperm.Read = [Microsoft.AnalysisServices.ReadAccess]::Allowed

“… ‘” + $roleToCreate.Name + “‘ adding cost centre restriction”
$attPerm = $dimPerm.AttributePermissions.Add($att.ID)
$attPerm.AllowedSet = “{[Organisation].[Cost Centre Code].&[” + $roleName + “]}”

$error.clear()
$dimPerm.Update()

if ($error[0])
{
$sw.WriteLine($rolename + “: Error updating Dimension Perms: ” + $error[0])
}
}

}

}

}

}

#cleanup
$sw.close()
$svr.Disconnect()


Getting to 32-bit ODBC data on 64-bit Windows

September 6, 2010

This really got me frustrated for a few days until a colleague pointed me in the right direction. The issue concerns actually displaying your 32-bit ODBC control panel in 64bit windows.

I was trying to install and setup some 32-bit drivers to an open source database engine within Windows Server 2008. I have been aware for a long time that there are both 32 and 64 bit versions of the ODBC control panel in windows and have set up both in the past (though it had been a while since i had to do 32-bit on 64 bit system.)

I kept running odbcadm32.exe using the “Run..” dialog box. This kept opening up the 64 bit ODBC control panel and had me really confused. The trick is to specify the path and run c:\windows\syswow64\odbc32adm.exe Both versions have the same filename and windows will choose the 64bit by default!