Skip to Content
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:

  • 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!



  • 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.



  1. Download the content (take the highest build numbered zip file) (link currently expires 22/March/2019 – will be extended again then)
  2. Import one of the five ‘Universe’ LCMBIAR files into your system using Promotion Management (it will go into “BI Platform Auditing” folder)
  3. Import the Web Intelligence LCMBIAR file (it will go into “BI Platform Auditing” folder)
  4. Edit the connection that is imported (in “BI Platform Auditing” folder) with the correct login credentials.
  5. Open the Web Intelligence document ‘STA1 – Start here – Events over time.wid’ as your starting point!





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,



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.


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!


More info on my blog

You must be Logged on to comment or reply to a post.

    Hi Matthew

    I was wondering if your Universe will enable me to split up total run time for reports into various parts

    Report is scheduled, and goes into
    1. Pending mode
    2. Pre-processing, prompt resolution, SQL generation etc
    3. Waiting for query to return from database
    4. Post Processing
    5. Distribution


    Can we just measure time spent on pre and post processing?

    • Hi Paul, The duration measure will only capture the duration if the individual event, and the events it captures that duration for is limited, I think to only refresh. Having said this, you could well determine the duration by looking at all the startdatetime of other events within the same ‘action’ or ‘session’. So my guess is you could probably determine some of these bits. Item 3 ‘waiting for query from db’ isn’t something we know as we just pass the query to the middleware and wait for a reply. Thanks for the requirements. I’m interested to know more about the business requirements here. Are you trying to identify where performance bottlenecks are so you can improve delivery time to end users? Regards, Matthew


        Hi Matthew

        Thanks for your reply, and yes we are trying to identify our production bottlenecks.  However just over a month ago we added 12 CPU’s to our 2 servers to bring the total up to 20.  That has made an amazing difference. 😉



  • Hi Matthew!

    Thanks for a revolutionary update on BO Auditing.  Finally we have something promising.   I have come across an issue with Auditing that has to do with View Event Count.   I am seeing inflated View counts when webi reports are opened via launchpad.   However, the view count seems to be correct when webi reports are consumed by rich client.  Viewing a report via launchpad (html version of webi), I get view count =2. While viewing a webi report via rich client tool, I get view count=1.

    Scheduling a report gets the same count ( that is, 2).

    I am working on BO 4.2 SP4 and CMC db is Oracle.

    I have posted my question BI Platform space as well (link below)

    Wondering if you have any insight into this.


    • Good question!

      So my universe is missing an important BusinessObject called ‘Service’. (I can’t quite believe I missed that!). If it had ‘Service’ then you wouldn’t actually see multiple view events.Well you would, but for different Services. So, for your particular query on view of webi docs, you need to add a filter Service_Type_ID = ‘AbI5_LpMFxFLhxl7DV91KYY’. With this you’ll get a lot less rows returned and you should (I think) get the right result.  I’ve got this added into my long backlog of improvements to make on the Universe.  Not sure when I’m going to add them all in. I might well develop an measure BusinessObjects “Web Intelligence Views”, but we’d need to have an object for all the other content types too, so I might opt for a more generic object, like “Object Views”, which is the number of views of an object irrespective of the type (Webi, CR, Lumira etc.).

      In BI 4.2 SP5 we plan to add events for Web Intelligence Report [Tab] Names (today it doesn’t capture the ‘Report Name’, just the Document Name). Additionally we plan to capture the page number within the Report. It will allow you to then see which reports and pages are viewed the most. There’s a few other auditing fixes in BI 4.2 SP5 particularly around Web Intelligence auditing.  Regards, Matthew

      • Thanks Matt. It would be good to have number of DB rows fetched for the report too similar to number of pages as this will enable us to grade the reports based on data volume.Right now we are grading reports only based on execution time.




        • Hello Mani, the number of rows returned is already captured in the Audit DB and there’s an object i in my universe for it too! Regards Matthew

      • Thanks Matthew Shaw .   That did the trick for webi inflated views, however, now the report I have developed doesn’t show any activity on other object types ( for example, Crystal report, Design Studio Dashboard/Analysis Application,  etc.).   When I take out this Service Type ID filter from my report, other objects activity shows up.    This custom report is designed to show activities for multiple object types (crystal reports, webi, dashboards etc.).  Is there a way I can show other objects’ activity at the same time within the same report.  Do I need to add Service Type ID filter for other object types (analysis application, crystal etc.) as well?

        Thanks for any help you can provide!

        • Well you just need to move the ‘WHERE’ functionality into the SELECT.

          Create a new measure object of the form: SELECT count( decode(Service Type ID, ‘AbI5_LpMFxFLhxl7DV91KYY’, TABLE.Column )). (with this TABLE.column is the table.column of the measure you already have in the measure). There is no WHERE clause.

  • Hi Matthew,

    I downloaded the oracle version of universe and webi reports and imported to the BO system, but its showing error like table or View does not exist.

    it can be use only for 4.1?.  Now I am using 4.2 SP4  Please let us know..






    • Hello Ravi, it works for BI 4.1 and BI 4.2. You might be best to review the many comments in this blog. I think there’s at least one other customer with the same error and a rely to resolve it. My guess is that you’ve got a ‘owner’ issue, like you’re connecting not as the owner of the schema. You might need to update the universe with an owner qualifier in the Data Foundation. Regards, Matthew

  • Hi Matthew,

    We have migrated only Cyrstal from 3.1 to 4.1 SP7 version so in our environment we don’t have Webi Intelligence.

    So can you please let me know how can I use your universe for auditing?




    • Hello, thanks for your interest. What I’ve provided is a UNX and a set of .WID Web Intelligence documents. The documents contain some quite complex queries and much of the business logic is held in those query definitions. Nevertheless you could use the UNX Universe with Crystal Reports for Enterprise to gain the value of the universe. Regards, Matthew

      • Thanks Matthew for the information. We don’t have Crystal Reports for Enterprise in our organization. So i’m not fortunate to use your universe. Is there any workaround for this .

        • Hello Sriram,

          You’d need to get the SQL that is generated and copy/paste that into Crystal Reports. That’s the only workaround I can think. But then, you’ll still need either Crystal Reports for Enterprise or Web Intelligence to generate the SQL in the first place. Sorry the solution isn’t available for Crystal Reports. Regards Matthew


  • Hi Matthew,

    Can you please port the Universe for the below database,

    Sybase Adaptive Server Enterprise 16.0

    This is the one which we are using as the Audit Database.



    Arun Krishnan G

  • Thanks Matthew!!! great article.


    Looks like a gap in BI Auditing Program

    Number of rows is not getting captured for schedule events = 1011.

    written a query which might be helpful to see what all the event detail type information we can get for different events.

    Let me know for any thoughts or ideas.


    select distinct d.event_type_id, d.event_type_name, a.EVENT_DETAIL_TYPE_NAME from ads_event_detail_type_str a
    join ads_event_detail b on a.EVENT_detail_TYPE_ID=b.EVENT_DETAIL_TYPE_ID
    join ads_event c on b.EVENT_ID=c.EVENT_ID
    join ads_event_type_str d on c.EVENT_TYPE_ID = d.EVENT_TYPE_ID
    and a.LANGUAGE = ‘EN’
    and d.LANGUAGE = ‘EN’
    and c.start_time between ’01-OCT-2017′ and ’02-OCT-2017′ /* used to filter ads_event table records. change it based on need*/
    order by d.event_type_id asc;



    • Hi Aditya –


      It’s there — but part of the REFRESH event (1003) , not the RUN event.


      Group by the RUN event’s SESSION ID and you’ll see what I mean.


      Hope this helps –



  • Hello!  What a great tool this will be!


    Some clarification if you don’t mind.  What version of this .unx etc, do I download if I am on the BI 4.1 SP3 version of the platform?

    • Hello Serena

      I believe previous questions/answers on this have been asked and replied above. I know there’s a lot of comments. The chances are, most, if not all, questions can be answered by looking in previous comments to this blog, or watching the utube video.

      For your question the requirements are as mentioned


      • 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

      So, you need to have at least BI 4.2 SP5 or higher. That’s quite old now so should suit most.



  • Hi Mat,


    Thank you.


    I downloaded Build 8.5 – BI4.1 UNX and WIDs (requires BI4.1 sp5 or greater).

    Promoted the universe, connection and WebI documents to SAP BI platform 4.2 SP03 Patch 6. Auditing database version MS SQL 2014.


    I’m facing an problem when creating new documents on top of BI Platform Audit Analysis.unx.

    For example, when I create new document on SAP BI Launchpad, then the List Of Values are empty. This means that I can not choose Values from List when creating report and I can not use prompts when refreshing reports.

    There is no such a problem when I use List of Values in information Design Tool Query Panel.

    Same problem appears with SAP BI platform version 4.2 SP01 Patch 2, Auditing database SQL Anywhere.


    We would really like to use this universe on BI launchpad platform.


    Best Regards,


    • Hello,  The issue isn’t directly related to the universe, it’s either a product issue or a configuration issue. My guess is this is a configuration issue, for example the environment isn’t setup correctly, such as middleware. IDT will use 32 bit middleware, whereas the server will use 64 bit middleware. So your connection to the Audit Database is probably missing the 64 bit middleware on the server. Either way, you need to troubleshoot the problem as its most likely an environmental issue, not actually a problem with the universe. If the Universe works in IDT, then the universe is fine. Similarly if the document ‘STA1’ works, then the BI Platform and its connectivity to the database is fine.  Regards, Matthew



    Hi, I cannot get the reports to work in my 4.2 environment. I get a message that references article 2054721 (IES 10901) (Error: INF) [Microsoft][ODBC Data manager] The following database error occurred: data Source name not found and no default driver specified. It says to reference article 2054721, which tells me I am SOL.

    I created the ODBC on the server and on my workstation for Audit SQL Server. originally I did in in a 64 bit SQL driver, but then read that WEBI reports may have an issue with 64 bit, so I changed it to 32 bit. But that did not help either.

  • Hi BI Audit experts,

    Audit DB offers a sample report “USA4 – Usage – Universe Objects usage”. However it does not work and I cannot retrieve any rows. I have also noticed on this blog that this report is “Partly fixed in BI 4.2 SP2. Still working on it.”.

    Using the Audit universe I have found out the reason for getting 0 rows – Derived table “DerivedDetailUniverseObjectNameAndQuery“. This derived table retrieves 0 rows on my BI platform.

    “DerivedDetailUniverseObjectNameAndQuery” SQL code:

    SELECT DetailUniverseObjectName.Event_ID, DetailUniverseObjectName.Bunch, DetailUniverseObjectName.Event_Detail_Value as UniverseObjectName, DetailQuery.Event_Detail_Value as Query FROM ADS_EVENT_DETAIL DetailQuery INNER JOIN ADS_EVENT_DETAIL DetailUniverseObjectName ON (DetailUniverseObjectName.Bunch = DetailQuery.Bunch AND DetailUniverseObjectName.Event_ID = DetailQuery.Event_ID AND DetailQuery.Event_Detail_Type_ID=25 AND DetailUniverseObjectName.Event_Detail_Type_ID=31)

    DerivedDetailUniverseObjectNameAndQuery Description copied from BO Audit universe: (This is a derived table associating The UniverseObjectName and the Query by the Event ID and Bunch. It joins the Event Detail table by EventID and Bunch and applies restrictions on the Event_Detail_Type_ID 25 for ‘Universe Object Name’ and 31 for ‘Query’. )

    When I run the query where Event_Detail_Type_ID=25 I retreive 0 rows.

    SELECT DetailUniverseObjectName.Event_ID, DetailUniverseObjectName.Bunch, DetailUniverseObjectName.Event_Detail_Value as UniverseObjectName FROM ADS_EVENT_DETAIL DetailUniverseObjectName Where Event_Detail_Type_ID=25

    When I change the query from Event_Detail_Type_ID=25 to = 31, I retreive many rows showing Universe objects. Possible from the queries that have been run and captured by Audit DB.

    Does anyone have a working solution, or SQL code that could retrieve the “Universe Object Name” used by the SAP BI users? Thank you in advance!

  • Hi Matthew


    I am trying to run the USA1 x15 u – Usage – Session Analysis (With Users, Without Mode) report, but I keep getting the error

    Database error: [Microsoft][SQL Server Native Client 10.0][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation.. (IES 10901)

    I have tried to see if I could identify what is causing the error, but so far without any luck.

    I did run the WebI SQL directly on the Audit database, and there were no errors there.  I did also try, instead of running on all the data, taking a small subset of data, eg. the last two months, but the error still occurs.

    Any ideas on what might cause this error?


  • Hi Matthew,

    We are using the audit universe for our internal Ad-hoc reporting where we want to do some usage analysis.

    We were able to refresh 6 months data within 5 – 10 minutes. In order to get accurate user usage details we will need ‘Universe’ object in our report. But when we use the universe object ,the report is having performance issues. We tried scheduling the report and it took around 5 hours to complete.

    Since our goal for this report is adhoc-reporting, it is not serving the purpose. Is there a way to fine tune the ‘universe’ object and improve the report performance ?

    Appreciate your hard work in developing and improving the audit universe..


    Thanks & Regards

    Vijay M


    • Hello Vijay

      I understand the problem and many others have reports poor performance when using the derived tables in the universe. I think the main reason for the poor performance is the derived tables do not limit on time and so it takes ages for the RDBMS to build the table in memory. Worst still, the table is the biggest as its the details table.

      So until I redesign you could just create an alias of the details table and filter on the UniverseName property Event_Detail_Type_ID=14, just like I did for most of the other detail objects.

      Thanks for posting the feedback. Other users may have already done this and can make other suggestions!;-)

      Regards, Matthew

  • Hi,


    We’ve managed to set up the Audit universe and the sample reports on SAP BO 4.1 running on Oracle DB. We are experiencing an issue when we are using the date filter in reports. It seems that the issue is related with the date formatting.   We are getting an error msg ‘ORA-01843: not a valid month’.

    Anyone came across this issue?


  • Hello Matthew,

    We have downloaded file the one you provide in this page and deployed universe to  IDT 4.2 version. Connection is working fine. Here Deployed universe doesn’t have alias a’s so business layer objects are not parsing. We tried 2 times to deploy the same universe. Still same issue.

    Please advise.



    • Hello Sandhya

      If you could describe more about what the problem is, what you’ve tried to do, what works and what doesn’t work. That would help the community to help you. A screen shot wouldn’t go a miss. Remember this solution is used by a lot of customers, so setup issues tend to be locally related configuration issues. Best to assume what’s provided works for others, so what is it about your setup that doesn’t allow it to work. You could, for example, compare it to other universes, or what happens if you try to build a universe yourself off the same data source. etc. etc.  Regards, Matthew



    Hi Matthew,

    I’m not able to access the download site it says the Share has expired; Can you republish?

    Or should I  open a ticket with SAP and you can upload to the ticket?


    Hi Matthew,

    That’s an awesome blog. Thank You!

    Few questions:

    1. How frequently is audit database refreshed or updated? I refreshed a report this morning and trying to refresh my audit report(a report with top level folder id, report name and event type refresh/delete) to reflect the same and I do not see a changed time on it yet?
    2. I also deleted 2 reports last week from same top level folder id as 1. and see those 2 reports with event type refresh but do not see delete event type instance?

    AM I missing something here? Please advise



  • Hi Matthew,

    I am currently working with the audit universe to create report that show number of views on reports, but today I encountered a problem with the language conversion. Originally i built the report around english viewing locale, but some users where using danish viewing locale. This ofcourse triggers the language conversion you have build into the universe, and consequently the report working correctly.

    This was of course due to filters and variables depending on english texts from Event Type, Object Type and the likes. A workaround to this is of course to use the corrosponding ID’s instead to avoid language conversion clashes.

    However during my investigation of this issue i came across several instances where data for a dimension was not translated, but rather null’ed instead. This was encountered for Object Type = ” Analysis Application” which i did not find any translation for in my report. Is this expected behaviour? or am i missing something here?

    SAP BI version: 4.2 SP3 Patch 4

    Audit DB: SQL anywhere

    Best regards


    • Hello Rasmus

      Could you please log this issue with our product support team and then (eventually) post the final response/resolution here. This is a product issue, not an issue with the universe. You could manually enter the missing lookup (not ideal) you could also point the audit connection at an empty database and allow the product to re-create all the tables (and lookups). You could then copy the table as appropriate, assuming it gets created with all the right values. I think the challenge here will be determining how the missing values are missing, product support will want to identify the root cause and fix that, rather than the symptoms.

      Thanks so much for posting nevertheless. Regards, Matthew


  • Hi Matthew,

    As per 2151625 – Auditing Analysis for Excel : Is It possible? KB(4.0 and 4.1), Analysis for Excel is not audited.

    But as per

    Events by Service-type

    Service type Event types generated
    Analysis Applications
    • View
    • Cube connection
    View An object is Viewed.

    • Analysis Applications
    • Analysis edition for OLAP
    • BI launch pad
    • BI Workspaces Application
    • BI Commentary Service
    • CMC
    • Crystal Reports Cache Service
    • Crystal Reports Viewing and Modification Service
    • Dashboards Cache Service
    • Desktop Intelligence
    • Information Engine Service
    • Open Document
    • SAP BusinessObjects Mobile
    • Web Intelligence
    • Web Intelligence Common Service
    • Web Intelligence Core Service
    • Web Intelligence Processing Service

    We are on BI 4.2 SP5 Patch 1, SQL server. I tried to query your universe for Analysis for excel objects but I don’t see any events.

    Does your universe cover the audit events related to Analysis for Excel?



    • Hi Matthew,

      I enabled retrieve and analysis events in Auditing. Now AO workbooks retrieve events using BI launchpad/Opendocument/Excel plugin are captured and displayed in audit report but no modify/save events are captures.




  • Hi  Matthew/BObj Experts,

    I have downloaded 4.x audit universe(Thanks Matt) and imported to Dev system . I’m trying to build few sample reports on this universe , but failed to understand the objects . I would be thankful for any suggestions or help on the below reports .

    User usage – count of reports used by user daily

    Report usage – Frequently used reports

    Report mofification , who , when

    Report Scheduling – count of scheduled reports

    System Load – user session daily

    Deleted reports – what reports were deleted and who deleted them



    • Hello Phang

      I would first recommend you watch the utube video for an overview, then study the detailed description for each object in addition to the descriptions provided in each of the sample documents. Regards, Matthew

  • Hi Matthew,


    Not able to download the content.

    Following error :

    Sorry, the link has expired.
    The link was set to expire after a certain amount of time. Please contact the person who shared this link with you.

    May you please republish.


    Madhukar Pandey

  • Hello Mat,

    Great work! We recently had request for Auditing implemented in BO and using this universe, we could deploy it successfully. Thanks!

    Though we are facing one issue, the audit configs on CMC  is done via ‘dba’ user . And while creating relational connection in universe, we created it using ‘Audit_dba’ user (this user is a copy of ‘dba’user – due to prod access issues, we are not able to use dba user for relational connection) . So when we access the Audit DB using these 2 different user ID we can see the audit tables and data.

    But when we try to create the WEBI reports, it throws error saying unable to find the audit tables.  Also, in IDT, under the connections, this ‘Audit_dba’ user has no tables in it and ‘dba’ has it.

    Is the issue because we have used ‘dba’ user in cmc config and now using other user id?


    Many Thanks!



    • Hello Hetal

      thanks for the feedback.

      I’d recommend editing the connection that you import as part of my solution, rather than creating a new one. It just saves you having to re-associate the universe with a different connection.

      The issue you describe seems to be a problem reading from the audit tables, it can’t find the tables. It means the data foundation layer isn’t qualifying the table names with the owner to meet your requirements. You’ll need to add the qualifier to the tables. Its a property of the tables/data foundation. Once you’ve done that, you need to do a ‘view table’ (on the TABLES, NOT on the aliases) to check its working ok. You could even run an Integrity check too. Then you can republish the universe and you’re job done. It shouldn’t take 5 mins.




  • Hello Matthew,

    We created a simple audit report extracting only run,refresh,view events in the report (applied a report level filter)

    Also there is a filter on application type equal to BI launchpad.

    This results only in view event results. Later on I found that, refresh and run events have application type as blank hence were not appearing in the report.

    Also, when going through your blog, we see this is a defect which has been mentioned by your ref no as  1670026096 and it says, application type values are missing. Also,  KBA 2391022 says issue has been resolved in 4.2 and 4.1 Sp10 BO.


    But, our BO system is of 4.1 SP 10 version and still this issue is persisting.

    Any idea why such behavior?

    Many Thanks!




    • Hello Hetal,

      Thanks for checking the details of the blog carefully. I think you need to log a support incident for this issue. (The ‘old’ values for audit event before you installed the ‘fixed’ version won’t be updated, so I’m assuming your issue applies to new events as much as old ones). Sometimes the events are actually there, but the ‘lookup’ value is missing and this I think applies for you. Might be worth checking that out. Also, sometimes if you ‘drop’ (well backup first please) the lookup table, the CMS will rebuild it and write back in all the ‘right’ values. That might be worth trying too. Regards, Matthew


  • Hi Matthew

    I have used this universe for quite some years, but now we have migrated our Audit-database to Sybase ASE, and I discover that the universe is not ported to Sybase yet 🙁

    Do you have any plans for that?

    Kind Regards

    Simon Lyng

    • Thank you Simon for your feedback. I’m pleased to hear you like it. I’ve not yet ported this to Sybase and currently I don’t have any plans to do so. I do get frequent requests for this. This is very much noted. Regards, Matthew


  • Hi, I got a tips about this solution today. Tried to downoad the zip-file but the link was not valid?

    I would be really happy if you can activate it again.


    Trond Erik

  • Dear Matthew Shaw ,

    We encounter strange error when trying to refresh the STA1. The error saying “The universe does not allow using a complex expression in a GROUP BY statement. You cannot run this query. (Error: INF)”.

    Our auditing database is SQL Anywhere that come with installer. Thank you.

    • Hello Teuku, thanks for posting the document name and the Auditing RDBMS type. This is very odd though. Anyone else with a similar issue? You’ve imported the correct Universe I presume? Regards, Matthew

  • Dear Matthew Shaw,

    We have not implemented any changes in production and since May 21, concurrent users sessions are not getting displayed in our system. I was using the usage session analysis report for the last 1 year and the issue started happening since May 21.

    Also session count is showing very less and I saw more session when I check manually in CMC in June month



    • Hello Gopal

      You’ll need to investigate further.

      I suggest you look at the CMC-Auditing to ensure all options are selected (except for ‘user-group details’) and check you have users that really are of type ‘concurrent’ (and not ‘named’)

      Once you’ve checked that, then look at the actual database entries in the AUDIT table to ensure there’s nothing in the universe that is filtering it out (could be a poor lookup value in a database that is somehow filtering the main AUDIT fact table. i.e. check by running SQL withOUT any other tables in the query other than the main AUDIT fact table).

      Once you’ve done that. you’ll need to log a support incident assuming its a product defect (unlikely by the way).

      My universe is for getting stuff out of the audit db, so you need to check its first got in ok. If the data ins’t getting into the audit db, then its Product Support, not me. Sorry this doesn’t provide a solution, but I hope it provides next best steps to follow. Let us know how you get on.

      Regards, Matthew



    Hi Matthew, 4.2 SP5 – does auditor not capture the SQL for freehand sql queries?

    We get it for universe-based queries just fine, but for FHS something like select sysdate from dual just tells us that there is an object with the name ‘sysdate’.  We get the connection used and the name of the query but no SQL.

    • Hi Nick,

      No, the Freehand SQL isn’t captured. Its a good ER and in the backlog, but I’m not aware of any commitment. Regards, Matthew

  • Hi Matthew

    Your hardwork is a boon to many of us. We have been using the auditing dashboard for quite a while now. Tracking usage activity and no. of reports frequently accessed. However, we now want to look into some of the authorizations a specific user has been given. We basically use Windows AD group authentication. We need to know for each user what access they have been given. I have attached a screenshot here for better understanding.I need Alias info from the screenshot below for each user. I have skimmed through the Auditing Universe but couldn’t get the right information. Any help on this would be highly appreciated.

  • Hi Matthew,

    As our Audit database is running on MySQL 5.6, do you plan to release MySQL content of the universe and sample WebI documents?




  • Hi Matthew-

    I was able to import the Audit universe.But while creating the audit report using “Universe Object” it is taking so much of time to provide the results in production.My Audit Database having 190GB filled out of 220 GB. Could you please help me on this how to resolve this issue? We have created indexs and everything. But still no use.


    Mahendra Reddy

  • Hello Experts,

    We are using BI 4.2 and our audit database is Sybase.

    I have downloaded the sample Audit Universe but this seems to be for BI 4.1 —- Version 2 – for SAP BI 4.x

    I just want the sample audit Universe for my purpose. Will this downloaded .unx file be compatible with my BI 4.2 setup ??


    Thanks in Advance.


  • Hi Matthew,

    Many thanks for offering this insight into Auditing DB and spreading this knowledge.

    I’ve recently went through your set of the Auditing reports and after running USA5 – Usage Universes,

    and I’d like to ask to clarify difference between Universe Event and Universe Action tabs of that report.


    Thank you!



    • Hello Edward

      Well the difference between an action and an event is that an action could result in many events. There’s a 1 action to 1 or many events. In practice however, the audit data doesn’t always capture it like that. It depends on the action.   Sorry for a vague answer. Others might be able to contribute if you could describe the action/events. Regards, Matthew

  • Hello Matthew:

    First, Kudos for your great contributions to the BusObjects community.

    My question, is there any plan to include this Auditing universe and Webi rports of your in future installation packages of SAP BI BusObjects?

    Andreas J.A.

  • Hi Matthew,

    Thanks for your BO Auditing Universe.

    However, we encountered problems with documents using “universe” dimension. We have very few universes on our platform, when I want to show the values on IDT the query turns but does not display anything, then on the server the “temp” file only increases when I start the update of documents until reach a huge size, do you have a solution?

    • Hello Ali,

      I need to remodel that Universe object so its not based on a derived table, for the size of the table of that derived table is too big for productive use. A solution would be to create a new alias as I’ve done for almost all the other detail values. The only thing that wouldn’t be quite right is the relationship with other universe properties. Regards, Matthew


      • Hi Matthew,


        When you say “A solution would be to create a new alias as I’ve done for almost all the other detail values” you mean lke that :

        • Hi Matthew,

          I have some issues when i want update the webi document with this dimension or only show values on IDT, in detail :

          • Disk space is filling up too fast with the temp file so i need to stop database and delete this file then restart
          • On IDT I cannot see any objects from the table even with a “show values”, the transaction runs without anything displayed even after one hour

          I tried with the solution I posted above…

        • Yes, but you may be missing a self-join to filter only the Universe name rows. Don’t forget the outjoin and don’t forget to add it into the right existing contexts. My guess is the data volume/time issue is due to the lack of a filter on Universe name.


  • Hello Matthew,


    thanks a lot for your work on it.
    Unfortunately, link is expired again, can you please reactivate it?

    Also, is the connection for Sybase ASE & MySQL DB available ?

    thanks a lot,



  • Hello Matthew,


    We have a test install of BOBJ 4.2 SP06 and promoted the auditing universe for SQL Anywhere. However, when I go to open STA1 report I get the following error. It’s on 64-bit RHEL Linux.

    The following database error occurred: (CS) “DBDriver failed to load : /​h​o​m​e​/​_​b​o​e​/​s​b​o​p​/​s​a​p​_​b​o​b​j​/​e​n​t​e​r​p​r​i​s​e​_​x​i​4​0​/​d​a​t​a​A​c​c​e​s​s​/​c​o​n​n​e​c​t​i​o​n​S​e​r​v​e​r​/​d​r​i​v​e​r​s​/​l​i​b​6​4​/​l​i​b​d​b​d​_​d​b​o​d​b​c​1​6​.​s​o ( cannot open shared object file: No such file or directory)” . For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901) (Error: INF )

    Have you seen something like this?

    I checked and the file really does exist. Using ldd I show it links against a bunch of files in the linux64 folder.

    Thanks for any help.


  • Hello, I am looking to get a report that will show all reports in the system and the last time they were run.  Seems reasonable.  I know I can get all reports via the CMS Driver and left join that to the Audit Data so it returns records that match on CUIDs and also shows Nulls where reports were never run.  (What you used to have to do via Excel prior to 4.2 SP3).

    However, getting the MAX Event Start Time Occurrence seems to be a bit harder.  I had tried under the old Universe and checked under yours and didn’t see a roll-up for it, just taking it and trying to do a calculation on it which for the life of me I could not get WebI to do it properly and only show the Max .  In the old universe I had to create a derived table with a select OBJECT_ID, MAX(Event_StartDate) AS MAX_EVENT FROM ADS_EVENT GROUP BY OBJECT_ID. Then outer join that the MAX_EVENT.  It then worked as expected in WebI dropping in the new field and filtering where they are the same.

    Besides running the same change in your universe, is there a better way with your structure or is this a change that can be requested?  I know the universe hasn’t been updated since version 8.5 in Dec 2015 and there have been quite a few changes made to BOBJ since.

    Thanks in advance, Nathan


  • Hello,

    Thanks a lot for this topic.

    I tried to use the Hana Universe but I am facing to this error:


    The following database error occurred: SAP DBTech JDBC: [259] (at 156): invalid table name: Could not find table/view ADS_EVENT in schema SYSTEM: line 7 col 3 (at pos 156). For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901) (Error: INF )


    Am I missed something?

    I am on BI 4.2 Support Pack 5 Patch 3  and  Hana version


    Is anybody already had this kind of issue?


    Thank you.