An understanding for how your BI Platform is used and utilised will enable you as a BI Platform Administrator to take the necessary steps to improve its reliability, performance and adoption within your organisation.
The Auditing database coupled with a new comprehensive Universe and a set of Web Intelligence documents that I have developed will help give you that insight you need and this is what I’d like to share with you now.
My Universe and documents have been in development, on and off, for some time but they have now reached a maturity level where I’m happy to share them with a wider community.
I’m overall pretty happy with the Universe and the documents, however they need a little performance testing on large data sets. This is where you can help me, help you!
Please download my latest ‘build’ (available for a limited time) and give them a blast. They are provided ‘as is’. I’m looking for feedback on any defects, performance issues and also additional reporting/business requirements. If you can get back to me with your feedback I can improve the content for everyone else to benefit. I may occasionally published a newer ‘build’ in the same container, so check every now and then for an update.
Once I’m happy with the amount of feedback and testing I will make the Universe and documents more widely and permanently available.
I have ported the universe to various databases and is currently available for:
- SAP HANA
- Microsoft SQL Server
- Oracle
- SQL Anywhere
- DB2 (added 18/11/2015. Limited testing, please expect some issues!)
Feedback on which database I should next port to would be helpful too!
There’s a large set of documents, each with a number of ‘reports’. The number of reports ranges from 1 to over 50 within a single document. So you can see I’ve been busy! They will take you some time to go through them all.
Here’s a list of documents:
1. STA1 – Start here – Events over time.wid
2. FRA1 – Fraud Detection – 1 machine more than 1 user.wid
3. FRA2 – Fraud Detection – 1 machine more with multiple logon failures.wid
4. LIC1 – License – 1 user more than 1 machine.wid
5. LIC2 – License – Periods when sessions exceeded X.wid
6. LIC3 – License – Users no longer using the system.wid
7. LIC4 – License – Users unable to login due to lack of Concurrent license.wid (added 20/11/2015)
8. LIC5 – License – Periods when named users exceeded 10 (added 25/11/2016 – download as a separate LCMBIAR file)
9. SYS1 – System – Event Log.wid
10. SYS2 – System – Delay in Recording of events to Audit Database.wid
12. SYS3 x1 – System – Overall System Load Analysis (without Mode).wid
SYS3 x2 mi – System – Overall System Load Analysis (Mode is Interactive Only).wid
SYS3 x2 ms – System – Overall System Load Analysis (Mode is Scheduled Only).wid
SYS3 x4 – System – Overall System Load Analysis inc Mode.wid
12. SYS4 x1 – System – Refresh Analysis (Mode is Interactive).wid
SYS4 x1 – System – Refresh Analysis (Mode is Scheduled).wid
SYS4 x2 – System – Refresh Analysis (inc Mode).wid
13. USA1 x1 – Usage – Session Analysis.wid
USA1 x15 u – Usage – Session Analysis (With Users, Without Mode).wid
USA1 x2 mI – Usage – Session Analysis (Mode is Interactive Only).wid
USA1 x2 mS – Usage – Session Analysis (Mode is Scheduled Only).wid
USA1 x30 umI – Usage – Session Analysis (With Users) (Mode is Interactive Only).wid
USA1 x30 umS – Usage – Session Analysis (With Users) (Mode is Scheduled Only).wid
USA1 x4 m – Usage – Session Analysis (With Mode).wid
14. USA2 – Usage – Large number of Data Providers.wid
15. USA3 – Usage – Documents no longer used in the system.wid
16. USA4 – Usage – Universe Objects usage. Identify infrequent used objects.wid (see defect list below)
17. USA5 – Usage – Universes no longer used.wid
Each document has an ‘About’ page that provides a few more details on its purpose.
The Universe is, of course, documented within itself. Every description box has a description! However I’ve not yet written supporting documentation for either the universe or the Web Intelligence documents. Feedback from you on what I should explain would be great!
Requirements:
- BI Platform BI 4.1Support Pack 5 or greater for the Web Intelligence Documents.
- BI Platform BI 4.1.x (or greater) for the Audit database to be queried
This means BI 4.2 meets these requirements.
Instructions
- Download the content (take the highest build numbered zip file)
- Import one of the five ‘Universe’ LCMBIAR files into your system using Promotion Management (it will go into “BI Platform Auditing” folder)
- Import the Web Intelligence LCMBIAR file (it will go into “BI Platform Auditing” folder)
- Edit the connection that is imported (in “BI Platform Auditing” folder) with the correct login credentials.
- Open the Web Intelligence document ‘STA1 – Start here – Events over time.wid’ as your starting point!
Webinar
- You may watch the recording (more in the series)
Please post your feedback here and I will do my best to comment back as soon as possible.
Matthew Shaw
Twitter: @MattShaw_on_BI
Current defect in my Audit Universe and Web Intelligence documents:
| Issue | Status |
|---|---|
| SYS1 – the Duration and ‘Delay in recording time’ show incorrect figures | I’m working on it. |
| Performance of using objects built on the Derived tables in the universe isn’t great | I’m working on it. |
Current defects in capturing of the Audit events (not a defect in the Universe or Web Intelligence documents, though it will impact them)
| Issue | My reference | Fix? |
|---|---|---|
| Action in sequence is a bit mad! The ‘Action in sequence’ should be 1, then 2, then 3. etc. Currently its a little random! Impact is you can’t quite determine the order of events, other than by time. Possibly can’t determine some event that might require a known order of events. | 1580137557 | We’re working on it. |
| List of Universe Objects not recorded when refreshing a Web Intelligence document based off a Universe. Impact is you the “USA4” document can’t return anything. Can’t say “given an object, which documents has it been used in before and by whom”., BI 4.2 SP2 now stores the universe objects names, however only the name of the object is captured and not the full path to it. This means if you have 2 objects of the same name (not a good idea anyway), then you can’t tell which one of the objects was used. Additionally the objects (or filter objects too) used as filters to any prompts are also not captured, though the prompt and prompt values are stored. Crystal Reports for Enterprise stores the full path of all objects used. |
1580133418, 1670185455, 1670185456, 1670185458 |
Partly fixed in BI 4.2 SP2. Still working on it. |
| On a Web Intelligence document ‘refresh’ event, the Bunch ID doesn’t change, meaning (for documents with multiple universes or different type of data providers) you can’t tell which Universe ID belongs to which Universe name, and you can’t see which Excel file name belongs to which data provider.. Impact means Cartesian result when querying any of: UniverseName and UniverseID. This means any queries based on these objects (typically used as a condition) will return some invalid results. It also means you can’t identify if a document/event has a mixture of different types of data providers, for example 1 Universe, 1 Freehand SQL. | 1570835454 | Targeting BI 4.1 SP8 |
| When you login and logout of the BILaunchPad multiple logon/logout events are captured, even though you can only login/logout once. Impact, means you can’t just view the number of events and compare login and logout event types, as there could be more logouts than logins! | 1570835975 | Fixed in BI 4.2 SP4 |
|
Some rows in the Audit database cause run-time errors. For SQL Server the error is “Database error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type varchar to bigint.. (IES 10901) (ErrorL INF )”. For DB2 the error is “Invalid character found in a character string argument of the function “BIGINT”. SQLSTATE = 22018″ The root problem isn’t with the Universe, per say, but a product defect. When the detail value of ‘0’ is stored for the detail types ‘Number of Rows’ and ‘Size’ the product stores an ASCII value of 0, rather than a string of ‘0’. I’ve worked around this problem in the universe but I will arrange for this to be corrected in the product. |
1670001433 |
Build 8.5 (In BI 4.2 SP3 the audit data will no longer store a ASCII character of value 0, but a string of “0”) |
| When refreshing a Web Intelligence document based on Freehand SQL, the detail type ‘Universe ID’ stores a single character of ASCII value 0. The ‘Universe Name’ and ‘Universe Object Name’ both store the name of the connection used (without the full path to the connection name). Additionally the SQL for the document is not stored. Impact: makes querying the audit database a little more complex. I will adapt the universe to cater for this. | 1670001434 | We’re working on it |
| When using Web Intelligence Rich Client and browsing the Repository, for every Web Intelligence document listed, a ‘Retrieval event’ is incorrectly captured. Impact- makes it appear more documents where retrieved than really where. | 1670004359 | Targeting BI 4.2 SP4 |
| Design Studio audit events against a universe: Universe ID is captured as ‘Cube Name’. The Universe name, SQl, parameters, values, universe objects used, refresh time are not stored. Impact: limited analysis on these kind of objects. | 1670004513 | We’re working on it |
| Web Intelligence based off an XLS: Audit events ‘refresh’ will store the XLS CUID under Universe ID. ‘Refresh’, ‘save’, and ‘create’ will store the XLS filename (not full path) under ‘Universe Name’. Impact means you can’t tell if the document is based off a Universe or an XLS file. | 1670004687 | We’re working on it |
| Login as a ‘Concurrent User’ with Universe Design Tool will increase number of concurrent users by 2, not 1 (as seen in the CMC-Session) and also in the Audit database (as the detail type ‘Concurrent User Count’). Additionally the audit database will record multiple logon (~5) and logout events (~6) with multiple session IDs (~5) when you would expect to only see 1 of each. | 1670009472 | We’re working on it |
| In Universe Design Tool, when you retrieve a universe the audit database captures ~8 retrieval events, when you expect to see just 1. The detail for each of these events are identical to each other. | 1670009473 | We’re working on it |
| Application Type is missing the lookup table (ADS_APPLICATION_TYPE_STR). The missing values for Application Type ID are:AbI5_LpMFxFLhxl7DV91KYY, AeHuywedXKRPl6V1sOVZNtA, AUcgkU0CbV1Co0ujVEw_gSA, AXUaxM4wcwtLqqcn7.eB5bQ). Impact: For some events (most Webi, but also Crystal and Universe) the Application Type will be ‘null’. This might make event level analysis tricky! | 1670026096 | Fixed in BI 4.2 SP4 and BI 4.1 SP10. See KBA 2391022. |
| When Web Intelligence Rich Client saves a document, or exports to PDF, locally the ‘Size’ written is not numeric and will cause your database to throw an error. It means the ‘Size’ object in my universe will not work. Only affects certain product versions (see KBA 2193391 and 2265803 for these versions). I plan to work around this problem in the next version of my Auditing Solution. To prevent the product from writing future invalid entries apply BI 4.1 SP7 patch 10 or greater (this patch will be merged into other SP lines + BI 4.2 lines) | OSS 344143 | Apply a product patch to prevent future invalid entries. Wait for my next UNX build to workaround existing invalid entries |
Other changes planned.
| If you have enabled ‘User Group Details’ (within CMC-Auditing) then the list of all the groups, the user is a member of, are captured for *every* *single* event. For example, if a user is in 150 groups, then all 150 groups are stored for each and every event associated with that user (Prompt, View page, refresh etc.). Given: the User Group assignment is unlikely to change within any session; querying any 2 lists of groups across events is so tricky and there’s an easy way to list when user-group assignment changes we plan to change this. | n/a |
Planned from BI 4.2 Support Pack 5, the list of groups, for a user, will be captured just once, at user logon and not for every event. This will *dramatically* reduce the data volume with no loss of functionality. To see if the user-group assignment has changed, just run a simple query: AppType=CMC; ObjectType= User; EventType=Modify. And look at the Property Name/Value for groups added or removed. Also, use my universe and the predefined filter “Only sessions where the user is a member of a particular group ?” to do as it says! |
Hi Matthew:
Working on creating a log of failed jobs and can across something I’m hoping you can help me understand.
For Event Type ID = 1011 (Run), the possible statuses are (from page 698 of the Platform Admin Guide v 4.2 2015-11-12):
0 – Successful
1 – Failed
2 – Failed but will be reattempted
3 – Cancelled
In the universe, the Event > Status Code object description says:
0 – Successful
1 – Failed
2 and 3 – Successful with warnings
And the Event > Status object select statement is
CASE Event.Status_ID WHEN 1 then ‘Failed’
ELSE ‘Successful’
END
I’m wondering why you are classifying Failed but will be reattempted and Cancelled as ‘Successful’? When I pulled my failed jobs using the built in Only Status Failed filter, it didn’t pick-up several reports that failed with status 2 so we missed that there was an issue with these instances.
To complicate things further, Event Type 1008 (Save), statuses 2 and 3 are successful statuses.
Maybe these universe field definitions need to be a bit more complex to handle the inconsistent use of the same status numbers?
Deb
Deborah,
Marvellous feedback, thank you so much. Very well documented too. I will indeed enhance this object. I see other event types (Logon, Save) also generate Status Codes: 2 and 3 respectively.
Regards
Matthew
Dear all,
Does someone has an example of a Design Studio application on this new Audit Universe ?
Many thanks
Hans
Hi Matt,
Thank you for sharing your reports with us.
Every think workss well in the univers but only the “Object Folder Path”. I can not get that information it is always empty !!
Can anyone help please.
I am on SAP BI 4.2 SP2, SQL Sybase 16.
Thks
Hi Mohamed.,
In CMC->Auditing Page ,you will see the option Folder Path Details ,confirm if you have selected that check box.
Regards,
Joyeb
Hi Joyeb,
That option wasn’t checked.
thanks, so now I need to execute each webi repport to get the information in the Audit DB.
Regards
Hi Mohamed,
Yes ,Now you need to run couple of reports to get the folder information .
Regards,
Joyeb
Hi ,
We are facing issue with the connection .Unable to find what should be the datasource name while creating audit db connection.
I am on SAP BI 4.2 SP2, SQL Sybase 16 on Linux.
Any help will be appreciated.
Regards,
Dewesh
Great improvement on previous auditing Universe and reports! Looking forward to working with this at a current project!
Nicely complemented by the YouTube video too!
Rene
Hi Matt,
i am using the auditing in 4.2 SP02 P1 and 4.1 SP07 P4 with oracle as DB. I think there is a bug since some patch. Since the notes 2193391(https://launchpad.support.sap.com/#/notes/2193391) and 2265803(https://launchpad.support.sap.com/#/notes/2265803) it is possible to audit saving of documents in richclient(Event_ID 1008) but it writes a wrong size in Event_Detail_Value for the Event_Detail_ID 17. It looks like a hexadecimal string (e.g. 14E08F0C). This causes some of your reports to fail with the error unknown number. All reports that include a size calcution like SYS3 X4.
Is this to be fixed in the universe or should this be fixed in a patch?
Regards,
Martin
Hello Martin
Indeed, there is a defect in the platform that a ‘0’ is not being stored correctly in the audit database. This is applicable for anything, including ‘Size’ (id 17) but also others like ‘Number of Rows’.
Technically it turns out that a ‘null’ was being sent, instead of a zero and what we see as a funny character (it has an ASCII value of 0) is a terminating character and is actually at the end of every string we store in the audit db. However it causes us a problem when you want to ‘sum’ it. So we fixed this in BI 4.2 Support Pack 3. I’ve just validated it, and its now working as you and I would expect it to. I see a proper ‘0’ as the detail.
As a workaround I’ve addressed it in many of the Universes I’ve provided, but not for all RDBMS’. Which RDBMS are you using please for your Audit Db? The logic to fix it yourself is to compare character 1 (of the detail) to an ASCII value of 0, if it is 0, then you want a string of ‘0’ otherwise accept the value in the field. You can then ‘sum’ that value once you’ve converted it to a number.
Whilst we have fixed it in BI 4.2 SP3, even if you update to this version, your database will still have the old data with this issue, so it needs to be addressed in the universe too.
Regards, Matthew
Hi Matt,
i’m using Oracle as Audit DB and Version 8.5 of the Audting universe.
I don’t understand completely what you mean by comparing the first character to the ASCII value because the value differs.
Here’s some extract from the size field for Event 1008 ID 17:
‘111E0D44’ first character 1
‘36302D44’ first character 3
‘46500B1C’ first character 4
….
and the ASCII Code of 0 is 48, right?
Could you please describe it once more with an example?
Regards,
Martin
Hi Martin
I see. When you mentioned the ‘Size’ detail and corruption I assumed this was the same problem I’ve seen before with regard to this ASCII 0 character. But for you this isn’t the case, I can tell for sure because the length of your detail for ‘Size’ is more than 1 character long.
So, there must be something not right. Now we need to fix this, and the best way would be for you (and others) to log a incident with our Product Support so they can reproduce the issue.
However, if you have data in you db which is ‘bad’ then we’ll also need to workaround this too, unless you’re happy to delete the bad data.
For the ASCII 0 character issue I used this SQL for Size for Oracle:
(sum(to_number(decode(ascii(dbms_lob.substr(“DetailSize”.EVENT_DETAIL_VALUE,1)),0,’0′,dbms_lob.substr(“DetailSize”.EVENT_DETAIL_VALUE,dbms_lob.getlength(“DetailSize”.EVENT_DETAIL_VALUE)-1))))/1024)
You could perhaps use a similar technique for your issue?
This issue isn’t fixed that I know of in any version.
Let me know how you get on. Please direct message me the incident number (so I can keep an eye on it too).
Regards, Matthew
Hi Matt,
i’m not allowed to send you a direct message, what do I have to do for it?
Regards,
Martin
Hi Matthew,
ok new Portal all personal Messages lost and no idea how to open a new personal Chat.
Regards,
Martin
Hi Matt,
is this also fixed in some SP or patch of Version 4.1?
Regards,
Martin
Hi,
I’m facing a strange issue with date objects using Audit universe. I have a report that tracks all scheduled activities, which runs/ is scheduled every 15 min. I’m in CST time zone and all date objects are converted to CST timestamp from GMT with daylight savings.
Problem is “Trunc(sysdate)” is displaying two hours behind timestamp. Due to which any activity between 12;00 AM to 1:59 AM is bringing previous day records. Strange thing is I’m not even filtering on date time. I’m filtering only on current day. Still it pulls the records of previous day till 1:59 AM and report runs fine after 2:00 AM. I tried various syntax like trunc(sysdate)+2/24 so on, nothing worked.
I appreciate any help, below is filter syntax.
To_date(to_char(from_tz(cast((ADS_EVENT.START_TIME AS TIMESTAMP), ‘GMT’) AT TIME ZONE ‘US/Central’, ‘MM/DD/YY’), ‘MM/DD/YY’) = Trunc(sysdate)
Thanks & Regards
Farees Mohammed
Matthew, congratulations on producing what is the most mature SAP solution in the Auditing space to date for BusinessObjects.
I have a question specific to the capture of events/actions related to Analysis for OLAP. What I am after is the ability to measure how long an end-to-end refresh takes of an AOLAP workspace. I’m already aware that there is a benchmarking t-code: ST03/ST03N which allows query run-times to be determined. I’m wondering if the Auditing universe can also capture the same information, plus and overhead required to render the AOLAP workspace. Despite turning on the auditing database log level to COMPLETE, I can’t seem to capture any relevant information regarding AOLAP workspace refreshes.
Please let me know if I’m missing something.
Regards,
Gino Terrigno
Hi Matthew ,
Is it possible to have audit for BICS connection based reports with below format
Report ,Datasource(BICS Name),variables filled,no of records fetched ,No of refreshes,User
We couldn’t find way to track BICS based events !
Thank you
Sateesh
Hi Matthew,
This is certainly a great tool for Admins!
I have a few questions and for the time would prefer to message you directly. How can I do so?
Regards,
Todd
Hi Matthew,
Thank you for the Audit universe, very helpful.
We have successfully imported your audit universe, set the audit level as default and able to generate the reports.
We have a requirement to capture user (client) and server hostname & IP addess in the report to track all users who are logged into the system from different countries. We tried to use [Host Name Reported By Client] & [Host Name Reported By Server]) from specific event type section but it doesn’t give the user machine name it come blank not sure how to pull this info. can you please help as its urgent request?
will appreciate your fast response. Thank you.
Kind Regards
Hema
Thanks for putting this together. One problem I’m having is opening the “STA1- Start here” report. I can open the report via Rich Client and refresh it. However, I can’t seem to open the report through Launchpad. I’m using BOE 4.1 SP6.
Any ideas would be appreciated.
Thanks.
Try and create the most basic report on the audit UNX on BI Launchpad. ie: pull in the dimension “Year” and refresh. Same problem? Do the other reports exhibit the same behaviour? Possibly the connectivity to the audit DB isn’t correctly configured on the server (server need 64 bits client, while WIRC uses the 32 bits client). Error is too generic at this stage to say what the problem is. OS info for BI server and audit DB type would be useful.
I’ve just uploaded a new LCMBIAR file with 1 new Web Intelligence document in it:
LIC5 – License – Periods when named users exceeded 10
It will show the periods when the number of users, that are of the type ‘named users’ (and not ‘concurrent user’), exceed a value of 10 (or another value you provide).
Hope you like it! Matthew (@MattShaw_on_BI)
Hi Mathew,
Is there any Audit Universe available for Sybase database?
Thanks,
Santhosh K
Hi Matthew,
We have earlier version of audit report (Sybase ASE version) and Our BO environment database is Sybase 15.7.135. We are exploring option to install new version of audit report but did not see LCMBIAR file for Sybase, is it advisable to use SQL Anywhere version of file. Please advice.
Thanks,
Prakash
Hi Matthew,
I also have a customer with ASE 15, is there a ASE migration plan of this universe? Or is a different one usable?
Thanks,
Javi Puig
Thanks Matthew,
very useful sharing. We do believe in open source 🙂
We have Oracle. Two humble comments:
1.If we want to create a historical report with object folder path and object as dimensions and if that object is moved in the past, we may only want the final destination of the object in our report. For that I introduced a new object as “UptodateFolder”. For that I created a new derived table “UptodateFolder Of An Object” with the following code and right joined with Event table over the OBJECT_ID :
select object_id,uptodateFolder from (
select start_time,object_id,max(object_folder_path) KEEP (DENSE_RANK LAST ORDER BY start_time )
OVER (PARTITION BY object_id) uptodateFolder
from ADS_EVENT
where length(object_folder_path)> 2
)x
group by object_id,uptodateFolder
2. In order to get universeid and name in the same derived table, the details table was joined with itself. But that provoked performance problems in my case, so I just dropped the universeid(I did not know why I will need that information) from the derived table. The high-performance derived table in its last form:
SELECT
DetailUniverseName.Event_ID,
DetailUniverseName.Bunch,
dbms_lob.substr(DetailUniverseName.Event_Detail_Value,dbms_lob.getlength(DetailUniverseName.Event_Detail_Value)-1) as UniverseName
FROM ADS_EVENT_DETAIL DetailUniverseName
where DetailUniverseName.Event_Detail_Type_ID=14
GROUP BY
DetailUniverseName.Event_ID,
DetailUniverseName.Bunch,
dbms_lob.substr( DetailUniverseName.Event_Detail_Value,dbms_lob.getlength( DetailUniverseName.Event_Detail_Value)-1)
Hi All,
I would like to monitor the distribution of WebI and CrystalReports standard reports by schedules and publications. Especially by status (successfull, failed), by recipients, by duration, by rows and by size.
What are the required parameters considering Application Type (JobServer?), Event Type (Deliver, Run, Refresh ?), Server (BO.APS.Core), Object Type ?
Anybody who has build something like this?
Thanks in advance!
Best regards
Christoph
Hi Christoph,
I would create a report with all those parameters as dimensions and filter out the report with objects I am interested in. For filtering the cuid’s of reports or object folder paths may be used.
Regards,
Emre.
Hi Matt
Great Work! Really appreciate your efforts!
We are finally working with our Auditing information and are a little perplexed…there is no data found for the Crystal Reports Processing servers…We see them processing work and there are values reflected in the CMC Metrics screens. We have/see Auditing data for other servers including… CR Cache, RAS, Job Servers, APS, CMS, WI Processing…etc…
We are using BI 4.1 PS 6, Patch 1 on SQL 2008 R2. Auditing configuration is set to collect all Common Events except Retrieve, Search and Trigger; all 3 Platform Events for Auditing, CAL’s and Rights. The Event Details are set for all except Query.
Any ideas/suggestions?
Thanks
Sherri
Hi all ,
anyone is having troubles with the ‘size’ when event detail type = 17 ??
I already tryed to use the solution already mentioned in a few coments converting the ascii value to 0 but even like that when I try to do a sum it converts to letters .
If anyone had the same problem or if there’s any updated audit universe please let me know 🙂
I would appreciate your help 🙂
Thanks
Hi all
Is there a way to catch all Webi with freehand SQL? A property or something like that.
If not, Matthew: is it possible to add this feature?
Thank you Matthew for this awesome work!
Guillaume
Hi Mathew,
Is there a Audit Universe for Sybase?
Thanks,
Santhosh K
Hi Matthew
I need to do some admin related work on some of the most costly Webi reports.
How do I identify those reports which try to query huge volumes of data in the warehouse?
thanks
nutty
What I have to do to resolve following error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Because I can’t run any raport
Hi,
First of all, thank you for the awesome work with BI Audit solution. The audit universe and reports are working fine when I have tried to install it on SAP BO that runs on Windows Server.
However, I am getting an error when I try to refresh the STA1 WebI report after the BI Audit installation on SAP BO 4.2 SP 2 that runs on Linux.
The following database error occurred: (CS) “DBDriver failed to load : /home/oracle/bobi/sap_bobj/enterprise_xi40/dataAccess/connectionServer/drivers/lib64/libdbd_dbodbc12.so (libdbodbc12_r.so: cannot open shared object file: No such file or directory)”
I believe PATH or Environmental Variables are missing. Any ideas how to fix it? It is a PROD environment and I would not like to experiment on it without knowing what is the exact cause of this error.
odbc.ini file content:
[ODBC Data Sources]
BI4_CMS_DSN_146=SQLAnywhere 16.0
BI4_Audit_DSN_146=SQLAnywhere 16.0
[BI4_CMS_DSN_146]
UID=dba
DatabaseName=BI4_CMS
ServerName=BI4_146
Host=localhost:2638
Driver=/home/oracle/bobi/sqlanywhere/lib64/libdbodbc16.so
[BI4_Audit_DSN_146]
UID=dba
DatabaseName=BI4_Audit
ServerName=BI4_146
Host=localhost:2638
Driver=/home/oracle/bobi/sqlanywhere/lib64/libdbodbc16.so
Technology:
SAP BO 4.2 SP 2
Platform OS: Linux
Audit DB: SQL Anywhere
Thank you very much in advance.
Br
VT
The problem was with the connection which was pointing to 32bit SQL Anywhere 12, DNS BI4_Audit. It works fine on my local PC, but it fails when I sent the universe back to the repository. Resolution: Create 64bit SQL Anywhere 16 connection that points to the server DNS, in this case BI4_Audit_DSN_146.
Hi Vytautas,
I have the same issue, can you specify what you mean by “Create 64bit SQL Anywhere 16 connection that points to the server DNS”. Where and how did you create that?
Kind regards
Sveinung
Check the ADS Database Connection Name in CMC > Auditing.
Go to Information Design tool under BI client. To create a new Relational connection, first start with a new project under Local Project view. Click New → Project → Enter the Project Name → Finish.
Right-click on Project name → New → Relational Connection.
Create the connection using the correct driver (in my case SQL Anywhere 16) and the “Database Connection Name” you have seen in CMC.
Publish the connection to the repository if it works fine. Later you can change the connection of the BIaudit universe to point to the connection you hav ecreated
Be aware that CMC/CMS uses 64bit DSN/DB driver to access Audit DB. Designer or IDT need 32bit DSN/DB Driver to access data. So the connection retrieved to the repository must be 64bit. Otherwise you will get an error when running the report in WebI.
In addition, I have downloaded both ODBC 32 and 64 bit drivers to my PC and configured them so that it will work fine when retrieving the Audit universe and editing it in IDT.
Hi,
Appreciate for your hard work and Thanks for sharing with us.
I would like to find who schedule a report on particular day including the IP address, can I get this info using the universe?
Regards
Santhosh
Hi Matt,
Long time no speak…
Great blog! Thanks very much. I’m glad somebody’s finally on top of this subject.
I’ve created a couple of dashboards for myself to keep tabs on report usage here at Newton, and it’s just dawned on me I’ve been missing some of the Live Office activity that’s starting to become popular. So now that I’ve added LO content to my report I’m struggling to find the name of the corresponding Universe used. I can see row counts, prompt answers, etc, but no Universe identifier in the ADS_EVENT_DETAIL table.
Do you have any tips on how I can get to the bottom of this?
Thanks in advance. Glad you’re still alive and well!
Paul Benson
I’ve made a bit of progress with this, so have half answered my question. I can see the underlying document name and CUID as ‘Element Name’ and ‘Element ID’ – and I’ve been able to map these back to the universe. All good.
But what about LO document that are purely query based? Is the element simply the universe itself? Unfortunately I don’t have a non-report based LO document to check…
Hello Pau,
Would it possible for you to share those Dashboards? i am trying to created a dashboard for report and user usage at my firm.
Any help will be appreciated.
Hi Matthew,
We are in BO 4.2 SP3 and DB is DB2 9.7. We have recently upgraded from BO 4.1 SP4 to BO 4.2 SP3.
We have audit enabled in the system. Could you please provide us lcmbiar for DB2 specific database and help us to obtain audit reports from the system.
Thanks,
Sarin.G
Do you have the UNX Universe for Sybase?, I didn’t find the universe for sybase in the LCMBIAR file.
Thank you in advance,
Srinivas
Hi Matthew,
I am using the universe provided by you for designing a webi report having UserID and User Group detail for each User.
Regards,
Arpan
hi
Download link is not working
can you please upload the universe and documents.
Thanks
B
Hi Matt,
I just wanted to say thanks. This is really great!
A small bug report of sorts –
I was getting the conversion to bigint error that others were getting. Since that error had been fixed, I did a bit of digging, and found an item in the ADS_EVENT_DETAIL table that had a value of “[Ljava.lang.Long;@1fa7a444” in the Event_Detail_Value column.
I am not sure what caused that, or if it was some weird fluke behavior, but I “fixed” it by just ignoring that value, and hoping it doesn’t happen again. (If it does, I’ll probably change it to something more specific.)
(SQL Server 2014, 4.2 SP3)
Thanks,
Julian Wechsler
Hi Julian,
It might be the known issue with “ADS_EVENT_DETAIL=17” which gives the “size” (of the object). Work around is, you just do not try to read that detail, acordingly do not use the “Document Size” measures of Business Layer in Audit..
Cheers,
Emre.
We use multiple tenants in our platform and would like to get – Maximum number of users accessing the Business Objects portal at the same time during the reporting period by tenant. We tried to use the measures – Sessions (Concurrent User) however, it only pulls in the max concurrent across tenants and not by an individual tenant. If you have a tenant that you logon 2 concurrent users to and then logon a 3rd for a different tenant the measure – Sessions (Concurrent User) increments the value for both tenants to 3. We did see the measure description. “There are two types of users: ‘Concurrent’ and ‘Named User’. Time events, like login/logout will store this number. The number is the maximum number of ‘Concurrent User’ session at the time of that event, and so it doesn’t make sense to use this measure with ‘username’ or other dimensions other than time.” So, our question would be where can we get the Maximum number of users accessing the Business Objects portal at the same time during the reporting period by tenant in the current Audit universe 8.5. If this is not possible in the existing universe/audit db, is there a different way for us to get this measure?
Thank you for posting here.
I understand your requirement. You’re right there’s no easy way to get this.
Its actually very tricky to get what you want from the auditing as it is today.
I can’t think of an easy solution.
I’ve captured this requirement for a future evaluation into Auditing improvements.
Thank you, Matthew
Hi Matthew / All
One of our clients is upgrading from BO 4.1 to BO 4.2. They would also like to migrate the audit database of the old BO 4.1 system during the upgrade. Is it possible to migrate the BO 4.1 audit database to BO 4.2. Creating a LCMBAIR file will not take the audit database i guess. Really appreciate if someone can help me on this as i can’t find any specific document in this regard.
Regards
Sanjit
Hello Sanjit, There’s no need to ‘migrate’ anything when updating from BI4.x to another BI4.x (including BI4.1 to BI4.2 and including the audit db). https://blogs.sap.com/2013/11/05/doing-a-full-install-for-updating-from-bi40-to-bi41-think-again/ Regards, Matthew
Dear Matthew,
We recently started using the new Audit universe. But is there any way to track which BO reports are scheduled and how many times and if any scheduling has been failed?
When I use Event Type I can only see values Deleted/Modified/Created/Retrieved. No event type to show ‘scheduled’. I tested scheduling one Analysis for Office workbook and one webi report. But still I am not able to see the schedule entry when I fetch the data from the Audit universe. Kindly suggest which universe object/field I should use.
Br,
Suvarna
Please use my document “SYS3 x4” as a starting point for your query. The doc will have a union of multiple queries so if you edit one query, you’ll need to edit the other ‘union’ query. Regards, Matthew
Thanks Matthew.
Hi Mathew,
I am trying to decode the Audit Logs for the Event Type = Modify. We need to build a report based for events when a principal is added to an object and what level of access is assigned to the object. The audit log information is encrypted for the field Property Value from the ADS_EVENT_DETAILS table. We are able to identify the access levels using the IDs but not the details of the principals they are assigned to. For example,
Event Detail Type = Property Value
Event Detail Value= 3&U=2,03&.1=563,03&.2=586,03
1 = Everyone Group
563 = ID for View access level
We aren’t able to figure out what 03 stands for. Also the details of the Principal to which these are assigned to. Can you provide some insights or let us know if that is possible from the ADS?
Thanks,
Priya
Hello Priya, sorry I don’t know. Perhaps 03 is granted? I’ve captured the requirement for an enhancement to Auditing, so please be assured SAP knows you need this info! Regards, Matthew
Hello Everyone!
Firstly, many thanks Matt for the excellent auditing materials; in themselves they are a first rate example of great universe and report design.
Secondly, we have a problem I hope someone can help with! We’re running BusinessObjects 4.1 SP4, and after installing the universe and reports, we ran Doc STA1 as suggested. Before the report will even refresh we get an “invalid identifier” ORA 00904 error on the “Month” field.
Has anyone seen this error and knows how to fix it? We know the materials are for SP5 but most reports do seem to work on SP4.
Thanks 🙂
Hi Matthew,
I am trying to implement Audit universe in our current deployment. We are using BI 4.2 SP3 on Linux.
I have imported the universe, webi ,connections BIAR files .
When we are deploying connection (SQL Anywhere),It’s throwing error as below
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified .
Please help.
Regards,
Dewesh
Hello Dewesh, You’ll need to edit the connection and change it to a valid connection for your system. This might involve changing the driver to the one for your system, not the one from my system. Regards, Matthew
Hi Matthew ,
Thanks for your reply.
I had created an 32 bit ODBC connection on my local PC and changed the Audit.dfx connection to point to the new connection. Universe is successfully tested and exported to the Enterprise.
Reports from Webi Rich Client works successfully,but when the same reports are scheduled in Enterprise. It’s failing with the below error.
Where to create the 64 bit ODBC connection on server or on client machine?
Thanks in advance.
Regards,
Dewesh
Hello Dewesh, I’m keen to keep the comments in this blog centred around the Auditing Universe and Auditing requirements. The problem you have is related to any universe connecting to any SQL Anywhere database from a Linux machine, so I don’t want to provide much help in the comments to this blog as this question is pretty generic. Sorry for passing on this. Regards, Matthew
Thank you Matthew for all the auditing reports. We have been waiting for this.
We are using DB2 for CMC and while running “USA1 x15 u – Usage – Session Analysis (With Users, Without Mode)”, it fails if we select more than one day of data. Real benefit of this report is to to get metrics about user session for days/weeks/months. I tried even scheduling , still same error.
The following database error occurred: A database error has occured. For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901)
Can you help ?
Thanks!
Hello Rajavelu, that error message isn’t enough to know what the problem is. Can I suggest you find what the database error message is. It might be related to either the data (perhaps something the universe doesn’t like in the data) or a run time problem relating to temporary working space for your database (like temp space). You’ll need to work it out for yourself and isolate what is causing the error. You could, for example remove an object from the query and keep removing objects until it works. For that document (USA1) you’ll have multiple queries ‘union-ed’ together, so when you remove one object from one query, you’ll need to remove the same object from the other query to keep the union valid. Regards, Matthew
Hi all,
1) First what i did Download
Build 8.5 – BI4.1 UNX and WIDs (requires BI4.1 sp5 or greater).zip
Document LIC5 only (addition to Build 8.5) lcm.job.airgap.list6929387289849922142.zip
SQLAnywhere Connection with DBA Password1 lcm.job.airgap.list3942094915599738321.zip
I took „Universe and Connection lcm.job.airgap.list4451198692545709640.lcmbiar“
I took „Build 8.5 Webi Docs lcm.job.airgap.list9102040672086520291.lcmbiar“
the folder BI Platform Auditing” is after import empty,
it should be Web Intelligence documents, right? as i said we have crystal report server 2016
so it will be work with Web Intelligence documents? many thanks for any help
Hello Frau, The solution uses Web Intelligence documents and a Universe (UNX type), so your Crystal Server won’t be able to make use of those. Regards, Matthew
Hi Matthew ,
Many thanks for your reply,that’s what I also figured out.
SAP Support send me this link as i asked for Sample Auditing Universe and Reports although they are know Crystal Server.
I have another question and maybe you can help me.
I had modified Auditing Universe connection use Information Design Tool the result is “unx Universe” but i can’t use this kind of Universe with crystal report 2016.
Have you any Idea how to do that?or any other way to report user activity?
Regards, Saba
Hi, Crystal Reports doesn’t include the functionality to create, produce or consume Universes. You need the BI Platform for that. Regards, Matthew
Hi Matthew,yes i now
i mean Universe as data source for Crystal Reports.
We have from BusinessObjects Enterprise (old system) Universe and Crystal Reports.
I update the Universe using Information Design Tool only problem i can’t use this kind of Universe with crystal report 2016 (need crystal report enterprise) Regards,Saba
Hello Matthew Shaw,
We downloaded reports based on webi and it was working fine. But now we are facing some issue and we are getting below error message while refreshing the report.
Request your help in fixing this. Thank you.
“The following database error occurred: [Microsoft][ODBC SQL Server Driver][SQL Server]’int’ is not a recognized built-in function name.. For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901) (Error: INF )”
BI version : 4.2 SP3 patch 5
DB : SQL server 2008.
Regards,
Kamesh
Seem the database isn’t happy with some aspect of an object. You’ll need to work out which object (likely to be a measure object) is causing the issue. Then determine what rows the issue is occurring for. The issue may be related to ‘corruption’ in the audit db. Please check the table in my blog for a list of known issues. Regards, Matthew
Hi Matthew,
Database related to the issue is CMS database and the universes is “BI platform CMS system database”. Recently we did patch upgrade from 4.2 SP3 to 4.2 SP3 patch 5.
Strange thing is report “Sample-User” is working in sandbox system but not in development and production.
Regards,
Kamesh
The ‘Sample-User’ isn’t part of my solution and won’t use the universe I’ve provided. Regards, Matthew
Hello all,
Can someone please tell me if the user full name is now recorded as part of the auditing in BI 4.2 SP3 ? Thank you.
Ely, Nothing new in BI 4.2 sp3 for that I’m afraid. I have though captured this requirement for consideration for a later release. Regards, Matthew
Hi Andrew
Hi Matt,
First off, great work with the Audit universe. I have done some development with the old universe and did find it had some errors and was a bit confusing.
Before doing some of the complicated analysis, I want to do some simple reporting on usage. I understand that for viewing a Webi document several events are generated. I am trying to simplify this to a count of Views per User. You have the appropriate measures already, but my counts still seem elevated.
For example, when simplifying the dimensions as much as possible, I still get 2 Actions for the View action/event. I’ve narrowed this down to the Application type being the discriminating row. In one row, the Application Type is ‘Logon without Client ID’ and in the second row the Application Type is null. In your demo, I noticed that you have an Application Type of Web Intelligence. If I were to see that value, I would know to filter the events by Event Type = ‘View’, Object Type = ‘Web Intelligence’ and Application Type = ‘Web Intelligence’.
So, two questions… what is the Application Type ‘Logon without Client ID’? And why do I not see Application Type = ‘Web Intelligence’?
Ok, maybe one more question… does this seam like the right filtering for capturing simple usage?
Thanks in advance for your help… I am also now following you on Twitter to keep in touch with other features as they become available…
Hi Carl
There are 2 or 3 view events when you open a webi doc depending upon your workflow. Crystal also has about 2 events for some workflow too.
For Web Intelligence we plan to fix this, I think for BI 4.2 SP5.
Why no Application Type for you? I’m not sure. You’d need to check that the lookup table has a valid entry for your language. You might need to log a support incident if you think there’s a defect. There might be a missing lookup, for example.
does this seam like the right filtering for capturing simple usage?
Well, it seems like that, but the audit events needs a little tidy up to really make sense of them. I think BI 4.2 SP5 should make things better, at least for webi.
All the best, Matthew
Hi Matthew
This is brilliant addition from your side apart from other contributions.
In terms of license consumption which report can be best utilized if the system is having both Named and Concurrent licenses ?
Sessions Peak measure seems to record sessions for each Event Type hence that would show incorrect figures.
I am talking purely in regards to concurrent license consumption. So couple of questions if you can kindly help with
Regards,
Ravi Pai
Thank you Ravi,
Q
In terms of license consumption which report can be best utilized if the system is having both Named and Concurrent licenses ?
A
USA1 – Usage – Session Analysis
LIC2 – License – Periods when sessions exceeded X
LIC5 – License – Periods when named users exceeded 10
Please read the ‘About’ report tab on each document to understand more about it.
Q
Sessions Peak measure seems to record sessions for each Event Type hence that would show incorrect figures.
A
The description for the object “Session Peak (Named + Concurrent User)” is
The number of sessions that where recorded in audit events. Many events will store a session ID. This is the number of session IDs within those events. This object should always be used with the ‘Year’, ‘Month’, ‘Day’ and ‘Hour’ dimension objects to show the number of sessions within that hour. **NO** other dimension objects should be used, otherwise the number of sessions will not be ‘by each hour’ but instead ‘by’ the other dimensions added into the query which will typically be the WRONG figure. When performing report aggregation, such as ‘Session Peak’ by ‘Month’, then this object will show the ‘Peak’ (or maximum) number of sessions with each ‘hour’. The sessions shown will be from both ‘Named User’ and ‘Concurrent User’ sessions. To limit the query to only ‘Named User’ or ‘Concurrent User’ please use the predefined filter ‘Interactive Mode’ or ‘Scheduled Mode’
So, yes. It would show incorrect figures if you used Event Type with “Session Peak (Named + Concurrent User)”
Q
Can we use Sessions (Concurrent Users) measure along with a time based dimension to get correct figures for a particular time and would this actually give a license consumption analysis for that particular time ?
A
The description for the object “Session (Concurrent Users)” is
There are two types of users: ‘Concurrent’ and ‘Named User’. Only logon and logoff events will store this number. The number is the maximum number of ‘Concurrent User’ session at the time of that event, and so it doesn’t make sense to use this measure with ‘username’ or other dimensions, including Tenant, other than time. However this measure can be used with the ‘Cluster’ dimension.
So, yes, it will show the correct figure.
Q
How can we pull a data for which user is using maximum concurrent licenses at a given time (In turn convert that user to named user)
A
I’m not sure I understand the question completely. The document
LIC4 – License – Users unable to login due to lack of Concurrent license
may be what you’re looking for. This will identify the events (and the usernames) when a user tried to login (and where those users are of type ‘concurrent’) but could not for lack of concurrent sessions available.
Regards, Matthew
Hi Matthew
For the Object Type ‘Analysis Workbook’, we are only able to see event types ‘Create’, ‘Modify’ and ‘Delete’. Is it possible to capture information about views or refreshes on Analysis Workbooks, similar to how events are captured for other object types?
Thank you.
Hello Hansen
I believe that is not possible. Please use the SAP ideas place to submit the idea and get others to vote on it. I do believe though, this feature is a known limitation and is being considered for a later release. Still please use SAP ideas and vote! Even post your idea URL here. Regards, Matthew
Dear Matthew,
I’m trying to use the auditing universe in order to see usage statistics for web intelligence documents.
Some of the reports’ descriptions are in Hebrew, and unfortunately letters in Hebrew show up as upside-down question marks the “full object path” field when querying the universe.
Do you know if there’s a setting to control this, or alternatively can point me to where those descriptions are recorded?
Many thanks,
Shai
Hello Shai, My guess is isn’t an issue isolated to just the Audit universe, and so perhaps better to post your question elsewhere? If this is an Audit Universe issue only, then I’m interested to determine what the issue is. My guess is that the database you’re using isn’t UTF8 enabled. Regards, Matthew
Hi Matthew,
These are the language settings for the database:
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS
2 ;
ת
PARAMETER VALUE
—————————— —————————————-
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
—————————— —————————————-
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.4.0
20 rows selected.
SQL> SELECT value$ FROM sys.props$ WHERE name = ‘NLS_CHARACTERSET’ ;
VALUE$
——————————————————————————–
WE8MSWIN1252
If this is right, can you please tell me which table is the object path coming from? We are in contact with SAP support on this and we need to clarify the issue to them.
Thanks,
Shai