SAP Commissions – Embedded Analytics Configuration – Custom Data Model
In this blog, you will learn each steps on how to create your own Custom Data Model for your Embedded Analytics which will help Sales User to see their own data based on User Security defined by admin.
The Embedded Analytics (EA) application uses core SAP Analytics Cloud (SAC) as the platform for its analytics solution. In this blog, the term Embedded Analytics is used to represent the analytics solution. When a customer requires new stories that are not possible with the out-of -box data models, new data models can be created based on customer requirements.
Note: The core data models provided with EA cannot be modified.
Overview of the EA flow for Custom Data Model
(Click on Image for clear resolution)
Creating SQL Views in EXT Schema with Structured Privilege Check
Below example SQL will store all the payments in view
CREATE VIEW "EXT"."ALL_PAYMENTS"("PERIODSEQ", "POSITIONSEQ", "EARNINGGROUPID", "EARNINGCODEID", "VALUE") AS SELECT PERIODSEQ, POSITIONSEQ, EARNINGGROUPID, EARNINGCODEID, VALUE FROM CS_PAYMENT ORDER BY PERIODSEQ ASC with Structured privilege check;
Creating Structured Privileges on EXT Schema for Data Security
You must add the data level security explicitly to relevant EXT tables and views.
Refer to examples of Structured Privileges for data models included in the Reference section. To apply structured privileges to a custom data model in the EXT, you must create your own version of these structured privileges in the EXT schema. For every custom model that is added, update the custom structured privileges in the EXT schema to include the new data model.
The following is an example of how to create an org-level structured privilege:
CREATE STRUCTURED PRIVILEGE ext.ALL_PAYMENTS_VW FOR select on ext.ALL_PAYMENTS where positionseq in (SELECT RULEELEMENTOWNERSEQ FROM CS_POSITION WHERE TO_cHAR(RULEELEMENTOWNERSEQ) IN( select ds.value from csa_datasecurity ds where ds.removedate=to_date ('22000101','yyyymmdd') AND ds.securitytype in ('POS') and ds.userid=session_context('APPLICATIONUSER') union select pos.ruleelementownerseq from csa_datasecurity ds,cs_position pos where ds.removedate=to_date ('22000101','yyyymmdd') AND ds.securitytype in ('ALL') and pos.removedate=to_date ('22000101','yyyymmdd') and pos.effectivestartdate<=current_Date and pos.effectiveenddate>current_Date and ds.userid=session_context('APPLICATIONUSER') union SELECT RULEELEMENTOWNERSEQ FROM CS_POSITION pos WHERE POSITIONGROUPSEQ IN ( select ds.value from csa_datasecurity ds where removedate=to_date ('22000101','yyyymmdd') AND ds.securitytype in ('PG') and ds.userid=session_context('APPLICATIONUSER') ) and pos.effectivestartdate<=current_Date and pos.effectiveenddate>current_Date and pos.removedate=to_date ('22000101','yyyymmdd') ) ); commit;
Adding Structured Privileges Grants
You must give structured privilege access to the tenant user.
The following is an example of how to give structured privilege access, where 1234 is the name of tenant:
GRANT STRUCTURED PRIVILEGE ext.ALL_PAYMENTS_VW TO 0509
Creating a CSN File – Schema Notation (CSN)
CSN (pronounced as “Season“) is a notation for compact representations of CDS models — tailored to serve as an optimized format to share and interpret models with minimal footprint and dependencies.
It’s similar to JSON Schema but goes beyond JSON’s abilities, in order to capture full-blown Entity-Relationship Models and Extensions. This makes CSN models a perfect source to generate target models, such as OData/EDM or OpenAPI interfaces, as well as persistence models for SQL or NoSQL databases.
Refer to the Sample CSN File for examples.
Note the following conditions for the CSN file:
Set the context in the custom CSN file as EXT.
- The model name must follow the following pattern:
Model name: <BusinessName><CustomerCode>
Example: Credits SAP
The names of the models must not be similar to any of the out-of-box models. To ensure that each model is different, all custom model names must end with a customer code, which is an abbreviated version of the customer name. BusinessName is the name that the customer uses for the model.
@Common.Label annotation name must match the model_name specified during ModelServices upload. (case-sensitive)
- @Common.Label annotation must be before @Analytics.query annotation. (FilterModels logic in java code starts the CSN search from ModelName). You must read the data from the EXT schema.
- The CSN file must have at least one Measure defined.
Deploying the CSN File
The CSN file is a JSON format file that defines the metadata that is necessary for the physical tables/views to be exposed in Embedded Analytics UI.
Select the Report and provide Role Permissions
Run Update Analytics via Pipeline Calculation Job
Run the pipeline job with Update Analytics which will have data in all CSA_PA***** tables which validates to User Security on what data needs to be displayed based on your structured privilege you had defined in above steps.
Feel free to leave your comments after reading this blog.