Looker Studio Reports

Introduction

Envoy utilises multiple approaches to reporting and data visualisation depending on the specific requirements of each use case.

Reporting Landscape

Looker Studio Reports: The primary method for reporting in Envoy is through Google Looker Studio. This approach was adopted due to cost efficiency - Looker Studio provides comprehensive reporting componentry including charts, tables, interactivity, and more out-of-the-box, eliminating the need to build these features bespoke within the application.

In-App Reports and Visualisations: Some reports and data visualisations are built directly within the Envoy application. These are used when deeply integrated functionality is required, such as: - Real-time alerts and notifications - Exception handling and monitoring - Features requiring deep system integration with Envoy's core functionality

When to Use Each Approach

Use Looker Studio when: - You need cost-efficient, powerful data visualisation and reporting - Standard reporting components (charts, tables, filters) meet your needs - The report can function independently with SQL query access to the database - User interaction is limited to viewing and filtering data

Use In-App Reports when: - Deep integration with Envoy's functionality is essential - Real-time notifications or alerts are required - The feature needs to trigger actions within Envoy - Complex user interactions beyond data viewing are needed

Architecture Overview

Infrastructure

Envoy is built on a Laravel backend with a MySQL database. The application is hosted on cloud infrastructure, with the database itself running on AWS Relational Database Service (RDS).

Database Access for Looker Studio

To enable Looker Studio reporting, a dedicated read-only database user has been configured with the following characteristics: - Read-only permissions to prevent any data modification - Whitelisted IP addresses including Looker Studio's IP ranges - Used exclusively for creating new data sources in Looker Studio

This setup ensures secure, controlled access to the database while maintaining data integrity.

Important Caveats

Out-of-System Nature of Looker Studio Reports

Looker Studio reports are essentially out-of-system. This means: - They visualise data through SQL queries executed against the database - They do not have access to Envoy application functionality such as: - Notifications - Deep system integration - Business logic and validation rules - Authentication and authorization beyond data filtering

The only commonality between Looker Studio reports and the Envoy application is that they both query the same MySQL database. Integration is primarily achieved through intelligently embedding these reports within the application interface, providing a seamless user experience despite the technical separation.

Report Documentation

Each Looker Studio report includes a hidden page called "How this report works". This page is only visible when viewing the report directly in Looker Studio (not through embedded views in Envoy). This documentation page contains: - Requirements for the report - How the report functions - Logic and business rules implemented - Important considerations and caveats

This hidden page should be your starting point when investigating issues, bugs, or planning changes to a report.

Understanding Data Sources

Accessing Data Sources

Each report may have one or multiple data sources, or blends of multiple sources. To view and manage these: 1. Open the report in Looker Studio 2. Navigate to Resource menu 3. Select Manage added data sources

This provides access to: - All fields produced by the underlying SQL query - The SQL query itself (with inline comments where helpful) - Field types and aggregation settings

Working with SQL Queries

The SQL queries powering data sources often include comments explaining the logic and principles behind the implementation. When working with these queries:

Best Practices: - Review existing comments within the query - Use database tools (e.g., TablePlus) to test query modifications - Leverage Large Language Models (LLMs) to help understand complex queries

Caution: Do not allow LLMs to alter SQL queries unless you thoroughly understand: - The intended function of the report - The business rules being implemented - How changes might affect other parts of the report - Potential unintended consequences

Changes to SQL queries can have cascading effects across multiple reports and embedded views. Always ensure comprehensive understanding before making modifications.

Looker Studio

Connecting a database into Looker Studio

1. A database user needs to be created in the database service on the server (speak to one of the snr devs for help). A read-only user will be required for this.
2. We can now use the database user to connect to the database in Looker Studio.
3. On Looker Studio home page select the Create button
4. We are going to create a new data source, which is really a SQL query that we can use to create a report.
5. You then choose the connector, in our case likely mysql.
6. You then need to enter the database connection details. We usually use the Basic Tab, where we can specify the database. You can also use the JDBC tab in which case you should prefix the URL with jdbc:mysql://. If using the JDBC tab you will need to specify the database within your query. Essentially you will be connecting to the Database Service rather than the specific database. This has advantages where you can query and join data across multiple databases in the same service.

1. On the right-hand side you can see we are putting in our query to return the data we want to report on. It is recommended to connect the database to your local DB tool (e.g. TablePlus) and build your query there. Looker Studio has terrible query-building functionality, in that it doesn't give helpful error messages and the syntax highlighting is poor.
2. Once the query is ready, click connect/reconnect to be taken to the next page showing your fields. Here is what you can expect to see:

1. There is some important functionality on this screen highlighted in the image above, however, we can create a report using the create report button. This will take you to the report builder which is where we will continue.
2. I will not recreate all the material that exists out there about how to build effective reports and standard functionality, rather the intention of this document is to highlight the key functionality that we use and the approach we take to building reports.

Key Functionality and Tips

Filters

This is an extremely important one for our team for several reasons. And off the bat, it is important to note we are not talking about dropdown filters for interactivity on the report, but rather Report Level, Page Level and Element Level filters. Here's why it's important:

1. Rather than creating a new data source for every report, we can create a single data source and use report level filters to filter the data. This is a huge time saver and also means that if we need to make a change to the data source, we only need to do it once. Very important for maintainability and following DRY principles.
2. We can use page level filters to filter the data on a page. This can be useful if we want page 1 to be filtered for males for instance and page two to be filtered for females as a random example.
3. We can use element level filters to filter the data on a specific element, an element being a table, chart etc.
4. NB!!! When looking at a report and the data is not making sense to you, given how the underlying SQL is written, it is likely that there is a filter applied somewhere that you are not aware of. So always check the filters.
5. The image below shows a report-level filter. In this example, there is a single data source for SB's and we are filtering at the report level to get the type we want.

Large Queries and Report Performance

1. If the report is pulling a large amount of data it is not uncommon for elements on the page to essentially timeout (time of writing July 2023). From experience, if you have a query which is querying multiple DBs this severely impacts the performance of the data source and report.
2. One solution if this can't be avoided is to utilise the extract data source functionality which comes out of the box in Looker Studio and is very easy to implement. An important caveat is the maximum refresh frequency is 24 hours. So if you need to refresh the data more frequently than this, this is not a viable solution.
3. All you do is follow the process to create a new data source, but the connector type you are interested in will be Extract Data. You then specify the data source you want to extract from (an existing data source) and the frequency of the refresh.
4. Of course then you need to connect your report to the new extract data source.

Updating a Data Source

When making an update to an existing data source, it is important to consider everywhere it is used so that a breaking change is not unintentionally introduced. Thankfully there is an easy way to check this, see below:

Publishing

When creating a new report we can turn on publishing which is akin to release management. So we can work on a live report without interrupting the users. Then once the changes are ready we can publish the report. We generally always use this functionality.

Pagination of Reports

1. Reports can have multiple pages which is sometimes the best way to handle a large amount of data or a complex report with distinct sections. With additional pages come additional elements to consider. For instance, with multiple pages, we can have the same filters across all pages by making the filters report level rather than page level.

2. A second important point on pages is that you don't have to stick to the standard size, simply go to the page settings and change the size. This is useful when you have a lot of data to display and we commonly extend the length of the page to accommodate this.

Embedding and Scheduled Email Delivery

Sharing of the report. On the Engauge solution we are using the embed functionality and the scheduled delivery functionality. Both are straightforward to use. For embedding, we simply click the button and get the embed code. For scheduled delivery it is similarly straightforward, however, an important note is that the report is sent as a PDF and is not interactive. i.e. tables that overflow by hundreds of rows will not be scrollable and only what's visible will be visible. The suggestion for scheduled delivery is to include a link to the report in the email body and secondly to maximise the use of charts to display the data in a concise and meaningful way.

Calculated Fields

It was mentioned above that calculated fields exist as functionality within Looker Studio, both at the Data Source level and the Report level. I think from a maintainability perspective it is better to add to the Data Source level since it's more evident to another person where the field is coming from. However, there are cases where this is not possible, such as when you are utilising a calculated field on a blended data source.

Here is a list of all the functions available to you when creating a calculated field: https://support.google.com/looker-studio/table/6379764?hl=en

Dynamically Filtered Embedded Reports

We determined a way to dynamically filter an embedded report. I.e. the report will show data specific to the user who is viewing the report. This is incredibly powerful and has been utilised in Engauge to provide entrepreneurs with progress reports/transcripts specific to them. How have we achieved this? 1. We add the appropriate filter to the report. In this instance, we are filtering on the foreign_user_id field. This filter is then hidden from the user, so they cannot utilise it and see other users' data. 2. Go to report settings and turn on Enable viewer filters in report link. This ensures the filter is included in the URL when the report is embedded. 3. We apply the filter with a single user selected and see the URL change and where the dynamic filter is being included in the URL as a parameter. Here is the example, you can see entrepreneur.foreign_uid in the URL, this is the dynamic variable. --- https://lookerstudio.google.com/embed/reporting/9e226c5a-ceb0-40b1-bda1-9cbbb1cd23ce/page/p_o60224hr0c?type=0&params={"df5":"include%25EE%2580%25801%25EE%2580%2580IN%25EE%2580%2580${entrepreneur.foreign_uid}"} 4. Then when we embed the report in the app, we simply insert it like above and ensure that the foreign_uid is being passed in as a parameter for each user.

It is important to note there may be a more elegant way of handling this using parameters but we were unable to get this working.

Blended Data Sources

If you need to combine data between two different data sources, which are perhaps querying data across completely separate servers. We can blend these data sources at a report level. This is a very powerful feature and allows us to combine data from different sources and create a single report. In the Engauge solution we are using this functionality to combine data from the app database and the rems database which exists on a separate server at the client's premises.

NB!!! to make the join you of course need a common key. i.e. foreign_uid then you can blend the data sources.

For instance, we could produce a report to determine the relationship between payment of fees (data that exists in rems) and scoring on assignments (data that exists in the app database).