After much demand I’m really pleased to announce an update to my BI Platform Auditing solution which is available now. Since I initially released it, over the last 7 years, my solution has become quite popular in the community that it still receives about 200 downloads a month!
The solution is a Universe and a bunch of Web Intelligence documents that query the BI Platform Auditing Database.
There are many new features in this latest update, many thanks to your feedback which means everyone benefits. Thank you.
The highlights are:
You can download the new update today and I’ve also put together a guide for what’s new which you can find below.
Your feedback is important because other administrators, just like you, would love to hear if it worked or if you found something to save them time. When you post your feedback here, please:
Includes support for auditing product enhancements, including:
Platform Support
Auditing databases supported:
There is a universe per database platform
The solution remains provided ‘as is’ without any formal support from SAP
New Objects for Sessions:
Enhancements made by the BI Platform means ‘Data Provider’ replaces ‘Universe’, since more than just Universes are now audited
Many things have been renamed:
Shows the users that exist in the BI Platform, but don’t have any auditing events associated to them. i.e. these users are likely to be users that have never logged-on
New document and possible thanks to Universe updates
If updating, backup and then delete the older solution:
Download and unzip latest ‘build’
(1) Retrieve the Universe from the Repository. You may need to create a local project to retrieve it into beforehand
(2) Open the Business Layer and the (3) Parameters and Lists of Values
(4 to 😎 Set the Time Zone Minutes for your organization as appropriate:
(9, 10, 11) Test the connection is valid by running the test query
Save and republish the universe back to the Repository
Validate the ‘outer-join’ performance is better than ‘sub query’ for Business Layer Filters ‘Interactive Mode’ and ‘Scheduled Mode’
There are 4 queries, in the Business Layer, for this purpose
2 use an outer-join to determine the ‘Mode’. This is now the default
2 use a sub-query. This was how these filters worked in the older version of the universe
Execute the queries and validate the ‘outer-join’ queries run better than the ‘subquery’ ones
The solution is a Universe and a bunch of Web Intelligence documents that query the BI Platform Auditing Database.
There are many new features in this latest update, many thanks to your feedback which means everyone benefits. Thank you.
The highlights are:
- New audit database support for Sybase ASE and MySQL
- Sybase ASE support needed for those with SAP BusinessObjects Private Cloud Edition
- Includes support for auditing product enhancements:
- How Concurrent Sessions are captured (required a significant re-development)
- Addition events such as Web Intelligence ‘Report’ and ‘Page’ retrievals
- Every existing document has been updated including:
- 5 documents completely redesigned
- 4 new documents added
- Many product improvements have been incorporated, such as document input controls and updated chart and table styles
- Lots of bug fixes
- Other improvements have been based on feedback from the SAP Community
- For example, time zone support and query performance
- Some tests show dramatic query performance improvements (44 mins to 44 seconds in one example)
- Update of existing documents to the new edition is seamless, even if previous audit database was on a different RDMBS platform
- (thanks to meticulous care to keep all the ‘ids’ of objects identical across all 7 universes)
- This update requires a minimum: SAP BI Platform 4.3 Support Pack 2 support
You can download the new update today and I’ve also put together a guide for what’s new which you can find below.
Your feedback is important because other administrators, just like you, would love to hear if it worked or if you found something to save them time. When you post your feedback here, please:
- Remember to read the guide below and keep the focus on this content (the universe and the documents)
- Remember general product questions (like how to use Information Design Tool to do x) are best posted as a new question
- (Questions posted as questions, rather than comments, gets more views and are easier to find by other users)
- Say which:
- RDBMS you are using (HANA, Sybase etc.) (remember there are 7 universes in total, although you’ll only ever use one of them)
- Version of the BI Platform (BI 4.3 SP2 etc.)
- Document name (SYS11 etc.)
- And if it’s a performance comment, mention the number of rows in your Events and your Event_Details tables, and the TimeZoneMinutes and the BOUNDARY_WEIGHT_TABLE settings
- I’d love to hear what you think of the latest update and I’m sure other administrators would like to know if it’s worth the effort!
Resources
| User / Update Guide | Version 1.0 (October 2022) Microsoft PowerPoint .ppt Preview Microsoft PowerPoint .ppt Download |
| Auditing Universe and Documents | Download Build 9 (October 2022) (same download link as in the original blog) |
Original introduction blog with ~570 comments (handy to find common questions & answers) | Blog Unlock the Auditing database with a new Universe and Web Intelligence Documents for BI4.1/BI4.2 |
Contents
- Auditing Solution Update Overview
- Audit Database Platform Support
- Business Layer
- Time-zone support
- Sessions
- Measure objects now guaranteed to return correct results:
- New objects
- Users that have never logged-on
- Optimised ‘Mode’
- Universe to Data Provider
- Business Layer Views
- Lots and lots of other improvements
- Data Foundation
- Web Intelligence Documents
- SYS11 - System - Event Log
- LIC14 - License - Users unable to login due to lack of Concurrent license
- LIC15 - License - Logon Events when users sessions exceeded 10
- LIC16 - License - Users never logged on
- SYS15 - System - Platform Services Log
- USA11 mi - Usage - Session Analysis (Mode is Interactive Only)
- USA15 - Usage - Data Provider Object usage. Identify frequency of all objects given a Data Provider
- USA16 - Usage - Data Provider Object usage. Identify frequency of a particular object(s)
- USA17 - Usage - Web Intelligence Document Report and Page Views
- Install, Configure, Optimise
- Backup and download
- Install
- Use the Information Design Tool to update the connection
- Use the Information Design Tool to configure the time zone
- Optimise (optional)
Auditing Solution Update Overview⤒
Includes support for auditing product enhancements, including:
- How Concurrent Sessions are captured
- Addition events such as Web Intelligence ‘Report’ and ‘Page’ retrievals
- Required a significant re-development particularly in the area of ‘Sessions’, which is where the majority of the content was focused
- Every existing document updated
- 5 documents completely redesigned
- 4 new documents added
- Many product improvements have been incorporated, such as Document input controls and updated chart and table styles
- This update requires minimum: SAP BI Platform 4.3 Support Pack 2
- New audit database support for Sybase ASE and MySQL
- Sybase ASE support needed for those with SAP BusinessObjects Private Cloud Edition
- Update of existing documents to the new edition is seamless,
even if previous audit database was on a different RDMBS platform - (thanks to meticulous care to keep all the ‘ids’ of objects identical across all 7 universes)
- Other improvements have been based on feedback from the SAP Community
- For example, time zone support and query performance
- Popular solution provided ‘as is’
- 500+ blog post comments
- 110,000+ blog post views
- 200 downloads per month,
- even after 7 years since it was last updated
Audit Database Platform Support⤒
Platform Support
- SAP BusinessObjects BI Platform version 4.3 Support Pack 2 onwards
Auditing databases supported:
| Database | Middleware | Database version |
| DB2 | DB2 CAE | DB2 11 for LUW |
| HANA | HANA Client | HANA 2 |
| MySQL | MySQL ODBC 8 | MySQL 8 |
| Oracle | Oracle OCI | Oracle 19c |
| SQL Anywhere | ODBC | SQL Anywhere 17 |
| MS SQL Server | ODBC | MS SQL Server 19 |
| Sybase ASE | Sybase CTL | Sybase ASE 16 |
There is a universe per database platform
- All universes are identical in terms of functionality, they just use different SQL for each
- Whilst the above list of database versions is a subset of the official support provided by SAP, it is expected that all officially supported database versions will work without any problems
The solution remains provided ‘as is’ without any formal support from SAP
Business Layer⤒
Time-zone support⤒
- Time dimensions (Year, Month, Day, Hour, Weekday, Start datetime) now support the time zone of your choice
- Set the time zone in the Business Layer Parameters ‘TimeZoneMinutes’
- Set the number of minutes to your time zone:
- ‘60’ would be UTC+1, ‘480’ would be UTC+8, ‘-330’ would be UTC-5.5
- This is a Universe setting, so all users will see events based on the time zone minutes you set here. If you’d like users to be prompted for their time zone minutes, simply enable the ‘Prompt to users’
- You will need to retrieve the universe, make the change, and re-publish
Sessions⤒
New Objects for Sessions:
- Measure: Sessions (Named Users)
- Measure: Sessions (Named User + Concurrent)
- Dimension: Sessions per user
- Dimension: User Access Type
- These return a true and accurate figure for the sessions meaning the old ‘Sessions Peak (Named User + Concurrent)’ is no longer needed and has been set to a status of ‘deprecated’. This old object returned an estimated number of sessions as it was based off the number of unique ‘session ids’ that existed within a given time and didn’t take into account the difference between overlapping and sequential sessions. These new objects take advantage of an improvement in how the BI Platform audits logon events.
- ‘Sessions per user’ has this description:
- The number of sessions a single user has established for themselves. Should the same user established multiple sessions concurrently, then this property will increase by one for each of the user’s concurrent sessions. When two different users, with each having their own session, this property will return 1. If the same user has two sessions concurrently, then it will return 2. This is not the same as the ‘Sessions’ measure objects which is the total number of sessions across all users. It means this property is helpful to determine when the same user has multiple sessions concurrently irrespective of the type of user they are (named user, or concurrent user)
Measure objects now guaranteed to return correct results:⤒
- The previous document ‘(SYS1) System Event Log’ returned wrong results for Event Duration!
- These measures ‘Event Duration (milliseconds, seconds, mins, hours)’ will now generate multiple SQL paths with any of the objects from the folder ‘Audit Details (multiple values per event)’ to now guarantee right results everytime
- The new ‘(SYS11) System Event Log’ uses new objects to prevent multiple paths but still return the correct results
New objects⤒
- Event Duration (seconds)
- Delay in Recording time (h)
- Both are needed to resolve a ‘fan’ trap when querying the EventDetails table
- Both are non-aggregates and return a value per event
- Other new objects:
- Format
- Filename
- Report (Web Intelligence page number, handy to see which report was retrieved)
- Page (Web Intelligence page number, handy to see which page was retrieved)
- Also a new filter ‘Only Web Intelligence documents NOT saved to the BI Platform’
Users that have never logged-on⤒
- Now possible to query: ‘Users that have never logged-on’
- Existing objects ‘User’, ‘Cluster’ and ‘Tenant’ can now be used (without other objects) to return users that have no events captured
- thanks to @Aggeregate Aware, an Outer-join, alias tables and a new Business Layer filter:
‘Only Clusters, Tenants and Users with no events’
- thanks to @Aggeregate Aware, an Outer-join, alias tables and a new Business Layer filter:
Optimised ‘Mode’ object⤒
- The ‘Mode’ object, which returns either ‘Interactive’ or ‘Scheduled’ depending upon the type of session that caused the event, is now available for general use, meaning:
- no need for any special ‘union’ queries
- all users can use it as a result or filter object
- thanks to a re-design at the data foundation layer which has also dramatically improved performance of this object
- (example: 44 mins now takes 44 seconds)
- same applies to the ‘Interactive Mode’ and ‘Scheduled Mode’ business layer filters.
Universe to Data Provider⤒
Enhancements made by the BI Platform means ‘Data Provider’ replaces ‘Universe’, since more than just Universes are now audited
Many things have been renamed:
New related Data Provider objects
- Data Provider + Object
- Data Providers
Bug fixes
- Fixed various bugs where the Data Provider (Universe), Data Provider Object (Universe Object), SQL/MDX/Query statement didn’t always return a result when they could have
Business Layer Views⤒
- Minor update:
- By default, the ‘Standard View’ is now shown
- Previously it was the ‘main’ view
Standard view shows objects suitable for the vast majority of queries
Each object will only return 1 row per event, it helps to ensure a single SQL Path is used and thus improved query performance
- Previously it was the ‘main’ view
- The ‘Advanced View’ enables access to additional objects, where each object can return multiple rows per event
- Some of the pre-defined documents make use of this
Lots and lots of other improvements⤒
- Default date and number formats set on all numeric objects
- Decimal places for all appropriate measure objects
- Many other minor improvements, too numerous too list
Data Foundation⤒
- All derived tables have been replaced by alias tables
- Shortcut joins used wherever possible
- Means the query time is likely to be improved particularly for:
- Parameters, Parameter Values
- Property, Property Values
- Data Provider, Data Provider ID, Data Provider Object, SQL/MDX/Query statement
- Improvement in how SQL/MDX/Query statements are captured means this property is now always returned by the universe (unlike before)
- Many new alias tables to support additional event details
- such as Report, Page, NamedUserCount, UserType
- Events table has been aliased ‘EventSessionsWithJobServer’ meaning a previously slow subquery has been turned into an outer-join. This redesigned the ‘Mode’ object and has dramatically improved performance
- New context called ‘ForceSeparateSQL’ based off a dummy table:
- This is used by all ‘sum’ aggregate objects based on the Events table
- It prevents a ‘fan-trap’ of the measure duplicating when multiple rows are returned per event
- Means measures ‘Event Duration’, ‘Document Size’ and ‘Rows’ will always be correct now, unlike before
- Updating your existing documents could means the query now generates multiple SQL and thus takes longer to execute
- Use the new standard documents which avoids this, allowing for a single SQL statement, but still obtain the correct results
Web Intelligence Documents⤒
- Every document updated and improved in some way or another
- New document ‘codes’ to help prevent confusion with existing documents
- Just add 10 to the code, for example “SYS1” becomes “SYS11”
- Many queries or documents where completely redesign to take advantage of the Universe improvements
- Many new documents:
- LIC16 - Users that never logged on
- SYS15 – Platform Services Log
- USA16 – Data Provider Object usage
- USA17 – Web Intelligence Document Report and Page Views
- All table/chart styles updated in every document
- (there are 100’s of them!)
- All now use the default style for BI 4.3 Support Pack 2
- A more modern look and feel compared to the older style
- Almost all ‘report input controls’ are now ‘document input controls’ allowing for more intuitive filtering whilst analysing
- Now follows a few selected highlights of these document changes
SYS11 - System - Event Log⤒
- It’s a simple event log
- Bugs fixed:
- Order of events is now correct (wasn’t always the case beforehand)
- Delay in Recording Time and Event Duration are now correct (previously they where not)
- Notes for those updating from the old Universe/Documents:
- Delete the previous document – SYS1 to avoid the query from now creating multiple SQL Paths and dramatically increasing the query time
- The updated document – SYS11 does not use multiple SQL Paths, but still returns correct results
LIC14 - License - Users unable to login due to lack of Concurrent license⤒
- Shows the logon events that failed due to lack of license and the error message shown to the user
- Notes for those updating from the old Universe/Documents:
- The old LIC4 document no longer returns results due to a change in the way errors are captured
- Delete the old LIC4 and use the new LIC14 instead
LIC15 - License - Logon Events when users sessions exceeded 10⤒
- Required a complete redesign of the query to use the new object ‘sessions per user’
- Now shows the logon events when a user reaches a certain number of sessions
- Notes for those updating from the old Universe/Documents:
- Previous LIC5 used the ‘Session Peak’ object which estimated the number of sessions a user had, it didn’t take into account the difference between overlapping and sequential sessions
- The new LIC15 now shows accurate results thanks to improvements to BI Auditing and Universe updates
LIC16 - License - Users never logged on⤒
Shows the users that exist in the BI Platform, but don’t have any auditing events associated to them. i.e. these users are likely to be users that have never logged-on
New document and possible thanks to Universe updates
SYS15 - System - Platform Services Log⤒
- Shows the log of when any BI Platform Service was stopped, started, enabled and disabled for the last 6 months (adjust the query to change this time filter as needed)
- This is a new document, it’s always been possible to build this, but it’s now part of the standard set of documents
USA11 mi - Usage - Session Analysis (Mode is Interactive Only)⤒
- The Sessions (named, concurrent etc.) are only for Interactive Sessions
- Notes for those updating from the old Universe/Documents:
- The old USA1 document used the ‘Session Peak’ object which estimated the number of sessions a user had, it didn’t take into account the difference between overlapping and sequential sessions. It did however return a figure for scheduled events, unlike the new USA11
- Delete the old USA1, instead use the new USA11 document for a true accurate session figure
USA15 - Usage - Data Provider Object usage. Identify frequency of all objects given a Data Provider⤒
- Complete query redesign following universe and auditing changes
- Notes for those updating from the old Universe/Documents:
- For the old USA5 document, the query time was typically unacceptable
- Following Universe optimisation changes and this new query design, the query time is likely to have improved
- Very similar to USA16, though USA16 is likely to have a longer query time
USA16 - Usage - Data Provider Object usage. Identify frequency of a particular object(s)⤒
- This is a new document as a replacement for the old USA5 document
- Given a Data Provider and a Data Provider Object, then the events that Object is used in is returned (time and document name etc.)
- If query time is too long, use USA15 which is likely to be quicker but only filters (at the query) on Data Provider, not Object
USA17 - Usage - Web Intelligence Document Report and Page Views⤒
- New document, thanks to Auditing and Universe enhancements
- Shows page retrieval events, for a whole document right down to individual page numbers of a report
- Right hand side: Page 1 of the ‘Event Log’ Report was read 19 times
Install, Configure, Optimise⤒
Backup and download⤒
If updating, backup and then delete the older solution:
- For: Universe, Connection, and the Web Intelligence documents:
- Use the Central Management Console – Version Management to add or check-in the old content
- And/or use Central Management Console – Promotion Management to create an LCMBIAR file of the old content
- So not to confuse old content with the new improved content, delete the old connection, universe and standard Web Intelligence documents
- Many documents have been redesigned and optimised
- Any custom built documents are likely to carry on working, so there’s no need to delete these, but they will need to be validated on the new universe
- Empty the recycle bin of the now deleted Connection and Universe assets to prevent any conflict when importing the latest versions
Download and unzip latest ‘build’
- Download here
- Unzip and then unzip ‘Build 9’
Install⤒
- Use Central Management Console – Promotion Management to:
- Import the Web Intelligence documents
- Import 1 of the 7 Universes, depending upon the RDBMS of your Auditing Database
- All universes, and all objects within those universes, all share the same IDs for pretty much everything. This means you can import any 1 of the 7 universes and the same set of Web Intelligence documents will work with any of them. There’s no need to re-bind or re-associate data providers with a different universe. It also means, you can change RDBMS at any point without the need to change any documents, by importing the universe for that RDBMS
- The promotion job will also import the associated connection (there’s a connection per RDBMS but only 1 connection will exist per Universe LCMBIAR file)
Use the Information Design Tool to update the connection⤒
- Open the Connection corresponding to your RDBMS
- (there will only be 1 audit connection per Universe LCMBIAR file, so you’ll see just 1 unlike the screenshot shown here)
- You may need to ‘Change Driver’ to use a different Database than the one specified
- If you do, be sure to keep the same Network Layer as the one shown for your connection
- Edit the connection and follow the wizard updating all the necessary fields
- (username, password, database hostname, port etc.)
- Sybase ASE and MySQL have a ‘ConnectInit’ command set. Read the description of the connection for more details about this setting to determine if it applies to you (also copied below). If you’re unsure, just leave it unchanged.
- Test the connection works
- You will either need locally installed 64-bit middleware or you can use the BI Platform Server middleware if you change a preference setting as shown
- ConnectInit command for Sybase:
- Please note the ConnectInit command 'set cr752225 off’.
- This may NOT be needed. Please see https://launchpad.support.sap.com/#/notes/0002647510 for more details that resolves an error 'Adaptive Server finds no legal query plan for this statement' when running some of the provided sample Web Intelligence documents that form part of this Auditing Solution
- ConnectInit command for MySQL:
- Please note the ConnectInit command 'SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));'.
- This may NOT be needed. Please see https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html for more details about this command. It resolves an error 'Expression #1 of HAVING clause is not in GROUP BY clause and contains nonaggregated column 'DATABASEOWNER.Event.Start_Time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by' when running some of the provided sample Web Intelligence documents that form part of this Auditing Solution
Use the Information Design Tool to configure the time zone⤒
(1) Retrieve the Universe from the Repository. You may need to create a local project to retrieve it into beforehand
(2) Open the Business Layer and the (3) Parameters and Lists of Values
(4 to 😎 Set the Time Zone Minutes for your organization as appropriate:
- ‘60’ would be UTC+1,
- ‘480’ would be UTC+8,
- ‘-330’ would be UTC-5.5
(9, 10, 11) Test the connection is valid by running the test query
- Optionally compare the performance with a TimeZoneMinutes setting of ‘0’ and your desired setting with this Test Query to give you an idea of the cost of setting it
Save and republish the universe back to the Repository
- Please do NOT perform an integrity check
- If you do, please ignore any errors, it’s expected that some datatypes for objects appear to be set incorrectly. Just ignore these errors and warnings.
Optimise (optional)⤒
- Should you experience performance issues when running the documents, you could consider:
- Setting the TimeZoneMinutes parameter back to the default of 0
- Enabling the ‘BOUNDARY_WEIGHT_TABLE’ parameter
- This will alter the SQL to create dynamic in-line views containing WHERE restrictions before then joining to other tables
- The setting uses the ‘table rows’ metadata inside the data foundation to determine which tables this applies to
- Assuming you have not updated the ‘table rows’ metadata in the data foundation, then the settings shown in the table will be applicable
- Enter the value of the parameter for the universe you are using
- 782339 if you’re using Sybase for example
- Test firstly with the ‘Event Details’ only (2nd column in the table below)
- Test thoroughly! Check the documents work and the performance improves
- If successful, but performance is still poor, apply the settings for ‘Events’ table too (3rd column in the table below)
- Update the value, 74700 if you’re using Sybase for example
- Test thoroughly! Check the documents work and the performance improves
- Disable this setting by setting it back to ‘-1’ should you encounter issues
| BOUNDARY_WEIGHT_TABLE setting to enable this on ‘Event Details’ tables | BOUNDARY_WEIGHT_TABLE setting to enable this on ‘Event Details’ and ‘Events’ tables | |
| DB2 | 178814 | 17114 |
| HANA | 2974855 | 270758 |
| MySQL | 560078 | 69395 |
| Oracle | 774644 | 84650 |
| SQL Anywhere | 110515 | 14573 |
| SQL Server | 6008089 | 680989 |
| Sybase | 782339 | 74700 |
- Share your experience in the community so others can benefit
- Others will be fascinated!
Validate the ‘outer-join’ performance is better than ‘sub query’ for Business Layer Filters ‘Interactive Mode’ and ‘Scheduled Mode’
There are 4 queries, in the Business Layer, for this purpose
2 use an outer-join to determine the ‘Mode’. This is now the default
2 use a sub-query. This was how these filters worked in the older version of the universe
Execute the queries and validate the ‘outer-join’ queries run better than the ‘subquery’ ones
- Should the subqueries out-perform the outer-join, then you’ll need to update the filters to be the same as the alterative subquery one
- Share your experience in the community if you needed to do this
- Share the RDBMS platform you’re using and the number of rows in the Events table along with your query times
- Others will be fascinated!
- SAP Managed Tags:
33 Comments
