Retrieving Property Values from Dimensions using Tables in Analytic Applications

[rohitchouhan]

In this blog post, I will provide you with a comprehensive and step-by-step explanation of how to retrieve property values from any dimension within SAP Analytics Cloud (SAC). I will walk you through the intricacies of the process, ensuring a thorough understanding of the methods and techniques involved.

There was already an easier way to retrieve properties using the Planning Model's getMembers() method. However, in the future they may restrict access to master data for non-generic dimensions.

If you are using getMembers with non-generic dimension you we see error like this in console:-

"Dimension type VersionDim is not supported"

As a result, we had to find an alternative method to obtain properties from dimensions, which led us to using tables in stories or analytical applications. Let's get started.

 

 

Prerequisites

• An active SAP Analytics Cloud account


• Access to a story or analytical application where you can create tables and buttons

Steps to Retrieve Property Values

1. Create a table in your story or analytical app:

• Open your story or create a new one in SAP Analytics Cloud.


• Add a table to the canvas.


• In the table, include all dimensions for which you need property values. For this example, let's consider the 'Version' dimension.
  
  Make sure that your tables have some data in table, so table will show properties as well. Even if your table is unbooked and have no data. it won't work. So you should have some data.

2. Show the property of the dimension:

• Right-click on the 'Version' dimension in the table.


• From the context menu, select 'Show/Hide' and then choose your desired property from the 'Properties' option.

3. Create a button and add the following JavaScript code:



Insert the following code in the onClick function:

/*

var selection = {

    "DIMENSION_NAME": "PROPERTY_ID"

};

*/



var selection = {

    "Version": "public.Actual"

};



var resultSet = Table_1.getDataSource().getResultSet(selection, 0, 1);



// Log the entire result set to the console

console.log(resultSet);



// Access the specific property value (e.g., START_DATE) from the result set

/* var startDate = resultSet[0]["DIMENSION_NAME"].properties["DIMENSION_NAME.PROPERTY_ID"]; */

var startDate = resultSet[0]["Version"].properties["Version.START_DATE"];



// Log the property value to the console

console.log(startDate);

// Output: 202312

Console Output

Explanation of the Code

The provided JavaScript code demonstrates how to obtain property values from the 'Version' dimension in SAC. First, we create a selection object specifying the desired dimension value. We then use the getResultSet() method to retrieve the data, limiting the result to one entry. Finally, we access the specific property value, in this case, 'START_DATE', from the result set and log it to the console.

Conclusion

By following these steps and utilizing the provided code, you can efficiently retrieve property values from any dimension in SAP Analytics Cloud. This method provides a reliable solution even after the 2024 Q4 update restrictions.

[N1kh1l]

rohitchouhan

This workaround of using hidden tables was already in practice but this  is a crude way of solutioning. Version is a key dimension in any planning project and getmembers() of planning model being restricted for a key dimension of the model is simply illogical. I hope SAP rectifies the API and continues to support it on Version dimension in particular. Anyway Thanks for the guided explanation of alternative solution. Many would be looking for this as lot of production use cases would be already lit up.

 

Br.

Nikhil

[zeenat_17]

Hi Rohit,

Hoping that SAP can continue to support API to get the Version dimension in particular.

But for now, I have followed the instructions provided here and I have used the Analytical Application page which lets user select the Version and then also helps deriving the Date filter for the Stories that it will open on the button click.

I have added the Version dimension and its properties Start Week and the End Week on the table on Analytical Application page

 

and also changed the code as explained, but I could noticed that now the Version dimension selection on the Analytical Application page is letting user to select more than one version dimension member (check box) which is not the right thing and can lead to issues.

we would want only single selection of Version dimension member to be selected. How do you plan to manage/Control it?

 

[SorroAndres]

Thanks for the blog,

Our current implementation relied heavily on the getmembers() method for extracting properties from the version dim. I understand it said clearly from the beginning that only generic dimensions were supported, but it still worked, so I don't get the point of changing things that for sure have affected many implementations already on production systems.

Another option in the future if for some reason this stop working would be to work directly over the member ID with some text logic, not ideal but given the uncertainty of all the changes in each Q...

Once again, thanks for this, I'm starting corrections now over about 50 objetcs...

And by the way, if you can update your post and include this console error: "Dimension type VersionDim is not supported". This will help people coming from google to find this post directly 🙂

[rohitchouhan]

Hy Andres, thank you for the suggestion, i have updated my blog.

[maithili_parulekar_141]

Hello,

Thankyou for this solution, although this does not work in case of unbook data, and I have a use case where I need to retrieve the property with unbook data. Do anyone happen to have any solution on that?

[bastianbrindley]

Hi Maithili,

You can just create a calculated measure (e.g. containing 1). Add this measure to Your table and then also unbooked values will be captured by the resultSet().

Best regards,
Bastian

Dummy Measure