Installing & configuring Service Reporting for IaaS usage and metering

This is the last blog post in a series on how to enable usage and metering for IaaS using System Center and Windows Azure Pack.

The series includes the following posts: 

Service Reporting is a new components in System Center 2012 R2, and in this blog post I’ll go over how you can install and configure it by doing the following:

  • Installing Service Reporting
  • Configure Service Reporting to extract data from Windows Azure Pack
  • Verifying data gets processed in the cubes
  • Make a simple dashboard in SharePoint.

The following things must be configured before starting on this guide

Completing all steps and pre-requisites from the previous blog posts:

Additional to the two prerequisites described above, the following components are needed:

  • SQL 2012 SP1 or higher running Analysis Services
  • SharePoint 2013 Enterprise with PerformancePoint enabled.

The Environment:

Operations Manager Server: OM01.contoso.com
Operations Manager DB Server DB03.contoso.com
Operations Manager DW Database Server: DB04.contoso.com
VMM Server: VMM01.contoso.com
SPF Server: VMM01.contoso.com
WAP Server: WAP01.contoso.com
Service Reporting Server: DB06.contoso.com
Service Reporting Database: DB06.contoso.com
SharePoint Server: SP01.contoso.com

 

Installing Service Reporting

The Service Reporting component will extract data from WAP Usage Service, transform the data and load it into the UsageAnalysis db.

Service Reporting is made up of three databases that are used for the ETL process:

  • UsageETLRepositoryDB
  • UsageStagingDB
  • UsageDatawarehouseDB

The following requirements should be verified before starting the Service Reporting installation.

Verify that SQL Services (SQL Server, SQL Server Analysis Services & SQL Server Agent) are running under a Domain account and that this domain account has the required access to the UsageDatawarehouseDB databases instance where Service Reporting DB will be implemented. Also make sure that this domain account has read access to the System Center 2012 R2 Operations Manager databases.

To verify the SQL Service Accounts settings do the following:

1. Logon to the server where Service Reporting databases will be installed.

2. Launch SQL Server Configuration Manager

3. Under SQL Server Services, verify that the following services are running under a domain account.

    image

To verify that the Service Account(s) has access to the database instance do the following:

1. Open SQL Management Studio as Administrator

2. Specify the server name for the Service Reporting SQL Database Server

3. Verify there is a login user for the service account (SQL Server & SQL Analysis Services) also verify that this user has the needed rights on the instance.

    image

Note: It might be necessary to modify the access after the install of Service Reporting, for the user to have the needed access to the UsageDatawarehouseDB if this is not granted at the instance level.

4. Do the same for the database server holding the Operations Manager Databases

5. Verify that the SQL Agent Service Account has read access to the Operations Manager Databases.

To install Service Reporting do the following:

1. Login as an administrator that has Sysadmin rights on the database server where Service Reporting component is to be installed.

2. Mount the Orchestrator 2012 R2 media on the server

3. Run SetupOrchestrastor.exe

4.  Select Service Reporting under the Service Management Section

    image

5. Click Install

6. Click Accept and click Next to License Terms

7. Verify that all prerequisites are “passed”

8. Specify a path for the installation folder and Click “Next”

9. Specify the Database server and instance for Data Warehouse databases and click “Next”

    image

10. Specify the SQL Analysis Server where Service Reporting is to be installed and click “Next”

    image

11. Click Next to Microsoft updates and review Installation Summery, if ok, click “Install”

12. Verify that the install goes well.

Configure Service Reporting to extract data from Windows Azure Pack

The Service Reporting component will extract data from the WAP Usage REST API. To get access to the data a user and password needs to me configured on the WAP Usage Server. To do this do the following:

1. Logon on to WAP Server and Start Windows PowerShell as Administrator

2. Load the WAP PS Module:

    Import-module MgmtSvcAdmin

3. Set the User Name for the WEB Service:

    Set-MgmtSvcSetting -Namespace UsageService -Name Username -Value ‘<User Name>’

    Exmaple: Set-MgmtSvcSetting -Namespace UsageService -Name Username -Value ‘SC’

4. Set the password for the user: Set-MgmtSvcSetting -Namespace UsageService -Name Password –Value ‘<Password>’ –Encode

    Example: Set-MgmtSvcSetting -Namespace UsageService -Name Password -Value ‘Password1’ –Encode

We have now created a user name and a password for the WAP Usage REST API, so Service Reporting will be able to extract usage data from WAP Usage Service. The next step is to configure Service Reporting to connect to the WAP Usage REST API. To do this do the following:

1. Login as an administrator on Service Reporting server.

2. Edit MaintenanceConfig.xml file under <InstallationDrive>:\Program Files\Microsoft System Center 2012 R2\Service Reporting\Maintenance folder

3. Update OM SQL Server Name and Database and WAP Usage Service in the XML file

Example:

<DataExtraction>

<ExtractSystemName>Service Reporting DW System</ExtractSystemName>

<ExtractProcessGroup>SR Data Extraction</ExtractProcessGroup>

<!–Start Register OperationsManager–>

<OperationsManagerSQLServers>DB03</OperationsManagerSQLServers>

<OperationsManagerDBs>OperationsManager</OperationsManagerDBs>

<!–End Register OperationsManager–>

<!–Start Register Windows Azure Pack Usage Service–>

<WAPUsageServiceURI>https://wap01.contoso.com:30022/usage?</WAPUsageServiceURI>

4. Open Windows PowerShell as an administrator.

5. Navigate to the <InstallationDrive>:\Program Files\Microsoft System Center 2012 R2\Service Reporting\Maintenance folder. For example, type cd “c:\Program Files\Microsoft System Center 2012 R2\Service Reporting\Maintenance” and then press ENTER

6. Run the following command to configure Service Reporting to talk to Operations Manager and WAP Usage REST API:

.\PostDeploymentConfig.ps1 –User <User from previous command> –Password <Password from previous step>

Example: .\PostDeploymentConfig.ps1 –User SC -Password Password1

    image

Verifying data gets transferred from WAP and processed in the cubes

1. Logon to the Database Server that hosts Service Reporting Databases as an administrator

2. Start SQL Management Studio and connect to the Database Instance holding the databases.

3.  Go to Databases folder, expand it and verify you can see the following Databases:

    image

4. Select SQL Server Agent > Jobs

5. Verify that the following jobs shows:

    image

6. Right Click on the Service Reporting DW System Job and select “Start Job at Step..”

7. Click Start

8. Verify that the job completes successfully (This can take some time to complete)

   image

9. Go to Databases folder, expand it and expand UsageDatawarehouseDB > Tables

10. Right clink on CloudDim and select “Select top 1000 Rows”

11. Verify that data shows in the table that is similar to below Picture

image

12. Click on “Connect” in the ribbon bar and select “Analysis Services”

13. Give the name on the server where Analysis Services is holding the Service Reporting Cubes and click “Connect”

14. Go to Databases folder, expand it and verify you can see the following databases

    image

15. Right click on each Database and select “Process”

16. Click “Ok”

17. Verify that the processing goes well.

   image

18. Click “Close” and close SQL Management Studio

Make a simple Dashboard in SharePoint

In order to show the data in the Data Warehouse we are using SharePoint 2013 PerformancePoint.

I will not explain how to install SharePoint, but will walk over the configuration of SharePoint PerformancePoint to connect to Data Warehouse.

I will give a few hints and tips. In this scenario I’m using SQL 2012 SP1 and SharePoint 2013

  • Remember to install ADOMD.NET to allow SharePoint 2013 to connect to SQL from here
  • Specifying Your ADOMD.NET Data Provider Version
  • Configure a new site with Business Intelligence (PerformancePoint)
  • Remember to configure a secure store with a user that has access to Data Warehouse

To create a sample Dashboard in SharePoint do the following

Create a data connection

1. Start a browser on the SharePoint Server and go to the new PerformancePoint site created for Usage & Metering

2. Select PerformancePoint Content and click on PERFORMANCEPOINT in the top menu and click “Dashboard Designer”

    image

Note: If Dashboard Designer is used for the first time, the designer will install (Click Run) and console will launch.   

3. Right Click on Data Connections and select “New Data Source”

4. Select Analysis Services and click “Ok”

5. Specify the server that holds the Analysis Services for Service Reporting

6. Click on the Database field and select UsageAnalysisDB from the list

7. From Cube select SRUsageCube

    Note: Cubes must have been processed at least once to show in this list

8. Click Test Data Source and verify that connection is successful

9. Right Click on the New Data Source Rename and save the Data Connection

Create Reports

1. In Dashboard Designer console select “PerformancePoint Content”

2. Right Click on “PerformancePoint Content” and select “New Report”

   image

3. In the “Report Template” select “Analytic Chart”

   image

4. Select the Data source that was created earlier

5. Give the Report a name e.g. “VM Runtime QTR”

6. Select “VM Runtime QTR” in the Workspace Browser

7. In the Details Pane Select “Measures”

8. In the Details Pane Select “Measures”

   image

9. Expand “Measures” and scroll down until “Hourly VM RunTime Total” shows in the list

10. Select “Hourly VM RunTime Total” and Drag and Drop it over to the “Series” Box in the lower central pane

11. Go back to the details view and Extend Dimensions > Date > Calendar Quarter

12. Drag and drop this into the “Bottom Axis”

13. You should now see Data in the central window showing “Hourly VM RunTime Total” at the bottom of the Report

14. Repeat step 4-12 using the following details:

Name of Report Series value (Measures) Bottom Axis (Dimensions)
Core Allocations QTR Daily Core Allocated Date Calendar Quarter
Disk Space QTR Daily DiskspaceAllocated
Daily DiskspaceUsed
Date Calendar Quarter

15. There should now be 3 reports in the Performance Content View

    image

Create  a Dashboard

1. In Dashboard Designer console select “PerformancePoint Content”

2. Right Click on “PerformancePoint Content” and select “New > Dashboard”

3. In the “Select a Dashboard Page Template” select “3 Columns” and click “Ok”

4. Give the Dashboard a name E.g. VM Dashboard under “Workspace Browser windows”

5. Click on Name (Page 1) and Rename it to VM Dashboard.

6. In the Details pane extend “Reports” > “PerformancePoint Content”

7. Select “Hourly VM RunTime Total” and drag and drop it into “Dashboard Content” > “Left Column”

8. Do the same for “Core Allocations QTR” and drag  & drop this in the “middle Column”

9. Drag and drop “Disk Space QTR” to the “Right Column”

Publish a Dashboard to PerformancePoint

In order to publish the dashboard in SharePoint, do the following

1. Right Click on the dashboard just created and select Deploy to SharePoint

2. If prompted Select SharePoint Server and Version and Click “Ok”

3. A browser windows will open and show the Dashboard inside PerformancePoint

The Dashboard should look similar to the one below:

    image

To drill Down on data do the following:

1. Click on VM Runtime QTR

2. Right click on one of the graphs and Select Drill Down To > Cloud

    image

    You can now see the number of VM hours pr cloud in the window.

    image

    If you hover over one of the pillars you can see the number of hours for a cloud for a given period of time.

3. Right click on one of the graphs and Select Drill Down To > Virtual Machine

    This will show you which Virtual Machine with the most VM Runtime hours within a give Cloud.

    image

I hope this introduction to Usage and Metering using System Center and Windows Azure Pack will help you better utilize these new functions in R2.

Please let me know if you have any feedback on this series!

Happy Usage and Metering using System Center and Windows Azure Pack.