BI Reporting Dimensions
Contact Expert v6.1 for Skype for Business Server
Dimensions are data based on which your facts can be sliced, grouped. broken down. The following dimensional data are available in the report packages:
- Dates
- Times
- Agents
- Campaigns
- Directions
- Media
- Dialing modes
The dimension tables are linked to the fact table (or tables) using Power Pivot's Diagram view. The dimension parameters can used in Excel pivot tables as rows, columns, filters, slicers and timelines.
Most dimension tables are refreshed together with the fact table(s) when selecting Refresh All... in Excel. The exceptions are the date and time tables. The source data of those tables resides in Excel files that does not have to be refreshed generally. Anyhow there might be situations when one would like to make modifications on those source files and want to re-load them to the Power Pivot data model. In that case follow the procedure described here:
Download the date or time dimension source Excel file from the knowledge base (see below)
Make the required changes in the source data
Within the Power Pivot data model of the report file select Existing connections...
Select the connection of the appropriate Excel source and press Edit
Modify Excel File Path parameter to point on your modified Excel source file
Press Save and then Refresh to download the modified data to the Power Pivot data model
Dates
This table is loaded from the popular Excel-5-Calendar-Date-Table-V2.xlsx Excel table, that is including a detailed table named DimDate with many columns about each day between 1 January 2000 and 31 December 2020. The table can be easily tuned for one’s specific requirements and can be used for formatting dates and for organizing days, weeks, months and years into hierarchical filters.
The version used in the sample file can be downloaded from here: Excel-5-Calendar-Date-Table-V2.xlsx.
| Field name | Descriptions | Possible or sample values |
|---|---|---|
| DateKey | The date in YYYYMMDD format. This field is used to link this dimension table with the fact table(s). | 20100702 |
| FullDate | The full date including time in the format defined by the system's locale. The time part is always midnight as this is a date value without actual time component. | 7/2/2010 12:00:00 AM |
| Calendar Year | The year part of the date | 2010 |
| Calendar YearMonth | Year with short month name. This column should be sorted based on Calendar YearMonth Num to be correctly sorted in pivot tables and pivot charts. | 2010-Jul |
| Calendar YearMonth Num | Year with month in YYYYMM format. | 201007 |
| Calendar Semester | Half year term. | Semester 2 |
| Calendar Quarter | Quarter year term. | Qtr 3 |
| Calendar Month Number | Month number in MM format (including leading zero) | 07 |
| Month Long Name | Full month name. This column should be sorted based on Calendar Month Num to be correctly sorted in pivot tables and pivot charts. |
July |
| Month Short Name | Abbreviated month name. This column should be sorted based on Calendar Month Num to be correctly sorted in pivot tables and pivot charts. | Jul |
| Calendar Week number | Number of week in the year | 27 |
| Calendar Week Name | Number of week in the year | Week 27 |
| Calendar 2-Week | Two week terms of the year | Week 27-28 |
| Calendar Number of Year | Sequence number of day in the year | 183 |
| Day Number of Month | Sequence number of day in the month. | 02 |
| Day Number of Week | Sequence number of day in the week. It is by default defined based on the US standard meaning Sunday is the 1st day of the week. | 6 |
| Day Name Long | Full day name. This column should be sorted based on Day number of Week to be correctly sorted in pivot tables and in pivot charts. | Friday |
| Day Name Short | Abbreviated day name. This column should be sorted based on Day number of Week to be correctly sorted in pivot tables and in pivot charts. | Fri |
| Weekday Indicator | Signs Saturday and Sunday as "Weekend", other days as "Weekday". | Weekday / Weekend |
| Readable Month | An informative display of month. It is useful when it would not be obvious from the context which year the date refers to. | 2010.07. (July) |
| Readable Day | An informative display of date. It is useful when it would not be obvious from the context which month and year the date refers to. | 2010.07.02. (Friday) |
| Date (based on locale) | The date defined based on the system's locale (without the time part) | 7/2/2010 |
| Month of year | Another version of the month of the year. This column should be sorted based on Calendar Month Num to be correctly sorted in pivot tables and pivot charts. | Jul 2010 |
The following date hierarchies are defined:
| Hierarchy name | Hierarchy levels | Description |
|---|---|---|
| Day of month | Calendar Year |
If using this hierarchy with pivot tables or pivot charts, the user will be able to drill down from year level to months and to days. |
| Day of week | Calendar Year Calendar Week Name Day Name Short |
If using this hierarchy with pivot tables or pivot charts, the user will be able to drill down from year level to weeks and to days. |
| Chart axis | Calendar Year Month of Year Date (based on locale) |
A year - month - day hierarchy optimized to be used as a chart date X axis. |
Times
This table is loaded from file TimeSeries.xlsx and is used to fast translate time duration measured in seconds into the more intuitive HH:MM:SS format.
The version used in the sample file can be downloaded from here: TimeSeries.xlsx.
| Field name | Descriptions | Possible or sample values |
|---|---|---|
| Minute | Sequence number of minute in the day. This field is used to link this dimension table to the fact table(s). | 837 |
| Hour | Hour of the day | 13 |
| Time | The simplest time string in HH:mm format | 13:57 |
| Hour range | The one hour range the actual time belongs to | 13:00-14:00 |
| Half hour range | The half hour range the actual time belongs to | 13:30-14:00 |
| Quarter hour range | The 15 minutes range the actual time belongs to | 13:45-14:00 |
| Ten minutes range | The 10 minutes range the actual time belongs to | 13:50-14:00 |
| Five minutes range | The 5 minutes range the actual time belongs to | 13:55-14:00 |
| Minute range | The 1 minutes range the actual time belongs to | 13:57-13:58 |
| Two hours range | The 2 hours range the actual time belongs to | 12:00-14:00 |
| Four hours range | The 4 hours range the actual time belongs to | 12:00-16:00 |
| Quarter day range | The 6 hours range the actual time belongs to | 12:00-18:00 |
| Eight hours range | The 8 hours range the actual time belongs to | 08:00-16:00 |
| Half day range | The half day the actual time belongs to | 12:00-00:00 |
Agents
| Field name | Descriptions | Possible or sample values |
|---|---|---|
| Agent name | Agent full name as defined in CE Portal. | Johnny Brown |
| Login name | Agent login name as defined in CE Portal. | jbrown@mycompany.com |
| Team | Primary grouping level for agents | Blue team |
| Business Unit | Secondary grouping level for agents | Help Desk |
| Organization unit | Deprecated data. It is a free form field that can be defined in CE Portal. It is used only for backward compatibility in CE 5.x. Use the hierarchical Team / Business Unit grouping system instead. | Help Desk - Blue team |
| Removed | Yes, if the agent has been deleted from the operation database of Contact Expert already. The agent record is still available for historical reporting. | Yes / No |
Campaigns
| Field name | Descriptions | Possible or sample values |
|---|---|---|
| Campaign name | The campaign name as defined in CE Portal. | Customer survey |
| Removed | Yes, if the agent has been deleted from the operation database of Contact Expert already. The agent record is still available for historical reporting. | Yes / No |
Directions
| Field name | Descriptions | Possible or sample values |
|---|---|---|
| Direction | Defining the direction of the interaction. | Inbound, Outbound. Unknown is it could not been determined. |
Media
| Field name | Descriptions | Possible or sample values |
|---|---|---|
| Media | Defining the media channel of the interaction. | Voice, Email or Chat |
Dialing modes
| Field name | Descriptions | Possible or sample values |
|---|---|---|
| Dialing Mode | Defining the dialing mode of an outbound interaction. | Preview, Progressive or Predictive. Not defined for inbound interactions. |