Oracle MetaLink 3

Don’t we all love MetaLink, Oracle’s replacement for Siebel SupportWeb? Judging from everyone I’ve spoken to, the answer’s a resounding ‘No’!

To be honest, a lot of the griping is down to the usual sour grapes that accompany any software release that exposes users to change. I can’t help but be amused by all these enterprise software developers wandering around grumbling about having to deal with a new system that’s “obviously” far inferior from the one they’re used to… these would be the same developers who spend the rest of their time grumbling about the “stupid” users of their systems who can’t cope with the “simple” differences between Siebel 6 and 7… A salutary lesson, perhaps

Forgetting the comparison of the merits of the two systems, I do think it’s a shame that we now have another example of Oracle moving Siebel away from the traditional policy of eating it’s own dogfood. (This is a term for software companies actually using the software they build internally, a practise that is well regarded in the software industry due to it adding internal pressures to improve the product.) Not only do Siebel (the Oracle-subsidiary) no longer use Siebel (the product) as their main CRM system, they also no longer use it for tracking customer service requests. It will be interesting to see whether this change has any impact on the quality and relevance of future Siebel features.

A direct impact the change to MetaLink has had on me is that my stack of SupportWeb deep-links no longer work, including the ones on this blog (thanks Oracle – a redirect would have been nice!). I’ve now been through the site and updated all the links I could find, please let me know if you spot any links that don’t work.

Siebel User Group Australia Event – 21 February

For all Sydneysiders reading: the next Siebel User Group event will take place on the 21st February. We’re back at the AGSM for a panel discussion on The Role That Siebel Plays In Your Enterprise, after which there’ll be the usual opportunity to catch up with past and future colleagues.

It’s a good broad topic with excellent panellists who I expect them to make my moderating role largely redundant. The last two SUGA events have been oversubscribed well in advance, so get on and register now.

Improving List of Values performance

A quick titbit to get things rolling again after an extended silly season hiatus…

Whenever you need to access lists of values data in your configuration, note that of the business components defined on the S_LST_OF_VAL table, Picklist Generic is cached while List Of Values is not. The impact of this is that repeated queries to Picklist Generic will be served from the memory of the Object Manager, whereas queries to List Of Values will always hit the database. So for improved performance be sure to use Picklist Generic in your configuration, not List Of Values.

Caching behaviour is controlled by the business component property Cache Data: when this property is set to true then the database is only read once for a given query. You can see in Object Explorer that Picklist Generic has Cache Data set to true, while it’s false for List Of Values. This configuration allows the List Of Values BC to be used for managing LOVs, where it’s critical to always have the current database values, while Picklist Generic can be used where performance is more important.

International Phone Formats

Since version 7, Siebel has had a somewhat confusing treatment of international phone numbers: numbers entered in the default format are stored simply with the country code, while numbers entered in any other format have a format string appended after a character return. All phone numbers are then displayed in either the default format or with their specific format string applied. You with me?

To illustrate with some examples….

Suppose the following default formats are defined using the PHONE_FORMAT LOVs:

Type	Display Value	Language-Independent Code	Order
PHONE_FORMAT	(000) 000-0000	(000) 000-0000	1
PHONE_FORMAT	(00) 0000 0000	(00) 0000 0000	61

The Display Value defines the format for the number, while the Order equates to a country code – so +1 for US, +61 for AUS.

Now, let’s assume we’re running the Siebel Client on an Australian workstation. How do different numbers get treated?

User Entered	Displayed As	Stored As
0298766543	(02) 9876 6543	+610298766543
(02) 9876 7654	(02) 9876 7654	+610298767654
+610298768765	(02) 9876 8765	+610298768765
+61 02 9876 5432	02 9876 5432	+610298765432 00 0000 0000
0412 3456 7890	0412 3456 7890	+61041234567890 0000 0000 0000
(0423) 3456-5678	(0423) 3456-5678	+61042334565678 (0000) 0000-0000
+14321239876	+1(432) 123-9876	4321239876
+1 432 123 8765	+1(432) 123-8765	4321238765

Essentially, non-American numbers get stored as a continuous string of numbers, including the country code and ‘+’ international indicator. If the number is entered in a format that matches the default format, then that’s it. If the number is entered with a specific format, then this format is appended to the phone number. The separator between the actual phone number and the format string is a character return plus line feed [CHAR(13) || CHAR(10)].

This vanilla behaviour causes all sort of problems with integration – CTI, EAI, SQL etc – to the extent that most 7+ implementations I’ve worked on have ended up with some form of scripting to get around it. I’ll talk about some of the impacts and workarounds in more detail in future.

More on Script Assist

A great update on the Script Assist functionality I mentioned a while back. In a comment on my original post, Jake pointed out a “*huge* performance impact when the method listing and autocomplete options were enabled on scripted BCs in v8″.

Now, an anonymous commenter posts that this problem has been recently fixed and “should be available as a QF and in the FixPacks”. The comment also highlights some of the additional scripting improvements in 8.0 that I hadn’t come across – including “Fix & Go” debugging, Siebel’s version of Visual Studio’s “Edit & Continue” – and signposts an “upcoming 8.1 feature, Script Performance Profiler”.

I’m very (inordinately?) pleased with the additional information. Partly because the improvements are all good, legitimate changes that drag Siebel Tools towards with other fully featured development environments, but moreso because it’s great to see an employee of Oracle monitoring and feeding back directly into the development community. This is exactly the sort of thing I was hoping to see when I started the blog… may a thousand geeky conversations bloom

Why there may be a place for Direct SQL

Over at Service Oriented Shenanigans Graham’s written a good, provocative piece titled Why Direct SQL is a very stupid thing to do. I’m happy to be baited… I started to respond there, but it turned into something worth a full post. Hopefully trackback pulls the whole conversation together like it’s supposed to…

Graham argues that because Siebel implements referential integrity through the repository rather than through database rules, it’s idiotic bypass all these rules and run SQL directly against the database. I’d make a few points:

• If you’re using Siebel Remote, then direct SQL could be additionally stupid. Any changes made directly to the database are not be propagated to the transaction log, so won’t be routed to the local databases, so your server will be out of sync with your remote clients… Nasty!

• If, however, you’re NOT using Siebel Remote, or you’re re-extracting all your local databases anyway, then I’d argue that there is a place for direct SQL. A limited place, sure, requiring utmost care, diligence etc etc, but at the end of the day it’s just another tool to only be used when appropriate. Siebel Expert Services will happily run direct SQL when the alternatives are ridiculously cumbersome. The alternatives (e.g. EIM, EAI) are also quite capable of significantly damaging your database and also require an appropriate level diligence: developers shouldn’t lapse into thinking that just because things are being done with the ‘proper’ methods they can’t do harm (EIM Delete in the wrong mode, anyone?).

• If you use direct SQL to insert a record, then you won’t get a Siebel Row Id. The procedures to generate a Row Id are in the database and could be called directly, but this really is idiotic. (I actually worked on an implementation where Siebel Expert Services had done this – hmmm…)

So don’t use direct SQL to insert records, and don’t break your referential integrity, but do remember that SQL is just another tool, with pros and cons to be weighed against the alternatives. And don’t come crying to me if you break your database and your architect justifiably calls you an idiot.

Debugging SQL with Oracle Cost Based Optimizer

I recently had a performance issue in Siebel 7.8 that on brief inspection looked like a SQL issue: the application was taking forever to build a list applet, re-query was just as slow, there weren’t any obvious calculated fields, no dodgy MVGs etc etc… it all pointed at the database.

So I did the obvious thing and spooled the SQL. Sure enough, the applet query was taking nearly 30 seconds to complete. Next step, fire up Toad to get an explain plan and start picking apart the SQL. Except when I pasted my query into Toad, it completed in half-a-second. The explain plan showed perfect use of the expected unique index scans, with no full table scans or other anomalies. Why the discrepancy?

After a bit of digging around, the answer came from our often-invaluable TAM: SupportWeb TechNote 582. The technote has all the background to the Cost Based Optimizer, and the key is the explanation of Siebel’s Oracle session variables. In summary, to make your SQL client behave like a Siebel session, you need to set the following session variables:

ALTER SESSION SET optimizer_mode = FIRST_ROWS_10
/
ALTER SESSION SET "_HASH_JOIN_ENABLED" = FALSE
/
ALTER SESSION SET "_OPTIMIZER_SORTMERGE_JOIN_ENABLED" = FALSE
/
ALTER SESSION SET "_OPTIMIZER_JOIN_SEL_SANITY_CHECK" = TRUE
/

In Toad, you add these statements and choose to ‘Execute as script’. The really significant variable here is that first one: optimizer_mode = FIRST_ROWS_10. This tells Oracle to optimize for the first page, rather than the whole query, which can dramatically change the way indexes are evaluated.

In my case, the session variables allowed me to replicate the slow performance of the query in Toad, the explain plan of which showed Oracle using the wrong index, which identified the incorrect evaluation of costs, which (eventually) pointed to a root cause of stats being generated on empty tables. Obviously!

Discussion Forums for Siebel

A new Siebel web resource… The Oracle Technical Network have recently added Community Discussion Forums for Siebel (you’ll need to sign up for free membership of TechNet).

Early days on the discussion pages, but in time I’d expect this to replace ITToolbox as the default destination for Siebel queries. I’ll be keeping an eye on the Siebel Technology thread and hoping to learn a few things.

(via both Andy and Graham)

Siebel User Group Australia

Things have been quiet around here while I’ve been on vacation, but a little side project I’ve been involved with has been barrelling along: I’m very pleased to be able to say that Siebel User Group Australia is up-and-running.

I’ve mentioned before my feeling that Siebel is severely lacking in good community support, at least in Sydney, and compared with other similar technology specialisations. Turns out that I’m not the only one with that sentiment. Driven largely by the enthusiasm of David Wong, a small group of us have been meeting for a couple of months to try and get a ‘grassroots’ movement happening. Sounds like some kind of revolution!

The outcome is SUGA, a new organisation that will hopefully become a catalyst for ongoing Siebel community events, networking and knowledge sharing. To kick things off, we’ve planned two evening events for the upcoming months. This coming Thursday’s “Siebel Upgrade Strategies” panel is already oversubscribed – a promising sign! – so register early for November’s “Siebel 8.0 Upgrade Case Study”.

For those in Sydney, hope to see you Thursday. I’ll be the skinny bloke at the front kicking off the whole shebang, then disappearing into the background while the real experts do their thing…

Synchronous Wait Steps

This one caught me out just recently while I was building a business process to poll a service every couple of seconds. Seemed simple enough: add a Wait Step to my flow, set MeasureUnit to Seconds and duration to 1, then call my service and loop back to the Wait under certain conditions. In my example I want the flow to fail if the service doesn’t complete after half a dozen attempts, so I set the Maximum Iterations property on the service step to 6 and handled the error that occurs on the 7th attempt. The workflow should run synchronously in the Application Object Manager, with the UI waiting for a response.

According to all documentation this should have worked, but instead the workflow was immediately returning a response to the UI the first time it hit the Wait Step. The workflow was continuing on exactly as expected in the background, but it appeared that Siebel was automatically persisting the workflow, making the flow asynchronous.

Took a bit of digging around, but the solution was to change the Mode of the business process. In Interactive Flow or Service Flow mode the Wait step causes the flow to move out of the UI context, but in good old 7.0 Flow compatibility mode it works as expected, blocking the UI until the workflow completes.

Doesn’t fit in very well with Siebel’s mandate of not creating new 7.0 Flows, but what can you do when there are bugs..?