Skip to main content
Skip table of contents

Power BI Template

A Power BI template is provided to simplify the process of connecting to and using the OData API. The template can be downloaded at the bottom of this page.

After November 22, 2022 please download the Power BI Template named GimmalRecords - Power BI Template v2.pbit.

This template will automatically connect to primary data as well as the related tables.

Prerequisites

  • Access to the Gimmal Records cloud service.

  • The user name and password to a Service Account in the Gimmal Records cloud service.

  • Gimmal Records Power BI template.

  • Knowledge of Power BI Desktop.

Opening the Power BI template

Once downloaded, open the template in Power BI Desktop. You will be prompted with the following window:

Enter the OData URL that is needed, which must include the /odata path at the end of the URL (for example, https://records.gimmal.cloud/odata). After entering the URL, click Load.

You should then be prompted with a new window to supply a set of credentials, like so:

Enter the Service User credentials under the Basic tab (as shown above) and click Connect. The data should load shortly afterward.

Relationships

Relationships have been pre-created to show the proper relationships between tables.

Report Pages

The Power BI template provides four pages, each described in this section.

Dashboard

The Dashboard shows several high-level visuals focused on the current state of records in the system. This dashboard uses the following tables:

  • records

  • legalholds

Additional columns were created in Power Query to aid in creating visuals:

ExpiredDays

How many days have expired since retention has expired on a record, or blank if retention has not expired

FileName

Parsed from PropertyBag, it contains the name of the file

FileSize

Parsed from PropertyBag, it contains the size, in bytes, of the file

Repository

Parsed from PropertyBag, it contains the repository a record exists in

Measures were created to help show some quick visual of records in the system:

DeclaredRecords

Uses IsRecord to count the number of declared records in the system

SumManagedFileSize

Uses FileSize and RecordClassTitle to sum the size of records that are classified to a Record Class

SumUnmanagedFileSize

Uses FileSize and RecordClassTitle to sum the size of records that are not classified to a Record Class

Records

The Records page displays a table of records meeting the criteria of the dropdowns in the header. By default, only manually classified records are displayed, however, you can also choose Obsolete, Superseded, Permanent, and Vital records.

This table could grow very large depending on the values selected. Be cautious of your selections.

Trigger Impact

The Trigger Impact page displays several tables. Moving from left to right, you can select elements of the File Plan and see which records are impacted. By default, the table will now show any records as that list could be extremely large.

An additional Measure was created (TriggerRetentionLifecycle) to prevent records from showing in the table until a File Plan element was selected.

File Plan Exceptions

The File Plan Exceptions page displays several lists showing potential exceptions in the File Plan.

  • Record Classes without Classification Rules

  • Record Classes without Lifecycles

  • Triggers without Retentions

  • Retentions without Lifecycles

Download

Here is the latest version of the Power BI Template:

Gimmal Records OData Power BI Template.pbit

If, for any reason, the previous template is needed, it can be downloaded here:

(OLD) GimmalRecords - Power BI Template (OLD).pbit

Addendum - Including Property Bag Items

There are several extended metadata properties for a Gimmal Records item that may be included in a container referred to as a ‘property bag’. This section of the document provides instructions on how to include values for these property bag items in a PowerBI extraction.

This is advanced functionality and Gimmal recommends that only users with experience in PowerBI attempt this procedure.

This example details how to create a column that will extract the value of a property bag item called “@repo”. Here is a screenshot of how that item appears within the Gimmal Records property bag:

Extracting the "@repo" value requires the creation of a new column in PowerBI. The following example provides the standard code for creating a new Column: 

Repository =

VAR PropertyBag = records[PropertyBag]

VAR PropertyName = "@repo"

VAR PropertyLength = Len (PropertyName)

VAR PropertyBagValueOffset = 11

VAR Offset = PropertyLength + PropertyBagValueOffset

VAR Position1 = FIND (PropertyName, PropertyBag, 1, 0)

VAR Position2 = IF (Position1 = 0, 0, FIND (",", PropertyBag, Position1 + Offset))

VAR Value1 = IF (Position1 = 0, "", MID (PropertyBag, Position1 + Offset, (Position2 - 1) - (Position1 + Offset)))

RETURN Value1

Two values will need to be changed within the above code example based on the property that you plan to extract: 

  1. The 1st line is the name of the new Column (e.g., Repository).

  2. The 3rd line is the name of the property within the ‘Property Bag’ (e.g., “@repo”).

The following table provides a detailed description of each VAR value that is shown in the example above:

Line #

Code

Explanation

1

Repository =

Names the new column. In this example, the new column will be named Repository. The = sign after the column name applies to the values supplied in the following lines.

2

VAR PropertyBag = records[PropertyBag]

Sets the ‘PropertyBag’ variable to the ‘PropertyBag’ column in the ‘records’ table.

3

VAR PropertyName = "@repo"

Sets the ‘PropertyName’ variable to the name of the property within the ‘PropertyBag’ column.

 In this example, the name of the property is @repo.

4

VAR PropertyLength = Len (PropertyName)

Sets the ‘PropertyLength’ variable to the length of the name of ‘PropertyName’. 

In this example, it is 5 (@repo).

5

VAR PropertyBagValueOffset = 11

Sets the PropertyBagValueOffset variable to 11. This is how many characters from the end of the ‘PropertyName’ to the 1st character of the value: “:”Value”:”

6

VAR Offset = PropertyLength + PropertyBagValueOffset

Sets the ‘Offset’ variable to the sum of the ‘PropertyLength’ and ‘PropertyBagValueOffset’.

 In this example, the sum would be 16 (5 + 11).

7

VAR Position1 = FIND (PropertyName, PropertyBag, 1, 0)

Sets the ‘Position1’ variable to the position of the first character of the ‘PropertyName’ variable within the ‘PropertyBag’ variable.

 In this example, it is 235.

8

VAR Position2 = IF (Position1 = 0, 0, FIND (",", PropertyBag, Position1 + Offset))

Sets the ‘Position2’ variable to 0 (zero) if the ‘Position1’ variable is 0. Otherwise, it is set to the location of the first after the ‘PropertyBag’ variable, starting at the position of the sum of the ‘Position1’ and ‘Offset’ variables. A comma always precedes the next property name.

 In this example, it is 262.

9

VAR Value1 = IF (Position1 = 0, "", MID (PropertyBag, Position1 + Offset, (Position2 - 1) - (Position1 + Offset)))

Sets the ‘Value1’ property to null if the ‘Position1’ variable is 0. Otherwise, it is set to the substring within the ‘PropertyBag’ variable starting at the position of the sum of ‘Position1’ and ‘Offset’. The length of the substring returned is the number of characters of the length of the value of the property.

 In this example, it is set to the substring starting at position 251 (235 + 16), which is the ‘S’ in SharePoint. The length is 10, which is 261 (262 – 1) – 251 (235 + 16).

10

RETURN Value1

This is the value to which the new column is set.

 In this example, the value is SharePoint.

 

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.