How to know which users who never login in SAP BO BI with WebIntelligence

This post shows some possible ways to show in WebIntelligence who never login in the platform using SAP BO BI Universe in SBOPRepositoryExplorer and in the second method combining with SAP BO BI Audit Universe. Also it can be a good option to understand if Audit is working as expected.

 

Method I (Without Audit data)

Prerequisites:

 Some required components:

  1. SAP BO BI 4.x Platform;
  2. SBOPRepositoryExplorer connection and the universe;
  3. WebIntelligence to create the document.

 

Creating a report with users information:

From WRC or from BI LaunchPad using WebIntelligence we can create next query to show number of users that we have in our SAP BO BI environment:

SBOPRepositoryExplorer_loginusers_1querypanel

In our Test environment we have 2.921 users.

Now we can discover the number of users that never logged in Test environment:

SBOPRepositoryExplorer_loginusers_2querypanel

It means that we have 2.921-2.676=245 users who have ever logged in Test environment.

With next query we can show list of users who never logged in this environment:

SBOPRepositoryExplorer_loginusers_3querypanel

 

 

Method II (with Audit data)

Prerequisites:

Some required components:

  1. SAP BO BI 4.x Platform;
  2. SAP BO BI Audit DB;
  3. SAP BO BI Audit Universe configured and pointing to Audit DB;
  4. Excel to save users from Audit Report;
  5. IDT (Information Design Tool) to configure SBOPRepositoryExplorer connection and the universe;
  6. WebIntelligence to create the document.

 

Creating Report with Users Login-Activity from Audit DB:

Using WebIntelligence and Audit universe:

- For result objects:

SBOPRepositoryExplorer_loginusers_4querypanel

- Filter Event Status with: User Logon Succeeded, Named User Logon Succeeded and Concurrent Logon Succeeded.

SBOPRepositoryExplorer_loginusers_5querypanel

At the end you have next query:

SBOPRepositoryExplorer_loginusers_6querypanel

Execute with Run Query:

SBOPRepositoryExplorer_loginusers_7Excel

Save document for a future use.

 

Export Report to Excel File:

Export report to Excel (XLS or XLSX):

SBOPRepositoryExplorer_loginusers_8Excel

Remove in the Excel all blank rows before head and all blank columns before “User Name�?, remove also any special character different than [a-Z][0-9].

You also can use SAP BO Live Office to retrieve data using Audit universe and schedule periodically.

Rename report name to the final table name in the universe:

SBOPRepositoryExplorer_loginusers_9Excel

Save to a visible path by SAP BO BI Client Tools (IDT and WRC) and by SAP BO BI WeIProcessingServer.

 

Retrieve SBOPRepositoryExplorer universe to IDT:

Create in IDT (Information Design Tool) a Local Project and from Repository Resources:

SBOPRepositoryExplorer_loginusers_10IDT

SBOPRepositoryExplorer_loginusers_11IDT

SBOPRepositoryExplorer_loginusers_12IDT

 

Configure Universe Connection attaching the Excel File:

To attach our Excel file definition to our universe we must create an universe connection in IDT into a project, for example:

SBOPRepositoryExplorer_loginusers_13IDT

SBOPRepositoryExplorer_loginusers_14IDT

 

Test Data from Excel in Connection:

Before continue with next steps is important to check if Excel data can be read where path is correctly defined and also the structure:
SBOPRepositoryExplorer_loginusers_15IDT

SBOPRepositoryExplorer_loginusers_15IDT2

SBOPRepositoryExplorer_loginusers_16IDT

 

Import new Table (Excel) into Universe:

Now we can import the table into the Data Foundation:

SBOPRepositoryExplorer_loginusers_17IDT

Insert Join between EXCEL’s table and USERS table:

SBOPRepositoryExplorer_loginusers_18IDT

Configure Join:

SBOPRepositoryExplorer_loginusers_19IDT

SBOPRepositoryExplorer_loginusers_20IDT

Save the Data Foundation.

 

Define new objects in the Business Layer:

Here we can define in the Business Layer, into the “Users�? folder the new measure coming from the new table:

SBOPRepositoryExplorer_loginusers_21IDT

for example, with next content:

sum( ifnull(“XLA_AUDIT_LOGON_USERS�?.�?NUMBER_OF_LOGINS_TOTAL�?,0) )

and before save and publish the universe, create a query to test results with all users and users without login:

- With login (in our example 2.921 users):

SBOPRepositoryExplorer_loginusers_22IDT

- Without login (in our example 2.761 users):

SBOPRepositoryExplorer_loginusers_23IDT

It means that we have only 2.921-2.761=160 users who have ever logged in Test environment.

Now we can publish our new universe to CMS for next topic.

Compare data from Method I and Method II

As you can observe in “Method I�? we have 245 users logged and in “Method II�? 160 users logged. We want to discover what users are different from “Method I�? and “Method II�? and try to understand why those users were not recorded in Audit DB.

- First is create a query with all users logged from both methods:

SBOPRepositoryExplorer_loginusers_24IDT

(245 users)

- Second is create a combined query (with minus) to get the list of users that were not included in Audit DB record:

SBOPRepositoryExplorer_loginusers_25IDT

So we have to investigate why those 85 users where not recorded in Audit DB.

That’s all by the moment.

Jorge Sousa


Leave a Reply

Subscribe

  • Facebook
  • Twitter
  • Google+
  • RSS Feed
  • YouTube