Home > Cloud Cruiser 4 > Working with standard reports > Data analysis in Microsoft Excel

Data analysis in Microsoft Excel

The Cloud Cruiser database contains views that allow you to more easily analyze loaded charges from within Microsoft Excel with an Office Data Connection. After relating the ReportCharge view to the ReportAccounts view by their AccountUID columns in Excel, you are able to create a PivotTable that shows charges for a desired level of your account structure.

This functionality requires Microsoft Excel 2010 or later. This topic covers connecting both Excel 2010 and Excel 2013 to a SQL Server database, but you can also connect Excel if your installation of Cloud Cruiser uses an Oracle database.

Connecting Excel

This section explains how to connect Microsoft Excel to your Cloud Cruiser database with a join of two views that enable you to analyze charge data.

To connect Excel 2010
To connect Excel 2013
  1. Launch Excel and create a new blank workbook.
  2. Click Data > Get External Data > From Other Sources > From SQL Server.
    The Data Connection Wizard opens.
  3. In Server name, enter the network computer name of the computer hosting the Cloud Cruiser database.
  4. Select Use the following User Name and Password and enter the user name and password of the Cloud Cruiser database account, then click Next.
    This is the Application User and Password that you entered on the Database Settings page of the Cloud Crusier installer. The default Application User is ccadmin. If you do not know these credentials, ask your database administrator to provide them to you or to create another account with read access to the Cloud Cruiser database.
  5. Select the ccdb database.
  6. Select the Enable selection of multiple tables option, then select the following views from the list:
    • ReportCharge
    • ReportAccounts

      The ReportAccounts view contains data about customer accounts based on the full account structure. If you want to view data in Excel based on an alternate account view you have created, instead select the ReportAccounts<view_ID> database view where <view_ID> is the numeric account structure ID.
  7. Click Finish.
  8. In the Import Data dialog box, choose PivotTable Report and click OK.
    It might take some time for Excel to load the data from this query.
  9. Click Analyze > Calculations > Relationships to define how to join the summary data to hierarchical account IDs and descriptions.
  10. In the Manage Relationships dialog box, click New .
  11. In the Create Relationship dialog box, make the following selections:
    • TableReportCharge
    • Column (Foreign)AccountUID​
    • Related TableReportAccounts
    • Related Column (Primary)AccountUID

      This joins the two views, creating a combined record of values from each wherever the AccountUID in one view matches an AccountUID in the other.
  12. Click OK and then close the Manage Relationships dialog box.

You now have a PivotTable based on a join of charge summaries and account information. For instructions for using this table to analyze your data, see Analyzing charge data.

Analyzing charge data

The procedure in Connecting Excel joins data about usage charges to data about customer accounts based on the full account structure. This enables you to aggregate charge data at any level in the hierarchy. For example, if you want to analyze total charges by day aggregated to the highest level in the structure, you can perform the following procedure.

To view total charges by day rolled up to the top level of your account structure

  1. In Excel, create a PivotTable based on your external data source that joins charge summaries and account information.
    If you have just completed one of the previous Excel connection procedures then you already have this PivotTable and can skip to step 2. If you have previously performed one of these procedures but no longer have the PivotTable in your Excel workbook, perform the following steps:
    Click Insert > Tables > PivotTable, select Use an external data source and click Choose Connection, then select the data source you created. In Excel 2013, this is <DBserverName> ccdb Multiple Tables and in Excel 2010, it is the name of the query you saved from Microsoft Query. Then click Open, and in the Create PivotTable dialog box click OK.
  2. In the PivotTable Fields pane to the right, find the field from the ReportAccounts view that holds the description for customers at the top level of your account structure and drag it to the ROWS box below.
    For each level in your default account structure, there is a <levelName> field and a <levelName>Desc field. The former holds the part of a customer’s account ID at that level and the latter holds the corresponding description. For example, if the top level of your account structure is “ServiceProvider,” drag the ServiceProviderDesc field to the ROWS box. This will give you one row per customer at the top level of your hierarchy, with the description displayed. You could instead display the partial account ID by dragging ServiceProvider.
  3. Find the AccountingPeriod field from the ReportCharge view and drag it to the COLUMNS box.
  4. Drag the TotalCharge field to the VALUES box.

Your worksheet now shows all charge data in the system for every top-level customer (in this example, ServiceProvider) broken down by each accounting period loaded. You can further break down this charge data by adding other fields, such as ServiceGroup, ServiceDescription, and even lower levels of the account structure, to the ROWS and COLUMNS boxes. See the documentation for the PivotTable feature of your version of Microsoft Excel for information about its capabilities and how to use them.

Analyzing detailed usage data

Similar to the procedure Analyzing charge data, you can also analyze detailed usage data. This data, accessed through your schema views, includes resource units and a set of identifiers to discover additional related information regarding usage. In your database, schema views are named dbo.schema_<type>, where <type> is the type of data available in that schema. For example, storage data is available in the dbo.schema_Storage view.

To analyze usage data

  1. Connect Excel to the appropriate schema view and the ReportAccounts view using the procedure in Connecting Excel with the following changes:
    • Excel 2013:
      • In step 6 when selecting views in the Data Connection Wizard, instead of the ReportCharge view, select the appropriate schema view, such as schema_Storage.
      • In step 11 when creating the relationship, substitute the appropriate schema view for ReportCharge as the Table.
    • Excel 2010:
      • In step 10 when selecting columns in the Query Wizard, instead of the ReportCharge view, select and move the appropriate schema view.
      • In step 12 when relating two columns, relate the AccountUID column of the appropriate schema view to the AccountUID column of ReportAccounts.
  2. Create and populate a PivotTable similar to the procedure in Analyzing charge data. Make the following changes and additions:
    • For the VALUES box, add columns from your schema view. For example, from the schema_AWS view you could add the PassthroughCost column.
    • Choose the fields you want to use in both the ROWS and COLUMNS boxes.

See the documentation for the PivotTable feature of your version of Microsoft Excel for information about its capabilities and how to use them.

Last modified

Tags

Classifications

This page has no classifications.
© Copyright 2018 Hewlett Packard Enterprise Development LP