Skip to Content

An understanding for how your BI Platform is used and utilised will enable you as a BI Platform Administrator to take the necessary steps to improve its reliability, performance and adoption within your organisation.

 

The Auditing database coupled with a new comprehensive Universe and a set of Web Intelligence documents that I have developed will help give you that insight you need and this is what I’d like to share with you now.

 

My Universe and documents have been in development, on and off, for some time but they have now reached a maturity level where I’m happy to share them with a wider community.

 

I’m overall pretty happy with the Universe and the documents, however they need a little performance testing on large data sets. This is where you can help me, help you!

 

Please download my latest ‘build’ (available for a limited time) and give them a blast. They are provided ‘as is’. I’m looking for feedback on any defects, performance issues and also additional reporting/business requirements. If you can get back to me with your feedback I can improve the content for everyone else to benefit.  I may occasionally published a newer ‘build’ in the same container, so check every now and then for an update.

 

Once I’m happy with the amount of feedback and testing I will make the Universe and documents more widely and permanently available.

 

I have ported the universe to various databases and is currently available for:

  • SAP HANA
  • Microsoft SQL Server
  • Oracle
  • SQL Anywhere
  • DB2 (added 18/11/2015. Limited testing, please expect some issues!)

Feedback on which database I should next port to would be helpful too!

 

There’s a large set of documents, each with a number of ‘reports’. The number of reports ranges from 1 to over 50 within a single document. So you can see I’ve been busy! They will take you some time to go through them all.

 

Here’s a list of documents:

1.     STA1 – Start here – Events over time.wid

2.     FRA1 – Fraud Detection – 1 machine more than 1 user.wid

3.     FRA2 – Fraud Detection – 1 machine more with multiple logon failures.wid

4.     LIC1 – License – 1 user more than 1 machine.wid

5.     LIC2 – License – Periods when sessions exceeded X.wid

6.     LIC3 – License – Users no longer using the system.wid

7.     LIC4 – License – Users unable to login due to lack of Concurrent license.wid (added 20/11/2015)

8.      LIC5 – License – Periods when named users exceeded 10 (added 25/11/2016 – download as a separate LCMBIAR file)

9.     SYS1 – System – Event Log.wid

10.     SYS2 – System – Delay in Recording of events to Audit Database.wid

12.     SYS3 x1 – System – Overall System Load Analysis (without Mode).wid

          SYS3 x2 mi – System – Overall System Load Analysis (Mode is Interactive Only).wid

          SYS3 x2 ms – System – Overall System Load Analysis (Mode is Scheduled Only).wid

          SYS3 x4 – System – Overall System Load Analysis inc Mode.wid

12.     SYS4 x1 – System –  Refresh Analysis (Mode is Interactive).wid

          SYS4 x1 – System – Refresh Analysis (Mode is Scheduled).wid

          SYS4 x2 – System – Refresh Analysis (inc Mode).wid

13.     USA1 x1 – Usage – Session Analysis.wid

          USA1 x15 u – Usage – Session Analysis (With Users, Without Mode).wid

          USA1 x2 mI – Usage – Session Analysis (Mode is Interactive Only).wid

          USA1 x2 mS – Usage – Session Analysis (Mode is Scheduled Only).wid

          USA1 x30 umI – Usage – Session Analysis (With Users) (Mode is Interactive Only).wid

          USA1 x30 umS – Usage – Session Analysis (With Users) (Mode is Scheduled Only).wid

          USA1 x4 m – Usage – Session Analysis (With Mode).wid

14.     USA2 – Usage – Large number of Data Providers.wid

15.     USA3 – Usage – Documents no longer used in the system.wid

16.     USA4 – Usage – Universe Objects usage. Identify infrequent used objects.wid (see defect list below)

17.     USA5 – Usage – Universes no longer used.wid

 

Each document has an ‘About’ page that provides a few more details on its purpose.

The Universe is, of course, documented within itself. Every description box has a description! However I’ve not yet written supporting documentation for either the universe or the Web Intelligence documents. Feedback from you on what I should explain would be great!

 

Requirements:

  • BI Platform BI 4.1Support Pack 5 or greater for the Web Intelligence Documents.
  • BI Platform BI 4.1.x (or greater) for the Audit database to be queried

This means BI 4.2 meets these requirements.

 

Instructions

  1. Download the content (take the highest build numbered zip file)
  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!

 

Webinar

 

 

Please post your feedback here and I will do my best to comment back as soon as possible.

 

Matthew Shaw

Twitter: @MattShaw_on_BI

Current defect in my Audit Universe and Web Intelligence documents:

Issue Status
SYS1 – the Duration and ‘Delay in recording time’ show incorrect figures I’m working on it.
Performance of using objects built on the Derived tables in the universe isn’t great I’m working on it.

Current defects in capturing of the Audit events (not a defect in the Universe or Web Intelligence documents, though it will impact them)

Issue My reference Fix?
Action in sequence is a bit mad! The ‘Action in sequence’ should be 1, then 2, then 3. etc. Currently its a little random! Impact is you can’t quite determine the order of events, other than by time. Possibly can’t determine some event that might require a known order of events. 1580137557 We’re working on it.
List of Universe Objects not recorded when refreshing a Web Intelligence document based off a Universe. Impact is you the “USA4” document can’t return anything. Can’t say “given an object, which documents has it been used in before and by whom”., BI 4.2 SP2 now stores the universe objects names, however only the name of the object is captured and not the full path to it. This means if you have 2 objects of the same name (not a good idea anyway), then you can’t tell which one of the objects was used. Additionally the objects (or filter objects too) used as filters to any prompts are also not captured, though the prompt and prompt values are stored. Crystal Reports for Enterprise stores the full path of all objects used.

1580133418, 1670185455,

1670185456,

1670185458

Partly fixed in BI 4.2 SP2. Still working on it.
On a Web Intelligence document ‘refresh’ event, the Bunch ID doesn’t change, meaning (for documents with multiple universes or different type of data providers) you can’t tell which Universe ID belongs to which Universe name, and you can’t see which Excel file name belongs to which data provider.. Impact means Cartesian result when querying any of: UniverseName and UniverseID. This means any queries based on these objects (typically used as a condition) will return some invalid results. It also means you can’t identify if a document/event has a mixture of different types of data providers, for example 1 Universe, 1 Freehand SQL. 1570835454 Targeting BI 4.1 SP8
When you login and logout of the BILaunchPad multiple logon/logout events are captured, even though you can only login/logout once. Impact, means you can’t just view the number of events and compare login and logout event types, as there could be more logouts than logins! 1570835975 Fixed in BI 4.2 SP4

Some rows in the Audit database cause run-time errors.

For SQL Server the error is “Database error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Error converting data type varchar to bigint.. (IES 10901) (ErrorL INF )”.

For DB2 the error is “Invalid character found in a character string argument of the function “BIGINT”. SQLSTATE = 22018″

The root problem isn’t with the Universe, per say, but a product defect. When the detail value of ‘0’ is stored for the detail types ‘Number of Rows’ and ‘Size’ the product stores an ASCII value of 0, rather than a string of ‘0’. I’ve worked around this problem in the universe but I will arrange for this to be corrected in the product.

1670001433

Build 8.5

(In BI 4.2 SP3 the audit data will no longer store a ASCII character of value 0, but a string of “0”)

When refreshing a Web Intelligence document based on Freehand SQL, the detail type ‘Universe ID’ stores a single character of ASCII value 0. The ‘Universe Name’ and ‘Universe Object Name’ both store the name of the connection used (without the full path to the connection name). Additionally the SQL for the document is not stored. Impact: makes querying the audit database a little more complex. I will adapt the universe to cater for this. 1670001434 We’re working on it
When using Web Intelligence Rich Client and browsing the Repository, for every Web Intelligence document listed, a ‘Retrieval event’ is incorrectly captured. Impact- makes it appear more documents where retrieved than really where. 1670004359 Targeting BI 4.2 SP4
Design Studio audit events against a universe: Universe ID is captured as ‘Cube Name’. The Universe name, SQl, parameters, values, universe objects used, refresh time are not stored. Impact: limited analysis on these kind of objects. 1670004513 We’re working on it
Web Intelligence based off an XLS: Audit events ‘refresh’ will store the XLS CUID under Universe ID. ‘Refresh’, ‘save’, and ‘create’ will store the XLS filename (not full path) under ‘Universe Name’. Impact means you can’t tell if the document is based off a Universe or an XLS file. 1670004687 We’re working on it
Login as a ‘Concurrent User’ with Universe Design Tool will increase number of concurrent users by 2, not 1 (as seen in the CMC-Session) and also in the Audit database (as the detail type ‘Concurrent User Count’). Additionally the audit database will record multiple logon (~5) and logout events (~6) with multiple session IDs (~5) when you would expect to only see 1 of each. 1670009472 We’re working on it
In Universe Design Tool, when you retrieve a universe the audit database captures ~8 retrieval events, when you expect to see just 1. The detail for each of these events are identical to each other. 1670009473 We’re working on it
Application Type is missing the lookup table (ADS_APPLICATION_TYPE_STR). The missing values for Application Type ID are:AbI5_LpMFxFLhxl7DV91KYY, AeHuywedXKRPl6V1sOVZNtA, AUcgkU0CbV1Co0ujVEw_gSA, AXUaxM4wcwtLqqcn7.eB5bQ). Impact: For some events (most Webi, but also Crystal and Universe) the Application Type will be ‘null’. This might make event level analysis tricky! 1670026096 Fixed in BI 4.2 SP4 and BI 4.1 SP10. See KBA 2391022.
When Web Intelligence Rich Client saves a document, or exports to PDF, locally the ‘Size’ written is not numeric and will cause your database to throw an error. It means the ‘Size’ object in my universe will not work. Only affects certain product versions (see KBA 2193391 and 2265803 for these versions).  I plan to work around this problem in the next version of my Auditing Solution. To prevent the product from writing future invalid entries apply BI 4.1 SP7 patch 10 or greater (this patch will be merged into other SP lines + BI 4.2 lines) OSS 344143 Apply a product patch to prevent future invalid entries. Wait for my next UNX build to workaround existing invalid entries

Other changes planned.

If you have enabled ‘User Group Details’ (within CMC-Auditing) then the list of all the groups, the user is a member of, are captured for *every* *single* event. For example, if a user is in 150 groups, then all 150 groups are stored for each and every event associated with that user (Prompt, View page, refresh etc.). Given: the User Group assignment is unlikely to change within any session; querying any 2 lists of groups across events is so tricky and there’s an easy way to list when user-group assignment changes we plan to change this. n/a

Planned from BI 4.2 Support Pack 5, the list of groups, for a user, will be captured just once, at user logon and not for every event. This will *dramatically* reduce the data volume with no loss of functionality. To see if the user-group assignment has changed, just run a simple query: AppType=CMC; ObjectType= User; EventType=Modify. And look at the Property Name/Value for groups added or removed.

Also, use my universe and the predefined filter “Only sessions where the user is a member of a particular group ?” to do as it says!

 

More info on my blog

 

To report this post you need to login first.

361 Comments

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

  1. Paul Shovlar

     

    Hi Matthew

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

    Scenario
    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?

    (0) 
    1. Matthew Shaw Post author

      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

      (0) 
      1. Paul Shovlar

         

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

        Thanks

        Paul

        (1) 
  2. J. Yousaf

    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)

    https://answers.sap.com/questions/307625/bo-auditing-inconsistency-in-view-count-for-webi-r.html

    Wondering if you have any insight into this.

    Thanks.

    (0) 
    1. Matthew Shaw Post author

      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

      (1) 
      1. Manikandan Elumalai

        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.

         

        Thanks

        Mani

        (0) 
        1. Matthew Shaw Post author

          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

          (0) 
  3. Ravikumar Periasamy

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

     

    Thanks,

    Ravi

     

     

    (0) 
    1. Matthew Shaw Post author

      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

      (0) 
  4. Sriram Boddu

    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?

     

    Thanks,

    Sri

    (0) 
    1. Matthew Shaw Post author

      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

      (0) 
  5. Arun Krishnan

    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.

     

    Regards,

    Arun Krishnan G

    (0) 
  6. Aditya Kurra

    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;

     

    Aditya

    (0) 
    1. Atul Chowdhury

      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 –

       

      Atul

      (0) 
      1. Aditya Kurra

        Yes, its part of refresh.. but the idea of getting Session ID’s from schedule & use them over the refresh is a great idea. Thank you Atul.!

        (0) 

Leave a Reply