Create the Performance Warehouse Database

  • Article
Cc526692.chm_head_left(en-us,TechNet.10).gif Cc526692.chm_head_middle(en-us,TechNet.10).gif Cc526692.chm_head_right(en-us,TechNet.10).gif

Create the Performance Warehouse Database

To install the Performance Warehouse Database 4.0 (PWDB40), you must first run the installation package Pwdb40Bsetup.msi from the Microsoft Hosting Solutions for Service Providers. Then you create the database by running the PWDB40Scripts.exe command.

Tasks

  1. Install the Files for the PWDB40 Database
  2. Create the PWDB40 Database
  3. Grant the MOMService User the db_datareader Role for the PlanManager Database
  4. Install the Files for the PWDB40 SSIS
  5. Install the PWDB40 SSIS Package
  6. Schedule the PWDB40 SSIS Package

Install the Files for the PWDB40 Database

Install the PWDB files by running Pwdb40Bsetup.msi on MOMSQL01.

Procedure DWR.11: To install the files for the PWDB40 database

  1. On MOMSQL01, in the Monitoring and Reporting folder on the solution distribution media, run Pwdb40DBsetup.msi.
  2. On the Welcome page click Next.
  3. Accept the default installation folder, click Next, and then click Next again.
  4. After the wizard has completed installation, click Close.

Create the PWDB40 Database

Create the PWDB40 database by running the PWDB40Scripts.exe command.

Procedure DWR.12: To create the PWDB40 database

  1. On MOMSQL01, 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.

  2. At the command prompt, enter PWDB40Scripts.exe to run the executable.

  3. You will see messages that say Database PWDB40 created successfully and Script completed successfully.

Grant the MOMService User the db_datareader Role for the PlanManager Database

The MOMService needs to be able to read data out of the PlanManager database for reporting purposes. Grant the MOMService user db_datareader rights to the PlanManager database using the SQL Server Management Studio on MPSSQL01.

Procedure DWR.13: To grant the MOMService user the db_datareader role for the PlanManager database on MPSSQL01

  1. On MPSSQL01, in Microsoft SQL Server Management Studio, expand MPSSQLVS01, expand Databases, expand PlanManager, and then expand Security.
  2. Right-click Users, and then select New User.
  3. In the User name and Login name fields, enter FABRIKAM\MOMService. In the Database role membership box, select the db_datareader check box. Click OK.

Install the Files for the PWDB40 SSIS

Install the files necessary to set up the PWDB40 SSIS package by running pwdb40ssis.msi.

Procedure DWR.14: To install the files for the PWDB40 SSIS

  1. On MOMSQL01, in theMonitoring and Reporting folder on the solution distribution media, run Pwdb40ssis.msi.
  2. On the Welcome page, click Next.
  3. Accept the default installation folder, click Next, and then click Next again.
  4. After the wizard has completed installation, click Close.

Install the PWDB40 SSIS Package

Install the PWDB40 SSIS package by double-clicking the SSISFORPWDB40.SSISDeploymentManifest file, which will then invoke the Package Installation Wizard.

Procedure DWR.15: To install the PWDB40 SSIS package

  1. On MOMSQL01, in Windows explorer, navigate to the C:\Program Files\Microsoft Hosting\Reporting\SSI Package folder.

  2. Double-click the SSISFORPWDB40.SSISDeploymentManifest file.

  3. On the Welcome page, click Next.

  4. On the Deploy SSIS Packages page, select SQL Server deployment, and then click Next.

  5. On the Specify Target SQL Server page, accept the defaults, and then click Next.

  6. On the Select Installation Folder page, accept the default path, and then click Next.

  7. On the Confirm the Installation page, click Next.

  8. On the Configure Packages page, modify the package parameters to match where you have the PlanManager and PWDB databases installed. For the reference architecture this would be:

    Property Value
    \Package.Connections[ProvisionSourceDbDataConnection].Properties[ServerName] MPSSQLVS01
    \Package.Connections[ProvisionWarehouseDbDataConnection].Properties[ServerName] localhost

    Click Next.

  9. Click Finish to install the package.

Schedule the PWDB40 SSIS Package

Schedule the PWDB40 SSIS package to run every four hours using SQL Server Management Studio.

Procedure DWR.16: To schedule the PWDB40 SSIS package on MOMSQL01

  1. On MOMSQL01, run SQL Server Management Studio.
  2. Expand SQL Server Agent, right-click Jobs, and then select New Job.
  3. In the Name field, enter Import Data from PlanManager to PWDB40. In the Owner field, enter FABRIKAM\MOMService.
  4. In the Select a page pane, click Steps, and then click New.
  5. In the Step Name field, enter Run PWDB40 SSIS Package. In the type field, select SQL Server Integration Services Package. For the Server field, use the drop-down to select MOMSQL01. For the Package field, click the ... button, click pwdb40SSIS, and then click OK to select it.
  6. Click the Configuration tab, and then click Add. Browse to C:\Program Files\Microsoft SQL Server\90\DTS\Packages\SSISFORPWDB40 and choose planmanagerpwdb40config.dtsconfig.
  7. In the left pane click Advanced. For On success action, select Quit the job reporting success.
  8. Click OK to close the New Job Step dialog box.
  9. In the New Job dialog box, in the left pane, click Schedules, and then click New. In the Name field, enter PWDB40 SSIS Schedule. Schedule the package to run once every 24 hours.
  10. Click OK to close the New Job dialog box.