Why BICS?
In our blog post Consuming SAP BW queries externally we went through the “official” possibilities of accessing BW query data from ABAP code or external systems. All these options come with specific limitations. In this blog post we are going to have a look at a “less official” but much more powerful interface: BICS.
BICS stands for BI Consumer Services and provides an interface to execute queries and planning objects and fetch the result data. There are actually two such interfaces:
The “classic” BICS interface is accessible via RFC enabled function modules – and is used by Analysis for Office, BEx Web and some other clients to access the backend.
The “modern” alternative to this is the InA interface that is used e.g. by SAP Analytics Cloud to connect to BW for live-connect, and is provided as an HTTP based service. In the background of this service the class based BICS model is used. The service is exposed via the services under the SICF path /sap/bw/ina/ – these handler classes represent the “switches and levers” used to tell the system what to do, while the BICS classes behind the scene represent the logical model of a BW analytics or planning application.
The BICS Function modules
There is a set of function modules starting with BICS_. They are RFC enabled so that they can be executed directly from a remote connection. Functions in the name range BICS_CONS_… provide general features, while the ones with BICS_PROV_… are for manipulating data providers.
If you execute an RFC trace (ST01 or ST05) you will see that these function modules are used by SAPs standard clients – like Analysis for Office or BEX – to access BW. If you use RSTT for tracing, only the BW-relevant function modules are shown and the parameters are recorded as well.
Is your custom “client” being developed in ABAP or in an environment where you can call RFCs – e.g. from Java via JCo or .NET Connector? Then you could call these function modules to access BW the same way the standard clients do.
For example you could call BICS_PROV_OPEN to fire up a query of your choice, and store the four-digit application handle, data provider handle and variable container handles you receive back. Get the list of variables using BICS_PROV_VAR_GET_VARIABLES passing the application and variable container handle to get the list of variables, and call BICS_PROV_VAR_SET_VARIABLES to set the variable values. When done, call BICS_PROV_SUBMIT_VARIABLES to finally submit them.
If you would like to change the drilldown, set filters, modify member or result display properties, call BICS_PROV_SET_STATE. This might seem easy, but it is made a bit harder by the fact that there is no individual calls to achieve one or the other change – you need to pass literally all settings at once. And this means you need to have a local shadow copy of the full state in your client, with lots and lots of properties. How would you initalize this state? Luckily you can dowload it directly using BICS_PROV_GET_INITIAL_STATE. This will return all the state variables, which you can the change spot by spot, and finally send them back via BICS_PROV_SET_STATE.
When all is set, use BICS_PROV_GET_RESULT_SET to retrieve the result data in a multidimensional format.
If you need planning features you could use BICS_CONS_CREATE_PLANNING_SEQU to create a planning sequence instance within the application handle, and use the returned sequence handle to call BICS_CONS_EXECUTE_PLANNING_SEQ.
When tracing standard clients you will see a lot of calls to BICS_PROV_GET_MEMBERS. This is used to get members of dimensions and their texts and attributes. It can be called both for characteristics and variables (which will of course return the characteristic behind the variable.) Depending on your scenario you might need these calls to offer the user the selection possibilities, but if your selections are ‘static and hardcoded’ you probably do not need any of them.
Interpreting the result
All the data needed to display the result table can be found in the output parameters of BICS_PROV_GET_RESULT_SET, with the exception of structure texts.
The result format is not trivial to interpret, but it’s doable. The counters e_n_row and e_n_columns are your starting point. That is how you know the size of your logical result table. Then for each row you need to look up the header values in table e_t_rows, from there you need to go via the index to e_t_member to look up the members related to the axis tuples. But notice that elements in the row drilldown on the higher drilldown levels are not repeated, so watch for changes in the element_index column.
Once you have the member from e_t_meber, jump via the presentation index references to e_t_member_presentation to read the actual texts to be displayed. Watch out, there can be multiple entries – for text and key, and even for attributes.
The cells in e_t_data_cells contain the data part of the table. That is probably the easiest part.
Coming back to structures, you will find structure members in e_t_dyn_kf as part of the output of BICS_PROV_GET_INTIAL_STATE with an “element SID”, the GUID (ELTUID) of the structure element, and the corresponding text are in e_th_meta_characteristics-structure_member. The result of this call is also where you would look for the description of dimensions themselves (not the dimension member values.)
Verdict
The BICS function modules can be called via RFC from outside the system – or directly from ABAP code on the system. Opposed to the feature-limited interfaces seen in our previous blog post they are pretty complete and allow access to literally all setting and properties of the query navigation state, as well as integration of planning features.
Unfortunately the non-object-oriented interface and the hundreds of settings and properties make the interface a overcrowded. All the settings have to be at least temporarily stored on the client side if you want to make use of advanced features (drilldown, filter, display properties.) Also the output format is not easy to process, depending on which elements you actually want to display, you might need to search where you find them in the dozens of output tables.
On the other hand, as the standard clients use the function module API, it is very easy to trace a working example and figure out from there what functions you need to call in which order. And, it is a remote interface, out of the box, without the need write any “adapter code” to call it from outside.
One more hint: To call BICS function modules via REST service without additional programming you might have a look at SICF service /default_host/sap/bw/ina/ExecuteBICS.
BW InA Interface
As stated earlier, the InA services can also be seen in the SICF tree and are implemented by handler classes all inheriting from CL_BICS_INA_HTTP_HANDLER. You can put a break-point in the handle_request method of these classes to debug what is actually happenining inside when you carry out specific actions e.g. in SAC. You can even see these calls in an RSTT trace if you record one, however the parameters are encoded as a binary JSON string which requires further processing to decode.
The best spot to actually see what is going on is in the CL_BICS_INA_CONTROLLER->_HANDLE_REQUEST method. The requests are handled by processor classes with the naming convention CL_BICS_INA_PROC_… where you can find the translation of the request onto the actual object model.
The more familiar you are with the BICS classes the easier you will find to figure out new things: Once you know the class relevant for a particular feature, you can set some breakpoints in there and see how the methods are called during a real-life scenario.
We are not going into any more detail on the InA interface – it is most probably a too generic one for typical custom interfacing requirements. The important thing for us to understand is: SAC uses InA to connect, and InA uses the BICS classes in its belly to access BW. This should at minimum give us the hint that the BICS classes will probably not disappear from one release to the next.
The BICS Classes
As stated above, these classes represent a runtime data model of BW applications and their components: queries, planning objects etc.
Most of these classes are somewhere within the BW_BICS root package. It contains on one side BW_BICS_BASE holding the most important general objects like application, event, iterator etc. as well as contants, exception classes and base classes. The second subpackage is BW_BICS_CONSUMER which holds the “consumable” objects like data areas, planning objects, query views, results sets, metadata objects etc. A few objects with the naming scheme CL_BICS_… can also be found in the RSBOLAP package. The packages could be a good starting point if you are trying to implement a specific feature and looking for an object or method that could be used.
In contrast to the funtion module based interfance, classes have some benefits: they encapsulate the logical objects with their possible operations, they provide reference based access – without the need to struggle with numerical handles. They are much more fine-grained and self-explanatory. If you write a ‘linear’ piece of code that will use BICS to acquire some data, it is definitely more convenient to use the classes.
That does not mean that the function module based access is to be forgotten completely: Using the BICS function modules directly via RFC gives you the option to access data without the need of developing anything on the BW ABAP server itself – the code is already there, and RFC enabled. You just need to call it.
Programming with BICS classes
In this section we are going to have a quick look at how you could use the CL_BICS_… classes directly from a local ABAP program. For example this could be a small ABAP report that will execute a few queries, fetch the data, and call a web service of some government authority to submit some statistics automatically.
Getting an application instance
It all starts with an application, represented by CL_BICS_APPLICATION. This objects could be thought of as a separate ‘session’ or an isolated set of runtime data. Most likely, you will use a single application only. All the objects – queries, planning functions, planning sequences – are executed or exist in the context of this application. So first of all you need to get an instance of such an application.
An application instance could be created directly via CL_BICS_APPLICATION=>CREATE_APPLICATION, but it is probably a better approach to use the service class CL_BICS_START. It has two methods: GET_APPLICATION and CLOSE_APPLICATION. It implements a ‘singleton container’, so if an application instance already exists in the current user session, it will not create a new one, but return the existing one.
While an application is implemented by the class CL_BICS_APPLICATION, you will see that CL_BICS_START->GET_APPLICATION returns a reference of type IF_BICS_APPLICATION, which is an interface. This is a common programming pattern to limit access to the ‘officially supported methods’ of the class to the consuming code. Ideally you should stick to using references of the interface type to access the application object. If all else fails and the interface does not expose a method or property you need, you can cast the reference to the class type.
"Get application
DATA(lr_app) = cl_bics_start=>get_application( ).Accessing a query
Queries are represented by the class CL_BICS_QV where QV most probably stands for query view. You can add a query to an application by adding it as a service. For adding a service you need a service descriptor first, like so:
lv_query = 'ZMY_QUERY_ID_Q01'.
DATA(lr_srvdesc_qv) = CAST if_bics_qv_srv_descr( lr_app->create_service_descr( cl_bics_service_type=>n_cr_query_view ) ).
lr_srvdesc_qv->if_bics_selector_srv_descr~set_query( lv_query ).
DATA(lr_qv) = CAST cl_bics_qv( lr_app->create_service( lr_srvdesc_qv ) ).The actual object type you get back by the create_service method is of type CL_BICS_QV, which implements two main interface IF_BICS_QV and IF_BICS_MD_QV. The difference is that the one with MD in the name is the ‘design time’ version with the metadata like axes, characteristics, key-figure and structure members, whereas the one without MD in the name provides access to the actual values and has all the properties and actions which relate to a particular execution of a query. Depending on what you would like to access you might need to use the one ore the other interface.
Passing variables
Now that we have our query, how do we set variables? Let’s note that IF_BICS_QV also includes IF_BICS_SELECTOR which encapsulates all the methods related to selections, especially the method GET_VARIABLE_CONTAINER is of interest to us at the moment to get an object of type IF_BICS_VARIABLE_CONTAINER. Using the GET_VARIABLE method we can get a particular variable object as IF_BICS_VARIABLE, which can check the value or provide metadata about the variable, but it can’t get or set its value. This is because variables can be of different types, so the reference first needs to be cast to the appropriate type, e.g. IF_BICS_VARIABLE_CHAR_MEMBER for regular characteristics.
Note that we queried the variable by it’s alternative name. This is because the full name of the variable contains a numeric suffix (the handle of the OLAP query view.)
DATA(lr_vc) = lr_qv->if_bics_selector~get_variable_container( ).
DATA(lt_variables) = lr_vc->get_variables( ). "To see a list of available variables - will not be used below
DATA(lr_v_salorg) = CAST if_bics_variable_char_member( lr_vc->get_variable( i_alternative_name = 'Z_SALESORG' ) ).
lr_v_salorg->get_iobj_selection( )->add_single_member_selection( '0001' ).
lr_v_salorg->set_iobj_selection( ).
lr_vc->submit_variables( ).
IF lr_vc->check_values( ) = abap_false.
DATA(lt_msg) = lr_app->if_bics_srv~get_message_manager( )->get_messages_as_table( ).
ASSERT 1 = 2.
ENDIF.Getting the result set
The simplest option to get the result set is by getting a so-called flat result set with the method if_bics_qv~get_result_set_flat. This first requires you to configure a flat description which is an object holding a mapping table beween the ‘elements’ of your query result and the column names of your output table. This way the output table could be defined in any custom way you like, and its colums are populated from the query data based on the mapping. While some of the columns would hold characteristic values, others would be populated with numeric data from the data cells of the query.
When it comes to structures, things get problematic as usual. Structures will always get the internal runtime id 2000000908 (cl_bics_structure_type=>n_c_id_structure_1) for the first structure and 2000000907 (cl_bics_structure_type=>n_c_id_structure_2) for the second structure. The flat descriptor will ignore the first structure. If there is a second structure that will be added as a ‘characteristic-like’ column, while all combinations of the two structure’s elements are available as a data cell and get an own column. The best you can get out of this situation is to remove the ‘characteristic-like’ column. If you have two structures with 3 and 5 elements each plus a month drilldown in the rows, you can get out a table with 12 rows and 15+1 columns, but you would not be able to get a crosstab-like result with 12*3 rows and 5+2 columns.
The most flexibility you will have if accessing the result set data via the classic methods and properties as the below example shows. Depending on how generic you want your solution to be, you will either ‘transpose’ the data from the multidemnsional format into a flat thable here, or your output data structures will also be ‘multi-dimensional’. In our example below we are collecting all (non-empty) data cells of the query into a more easy
TRY.
DATA(lr_rs) = lr_qv->if_bics_qv~get_result_set( ).
CATCH cx_bics_result_set_empty.
" Empty Result Set
REFRESH et_dashboard_data.
RETURN.
CATCH cx_bics_no_authority.
" No authorization
RAISE EXCEPTION TYPE y0bw_cx_tp_exception EXPORTING errormsg = |Missing authorization when fetching query resultset|.
ENDTRY.
"Get the data cells
DATA(lr_data_cells) = lr_rs->get_data_cells( ).
FIELD-SYMBOLS <lt_data_cell> TYPE if_bics_rs_data_cell=>tn_ts_data_cell.
ASSIGN lr_data_cells->if_bics_data_container~n_r_data->* TO <lt_data_cell>.
"Get currency metadata
DATA(lt_currency_unit_info) = lr_rs->get_currency_unit_info( )->n_ts_currency_unit_info.
"Get the row and column tuples
"These are tables with a dynamic type. They contain as many columns as the number of drilldown characteristics on the axis, plus an additional tuple metadata field.
"The columns which represent a characteristic contain a substructure which points to the corresponding characteristic member by index.
"The tabls contain one row per tuple.
DATA(lr_row_tuples) = lr_rs->get_rows_axis( )->get_tuples( ).
DATA(lr_col_tuples) = lr_rs->get_columns_axis( )->get_tuples( ).
FIELD-SYMBOLS <lt_row_tuple> TYPE INDEX TABLE.
FIELD-SYMBOLS <lt_col_tuple> TYPE INDEX TABLE.
ASSIGN lr_row_tuples->if_bics_data_container~n_r_data->* TO <lt_row_tuple>.
ASSIGN lr_col_tuples->if_bics_data_container~n_r_data->* TO <lt_col_tuple>.
" Count the number of drilldown characteristics on the row & column axis
DATA(lv_row_char_count) = lr_qv->if_bics_qv~get_rows_axis( )->count_characteristics( ).
DATA(lv_col_char_count) = lr_qv->if_bics_qv~get_columns_axis( )->count_characteristics( ).
"Cell based processing
"In this scenario each cell will lead to an additional row in the output table, as the output table has just one value column
"and is in an 'account model'.
FIELD-SYMBOLS <ls_tuple> TYPE any.
FIELD-SYMBOLS <ls_tuple_element> TYPE if_bics_rs_axis_tuple_element=>tn_s_tuple_element.
FIELD-SYMBOLS <ls_tuple_info> TYPE if_bics_rs_axis_tuple=>tn_s_tuple_info.
DATA lt_cellchars TYPE tt_cellchars.
DATA ls_data LIKE LINE OF et_dashboard_data.
LOOP AT <lt_data_cell> ASSIGNING FIELD-SYMBOL(<ls_data_cell>).
"Do not transfer empty cells
IF <ls_data_cell>-value_exception = cl_bics_value_exception=>n_c_null.
CONTINUE.
ENDIF.
"---------------------------------------
"Technical processing of cell tuples into a reasonable format (lt_cellchars)
"---------------------------------------
"Get row and column tuples for this cell
READ TABLE <lt_row_tuple> ASSIGNING FIELD-SYMBOL(<ls_row_tuple>) INDEX <ls_data_cell>-row_index.
READ TABLE <lt_col_tuple> ASSIGNING FIELD-SYMBOL(<ls_col_tuple>) INDEX <ls_data_cell>-column_index.
"We will now collect the value of each characteristic - regardless whether it is on row or column axis - into a table
REFRESH lt_cellchars.
DO lv_row_char_count + lv_col_char_count TIMES.
IF sy-index <= lv_row_char_count.
DATA(lr_rs_axis) = lr_rs->get_rows_axis( ).
DATA(lr_qv_axis) = lr_qv->if_bics_qv~get_rows_axis( ).
DATA(lv_index) = sy-index.
ASSIGN <ls_row_tuple> TO <ls_tuple>.
ELSE.
lr_rs_axis = lr_rs->get_columns_axis( ).
lr_qv_axis = lr_qv->if_bics_qv~get_columns_axis( ).
lv_index = sy-index - lv_row_char_count.
ASSIGN <ls_col_tuple> TO <ls_tuple>.
ENDIF.
"Query characteristic
DATA(lr_qv_char) = lr_qv_axis->get_qv_characteristic( i_index = lv_index ).
"Tuple info
ASSIGN COMPONENT 'TUPLE_INFO' OF STRUCTURE <ls_tuple> TO <ls_tuple_info>.
"Tuple element
ASSIGN COMPONENT lv_index OF STRUCTURE <ls_tuple> TO <ls_tuple_element>.
"Add cell characteristic
DATA ls_cellchar TYPE ts_cellchars.
CLEAR ls_cellchar.
"Name
IF lr_qv_char IS INSTANCE OF if_bics_qv_structure.
DATA(lr_qv_struc) = CAST if_bics_qv_structure( lr_qv_char ).
IF lr_qv_struc->n_alternative_name IS NOT INITIAL.
ls_cellchar-chanm = lr_qv_struc->n_alternative_name.
ELSE.
ls_cellchar-chanm = lr_qv_char->if_bics_md_characteristic~n_s_md_characteristic-name.
ENDIF.
ELSE.
ls_cellchar-chanm = lr_qv_char->if_bics_md_characteristic~n_s_md_characteristic-name.
ENDIF.
"Sum or value?
IF <ls_tuple_info>-total_element = lv_index.
"This is a totals entry
ls_cellchar-issum = abap_true.
ELSE.
"Get the member
DATA(lr_membercontainer) = lr_rs_axis->get_members( lr_qv_char ).
DATA(lr_membertable) = lr_membercontainer->if_bics_data_container~n_r_data.
FIELD-SYMBOLS <lt_membertable> TYPE INDEX TABLE.
ASSIGN lr_membertable->* TO <lt_membertable>.
READ TABLE <lt_membertable> ASSIGNING FIELD-SYMBOL(<ls_membertable>) INDEX <ls_tuple_element>-member_index.
ASSIGN COMPONENT 'KEY' OF STRUCTURE <ls_membertable> TO FIELD-SYMBOL(<lv_membervalue>).
"Take over value
IF lr_qv_char IS INSTANCE OF if_bics_qv_structure.
lr_qv_struc = CAST if_bics_qv_structure( lr_qv_char ).
DATA(lt_strucmembers) = lr_qv_struc->get_structure_members( ).
READ TABLE lt_strucmembers INTO DATA(ls_strucmember) WITH KEY name = <lv_membervalue>.
ls_cellchar-chavl = ls_strucmember-ref->n_s_md_struc_mem-alternative_name.
IF ls_cellchar-chavl IS INITIAL.
ls_cellchar-chavl = <lv_membervalue>.
ENDIF.
ELSE.
ls_cellchar-chavl = <lv_membervalue>.
ENDIF.
ENDIF.
INSERT ls_cellchar INTO TABLE lt_cellchars.
ENDDO.
" At this point all characteristics and their values are in lt_cellchars, for example:
" lt_cellchars[ chanm = '0CALMONTH2' ]-chavl
" sums are marked with
" lt_cellchars[ chanm = '0CALMONTH2' ]-issum
" and the cell value is in
" <ls_data_cell>-display_value
" and unit information can be found in
" lt_currency_unit_info[ id = <ls_data_cell>-currency_unit_id ]-key.
ENDLOOP.Alternatively you could use a renderer to have te result set rendered to a specific format, for example CL_BICS_INA_RS_RENDERER would give you the possibility to render the result set to JSON, or you would write your own subclass of CL_BICS_RS_RENDERER to render in any format you want.
Planning
You can also execute plannung sequences and planning functions – the changes to their planning buffer will influence the data reported by the queries without the need to save the planning buffer first.
"Add planning sequence
DATA(lr_plseq) = CAST cl_bics_planning_sequence( lr_app->get_planning_sequence( 'Z_MY_PLANNING_SEQ' ) ).
"Set variables
DATA(lr_plseq_vc) = lr_plseq->if_bics_planning_sequence~get_variable_container( ).
... setting variables same as for a query ...
lr_plseq_vc->submit_variables( ).
"Execute planning sequence
lr_plseq->if_bics_planning_sequence~execute( ).And when you’re done…
Don’t forget to free the application resources:
cl_bics_start=>close_application( ).
Craving for more examples?
Have a look at the CL_BICS_TEST_… classes for some nice examples.
A hint for REST APIs based on BICS classes
If you program a REST API that accepts multiple calls to change the query state over time, don’t forgot to change your web service to stateful: server->set_session_stateful( ).
