A Conditional, Data-Driven Action Link

The initial release of this functionality was seen as a positive step towards tighter integration of the Siebel and Analytics applications. Since then, many versions onward the functionality has moved on little and there are still limitations which continue to frustrate developers.

The 'so-called' increased usability of action links available in v7.8.x onwards is buggy and doesn't provide the same 'mouse-over' information when choosing the custom data format route (accessed via the 'Data Format' tab for the column in the request); so personally, I prefer the custom data format. The syntax can take a little getting used to, but is straightforward to manipulate once you understand the components.

Current limitations aside (such as the inabilty to target child applets in Siebel), a little more intelligence can be added at the request level to make your action links conditional, based on the data you return in your request.

This might be performed using a CASE statement, but more complex functions could provide a range of results to use for your action links. For example column A contains your data, column B your conditional action link. The basic function syntax for column B would be as follows:

CASE Column A
WHEN = 1 THEN 'Action Link 1'
WHEN = 2 THEN 'Action Link 2'
ELSE 'Action Link 3'
END

So you have your basic conditional navigation here, but its important to get the syntax right for each action link created. Ultimately you want this to resolve correctly for the custom data format.

With a standard action link, normally 3 elements are provided in the custom data format: View, Applet and Row Id. In this case, you will provide all 3 elements as one string from column B and amend the data format slightly.

The string will need to be correctly 'escaped' for both Analytics to recognise it as a string, but also for it to resolve correctly using the javascript function NQSWENav, referenced when creating action links.

- Char(39) : SQL escape character for single quotes
- || : double pipes used by analytics here to concatenate strings
- , : commas separate the 3 arguments normally used in action links

Char(39)||'Order Entry - Line Items View (Sales)'||Char(39)||','||Char(39)||'Order Entry - Order Form Applet'||Char(39)||','||Char(39)||Order."Row Id" ||Char(39)

Notice you're adding 2 single quotes each time, the 1st one will be removed by the analytics server leaving the second one when using the javascript function. Add this string after the 'THEN' portion of your CASE statement. For each different condition in the CASE statement add the action link functionality using this syntax replacing the view, applet and row id arguments each time.

Your statement will probably looks something like this:

CASE Column A
WHEN = 1 THEN Char(39)||'Order Entry - Line Items View (Sales)'||Char(39)||','||Char(39)||'Order Entry - Order Form Applet'||Char(39)||','||Char(39)||Order."Row Id" ||Char(39)
WHEN = 2 THEN Char(39)||'All Service Request across Organizations'||Char(39)||','||Char(39)||'Service Request List Applet'||Char(39)||','||Char(39)||"Service Request"."Row ID"||Char(39)
ELSE Char(39)||'All Activity List View'||Char(39)||','||Char(39)||'Activity List Applet - Basic - No Toggle'||Char(39)||','||Char(39)||Activity."Activity Id"||Char(39)
END

Now that you've got your CASE statement, open the 'Format Column' portion of the request and go to the 'Data Format' tab. Make sure the 'Override Default Data Format' is checked and select 'Custom Data Format' from 'Treat Text As' drop-down list. In the 'Custom Text Format' add the following syntax:

@[html]"<a href=\"javascript:NQSWENav("@"); \">"More..."</a>"

- @ : refers to the contents of the column
- NQSWENav : the javascript function used to navigate to the Siebel application

It is recommended not to display the column contents, in this case it will be quite long, rather provide a meaningful link prompt (e.g. 'More...', 'Link...'), but this is entirely personal preference.

Look at the results of the request to check you've got the syntax right, then run it from your integrated application (Siebel/Analytics) to see those action links in action.

N.B. For those new to action links, it is the Row Id column from the base table which is required (not any other identifier) for action links to work correcly.

New Version of OBIEE

Version 10.1.3.3.3.1..3.1.1.2..3.1..3. of OBI Enterprise Edition is now released.

It's got some new stuff in.

How exciting!

7.9 Is here, Huh?

So you thought that OBI EE was the new Analytics. Think again. Siebel analytics has jsut gone into version 7.9, but so far I have only seen Applications 7.9, so it looks like platform is either 7.8 or OBI EE :

There new names though:

Siebel Analytics Application now becomes

Oracle® Business Intelligence Applications (OBIA)

Siebel Analytics Repository now becomes

Oracle Business Intelligence Applications repository (OBIAR?)

Spotted the trend yet!!


This is from the documentation.
Changes to Oracle BI Repository
The Siebel Analytics repository (SiebelAnalytics.rpd) was renamed to the Oracle Business
Intelligence Applications repository (OracleBIApplicationsApps.rpd).
The Oracle BI repository contains the following changes:
■ Naming conventions. Logical tables have been renamed to start either with "Dim," to indicate
dimension tables, or "Fact," to indicate fact tables.
■ Data security. The position security design was modified to use the new table W_POSITION_DH instead of W_PARTY_LOGIN. For more information about data security, see the Oracle Business Intelligence Applications Installation and Configuration Guide.
■ Presentation layer. Some old catalogs have been replaced with new catalogs. The catalogs that
have been replaced are designated with a STOP icon in the presentation layer; these catalogs
will be deleted in a future release. It is highly recommended that you migrate your reports to the corresponding new catalogs.
■ Time dimension. A common time dimension is used in all new catalogs to indicate a unified time dimension across all facts. Some secondary date dimensions are still exposed when necessary, but they are not conformed to all facts, and, thus, should be used cautiously.

I never did trust using the W_PARTY_LOGIN so I'm glad it has gone. I'll let you knwo what I think of the replacement when I have tested it.
Good to see a common time dimension, but how does it work? Again mre testing will tell.

Make the most of your repository

Quick and Easy Mail Merge

Why not make the most of your repository, as well providing the backbone of your reports it is also a great source of clean organised data that is easily accessible by other programs. Here’s one way to address envelopes or letters using MS Access and Word.

Create a new blank database in Access.

Create a new query in design mode, close the Show Table dialogue and go to Query>SQL Specific>Pass-Through.

Now click on the Properties icon on the toolbar and click the ellipsis next to ODBC Connect Str, this will open the Select Data Source dialogue.

Click the Machine Data Source tab and click New and follow the instructions to create a Data Source Name based on Oracle BI Server from the drivers list, follow the instructions and enter appropriate user names and passwords to suit your systems configuration. Enter your repository user name and password and answer yes to save this information in the connection string.

Type your SQL query. Click the icon to run the query and test you SQL.

Now create another new query in design view, click the queries tab in the Show Table dialogue and select your pass through query, add the columns to the new query.

Click the drop down arrow next to the Query Type icon and select Make-Table Query, save the query with an appropriate name, and click the Run Query icon.

You will be presented with a warning about the number of records to be pasted into the table, dependent on your needs you may decide to set some selection criteria to break the list into more manageable chunks, this may depend on whether you are merging to a letter, labels or envelopes. You could create multiple Make- Table queries to populate appropriately named tables based on select by County, State, City or Name beginning with…... They can all be based on the one pass through query.


If you have the coding skills you can automate the running of these queries and refreshing of the tables.

Once the make table query has run, click Tables in the objects list and you will see the table you have created, select it navigate to the Tools>Office Links>Merge it with Microsoft Office Word, or click the down arrow next to the Office Links icon, follow the instructions in the dialogue, simple.

Documentation, It's MAD!

Let's face it, there's not many people who like documentation, except a good BA of course, but every single project needs it.

Now if the title and first sentence haven't put you off I'm going to let you into a few secrets.

1. A good set of design docs will save your project money
2. Post project documentation (actual designs, architecture maps, etc) will rarely, if ever, get read
3. Your documentation will be instantly out-of-date with release x.1

Still with me? OK, they're not really secrets, but they are issues we have to contend with. The motivation to write something detailed that will never get read and will be obsolete in 5 minutes, without the proper tools is pretty low. Especially if you have just had the go-live party the night before :)

So a typical Analytics (Oracle BI EE for the late comers) will need the following

a. Design of Datawarehouse - This includes Tables, fields, indices, schemas, tablespaces, parameters, views, Materialized views, etc
b. Design of DAC - EP's, Subjects, Table Groups, Tables, Indices (again!), Tasks
c. Design of ETL
d. Design of Analytics Repository (Oracle BI Repository)
e. Design of Webcat
f. Integration designs
g. Technical Architecture Map
h. Support Guide - What to do if.... scenarios

what did I miss?

(We are going to assume Analytics with Informatica ETL for this example)

Here's the catch. You have a reference to a physical table in at least four places - Db, DAC, Informatica, Physical layer. In your design you want to ensure referential integrity, i.e. don't refer to a table in the DAC that does not actually exist in the Db!. Another example where you have this is with tasks in the DAC that refer to Informatica Workflows.

My prefered approach is to create all these documents up front, i.e. in the design phase (say that last bit slowly with a deep voice), then update the documents at the end for what actually got built.

What tools do you have?

Most project sites that I go to will have MS Office, but you really need the professional version for Access and Visio Professional. You could try to publish the designs in Word or HTML, and you could even use Analytics to present the design!!
The Word vesion is probably the easiest (least technical) but requires most work, the Analytics version will need a place to be set up, with Webserver, db and Analytics server licence.


What do I use?

Glad you asked that because we have developed the MAD - Majendi Analytics Designer, system. This is a semi automated system that can document an existing Analytics aplication, from DB to Webcat. It produces MS Word documents which details all of the items in a) to e) above. I supplement this with Visio diagrams for star schemas (This also uses the design repository for the source tables).
As well as the Word documents templates being populated we also have a miniture Analytics system which details the meta data. The heart of the system is a set of Access databases which import metadata from txt files or directly using ADO (so it can work on any database) to conect to the databases. A side benefit is that building the system teaches you all the underlying data structures in the ETL and DAC.

How do you get the information you need?

You can query the repositories directly in the Oracle/SQL database.
You can use the documentation utilities of Analytics and the Web catalog manager
I also use the UDML file that you can get from Analytics, and parse this into an Access Db.

(If you want to know about UDML files I'll be posting a short note on that later).

The beauty about the MAD system is that I can document a whole complex system in a couple of days, even better is the ability to analyse an existing application to help with an upgrade, or with new releases - your documents can very quickly reflect the underlying new system.

So there you have it, Documentation may be boring but you can make it interesteing by using your technolgy skills to reduce the problem down to automated tasks, just like we did. :)