Technical Articles
Unlock the Auditing database with a new Universe and Web Intelligence Documents for BI4.1/BI4.2
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!)
- My SQL (added October 2022)
- Sybase ASE (added October 2022)
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)
Update October 2022
A new edition is now available meaning the latest edition has different minimum requirements:
- SAP BusinessObjects BI Platform 4.3 Support Pack 2
Please visit https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/ for details
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 | Fixed in BI 4.2 SP5 possible before as well |
| 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 | Fixed in BI 4.1 SP10 and BI 4.2 SP4 |
| 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,
We have a requirement to baseline and find out the average time of how a universe performs - can I use this audit universe to obtain average timing for queries that run off a universe ? Could you please advise the format and objects to be used.
Regards,
Mihir
Dear All,
Can we extract reports from Audit universe with bex query reports executed by the users.
We have extracted reports of users in Audit universe with dashboardsa nd webi reports.
But we need to extract reports from bex query executed by the Users.
Is it possible to do the same.
Thanks & Regards
Afzal Kar
Download link is not working.. Can you plz extend it.. Thanks
New link is https://sap-my.sharepoint.com/:f:/p/matthew_shaw/Ehpzmgu3VfZAsMu8vLvBrCQBtVNSDRNy8mL-mDTvuRU0CA?e=25Hqwk
however SAP policy has changed and I’m only able to share for 10 days, so the link will stop again in 10 days from today (15 May 2019)
I'm trying to find an alternative solution. Regards Matthew
Hello
I've created a new KBA to store the Universe and Documents https://launchpad.support.sap.com/#/notes/2793147
The KBA will take a few hours to be made available.
This should solve the problem long term.
Regards, Matthew
Thanks. I can download it now..
Hello All,
Is there any way to see how many users are logged on at a particular time and find out how the maximum number possible?
Note: All the users are of type Named User in the system.
I have created one audit report which shows the max no of logon by users -month wise. Need to understand how to extract the above info.
Thanks!
Hello Kataria,
Can I suggest this blog post for you https://blogs.sap.com/2017/05/12/limits-to-number-of-sessions-for-named-users/
I think it will answer you question. Regards, Matthew
Can this be used to report on Promotion management? Are there any Out-of-the-Box Reports for promotion management? I'd like to get the 'Change Management ID' field that is available when running a promotion job.
Hello Michael,
The universe provides access to the whole auditing database, so you can use it to report on everything captured in there. There's no dedicated Documents for Promotion Management. I'd recommend using the SYS1 document as its a simple 'audit log' and this can then be helpful to know everything that is captured. You can then create a new query based upon what you see and what you might want to filter on. Regards, Matthew
We would like to create a report based on the Audit universe, which will provide us the query or queries used in any web intelligence report.
Is it possible to obtain the query used in any web intelligence report using the Detail field and filtering on the Detail Type?
Thanks in advance!
Hello Matthew.
I just download your files from the KBA (https://launchpad.support.sap.com/#/notes/2793147).
But I am not able to unzip the file "Build 8.5 Web Intelligence Documents (requires BI4.1 sp5 or greater).zip". The failure message "The file ... can not opened as Archive" occurs
Could you please provide the file again.
Best regards
Christian
Hello Christian,
Sorry you are having problems with that file. I’ve tried to reproduce the problem but I’ve not been able to do so. Could you try again (different browser, different PC, different network) ? If that still doesn’t work please send me your email and I’ll forward the file. Contact me via the Former Member Regards, Matthew
try with 7zip
Running 7-Zip as Administrator was able to extract the lcmbiar file but While importing file using Promotion Management get
Operation failed!
Error reading lcmbiar file content.
Any idea how to import the “Build 8.5 Web Intelligence Documents (requires BI4.1 sp5 or greater).zip” successfully?
What’s the prupose of “Build 8.5 Web Intelligence Documents (requires BI4.1 sp5 or greater).z01” file?
can someone email a copy of the file?
Hi Mathew,
I am running report Usage - Documents no longer used in the system report
like 101347_<Report name>_115718,
101347_<Report name>_116969,
101347_<Report name>_116972,
101347_<Report name>_117189
However, in the system actively there is only single version of <Report name>
So not sure how to interpret the results out of this report?
Regards,
Kiran
Hello Kiran
My guess is these reports could be temporary reports,perhaps used in a publication or scheduling. You'd need to dig a lot more into where these reports are stored and what/who created and deleted them to understand.
My universe is just reporting on what's in the database.
Regards, Matthew
Hello,
Would the SQL Anywhere build work on a Sybase database?
Thank you.
Sadly not no. Regards, Matthew
Is there a sample audit universe for Sybase CMS Database?
We are trying to find sample audit universes for Sybase Db.
Also, do we download these LCMBIAR sample universe files and build audit webi reports on the audit universes?
regards
Varun
Hello Varun,
I am also looking for sample Universe and reports for Sybase DB (we are on BOBJ 4.2 SP7 - BW4/HANA). if you could find the sample docs please let me know.
Regards,
Radhika
Hope to release Sybase DB universe soon. It will be on BI 4.3 Support Pack 3
[update October 2022] now available https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/
Hello Matthew
I am trying to downloaded the Build 8.5 for WebI reports and tried to extract and it is unable to convert and a BIAR file is created with 0KB size. I tried multiple browsers and still have this issue.
I used Note 2793147
Can you let me know if I need to try something else?
Thanks
Venkat
Hello Matthew,
Is the Audit Universe supported on Linux (Business Objects 4.2 SP7 on RHEL7.5)?
Thanks,
Jason
Hello Jason, Yes that will work. The OS of the BusinessObjects Platform is not relevant for the Universe or the Documents. Regards, Matthew
Hi Matthew,
what could cause scheduled event not to be saved on auditing? For testing I created a new simple report. I refreshed it once manual and once I scheduled it to run in a minute. After that I ran report SYS4x1 for both modes. The "interactive" shows results that "scheduled" not.
Audit config is set to default.
Any ideas?
Hello Thilo, Could be lots of things. Sorry that's out of scope for my blog. Best to search the KBA, post a question to the community or log a support incident please. Regards, Matthew
Matthew,
Would it possible to get non-lcmbiar versions of the UNX/UNV and WebI document, saved without security. I took your original copy updated the folder and names and don't want the latest lcmbiar to over right the changes.
Thanks Kindly,
Ajay Gupta
Hello Ajay, sorry they are only available in LCMBIAR format. Regards, Matthew
Hi Matthew,
Do the tool support BOBJ on ASE database? Any plan at all?
Thanks
Ed Chiang
Sybase ASE universe coming soon. It will be on BI4.3 SP2
[update October 2022] Now available https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/
Hello Matthew Shaw!
Do you have a guide written on how to create the universe and report? I would love to us that as a tool on learning about those subjects!
Hello James.
I've not written such a guide, sorry. For Universe Design you really need a 2 or 3 day training course. I believe SAP still provide the training courses. The documentation is more a technical feature/function and won't go so much into the 'why' or best practices. Its a big topic! Regards, Matthew
Okay, thanks Matthew.
Mattew,
We are running BOB 4.2, SP7, Patch 02 and there are still APPLICATION IDs missing from the ADS_APPLICATION_TYPE_STR table. According to your notes above in this blog, this was supposed to be fixed in BOB4.2, SP4
Any ideas why these are still missing and how do I fix this?
Thanks!
Michael Grackin
Hello Michael, It might be that only a new audit database with that version will have the new 'missing' entries. You could create a new schema for auditing, just to see the new table values. Then change back to original audit scheme. You can then optionally manually add in any missing rows into the lookup table(s). Product Support may also be a good way to get some assistance. Regards Matthew
Hi Matthew,
I am missing something in the process but can't see it so apols in advance.
I've imported the LCMBIAR files, all successfully. I am not seeing the connection to edit it in either the Connections or OLAP Connections menu.
Any guidance would be appreciated.
Thanks
Gill
Hello Gill, You'll find the connection in the Information Design Tool, rather than in the Central Management Console (CMC). I think you'll using the CMC? Regards, Matthew
Thanks Matthew, yes I was using the CMC
With regards
Gill
Hi Matthew and other experts:
We have a Sybase type ADS Database.
We used to audit reports many years ago, we want to start using them. We want to download the standard audit reports provided by SAP. For this I was referring to the following SAP note, following your comment on one of the answers above.
Question is: for Sybase db, that we have, from where can we get the standard audit reports to download?
2793147 - Where can I download the BI Platform Auditing Universe and Reports?
Environment
regards
Varun
I have exactly the same case...looking for help
Hi Victor,
We downloaded the sample SQL Server universes and changed the source to Sybase in IDT.
Hope you have already been able to do that!
Varun
Hello all, SybaseASE is now supported with my latest version https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/
Regards, Matthew
Hi Matthew,
In our case, the error messages are written in German in audit database. The filters in some of the audit reports are considering error messages in English.
What kind of settings do we have to change so that the error messages are written in English in audit database.
Thank you.
Regards,
Parag
Hello Paragkumar, Just change the self-joins I've placed on the lookup table to the language you'd like. You'll find I limited them to 'EN', you'll just need to change these to your preferred language. Regards Matthew
Hi Guys, I am able to successfully import UNX files but not able to import WEBI reports. After Zipping the folder i can see only 0KB size. can someone please share me the file asap?
Hi Matthew /Experts:
We want to download the standard audit reports provided by SAP. We followed your comment on one of the answers above where you mentioned the SAP note 2793147 – Where can I download the BI Platform Auditing Universe and Reports?
We are working in a system with the following characteristics:
Data base: Adaptive Server Enterprise - 16.0 SP03 PL07 OS: x86_64 (64-bit) SLES 11.1
Java connection: JDBC
SAP BusinessObjects BI Platform 4.2 Support Pack 6 Patch 3
Version: 14.2.6.2953
QUESTION --> WHEN & WHERE do we can download the standard audit reports for this database?
Thanks in advance,
Jorge Duran
Hi Matthew,
I imported the universe (SQL Anywhere) and webi sample reports. When I open and refresh the report STAT1, It throws the below error
Could you please guide me how to resolve this?
Earlier It was showing a different error,
"data source name not found and no default driver specified".
Please suggest
Regards,
Arpan
Hellp Arpan. The error appears to be an invalid user/pass. So you'll need to update the connection with the right user/pass. You'll best to do this in the Information Design Tool and you want to update the connection you imported as part of this solution. The user/pass needs to match the user/pass of the audit db. Regards, Matthew
Hi Matthew,
Thanks ! Its working now.
But I am facing an error while trying to run the report "USA1 x1 - Usage - Session Analysis". It has a filter "Only events between ? and ?". For me all the reports which have this filter are not working. It prompts the blow error
I read some blogs about how to address this kind of problem but It does not help. I am not deep into Universe and Webi stuff. I only know the basics.
Could you please suggest?
Thanks in advance!
Regards,
Arpan
Hello Arpan
I wonder if the data type for the event date has changed or is somehow different. I've not heard of this issue before, so it suggests the problem is not generic, but local to your environment. Regards Matthew
Hi Matthew,
Thanks a lot for the prompt reply!
Any suggestions, how to resolve this?
Regards,
Arpan
Hello Arpan
I'm not sure what your problem was here.
I couldn't reproduce the problem. Please try again with the latest version now available https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/
Regards, Matthew
Hi Matthew,
Really enjoying using the resources you've provided - two questions:
1. What does the auditor 'Edit' action capture? I want to to analyse number of times the query in a report is edited. Would Edit give me that? I've not been able to find definitions for auditing events like this one - Platform Admin Guide only has limited info.
2. How do I exclude system user actions on reports. Say I want to produce a report with average of human user views of report x, how do I exclude Views that took place as part of the automated refresh workflow, for instance.
Many thanks! 🙂
Hello Andrew
1-Sorry, there is no definitive list
2-Please use the predefined filter. I think its called 'Only User Events' or something like that.
Regards, Matthew
Hi Matthew - your universe rocks. We are using an Oracle database and I can't figure out how to convert the [Detail] object when it is associated with a prompt value to a date. It is a character (string) field and looks like 12 digits (ex: 158328000000). What was actually entered in the date prompt was 3/4/2020. Is there a way to convert it in the report or universe if the prompt was a date?
Many thanks!
Lynn
Dates are now captured in a human readable format. (before it was the number of seconds from a day in 1970 I seem to recall).
Hi Matthew,
We are not able to make the connection to work. Do you have an idea of what are we missing. The following message appears: DBD: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Thanks in advance.
Regards,
Patricia
Hi Matthew.
Is there an updated report containing the fix for report "USA4 – Usage – Universe Objects usage. Identify infrequent used objects.wid" per Reference IDs 1580133418, 1670185455, 1670185456, 1670185458
Can a User's email address (under USERS AND GROUPS > USERS > NAME > PROPERTIES > EMAIL in XI4.2.6 CMS) be reported on (queried) from the Audit universe that has been auto-populated by Active Directory to XI4.2.6?
Thanks,
Leon
Hello Leon
Basically no. That email address is stored in the CMS database and its not captured as part of the user in the Audit database ADS_USER table. Regards, Matthew
Hello Matthew,
could you please provide the latest audit universe files for HANA CMS DB
Thanks,
Bharath
Hello Bharath
The latest ones are the ones available. Regards, Matthew
Hi Matthew,
It’s been for a while. Hope everything is going well.
We have a large multi-tenant deployment based on mysql, but it’s not currently supported. Would it be possible to add mysql to the supported DB list? Or any possible workaround to run the auditing report/universe against mysql audit database?
All My Best,
Dafang
We are looking for this support too, MySQL based Universe. Are there any plans for this support on the roadmap?
You could always take one of the existing universes (I'd recommend the SQL Server one) and modify it to use a MySQL connection. You would then have to modify the DataFoundation to either use or exclude the schema owner on the tables - depending on whether MySQL requires it. You might also have update some of the Business Layer if there's any database-specific syntax there. You would use "Verify Universe" to find these types of issues.
-Dell
Hello Zhang, hope you are doing great, looks like its been a while it has been posted, just looking to see if you ever gotten an reply on this topic if not, what was your effective solution adapted.
Regards,
Gopi
Hello
My latest edition now supports MySQL
https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/
Let us know how you get on.
Regards, Matthew
The documents in the zip files are corrupted, can these be corrected please.
We're looking for a report that will show us which scheduled reports are consistently the longest to run. Which of your sample reports would be best for that? I've run a few and WOW there is a lot of information, but I don't really see what I'm looking for.
The Webi Document zip file seems to be corrupted. It wont extract in WinZip. It will in 7-zip but the extracted file is 0 bytes. Any other way to get theses documents. Maybe create smaller lcmbiar files?
Hi Everyone-
We are creating usage report using "BI paltform audit analysis universe".
My query is " If i schedule any any webi report what is the event type it will store in audit database?"
Is it view,save,run or refresh?
Regards,
Surya
Hi Matt,
Thanks for this, very useful. I am trying to use universe in SAP BO 4.2 SP8 system. After repointing the connection, universe is not able to find any of the Auditing tables (ADS_*) even though auditing is enabled (HANA DB) and set to default. Any inputs on what i am missing.
Regards
Siddu
Hello,
We are unable to download the universe and the webi file. The KB - SAP Note/KBA 2793147 is coming back saying that it is not available. Can you make the files available to download.
Thanks
Satya
Hello Satya
Sorry about the download problem. I've found a better way to share the contents now. I've updated the link and so that should be ok now. Regards, Matthew
Hello Matthew,
Thanks for the universe and canned reports.
Can you please let me know if you have a repository of webi reports off of this universe apart from canned reports you provided ? I want to know list of webi reports refreshed by user by date by universe names of the report .(by objects used in webi report if possible)
Thanks,
Srinivas
Your query needs to look like this. I don't know if this works in the existing Universe, but it works in the new Universe (build9) not yet available - but it will be soon! Stay tuned for updates to this blog. You can optionally add in the Data Provider Object too if you wanted, but of course you'll get a lot of rows back!
Hi Matthew,
Our Audit database has grown to a size where reports take 60 plus minutes to run when they were taking minutes before, I suspect it is just data size and MS SQL not picking the best route for the data.
Do you have any thoughts about adding indexes to the Audit DB to improve reporting performance?
Cheers.
Andy
Hi Andy
Can you try with the latest version of the universe solution, now available at https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/ ?
I made a lot of performance improvements which I hope means we can get you queries back down to take a few minutes.
Let us know how you get on please,
All the best Matthew
Hi Matthew
I've managed to import the Universe and WebI reports from the download.
However, I'm receiving the error attached when I try and change the connection details in IDT (Open via repository, clicking edit on connection)
We have the Audit database and CMS on a DB2 10.5 instance.
I'm thinking that I need to download the DB2 runtime client to allow me to open the connection details and the change them to point to the correct DB name and supply the correct credentials.
Hopefully the above makes sense, any help you could provide would be great
Cheers
Graham
IDT Error
Hi Graham,
you should have 2 possibilities.
1. Install and catalog your db2 driver and use the IDT in LocalMiddleware Mode.
2. You can change the middleware mode of your IDT to Server Middleware then you can use the driver installed on your BO Server
Hope this helps!
Regards,
Martin
Hi Martin
Thanks a lot for your quick reply. I've had a go at option 2 (mainly as I didn't want to go down the IBM rabbit hole of installing and configuring the driver locally etc..) and changed the setting to look at the server middleware, but still no joy.
I expect the ODBC driver isn't installed on the BO server either (it's managed by a 3rd party), which is probably why it's kicking off.
Annoyingly, I think I need to try and install the DB2 ODBC driver, which it seems IBM don't make it particularly straightforward.
Thanks a lot for your help and if I do get to a solution, I'll post it on here to help other who might encounter the same problem.
Cheers
Graham
Hi Graham,
I think it s not possible connecting CMS and Audit DB to IBM DB2 without a driver on the BO Server. Normally it is the DB2 Client.
You should choose this for your connection in IDT and use the DB entries for your auditing DB from the CMC.
But for changing the driver type you need the installation of the DB2 driver locally on the machine you are using for IDT.
So unfortunately you have to install the DB2 driver anyway.
Regards,
Martin
Hello
It's been a while, but I eventually managed to get this working on the DB2 Audit database.
I had several problems, from the 3rd party hosting site providing the wrong port for the Audit DB and then when they created a specific DB2 user for me to access the Audit DB they didn't give it the correct grants in DB2 ! In the end I just got the credentials for the SAPBOAUDIT user who owns the objects.
We managed to get these resolved, and then I decided to use a JDBC driver in the Auditing universe and this worked fine.
There is such a huge volume of info in the canned reports, I feel a bit of a mug as all I'm keen on getting at the moment is the frequency of when a artefact is viewed!
i.e. listing the most popular to least popular objects...inc. WebI Reports & Lumira DBs ! (though, I'm not sure if one of the canned reports provides this)
Cheers
Graham
Hello Matthew,
we are experimenting with the Report "SYS1 - System - Event Log".
Our problem: We never see all events that exist, the report always shows 56 Pages (+/- 1) maximum.
We do not have a similar problem when we use the associated universe "BI Platform Audit Analysis.unx".
Our Audit DB is HANA 2.0, SPS 5. SAP BO is 4.2 SP7.
Thanks for any ideas/suggestions!
Matthias
Hi Matthias, I'd be included to look at what limits have been placed in the query (row limit) and if there's any limit on the universe (row limit). I believe then ones I've made available don't have any limits set, but I'm not 100% sure. From what you say, it suggests the limit is on the document, not the universe. Regards, Matthew
Hello Matthew,
we are using HANA CMS DB, while refreshing the data i am getting the below error. recently we have upgraded to 4.2SP8. could you please provide me the solution . appreciate your help
Hello,
I couldn't reproduce the problem. I'm just validating my updated Universe build 9 against HANA 2 with BI 4.3 SP2 and this document worked just perfectly. Stay tuned for an update coming soon!
I guess you're problem was 'local' to your environment in some way or another. I wonder if you edited the query in some way?
Matthew
I tested the latest version of my universe and paid particularly close attention the this GROUP BY when querying HANA. I still couldn't reproduce the problem. I did though, change the way the query was designed so it no longer uses the 'advanced query objects' (as I called them) which may have played a part in this problem, I really don't know. Let us know if you still get the problem with the latest version available at https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/
Hi Matthew, I am trying to install Audit Universe with SQL Anywhere 17 as the database but I have somes errors about libraries, but when I use SQL Anywhere 16 it works fine. My question is, which version of SQL Anywhere should I use to install? Exist any workaround to use SQL Anywhere 17 instead?
Thanks
Angel Gonzalez Navas
Hello Angel,
My latest Universe for BI 4.3 SP2 will support Sybase SQL Anywhere 17. It's not yet available, but it will be soon! Stay tuned...
Regards, Matthew
Hello Matthew,are Universe for BI 4.3 SP2 Sybase SQL Anywhere 17 available? Regards Sara
Hello Frau,
Yes, they are now https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/
All the best, Matthew
Hi Matthew,
We have created report on auditing universe to display the success/failed status of the publications running in the system. However, there is one case where publication is getting Failed due to the source report refresh error. But when we check the audit report, it is not displaying the email address of the recipients to whom that publication was to send and only display one line with the publication name, email address blank and status failed. We are already using Detail type-"To address" to capture email address field.
Is this the usual product behavior or we can make some backend changes to capture email address for publications getting failed due to source report refresh error.
Hello,
My system doesn't seem to be capturing any Universe event details. Are there any configuration points/steps to capture this info? I am interested in using the Universe Data Provider Refresh universe objects. I also tried the Webi Reports: USA4 and USA5 and they return no data.
BI Platform 4.2 sp09
Thanks,
Mike
Hi Michael,
Please try again with the latest version of my auditing solution https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/
I made a good number of improvements with the documents USA4 and USA5 (which now have new codes) and the underlying universe objects. It means these should now return data where previously they did not. There have been changes to how the data was being captured which meant the universe needed to be updated for this reason.
Let us know how you get on, I'm sure others will be interested
All the best, Matthew
Hi Matthew
We have just upgraded to BI4.2 SP9 Patch 3 and our CMS and Audit databases are on SQLSERVER 2014.
In order to migrate to BI4.3 we will need to retrain our users on the FIORI interface, which is quite different to the original interface! This is available in BI4.2 and accessed via a different webaddress:
Original - https://<servername>:<port>/BOE/BI
Fiori - https://<servername>:<port>/BOE/BILaunchpad
If I log in via each of these and run the "SYS1 - System - Event Log" report then "Application Type" shows as "BI launch pad" for both the old interface and the new Fiori interface.
Is there a way from the audit logs to see which interface users are logging in to?
Many thanks
Regards
Andrew Dale
Hello All
For identifying used "Universe Objects" there is a solution in "USA4 - Usage - ..."
In addition I need to identify also "Universe Objects" that are only used in the "Query Filter" but not in the "Result Objects".
Any ideas?
Many thanks
Regards
Christian Loder
This is not possible to do through the auditing universe because the information is not stored in the audit database. You would have to use the RESTful Web Service SDK to get this information.
-Dell
Thanks
I was hoping ... 🙂
Hi Matthew,
Is there a new link? When I open the version 8.5 there are only db2, Hana and Oracle. Earlier I have got an sql server version as well
Hi Matthew and everyone,
i don't know where this join is made in the universe : upper({fn left(EventType.Language,2)})=upper({fn left(@Variable('PREFERRED_VIEWING_LOCALE'),2)}))
I can see it on the reports SQL but not in the Universe Design Tool.
Someone can help me ?
Many thanks
Regards
Hi Vardan,
Tis is een self-restricted join.
In the Data foundation you'll find a list of all joins
Hello and good morning Matthew:
As always, thanks for your great work 😉
Just asking is this the current Audit Universe & Webi reports available or is there a more recent version/blog I should look at for SAP BusObjects 4.3 SP2?
Thanks,
Andreas J.A.
Hello Andreas,
Thanks for your feedback.
The current version available is the latest, but I am actively working right now on an update for BI 4.3 SP2.
So far the improvements are:
I'll be added a number of enhancements that I've collected over the years.
If you'd like to see anything in particular now couldn't be a better time to ask! So hit that reply button and make your suggestions! I can't promise I'll implement them all, but I'll see what I can do.
All the best
Matthew
Hi Matthew,
Is this universe now available for MySQL?
Regards,
Mirko
Hi Mirko,
No, there's no MySQL version. I guess you'd like one?
Regards, Matthew
Hi,
Yes, that would be very useful. 🙂
Currently our BO environements are based on 4.2 SP09 and 4.3 SP02.
Regards,
Mirko
Hello Mirko
I ported the latest edition of my universe to MySQL based on your feedback. Please let me know how you get on (please post your feedback on the latest version at https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/ )
The performance of the 'interactive mode' on MySQL wasn't that great. It means you may find the 'older' way I determined the mode will be quicker. The older way was with a subquery of NOT IN, which on other platforms performs much better. So the jury is out for MySQL with regard to the performance of the 'mode' object. My user guide (at the link just mentioned) has more details. Let us know how you get on,
All the best, Matthew
Hi Matthew,
Thanks you very much. Is this also available or even already executable for 4.2?
Regards,
Mirko
Hi Mirko
Sadly not, it has to be BI 4.3 SP2 onwards. You can query a BI 4.2 Audit db with it of course, but the Universe and Documents needs to be on BI4.3 SP2 as a minimum.
Regards, Matthew
Update October 2022
A new edition is now available.
Please visit https://blogs.sap.com/2022/10/06/auditing-solution-update-for-sap-businessobjects-bi-platform-4.3/ for details
Hello. Thank you for this.
Might I also suggest that the SAP Note that had been created for this: 2793147 also be updated to show the latest version changes.
Thank you Narthan, that's updated now. Thank you, Matthew
Hi we are using
SAP BusinessObjects BI Platform 4.3 Support Pack 2 Patch 1 Version : 14.3.2.4121.1
and are attempting to use the Audit universe with CMS+Audit in MySQL.
We seem to have got the Audit and connectors working - ODBC with a user DSN and a unicode MySQL 8.0 driver (on Windows).
So - good start - the "Start here" query runs fine.
We having some problems with several reports taking a long time to execute (gets very near 99% done) and then responding with errors - for example SYS 11 Event Log with a date range of just a couple of days. An example is
"The following database error occurred: Error writing file 'C:\Windows\SERVIC~2\NETWOR~1\AppData\Local\Temp\MYfbt8rx6gsq9wy8c0' (OS errno 28 - No space left on device). "
it doesnt look like we are out of disk space.
https://me.sap.com/notes/2054721/E
Any ideas?
Hello Simon
Good to hear you're making use of the sample universe and documents for MySQL as the auditing database. The document 'STA11 Start here' run successfully so a great deal of things are all working as expected. However, the longer queries are failing which suggests you have a run-time error, likely related to the volume of the data in some way.
The error you received back from the MySQL middleware was "OS errno 28 - No space left on device". A quick internet search for this error with 'MySQL' added to that search term, finds a good number of suggested solutions and causes and I'd agree, it is most likely a lack of disk space on the database server itself, not the client. Also, the lack of disk space isn't about the actual disk space, but the lack of temporary or swap space the database has access to. If you run the same SQL directly on the database you should observe the same error.
I'm pretty confident this is a database issue and not related to anything on the client or actually any aspect of my sample solution or anything SAP technology has control over. Though I appreciate the issue is observed when using it for more complex queries rather than simple ones.
Hope this helps and you're able to get it to work.
All the best, Matthew
Thanks for the reply Matt.
We've been tweaking out test system to try and get this to behave but with no joy.
Its not a very busy test system - so the expectation was that it should be able to run the SYS11 - System - Event Log with a date range of a few days with little problems.
It seems to eat the disk - with the tmpdir MySQL setting to a near empty disk it consumed 100Gb before failing (over 30 mins).
We also tried chainging things up by using internal_tmp_mem_storage_engine=MEMORY and setting the table sizes to 2Gb - resulting in the same behaviour.
Disk usage seems to be the norm for MySQL 8.0. I'd be interested in you or the community sharing any recommendations to make it work - and make it performant.
Hope you can help!
Hi Simon
For the STA1 document, what does it show for the total number of events and event details, please?
You could try to enable the boundary_weight_table optimisation feature, it might help reduce the table size for the dates you're interested in. It might not make any difference in this case though.
Better for someone with better MySQL admin knowledge than me, to share an idea about how to tune or configure MySQL so to avoid this issue. I see this as a MySQL configuration issue related to the volume of data. Hence would be good to see the number of rows you're trying to query.
All the best, Matthew