Mark Rittman has an excellent article about querying the DAC repository database tables, including a downloadable RPD file. Being new to working with RPDs I thought it would be good practise to explore this as well as hopefully get some useful information about our current ETL deployment.
I downloaded the RPD to c:\OracleBI\server\Repository and opened it up in the Admin tool (Administrator/Administrator).
First off I changed the connection pool to point to my DAC repository database, having setup a TNS entry for it first.
Then I had to rename the physical schema from DAC to DAC_REPO, and moved S_ETL_DAY from S_NQ_ACCT to DAC_REPO – both of these are just how our DBs laid out, YMMV
To test the connectivity I did Update Row Count over one of the tables, and got
There was an error while updating row count for “ORCL”..“DAC_REPO”.“S_ETL_DAY”:
[nQSError: 17001] Oracle Error code:942, message: ORA-00942: table or view does not exist …
I’d already checked where the tables did reside through SQL Developer:
, I figured it was maybe not qualifying the table name, so found this in the connection pool settings:
:
W_ETL_FOLDER.PRIORITY, W_ETL_RUN_STEP.GROUP_NAME and W_ETL_RUN_STEP.RUN_MODE
I deleted these from the Physical layer, hoping that it would warn me if they’re used in the BMM or Presentation layer. It didn’t - but running a Global Consistency Check warned me that Run Mode is used in the Task Runs logical table, so I deleted it from there too.
I saved the RPD and change my NQServer.config to point to it:
Starting up BI Server I got an error:
2009-07-23 11:47:45 [nQSError: 26011] File C:\OracleBI\server\Config\NQSConfig.INI Near Line 30, near : Syntax error [nQSError: 26012] .
So I guess it’s not happy with the spaces in the filename. I changed it to
Star = “DAC Analysis.rpd”, DEFAULT;
and it starts up fine. I then got an error trying to log into Answers:
Error connecting to the Oracle BI Server: The specified ODBC DSN is referencing a subject area that no longer exists within the Oracle BI Repository.
State: 08004. Code: 10018. [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused. [nQSError: 43037] Invalid catalog, Sample Sales, specified. (08004)
(I like the fact that Presentation Services parses the ODBC error into something more intelligable. I’d have eventually figured it out from the ODBC error, but being told up front what the problem is doesn’t happen enough with computer errors:) )
This happened because in my system DSN (pointed to in the Presentation Services instanceconfig.xml) I had checked the “Change default catalog to” box, and it was trying to find SampleSales in the repository when it didn’t exist.
Unticking this box finally let me log in
On a specific point, the “# Succesful” and “# Failed” measures in Task Runs refer to the number of rows, not number of tasks as it could be interpreted.
The RPD is described by Mark Rittman as:
[…] no means finished or error tested, but if you want to see how I put the example together, feel free to download it and see how it’s put together.[…]
and this is a fair description of it. It’s a great starting point which has done a lot of the hard work, and it is very useful as a head-start for understanding the DAC repository tables.
However, it would be wrong to think of it as an out-the-box solution for super-fancy reporting against the DAC. Realistically you still need to understand the tables and data that you’re analysing otherwise you’ll come up with misleading or plain wrong reports.
But that said, if you have a DAC deployment that you maybe want to do some serious performance work with and want a way to visualise what’s going on in your batch, this is a great starting place.
