Reporting & Analytics

Role required
To configure Azure Active Directory for initial setup of the Data Connector to be an Azure AD Global Administrator or Application Administrator.

Introduction

Now that Records365 is managing your organization’s content across its lifecycle, from creation & modification of content captured by our Connnectors, through categorization against your File Plan driven by the Rules engine, all the way to Disposal once its retention schedule has expired, we provide powerful tools to manage & report on these processes end-to-end.

Our first iteration of Reporting & Analytics functionality for Records365 is the Reporting & Analytics Dashboard and Data Connector for Microsoft Power BI.

We’ve chosen Power BI as our first supported platform for delivering advanced analytics and reporting from Records365 as we believe it provides a great entry point for not only rich visualization and actionable insights into your content, but also allows you to enhance your reports with over 60 other sources of analytics data.

Let’s get started!

Create your Azure AD Application to access Records365

In order for the PowerBI Data Connector to access Records365 on behalf of a user and return data, we need to create an Azure AD Application to control authentication and access.

  1. Sign in to the Azure portal.
  2. If your account gives you access to more than one, click your account in the top right corner, and set your portal session to the desired Azure AD tenant.
  3. In the left-hand navigation pane, click the Azure Active Directory service, click App Registrations (Preview), and click New Application Registration. The functionality is the same in the App Registrations page, the instructions below will just be slightly different.
  1. When the Register an Application pane appears, enter the Data Connector application registration information and click Create:
    • Name: Records365 Data Connector
    • Supported Account Types: Select “Accounts in this organizational directory only”
    • Redirect URI: Select “Web”, then enter https://oauth.powerbi.com/views/oauthredirect.html
  1. Click Register. You are taken to the application’s main registration page.
    • Make a note of the Application ID value. This will be required later.
  2. Click on the API Permissions menu item:
    • Click Add a permission
    • Under Select an API, click “APIs my organization uses”
    • Search for ‘Records365’.
    • Select Records365 and click Select.
    • Check user_impersonation and offline_access under Delegated Permissions and click Select.
    • Click Done
  1. Click on the Certificates & secrets menu item:
    • Click New client secret
    • Create a description for the secret, for example “Power BI Connector Secret”, then click Add
    • When the Client Secret is displayed, copy it and save it. It will not be displayed again once you leave this page.

Installing the Records365 Reporting & Analytics Dashboard

The Reporting & Analytics Dashboard depends on Power BI functionality which is currently in Preview.

Currently, there is a separate version of the Reporting & Analytics Dashboard for each of our Records365 geographies. You will notice that the link you use to log in to Records365 is preceeded by the geography identifier - USW (US West), UKS (UK South), AUE (Australia East) and CAC (Canada Central). Download the relevant install package here:

Reporting & Analytics for Records365 USW

Reporting & Analytics for Records365 AUE

Reporting & Analytics for Records365 UKS

Reporting & Analytics for Records365 CAC

The install package contains two files: a .PBIX report file for Power BI Desktop, and a .mez Custom Data Connector file. For more information about Power BI Custom Data Connectors, click here.

Extract both files to an easy to access location, and make sure you have Power BI Desktop installed. Power BI Desktop is free, and so is the Records365 Reporting & Analytics Dashboard.

Installing the Custom Data Connector

First, open Power BI Desktop. In the File menu, click Options and Settings then Options. From the left hand menu, select Security, then under Data Extensions, change the selection to “(Not Recommended) Allow any extension to load without validation or warning”. More information about this setting is available in the “Learn more about data extensions” link just below it. RecordPoint is in the process of certifying our Custom Data Connector with Microsoft, so this setting will no longer be required in the future.

Close Power BI Desktop, open a Windows Explorer window, and navigate to your My Documents directory. Create a New Folder named “Power BI Desktop”, and within it, another folder called “Custom Connectors”. Now, copy the .mez file from the install package to this directory.

Re-open Power BI Desktop, and to check that the Custom Connector has successfully been installed, click on Get Data in the ribbon, then More…. Select Online Services from the left menu, then scroll to the bottom. You should see the Records365 connector listed as below:

If the connector is not present, and no error is displayed when starting Power BI Desktop, check that the above settings are correct, or contact support

Use the Records365 Reporting & Analytics Dashboard

Now that the Custom Data Connector is installed, we can access data from Records365 in Power BI! Start by opening the Records365 Reporting & Analytics Dashboard.pbix file from the install package.

You should see the dashboard visualizations display:

The Dashboard is still displaying sample data at this point. To refresh the Dashboard and populate it with live data from your Records365 tenancy, click on the Edit Queries button in the ribbon.

The Dashboard pulls down a lot of data from the Records365 service. While we are in Beta, we are continuing to make performance improvements, but be aware the refresh may take a while.

The Dashboard report file already has sample connection information saved in it. To replace these details with your own, click on the Cog icon at the right of the ‘Connect’ query:

Enter the Client ID and Client Secret that you saved earlier when creating your Azure AD Application.

Authenticating to Records365

The first time you connect to your data, you’ll need to Sign In to Records365. Click on the ‘Edit Credentials’ button in the yellow warning bar, and then log in using your regular Azure AD account that you use to log in to Records365. Once signed in, click on ‘Connect’ to refresh your data.

Once connected, you should see the ‘Connect’ query returning a table of the functions and queries that the Data Connector exposes. These are then used by the other queries stored in the Dashboard report to ingest data from Records365 and populate the visualisations in the dashboard. To load in your data, click on ‘Close & Apply’ from the ribbon:

To get data through the Power BI Data Connector, you must be assigned as a user of Records365. For more information, see Add Users

Leveraging the Dashboard in Power BI Desktop

Detailing all the different features of Power BI that the Reporting & Analytics Dashboard exposes is beyond the scope of this article. There is a series of comprehensive documentation available here which we recommend that you bookmark if you would like to learn more about Power BI.

Some useful things to note are:

  • Most of the visualisations in the Dashboard page filter the remaining items on the page when you click on them. For example, clicking on a slice of the “Uncategorized Records by Content Source” pie chart will filter the counts at the top of the Dashboard as well as some of the other visualisations, like the Daily Records Submitted on the right of the page. Not all the visualisations behave in this way as some of the metrics don’t provide additional insight when used as a filter.
  • When hovering over a data point in a visualisation, like a slice of a pie chart, a point on a line chart, or a bar in a bar chart, a tooltip will appear showing the detailed metrics that this data point or ‘slice’ represents. You can then right-click on the data point in question and select Drillthrough then Record Details Report to be taken to the Record Details page, with the details filtered to just the slice of data you selected. This lets you drill in and work on individual records as you require.
  • The contents of all the visualisations can be exported by hovering over the chart and clicking on the ‘…’ icon at the top right of the chart area, then selecting ‘Export data’.

Editing the Dashboard

While our out-of-the-box Reporting & Analytics Dashboard demonstrates some key insights into your content estate, regardless of which content source it resides in, we know that you understand your data best. The Dashboard is intended as a template, and can be edited as-is to modify the visualisations or data fields being consumed. All the powerful features of Power BI can be used directly against the data imported from the Records365 Data Connector - to get started, here are some pointers:

Build your own analytics dataset!

One of the key reasons Records365 supports Power BI is to empower our customers to gain insight into your content and enable rich, actionable insights.

Going a step further than customizing our Dashboard report, the Records365 Data Connector allows you to combine custom queries that you perform against Records365 with over 60 different data sources supported by Power BI. To get started, click on Get Data from the Power BI ribbon, then find the Records365 Data Connector under ‘Online Services’.

Once logged in, you will see the Query Navigator for Records365:

There are currently 5 queries that the Data Connector exposes: Get Aggregations, Get Connectors, Get Records, Get Loans and Get Records Summarized. The 3 additional queries, Get Machine Learning Categories, Model and Latest Run are related to beta functionality for our Intelligent Classification engine. Expect to see more about this soon!

Get Records

Currently, the Power BI Data Connector can pull down a maximum of 100,000 rows of data from the Records365 service. If you are hitting this limit, we suggest refining your query using the instructions below. We are working to increase this in the future.

Get Records is the key query that the Data Connector performs, and drives the majority of the report data you see exposed in the Dashboard. This query is parameterized, meaning that you can supply filters when pulling your data down from Records365, to help practice good data hygiene and avoid retrieving unnecessary data from the service. The query has documentation showing how to use it - tick the checkbox next to ‘Get Records’, and click ‘Ok’:

There are a few different options for how to use this query. Supplying none of the optional parameters will get all your records from Records365. This could take a long time! To cut down on loading data we don’t need, we can supply a simple query using the first 4 parameters of the query:

The dropdown menus will guide you on the accepted values for each field that makes up your filter. In this example, we will limit our query to Records365 to only return records where the Author field contains the text ‘John’. To perform a more complex search, we can supply a Search Query object as the 5th parameter to the Get Records query. This requires some familiarity with JSON formatting. The easiest way to build the Search Query object for a complex query is to perform an Advanced Search from Records365, and then capture the search query from the browser.

  1. Log into Records365
  2. Click on ‘Advanced Search’ at the top of the page.
  3. Construct your search query by selecting the properties, operators and values you want to search on. Add multiple conditions using ‘AND’ and ‘OR’ logic - see Search in Records365 for further details.
  4. Before clicking the ‘Search’ button, press F12 in your browser to open the Developer Tools window. This will look slightly different depending on which browser you are using. Select the ‘Network’ option - this shows the details of the network requests that are sent from your browser to the Records365 application.
  5. Click the ‘Search’ button. You should see a ‘POST’ to /api/item/searchgrid - there may be some additional requests, ignore these.
  6. You want to find the Request Body for the search. This has the Search Query we are going to use in the Data Connector. Depending on your browser, this will be a little different - here are some further details: Microsoft Edge, Chrome, Firefox.
  7. You should have a JSON object that looks something like this - it will be longer if you have added multiple conditions:

{
    "query": {
        "children": [{
                "children": [],
                "searchTerm": {
                    "operator": "Contains",
                    "fieldName": "Author",
                    "fieldType": "StringType",
                    "fieldValue": "John"
                }
            }
        ],
        "boolOperator": "AND"
    },
    "order": {
        "orders": [{
                "fieldName": "Score",
                "operator": "DESC"
            }
        ]
    },
    "pageIndex": 0,
    "pageSize": 30
}
  1. We need to grab the value of the “query” object. Copy the { } brace symbols and everything in between immediately following “query”: and before ,“order”:
  2. You should end up with the following object:
{
        "children": [{
                "children": [],
                "searchTerm": {
                    "operator": "Contains",
                    "fieldName": "Author",
                    "fieldType": "StringType",
                    "fieldValue": "John"
                }
            }
        ],
        "boolOperator": "AND"
    }
  1. This is what we need to supply as our Advanced Search Query parameter for the Get Records query in Power BI. The example query above is exactly the same as the simple query example we used before, but this is now extensible and more AND/OR conditions can be added to finely target your query.

You can only supply a simple query or an advanced search query, not both at once. The Get Records query will display an error if values are provided for both parameters.

Get Records Summarized

This is a simpler query that returns a summary count of records based on the field you supply. For example, if you supply the field RecordCategory, you’ll see a summary count of records by Record Category:

Get Aggregations

The Get Aggregations query is a pre-filtered version of the Get Records query, which only returns Record Folders and Boxes from Record365.

Get Connectors

Finally, the Get Connectors query is a simple query that lists all the Connectors in your tenancy. We use this to display the connector image filter in the Reporting & Analytics Dashboard.

Extend your Records365 data with other data sources

Now that you’ve seen how to perform a variety of queries against the Records365 Data Connector, you can target exactly the data you are interested in. But what about extending that to build rich analytics across other data sources?

Some example questions you might want to answer with this kind of analytics could be:

  • Which department in your organization creates the most uncategorized content?
  • Which customer accounts in your CRM are associated with a legal hold?

Describing how to write queries and mash up data sources in Power BI is beyond the scope of this documentation, but there is a comprehensive tutorial available here: Shape and Combine Data. With your Records365 data seamlessly exposed in Power BI thanks to the Data Connector queries, get started extending it with more sources and insights!

Finally, now that you’ve created that perfect report, you need to share it with your colleagues! Publish from Power BI Desktop to the Power BI Service to share the finished report within your organization.