Author: admin

Prerequisite

Before you start, make sure you have installed the following software:

* Java SE Development Kit (JDK) version 1.8.x (Java 8)
* Apache Maven https://goo.gl/EF1BVL #DataIntegration #ML

Dimension members are one of the basic concepts in any analytics application. Every analytical app, dashboard or self-service tool contains member lists: inside of a Crosstab, on a chart axis or legends, on a filter control and so forth.

Therefore one of the very first features in Design Studio was an API to retrieve a member list and display it in a listbox, combobox etc. The API getMemberList is in the meantime replaced by getMembers. Let’s assume that our data source DS_1 contains a dimension “customer_id” and we have a listbox LISTBOX_1 that we want to fill with the members from dimension “customer_id”. This is easy and cab be done with the following script: var members = DS_1.getMembers(“customer_id”, 100); members.forEach(function(member) { LISTBOX_1.addItem(member.internalKey, member.text); });

What is interesting with this script?

* The functions has an argument to limit the number of members to be fetched. Dimensions can have a huge number of members and retrieving them can be very expensive.

* getMembers returns a array of quite interesting objects. They contains quite interesting fields and one method

The fields of a Member objects contain so-called presentations. Each member could have several, including several key types and texts with different length. Which text is returned by the “text” field could be e.g. configured in BEx Query Designer.

Very powerful is the function getAttributeMember. It allows you to access content from a so-called Display Attribute for each member. Let’s extend our sample to also show the birthdates of our customers: var members = DS_1.getMembers(“customer_id”, 100, [“birthdate”]); members.forEach(function(member) { var birthdayAttributeMember = member.getAttributeMember(“birthdate”); LISTBOX_1.addItem(member.internalKey, member.text + “ (” + birthdayAttributeMember.text + “)”); });

You see that I have added “birthdate” twice – ones as an array at the optional last parameter of getMembers. And as argument of getAttribributeMember. If I would remove the third parameter of getMembers, the script would still work, but would run much much slower! If you pass the display attributes that you need to getMembers, they will be retrieved with as part of the single roundtrip from BW or HANA. If you try to get them later, you might end up with an additional roundtrip per customer!

Also attribute members have multiple presentations, including keys and texts, therefore you have the same fields available. But and attribute member can’t reference another attribute member.

Filter Behavior

Let’s now see how our member list reacts on active filters. If I modify my script like this, what happens? Will getMembers return only one entry? DS_1.setFilter(“customer_id”, “3872”); var members = DS_1.getMembers(“customer_id”, 100); members.forEach(function(member) { LISTBOX_1.addItem(member.internalKey, member.text ); });

No! The result is completely unchanged. Strange, isn’t it? But if I have a Crosstab or Chart in my app, I’ll see that the filter is work. It should only one customer.

Let’s try something else: DS_1.setFilter(“city”, “Albany”); var members = DS_1.getMembers(“customer_id”, 100); members.forEach(function(member) { LISTBOX_1.addItem(member.internalKey, member.text ); });

Now we might get a different result – or even the same. It depends on the “Member Access Mode” or “Read Mode of Members for Filtering”. You can configure it per dimension, e.g. in Initial View Editor:

You see that I have configured “Values in Master Data”. This means that filters have no influence on member lists at all, they contain always all members from master data.

The available modes depend on the data source type. The picture shows a HANA view. BW queries and Lumira Offline Datasets have different modes.

In Lumira 2.1 you can also set the Member Access Mode also with scripting: DS_1.setMemberAccessMode(“customer_id”, “MODE_POSTED_VALUES”); DS_1.setFilter(“city”, “Albany”); var members = DS_1.getMembers(“customer_id”, 100); members.forEach(function(member) { LISTBOX_1.addItem(member.internalKey, member.text ); });

Now my listbox will only contain customers from Albany. Obviously the result of getMembers depends on filters of other dimensions but not on filters of the current dimension itself.

Members for Filtering

This behavior is by intention. APIs like getMembers are intended to provide some UIs for choosing the members for filter operations. If the memberlist itself would be filtered, the UI would be unusable – after you have filtered for a member you can never come back to another filter state.

You can nicely try it out using several list boxes to simulate a Facet Filter component. Each listbox is filled with members from a different dimension. Clicking some entries would include the member into the filter. You could achieve this with scripting. Much easier however is to use Property Binding. Internally Property Binding uses exactly the same functionality as getMembers.

Create a listbox and drag-and-drop the data source component to it. You will be prompted for a dimension. Repeat the step for all other dimensions. Configure all dimension using “Only values with Posted Data”. In Initial View editor you might need to but them to the “Background Filter” basket.

As a result you have an app with cascading facet filters.

Members from Resulset

We learned that getMembers works, but it is intended to create UIs  that configure filters. Therefore in many cases it might get the wrong result and it is unnecessarily time consuming.

If you want to get exactly the members that you see in a Crosstab or Chart, you should retrieve the members from the resultset. This has two advantages:

* All filters are applied. You get exactly the members that you see in the Crosstab.
* It is much faster, as the resultset is usually anyway available.

Use the following script. Details about the resultset and the API getDataSelections can be found in this blog. var selections = DS_1.getDataSelections({ “customer_id”: “?” }); selections.forEach(function(selection) { var member = DS_1.getMember(“customer_id”, selection); LISTBOX_1.addItem(member.internalKey, member.text); });

With “member” you can do the same things as show above, e.g. retrieving other presentations and display attributes. However you should avoid using display attributes that are currently not shown in your resultset, as this could cause extra round trip to BW or HANA.

The members are retrieved as if you would search in a Crosstab in English reading order: first left-to-right then top-to-bottom. If the dimension is not the first on a its axis, the order might be a bit unexpected. The duplicate members that could also appear in such a case are automatically removed. The following picture shows this case: The “customer_id” dimension is placed as second dimension in rows axis after “city” using Key and Text presentations.

Getting a Single Member

Sometimes you have a member key and you need some presentation (e.g. the text) or a Display Attribute. The following snippet uses a constant key, but you can also use a variable. var member = DS_1.getMember(“customer_id”, { “customer_id”: “3872” }); APPLICATION.alert(member.text + “ ” + member.getAttributeMember(“birthdate”).text);

Again the warning: getAttributeMember can make the app very slow if used to frequently on data that is not yet cached.

Conclusion

It is important to understand that there are two ways how you can retrieve members:

* for filter UIs using getMembers
* from resulset using getDataSelections and getMember

I have explained the differences, advantages and disadvantages. I’m curious if there are other use cases that require new features or APIs for coming release. I’m looking forward to your comments.

  http://bit.ly/2DKxeNz #SAP #SAPCloud #AI

Hi,

This is Srinivas Dayana, Working for Cognizant Technology Solutions. I have 10 years of experience in BW and ABAP. I am working on SAP HANA for the past 4 years. As part of the Project requirement, we are currently Simplify few Processes and even want to use the optimised BW objects like ADSO and Composite Provider which will ease the migration from BW 7.4 to BW 7.5.

Summary:

As part of the Project requirement we have build a new data model using the ADSO and Composite Providers and we came across the scenario where there are almost 100 workbooks which are created by Business and all the workbooks will be effected when we switch from old Model to New Data model and Business have to recreate all the workbooks again which will effect Business reporting. So we are using the below conversion tool to convert the Multiprovider to Composite Provider and then enhance the Composite provider with the newly created composite provider so that none of the Queries will be effected and there will not be any impact on the workbooks which will save  lot of time for the business users.

Key Outcomes:

* Easy conversion of Multiprovider to Composite Provider
* Pros of using the below conversion tools
* Points to remember while using the approach

Introduction: We have Business scenario to convert the old BW Multiproviders to Composite Provider, so that we can use some additional functionalities like Join and perform operations on top of the existing MPRO’s. Business has so many Queries and Workbooks that are created on the same and we don’t want to change the existing Reports and Workbooks as that may have impact on the Business reporting.

This Conversion method will ease the Process of converting the Multiproviders to Composite provider with out having impact on Queries and Workbook.

The Below is the step by step Procedure to convert multiprovider to composite Provider:

Step 1: Create a new multiprovider for testing purpose

Step 2: Select the required sources for the multiprovider

Step 3: Identify the Fields that are required and the source from which the data should be populated for the field

Step 4: Activate the Multiprovider

Step 5: Execute the program RSO_CONVERT_IPRO_TO_HCPR

Step 6: Select the Infoprovider which we want to convert to Composite Provider. In this case we will use the multiprovider, which we have created for the test purpose.

Step 7: In the above screenshot , provide the name of the composite provider

* If we want the infoprovider name and the composite provider name to be same then give the same name as the Infoprovider
* We can also give a new name depending on the requirement

*In this case we want to create the Composite provider with the same name, as none of the existing Queries or Workbooks will not get effected.

Step 8: We cannot execute the above Program without giving backup provider name

Step 9: Provide the Backup infoprovider name  and execute in Simulate mode to check whether the Infoprovider can be converted to Composite provider or not.

Step 10: if there are no errors then we can go ahead with the conversion of Multiprovider to  a Composite Provider

Step 11: Execute the Program providing all the required Inputs and use the setting “Convert Infoprovider and copy Queries”

Step 12: Select the required Queries that we want to backup.

Step 13: Select the Required fields for the Queries

Step 14: Provide the Backup Query name, we cannot give the Query name same as the Backup Query  name, in the below example the report 3 will give the error as we have given the same name

Step 15: Provide the proper backup Query name and Proceed

Step 16: Check if there are no errors and confirm

Step 17: Multiprovider is now converted to a Composite Provider

Step 18: Open Query Designer and check for the Target and we can see one set of actual Queries and another set of Backup Queries

Step 19: Test the report before and after the changes

*Output of Query on multiprovider

*Output of Query on Composite Provider

Note: If we collect all the changes in the same Transport then we need to import the changes twice and if everything works fine and if we want to delete the Backup Queries, collect the Queries in separate Transport and import them separately.

The conversion is successful and in case of issues and we need to restore back to the old MPRO , please execute step 20.

Step 20: If the Conversion has some issues and the reports are not working fine we have setting to restore the backup. Select the setting “Restore backup” so that we will be converting back to the Old MPRO.

Note: The setting should be same as the setting during conversion. We need to Provide the exact same setting that we have provided when we have initially converted the Multiprovider to Composite provider or else Restore will not work, so make sure we have backup of the settings. We always need to select the Overwrite Existing HCPR option while doing the restore backup option

Step 21: We can see the Composite provider is set back again to multiprovider

Benefits of this Approach

* Easy Conversion of Multiprovider to Composite provider
* None of the Queries will be effected due to the conversion from Multiprovider to Composite provider
* Use the additional functionalities of composite provider
* None of the workbooks created on the Queries will be effected, so there will not be much impact on the Business due to the conversion

Hope this post is useful and i will update further post on SAP BW and SAP HANA when i have some new learning. http://bit.ly/2DKwisx #SAP #SAPCloud #AI