The data warehouse service component of CloudPortal Services Manager is new to v11. The primary purpose of the data warehouse service is to copy a snapshot of provisioning data from the CloudPortal Services Manager transactional database to the data warehouse, a process which occurs once per day enabling provisioning history to be accumulated for reporting purposes.
The data warehouse service fulfills a number of functions:
- Execution of the scheduled daily transfer of provisioning data to the data warehouse
- Publishing of service-specific configuration files during service import/export
- Providing methods for changing passwords and other connection string details for data transfer connections to the OLM and OLMReporting databases
- Retrieval of usage information (totals for customers or resellers, detailed billing data)
Execution of the Data Transfer process
There are a number of ways to execute the data transfer process to copy transactional provisioning data to the data warehouse for reporting purposes.
Data Warehouse Service methods
The data warehouse service provides the following methods that commence or resume the transfer process:
- ExecuteTransfer – will commence the transfer using the date parameter provided as the batch date, and will wait for the process to complete and return a value to indicate success or failure.
- Resume Transfer – will resume a previous transfer from the command specified by name and using the date parameter provided as the batch date, and will wait for the process to complete returning a value to indicate success or failure.
- BeginTransfer – will commence the transfer using the date parameter provided as the batch date, but will not wait for completion of the process.
A program exists in the [Program Files (x86]\Citrix\Cortex\Data Warehouse\Data Transfer folder named DataTransfer.exe that will initiate the transfer process when run by invoking the ExecuteTransfer method. DataTransfer.exe is executed by a scheduled task every day at 4am.
It is possible to provide a command-line parameter named -date that accepts a date and time in yyyy-MM-dd-HH-mm-ss format to populate a batch for a date other than today’s date. If the -date parameter is not provided the batch date will be the current date and time if later than 7am, or the previous day at 23:59:59 if earlier than 7am.
CloudPortal Services Manager web application
From within the CloudPortal Services Manager web application it is possible to execute the data transfer process by clicking the “Transfer data from views to data warehouse” link in the sidebar of the Reports->Configuration->Data Warehouse page. Service Schema Administrator permissions are required to access this page.
The sidebar will display a status image to indicate success or failure of the process. It is possible to click through from the hyperlinks on the start and end times in this list to view the data warehouse service log file for a completed batch, which may prompt for credentials to view the log file.
During service import the CloudPortal Services Manager web application or installer will publish service-specific configuration files to the data warehouse service. These files will be saved as XML to disk within the config subfolder of the data warehouse service program files folder, into one of the following locations:
- config\datatransfer – this folder contains service-specific configuration files that define how to transfer data from the OLM to OLMReporting databases.
- config\datawarehouse – this folder contains service-specific configuration files that define the hierarchy of types used by the service to control the aggregation process.
During service export, the same files will be retrieved from the data warehouse service by the CloudPortal Services Manager web application for inclusion in the exported service package.
To change the password for the connection to the OLM or OLMReporting databases, the SetConnection method can be used which will automatically encrypt the password and store it into the data warehouse service primary configuration file.
The SetConnection method accepts the following parameters:
- connectionName – the value ‘Source’ will be used for the OLM database, and the value ‘Destination’ for the OLMReporting database.
- server – the server name and port, and optionally the SQL Server instance name.
- database – the database name.
- username – the SQL Server username that the data warehouse service should use to connect to the database. If blank the data warehouse service will attempt to connect using Windows Integrated Security using the application pool identity.
- password – the unencrypted password that the data warehouse service should use to connect to the database. If blank the data warehouse service will attempt to connect using Windows Integrated Security using the application pool identity.
Retrieval of Usage Data
To retrieve aggregate usage for a customer or reseller, the GetCustomerUsage or GetServiceUsage methods can be used. GetCustomerUsage will retrieve usage data for all services for a customer, while GetServiceUsage will retrieve the same data but only for one service.
Retrieval of Billing Data
To retrieve a detailed listing of billable items as displayed in the Customer Detail or Reseller Detail billing reports, the GetBillingUsage method may be used.
Accessing the Service
The data warehouse service is implemented as a WCF service hosted in IIS. To execute methods on the data warehouse service without programmatically creating a WCF client will involve the use of a tool such as WCFStorm which is downloadable from the following location:
<a title="http://www.wcfstorm.com/wcf/wcfstorm-lite.aspx" href="http://www.wcfstorm.com/wcf/wcfstorm-lite.aspx" rel="nofollow">http://www.wcfstorm.com/wcf/wcfstorm-lite.aspx</a>
The data warehouse service is installed into the CortexServices site in IIS. The default URL will use port 8095 as shown below:
HTTP is enabled by default at installation time. To enable HTTPS/SSL involves editing the web.config to uncomment sections that define an additional WCF binding and endpoint, in addition to configuring IIS to add an HTTPS binding and editing the Data Warehouse Server Connection in CloudPortal Services Manager. These sections are identified in the web.config by comments such as the following:
<!--Uncomment the following section to enable SSL-->
Windows Integrated Security is used as the Authentication method for the data warehouse service, with access being restricted to Domain Admins or a specified user as defined in the web.config ValidUserName appSetting.
<add key="ValidUserName" value="DOMAIN\USERNAME"/>
When errors are encountered during execution of methods on the data warehouse service, they may be written to a number of different locations as listed below. These locations may be consulted while debugging to find detailed error messages.
- Windows Event Viewer
- [Program Files (x86]\Citrix\Cortex\Data Warehouse\Data Warehouse Service\log folder
- Batch_Log table within the OLMReporting database
Additionally, WCF tracing may be enabled if required in the web.config file to provide information regarding connectivity to the WCF service hosted in IIS. During the normal course of diagnosing data warehouse service errors it will not be necessary to enable WCF tracing.