Building an All-Up VPC

Here are the steps and order to build a microsoft bi all up vpc or server.

1. install windows server 2003 R2
• run windows update and install sp2
2. install sql server 2005 enterprise
• pre-requisites
• install db, rs, as, is services
• apply sp2
• install data mining add-ins (optional)
• install dundas controls (optional)
3. install office 2007 enterprise
• word, powerpoint and excel (optional visio for strategy maps and InfoPath for forms)
• apply SP1
4. install .NET Framework 3.0 (required by MOSS/WSS)
• apply SP1
5. install MOSS 2007 enterprise or WSS 3.0
• apply SP1 (or get slipstream version here)
• install Sharepoint Designer (optional)
6. Install Proclarity Analytics Server 6.3
• Desktop Pro
• install reporting services export (optional)
• KPI Designer
• Selector
7. Install Performance Point
• install asp.net 2.0 ajax extensions 1.0
• Monitoring and Analytics
• Planning
• Business Modeler
• Planning excel client
8. Configure Reporting Service Integrated Mode
9. Proclarity PPS patch
10. drink a beer

Security

Steps to implement per user security in All Up MS BI

MOSS/WSS

• edit authentication for the port 80 webapp. Application Management -> Application Security -> Authentication Providers.
• Change IIS setting to Negotiate (Kerberos) with Integrated Windows authentication checked.
• edit web.config found under /inetpub/wwwroot/wss/VirtualDirectories/<port>. Change Bpm.ConnectionPerUser to “True”

PPS

• edit web.config found /Program Files/Microsoft Office PerformancePoint Server/3.0/Monitoring/PPSMonitoring_1/WebService. Change Bpm.ConnectionPerUser to “True”.
• I would advise to change the web.config for the Preview site there too.

ProClarity

• Set Cache.AccessMgr = AccessMgr
• Cache.RestrictAccessToCacheDir = False
• Cache.HitRate = 0
• Cache.SpaceLimitMB = 10
• Cache.HardSpaceLimitMB = 10

SQL

Option 1

• Create Roles, add AD groups to roles
• Lock down dimensions and dimension members in cube.

Option 2

• Cross Reference table which maps userid’s to dimension member. Pass userid as part of allowed member set in mdx.

Quick and Dirty parsing of audit.log file

install Log Parser

run this command

 

LogParser "SELECT Time, MachineName, UserName, UserId, RecordType, CompletionSuccess, Value, Message, Application INTO AuditTable FROM audit.log" -o:SQL -database:YourAuditDB -createTable:ON

 

Basically, it will create a table (on the local machine) and the create the columns in the SELECT statement. I am still working out how to best get at the Array AuditItems.

 

Clear data from your PPS Planning model

I figured out a way to clear out data from your planning model after testing business rules. You write a clear business rule and copy your scope from the business rule then set the value to 0. You cannot set the value to NULL because in the MeasureGroup AppDB table the value column has a notnull constraint on it. So you run the rule, then in SQL, go to the AppDB and the MG_site_model_measuregroup table and delete all rows where value=0. Then you have to deploy the model again in business modeller to re-gen the cube.

Seeding Rules

You cannot use a range (":") in the THIS statement in a business rule. You must use the lag function like…

[Time].[Fiscal].CurrentMember.Lag(6)

BizSystemFlag

There are only two columns that you need to deal with when populating data to the staging db. One is BizSystemFlag, which takes

0 = default, no action

200 = insert

300 = update

400 = delete

The other is BizValidationStatus, which you leave as "0".