BusinessObjects Board

Universe Usage - Valid Event Types?

other Auditor
#1

I’m looking at the usage of our universes. We want to know which ones are used the most.

I originally created a report that only looked at the Refresh Event Type but some of the numbers look off. When I just look at Universes (Event Detail Type 14) and the Event Types that are associated with them I see many Event Types:[list]Create
Edit
Prompt
Refresh
View[/list]Some of these don’t make sense.

Create - I would only expect to see this the first time a universe was moved into the environment but I see some universes showing several hundred of these events.
Edit - some of our universes show a lot of edits but our universe design work is not done in this environment so I would expect to only see this event triggered if we were to promote a new version or need to make a connection modification.
Prompt - I would expect this to be report event type but I suppose it could be triggered if there were used conditions in the universe?
RefreshThis one makes the most sense but it seems to be low on the reporting side from a numbers perspective.
View - The only valid use of this that I could see would be if a user were creating a new Web Intelligence report but then again, why would it be an Event for a universe?

The report that I am using to look into these different events is a fairly simple one. It is against SQL Server and the generated SQL is

SELECT max(dbo.ADS_EVENT_DETAIL.Event_Detail_Value),
  max(dbo.ADS_EVENT_TYPE_STR.Event_Type_Name),
  Count(Distinct dbo.ADS_EVENT.Event_ID)
FROM dbo.ADS_EVENT_DETAIL
INNER JOIN dbo.ADS_EVENT ON (dbo.ADS_EVENT.Event_ID=dbo.ADS_EVENT_DETAIL.Event_ID)
INNER JOIN dbo.ADS_EVENT_TYPE ON (dbo.ADS_EVENT_TYPE.Event_Type_ID=dbo.ADS_EVENT.Event_Type_ID)
INNER JOIN dbo.ADS_EVENT_TYPE_STR ON (dbo.ADS_EVENT_TYPE_STR.Event_Type_ID=dbo.ADS_EVENT_TYPE.Event_Type_ID  AND  dbo.ADS_EVENT_TYPE_STR.Language='en') 
WHERE dbo.ADS_EVENT_DETAIL.Event_Detail_Type_ID  =  14
GROUP BY
  dbo.ADS_EVENT.Event_ID, 
  dbo.ADS_EVENT_TYPE.Event_Type_ID

Does anyone have any better information on when or why these events are generated for universes?

#2

John -

You’re conflating multiple audit concepts (your interpretation of your query with what you’re expecting to get).

The query that you wrote is basically plucking any arbitrary audit event that has in its complete trace, a UNIVERSE NAME detail.

So when you see “CREATED” events at 200+ for efashion, it’s not that efashion was created 200 times, but possibly that 200 reports/schedules/instances/whatever were created using that universe as a source (or some similar type of trace data).

Run my MSSQL Audit BI4 query below for a proper # of CREATE events for all universes in your system. It should paint a picture that much more closely describes “All universes created on my system” and you shouldn’t get any repeats (or at least minimal repeats, but ideally, none)

Note: This still doesn’t solve for your “Show me overall universe usage over time” use case which is a separate story.

SELECT
  ae.start_time,
  ae.object_name,
  obj.object_type_name,
  ae.object_id,
  ad.Event_Detail_Type_ID,
  ae.user_name,
  ae.server_id,
  ae.session_id,
  event_type_name,
  event_detail_type_name,
  CAST(event_detail_value AS VARCHAR(4000)) AS "detail"
FROM
  ads_event ae,
  ads_event_detail ad,
  ads_event_type_str aes,
  ads_event_detail_type_str ads,
  ADS_OBJECT_TYPE_STR obj
WHERE
  ae.event_id = ad.event_id
  AND aes.event_type_id = ae.event_type_id
  AND ad.event_detail_type_id = ads.event_detail_type_id
  AND ads.language = 'EN'
  AND aes.language = 'EN'
  AND obj.Object_Type_ID = ae.Object_Type_ID
  AND obj.language = 'EN'
  AND obj.Object_Type_ID IN ('AWg5LSrKtexDoFvUmRpZTHs', 'AejLs4l11SBKp2XHBDI.iAI')
  AND ae.Event_Type_ID = 1005
  AND ad.Event_Detail_Type_ID = 17

Hope this helps -

Atul

Atul Chowdhury (BOB member since 2003-07-07)