Create the Performance Warehouse Database
To install the Performance Warehouse Database 4.5 (PWDB40), you must first run the installation package Pwdb40Bsetup.msi. Then you create the database by running the PWDB40Scripts.exe command.
Create the PWDB40 database by running the PWDB40Scripts.exe command.
Procedure W08-DWR.7: To create the PWDB40 database
On OMSQL01, open a command prompt and change directory to C:\Program Files\Microsoft Hosting\Reporting\PWDB40 Package.
Important Make sure to run the following command from the C:\Program Files\Microsoft Hosting\Reporting\PWDB40 Package directory. If you do not, part of the PWDB40 database creation will fail.
At the command prompt, enter PWDB40Scripts.exe to run the executable.
You will see messages that say Database PWDB40 created successfully and Script completed successfully.
The OMAdmin needs to be able to read data out of the PlanManager database for reporting purposes. Grant the OMAdmin user db_datareader rights to the PlanManager database by using the SQL Server Management Studio on MPSSQL01.
Procedure W08-DWR.8: To grant the OMAdmin user the db_datareader role for the PlanManager database on MPSSQL01
On MPSSQL01, in Microsoft SQL Server Management Studio, expand MPSSQLVS01, expand Databases, expand PlanManager, and then expand Security.
Right-click Users, and then select New User.
In the User name and Login name fields, enter FABRIKAM\OMAdmin. In the Database role membership box, select the db_datareader check box.
Install the files necessary to set up the PWDB40 SSIS package by running pwdb40ssis.msi.
Procedure W08-DWR.9: To install the files for the PWDB40 SSIS
On OMSQL01, in the Monitoring and Reporting folder on the solution distribution media, run Pwdb40ssis.msi.
Follow the wizard to perform a default installation.
Install the PWDB40 SSIS package by running the DTSInstall command line tool.
Procedure W08-DWR.10: To install the PWDB40 SSIS package
On OMSQL01, start a command prompt and ensure it is running with administrative rights.
Run the following command:
DTSInstall "C:\Program Files\Microsoft Hosting\Reporting\SSIS Package\SSISFORPWDB40.SSISDeploymentManifest”
Follow the wizard to start the installation. Select SQL Server deployment for SSIS package. Accept defaults for target SQL server and installation folder. Modify the package parameters to match where you have the PlanManager and PWDB databases installed. For the reference architecture this would be:
Complete the installation.
Inbound and outbound network DTC access is required for network transactions on OMSQL01 for PWDB40 SSIS scheduled job.
Procedure W08-DWR.11: To configure network DTC on OMSQL01
On OMSQL01, at a command prompt, run dcomcnfg to open the Component Services console.
Navigate to Distributed Transaction Coordinator. Right-click Local DTC and select Properties to open the Local DTC Properties dialog box.
On the Security tab, enable the Network DTC Access, Allow Inbound and Allow Outbound options. Ensure that Incoming Caller Authentication Required is selected.
Schedule the PWDB40 SSIS package to run every four hours using SQL Server Management Studio.
Procedure W08-DWR.12: To schedule the PWDB40 SSIS package on OMSQL01
On OMSQL01, run SQL Server Management Studio.
Create a new job. In the Name field, enter Import Data from PlanManager to PWDB40. In the Owner field, enter FABRIKAM\OMAdmin.
Create a new step at the prompt to select a page. In the Step Name field, enter Run PWDB40 SSIS Package. In the Type field, select SQL Server Integration Services Package. For the Server field, select OMSQL01. For the Package field, select pwdb40SSIS.
On the Configuration tab, select Add. Browse to C:\Program Files\Microsoft SQL Server\90\DTS\Packages\SSISFORPWDB40 and choose planmanagerpwdb40config.dtsconfig.
In the left pane, click Advanced. For On success action, select Quit the job reporting success. Confirm the setting and close the New Job Step dialog box.
In the New Job dialog box, in the left pane, select Schedules, and then select New. In the Name field, enter PWDB40 SSIS Schedule. Schedule the package to run once every 24 hours.