00:00:00 Introduction

Good afternoon everyone, and thanks for joining us. Today we are going to talk about Power BI and ActivityInfo. My colleague Faiza is here with me checking the chat and questions to help me out. If you have a question, please use the Q&A feature as it makes it easier to track. This session is being recorded and will be posted on the website shortly.

For those who don't know me, my name is Alex Bertram. I am the Technical Director at BeDataDriven. I spend about half my time on the development team and the other half working with our users.

My presentation today has four main topics. We will start with the basics of how to connect ActivityInfo to Power BI and make some basic visualizations. Then, we will spend time looking at the ActivityInfo Query API, which is the tool that allows us to get data out of ActivityInfo; this will help you understand how to work with subforms. Next, we will look at Power Query inside Microsoft Power BI, which is a neat tool for data management and wrangling. Finally, I will reserve about 20 minutes at the end for Q&A.

00:02:40 Connecting ActivityInfo to Power BI

We are going to start in ActivityInfo. I have prepared a dummy dataset regarding school rehabilitation and clinic construction in Somalia. I have made the forms public so you can open the database and follow along. The dataset tracks basic information about schools, the status of rehabilitation, and for those in progress, whether they are on track or off track. Our goal is to build a dashboard to help program management monitor this program. The purpose of a dashboard is to trigger action, helping people know when to act, such as speeding up or addressing issues.

We will focus on the technical details of connecting ActivityInfo with Power BI using ActivityInfo's API (Application Programming Interface). We have made it easy to access the API from the Table View. If you go to your form, click on the "Export" button, and select "Export via the API." For today, we will use the option "Query all fields as JSON." This provides a URL that you can use.

In Power BI, click on "Get Data" and select "Web." Paste the URL you generated from ActivityInfo. When asked for credentials, select "Basic." I strongly encourage you not to use your actual password. Instead, generate an API token. Go to your Profile Settings in ActivityInfo, select API Tokens, and add a new token. Give it a name (e.g., "Power BI Windows") and choose "Read-only." Copy this token and paste it into the password field in Power BI. This limits security risks compared to using your main password.

00:12:55 Creating basic visualizations

Once the data is loaded, Power BI presents the fields on the right-hand side. We will start by creating a chart of the school status by region. Geography is often a good dimension for dashboards because teams are often managed by region. By dragging "Region" to the axis and "Status" to the legend, we can see which regions are on track and which have not started. For example, we might see that in one region, 12 schools have not started, which should trigger management action.

Next, we can add a table to see the raw data, allowing us to drill down into problems. We can drag fields like the name of the school, rehabilitation status, and assessed status into the table. This allows a manager to click on a specific region in the chart and see the specific schools involved in the table.

The data in Power BI is live connected to ActivityInfo. If I update a record in ActivityInfo—for example, changing a school's status from "Not Started" to "In Progress"—and then click the "Refresh" button in Power BI, the dashboard will update to reflect that change immediately.

00:24:30 Understanding the Query API and subforms

A common question is how to handle subforms, such as monthly reports inside a general folder. To demonstrate this, I will use a monthly reporting database template. If you export the parent form via the API, you will only get the parent records, not the subform data. This is because Power BI expects a flat table, and subforms represent a nested table structure.

To access subform data, you must navigate down to the subform level in ActivityInfo and export that specific form via the API. When you query the subform, ActivityInfo automatically includes related parent information, such as the partner organization's name.

When exporting via API, you have the choice to "Query all fields" or "Query selected fields." If you use "Query all fields," we try to give you sensible field names. However, it is often better to add "Codes" to your fields in the Form Settings in ActivityInfo (e.g., changing a long question to a code like "training"). These codes will then appear as the column headers in Power BI, making the data much easier to work with.

You can also customize the query URL to only pull specific columns. This acts like a SQL statement. The easiest way to generate this is to use the "Select Columns" feature in the ActivityInfo Table View to organize the view exactly how you want it, and then choose "Query selected fields as JSON" from the export menu.

00:42:00 Using Power Query to combine forms

We will now look at Power Query, which allows for more advanced data management. Suppose we want to report on the total beneficiaries of our program, combining student populations from the school rehabilitation form with catchment populations from a clinics form. These are two separate forms in ActivityInfo.

In Power BI, next to your query, click "Edit Query" to open the Power Query Editor. First, for the Schools query, we want to combine boys, girls, and teachers into a single "Beneficiaries" column. We click "Add Column" then "Custom Column" and create a formula summing these fields.

Next, we bring in the Clinics data by creating a "New Source" from the Web and pasting the API URL for the clinics form. Note that Power BI caches your credentials, so it may not ask for your API token again. If you need to update credentials later, you must go to "Data Source Settings" and edit permissions.

To combine the datasets, we need to ensure the columns match. In the Clinics query, I rename "Catchment Population" to "Beneficiaries" so it matches the custom column I created in the Schools query. Then, I use the "Append Queries as New" function to combine the Schools and Clinics tables into a single dataset. This results in a unified table where I can analyze total beneficiaries across both activities.

We can also add a "Target" column in Power Query to visualize our progress against a goal. Sometimes Power BI does not automatically detect data types, so you may need to right-click a column header and select "Change Type" to ensure numbers are treated as "Whole Numbers" rather than text. This allows you to use them in calculations and visualizations, such as a gauge chart showing total beneficiaries against the target.

01:01:16 Q&A Session

Question: How do you collapse 200 forms into one, like in the Jordan Humanitarian Response database? Answer: Unfortunately, there is no easy way to melt that all together into one table through the API directly yet. You would have to query each form individually in Power BI, which is quite a bit of work.

Question: How can we run cross-tabulations in Power BI? Answer: You can use the Matrix visualization. I will demonstrate using an IDP assessment database. By dragging different attributes to rows and columns (e.g., Family Representative vs. Prefecture), you can create a cross-tab view similar to what you might do in SPSS or Excel.

Question: Does ActivityInfo provide a staging area for ETL (Extract, Transform, Load) before visualization? Answer: ActivityInfo is a full database solution that handles the ETL cycle. You can define structures, import data, validate it, and create reports within the platform. However, you can also connect it to Power BI for the visualization stage if you prefer.

Question: Can you show the connection steps again? Answer: To connect, get the API URL from the Export menu in ActivityInfo. In Power BI, select "Get Data" -> "Web" and paste the URL. For credentials, use "Basic" authentication. Use your email as the username and a generated API Token (not your password) as the password. If you need to update credentials later because they are cached, go to "Transform Data" -> "Data Source Settings" -> "Global Permissions" -> "Edit Permissions" to update the token.

Thank you all for joining. Please fill out the survey to help us improve future sessions. The recording will be available on our website shortly.