Page tree
Skip to end of metadata
Go to start of metadata

On this page:

Target audience: Any Application owner, Manager, VP, Director, Key User who is interested in exploiting CAST AIP results in an Excel file and building their own monitoring reports, pivot tables, charts, tables etc.

Summary: This document provides an example of an Excel file that can be manipulated after installation of Power Query.

Click to enlarge the example output in Excel

Prerequisites

(tick)

Access to the CAST RestAPI 8.1 (or above) from the machine on which you are using Excel.

(tick)Excel 2016, Excel 2013 or Excel 2010 installed.
(tick)Power Query downloaded and installed (for Excel 2010 and 2013) - see https://www.microsoft.com/en-gb/download/details.aspx?id=39379. Power Query is installed by default with Excel 2016. A lot of information about Power Query is available on the internet.

Principle

The aim is to be able to load results from the RestAPI in Power Query modules and then to manipulate data in Excel as usual using charts, tables, pivot tables and so on... A simple data refresh will update the data everywhere in your Excel file. Power Query enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Power Query also provides you with the ability to search for public data from sources such as Wikipedia.

Manipulating Power Query requires some practice, that's why CAST has provided a "starter" Excel file that will provides sample queries for your own CAST RestAPI. Based on this file, you can adapt it for your own organization's context.

It is also useful to be able to manipulate the CAST RestAPI to define URIs for your own needs. To do so, please refer to our CAST RestAPI documentation.

Excel file samples

Please find attached an Excel file sample: PowerQuery-Example_8.1.xlsx

The file has 3 visible worksheets.

  • a Config worksheet to configure the URL to a live RestAPI (only version 8.1 or above) with some explanation about how to authenticate
  • an Overview worksheet presenting some charts and pivot tables based on Power Query queries. These are just examples that can be modified and adapted. This worksheet is based on a hidden worksheet called Overview Data containing all Power Querie results.
  • a Monitoring worksheet presenting some column charts. These charts are implemented as samples. This worksheet is based on two hidden worksheets (Monitoring Data and Pivots Monitoring).
  • The Power Query queries contain 12 queries and 2 functions:
    • One function is able to load a URI from the RestAPI URL in CSV format and to import it as table in Excel
    • One function is able to take any value in the worksheet. 
    • All queries manipulate tables like change type, merge columns, sort data, change value etc.

All the content in this file can be freely updated and the aim is really to show that many actions can be done to design any table you require for your BI solution.

How to use the example Excel file

Enter the RestAPI url

Click the Config worksheet and enter your RestAPI URL respecting the following format:

http://<hostname>:<port>/<war_name>/rest/

Authentication

Once you enter the RestAPI url, you need to authenticate before updating the data for existing queries. This step can be a little bit challenging depending on the Excel version you are using. You need to first load first the RestAPI to be sure that the URL will be identified by Excel as a potential Data source. You can connect to Data Source Properties to see if your URL has been identified: if not you will have to load a query in the Query Editor.

The first important step is to manage the authentication to connect to the RestAPI URL. Depending on your version of Excel, the connection could be different.

Choose the instructions for your release of Excel:


 Use with Excel 2010 and 2013
  • ENTER your RestAPI url: When the file is opened, in the Config tab, enter the URL of your RestAPI respecting the following format: http://<hostname>:<port>/<war_name>/rest/

 

  • AUTHENTICATE . This step is usually the challenging part of using Power Query.

    1. Go into Power Query Menu and click on Show Pane


    2. In the Workbook Queries, click on the Refresh icon for the first query LoadAPI only


    3. Select Basic in the left menu of pop up and enter credentials for your RestAPI url and then click on the Save button


      All queries should be loaded correctly in the Workbook Queries
  • REFRESH your DATA: Finally go in to the Overview Worksheet and go in to the Data menu and select Refresh All. Sometimes you may need to repeat the "Refresh All" to be sure all data are updated in charts and tables. Each Ttme you need to update your data after new snapshots, you just have to do a refresh all:


 Use with Excel 2016
  • ENTER your RestAPI url: When the file is opened, in the Config tab, enter the URL of your RestAPI respecting the following format: http://<hostname>:<port>/<war_name>/rest/

 

  • AUTHENTICATE

    1. In Data tab, select Show Queries


    2. In the Workbook Queries, click on the Refresh icon for the first LoadAPI query only


      Note


      If you have an error pop up message like

      you need to go in the Data Tab, select New Query > Data Source Settings... > Global Permissions. Select your url and then Edit Permissions and then Edit Credentials

    3. Select Basic in the left menu of pop up and enter credentials for your RestAPI url and then click on the Save button
    4. Select Privacy level as Public and click on Done, then Close the last pop up
  • REFRESH your DATA: Finally go in to the Overview Worksheet and go in to the Data menu and select Refresh All. Sometimes you may need to repeat the "Refresh All" to be sure all data are updated in charts and tables. Each Ttme you need to update your data after new snapshots, you just have to do a refresh all

Data Refresh

When the RestAPI URL authentication is validated, you can then Refresh all data from your worksheet doing a Refresh All in the Data tab:

Power Query Queries

if you launch the Editor of queries, you will find a list of predefined queries based on RestAPI Uris. These RestAPI Uris are based on a hidden worksheet called RESTAPI Uris containing samples of pre-configured REST API queries.  All these can be totally adapted.

Query NameCommentRestAPI Uri
Health Factors

Query to get Health Factors scores for last snapshot of each application

AAD/results?quality-indicators=(business-criteria)&applications=($all)&snapshots=(-1)&format=(snapshotsAsRows)
SizingQuery to get Locs (10151), artifacts (10152), critical violations (67011), technical debt (68001) for last snapshot of each applicationAAD/results?sizing-measures=(10151,10152,67011,68001)&snapshots=(-1)&applications=($all)&format=(snapshotsAsRows)
Technology sizingQuery to get Locs (10151), artifacts (10152), critical violations (67011) for last snapshot of each technologies of each applicationAAD/results?sizing-measures=(10151,10152,67011)&snapshots=(-1)&technologies=($all)&format=(snapshotsAsRows)
Top Critical RulesQuery to get list of critical rules and non critical rules for Total Quality Index (TQI) for last snapshot of all applications. As Results we get scores, total violations and critical violations, total checks, added/removed violations and added/removed critical violationsAAD/results?quality-indicators=(cc:60017,nc:60017)&snapshots=(-1)&applications=($all)&select=(violationRatio,evolutionSummary)&format=(snapshotsAsRows)
Function PointsQuery to get list of function point rules for last snapshot of all applicationsAAD/results?sizing-measures=(functional-weight-measures)&snapshots=(-1)&applications=($all)&format=(snapshotsAsRows)
Evolution OverviewQuery to get TQI (60017), Locs (10151), critical violations (67011), technical debt (68001), added technical debt (68901), removed technical debt (68902) for all snapshots of all applications. As results we get scores or count, total violations and critical violations, total checks, added/removed violations and added/removed critical violationsAAD/results?quality-indicators=(60017)&sizing-measures=(10151,67011,68001,68901,68902,)&applications=($all)&snapshots=($all)&format=(snapshotsAsRows)&select=(violationRatio,evolutionSummary)
LocsGet Lines of code for last snapshot of all applicationsAAD/results?sizing-measures=(10151)&applications=($all)&snapshots=(-1)&format=(snapshotsAsRows)
CriticalViolationsGet critical violations for last snapshot of all applicationsAAD/results?sizing-measures=(67011)&applications=($all)&snapshots=(-1)&format=(snapshotsAsRows)
CV per LocMerge Lines of code (from Locs query) and critical violations (from CriticalViolations query) for last snapshot of all applications
Nb AppsGet number of applications (select distinct number of applications based on Health Factor query)
GetconnectionFunction that connect to the RestAPI url and load a csv file based on a REST uri (parameter of the function)
GevValueFunction that load the value of a cell of any worksheet

For all these queries, some Power Query manipulations are activated and can be observed in the Query Editor. All these queries based on RestAPI uris are samples that should help users to query any data from the RestAPI depending on required BI reports.

Potential Errors, what to do?

ErrorWhat to do

Security Warning

Click on Enable Content button

Any error popup

Please validate that you are using a supported version of RestAPI (AIP 8.1 or above)

A popup with 470 error is an authentication error


Go into Data Source settings, Select Global Permissions, Select Edit Permissions on your url, Select Edit for Credentials

An error like in Query Editor

470 is an Authenticate error, You need to go in the Data Source Connection Properties to authenticate on the RestAPI url that you defined in the Config tab
  • No labels