The fundamental objective of data modeling is to expose data that holds value for the end user. For data modeling, it is essential to prepare the data set in such a way that can answer the business requirements and also how different types of data can be modeled to create optimal conditions for data analysis.
An excellent business example would be to evaluate the workforce and determine what actions need to be taken to ensure that your company has the right people in place at the right time. Digging deeper, like
- Analyse, forecast, and plan workforce supply and demand
- Assess workforce gaps
- Determine target talent management interventions and create action plans
- Create an optimal workforce mix that aligns with financial goals etc
All such requirements require analysing, cleansing the raw data and filtering for data analysis. This is hard to do with just a single query, and that is why it is essential to execute these processes before analysis. With the objectives outlined, database tables can be defined with required aggregated fields These definitions become part of a data dictionary which is an integral part of any successful data model.
An HR key performance indicator or metric is a measurable value that helps track pre-defined organizational goals of human resources management and to optimize recruiting processes, employee engagement, turnover rates, training costs, etc.
Let's talk about a simple KPI or a metric for Talent Analysis.
Check the team's potential by giving insights into employee performance metrics based on locations, business units and departments. [This business requirements defines the business objective at a high level and is non-technical].
With additional details, it could be defined as
Get the rating of all employees and categorize them under the categorizes like Top performers, Emerging Performers and, Performers - who need improvement based on Location, Business Unit and Department. [This requirement defines the technical objective and provides technical definition]
Or with additional data object entity details, it could be defined as
Get the employee information, such as first and last names, gender, etc., from the PerPersonal table and for department, location, and business unit, from the EmpJob table. Then, Retrieve all employee ratings from FormHeader for FormTempleteType equal to "review.". Create a new field called Category to categorize employees as per ratings. At last, filter them by department, business unit, or location. [Here, the requirements provide technical entity details in SAP SuccessFactors required for Data modeling]
Using SAP Datasphere, you can model your data using graphical, drag-and-drop views or SQL statements. In this blog post, let's look at how we can model the SAP SuccessFactors data using these different options to derive analytical views for the Talent Analysis Metrics.
Blog post series for our learning Journey with SAP Datasphere and SAP Analytics Cloud together to handle complex analytics scenarios.
- Connecting SAP SuccessFactors and SAP Datasphere (click here)
- Bring SAP SuccessFactor Data into SAP Datasphere (click here)
- Modeling SAP SuccessFactors Data in SAP Datasphere (this blog)
- Building Analytic Models for SAP SuccessFactors KPI (click here)
- Build SAP Analytics Cloud’s Dashboard for SAP SuccessFactors KPI (click here)
Prerequisite
- SAP BTP global account setup with SAP Datasphere service instance. Follow the excellent blog How to create SAP Datasphere service instance in the SAP BTP Platform?
- A SAP SuccessFactors instance.
Let’s proceed Step by Step
Before we start, ensure that you have followed and completed the steps mentioned in my previous article(click here), which will help you to connect SAP SuccessFactors and SAP Datasphere.
Also, please follow the second article(click here) to import the following SAP SuccessFactors tables and replicate their data in SAP Datasphere.
Once you have imported the data, you should be able to see the list of remote tables as shown below: 
Now, let us model the data using all different approaches:
Modeling data using SQL Statements
In SAP Datasphere's powerful SQL editor, you can create a view to query sources. You can use SQL Script to create a table function or write a conventional SQL query using SELECT statements and operators like JOIN and UNION.
In Data Builder, take the following actions to begin modeling your data using the SQL Statement. On welcome page, select Data Builder in the left side menu and select the space where you want to model your data. 
Then, click on New SQL View.
On the left side of the screen, you can see a list of your local and remote tables and views. You can type your SQL code. It will offer suggestions as you input your code. Suggestions include SQL commands as well as table and column names found in your sources. By doing so, you can move faster and avoid typos.
When required, you can preview the data by selecting the View icon from the View dropdown menu at the top, and to validate your code, you can click on the shield icon. 
Below is an example of SQL code which fetches all the latest records from table T_FODepartment with fields externalCode, startDate, and department names.
Select D."externalCode", Max(D."startDate") as startDate, D."name" From "T_FODepartment" as D GROUP BY D."externalCode", D."name"
Dimensions are entities that contain master data that categorize and group the numerical data contained in your measures. In the Semantic Usage, if you select Dimension, it indicates that the entity contains attributes that can be used to analyse and categorize measures defined in other entities.
To do this you will have to define the data model type as a Dimension in the model properties sidebar of the view and toggle on the option Expose for consumption. 
Once the SQL code is complete, you can click the shield to validate it. Select the view icon from the view dropdown menu at the top to preview the data. When finished and confirm your code is valid, Save and Deploy your data model.
Please note that the goal of running the above query is to get the most recent and unique records from T_FODepartment. You can execute a similar query to get the necessary columns from other tables. Executing a query on every table in this situation won't be possible here in this blog post. Please check the snapshot below to see all the views we have built for our data model.
Modeling data using Graphical View
The graphical view builder in SAP Datasphere makes it simple to develop data views. You may proceed logically without requiring to be familiar with SQL statements.
In the graphical view builder, you have many resources to model your data, combine them from many sources and assigning business semantics that make your output easier to understand.
Go to the Data Builder and click on the New Graphical View button. 
As you imported Remote tables from SAP SuccessFactors, your data is under Repository, on the top right-hand side of the screen.
To start building your model, click and drag the T_FormHeader table onto the canvas. 
As you can see, an output node appears as soon as you drop your table on the canvas. Click on the output node and then click on the data preview button to see a preview of the T_FormHeader data.
To add a new calculated column, click the Fx icon next to the output, a projection, or a join node. 
Then, click on the plus icon in the Calculated Elements Properties sidebar on the right. By doing this, you have added a new calculated column. Next, we will look at how to set up the calculation that will show data in this column. 
Now it is time to add technical and business name for the column and write a formula in the expression box as a logic which defines the column. Based on the formula you use, the system suggests the data type. You can also adjust the data type later if required.
As we discussed at the beginning, we need to categorize employees like Top performers, Emerging Performers and, Performers - who need improvement as per the rating and create a new field called Category.
Considering this, it is time to use the code editor to write a calculated expression as a logic defining the new calculated column.
CASE WHEN rating >0 AND rating < 3 THEN 'Performers - need improvement' WHEN rating >=3 AND rating <=4 THEN 'Emerging Performers' WHEN rating <=5 THEN 'Top Performers' ELSE 'Newly Joined' END
You can validate the above expression using the Validate option and preview data to confirm if the new column has been added. 
You have successfully added the calculated columns Category in your view.
Change the semantic usage to Analytical Dataset in the model properties tab on the right side of the screen. This will allow you to change attributes into measures. 
To convert an attribute into a measure, drag and drop the attribute into the measures area in the Model Properties tab. In this case, we have defined Form Data Status as a measure. 
Once you are done adding your semantic information and types, click on the save icon on the top left corner of the screen, then click on the deploy icon next to the save icon to deploy your model. 
Create an E/R model
An E/R model provides a diagram for organising your data entities (tables and views) in relation to one another. You can:
- Add entities from the repository
- Modify the properties of your entities and creating associations directly in the diagram.
- Save and deploy all the contents of your model with a single action.
The work that you do in an E/R model benefits all the users in your space as they can use the entities that you import or enhance as sources in their views.
Go to the Data Builder and click on the New Entity-Relationship Model button. 
This takes you into the graphical modeling interface. Click and drag the V_FormHeader onto the canvas Next, click and drag the V_PerPersonal to add more detailed information about Employee rating. 
After including the V_FormHeader View and the V_PerPersonal View from the SAP SuccessFactor dataset into the model, they must now be associated to each other.
Click on the V_FormHeader view and then click on the Create Association arrow icon. Drag it over to the V_PerPersonal View to create an association between these two views.
Ensure that the created association is between the columns FromSubjectId from V_FormHeader and PersonalExternalId from V_PerPersonal in the Association Properties panel.
Drag and drop to connect the columns from one table to the other if it’s not done already
Similarly, you can drag and drop other views or tables. These tables contain information which is useful when connected to the V_FormHeader. Please check the snapshot below to see all the views and the association among them we have built for our data model.
After joining all the views and tables, type a name into the Model Properties panel and click Save. Once that’s completed, you can click on the deploy icon. You created an Entity-Relationship model to create associations between the tables and views.
Summary
So, now you have seen how we can model the SAP SuccessFactors data using the available option in SAP DataSphere by adding semantic information to the entities and exposing them directly or combining, refining and enriching them.
What’s next?
In the next blog post, we will see how to use SAP Datasphere Analytic Model to allows multi-dimensional and semantically rich analytical modeling to answer business questions easier, faster and more efficiently.
Building Analytic Models for SAP Successfactors KPI
Keep your anticipation high for the upcoming blog posts. Stay curious!
Reference & Further Reading
Acquiring and Preparing Data in the Data Builder
Introducing Data Modeling in the Data Builder
Basic Data Modeling with a Graphical View
Model Data with SQL in SAP Datasphere
Create an ER Model in SAP Datasphere
Modeling Data in the Data Builder
For more information and related blog posts on the topic page for SAP Datasphere.
If you have questions about SAP DataSphere you can submit them in the Q&A area for SAP Datasphere in the SAP Community.
7 Comments
