Configuration file for the package store

August 13, 2008 Leave a comment
Ran into an issue today with the SSIS package store namely running on a x64 machine that had files stored in both \Program Files and \Program Files (x86)  the SSIS package configuration XML file was pointing in the wrong place which did not contain the config file.  Two articles discussing this were helpful in resolving the issue they can be found here and here.  The first article explains configuring the XML file the second has the registry location pointing to the path of the file.
 
-Andy
Categories: SQL Server SSIS

The total number of errors during this submission is 1.

August 5, 2008 Leave a comment

Today while setting up my training environment for class I ran into this neat error.  I figured the problem was that I removed the model in which the user was assigned to the modeler role before first removing the role. This is what you get when trying to remove the user from the Users section of the Administration Console:

I found that in the back-end the user was still attached to a role of modeler for the model sub-site I had removed. By running the following query I was able to see what roles the user was still attached to, which in this case was only one role. Here is the query to get the UserRoleAssignmentId,RoleId, and UserId associated to a users alias.

--Get all of a users given Roles
SELECT a.[UserRoleAssignmentID]
      ,a.[RoleID]
      ,a.[UserID]
      ,u.[UserName]
  FROM [PPSPlanningSystem].[dbo].[Users_Roles] a
INNER JOIN dbo.BizUsers u on u.[UserId] = a.[UserId]
WHERE u.[UserName] = 'DOMAIN\username'

After determining the UserRoleAssignmentID I was able to remove the user to role association with the stored procedure in PPSPlanningSystem

--Execute to remove all roles from a given user
DECLARE @RC int
DECLARE @UserRoleAssignmentID bigint

EXECUTE @RC = [PPSPlanningSystem].[dbo].[bsp_BizUserRoleDeleteByAssignmentID] 
   @UserRoleAssignmentID='1767'

And finally remove the user from the system using this stored procedure.

--Now you can remove the user from within the admin console or via the store procedeure
DECLARE @RC int
DECLARE @UserID bigint

EXECUTE @RC = [PPSPlanningSystem].[dbo].[bsp_BizUserDelete] 
   @UserID='219'

Interestingly even after you remove the all the roles for a user you still receive the submission error through the console, but the back-end end executes without error and indeed removes the user from the console. If you try to execute the proc to remove the user before removing the role you will receive the following error:

Msg 547, Level 16, State 0, Procedure bsp_BizUserDelete, Line 15
The DELETE statement conflicted with the REFERENCE constraint "FK1_BizUsers_UsersRoles". The conflict occurred in database "PPSPlanningSystem", table "dbo.Users_Roles", column ‘UserID’.
The statement has been terminated.
Msg 50016, Level 16, State 5, Procedure bsp_BizUserDelete, Line 19
Module(Security)SP(bsp_BizUserDelete)ErrSeq#(5): GRANT statement failed at (BizUsers).

-Andy

Categories: Uncategorized

PerformancePoint Server 2007 for Applications certification

I received my PerformancePoint Server 2007 for Applications certification (Exam: 070-556) today.  The test was very easy to register for and there was a bunch of testing centers to take it at. If you are interested in learning more about the Microsoft Performance Management specialization you can get the information here. All the information about training and some great videos/workbooks can be found here.
 
-Andy

PerformancePoint Server (PPS) 2007 Security Migration

July 30, 2008 3 comments

I was recently asked about how to migrate security from one environment to another, I figured what a better way than to post a blog about it. In order to do this you will need at least three things: two CSV files in a format in which the PPSCmd.exe can interpret and a batch file to properly execute the PPSCmd executable.

You must have at minimum read permissions to the [PPSPlanningSystem] database on the server, and be granted user/data administrator roles in the PPS admin console.

Now for the example:


Execute the following queries on SQL Server to retrieve the users and users to roles from the source system, save each of the files as users.csv and userroles.csv respectively:

--Query used to generate users.csv
SELECT c.[UserLabel] AS [Label]
      ,c.[UserName] AS [Name]
      ,c.[EmailAddress] AS [Email]
  FROM [PPSPlanningSystem].[dbo].[BizUsers] c
--Query used to generate userroles.csv
SELECT DISTINCT 
      b.[UserLabel] AS [User]
	,c.[RoleLabel] AS [Role]
	,'ApplicationName' AS [Application] --Application in Business Modeler Site Summary
	,'ApplicationLabelName' AS [Scope] --Label in Business Modeler Site Summary
 FROM [PPSPlanningSystem].[dbo].[Users_Roles] a
 INNER JOIN [PPSPlanningSystem].[dbo].[BizUsers] b ON b.[UserID] = a.[UserID]
 INNER JOIN [PPSPlanningSystem].[dbo].[BizRoles] c ON c.[RoleID] = a.[RoleID]
ORDER BY 2

After generating the two CSV files you are ready to import them, I have created a batch script to do this for me my file looks like the following:

@echo off
@echo Importing Application Security...

SET /p SERVER="Enter PPS web service machine name: "
SET /p PORT="Enter PPS web service port: "
SET /p CSVPATH="Enter Path to CSV Files: ie:C:\Security :"

@echo Importing Users...
ppscmd importsecurity /server http://%SERVER%:%PORT% /type user %CSVPATH%\users.csv
pause
@ECHO Importing Users to Roles...
ppscmd importsecurity /server http://%SERVER%:%PORT% /type userrole %CSVPATH%\userroles.csv
pause

Note that I have just ppscmd in my file, I have added the full path for the PPSCmd to my Path variables in System Properties > Environment Variables> Path default is C:\Program Files\Microsoft Office PerformancePoint Server\3.0\BizModeler\

-Andy

My blogging site is up!!!

Yep! My blogging site is up, I will be putting up my first real post as soon as I get some time to write it up so far topics I have stored are:
 
  • Exporting and Importing security (Security migration) for PPS2007.
  • How to not break the server by changing the server name / domain in which server resides.
  • How to easily migrate reports without manually exporting/importing each one individually.
  • SSIS packages to execute a PPSCMD command and migrate an application.
  • Using T-SQL views and store procedures in conjunction with SSIS to add, update and delete members from dimensions, hierarchies.
  • How to slowly change your models structure.
More topics to come.
 
 I have a lot of information with little spare time to actually post it but I will be working hard to try and get it all out there.
 
Also if anyone reading this knows how to easily use a CSS tag or insert a rich text control into windows live spaces so I can easily post well formatted T-SQL and code that would be helpful.  I am still working on getting Windows Live Writer installed for some reason the firewall here at work blocks the installer.. =(
 
 
-Andy
Categories: General Posts
Follow

Get every new post delivered to your Inbox.