Chat now with support
Chat with Support

Unified Communications Analytics 8.7 - Resource Kit

Using Power BI Desktop to create a bar chart for Exchange public folder item counts

The following example shows you how to create a vertical bar chart for public folders that shows the number of items in each public folder on a particular day.

To select public folder item count for a specific date
1
After you have connected to OData and displayed the tables in the Navigator, select ExchangePublicFoldersSnapshot and click Edit.
2
In the Query Editor ribbon, click Choose Columns.
3
Clear (Select All Columns).
4
Select Key, Fullpath, ItemCount, Name, and SnapshotDate and click OK.
5
Right-click SnapshotDate and select Change Type.
6
Select Date.
Messages are stored in public folders using the exact date and time of each message. You change the SnapshotDate column from Date/Time to Date so that messages can be aggregated by day to produce a total item count for the specified date.
7
At the top of the SnapshotDate column, click the down arrow to see a list of snapshot dates.
8
Select the snapshot date that you want.
9
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.
To create a bar chart that shows public folder item count for a specific date
After you have downloaded the public folder items and snapshot date, you can create a visualization of the data.
1
In the Visualizations pane, select the vertical bar chart icon.
Under the Visualizations pane, a secondary pane shows the bar chart parameters such as Axis and Values.
2
Drag the Name and ItemCount fields, listed in the Fields pane, to the Axis and Values labels respectively.
3
Under Filters, click the down arrow to expand Name(All).
4
Locate IPM_SUBTREE and clear the check box.
5
Under Visualizations, click the paintbrush icon to display customization options.
6
Set Data Labels to On.
The resulting bar chart will show all the public folders and the number of items in each for the selected day.

Using Power BI Desktop to create a trend for item counts in an Exchange public folder

Using Power BI Desktop to create a trend for item counts in an Exchange public folder

The following example shows you how to create a trend that shows the number of items in a specific public folder over time.

To select public folder item counts over time
1
After you have connected to OData and displayed the tables in the Navigator, select ExchangePublicFoldersSnapshot and click Edit.
2
In the Query Editor ribbon, click Choose Columns.
3
Clear (Select All Columns).
4
Select Key, Fullpath, ItemCount, Name, and SnapshotDate and click OK.
5
Right-click SnapshotDate and select Change Type.
6
Select Date.
Messages are stored in public folders using the exact date and time of each message. You change the SnapshotDate column from Date/Time to Date so that messages can be aggregated by day to produce a total item count for the each date.
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.
To create a trend that shows the item counts in a public folder over time
After you have downloaded the public folder item count 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 ItemCount.
2
Drag the SnapshotDate, Name, and ItemCount fields, listed in the Fields pane, to the Axis, Legend, and ItemCount labels respectively.
3
Under Filters, click the down arrow to expand Name(All).
4
Clear (All) and check the public folder name that you want to trend.
5
Under Visualizations, click the paintbrush icon to display customization options.
6
Set Data Labels to On.
The resulting trend graph will show the public folder you selected and the number of items in the folder for each day in the snapshot dates.

About query performance

If you query for a large amount of data from the tables, the query can take a significant amount of time to complete. It is recommended that, when you edit queries, you only select the fields that you need.

 
Table 2. Examples of load times for different query sizes.
Number of Rows
Number of Columns
Query Size (MB)
Query Load Time (seconds)

30,000

3

4.5

13

30,000

23

18

47

About columns with multiple values

By default, in OData each row contains a single entity such as one message, one conference, and so on. So for columns that can contain multiple values (such as participant), if you select Count for the Count of Key, you will see duplicate values.

For example, if you selected participants and a message has several participants, the same message appears in multiple rows, one entry for each participant. In this case, change the Count of Key from Count to Count (Distinct) to get a count of distinct keys.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating