OLAP cube and Excel

The OLAP OLAP databases (Online Analytical Processing) enable Business Intelligence queries. OLAP is a database technology that has been optimized not for processing transactions, but for query and report generation. The source for OLAP is OLTP (Online Transactional Processing) databases, which are usually stored in data warehouses. OLAP data is derived from this traditional data and is aggregated into structures that allow sophisticated analysis. OLAP data is also organized hierarchically and stored in cubes rather than tables. [MS Excel] cube offers the following advantages and options:

• The data are stored separately from the live database, so queries do not affect productive operation.

• Queries are very powerful because they are already aggregated according to many criteria in the cube. In the query itself, no functions are required to form totals or other key figures. Instead, the data can be read using the dimensions provided. Performance is therefore often the main reason for using an OLAP cube.

• It is possible to access historical data: A status is saved with each data generation. This makes it very easy to compare a particular sample with the previous month or year.

Drawbacks:

• A separate server is required for the cube (the productive instance should not be used for this).

• Due to the system, the data is only up-to-date with the last cube filling (by default once per night).

• Only data contained in the cube can be evaluated. A later addition is connected with programming effort (the generator must be adapted).

 

Connection with Excel

Microsoft Excel is a convenient tool for accessing the OLAP Cube and formulating queries. The following steps must be performed:

After starting Excel change to the menu item Data > Get Data >From Database > From Analysis Services.

Specify the server name of the SQL Server instance and click Next:

Select ticket cube and finish.

Overview: Ticket cube and pivot table fields

1. Pivot table Provides an interface that guides a user through multiple dialogs for ergonomic data entry. This helps you to fill out forms.

2. Measurement groups - aggregatable values

3. Dimensions - values/objects for filtering and grouping

4. Areas in which measures Numeric values in a cube that are based on a column in the fact table of the cube. Measures are the central values in the cube that are preprocessed, aggregated, and analyzed. Typical examples are sales, profits, revenues, and costs. [MS Excel] or dimensions One or more organized hierarchies of levels in a cube that the user understands and uses as a basis for data analysis. For example, a geographical dimension can contain levels for country/region, state/canton, and city. Or a time dimension can contain a hierarchy of levels for year, quarter, month, and day. In a PivotTable or PivotChart report, each hierarchy becomes a set of fields that can be expanded and reduced to display lower or higher levels. [MS Excel] can be included (measures are aggregatable values and can only be used in the Values area).

 

Status change

Based on the status change of the tickets

Tickets

Based on the tickets

Ticket times

Based on status change of tickets taking into account the service time stored in the ticket

Processing time: Consideration of status of status type In process

Waiting time: Consideration of the status of the Waiting status type

Reaction time: Time period between ticket creation and first status change to a status of the In process type

Dimensions - detail information, filter, grouping

Name	Description
Owner	Current owner of the ticket
Owner Group	Affected user of the ticket
Affected group	Current affected group
Date	Creation date of the ticket
Date aggregation	Returns functions to accumulate the values used over time.
Date aggregation until/Date aggregation from	Only in combination with date aggregation. Extends the date aggregation by a period of its own.
Date comparison	Provides functions to compare the values used over periods of different periods.
Creating Group	Created group of the ticket
Service	Hierarchy of service transactions: Service categories and service catalogs of the ticket
SLM escalation	Hierarchy of SLM priorities: SLM services and contracts of the ticket
Ticket	Ticket meta data
Ticket schema	Ticket schema of the ticket
Ticket status	Ticket status
Ticket status until	Filter and/or grouping option when using values from the status change measure group
Ticket status from	Filter and/or grouping option when using values from the status change measure group
Ticket value 1 – Ticket value 10	Ticket fields and values, up to 10 uses as grouping or filtering
Responsible group	Current responsible group of the ticket
Responsible	Current responsible user