Chat now with support
Chat with Support

Unified Communications Analytics 8.8.3 - 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:

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.

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.

3
Select the All category which includes all data connection types from all categories.
5
Select OData Feed and click Connect.
7
Click OK.
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.

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.

3
Clear (Select All Columns).
4
Select Key and Timestamp, and click OK.
5
Right-click Timestamp and select Change Type.
6
Select Date.

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.

 

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.

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.

2
Drag the Key and Timestamp fields, listed in the Fields pane, to the Axis and Values labels respectively.

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.

1
After you have connected to OData and displayed the tables in the Navigator, select EmailMessages and EmailMessageParticipants and click Edit.
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.
8
Clear (Select All Columns).
9
11
Clear (Select All Columns).
12
Select Key and EmailAddress and click OK.
14
Select DisplayName.
15
Right-click on the DisplayName column and rename the column to Participant Name.
17
In the Power BI ribbon, click Manage Relationships and select Autodetect…
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.
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating