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!

To report this post you need to login first.

328 Comments

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

  1. Deborah Tolley

    Hi Matthew:

    Working on creating a log of failed jobs and can across something I’m hoping you can help me understand.

    For Event Type ID = 1011 (Run), the possible statuses are (from page 698 of the Platform Admin Guide v 4.2 2015-11-12):

    0 – Successful

    1 – Failed

    2 – Failed but will be reattempted

    3 – Cancelled

    In the universe, the Event > Status Code object description says:

    0 – Successful

    1 – Failed

    2 and 3 – Successful with warnings

    And the Event > Status object select statement is

    CASE Event.Status_ID WHEN 1 then ‘Failed’

    ELSE ‘Successful’

    END

    I’m wondering why you are classifying Failed but will be reattempted and Cancelled as ‘Successful’?  When I pulled my failed jobs using the built in Only Status Failed filter, it didn’t pick-up several reports that failed with status 2 so we missed that there was an issue with these instances.

    To complicate things further, Event Type 1008 (Save), statuses 2 and 3 are successful statuses.

    Maybe these universe field definitions need to be a bit more complex to handle the inconsistent use of the same status numbers?

    Deb

    (0) 
    1. Matthew Shaw Post author

      Deborah,

      Marvellous feedback, thank you so much. Very well documented too. I will indeed enhance this object. I see other event types (Logon, Save) also generate Status Codes: 2 and 3 respectively.

      Regards

      Matthew

       

       

      (0) 
  2. Mohamed SOLAANI

    Hi Matt,

    Thank you for sharing your reports with us.

    Every think workss well in the univers but only the “Object Folder Path”. I can not get that information it is always empty !!

    Can anyone help please.

    I am on SAP BI 4.2 SP2, SQL Sybase 16.

    Thks

    (0) 
    1. Dewesh Chandra

      Hi ,

      We are facing issue with the connection .Unable to find what should be the datasource  name while creating audit db connection.

      I am on SAP BI 4.2 SP2, SQL Sybase 16 on Linux.

      Any help will be appreciated.

       

      Regards,

      Dewesh

      (0) 
  3. Rene Gielen

    Great improvement on previous auditing Universe and reports! Looking forward to working with this at a current project!

    Nicely complemented by the YouTube video too!

    Rene

    (0) 
  4. Martin Dingeldey

    Hi Matt,

    i am using the auditing in 4.2 SP02 P1 and 4.1 SP07 P4 with oracle as DB. I think there is a bug since some patch. Since the notes 2193391(https://launchpad.support.sap.com/#/notes/2193391) and 2265803(https://launchpad.support.sap.com/#/notes/2265803) it is possible to audit saving of documents in richclient(Event_ID 1008) but it writes a wrong size in Event_Detail_Value for the Event_Detail_ID 17. It looks like a hexadecimal string (e.g. 14E08F0C). This causes some of your reports to fail with the error unknown number. All reports that include a size calcution like SYS3 X4.

    Is this to be fixed in the universe or should this be fixed in a patch?

    Regards,

    Martin

    (0) 
    1. Matthew Shaw Post author

      Hello Martin

       

      Indeed, there is a defect in the platform that a ‘0’ is not being stored correctly in the audit database. This is applicable for anything, including ‘Size’ (id 17) but also others like ‘Number of Rows’.

       

      Technically it turns out that a ‘null’ was being sent, instead of a zero and what we see as a funny character (it has an ASCII value of 0) is a terminating character and is actually at the end of every string we store in the audit db. However it causes us a problem when you want to ‘sum’ it. So we fixed this in BI 4.2 Support Pack 3. I’ve just validated it, and its now working as you and I would expect it to. I see a proper ‘0’ as the detail.

       

      As a workaround I’ve addressed it in many of the Universes I’ve provided, but not for all RDBMS’. Which RDBMS are you using please for your Audit Db? The logic to fix it yourself is to compare character 1 (of the detail) to an ASCII value of 0, if it is 0, then you want a string of ‘0’ otherwise accept the value in the field. You can then ‘sum’ that value once you’ve converted it to a number.

       

      Whilst we have fixed it in BI 4.2 SP3, even if you update to this version, your database will still have the old data with this issue, so it needs to be addressed in the universe too.

       

      Regards, Matthew

      (0) 
      1. Martin Dingeldey

        Hi Matt,

        i’m using Oracle as Audit DB and Version 8.5 of the Audting universe.

        I don’t understand completely what you mean by comparing the first character to the ASCII value because the value differs.

        Here’s some extract from the size field for Event 1008 ID 17:

        ‘111E0D44’ first character 1

        ‘36302D44’ first character 3

        ‘46500B1C’ first character 4

        ….

        and the ASCII Code of 0 is 48, right?

         

        Could you please describe it once more with an example?

         

        Regards,

        Martin

        (0) 
        1. Matthew Shaw Post author

          Hi Martin

          I see. When you mentioned the ‘Size’ detail and corruption I assumed this was the same problem I’ve seen before with regard to this ASCII 0 character. But for you this isn’t the case, I can tell for sure because the length of your detail for ‘Size’ is more than 1 character long.

          So, there must be something not right. Now we need to fix this, and the best way would be for you (and others) to log a incident with our Product Support so they can reproduce the issue.

          However, if you have data in you db which is ‘bad’ then we’ll also need to workaround this too, unless you’re happy to delete the bad data.

           

          For the ASCII 0 character issue I used this SQL for Size for Oracle:

           

          (sum(to_number(decode(ascii(dbms_lob.substr(“DetailSize”.EVENT_DETAIL_VALUE,1)),0,’0′,dbms_lob.substr(“DetailSize”.EVENT_DETAIL_VALUE,dbms_lob.getlength(“DetailSize”.EVENT_DETAIL_VALUE)-1))))/1024)

           

          You could perhaps use a similar technique for your issue?

           

          This issue isn’t fixed that I know of in any version.

           

          Let me know how you get on. Please direct message me the incident number (so I can keep an eye on it too).

           

          Regards, Matthew

           

          (0) 
  5. Farees Mohammed

    Hi,

    I’m facing a strange issue with date objects using Audit universe. I have a report that tracks all scheduled activities, which runs/ is scheduled every 15 min. I’m in CST time zone and all date objects are converted to CST timestamp from GMT with daylight savings.

    Problem is “Trunc(sysdate)” is displaying two hours behind timestamp. Due to which any activity between 12;00 AM to 1:59 AM is bringing previous day records. Strange thing is I’m not even filtering on date time. I’m filtering only on current day. Still it pulls the records of previous day till 1:59 AM and report runs fine after 2:00 AM. I tried various syntax like trunc(sysdate)+2/24 so on, nothing worked.

    I appreciate any help, below is filter syntax.

     

    To_date(to_char(from_tz(cast((ADS_EVENT.START_TIME AS TIMESTAMP), ‘GMT’) AT TIME ZONE ‘US/Central’, ‘MM/DD/YY’), ‘MM/DD/YY’) = Trunc(sysdate)

     

    Thanks & Regards

    Farees Mohammed

    (0) 
  6. Gino Terrigno

    Matthew, congratulations on producing what is the most mature SAP solution in the Auditing space to date for BusinessObjects.

    I have a question specific to the capture of events/actions related to Analysis for OLAP.  What I am after is the ability to measure how long an end-to-end refresh takes of an AOLAP workspace.  I’m already aware that there is a benchmarking t-code:  ST03/ST03N which allows query run-times to be determined.  I’m wondering if the Auditing universe can also capture the same information, plus and overhead required to render the AOLAP workspace.  Despite turning on the auditing database log level to COMPLETE, I can’t seem to capture any relevant information regarding AOLAP workspace refreshes.

    Please let me know if I’m missing something.

    Regards,
    Gino Terrigno

    (0) 
  7. Sateesh Kumar

    Hi Matthew ,

    Is it possible to have audit for BICS connection based reports with below format

    Report ,Datasource(BICS Name),variables filled,no of records fetched ,No of refreshes,User

    We couldn’t find way to track BICS based events !

    Thank you
    Sateesh

    (0) 
  8. Todd Abercrombie

    Hi Matthew,
    This is certainly a great tool for Admins!
    I have a few questions and for the time would prefer to message you directly.  How can I do so?

    Regards,

    Todd

    (0) 
  9. Hemalatha Komaragiri

    Hi Matthew,

    Thank you for the Audit universe, very helpful.

    We have successfully imported your audit universe, set the audit level as default and able to generate the reports.

    We have a requirement to capture user (client) and server hostname & IP addess in the report to track all users who are logged into the system from different countries. We tried to use [Host Name Reported By Client] & [Host Name Reported By Server]) from specific event type section but it doesn’t give the user machine name it come blank not sure how to pull this info. can you please help as its urgent request?

    will appreciate your fast response. Thank you.

    Kind Regards
    Hema

    (0) 
  10. George Cook

    Thanks for putting this together.  One problem I’m having is opening the “STA1- Start here” report.  I can open the report via Rich Client and refresh it.  However, I can’t seem to open the report through Launchpad. I’m using BOE 4.1 SP6.

    Any ideas would be appreciated.
    Thanks.

    (0) 
    1. Romain Anselin

      Try and create the most basic report on the audit UNX on BI Launchpad. ie: pull in the dimension “Year” and refresh. Same problem? Do the other reports exhibit the same behaviour? Possibly the connectivity to the audit DB isn’t correctly configured on the server (server need 64 bits client, while WIRC uses the 32 bits client). Error is too generic at this stage to say what the problem is. OS info for BI server and audit DB type would be useful.

      (0) 
  11. Matthew Shaw Post author

    I’ve just uploaded a new LCMBIAR file with 1 new Web Intelligence document in it:

    LIC5 – License – Periods when named users exceeded 10

    It will show the periods when the number of users, that are of the type ‘named users’ (and not ‘concurrent user’), exceed a value of 10 (or another value you provide).

    Hope you like it! Matthew (@MattShaw_on_BI)

     

    (0) 
  12. Prakash Gowda

    Hi Matthew,

    We have earlier version of audit report (Sybase ASE version) and Our BO environment database is Sybase 15.7.135. We are exploring option to install new version of audit report but did not see LCMBIAR file for Sybase, is it advisable to use SQL Anywhere version of file. Please advice.

    Thanks,
    Prakash

    (1) 
  13. Emre Arikan

    Thanks Matthew,

    very useful sharing. We do believe in open source 🙂

    We have Oracle. Two humble comments:

    1.If we want to create a historical report with object folder path and object as  dimensions and if that object is moved in the past, we may only want the final destination of the object in our report. For that I introduced a new object as “UptodateFolder”. For that I created a new derived table “UptodateFolder Of An Object” with the following code and right joined with Event table over the OBJECT_ID  :

    select object_id,uptodateFolder from (
    select start_time,object_id,max(object_folder_path) KEEP (DENSE_RANK LAST ORDER BY start_time )
    OVER (PARTITION BY object_id) uptodateFolder
    from ADS_EVENT
    where length(object_folder_path)> 2
    )x
    group by object_id,uptodateFolder

    2. In order to get universeid and name in the same derived table, the details table was joined with itself. But that provoked performance problems in my case, so I just dropped the universeid(I did not know why I will need that information) from the derived table. The high-performance derived table in its last form:
    SELECT
    DetailUniverseName.Event_ID,
    DetailUniverseName.Bunch,
    dbms_lob.substr(DetailUniverseName.Event_Detail_Value,dbms_lob.getlength(DetailUniverseName.Event_Detail_Value)-1) as UniverseName
    FROM ADS_EVENT_DETAIL DetailUniverseName
    where DetailUniverseName.Event_Detail_Type_ID=14
    GROUP BY
    DetailUniverseName.Event_ID,
    DetailUniverseName.Bunch,
    dbms_lob.substr( DetailUniverseName.Event_Detail_Value,dbms_lob.getlength( DetailUniverseName.Event_Detail_Value)-1)

    (0) 
  14. Christoph Brill

    Hi All,
    I would like to monitor the distribution of WebI and CrystalReports standard reports by schedules and publications. Especially by status (successfull, failed), by recipients, by duration, by rows and by size.
    What are the required parameters considering Application Type (JobServer?), Event Type (Deliver, Run, Refresh ?), Server (BO.APS.Core), Object Type ?
    Anybody who has build something like this?
    Thanks in advance!
    Best regards
    Christoph

    (0) 
    1. Emre Arikan

      Hi Christoph,
      I would create a report with all those parameters as dimensions and filter out the report with objects I am interested in. For filtering the cuid’s of reports or object folder paths may be used.
      Regards,
      Emre.
       

      (0) 
  15. Sherri Graham

    Hi Matt

    Great Work!  Really appreciate your efforts!

    We are finally working with our Auditing information and are a little perplexed…there is no data found for the Crystal Reports Processing servers…We see them processing work and there are values reflected in the CMC Metrics screens.  We have/see Auditing data for other servers including… CR Cache, RAS, Job Servers, APS, CMS, WI Processing…etc…

    We are using BI 4.1 PS 6, Patch 1 on SQL 2008 R2. Auditing configuration is set to collect all Common Events except Retrieve, Search and Trigger; all 3 Platform Events for Auditing, CAL’s and Rights.  The Event Details are set for all except Query.

    Any ideas/suggestions?

    Thanks

    Sherri

    (0) 
  16. Elson Simoes

    Hi all ,

    anyone is having troubles with the ‘size’ when event detail type = 17 ??

    I already tryed to use the solution already mentioned in a few coments converting the ascii value to 0 but even like that when I try to do a sum it converts to letters .

    If anyone had the same problem or if there’s any updated audit universe please let me know 🙂
    I would appreciate your help 🙂

    Thanks

    (0) 
  17. Guillaume HUCHET

    Hi all

    Is there a way to catch all Webi with freehand SQL? A property or something like that.

    If not, Matthew: is it possible to add this feature?

    Thank you Matthew for this awesome work!

    Guillaume

    (0) 
  18. nutty buddy

    Hi Matthew

    I need to do some admin related work on some of the most costly Webi reports.

    How do I identify those reports which try to query huge volumes of data in the warehouse?

     

    thanks

    nutty

    (0) 
  19. Łukasz Kłoczko

    What I have to do to resolve following error:

    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    Because I can’t run any raport

    (0) 
  20. Vytautas Thienel

    Hi,

    First of all, thank you for the awesome work with BI Audit solution. The audit universe and reports are working fine when I have tried to install it on SAP BO that runs on Windows Server.

    However, I am getting an error when I try to refresh the STA1 WebI report after the BI Audit installation on SAP BO 4.2 SP 2 that runs on Linux.

    The following database error occurred: (CS) “DBDriver failed to load : /home/oracle/bobi/sap_bobj/enterprise_xi40/dataAccess/connectionServer/drivers/lib64/libdbd_dbodbc12.so (libdbodbc12_r.so: cannot open shared object file: No such file or directory)”

    I believe PATH or Environmental Variables are missing. Any ideas how to fix it? It is a PROD environment and I would not like to experiment on it without knowing what is the exact cause of this error.

    odbc.ini file content:

    [ODBC Data Sources]
    BI4_CMS_DSN_146=SQLAnywhere 16.0
    BI4_Audit_DSN_146=SQLAnywhere 16.0

    [BI4_CMS_DSN_146]
    UID=dba
    DatabaseName=BI4_CMS
    ServerName=BI4_146
    Host=localhost:2638
    Driver=/home/oracle/bobi/sqlanywhere/lib64/libdbodbc16.so

    [BI4_Audit_DSN_146]
    UID=dba
    DatabaseName=BI4_Audit
    ServerName=BI4_146
    Host=localhost:2638
    Driver=/home/oracle/bobi/sqlanywhere/lib64/libdbodbc16.so

     

    Technology:

    SAP BO 4.2 SP 2

    Platform OS: Linux

    Audit DB: SQL Anywhere

     

    Thank you very much in advance.

     

    Br

    VT

     

    (0) 
    1. Vytautas Thienel

      The problem was with the connection which was pointing to 32bit SQL Anywhere 12, DNS BI4_Audit. It works fine on my local PC, but it fails when I sent the universe back to the repository. Resolution: Create 64bit SQL Anywhere 16 connection that points to the server DNS, in this case BI4_Audit_DSN_146.

       

      (0) 
      1. Sveinung Ryland

        Hi Vytautas,

        I have the same issue, can you specify what you mean by “Create 64bit SQL Anywhere 16 connection that points to the server DNS”. Where and how did you create that?

        Kind regards

        Sveinung

        (0) 
        1. Vytautas Thienel

          Check the ADS Database Connection Name in CMC > Auditing.

          Go to Information Design tool under BI client. To create a new Relational connection, first start with a new project under Local Project view. Click New → Project → Enter the Project Name → Finish.

          Right-click on Project name → New → Relational Connection.

          Create the connection using the correct driver (in my case SQL Anywhere 16) and the “Database Connection Name” you have seen in CMC.

          Publish the connection to the repository if it works fine. Later you can change the connection of the BIaudit universe to point to the connection you hav ecreated

          Be aware that CMC/CMS uses 64bit DSN/DB driver to access Audit DB. Designer or IDT need 32bit DSN/DB Driver to access data. So the connection retrieved to the repository must be 64bit. Otherwise you will get an error when running the report in WebI.

          In addition, I have downloaded both ODBC 32 and 64 bit drivers to my PC and configured them so that it will work fine when retrieving the Audit universe and editing it in IDT.

           

           

          (1) 
  21. Santhosh Katakam

    Hi,

     

    Appreciate for your hard work and Thanks for sharing with us.

     

    I would like to find who schedule a report on particular day including the IP address, can I get this info using the universe?

     

    Regards

    Santhosh

    (0) 
  22. Paul Benson

    Hi Matt,

    Long time no speak…

    Great blog! Thanks very much. I’m glad somebody’s finally on top of this subject.

    I’ve created a couple of dashboards for myself to keep tabs on report usage here at Newton, and it’s just dawned on me I’ve been missing some of the Live Office activity that’s starting to become popular. So now that I’ve added LO content to my report I’m struggling to find the name of the corresponding Universe used. I can see row counts, prompt answers, etc, but no Universe identifier in the ADS_EVENT_DETAIL table.

    Do you have any tips on how I can get to the bottom of this?

    Thanks in advance. Glad you’re still alive and well!

     

    Paul Benson

     

    (0) 
    1. Paul Benson

      I’ve made a bit of progress with this, so have half answered my question. I can see the underlying document name and CUID as ‘Element Name’ and ‘Element ID’ – and I’ve been able to map these back to the universe. All good.

      But what about LO document that are purely query based? Is the element simply the universe itself? Unfortunately I don’t have a non-report based LO document to check…

      (0) 
    2. Muhammad Patel

      Hello Pau,

       

      Would it possible for you to share those Dashboards? i am trying to created a dashboard for report and user usage at my firm.

       

      Any help will be appreciated.

      (0) 
  23. Sarin Golla

    Hi Matthew,

     

    We are in BO 4.2 SP3 and DB is DB2 9.7. We have recently upgraded from BO 4.1 SP4 to BO 4.2 SP3.

    We have audit enabled in the system. Could you please provide us lcmbiar for DB2 specific database and help us to obtain audit reports from the system.

    Thanks,

    Sarin.G

    (0) 
  24. Arpan Gupta

    Hi Matthew,

    I am using the universe provided by you for designing a webi report having UserID and User Group detail for each User.

    1. The number of Users, output by universe is far less than the users shown by query builder, Why ? Could you please throw some light on this.
    2. If I want to have a extra column, User last logon time in the above report, Is there any possibility to do it by using your universe.

    Regards,

    Arpan

    (0) 
  25. Julian Wechsler

    Hi Matt,

    I just wanted to say thanks. This is really great!

    A small bug report of sorts –

    I was getting the conversion to bigint error that others were getting. Since that error had been fixed, I did a bit of digging, and found an item in the ADS_EVENT_DETAIL table that had a value of “[Ljava.lang.Long;@1fa7a444” in the Event_Detail_Value column.

    I am not sure what caused that, or if it was some weird fluke behavior, but I “fixed” it by just ignoring that value, and hoping it doesn’t happen again. (If it does, I’ll probably change it to something more specific.)

    (SQL Server 2014, 4.2 SP3)

    Thanks,
    Julian Wechsler

    (0) 
    1. Emre Arikan

      Hi Julian,

      It might be the known issue with “ADS_EVENT_DETAIL=17”  which gives the “size” (of the object). Work around is, you just do not try to read that detail, acordingly do not use the “Document Size” measures of Business Layer  in Audit..

      Cheers,

      Emre.

      (0) 
  26. Erica Rape

    We use multiple tenants in our platform and would like to get – Maximum number of users accessing the Business Objects portal at the same time during the reporting period by tenant. We tried to use the measures – Sessions (Concurrent User) however, it only pulls in the max concurrent across tenants and not by an individual tenant. If you have a tenant that you logon 2 concurrent users to and then logon a 3rd for a different tenant the measure – Sessions (Concurrent User) increments the value for both tenants to 3. We did see the measure description. “There are two types of users: ‘Concurrent’ and ‘Named User’. Time events, like login/logout will store this number. The number is the maximum number of ‘Concurrent User’ session at the time of that event, and so it doesn’t make sense to use this measure with ‘username’ or other dimensions other than time.” So, our question would be where can we get the Maximum number of users accessing the Business Objects portal at the same time during the reporting period by tenant in the current Audit universe 8.5. If this is not possible in the existing universe/audit db, is there a different way for us to get this measure?

     

    (0) 
    1. Matthew Shaw Post author

      Thank you for posting here.

      I understand your requirement. You’re right there’s no easy way to get this.

      Its actually very tricky to get what you want from the auditing as it is today.

      I can’t think of an easy solution.

      I’ve captured this requirement for a future evaluation into Auditing improvements.

      Thank you, Matthew

       

      (0) 
  27. Sanjit Jha

    Hi Matthew / All

    One of our clients is upgrading from BO 4.1 to BO 4.2. They would also like to migrate the audit database of the old BO 4.1 system during the upgrade. Is it possible to migrate the BO 4.1 audit database to BO 4.2. Creating a LCMBAIR file will not take the audit database i guess. Really appreciate if someone can help me on this as i can’t find any specific document in this regard.

    Regards

    Sanjit

    (0) 
  28. Suvarna Ray

    Dear Matthew,

    We recently started using the new Audit universe. But is there any way to track which BO reports are scheduled and how many times and if any scheduling has been failed?

    When I use Event Type I can only see values Deleted/Modified/Created/Retrieved. No event type to show ‘scheduled’. I tested scheduling one Analysis for Office workbook and one webi report. But still I am not able to see the schedule entry when I fetch the data from the Audit universe. Kindly suggest which universe object/field I should use.

    Br,

    Suvarna

    (0) 
    1. Matthew Shaw Post author

      Please use my document “SYS3 x4” as a starting point for your query. The doc will have a union of multiple queries so if you edit one query, you’ll need to edit the other ‘union’ query. Regards, Matthew

      (0) 
  29. Priyadarshini Sekar

    Hi Mathew,

    I am trying to decode the Audit Logs for the Event Type = Modify. We need to build a report based for events when a principal is added to an object and what level of access is assigned to the object. The audit log information is encrypted for the field Property Value from the ADS_EVENT_DETAILS table. We are able to identify the access levels using the IDs but not the details of the principals they are assigned to. For example,

    Event Detail Type = Property Value

    Event Detail Value= 3&U=2,03&.1=563,03&.2=586,03

    1 = Everyone Group

    563 = ID for View access level

    We aren’t able to figure out what 03 stands for. Also the details of the Principal to which these are assigned to. Can you provide some insights or let us know if that is possible from the ADS?

    Thanks,

    Priya

     

    (0) 
    1. Matthew Shaw Post author

      Hello Priya, sorry I don’t know. Perhaps 03 is granted?  I’ve captured the requirement for an enhancement to Auditing, so please be assured SAP knows you need this info! Regards, Matthew

       

      (0) 
  30. Andrew McFarlane

    Hello Everyone!

    Firstly, many thanks Matt for the excellent auditing materials; in themselves they are a first rate example of great universe and report design.

    Secondly, we have a problem I hope someone can help with! We’re running BusinessObjects 4.1 SP4, and after installing the universe and reports, we ran Doc STA1 as suggested. Before the report will even refresh we get an “invalid identifier” ORA 00904 error on the “Month” field.

    Has anyone seen this error and knows how to fix it? We know the materials are for SP5 but most reports do seem to work on SP4.

    Thanks 🙂

    (0) 
  31. Dewesh Chandra

    Hi Matthew,

    I am trying to implement Audit universe in our current deployment. We are using BI 4.2 SP3 on Linux.

    I have imported the universe, webi ,connections BIAR files .

    When we are deploying connection (SQL Anywhere),It’s throwing error as below

    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified .

     

    Please help.

     

    Regards,

    Dewesh

    (0) 
    1. Matthew Shaw Post author

      Hello Dewesh, You’ll need to edit the connection and change it to a valid connection for your system. This might involve changing the driver to the one for your system, not the one from my system. Regards, Matthew

       

      (0) 
      1. Dewesh Chandra

        Hi Matthew ,

         

        Thanks for your reply.

        I had created an 32 bit ODBC connection on  my local PC and changed the Audit.dfx connection to point to the new connection. Universe is successfully tested and exported to the Enterprise.

        Reports from Webi Rich Client works successfully,but when the same reports are scheduled in Enterprise. It’s failing with the below error.

        The following database error occurred: (CS) “DBDriver failed to load : /data/sap_bobj/enterprise_xi40/dataAccess/connectionServer/drivers/lib64/libdbd_dbodbc12.so (libdbodbc12_r.so: 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

        Where to create the 64 bit ODBC connection on server or on client machine?

        Thanks in advance.

         

        Regards,

        Dewesh

        (0) 
        1. Matthew Shaw Post author

          Hello Dewesh, I’m keen to keep the comments in this blog centred around the Auditing Universe and Auditing requirements. The problem you have is related to any universe connecting to any SQL Anywhere database from a Linux machine, so I don’t want to provide much help in the comments to this blog as this question is pretty generic. Sorry for passing on this. Regards, Matthew

           

          (0) 
  32. RAJAVELU KANDASAMY

    Thank you Matthew for all the auditing reports. We have been waiting for this.

     

    We are using DB2 for CMC and while running “USA1 x15 u – Usage – Session Analysis (With Users, Without Mode)”, it fails if we select more than one day of data.  Real benefit of this report is to to get metrics about user session for days/weeks/months. I tried even scheduling , still same error.

    The following database error occurred: A database error has occured. For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901)

    Can you help ?

    Thanks!

     

     

    (0) 
    1. Matthew Shaw Post author

      Hello Rajavelu, that error message isn’t enough to know what the problem is. Can I suggest you find what the database error message is. It might be related to either the data (perhaps something the universe doesn’t like in the data) or a run time problem relating to temporary working space for your database (like temp space). You’ll need to work it out for yourself and isolate what is causing the error.  You could, for example remove an object from the query and keep removing objects until it works. For that document (USA1) you’ll have multiple queries ‘union-ed’ together, so when you remove one object from one query, you’ll need to remove the same object from the other query to keep the union valid. Regards, Matthew

      (0) 
  33. Frau Saba Wali

    Hi all,

    we have crystal server 2016 (BI Platform 4.2) and use sql anywhere 16 for auditing.

    1) First what i did Download
    Build 8.5 – BI4.1 UNX and WIDs (requires BI4.1 sp5 or greater).zip
    Document LIC5 only (addition to Build 8.5) lcm.job.airgap.list6929387289849922142.zip
    SQLAnywhere Connection with DBA Password1 lcm.job.airgap.list3942094915599738321.zip

    2) Import one of the five ‘Universe’ LCMBIAR files into your system using Promotion Management (it will go into “BI Platform Auditing” folder): Build 8.5 SQL Anywhere
    I took „Universe and Connection lcm.job.airgap.list4451198692545709640.lcmbiar“
    3) Import the Web Intelligence LCMBIAR file (it will go into “BI Platform Auditing” folder)
    I took  „Build 8.5 Webi Docs lcm.job.airgap.list9102040672086520291.lcmbiar“
    the folder BI Platform Auditing”  is after import  empty,
    it should be Web Intelligence documents, right? as i said  we have crystal report server 2016
    so it will be work with Web Intelligence documents? many thanks for any help
    (0) 
    1. Matthew Shaw Post author

      Hello Frau, The solution uses Web Intelligence documents and a Universe (UNX type), so your Crystal Server won’t be able to make use of those. Regards, Matthew

      (0) 
  34. Frau Saba Wali

    Hi Matthew ,

    Many thanks for your reply,that’s what I also figured out.

    SAP Support send me this link as i asked for Sample Auditing Universe and Reports although they are know Crystal Server.

    I have another question and maybe you can help me.
    I had modified Auditing Universe connection use Information Design Tool the result is “unx Universe” but i  can’t use this kind of Universe with crystal report 2016.
    Have you any Idea how to do that?or any other way to report user activity?

    Regards, Saba

    (0) 
    1. Matthew Shaw Post author

      Hi, Crystal Reports doesn’t include the functionality to create, produce or consume Universes. You need the BI Platform for that. Regards, Matthew

      (0) 
  35. Frau Saba Wali

    Hi Matthew,yes i now

    i mean Universe as data source for Crystal Reports.

    We have from BusinessObjects Enterprise (old system) Universe and Crystal Reports.

    I update the Universe using Information Design Tool only problem i can’t use this kind of Universe with crystal report 2016 (need crystal report enterprise) Regards,Saba

    (0) 
  36. Cognizant Service User

    Hello Matthew Shaw,

    We downloaded reports based on webi and it was working fine. But now we are facing some issue and we are getting below error message while refreshing the report.

    Request your help in fixing this. Thank you.

    “The following database error occurred: [Microsoft][ODBC SQL Server Driver][SQL Server]’int’ is not a recognized built-in function name.. For information about this error, please refer to SAP Knowledge Base Article 2054721 on the SAP Support Portal. (IES 10901) (Error: INF )”

    BI version : 4.2 SP3 patch 5

    DB : SQL server 2008.

    Regards,

    Kamesh

     

    (0) 
    1. Matthew Shaw Post author

      Seem the database isn’t happy with some aspect of an object. You’ll need to work out which object (likely to be a measure object) is causing the issue. Then determine what rows the issue is occurring for. The issue may be related to ‘corruption’ in the audit db. Please check the table in my blog for a list of known issues. Regards, Matthew

      (0) 
      1. Cognizant Service User

        Hi Matthew,

        Database related to the issue is CMS database and the universes is “BI platform CMS system database”. Recently we did patch upgrade from 4.2 SP3 to 4.2 SP3 patch 5.

        • Report name : STA1 – Start here – Events over time.wid – Working fine (Universes : BI Platform Audit Analysis)
        • Report name: Sample-User – not working (Universes : BI platform CMS system database)

        Strange thing is report “Sample-User” is working in sandbox system but not in development and production.

        Regards,

        Kamesh

         

        (0) 
  37. Ely Turkenitz

    Hello all,

    Can someone please tell me if the user full name is now recorded as part of the auditing in BI 4.2 SP3 ? Thank you.

     

    (0) 
    1. Matthew Shaw Post author

      Ely, Nothing new in BI 4.2 sp3 for that I’m afraid. I have though captured this requirement for consideration for a later release. Regards, Matthew

       

      (0) 
  38. Andrew McFarlane

     

    Hi Everyone,
    I’ve built a report on Matthew’s universe that shows concurrent usage across months, days, weekdays, and hours.
    The “Sessions (Concurrent user)” object gives a value that is “max at the time of an event”. So if I create a graph with the Day object and the above object, will I see the max for each day in the chosen period?
    Moreover, if I create a different report, and this time specify the period of a month, and put Weekday and Hour along the X access, and concurrent sessions along the Y, what is shown? The weekday object shows days 1-7, so I assume that it presents the max value, across the whole month, for each hour of each weekday?
    Thanks
    (0) 
    1. Matthew Shaw Post author

      Hi Andrew

      shows concurrent usage across months, days, weekdays, and hours. The “Sessions (Concurrent user)” object gives a value that is “max at the time of an event”. So if I create a graph with the Day object and the above object, will I see the max for each day in the chosen period?
      It will show the maximum number of concurrent sessions for those users that are of the type ‘concurrent’. It will show this number aggregated by the dimensions you have in your query. You have Day in your query, so it will be the maximum across each Day.
      I create a different report, and this time specify the period of a month, and put Weekday and Hour along the X access, and concurrent sessions along the Y, what is shown?
      As above, it will show this number aggregated by the dimensions you have in your query. You have WeekDay and Hour in your query, so it will be the maximum across each WeekDay Hours. You omitted ‘Day’, and so you’ll have the maximum sessions across all WeekDay Hours within the month.
      The weekday object shows days 1-7, so I assume that it presents the max value, across the whole month, for each hour of each weekday?
      Yes. Spot on.
      Regards, Matthew
      (1) 
  39. Carl Maib

    Hi Matt,

    First off, great work with the Audit universe. I have done some development with the old universe and did find it had some errors and was a bit confusing.

    Before doing some of the complicated analysis, I want to do some simple reporting on usage. I understand that for viewing a Webi document several events are generated. I am trying to simplify this to a count of Views per User. You have the appropriate measures already, but my counts still seem elevated.

    For example, when simplifying the dimensions as much as possible, I still get 2 Actions for the View action/event. I’ve narrowed this down to the Application type being the discriminating row. In one row, the Application Type is ‘Logon without Client ID’ and in the second row the Application Type is null. In your demo, I noticed that you have an Application Type of Web Intelligence. If I were to see that value, I would know to filter the events by Event Type = ‘View’, Object Type = ‘Web Intelligence’ and Application Type = ‘Web Intelligence’.

    So, two questions… what is the Application Type ‘Logon without Client ID’? And why do I not see Application Type = ‘Web Intelligence’?

    Ok, maybe one more question… does this seam like the right filtering for capturing simple usage?

    Thanks in advance for your help… I am also now following you on Twitter to keep in touch with other features as they become available…

     

    (0) 
    1. Matthew Shaw Post author

      Hi Carl

      There are 2 or 3 view events when you open a webi doc depending upon your workflow. Crystal also has about 2 events for some workflow too.

      For Web Intelligence we plan to fix this, I think for BI 4.2 SP5.

      Why no Application Type for you? I’m not sure. You’d need to check that the lookup table has a valid entry for your language. You might need to log a support incident if you think there’s a defect. There might be a missing lookup, for example.

      does this seam like the right filtering for capturing simple usage?

      Well, it seems like that, but the audit events needs a little tidy up to really make sense of them. I think BI 4.2 SP5 should make things better, at least for webi.

      All the best, Matthew

      (0) 

Leave a Reply