OBIEE Impact Analysis – What if I delete a column from RPD, what are the reports that are being affected? – Part 2

In my recent post, I have shown how to gather the RPD and Webcatalog data required for data lineage. And now we will build an OBIEE rpd with these OBIEE Web Catalog and Repository CSV files by creating ODBC connections or we can import the CSV files data into a database table using any import tools. For example, I am using SQL developer to import data from CSV files into a database table. I used SQL developer excellent feature to import data from CSV files.  This is very simple, just expand the database and right click on the tables. Then select Import Data.

Select the CSV file to import.

Define the CSV file properties, like format type = delimited and delimiter = tab.

Proceed to next wizards. This will create a table in specified Oracle DB and imports the data into it. Repeat the same steps for RPD_Data.csv files to load into other table.

Now import these tables into OBIEE RPD and build a repository with all 3 layers.

I have used the following business logic to join the Catalog and RPD data. (The table and column names are dependent on what you have customized)

Catalog. Subject Area = RPD. Subject Area

RPD.Presentation Table = Catalog. Table

Catalog.Presentation Column = RPD.Column

Deploy this RPD and configure an empty webcatalog (or you can use existed one) through Oracle Fusion Middleware EM and restart all the OBIEE services.

Now we are ready with the data lineage information.  Just login to OBI Presentation Services and create the necessary reports to know about reports statistics and their columns definition all the way from physical sources.

For example,

Question 1: What are the physical columns used by a report?

Ok well, I’ll create a report with the required columns to answer this question (See the report below).

From above report I can answer, what are the columns the report is made up with, what is the definition of a metric and what is the source of that particular column.  And the above report also tells me what happens if I delete a particular column from RPD like what are the reports that are being impacted?

Question 2:  How many reports have been developed and who?

Create a report and answer there are 39 total reports and ownership details are below.

Like this we can create the reports to answer some crazy questions about column lineage.

Hope this helps you all..

 

Thanks,
Shiva

 

OBIEE Impact Analysis – What if I delete a column from RPD, what are the reports that are being affected? – Part 1

In general every OBIEE Application user/developer needs to know the origin of the data displayed on reports. The general questions that everyone looks for answers to understand their application are:

• How the report columns are defined back in metadata?
• What are the OBIEE objects (Analysis, BIP Reports, Prompts and filters etc.) that are being referenced by a column(s)?
• How columns are mapped through metadata?
• Who are the owners of reports?
• And more …

And also if you want to modify/update your metadata definition, the developer/BI Analyst should be aware of the impact of that change in already developed content.

To answer all these questions, we need to build a column lineage, from dashboard to subject area, to physical column, and then to column in source system. We have so many tools to build this Data lineage, but within OBIEE itself, we have some great information that can be used to build this Data Lineage to answer many of questions but not all.

To build OBIEE Data Lineage, We need to extract the objects information from

1. OBIEE Web Catalog and
2. OBIEE Repository (RPD).

OBIEE Web Catalog Data:

The All Web Catalog objects information can be found using Catalog Manager Report. Generate a catalog report using Catalog manager and then export it to a tab delimited CSV file.

To export the OBIEE Web Catalog report to a CSV file:

• Open OBIEE Catalog Manager and connect to the catalog that contains the metadata that you want to include in the lineage. For example: E:\OBIEE\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite
• Select Tools > Create Report.
• In the Create Catalog Report dialog, select the following columns to include in the report: Owner, Folder, Name, Subject Area, Formula, Table, and Column. Of course, this depends on what kind of analysis you are trying to do.
• Click OK, to generate the report.
  
• Save the report in a temporary file, for example c:\temp\catalog_data.csv.
• Click OK.
• The file will be saved as tab delimited CSV file with all the information of the columns that we pulled.

OBIEE Repository Data:

All repository objects information can be generated with Repository documentation utility available within OBIEE Administrator tool. Generate the repository documentation and export to a text file.

To export the OBIEE Repository documentation to a text file:

• Open the Oracle BI Administration tool and connect to the OBIEE Repository containing the metadata that you want to include in the lineage. For example, SampleAppLite.rpd
• In the OBIEE Administration tool, select Tools > Utilities.
• In the Utilities dialog, select the Repository Documentation utility and click Execute.
• Save the repository documentation in a temporary file, for example c:\temp\RPD_Data.csv.
• Click Save.

Lets see how to make use of this RPD and WebCatalog data to build column Lineage in my next blog post.

Thanks,
Shiva

 

How to Clear the query cache in OBIEE11G Presentation Server?

When users run analyses, Presentation Services can cache the results of those analyses. Presentation Services determines if subsequent analyses can use cached results. If the cache can be shared, then subsequent analyses are not stored.

The files for the Presentation Services cache have names such as nQS_xxxx_x_xxxxxx.TMP. The files are created by the ODBC driver but generally do correspond to ODBC requests that the Presentation Services cache keeps open. The files are stored in the following directory:

ORACLEBI_INSTANCE\tmp\OracleBIPresentationServices\coreapplication_obipsn\obis_temp

The files for the cache are removed whenever Presentation Services shuts down cleanly. If Presentation Services shuts down unexpectedly, then various cache files might be left on disk. You can delete the files when Presentation Services is not running.

The Presentation Services cache is not the same cache that is accessed by the Oracle BI Server.

Sometimes we may need to clear the Presentation server cache also, to clear that follow the below steps:

• OBIEE 11G has a catalog manager command called “ClearQueryCache” to clear out the PS cache.
• The syntax of ClearQueryCache command is: 
  runcat.cmd/runcat.sh -cmd clearQueryCache  -online <OBIPS URL> -credentials <credentials properties file>
• Create a catalog manager credential properties file. Open a text file and type the following entries.
            Login = <weblogic_admin_Username>
            Pwd = <weblogic_Admin_Userpassword>  
  And save in a directory with the file name as catmancredentials.properties.
• Now open command prompt and navigate to <MW_HOME>instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalogmanager\
• And Run the following command to clear OBIPS query cache:
  runcat.cmd -cmd clearQueryCache -online http://shiva-pc:9704/analytics/saw.dll -credentials E:\catmancredentials.properties

Thanks,
Shiva