Tuesday, July 5, 2011

Who moved my universe ?

One of the most unpleasant symptoms that can happen to a busy BI team are universe overrides by different designers.In general , universe overrides shouldn't happen and by determining simple working processes, keeping the team synchronized and updated and making sure that the universe life cycle is secured we should get a healthy environment . But what happens when someone exports the universe to production against the regulation?

As the team manger, finding out who exported the universe last can help you improve the change management and simply know who was the last person that updated the universe which can be a very important information. This information can be pulled out from the Activity universe and by crossing this information with the CMC, you can know for sure.

The following query is based on the Activity universe (Oracle based) and will pull out all the changes that were made in a particular universe in a specific date.  

Please note:   The changes are not necessarily describing export actions of the universe and therefore i recommend to Identify the last record of the export by looking in the CMC, under universes, you can find a column next to The name of the universe that shows the last update date, this date will also appear in the report and by crossing these records you will get the correct answer to "who exported the universe last?"  

Here is the query script:

 Table__37.DETAIL_TEXT,   AUDIT_EVENT.User_Name, AUDIT_EVENT.Start_Timestamp +( (3/24) )FROMAUDIT_EVENT,( SELECTAUDIT_DETAIL.Detail_Text,AUDIT_EVENT.Event_ID FROMAUDIT_DETAIL,AUDIT_EVENTWHEREAUDIT_DETAIL.Event_ID=AUDIT_EVENT.Event_ID AND AUDIT_EVENT.Event_Type_ID = 65544) Table__37WHERE( Table__37.EVENT_ID=AUDIT_EVENT.Event_ID )AND (( ( Table__37.DETAIL_TEXT ) LIKE '%' @Variable('enter teh universe name or part of the universe name ') '%' )AND Table__37.DETAIL_TEXT NOT LIKE '%Everyone%'AND Table__37.DETAIL_TEXT NOT LIKE '%/%'AND ( ( trunc(AUDIT_EVENT.Start_Timestamp) ) BETWEEN @Prompt('from action date','A','Action Time\Action Date',mono,free)AND @Prompt(' till action date','A','Action Time\Action Date',mono,free) ))

No comments:

Post a Comment