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

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.

• @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

Uploaded Stories Permissions be stored in below table.. which Group Name displayed as to be assigned for each User in SAP Commissions.. SAP IAS receive the same group Name without assigning manually by an admin and again sends to EA.

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.