Monitoring BO user sessions for HANA Data source using Web Intelligence

[former_member182521]

Happy New year SAP Community,

Again its been a year and I just wanted to share my findings when I got a chance to work on a troubleshooting scenario involving user sessions.As we know we can monitor and manage BO user sessions from Sessions Page in CMC. Did we ever investigated what these BO sessions are doing in background?



Most of us know how to use Webi Admin Tool to perform similar session analysis on servers hosting Web Intelligence related services https://wiki.scn.sap.com/wiki/display/BOBJ/WebiAdminTool

In this blog I would like to show you what is happening in background when you connect to a HANA data source and how to correlate the Server/Service/User and generate a report out of it and finally show the power of Web Intelligence through its features.

To get good understanding about connecting HANA from Web Intelligence, you can refer this blog (https://blogs.sap.com/2012/07/23/faq-sap-bi4-webintelligence-on-hana/ ) which has a great information though it is pretty old.

Here is my case

I want to know who is using my Sales Analysis dashboard (in real time) which is my only dashboard consuming HANA as a data source.

This is how my analysis started.

• Get all my APS services that are hosting Analysis Application Service (Per my APS split these servers are used while running DS dashboard).

• Run the Sales Analysis dashboard (Design Studio) in BI launchpad.


• I am sure this dashboard is going to hit my HANA DB as it based on it.


• Make sure you have access to SYS Schema

• Run the below SQL from HANA System view M_CONNECTIONS from SQL Console (Replace BO Server with xxxx and appropriate Process Id)

The resultset is something like below which would give you a correlation between your BO Service being used with your corresponding user id (SSO Connection is preferred for exact user name in BO & HANA). You see multiple DB sessions as we have parallel processing enabled as well as multiple data sources referring to same database with different levels of aggregation.



To get enhanced view on my sessions I created a Web Intelligence report built on top of the above HANA System view as below



and list of unique users & list of unique Servers connected from BO.

.

Few more improvements

• Enable Data tracking to see what is changed over time between your each report refresh.

• Enable Auto Refresh (using Extension API as we are still in < BI 4.2 SP6). in BI 4.2 SP6 this feature available out of box.

We can perform similar analysis using PIDs of other servers (make use of services split in APS) just by modifying the Custom SQL executed from HANA side to include different PIDs and Server nodes. You can monitor not only the sessions generated from BO but also every session created in HANA which could be useful when you have a large deployment with multi-point access to HANA system.

Thanks for reading and looking forward your comments and feedback on how this works in your environment.

 

Update : 1 (Jan 08, 2019) 

If you want to extend this to SQL Server or Oracle try with below system tables/views.

SQL Server

----------

Select hostname, hostprocess,nt_username, net_address,net_library,

       login_time, last_batch,status

from sys.sysprocesses;



Oracle

-------

Select username,osuser,terminal,

	utl_inaddr.get_host_address(terminal) IP_ADDRESS, 

	client_info, process

from  v$session

where username is not null

order by username,osuser;