This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
2018 Excellence Awards Entry: CIO API Metrics Reporting
Name: Kenny Dsouza
Title: Systems Analyst
Overview of Use Case
Digital Transformation keeps on commanding meeting room talks as organizations progressively understand the organizational and cost efficiencies that digitization can give. For some, application programming interfaces (API's) are a fundamental segment of combining the old and the updated IT stages, capturing huge measures of information and eventually accomplishing their digital transformation strategy. Below use case exhibits how I have utilized Alteryx to extract information from an API platform and build a Tableau dashboard to report the API Metrics to the CIO and senior leadership.
Describe the business challenge or problem you needed to solve
The API strategy in a company is determined by the CIO - the key person to bring about a digital transformation in the organization. To enable self-service IT and the reuse & monetization of existing API’s, my leadership requested a way to measure the adaptability of API's across the enterprise, along with key metrics that measure the quality of the API. We use Apigee as one of our API Gateway tools - it helps in having secure API calls, traffic throttling mechanisms, error handling, and a way to analyze API traffic. However, it is difficult to get a holistic picture of the trends and other metrics around APIs at an enterprise level.
- Develop the ability to view the adaptability/trending of API’s across the enterprise
- Calculate metrics around the number of API’s, developers building API’s, and API products & applications in the organization
- Create a way to view metrics around average response time, error rate and traffic composition on all API’s
Describe your working solution
Apigee is the main source of data for the API metrics where most of the information was extracted. Along with Apigee, I have also integrated the data with the following sources:
Workday - Ownership information, so that the API’s are mapped to the right owner and department in the organization
Service Now - Applications are mapped to the associated API’s
Cloud Foundry – Microservices are mapped to the associated API’s
Apigee gateway has their API’s exposed so that we can consume the information available on their platform using RESTFUL API calls. The data is usually in JSON or XML format. Once we extract the JSON file using the download tool via RESTFUL calls, we need to organize the information into a tabular format so that it would be easier to visualize the output. However, the required data is stored in different components can be accessed using different API calls. For instance, if we have to understand the relationship between the developers and the API products, we would have to extract the developer’s information & API Product information separately and then join this information based on their GUID. Similarly, we needed to make multiple REST API calls to each component we want to analyze and report these key metrics in a visualization tool like Tableau.
Below is a Macro I built, so that we could call these various different components from Apigee and reuse them instead of rebuilding it every time when we need to access the information. The data extracted is huge, which is why we extract only for a particular time range and load the information into the SQL database in an incremental methodology. To set the time range we use the date app interface. The JSON parser helps us to parse the information and convert the data into a table format. The Cross Tab helps in grouping the data and provides headers and values in a systematic format.
Once we have the macro prepared, based on which key metrics need to be reported and analyzed, we run a workflow to extract the input from the source, convert the data from a JSON format to a tabular format, and then join the data from various different components into one big normalized table. Below workflow is one of the components used to extract the information from Apigee.
At the end of the workflow, the data is loaded into a SQL database. The workflow is currently published to the Gallery and is scheduled to run on a daily basis. The data from the SQL database is then published to the tableau server through live connection so that when the database is refreshed, the Tableau reports are refreshed automatically. Below is an example of some of the API metrics dashboard that is reported to the senior leadership as a result of this process.
Describe the benefits you have achieved
Using Alteryx for my ETL process and a Tableau dashboard to display the information has helped me to provide insights into important metrics around digital transformation. Below are few of the many benefits we’ve realized and how it has had a major impact on our organization…
Automation & Time Savings: Since the dashboards are automatically updated and refreshed on a daily basis, it saves us the manual effort of having to extract and process the information from multiple sources. This also frees up time for developers who extract the information and helps them focus on critical projects.
Decision Making & Insights: Since the workflow is integrated with multiple sources, senior leaders can make better decisions and the analyst can provide enriched insights into the data. Leadership can view the metrics on a holistic level and make decisions to help them in digital transformation.
Data Accessibility: These reports are accessible to every employee in the company, so they don’t have to go through the cumbersome experience of requesting access to the platform (Apigee) and then searching for information based on their needs.
Overall, the Alteryx process approximately saved us 6 to 7 hours where developers would query data from Apigee for each API. Currently, the workflow executes for 20 mins and all the data is refreshed and the dashboard is automatically updated.