Using the Power BI starter template
These instructions take you through the steps of connecting the DocRead PowerBI starter template to your DocRead data.
You need to have the DocRead Analytics Module enabled on your account to use the DocRead PowerBI starter template.
The DocRead Analytics Module can only be enabled by the DocRead team. To enable this module on your account, please contact the DocRead customer success team for a quote.
Check this article for more information about the DocRead Analytics Module.
Introduction
The DocRead Power BI starter template was developed to demonstrate how to connect Power BI to your DocRead analytical store and create custom reports. This starter template includes a variety of sample reports that can be customized to meet your needs. You can also use these sample reports to explore how DocRead analytical data can be manipulated and presented in your own reports.
- Connecting the PowerBI template to your data
- Refreshing the data in PowerBI
- Sample Reports
- PowerBI Data Model
Connecting the PowerBI template to your data
When the DocRead Analytics Module is enabled on your account you will receive a welcome email with the following information:
- PowerBI starter template URL - The url for the DocRead PowerBI starter template
- ContainerURL - URL for a cloud container containing your analytical data
- Container Shared Access Signature (SAS Key) - The secret key that gives you permission to access your analytical data
Please keep the welcome email information secure. Anyone can access your analytical store with it.
Important facts about the DocRead analytical data
- The analytical data is read-only. It cannot be modified.
- The analytical data is synchronised with your live DocRead data once per day (it’s not real-time).
- The synchronisation time window is between 2 AM and 5 AM UTC.
- After enabling the DocRead Analytics Module for the first time, it may take up to 24 hours for your analytical data to become available.
-
Download the Starter Template file provided by Collaboris and open it in PowerBI.
-
At the prompt, add the Container URL provided in the welcome email and click load:
- Go to the Shared access signature tab:
-
Add the SAS key provided with your welcome email.
-
Your data will be added to the Power BI model (this may take a few seconds).
-
Save the newly populated file. The connection details will be saved with the file, so you will not need to provide them again when you open it in the future.
Refreshing the data in PowerBI
To refresh the PowerBI simply open the PowerBI file you saved on the previous steps and click the refresh button.
Sample Reports
Assignment Summary
This report shows the status of all assignments created by DocRead 365. The filters of DocumentName and Assignment Status can be used to drill down into the detail.
Use case: to provide an overview of all items assigned by DocRead.
Assignment Focus Reports
These four reports summarize Assignments, Audiences, Users and Documents. Additional detail can be found in later pages.
Use cases: To gain a quick oversight about the worst performing elements within each summary.
User Assignment Status
Select a single user from the drop down box to see a summary of all items assigned to them. A list is also provided showing details for all assignments. The information shown on this report can also by filtered by Assignment status.
Use cases: to obtain detailed compliance information about a specific user.
Assignments by Group Report
Select a single group from the drop down box to see a summary of all items assigned to it. The list below shows details for all items assigned to the selected group. The information shown on this report can also by filtered by Assignment status.
Use cases: Department managers / group managers
Document Assignment Status
Select a single document from the drop down box to see a summary of all assignments created for it. A list is also provided showing details for all assignments. The information shown on this report can also by filtered by Assignment status.
Use cases: to obtain detailed compliance information about a specific user.
Reading receipts
Reading receipts are created to prove that someone did (or did not) complete a DocRead assignment. The information shown on this report can also by filtered by several fields including Document, User and reason.
Use cases: Compliance managers needing to prove a specific user / document compliance.
Library Reading receipts
Select a single library from the drop down box to see details of all DocRead receipts created for items stored in the library. The information shown on this report can also by filtered by DocumentName.
Use cases: Library / document owners needing to prove completion of all assignments for a specific library or document.
Group Reading receipts
Select a single group from the drop down box to see details of all DocRead receipts created for members of the group. The information shown on this report can also by filtered by ReceiptType (positive/negative).
Use cases: Group/department managers needing to prove completion of all assignments for members of their team.
Drill Down Dashboards
The drill down dashboards are possible because a hierarchy has already been created in the Power BI database models (described above) for Site>Library>Document and Group>User.
Site Dashboard
The Site dashboard report will begin with a summary of the reading statuses of all assignments created from that site.
To activate the dropdown options click within the chart area and you should see the following icons appear in the top righthand corner of the visualization.
These can be used as normal within this visualization to drill down and up through the reporting hierarchy.
Level 2: Library detail (from within the selected site)
Level 3: Document detail (from within the selected library)
Note: To see the details of assignments for a specific document, you need to use the Document report.
Group dashboard
The Group dashboard report will begin with a summary of the reading statuses for all groups used with DocRead.
Drilling down into a specific group will display the assignment status for all individual users in that group.
PowerBI Data Model
AssignmentsResult table
Contains information about the last time the assignments information was synchronised with the live data and the results of the synchronisation.
| DR fields | Data type | description |
|---|---|---|
| Application | Text | DocRead |
| LastUpdated | Date/time (dd/mm/yyyy hh:mm:ss) | the time and date the assignments were last synchronised with the live DocRead data (by default this is once/day). |
| Message | Text | message describing the result of the import (success/failure) |
| Status | Text | ok |
ReceiptsResult table
Contains information about the last time the receipts information was synchronised with the live data and the results of the synchronisation.
| DR fields | Data type | description |
|---|---|---|
| Application | Text | DocRead |
| LastUpdated | Date/time (dd/mm/yyyy hh:mm:ss) | the time and date the receipts were last synchronised with the live DocRead data (by default this is once/day). |
| Message | Text | message describing the result of the import (success/failure) |
| Status | Text | ok |
Assignments table
The assignments table contains information about all of the assignments created by DocRead 365.
The full data schema can be found here (link) however, key fields are discussed below
Combined status: this shows the overall status of an assignment (completed, assigned or overdue)
Two hierarchies have already been created as follows:
- Group name: Group name > User name.
- Site URL: Site URL > Library URL > Document URL
- Assignments Result
- This confirms the date the assignments data was last updated. The date and time is displayed at the top right hand side of assignment reports.
- Receipts Result
- This confirms the date the receipts data was last updated. The date and time is displayed at the top right hand side of reports showing receipt information.
| DR Fields | Data type | description |
|---|---|---|
| ShortId | Text | a unique ID used within DocRead to identify the specific assignment |
| AgreedTerms | Text | the T&S statement agreed at the time the user completed their assignment |
| AssignmentType | Text | either Required or Recommended |
| AssignmentUrl | Text | URL to the user assignment |
| AudienceID | Text | a unique ID used within DocRead to identify the specific audience |
| AudienceName/ | Text | the audience name used when creating the task. Note if nothing was entered “Default document audience” will be used |
| AudienceScope | Text | either Document or Global |
| AutoCompleted | True/false | either yes (if the document was autocompleted by DocRead when the user completed a duplicate assignment for the same document) or no if the user completed the assignment directly |
| CombinedStatus | Text | can be Overdue, Assigned or Completed. This field combines the Assignment status (assigned or completed) with the overdue field (yes/no) to give the overall status value |
| CompletedDateTs | Whole number | the completion date and time stamp of the assignment stated in TICs |
| CompletionDate | Date (dd MONTH yyyy) | the completion date for the assignment |
| CreationDate | Date (dd MONTH yyyy) | the creation date for the assignment |
| CreationDateTs | Whole number | the creation date and time of the assignment stated in TICs |
| DocumentId | Text | a unique ID used within DocRead to identify the specific document |
| DocumentName | Text | the name of the document as shown in SharePoint |
| DocumentTitle | Text | the title of the document as shown in SharePoint |
| DocumentUrl | Text | the URL location of the document |
| DocumentVersion | Text | document version number as stated in SharePoint |
| DocumentVersionUrl | Text | the URL location of the specific version of the document |
| DueDate | Date(dd MONTH yyyy) | the due date for the assignment |
| DueDateTs | Whole number | the due date and time of the assignment stated in TICs |
| GroupId | Text | a unique ID used within DocRead to identify the specific group |
| GroupIDCount | Calculation | used to count the number of distinct group id’s in reports ignoring blank rows |
| GroupName | Text | the name of the group used for the assignment |
| LibraryId | Text | a unique ID used within DocRead to identify the specific library |
| LibraryName | Text | the name of the library containing the document |
| LibraryUrl | Text | the URL location of the library |
| PartitionKey | Text | internal field used by DocRead |
| ReceiptUrl | Text | URL to the DocRead receipt |
| RecordType | Text | Assignment |
| ShortID | Text | a unique ID used within DocRead to identify the specific SOMETHING |
| SiteCollectionId | Text | a unique ID used within DocRead to identify the specific site collection |
| SiteId | Text | a unique ID used within DocRead to identify the specific site |
| SiteUrl | Text | the URL location of the site |
| Status | Text | can be either Assigned or Completed |
| UserEmail | Text | the email address for the user |
| UserId | Text | a unique ID used within DocRead to identify the specific user |
| UserName | Text | the name of the user |
| GroupName Hierarchy GroupName GroupID UserName | created in PowerBi to allow drill-down from group to user | |
| SiteURL Hierarchy SiteURL LibraryURL LibraryName DocumentURL DocumentName | created in PowerBi to allow drill-down from site to library and document |
Receipts table
The receipts table contains information about all of the receipts generated by DocRead 365. These can be either positive (confirming the user read the document) or negative (confirming the user did NOT read the item).
The full data schema can be found here (link) however, key fields are discussed below
Auto completed: If a user receives multiple assignments for a single policy (because they are in different groups for example) then when they complete one assignment, all other ones will be automatically completed at the same time. One assignment will show as “Auto completed : No”, the others will show as “Auto completed: True”
| DR field | format | description |
|---|---|---|
| AgreedTerms | Text | the T&S statement agreed at the time the user completed their assignment |
| AssignmentID | Text | a unique ID used within DocRead to identify the specific assignment |
| AssignmentShortID | Text | a unique ID used within DocRead to identify the specific assignment |
| AssignmentType | Text | this refers to whether the original assignment was Required or Recommended |
| AudienceID | Text | a unique ID used within DocRead to identify the specific audience |
| AudienceName | Text | the name of the audience as described in DocRead |
| AudienceScope | Text | Document or Global |
| AutoCompleted | True/false | either yes (if the document was autocompleted by DocRead when the user completed a duplicate assignment for the same document) or no if the user completed the assignment directly |
| DocumentBusinessID | Text | a unique ID used within DocRead to identify the specific SOMETHING |
| DocumentContentType | Text | internal field used by DocRead |
| DocumentIcon | Text | the file extension of the item assigned |
| DocumentID | Text | a unique ID used within DocRead to identify the specific document |
| DocumentItemID | Text | a unique ID used within DocRead to identify the specific document item |
| DocumentName | Text | the name of the document as shown in SharePoint |
| DocumentTitle | Text | the title of the document as shown in SharePoint |
| DocumentUrl | Text | the URL location of the document |
| DocumentVersion | Text | the version number of the document as stored in SharePoint |
| GroupID | Text | a unique ID used within DocRead to identify the specific group |
| GroupName | Text | the name of the group used for the assignment |
| Library | Text | the library URL where the document is stored in SharePoint |
| Message | Text | the description included in the receipt |
| Overdue | True/false | Yes or No |
| PartitionKey | Text | internal field used by DocRead |
| Reason | Text | the reason the receipt was created |
| ReceiptDate | Date/time (dd/mm/yyyy hh:mm:ss) | the time and date the receipt was created |
| ReceiptType | Text | either Positive (confirming the user completed the assignment) or Negative (confirming the user did NOT complete the assignment) |
| TaskCompletionDate | Date/time (dd/mm/yyyy hh:mm:ss) | the time and date the assignment was completed |
| TaskCreationDate | Date/time (dd/mm/yyyy hh:mm:ss) | the time and date the assignment was created |
| TaskDueDate | Text (yyy-mm-ddThh:mm:ss.nnnnn+timezone) | the assignment due date in a special format |
| UserEmail | Text | the email of the user |
| UserID | Text | a unique ID used within DocRead to identify the specific user |
| UserName | Text | the name of the user |