Introducing the Fundamentals of Analytics Cubes and Fact Tables

An Online Analytical Processing (OLAP) Cube is a data structure that allows fast analysis of data. They structure data according to the multiple dimensions. A multi-dimensional cube for reporting on employees might be, for example, composed of 6 dimensions: Employee, Time, Department, Company, Division, and Location.

Employee Central provides a rich navigation experience, allowing users to see at a glance all information relating to an individual employee. You can also see employee history, for example, prior positions, managers, job details, and other information back to their hire date.

The Employee Central information is stored in a variety of tables. Reporting tools like table report builder and Advanced Reporting queries these tables. Table and Advanced Reporting report lists and pivots of lists of data extracted from Employee Central, with little to no transformation.

Transforming data is what allows you to move from Reporting into Analytics. Transforming allows you to:

• Allocate and distribute employees across a span of time.

• Create hierarchical relationships and allocate employees to those hierarchies.

• Aggregate and collate data at the time of transformation, providing faster performance at report runtime.

If you look at an employee’s Organizational Info and Compensation Info, each of these blocks hold current and historical data stored in separate tables. Employee Central uses Effective Start Date and Effective End Dates, and these fields provide the history of changes and when they occurred for the employee.

However, the start and end dates are only applicable to the individual table. If an employee transfers department (stored in the organizational info table) without a pay change (stored in the compensation info table), then only a new record (row) is entered in the organizational info table, the compensation table is unchanged.

For the cube, the value of each applicable field (dimension) needs to be captured at each change of the employee. The build of the complete history of the employee is time splicing. In the example, Hella Buhr is:

• Hired on 6/5/2005.

• Transferred to a new department on 2/2/2015.

• Has a compensation change on 8/19/2016.

Workforce Analytics on HANA takes all the records and aligns them into a single time sequence for Hella Buhr.

These time spliced records become the WFA on HANA Data Warehouse built specifically to optimize Analytics. This warehouse is in the form of a star schema with the employee represented in key/main Fact tables (shown here in yellow) linked to secondary tables containing the dimensional/hierarchical data. The WFA on HANA Data Warehouse then forms the basis for a 'cube' where all the employee data is collated and aggregated.