Monday, March 26, 2012

Extending the Siebel Data Model without Overstretching

Every now and then I end up in front of a monitor at a customer site and hear the words "Look how we have added some data to this applet!". And most of the times I have to suppress screams of agony when I witness something like this:
Click to enlarge (if you dare)
The above is an abstracted/anonymized version of an applet that the developer at the customer site showed me. Obviously they want to display the customer revenue for the all months of the current year and the previous year.

Of course they had created their CX_ETC table and added 24 fields to the Account business component.
The revenue data would then be synchronized with an external system via an integration object (which they created as well).

Now guess what happened!

End users wanted to see revenue data from two years ago as well...(and they will not stop there).

So the developer ended up creating another set of 12 columns in the CX_ETC table, adding another set of 12 fields to the BC, and finally (so he thought) adding 12 controls to the form applet.

A few days after deployment, they remembered that there is also an integration object which they quickly modified to add 12 new fields.

The developer became slightly worried if they were still on the right track...


If you see anything like the above, please erase it from the face of the earth immediately. It's a disease known as "Thinking in columns" where you just add a few columns and think you're done (most people are affected by this disease when they work too much with spreadsheets). Obviously this is the wrong track in enterprise applications like Siebel CRM.

How to extend the Siebel data model without overstretching:

I took the liberty of putting together a few hints to point to the correct way of implementing requirements like the above. Whenever you think "in columns", you should ask yourself if you shouldn't better think "in rows".

This is because in a relational database you can add rows (millions of them) to a table without any extra effort. But adding columns will always result in more work and you will be late for your date again ;-)

In Siebel CRM, we have pre-built 1:M extension tables. For the above requirement, we should have implemented a new child business component for the Account BC based on the S_ORG_EXT_XM table. This table has a foreign key to the account and you would only need to use one pre-built column for the month code (e.g. 2010/01 for January 2010 - or similar) and one for the revenue. (Note: I also proposed to use the Revenue BC but for reasons to far stretched for this post, the customer chose the XM table).

There is absolutely no need to add 12 new columns for each year worth of data as you would simply import new rows for each account.

To expose the interface for the external system, you create an integration object which now can transport all data without ever needing further configuration.

To expose the data to the end users, you have the choice between a master-detail view or an MVG applet. The list applet will always display any data which is related to the parent account and you will never have to modify the applet again.

have a nice day



Static Variable said...

This is such nice advise. I hope my business analysts and managers are seeing this article. I had to recently add 12 price fields to assets to show price in last 12 months.

And I totally agree that this problem is mainly due to working with excel sheets, and users want to export the data from siebel easily for their work.

Piyush said...

One thing which I never understand is why people always look for export functionality in siebel.

This earth has so many reporting solutions why not generate reports from there.

If still you people want to export from siebel create specific IOs for your specific needs...

12 new columns for price...thts disaster