Saturday, December 27, 2008

Hyperion Brio Impact Manager

Today, let's understand and discuss about the Impact Manager feature available in Brio or Hyperion Interactive reporting.

Impact Manager is a utility which can be very useful in situations where you want to find out the dependencies of a particular column of a database table being refrenced in Brio data models and queries.

let's say for example there is a proposed change in table definations of some particular table and you want to identify the brio reports which are going to affect by this change. To perform this job manually by openning every single document and checking for those tables and columns used in the report is not a practical solution especially when you have thousands of reports published in Hyperion Workspace.

Not only that. After identifying those reports you may want to update all the reports automatically by the system only.

Another similar scenario similar to this is let's say you are going to add a new column in a particular table and you would like to see what possible reports may possibly require to use this new field. The IT may not want to individually ask all the users if they want to or intend to use this new column in their reports.

To start using the Impact Manager utility, you first need to enable the Harvesting option.
To enable harvesting option, log on to Hyperion Workspace using a User ID with Administrative privilages. Go to Navigate- Administer- General. This will display all the general properties.
Select 'Enable Harvesting' option and save. This option is not enabled by default.

The next step is to invoke Harvesting. Now what does this invoking will do?
When you invoke Harvesting, the system will read all the Brio documents published in Hyperion Workspace and then extract and store the metadata in the tables created in the database repository.
When you first invoke it, the system will create some tables (around 40) in the database repository with names starting from 'V8_H%' for the purpose of storing the metadata information.

Also, once invoked this process may take several hours(around 6-7) to complete for a system having thousands of published reports.

The metadata repository will have information related to Report sections, Tables and columns used in reports and Section dependencies.

To invoke Harvesting, From Navigate- Impact Manager-Synchronise Metadata select 'Run Now' option and click on 'Submit'. If you want to schedule it for some other time, you can choose 'Schedule' option.

You can check the status of this invoking process by selecting Navigate-Impact Manager- Show task status.

The system will tell you the status for every published document. Some of the status may show failed due to various reasons like not finding the suitable connection information. You may need to manually go and check those documents and try to correct it.

Now once this process is complete, you would like to see how this works.

There are three options-
1. Create an SQL query by combining the tables and selecting the appropriate columns.
2. Create a bqy document with a query similar to point number 1.
3. Using 'Impact of Change' report available with Impact Manager.
To use this report, select navigate- Impact Manager- Show Impact of Change.

Now, In the picture above there are some tabs aligned Horizontally. Select the first tab 'Home'.

Simply select a table or column in the filter and click on 'Set Filter' to give you the dependencies.
The tabs you see will provide information to queries, datamodels, tables and columns related to the filter you had set.
Here in this slide I have selected 'RDBMS Table Name'. You can choose from options available under the drop down menu like Document name or topic name.
Some of the tables which would be useful to prepare the query are:
1. V8_H_QRY_COLUMN- Holds info such as SQL definition which points to actual base table and column with corresponding query column name.
2. V8_H_QRYCOL_DMCOL- Stores cross reference data which can be used for linking document objects and database objects. It has column id for IR document and corresponding database column id .
3. V8_H_HPSU_COLUMN- Stores column information as defined in IR documents and linked to it’s Table ID.
4. V8_H_HPSU_TABLE- Stores table information as used in IR documents and has a link.
5. V8_H_DOCUMENT- Stores path, parent folder, document name, version, time_harvested and document.
Hope this would help.

Manohar Rana


ab said...

Hi we are group of industry experts running Training, Projects and maintainence company in

India that specializes in BI business intelligence / DWH work in Hyperion, Cognos, Business

Objects, Crystal etc. Anyone interested from UK, USA interested in discussing partnering, offshoring,

Projects, Training or support and maintainence work at low cost and high quality work by

experts may contact

bammidi said...

Hi amit,

i hope you are doing good. we need trainer who can teach HFM and FIR (9.3v) tools. plz let me know if anyone interesting to come and train us our team in mumbai asap..