OBIEE 11g Transactional Update Failed [nQSError:37005] – Patch Available
Posted on September 6, 2011 by Paul McGarrick
Filed Under 11g Repository, Admin Tool, OBIEE 11g, OBIEE Errors
I’ve blogged in the past about the annoying Transactional update failed message that is seen when editing the OBIEE 11.x repository in online mode. Well it seems Oracle have finally released a patch to fix this – #9884975. It can be downloaded once you’ve obtained the necessary password via a Service Request.
Adding an XML Data Source in OBIEE 11g
Posted on September 5, 2011 by Paul McGarrick
Filed Under 11g Repository, OBIEE 11g, XML
Here’s a quick example of how to manually set up an XML file as a source in OBIEE 11g.
First I created a simple example file named XML_Example.xml. Here is the structure:
Download it here if you want to try it out and are too lazy to type.
Next, create a new database in the physical layer. Set the Database as XML.
We need a Connection Pool. Set the ‘Data Source Name’ as the path to the directory on the server where the XML file is found.
Create a table with the name of the XML file (minus the file extension).
The columns within the table can be called whatever you want. What is important is the XPath declaration which describes how to find the column within the XML tree structure.
We are left with this structure in the physical layer:
Let’s try it out. We can use SELECT_PHYSICAL to query the repository physical layer directly from Administration > Issue SQL. E.g.
select_physical “TOTAL-BI” from “An XML Example”.”XML_Example”;
As if by magic, the data is retrieved from the XML file.
OBIEE 11g Usage Tracking RPD
Posted on September 1, 2011 by Paul McGarrick
Filed Under Installation, OBIEE 11g, Usage Tracking
It seems a lot of people are having problems implementing Usage Tracking in OBIEE 11g. The sample repository RPD file supplied by Oracle as part of the 11.1.x install is from the older (10g) version and requires uplifting via the upgrade assistant. Unfortunately the upgrade assistant requires that the repository has a password specified and this can’t be set unless you have the Admin Tool from OBIEE 10g.
To save time I’ve created an uplifted version which is compatible with 11.1.1.5 (release 308). Download it using the link below – pw=Administrator.
I’ve made the necessary tweaks to the definition of table S_NQ_ACCT so that the structure will match that of the vanilla table found in the (PREFIX)_BIPLATFORM schema created with the RCU.
For the curious among you, column RUNAS_USER_NAME has now become IMPERSONATOR_USER_NAME and there are 2 new columns: QUERY_BLOB and QUERY_KEY. See the screenshots comparing the old table definition to the new.
Old Definition:
New Definition:
Finally, for completeness if you want to create table S_NQ_ACCT in another schema you can use this code (Oracle):
DROP TABLE S_NQ_ACCT ; CREATE TABLE S_NQ_ACCT( USER_NAME varchar2(128), REPOSITORY_NAME varchar2(128), SUBJECT_AREA_NAME varchar2(128), NODE_ID varchar2(15), START_TS date, START_DT date, START_HOUR_MIN char(5), END_TS date, END_DT date, END_HOUR_MIN char(5), QUERY_TEXT varchar2(1024), QUERY_BLOB clob, QUERY_KEY varchar2(128), SUCCESS_FLG number(10,0), ROW_COUNT number(10,0), TOTAL_TIME_SEC number(10,0), COMPILE_TIME_SEC number(10,0), NUM_DB_QUERY number(10,0), CUM_DB_TIME_SEC number(10,0), CUM_NUM_DB_ROW number(10,0), CACHE_IND_FLG char(1) default 'N' not null enable, QUERY_SRC_CD varchar2(30) default '', SAW_SRC_PATH varchar2(250) default '', SAW_DASHBOARD varchar2(150) default '', SAW_DASHBOARD_PG varchar2(150) default '', PRESENTATION_NAME varchar2(128) default '', ERROR_TEXT varchar2(250) default '', IMPERSONATOR_USER_NAME varchar2(128) default '', NUM_CACHE_INSERTED number(10,0) default null, NUM_CACHE_HITS number(10,0) default null ) ;
OBIEE 11g – Deploying Custom Skins, Styles etc.
Posted on September 1, 2011 by Paul McGarrick
Filed Under OBIEE 11g, Skin, Tutorials
It’s been posted on the web before but I always struggle to find the link to this useful Oracle White Paper. It describes the steps required to customise the OBIEE user interface. To save you searching, here’s the link:
There’s also a new tutorial from Oracle on this subject. See the OTN here:
- Creating and Deploying Custom Styles, Skin, and Messages in Oracle BI EE 11g (Oracle account required)
OBIEE Repository Modelling Articles
Posted on August 30, 2011 by Paul McGarrick
Filed Under 10.x Repository, 11g Repository, OBIEE 10, OBIEE 11g, Tutorials
Here are a few useful articles I came across concerning OBIEE repository (RPD) modelling. They are posted on Oracle BI Development’s BI Foundation blog and might be of use to anyone starting out in the OBIEE repository for the first time.
Full credit to Bob for doing a fine job on writing these up.
OBIEE 11g – Export To The File System
Posted on August 22, 2011 by Paul McGarrick
Filed Under Dashboards, Delivers, Export, OBIEE 11g, Reports
Here’s a nice script I wrote to enable the exporting of content from OBIEE to the Windows file system. Useful if you don’t have enough licences for everyone to consume reports/dashboards within the OBIEE application or if you want to build an archive over time.
The principle is quite simple, an Agent is set up in Oracle Delivers and the Delivery Content specified as normal. In this case a whole dashboard page is going to be exported as a PDF.
On the Actions tab we add a new action to Invoke a Server Script.
Click the Edit Parameters (pencil) icon to change the properties. Set the language to VBScript and set the Script Path to be wherever the export script (downloaded from the bottom of this post) has been saved on the OBIEE server.
Finally we must specify 3 parameters for the export script to use: the file to be exported, the desired file and folder names. See the screenshot:
Now each time the Agent is executed it will create a subdirectory (with yyyy-mm-dd format) and within that directory the file will be saved.
Here you can download the VBScript that does the hard work. Neat eh?
Multiple LDAP Authentication Providers in OBIEE 11.1.1.5
Posted on July 18, 2011 by Paul McGarrick
Filed Under LDAP, OBIEE 11g
With the release of OBIEE 11.1.1.5 we are able to get round one of the drawbacks from the previous version, namely having more than one LDAP provider configured in Weblogic. Oracle have virtualised the providers so that more than one can be used at a time. To enable this, one extra config step is required:
Open Enterprise Manager, go to Weblogic Domain > bifoundation_domain. Right click and choose Security > Security Provider Configuration
Click the Configure button to get to the Identity Store properties.
Add a custom property called virtualize with value true.
Click OK then restart both the Admin Server and Managed Server.
There’s more information in the Oracle documentation.
OBIEE Multi-Environment Quick Login
Posted on July 15, 2011 by Paul McGarrick
Filed Under OBIEE 10, OBIEE 11g
Here’s a little snippet that can save time if you often need to switch between multiple OBIEE instances (Local, Dev, QA, Prod etc.) It can be a pain to type your username and password over and over so I came up with a one-click launch page.
I like set my browser home page up with all my frequently used links, system login information and RSS feeds etc. You need a little HTML knowledge to build such a page but it’s not too difficult.
Anyway, part of my launch page contains an HTML form with the username and password embedded as hidden fields. It’s then a simple click to log in to any given instance. Here’s the code:
<html>
<head>
<title>OBIEE Quick Launch Page</title>
<script>
<!--
function setActionAnalytics(){
f = document.forms[0];
for (i=0;i<f.server.length;i++){
if(f.server[i].checked) {
tt = 'http://'+f.server[i].value;
tt+= ':9704/analytics/saw.dll?bieehome';
f.action = tt; break;
}
}
}
-->
</script>
</head>
<body onload="setActionAnalytics()">
<form id="logonForm" method="post" action="">
<input type="hidden" name="NQUser" value="weblogic" />
<input type="hidden" name="NQPassword" value="weblogic123" />
<input type="hidden" name="selectlanguage" value="en" />
<input type="radio" name="server" value="localhost"
onclick="setActionAnalytics()" checked="true" />Local<br />
<input type="radio" name="server" value="dev-server-name"
onclick="setActionAnalytics()" />Dev Server<br />
<input type="radio" name="server" value="qa-server-name"
onclick="setActionAnalytics()" />QA Server<br />
<input type="radio" name="server" value="prod-server-name"
onclick="setActionAnalytics()" />Prod Server<br />
<input type="submit" value="Sign In" />
</form>
</body>
</html>
I also have a similar form for logging in to Weblogic Administration Console which I can share if required.
Using Oracle SQL Developer with MySQL
Posted on July 15, 2011 by Paul McGarrick
Filed Under MySQL, SQL Developer
A few months back I blogged about using Oracle’s excellent SQL Developer tool with Microsoft SQL Server databases. In a similar way you can also connect to MySQL – here’s how…
You will need to download the most recent version of Connector/J from the MySQL website. This is the official JDBC driver for MySQL, made available under the GPL.
Once you have the driver unarchived, move the .jar file somewhere permanent. I like to put all my third party JDBC drivers together in the SQL Developer root directory.
Next go to Tools > Preferences > Database > Third Party JDBC Drivers. click Add Entry and browse to the jar file.
You should now find a new tab has appeared when you next create a new database connection.
Simple eh?
Oracle Magazine BI Articles
Posted on July 1, 2011 by Paul McGarrick
Filed Under OBIEE 11g, Oracle, Tutorials
Oracle Magazine is published bi-monthly and is put up online as part of the Oracle Technology Network. The magazine often contains useful articles on Business Intelligence, usually written by Mark Rittman. Here are some recent examples that may be of interest:
- Upgrade to Oracle Business Intelligence EE 11g
- Putting Business Intelligence on the Map
- Using the Action Framework
- Defining KPIs and Scorecards
- Essbase 11.1.2 Aggregate Storage Option Databases
- Deriving and Sharing Business Intelligence Metadata
- Oracle Warehouse Builder 11g Release 2 and Heterogeneous Databases
« go back — keep looking »
