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

[jorge_sousa]

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:

In our Test environment we have 2.921 users.

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

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:

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:

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

At the end you have next query:

Execute with Run Query:

Save document for a future use.

Export Report to Excel File:

Export report to Excel (XLS or XLSX):

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:

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:

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:

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:

Import new Table (Excel) into Universe:

Now we can import the table into the Data Foundation:

Insert Join between EXCEL's table and USERS table:

Configure Join:

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:

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):

- Without login (in our example 2.761 users):

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:

(245 users)

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

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

That's all by the moment.

Jorge Sousa