Data sets or data marts are groups of SQL Server stored procedures in the product database. Result of a data mart execution is a subset of the data warehouse, which is oriented on a specific business entity. Data sets are used for standard reports. Also using “stable” data sets customers are able to create own reports in case they need something special above standard reports.

In data warehouse each fact and each dimension table are presented as separate data mart, this makes easy access to frequently needed data. Fact and dimension data marts can be later combined using relation models into three general views:

  • Agent View
  • Service View
  • Customer View

This views are needed for role based access control and to analyze business from different points of view.

Business points of view:

  • The Agent View is used for agent performance analysis, the grain of this view is agent task or agent state.
  • The Service View is used for service call flow and call distribution analysis, the grain is service task and it provides information on happenings within a workflow of a specific service before task is in context of this service.
  • The Customer View is used for customer path analysis between multiple services. It provides information on grains of customer interactions. Each customer task may be solved by one or more service transfers. This view shows measure values with from customer task start until it is ended and may include multiple service tasks.

[Reporting].[usp_GetDataSet] Query Procedure

The [Reporting].[usp_GetDataSet] procedure is designed to return one specific dataset (one table of data). Depending on the input parameters, the procedure output is different.

Example call:

EXEC [Reporting].[usp_GetDataSet]
@DataSet = 'ServiceTask'
, @Version = '3.1.1'
, @TimeZone = '(GMT) Coordinated Universal Time'
, @LanguageId = 'ENG'
, @MonthLimit = 3
SQL

[Reporting].[usp_GetDataSet] Parameters

Input parameters belong to the [Reporting].[usp_GetDataSet] procedure intended for selecting target dataset out of the available dataset list and shape the output.

Parameter nameTypeDescriptionDefault

@DataSet

string

It is the name of the fact or dimension, which must be queried

-

@Version

string

Depending of the version the output may slightly differ: new fields may be available only in new version, or same column may be renamed or completely deleted

-

@TimeZone

string

Time zone name

(GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna

@LanguageId

string

Language of the date part fields: Month and day of week

ENG

@MonthLimit

integer

Filter dataset to return data only for last N month

3

@DataSet parameter values

The table below contains available reporting datasets.

  • The column "Permission Type" is the specified supervisor permission required, which is considered during RBAC.
  • The "OU binding" field describes which Organization Unit (OU) rules are considered during RBAC.


DataSetPermission TypeOU binding rules

Agent

Agent

Direct permission

+ deleted children

AgentProfile

Agent

Direct permission

+ parents up + deleted children

AgentSelectionType

Unrestricted


AgentStateHourlySnapshot

Agent

Direct permission on Agent historical OU

+ deleted children

AgentStatePeriod

Agent

Direct permission on Agent historical OU

+ deleted children

AgentStateType

Unrestricted

 -

AgentTask

Agent

Direct permission on Agent historical OU

+ deleted children

AgentTaskResult

Unrestricted

 -

CrossSellingCode

Agent or Service

Direct permission

+ parents up + deleted children

NotReadyReason

Agent

Direct permission

+ parents up + deleted children

Service

Service

Direct permission

+ parents up + deleted children

ServiceTask

Service

Direct permission on service OU

+ deleted children

SurveyQuestion

Service

Direct permission

+ parents up + deleted children

SurveyResult

Service

Direct permission on service OU

+ deleted children

TackCompletionInformation

Agent

Direct permission on service OU

+ deleted children

TaskCompletionCode

Agent or Service

Direct permission

+ parents up + deleted children

TaskResult

Unrestricted

 -

Time

Unrestricted

 -

Transfer

Service

Direct permission on service OU

+ deleted children

TransferType

Unrestricted

 -



DataSetPermission TypeOU binding rules

BetweenType

Unrestricted

 -

CalendarCategoryType

Unrestricted

 -

CustomerPath

Customer

Direct permission on any service OU in CustomerPath

+ deleted children

CustomerTask

Customer

Direct permission on any service OU in CustomerPath

+ deleted children

Date

Unrestricted

 -

ModalityType

Unrestricted

-

OrganizationUnit

Agent or Service

Direct permission

+ parents up + deleted children

OriginType

Unrestricted

StaticDimension

TaskType

Unrestricted

 -

Time

Unrestricted

 -

Transfer

Service

Direct permission on service OU

+ deleted children

TransferType

Unrestricted

 -

@Timezone parameter values

Every fact data mart stored procedure have input parameters, which can convert date time values to client time zone. Local time zone may be specified using 2 equal options:

  • Time zone name

  • Pair of the values @Offset, @UseDaylightSavings

The table below contains a list of available client time zones:

Time ZoneOffsetDaylightSavings Flag

(GMT-10:00) Hawaii

-10:00

0

(GMT-09:00) Alaska

-10:00

1

(GMT-08:00) Pacific Time (US and Canada); Tijuana

-08:00

1

(GMT-08:00) Tijuana, Baja California

-08:00

1

(GMT-07:00) Mountain Time (US and Canada)

-07:00

1

(GMT-07:00) Arizona

-07:00

1

(GMT-07:00) Chihuahua, La Paz, Mazatlán

-07:00

1

(GMT-06:00) Central Time (US and Canada)

-06:00

1

(GMT-06:00) Saskatchewan

-06:00

0

(GMT-06:00) Central America

-06:00

1

(GMT-06:00) Guadalajara, Mexico City, Monterrey

-06:00

1

(GMT-05:00) Eastern Time (US and Canada)

-05:00

1

(GMT-05:00) Indiana (East)

-05:00

1

(GMT-05:00) Bogota, Lima, Quito

-05:00

1

(GMT-04:00) Atlantic Time (Canada)

-04:00

1

(GMT-04:00) Georgetown, La Paz, San Juan

-04:00

1

(GMT-04:00) Santiago

-04:00

1

(GMT-04:00) Manaus

-04:00

1

(GMT-04:00) Asuncion

-04:00

1

(GMT) Coordinated Universal Time

+00:00

1

(GMT) Greenwich Mean Time: Dublin, Edinburgh, Lisbon, London

+00:00

1

(GMT) Monrovia, Reykjavik

+00:00

0

(GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna

+01:00

1

(GMT+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague

+01:00

1

(GMT+01:00) Brussels, Copenhagen, Madrid, Paris

+01:00

1

(GMT+01:00) Sarajevo, Skopje, Warsaw, Zagreb

+01:00

1

(GMT+01:00) West Central Africa

+01:00

1

(GMT+02:00) Amman

+02:00

1

(GMT+02:00) Beirut

+02:00

1

(GMT+02:00) Cairo

+02:00

1

(GMT+02:00) Jerusalem

+02:00

1

(GMT+02:00) Harare, Pretoria

+02:00

1

(GMT+02:00) Windhoek

+02:00

1

(GMT+02:00) Helsinki, Kiev, Riga, Sofia, Tallinn, Vilnius

+02:00

1

(GMT+02:00) Athens, Bucharest, Istanbul

+02:00

1

(GMT+02:00) Minsk

+02:00

1

(GMT+03:00) Baghdad

+03:00

0

(GMT+03:00) Moscow, St. Petersburg, Volgograd

+03:00

0

(GMT+03:00) Kuwait, Riyadh

+03:00

0

(GMT+03:00) Nairobi

+03:00

0

(GMT+03:00) Tbilisi

+03:00

0

(GMT+04:00) Abu Dhabi, Muscat

+03:00

0

(GMT+04:00) Baku, Tbilisi, Yerevan

+03:00

0

(GMT+04:00) Port Louis

+03:00

0

(GMT+11:00) Magadan, Solomon Islands, New Caledonia

+11:00

0

@Language parameter values

Every fact data mart stored procedure has language parameter

LanguageId

Description

DEU

Deutsch (German)

ENG

English (default)

ESP

Spanish

FRA

French

ITA

Italian