SAC Analytic Application - Scripting for Beginners: Part 1 – How-To read and change Master Data of Dimensions

[swinkler169]

When working with SAC and setting up an Analytic Application, you will probably come across scripting.  If you are not an expert in Java Script, it will probably take some time until you are familiar with this script language and therewith also until you are familiar with scripting in SAC.

With a lot of searches in Google and trial and error you will reach your goal certainly. This is also how it worked out for me. This blog should help you to save some time to find the suitable solution for your challenges. I picked some standard use cases that the most implementer will come across when creating an Analytic Application.

Each part of this blog will cope with the following cases:

• Part 1 – How-To read and change Master Data of Dimensions


• Part 2 – How-to hand over Parameters for Data Actions


• Part 3 – How-to handle and calculate Dates


• Part 4 – How-to use results from charts

In my use case, I want to create an application, where the user can enter their steps and activities they did per day and compare to other users. This will be a kind of activity challenge in the end. In this part, I will shorty explain how to read and change the properties of a dimension in SAC. This is needed right at the beginning to identify the user, which is opening the Application.

If you have an SAP BW background as I do, the requirement to read the master data of a dimension sounds rather easy. And it is even easy, if you know which objects you need to create in your Analytic Application as for example a Planning Model and if you are familiar with the syntax.

First, I am going to explain how to read from the Master Data of the Dimension ‘User’. Second, I will show hot to change the Master Data of the Dimension ‘User’, but let’s start with reading.

1.      Read Master Data

1.1.    The Dimension ‘User’

We have the Dimension ‘User’ in a Model that is used in my Analytic Application. The Dimension ‘User’ has an ID, a Description, Team, TargetSteps and Tech_Name as properties. This is how it looks like:

The Team is used for a Hierarchy which is called H1 and the Technical Name equals the System User.

As I know the system user of the user currently running the Analytic Application, I want to get the Member ID for this user to filter my charts on this specific user.

If Speedy Gonzalez runs my Analytic Application, I know that his System User has the Technical Name “SPEEDYGONZALEZ”, but I need the Member ID “SG” for filtering the charts. This is the string, that is needed for filtering the User in case of Speedy Gonzalez: “[User].[H1].&[SG]”.

1.2.    Create a Planning Model in the Analytic Application

Go to the section “Planning Models” in the Outline and add a new Planning Model. Now click on the three dots on the right of the new Planning Model and enter a model which includes all relevant dimensions, where you want to read or change the properties.

1.3.    Create Variables for the User ID

Of course, we could also use a local variable in the script. However, I chose a global Variable to reuse it in Analytic Application. Once it is filled, it will have the same value during runtime.

In the Outline go to the section "Script Variables" and add a new Variable of type string.

1.4.    Create Script in Analytic Application

As I am filtering on the User, I want to do it right in the beginning of running the Analytic Application. To do so, I add the coding onInitialization of the Canvas.

Here, I add the following coding:

//get the system user

var user = Application.getUserInfo().id;



//get Properties of Dimension "User"

var g_user_prop = PlanningModel_1.getMembers("User");



//loop at all users, if Technical Name = System User, fill relevant Variables

for (var i=0;i<g_user_prop.length;i++){

var tech_name = g_user_prop[i].properties["Tech_Name"];



if (tech_name === user)

{  Var_User_Hier = "[User].[H1].&[" + g_user_prop[i].id + "]";};};



//set the filter on User

Chart_1.getDataSource().setDimensionFilter("User",Var_User_Hier);

When initializing the Analytic Application now, the System User is taken, the respective ID is derived and the Chart_1 is filtered on the derived User.

2.      Change Master Data

Now that we know how to read the Master Data, I am going to explain how to change it from the Analytic Application with the help of scripting.

Next to the ID, the description, the Team and Technical Name the Dimension also has a property which is called ‘Target Steps per Day’ (TargetSteps).

In the Analytic Application I would like to enable the user to change the number of steps, which is saved there, on their own. To do so, the Planning Model as introduced in 1.2. is reused again.

2.1.    Popup

A new Popup can be created by clicking on the Plus symbol right next to “Popups” in the Outline.

On the Popup, I added an InputField which is called Input_ NewTarSteps.

I also enable the Buttons ‘OK’ and ‘Cancel’ on the Popup.

2.2.    Create Script in Analytic Application

I want the Target Steps, that are entered in the Input Field to be saved, when the user clicks on ‘OK’ in the Popup.

Before adding the script, I also created a variable, which is called g_properties and which is of type ‘PlanningModelMember’.

Now, I add the coding as OnClick for the Popup.

//only execute script if the user clicks on OK

if ( buttonId === "button1" ) {



//save new target steps to Dimension User Properties

var target_steps = Input_NewTarSteps.getValue();

g_properties=({id:Var_UserID,properties:{TargetSteps:target_steps}});

PlanningModel_1.updateMembers("User",g_properties);

Popup_1.close();};

If the user enters Target Steps in the Input Field and presses the OK button, the Dimension ‘User’ will be updated in the background.

Now I can easily filter the Charts and Input Fields in my Application to enable the user to enter his/her steps and activities and to set user relevant information as for example the target steps for themselves. In the next part I will shortly explain how to hand over parameters from an application to a Data Action.

Link to Part 2: https://blogs.sap.com/2021/12/06/sac-analytic-application-scripting-for-beginners-part-2-how-to-hand...

[former_member4854]

Nice information.

[ivan_camac]

Hi Svenja,

Just one point to note is that the getMembers method only returns the first 200 members for a dimension by default. In your example with only 3 records this will of course work fine, but as soon as you exceeded 200 members you would need to increase the threshold. You will however begin to notice a degradation in performance. Also as the number of members increases your loop will logically also take longer to execute.

As mentioned in the SAC Developer Handbook, it is suggested to use getResultSet() instead of getMembers for performance, however this then assumes that the value you are looking for is in fact in the current result set.

Taking this approach, you could include a hidden table in your application, containing just a single measure and the required dimensions in the drill state.

For the example script below I have used a standard BW Live model (not a planning model) based on a query essentially over 0EMPLOYEE in BW. Field references are adapted accordingly. This assumes a 1:1 relationship for the Username > Employee record. Script would be as follows:

//reference to table datasource

var ds = Table_1.getDataSource();



//set filter on datasource using current user id

ds.setDimensionFilter("0USERNAME",Application.getUserInfo().id);



//get resultset for first measure

var perno = ds.getResultSet()[0];



//error handling if no result found

if (perno !== undefined) {

        //assign name of user from 0EMPLOYEE to global variable

	username = perno["0EMPLOYEE"].description;

} else {

	username = 'no employee found';

}

The benefit is that there is no record limit to this approach as there will always be for getMembers. The data is being first filtered by BW with setDimensionFilter and the result set will only include the matching records. This has a secondary benefit in that you then don't need to loop through all the members to find your match.

This approach assumes a Live BW connection on top of a dimensional query (but should equally work for your planning model). The dimension you need to filter / search on needs to be navigational, but doesn't have to be in the drill state for the working table. The dimension you want to retrieve does need to be in the table drill state, although technically you could then use any non-navigational property belonging to the dimension you are retrieving, as long as you have it displayed in the result set.

Finally, you can potentially add further small performance gains by changing the Data Refresh property on the working table to 'Refresh Active Widgets Only'. This will still refresh the data when you call the getResultSet method on the table, but most other story interactions should not cause a data refresh / server roundtrip.

Cheers, Ivan.

[mohammedkhajamohiuddin]

How did you create a planning model on live connection?

[swinkler169]

Hi mohammedkhajamohiuddin

it is not a live connection, it is all done in SAC so it is native planning.

BR Svenja

[stephlef]

Hello Svenja,

And thank you for your wonderful article. At the time I'm reading it, the links to the 2nd blog post is not working for persons without editing rights.

Cheers,

Stéphane

[swinkler169]

Hi stephlef

now the issue should be solved and you should be able to use the link.

Best regards

Svenja

[Krishna_Prasad]

Hi Svenja,

I have read your blog on "Part 1 – How-To read and change Master Data of Dimensions". I have a similar requirement and I tried to implement it by referring to your blog but could not.

Kindly create a blog on below requirement, I have come across this requirement in different use cases.

 

Requirement: Restrict the planning period based on version Attributes.

Version dimension has two properties Start Period "202201" End Period "202212" .

Based on the version(public) restricted in Table, the date dimension should be automatically restricted to Date range "202201" to "202212" by reading the version properties when the application is initialized.

 

Regards,

Krishna

[Sujit]

Hi swinkler169 ,

For some reason the link to the second blog does not work, if you are not logged on to SAP Blogs website, it takes you to the login page, and once logged in displays a message saying something like 'You do not have access to edit this page'

I guess this is due to the "action=edit" at the end of the URL.

Could you please look into it.

 

Regards,

Sujit Honrao

[pradeepkumardeva]

Hello Svenja,

I have read your blog on "Part 1 – How-To read and change Master Data of Dimensions". I have a similar requirement and I tried to implement it, But unfortunately. I am able to update the master data and along with Properties.

Here it is my model structure is 3 Dimensions only. (Version, Transaction Date (Date)and ID (Generic) along with ID`s Properties)

User Requirement is they want to update/new record only for forecast data.

We have created one analytical application and created a popup as a new record creation by using Java Script here we need to update new record (ID along with ID`s Properties) along with Transaction Date itself.

Here we have facing issue with unable include Transactional Date in the script, But Master ID and Properties are updated properly only issue with Date.

Kindly help on this, how to write the Code, Is there any mistake on code (New for me JAVA Script)

Note : Below Code is updated master data perfectly, in this code how to include Transaction Date and update the record.

Please refer screens

 

 

Thank you very much.