Create IT Microsoft Cloud Solution Provider (CSP) Reporting Samples
This is a CSP Reporting sample application, intended for Microsoft CSP Partners, that provides Customer Insights (CI) and Opportunities Identification (OI) for Office 365 (seat based licensing).
The analysis made by the sample, complements and details the information that is available in the CSP Partner Center Portal, and also shows how with a basic set of rules the CSP customer basis can be segmented and classified in terms of:
- Activation Opportunities - Seat related opportunities (e.g. A customer purchased 10 E3 seats but only 5 of them are assigned)
- Usage Opportunities - Last 30 days' service usage opportunities (e.g. From the 5 assigned E3 seats, 2 licenses have not used Skype for Business in the last 30 days)
(At the end of this document, you can find more detail about the opportunity types that are identified.)
Supplied with this information, the CSP Partner can for example determine and prioritize the customers that should be targeted by a Marketing Campaign, and/or work with their Sales and Support teams, to get these customers to make a better use of their purchased licenses and services.
The sample is composed by:
- A Reporting Job - Extracts and Analysis the customer information, that results in the Opportunities Identification. Can output information to CSV files or to a database, allowing in the case of the database to store and compare customer historic information.
- Power BI Reports (optional) - Provides reporting samples that present the extracted and analyzed information
- A Reporting Portal (optional) - Provides a basic mechanism for managing the customers that are targeted by a campaign, allowing the filtering of the report to the campaign customer base. The actual management and sending of these campaigns are not in the scope of these samples.
The minimum installation corresponds to the deployment of the Reporting Job, supporting only the output of information to CSV files. For this you will need:
- .NET Framework 4.6.1
- Internet Access
You can also use this configuration if you just want to check/test these samples.
The recommended configuration corresponds to the deployment of the Reporting Job and the Power BI Reports. Optionally, you can also deploy the Reporting Portal if you think that filtering the customers by associated campaign is going to be useful for your scenario. For this you will need:
- Reporting Job:
- .NET Framework 4.6.1
- SQL Server 2014 Database or SQL Azure V12 Database
- Internet Access
- Note: Can also run as an Azure Web Job
- Power BI Reports:
- Power BI Desktop application
- (optional) Office 365 Power BI enabled tenant, if you wish to publish the reports, and make sharing easier
- (optional) Reporting Portal
- .NET Framework 4.6.1
- IIS Web Server
- Note: Can also run as an Azure Web App
(Note: The instructions below require some prior technical knowledge to complete.)
- Download the binary file from the latest project release
- Uncompress the files to a folder
- Configure the Reporting Job (see corresponding sections below for details):
- CSP Tenant settings
- CSP Reporting Database
- (optional) With the provided SQL Script, create a new Reporting database (if you want to have historic information, you will need this. Otherwise you will only be able to export to CSV files)
- (optional) Install the Reporting Portal to a IIS Website. .Net 4.6.1 and ASP .Net MVC 5 required. Configure the Reporting Portal (see corresponding sections below for details):
- CSP Reporting Database
- (optional) Install the Power BI reports to a Power BI enabled Office 365 tenant. Configure the reports:
- CSP CSV files
- CSP Reporting Database
The Reporting Job has several configurable settings in the app.config file. Set the values as appropriate for your tenant.
| Configuration Key | Description |
|---|---|
| CSPTenantName | The default domain of the CSP Tenant with Microsoft. (This is typically an "onmicrosoft.com" domain.) |
| CSPTenantId | The Microsoft Id of the CSP Tenant. This can be retrieved from the Partner Center Portal. |
| AzureADAppId-NativeApplication | The id of the native tenant application registered in the CSP AD directory with permissions to access the Partner Center SDK API (see https://msdn.microsoft.com/en-us/library/partnercenter/mt267552.aspx - section Enable API access; and/or https://msdn.microsoft.com/en-us/library/partnercenter/mt634709.aspx - section Configure authentication for Partner Center APIs). The application should have explicit permissions for Azure Active Directory "Read directory data", and Pre-Consent should also be enabled (see powershell script in the "Scripts" directory). |
| CSPAdminUsername | Partner Center service account username. A Helpdesk agent account type is sufficient. |
| CSPAdminPassword | Partner Center service account password. The previous account password. |
| CSPCountryTwoLetterCode | The country two letter code where the CSP tenant is registered. Example: US or UK or PT |
The Reporting Job and the Reporting Portal both need a database connection to work. If you choose to not use a database, you can still use the job to export the reporting information to CSV files.
| Configuration Key | Description |
|---|---|
| CSPDatabaseModelEntities connection string | The connection string to the Reporting Database. |
| Logging connection string | The connection string to the Reporting Database. Typically, it refers to the same database as above. |
The Reporting Job is a console application that accepts a single parameter. That parameter can be:
| Parameter | Description |
|---|---|
| activationCSV | Extracts and generates the Activation Opportunities report to a CSV file. |
| customerUsageCSV | Extracts and generates the Usage Opportunities report to a CSV file. |
| activationBD | Extracts and generates the Activation Opportunities report to the Reporting database. |
The generated CSV files have a header line, all fields are separated by the tab (\t) character, and there is no text qualifier surrounding fields.
Note: It can take several hours for the job to process all customers. Also errors can occur while extracting and processing the customer information. In the case of error, the job tries to continue processing the remaining customers. More detailed information can be checked in the logs and on the exported information.
Use a web browser and navigate to the homepage address of the Portal website installation.
You can use the Portal to:
- List campaigns
- Manage campaigns associated customers
- Create a new campaign
- Delete a campaign
Note: To be able to associate customers to campaigns, first you must guarantee that a run of the Reporting Job (with the database parameter) as completed with success. If not, the customer list will be empty or only partially complete.
To work with the Power BI reports you will need to install and use the Power BI Desktop Application, available at: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-get-the-desktop/#to-download-and-install-power-bi-desktop
The Power BI Report files are named:
- CSP_CSV_Reports.pbix - For the CSV generated analysis
- CSP_Database_Reports.pbix - For the Database generated analysis
To configure the report, you must first run the Reporting Job and generate the Activation and the Usage CSV files. Afterwards, follow these steps on Power BI Desktop:
- Open the report file
- On the ribbon, "Home" tab, click "Edit Queries"
| Table | CSV File to choose |
|---|---|
| Customers | Usage |
| Subscriptions | Usage |
| Skus | Activation |
 |
- Repeat the last three steps and update the Subscriptions and Skus tables also
- To finish, on the ribbon, click "Close & Apply"
To configure the report, follow these steps on Power BI Desktop:
- Open the report file
- On the ribbon, "Home" tab, click "Edit Queries"
NOTE: The Power BI reports can be published to an Office 365 tenant with Power BI enabled, for easier sharing within a team.
The opportunities are classified by an Action Type and a Sub-Type detail. The detail is explanatory, indicating the reason for that classification.
The following types of opportunities are identified:
| Action Type | Action Sub Type |
|---|---|
| NO ACTION NEEDED |
|
| ACTION NEEDED |
|
| ACTIVATION OPPORTUNITY |
|
| Scenario not defined | If this occurs to you, please contact us :) |
The usage is analyzed taking into consideration the user's activity in the last 30 days. Currently, only usage referring to the following Office 365 services are supported:
- Exchange Online
- Skype for Business Online
- SharePoint Online
The following types of opportunities are identified:
| Action Type | Action Sub Type |
|---|---|
| NO ACTION NEEDED |
|
| ACTIVATION OPPORTUNITY |
|
| Not supported |
|
| Scenario not defined | If this occurs to you, please contact us :) |
Final disclaimer: These samples are provided freely as a proof of concept as-is, with no support whatsoever.