The data model for the Human Capital Management application looks similar to what is shown in the following diagram:
There are six tables in this application:
Employees: This is the main table, which has one record per employee and month. It contains all the relevant information about the employee such as country, position, salary, and so on. It would probably be a cleaner data model if this table were split into one table containing employee information that doesn't change over time and another table with the time-dependent information.However, since the QIX engine analyzes the data just as efficiently either way, we don't see any great benefit in spending time structuring the data more.
Preview of the Employees table
Hierarchy: This table contains information about the hierarchy of the employee, such as who the manager of the employee is, and who the VP of the employee is.Survey: This table contains the results from an external survey made on employee satisfaction. Such surveys are usually made once in a year, so if the results from several surveys are kept in this table, the key needs to hold information not only about the employee but also about the year in which the survey was made.Training: This table contains information about the training sessions attended. Hence, if an employee has attended two courses, two records are stored. The table also contains costs associated with the training sessions.Courses: The possible courses are stored in this separate table.Map shapes: This table lists all countries. It has one record per country and could, in principle, hold demographic information about the country. However, in this case, it only holds the map information—the shapes of the countries—used in the map object, which is in the user interface.
Note that this application has fields used for measures in several tables: salary can be found in the Employees table, cost for training can be found in the Training table, and ratings from the survey can be found in the Survey table. This is in sharp contrast to classic BI tools, where all such facts need to be in one single table, the Facts table.
There are many fields that can be used as dimensions, and a large number of them have been added as dimensions to Library such as Employee Name, Age Group, Department, and so on.
In principle, any field that a user would want to use as a grouping symbol should be added as a dimension. However, you should not add keys with cryptic names or numbers that should be used as measures.
The dimensions in Library
A number of measures have also been defined, for example, # of Employees, # of Women, Attrition, # of New Hires, Avg Compensation, and so on.
It is important that the app developer writes the formula correctly, since this is something that could be difficult for a business user. A business user doesn't always have knowledge about the data model, which is something you need in order to get all the expressions right.
In the following table, you can find some of the measures defined in this app:
|
Measure |
Definition |
|---|---|
|
# of Employees |
|
|
# of Women |
|
|
Attrition |
|
|
Avg Compensation |
|
|
Completed Training ratio |
|
|
Employee Satisfaction Ratio |
|
|
New Hires ratio |
|
|
Terminations |
|
|
Wages Amount |
|
Finally, there are also a number of visualizations added to Library. These are important, as they help a business user in the initial use of the app.
The most common bar charts and tree maps have been stored here: Number of employees by role, Number of employees by management position, and so on.
The list of visualizations in Library