Modeling SAP SuccessFactors Data in SAP Datasphere
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.
- 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.
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 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.
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.
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.
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.
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.
I understand that E/R model in SAP Datasphere for sap Successfactors doesn’t have the standard relationship between the different entities of SuccessFactors:
so customer have to start from scratch like for any other third party provider
The E/R model doesn't have the standard relationship between the different entities of SuccessFactors. Still, it will help you to create associations between entities directly in the diagram, and after deploying, it will modify your entities' properties. It will work the other way around if you have already defined association in the entities, then it will automatically reflect in the E/R model.
Thanks for sharing nice series on SuccessFactor and Datasphere.
Hello Premnarayan Patidar, Thanks for your feedback!
Hi Lalit Mohan Sharma, thanks for your awesome contribution in this space. Your well-structured sample combining two very significant SAP Cloud Solutions is exemplary and serves as a great reference for the community. I appreciate your efforts and look forward to seeing more of your work in the future. Kudos!
Thanks for that friendly feedback!