Role Required To set up your Enterprise Reporting & Analytics dashboard you need to be assigned to the Application Administrator or the Records Manager role.
Make sure you have the latest version of Power BI Desktop installed. Power BI Desktop is free.
Introduction
Our Enterprise Reporting & Analytics solution offers a comprehensive at scale reporting data feed as well as pre-built Microsoft Power BI dashboards enabling you to rapidly gain insights into content managed by Records365. You can leverage the pre-built dashboards as a starting point to build your own tailored dashboards. In addition, you can use Power BI to integrate over 60 other data sources to create powerful visualizations across different reporting repositories in your organization.Accessing the Records365 default report
Download the default report
- Login to Records365
- Navigate to the Administration page by clicking the cog wheel on the top bar
- Click on Reporting from the left-hand navigation menu
- Click the Enterprise tab
- Download the Power BI file
Connecting the report to your tenant
Now that you have downloaded the report, you can access data from Records365 in Power BI. Let’s set-up the dashboard and connect it to your Records365 tenancy. First we will need to get the connection details for the Power BI report from Records365.- Login to Records365 as an Application Administrator or Records Manager
- Navigate to the Administration page by clicking the cog wheel on the top bar
- Click on Reporting from the left-hand navigation menu
- Click the Enterprise tab
- Below are the values and their mapping to their respective fields for the Power BI report
| Records365 field | Power BI field |
|---|---|
| Server URL | Server |
| Database Name | Database |
| User name | User name |
| Password | Password |
Note: For first time use, you will need to generate a password using the Generate new password button. Once you have generated a password use the Copy button to retrieve the password value.
- Now open the downloaded report, Records365 Enterprise Reporting & Analytics Dashboard.pbit using Power BI desktop
- If presented with a Potential Security Risk dialog click OK to continue
- As the report loads it will prompt you for your connection details, please enter the relevant values as outlined above
- Click Run for all Native Database Query dialog forms
- Your data will start loading into the report
Tips for first time use:
- Most of the visualizations 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 visualizations, like the Daily Records Submitted on the right of the page. Not all the visualizations 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.
- The contents of all the visualizations 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’. There are limits as to what Power BI supports, please refer to official documentation here.
Building your own dashboards & reports
Understanding Power BI
Walking through all of the features of Power BI that our Reporting & Analytics dashboard uses is beyond the scope of this documentation. We recommend that you look at this article to help you get started using Power BI. Our prebuilt reporting dashboard should be used as a template to build additional visualizations and reports. The following articles provide additional guidance on how to extend the prebuilt dashboard:- Add visualizations to a Power BI report
- Data View in Power BI Desktop
- Best design practices for reports and visuals
You are encouraged to create your own custom reports. However, we highly recommend you use the provided template as your starting point. Many data relationships and query structures have been built in the template so that you can achieve best performance. Building custom reports without using the Records365 dashboard template can be done, but is outside our support terms.
Understanding Records365 data feeds
Our Reporting & Analytics solution gives you access to the following data feeds:
| Data Feed | Description |
|---|---|
| ITEM_ALL_LATEST_WITH_SOURCE | Latest version of all records, includes all fields from content sources |
| CONNECTOR_TYPE | Contains information for each connector types |
| ITEM_HOLD | Contains information about records that have Holds applied |
| PHYSICAL_LOAN_WITH_LOOKUP | Contains information about loan requests |
| PHYSICAL_LOAN_ITEM | Contains information about relating loan requests with records |
| DISPOSAL_APPROVAL_FLATTENED_LATEST | Contains information about records with disposal approvals that are pending, approved or rejected |
| METADATA_QUERIES | Provides the query text for all content source fields that can then be used to include fields into other queries |
| AUDIT_EVENT | Contains information regarding user actions on given records. For example, if an item was modified, and by who. |
| ITEM_CATEGORY_WITH_RETENTION_SCHEDULE | Contains information about categories available in your file plan, with information on the corresponding retention schedules |
| ITEM_HOLD | Contains information on records which have been placed on hold. |
You may mix and match reporting data from any of these reporting data feeds in order to create tailored visualizations and reports.
Using content specific source fields
As part of the enterprise reporting feature you can leverage all of the metadata originating from a records respective content source within your reports. By default all source fields are not included as part of the underlying data within the report. You can include additional source fields into the dataset, however as you add more fields it will result in larger datasets and may impact the performance of the report. If you have specific pages within your report that utilize certain source fields, you should consider "duplicating" the underlying dataset query and include the specific source fields. This duplicated query could then be used to power specific pages. This will minimize the impact on the default query, which is used for all of the pages and charts in the default report. Below is an example of creating a dataset query that is specific to SharePoint Online records;Duplicating a Records365 reporting Query in Power BI
Duplicating a query allows you to alter or extend a query without impacting existing charts. This is recommended when extensive changes are required in order for a query to surface the appropriate information for your reporting needs. To duplicate the main dataset query perform the following:
- With the report open in Power BI desktop, click the Transform data button
- Under the Snowflake Direct Queries, right click ITEM_ALL_LATEST_WITH_SOURCE and click Duplicate
- You will now have a new query, rename it to something meaningful, for example ITEM_ALL_LATEST_WITH_SPO
Extending a query to include additional source fields
- From the Report view within Power BI, click the Metadata Fields & Queries page
- Use the Field Name, Content Source, and or Data Type selectors to refine the results. The Content Source of Records365 applies to Physical Records based fields, the Content Source of External applies to External Data based fields
- For the field that you wish to include, click the value in the Query column
- Right click the value and select Copy -> Copy Value
- Click the Transform data button
- Select the query that you want to include the addition field in, either an existing query, or one that was duplicated in the previous step
- Click the Advanced Editor button
- Locate the select statement, it should look like this
"select * from "&Database&".""PUBLIC"".""ITEM_ALL_LATEST_WITH_SOURCE""",
- After the word Select insert a new line
- Paste the value from step 4
- At the end of the pasted value enter a ","
- Insert a new line after the newly added "," character. In this example the Contract Value field has been added, and the resulting query looks like
"select
IFF (IS_NULL_VALUE(source_metadata:""Double|Contract_x0020_Value""), NULL, CAST(source_metadata:""Double|Contract_x0020_Value"" AS Double)) AS ""Contract_x0020_Value"",
* from """&Database&""".""PUBLIC"".""ITEM_ALL_LATEST_WITH_SOURCE""",
- Repeating steps 9 -12 for each additional field
- Once all the additional fields have been added, click the Done button on the Advanced Editor
- In the Power Query Editor window, permissions will be required to run the query, click Edit Permission, and click Run
- Click Close and Apply on the Power Query Editor window
- The newly included source fields will now be available to be added to your visualizations. Below is an example new page called Contracts using the newly added SharePoint Online source field Contract Value from the duplicated query ITEM_ALL_LATEST_WITH_SPO
With Records365s ability to span content sources, records may have a null value for source fields. The Power BI True/False Data Type does not support null values, therefore Boolean based source fields are mapped as the Text Data Type.
Publishing and sharing your report
Once you have finalized your report, we recommend publishing the report to the Power BI service for ease of sharing across your organization. See the following articles for publishing and sharing.
If the report fails to load from Power BI service with the following message "Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again.", you will need to re-enter the credentials for the underlying dataset.
To re-enter the credentials for the underlying dataset with the Power BI service
- Navigate to the Data hub area of Power BI service
- For the dataset corresponding to your published report, click the More Options button "...", and select Settings
- Click the Data source credentials setting
- For the Snowflake entry, re-enter the Username and Password, as provided in the Connecting the report to your tenant section above.