Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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


Image ModifiedImage Modified

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.

...

Choose the instructions for your release of Excel:

 


Expand
titleUse 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:

 



Expand
titleUse 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


      Info
      titleNote


      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

...

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),
artefacts
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),
artefacts
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
 


Info

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

Image Modified

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

Image Modified

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

An error like in Query Editor

Image Modified

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