After my first blog about BICS HANA in design studio and with BI 4.1 being GA last week, I have decided to right a series of blog around our BI suite connecting to BW with BICS (3 B's)
They are already many resources available and to not repeat things I will try to offer you a different view.
I'm thinking of several parts, let's see where I can go:
Part 1: Deep dive into a basic workflow (current)
Part 2: Analytical vs. reporting (to come)
Part 3: <tbd>
The goal of this first part is to understand how the connection is made between BI (in this case our Business Objects Enterprise - BOE) and BW. I will describe a basic workflow using web intelligence and we will look at traces on the systems to confirm the way it works.
I will not go into details on how to setup traces as I prefer to insist on the way it works. I will really recommend you to look at this great documentation with many information about traces and how to optimize performance. http://scn.sap.com/docs/DOC-33706
The agenda for this blog will be:
- a definition of BICS (I will not try to define BI and BW)
- 4 different workflows (3 with webi)
- a summary
So let's start with what is BICS?
BICS means BI Consumer Services and is a SDK developed and used by SAP.
It is a layer for multidimensional and relational data access and its API offers many benefits for developing BI applications and guess what? SAP BI tools use it!
We are focusing on BICS with BW so here is a thing to remember for this 1st blog. We differentiate 2 types of BICS:
- JAVA BICS
- ABAP BICS
Pretty obvious where the difference is... but we will focus on how they work together. We could mention another one but not let's complicate things for now.
How does this work? (my simplified definition)
A java BI application uses java bics. The java side of bics uses the SAP Java Connector (JCo) to connect to BW via RFC and triggers the abap bics. The abap bics gets the result from bw and send it back to the java bics that presents it back to the BI application.
Easy... but still let's try to put facts behind this definition.
Create an OLAP connection
If you have never done it, you can see how to create OLAP connections from the CMC here.
This creation process is handle by the Multi Dimensional Analysis Service (MDAS) from the BI platform (BOE). You can find it within the Adaptive Processing Server (APS). For the purpose of the blog, I want to isolate my process so I have clone the APS only with the MDAS service and rest of the servers are down (except CMS and I/O FRS).
--- working on your acronyms ---
The server's trace is set to High as I want to see what is happening... If you just fill the connection details, not much... But we will use the connect option to browse the BW system as you may want to create your connection on top of an info provider or query directly.
Using Process Explorer, I can see that my java process (my MDAS server) communicates with my BW system, sounds good!
And using my notepad++ on my traces, I will search for "BICS":
Good start! it seems we can confirm that my BI java process uses BICS and JCo to connect to BW. Let's move to the next workflows using web intelligence and search for RFCs.
Browse a connection from webi
It is time now to go to the BI launch pad and create a new web intelligence document.
- Note: You can only create a new Web Intelligence document from a BEx query when your modify preference is set to the Rich Internet Application.
So I need to make sure I start the Web Intelligence processing server but I also need the DSL Bridge Service under the APS. I will do the same thing from previous workflow (clone APS and assign DSL service):
Again let's browse our connection to find my BEx query for my web intelligence report and look at our java process (my DSL server this time):
Connection is made.. nice! and if we look in the trace for "BICS":
and for "NODES":
But we did this already, just a new java process we are looking at... It is time to move to the BW system, I will use transaction ST05 to trace RFC:
Activate > Run your workflow (browsing BW from Webi) > Deactivate > Display - Super easy!
JCo uses RFC to talk to BW and the calls we have seen in the trace should be seen in the BW system too.
And yes we do see them!
Interesting enough it took 18 seconds to give back to my "not-sized" BOE the first level of nodes...long...but I'm also using a user with SAP_ALL authorization and the BW system is far from being small (thousands of objects).
Query is selected, next step comes.
Web Intelligence will now present you the query panel:
The query panel is the place where you will have access to the metadata of your BEx query (hierarchies, dimensions, attributes...). You can then select the objects you want to have in your report, apply filters, create prompts, select levels,...
This process of retrieving metadata information is called design time. So let's have a look at it closer.
The DSL server I have created is still in charge so we will just check RFC trace from BW:
We can see that BICS was working to retrieve this metadata information.
BICS_PROV_SET_HIERARCHY is a good example as BICS needs to set each hierarchy because they are all presented in the query panel. In the trace we can see that the max duration was ~500ms so still ok...but what will happened if you have 30 infoobjects with each of them having 100 hierarchies... (never saw something like this)
worst case: 0.5*30*100 = 1500 seconds ... ok it could become costly ...
But I have good news for you! A lot of improvements have been done in BI 4.1 (I will try to show you an example in one part of this blog series)
Now we have seen BICS being used in the java processes of BOE, the JCo, RFC, let's move the final stage, the ABAP BICS.
Once you have selected the different objects you want to display in your report, you can click Run Query and then the actual data is retrieved from the BW system. This is the runtime.
So let's go one step further with a transaction that I really like: RSTT. Select the user you want to trace in the User Activation screen and then check the trace in the Trace Collection.
You will see the different ABAP functions from BICS that were executed and the time it took.
We can see now that the calls are a bit different than in design time and a good example that we are in runtime is the BICS_PROV_GET_RESULT_SET where the actual data asked by the user is retrieve.
But what is even cooler with this transaction is that you can debug in ABAP (if you like too ) and see what happened in the workflow and what was exchanged between the JAVA and the ABAP sides of BICS.
Let's go quickly to the E_T_DATA_CELLS of the BICS_PROV_GET_RESULT_SET and we can notice that the table has a specific structure for optimization so difficult to read for normal human eyes...
But easy enough I can see that the value of row 6 (in yellow) is the same that I can retrieve in my webi doc:
Memo: your job here is not to debug! just if you want to have fun.
What I want to mention here is that with rstt, you can have a great overview of where the runtime was spent in BW. And what is even better, if you see one call being extremely long you should better check SAP notes as one could improved your overall performance after few minutes of implementation in tcode snote.
So what did we see?
- BICS for BW has 2 parts JAVA and ABAP.
- The ABAP side of things is easy to check on BW and I will recommend you to always check sap notes for ABAP BICS improvements.
- Different BI java processes in BOE uses java BICS to access BW hence importance of changing the default configuration with a proper sizing!
- Difference between design time and runtime.
- Finally, if you remember my definition from the beginning:
A java BI application uses java bics. The java side of bics uses the SAP Java Connector (JCo) to connect to BW via RFC and triggers the abap bics. The abap bics gets the result from bw and send it back to the java bics that presents it back to the BI application
Hopefully it makes more sense now.
It was a bit longer than I expected at the beginning but hope you enjoyed reading! I'll be happy to get comments/questions and will try to answer if I can.
More soon where I will compare analytical vs. reporting with something you should all have at home.
PS: I used BI 4.0 SP7 and BW release: 702 / level: 0006