Chat now with support
Chat with Support

Unified Communications Analytics 8.7 - Resource Kit

Using Microsoft Power BI to explore the OData endpoint

You can use Microsoft Power BI Desktop to access the UC Analytics OData endpoint to extract and view your collected data. For performances reasons, it is recommended that you always edit and filter the query before loading the data. For example, you might filter by limiting the query to a short period of time or limiting the query to selected people.

The workflow for using Power BI can be divided into the following procedures:

1
Connecting to OData.
2
Selecting and downloading the data to your local computer.
3
Creating a visualization of the data.

The procedure to connect to OData is provided followed by a sample scenario to show the procedures for selecting and downloading data and for creating a visualization.

Prerequisites

You must install Microsoft Power BI Desktop on your computer. You can download Power BI Desktop from the following Microsoft web site: https://powerbi.microsoft.com

You also must have unrestricted access to all the data (configured in the Security settings in the UC Analytics Admin Setting) that you want to extract. For information about granting access to the UC Analytics data, see the UC Analytics Deployment Guide.

To connect to OData
1
Open Power BI Desktop.
2
On the home ribbon, click Get Data.
Data types are organized in the following categories:
•
All
•
File
•
Database
•
Azure
•
Other
3
Select the All category which includes all data connection types from all categories.
4
In the All list, scroll down to locate the OData Feed.
5
Select OData Feed and click Connect.
6
Under URL, enter the URL for the server on which the UC Analytics Data Engine is installed and specify port 1336.
For example, for server MyAnalytics, you would enter: http://MyAnalytics:1336
7
Click OK.
Power BI Desktop makes the connection to OData Feed and, in the Navigator, presents a list of the available tables.
 
* 
TIP: Do not select the Load button at the bottom of the Navigator pane before you have edited your query. If you simply select a table and click Load, all the data in the table is loaded into local RAM on your computer. For example, if you selected EmailMessages and clicked Load, data for every email message stored in UC Analytics would be downloaded to your computer. The download could take a very long time.
8
To edit the query before loading data, select the Edit button.

Sample scenarios

The following sample scenarios explain how to download selected data and to create visualizations using Power BI Desktop. By following the steps in these scenarios, you can become familiar with some of the Power BI Desktop functions.

For more information about using Power BI Desktop, see the knowledge base at the Microsoft Power BI support site: https://support.powerbi.com/knowledgebase.

Using Power BI Desktop to recreate a graph from the Email - Activity insight

The following example shows you how to recreate a graph from the Email - Activity insight that shows the number of email messages over time. To recreate the graph, you need two fields from the EmailMessages table: Key and Timestamp.

To select email message data over time
1
After you have connected to OData and displayed the tables in the Navigator, select EmailMessages and click Edit.
2
In the Query Editor ribbon, click Choose Columns.
3
Clear (Select All Columns).
4
Select Key and Timestamp, and click OK.
5
Right-click Timestamp and select Change Type.
6
Select Date.
Email messages are stored using the exact date and time of each message. You change the Timestamp column from Date/Time to Date so that messages can be aggregated by day to produce a reasonable line graph.
7
In the Query Editor ribbon, click Close & Apply.
Your changes are saved. When you first load data in Power BI Desktop, you will see Report View with a blank canvas.
The Visualizations and Fields panes should be displayed on the right.
Optional: Using the Advanced Editor

If you want to see the code that Query Editor is creating with each step, or want to create your own code, you can use the Advanced Editor.

To launch the advanced editor, select View from the ribbon and select Advanced Editor. A window appears, showing the existing Query code.

For example, you could select the Key and Timestamp fields from the EmailMessages table using the Advanced Editor by entering the following sample query.

To enter the selection code in the advanced editor
1
After you have connected to OData and displayed the tables in the Navigator, right-click EmailMessages and select Advanced Editor.
2
Paste the following query into the Advanced Editor:

 

let

Source = OData.Feed("http://MyAnalytics:1336",

EmailMessages_table = Source{[Name="EmailMessages",Signature="table"]}[Data],

#"Removed Other Columns" = Table.SelectColumns(EmailMessages_table,{"Key", "Timestamp"}),

#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Timestamp", type date}})

in

#"Changed Type"

3
Click Done.
Restricting a query to a specified date range

To improve query performance, you can insert code to specify a start date and an end date for the data shards to be included in the query. You can insert the following code immediately after first line as follows:

 

let

Source = OData.Feed("http://MyAnalytics:1336",

[ ],

[Query=[

IsFromExternalToolRequest="true",

StartDate="2019-03-01T00:00:00",

EndDate="2019-03-07T00:00:00"]

] ),

EmailMessages_table = Source{[Name="EmailMessages",Signature="table"]}[Data],

#"Removed Other Columns" = Table.SelectColumns(EmailMessages_table,{"Key", "Timestamp"}),

#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Timestamp", type date}})

in

#"Changed Type"

For the StartDate and EndDate you enter the dates that specify the time period for which you want to include records.

After you click Done, the query does not reflect the selected date range. In the Query Editor ribbon, you must click Refresh Preview to preview the data with specified shards. You can click Close & Apply to view the data for the date range from the database.

To create a graph that shows email messages over time
After you have downloaded the email message data by date, you can create a visualization of the data.
1
In the Visualizations pane, select the line graph icon.
Under the Visualizations pane, a secondary pane shows the line graph parameters such as Axis, Legend, and Values.
2
Drag the Key and Timestamp fields, listed in the Fields pane, to the Axis and Values labels respectively.
The resulting line graph should closely resemble the graph in the Email - Activity insight.

Using Power BI Desktop to create a graph for specific user email activity

The following example shows you how to recreate a graph that shows the number of email messages over time for a specific user. You need to use two different tables: EmailMessages and EmailMessageParticipants.

To select email message data for a specific user over time
1
After you have connected to OData and displayed the tables in the Navigator, select EmailMessages and EmailMessageParticipants and click Edit.
2
When the Query Editor has the EmailMessages table in preview, in the Query Editor ribbon click Choose Columns.
3
Clear (Select All Columns).
4
Select Key and Timestamp and Sender, and click OK.
5
Right-click Timestamp and select Change Type.
6
Select Date.
Email messages are stored using the exact date and time of each message. You change the Timestamp column from Date/Time to Date so that messages can be aggregated by day to produce a reasonable line graph.
7
Click the down arrow at the top right of the column header to expand the Sender column.
8
Clear (Select All Columns).
9
Select Key.
10
When the Query Editor has the EmailMessageParticipants table in preview, in the Query Editor ribbon click Choose Columns.
11
Clear (Select All Columns).
12
Select Key and EmailAddress and click OK.
13
Click the down arrow at the top right of the column header to expand the EmailAddress column.
14
Select DisplayName.
15
Right-click on the DisplayName column and rename the column to Participant Name.
16
In the Query Editor ribbon, click Close & Apply.
Your changes are saved. When you first load data in Power BI Desktop, you will see Report View with a blank canvas. The Visualizations and Fields panes should be displayed on the right.
17
In the Power BI ribbon, click Manage Relationships and select Autodetect…
This step detects the relationships between the data.
To create a graph that shows email messages for a specific user
After you have downloaded the email message data by date and the email participant data, you can create a visualization of the data.
1
In the Visualizations pane, select the line graph icon.
Under the Visualizations pane, a secondary pane shows the line graph parameters such as Axis, Legend, and Values.
2
Drag Key and Timestamp fields, listed in the Fields pane, from the EmailMessages entity into the Values and Axis fields respectively.
3
Drag Participant Name field, listed in the Fields pane, from the EmailMessageParticipants entity into the Legend field for the line graph.
You should see the email activity trends for all the participants.
4
To analyze a specific participant, expand Participant Name under the Filters, and select the name of the participant that you want to see.
The resulting line graph should show the email activity over time for a specific user.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating