Create the Performance Warehouse Database

  • Article

To install the Performance Warehouse Database (PWDB40), you must first run the installation package Pwdb40Bsetup.msi. 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 OMAdmin User the db_datareader Role for the PlanManager Database

  4. Install the Files for the PWDB40 SSIS

  5. Install the PWDB40 SSIS Package

  6. Configure Network DTC on OMSQL01

  7. Schedule the PWDB40 SSIS Package

Install the Files for the PWDB40 Database

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

Procedure W03-DWR.4: To install the files for the PWDB40 database

  1. On OMSQL01, in the Monitoring and Reporting folder on the solution distribution media, run Pwdb40DBsetup.msi.

  2. Follow the wizard to perform a default installation.

Create the PWDB40 Database

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

Procedure W03-DWR.5: To create the PWDB40 database

  1. 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.

  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.

Note

  • If the PWDB40 database already exists, you will be prompted to decide whether to re-create the database or not. To remove the existing PWDB40 database and create a new one, enter Y. Otherwise, the existing PWDB40 database will be upgraded to the latest version without data loss.

  • You can also run the PWDB40Scripts.exe /r command to directly remove the existing PWDB40 database and create a new one.

  • During the database re-creating process, no prompt will be displayed.

  • Before you re-create the PWDB40 database, back up the existing PWDB40 database to avoid data loss.

Grant the OMAdmin User the db_datareader Role for the PlanManager Database

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 W03-DWR.6: To grant the OMAdmin 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\OMAdmin. In the Database role membership box, select the db_datareader check box.

Install the Files for the PWDB40 SSIS

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

Procedure W03-DWR.7: To install the files for the PWDB40 SSIS

  1. On OMSQL01, in the Monitoring and Reporting folder on the solution distribution media, run Pwdb40ssis.msi.

  2. Follow the wizard to perform a default installation.

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 W03-DWR.8: To install the PWDB40 SSIS package

  1. On OMSQL01, in Windows explorer, navigate to the C:\Program Files\Microsoft Hosting\Reporting\SSIS Package folder, and then double-click SSISFORPWDB40.SSISDeploymentManifest.

  2. 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:

    Property

    Value

    \Package.Connections[ProvisionSourceDbDataConnection].Properties[ServerName]

    MPSSQLVS01

    \Package.Connections[ProvisionWarehouseDbDataConnection].Properties[ServerName]

    localhost

  3. Complete the installation.

Configure Network DTC on OMSQL01

Inbound and outbound network DTC access is required for network transactions on OMSQL01 for PWDB40 SSIS scheduled job.

Procedure W03-DWR.9: To configure network DTC on OMSQL01

  1. On OMSQL01, open the Component Services console from Administrative Tools.

  2. Expand Component Services, and then expand Computers. Right-click My Computer and select Properties to open the My Computer Properties page.

  3. On the MSDTC tab, click Security Configuration and verify the following information:

    • Ensure the Network DTC Access, Allow Inbound and Allow Outbound check boxes are selected

    • All other options are left as default.

  4. Follow the prompt to restart the MSDTC service.

Schedule the PWDB40 SSIS Package

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

Procedure W03-DWR.10: To schedule the PWDB40 SSIS package on OMSQL01

  1. On OMSQL01, run SQL Server Management Studio.

  2. Create a new job. In the Name field, enter Import Data from PlanManager to PWDB40. In the Owner field, enter FABRIKAM\OMAdmin.

  3. 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.

  4. On the Configuration tab, select Add. Browse to C:\Program Files\Microsoft SQL Server\90\DTS\Packages\SSISFORPWDB40 and choose planmanagerpwdb40config.dtsconfig.

  5. 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.

  6. 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.