For Instance

Bit of a techie one today, a short introduction to instanceconfig.xml.

Instanceconfig.xml (we’ll call it IC) is a configuration file for the Analytics web. It is a replacement for using the registry. You could use the registry but there’s little point, and you probably won’t have access rights anyway.

The IC file sits in C:\SiebelAnalyticsData\Web\config and is installed with the application. The basic installation provides a simple set of instructions to the Web component (see below), which one can add to or change.

The basic module looks like this, (i have removed the lead<)
?xml version="1.0" encoding="utf-8"?>
webconfig>
serverinstance>
dsn>
AnalyticsWeb
/dsn>
javahome>
C:/j2sdk1.4.2_08
/javahome>
catalogpath>
C:/SiebelAnalyticsData/web/catalog/Majendi_dev.webcat
/catalogpath>
catalogmaxautosaves>
10
/catalogmaxautosaves>
alerts>
scheduleserver>
UK_SRV_123
/scheduleserver>
/alerts>
/serverinstance>
/webconfig>


Notice the opening tag, ?xml version="1.0" encoding="utf-8"?>
. This will be present in all the xml files in Analytics. Make sure you don’t place anything before this, including spaces (As I found out the hard way).
The javahome>
tag points to your installation of Java SDK. Make sure there are no spaces in the path or this will fail.
The catalogpath> is easy to understand, and here I have renamed the catalog to Majendi_dev.webcat. If you put a name of a file that does not exist in this key then Analytics will create it for you.
The dsn> tag provides the name for ODBC connections.

You may notice that there is a sub-section for Alerts, which, in this case, tells the web server where to find the scheduling server.

There is no simple source or reference to tell you what other tags may be added, and what their settings are (I’ll try to put one in by book), but you can glean hints from other peoples SR’s and from bookshelf!

If you add a views tag then you can customise the number of rows. Try adding the following

resultrowlimit>12000
/resultrowlimit>

This a basic one, it increase the maximum number of rows that Analytics will return (the default setting is 10,000)

Other more complex ones can change small behaviours within the system, for example,

pivotview>
maxvisiblepages>
1000
/maxvisiblepages>
/pivotview>.


This set could be useful

views>
table>
defaultrowsdisplayed>200
/defaultrowsdisplayed>
defaultrowsdisplayedindelivery>250
/defaultrowsdisplayedindelivery>
defaultrowsdisplayedindownload>1250
/defaultrowsdisplayedindownoad>
/table>
/views>


Do you want to change the default skin, easy..


!-- Default Styles -->
defaultstyle>Majendi
/defaultstyle>
defaultskin>Majendi
/defaultskin>

How giving more values in a drop down list
!-- Prompts -->
prompts>
maxdropdownvalues>20000
/maxdropdownvalues>
/prompts>



Have a good look at the bookshelf and Service Requests in SupportWeb for many more examples. If you find any undocumented ones post them here, and I’ll put them in the book.

And then there were three...

ETL tools used in Oracle BI that it.

With Oracle going round buying up lots of companies they have been creating a huge potential for confusion for the integration community, including their own consultants.

We had an Oracle consultant in the other day to tune up the database performance (he was very knowledgeable by the way). He had just transferred into the new BI practice and was an expert with OWB, the in house ETL tool. When faced with Informatica he had to learn quickly, on site with us showing him how to use it. Strange really, as we licence the product from Oracle.

It’s not the consultants fault; I am led to believe that there are over 500 products being used in Oracle environments, so there are probably more than three ETL tools in the mix (there’s even talk of ETL free warehouses).

But what should customers use? What should we be advising customers starting a new BI project? What will Siebel 8 use at it’s core?

Over the past year we have had lots of information and rumours coming from user groups, press releases and the consultant word of mouth. Most of what I have heard either conflicts with other messages or just doesn’t make any sense. An example is that there is talk of removing the DAC (a very useful tool btw), and replacing with an Informatica tool, but that Informatica is not their preferred ETL tool.

Over the next 3-6 months I’ll be advising clients on the technology choices they have, and when choosing Oracle, the different flavours. I’ll also be recruiting more staff and training others on the appropriate software tools. Without clear open communication from Oracle these tasks are near impossible.

I hope that we can get a better idea of where things are going as soon as possible.

Self Configuring Warehouses

One of the reasons people don’t like the DAC is that it takes control over the ETL.
One of the reasons I like the DAC so much is the fact that it manages the ETL process with ease!

Not only does it run the Informatica tasks in the right order (providing you tell it the correct order!), but the DAC also runs other tasks, like SQL jobs.

Up to now the preferred method of running SQL in the warehouse or OLTP has been either directly (i.e. via TOAD or SQLPlus) or via stored procedures. But with the DAC you can run SQL directly that you keep in txt files in C:\SiebelAnalytics\DAC\CustomSQLs.

The usual application for these files is for simple update tasks, in the Oracle supplied example script it performs an insert.
insert into test_trgt (id) values (999)

One of the script files is used by the DAC on most tasks - customsql.xml. An example of the use of this file is the TRUNCATE statement that is run for a task. (On the task Edit page you have the choice of ‘Truncate Every Time’ and ‘Truncate on Full Load’)
The script looks like this


TRUNCATE TABLE %1


Where %1 is substituted for the table name(s) being truncated on that task.

Another useful script in customsql.xml is the Analyse statement


DBMS_STATS.GATHER_TABLE_STATS(ownname => '@TABLEOWNER', tabname => '%1', estimate_percent => 30, method_opt => 'FOR ALL COLUMNS SIZE AUTO',cascade => true )


This one is more detailed but should not be ignored in any Warehouse project. As an analyse runs every table (if you have set the correct parameters) then this job needs to run as quickly as possible. Look at some of the values e.g. estimate_percent => 30 part. You may want to adjust this!!


A custom warehouse

Now here’s the really useful bit.

So you have a new warehouse and you’ve been creating new tables (prefixed with WC_ of course), custom fields (prefixed X_ of course!) and new indexes. This work was done is a development database and needs to be populated into Test, Training, Production, Support, etc environments. You also have version 1, 1.1, 1.2, etc.

Take the DDL that manipulates the Database structure and place this into a sql script file in C:\SiebelAnalytics\DAC\CustomSQLs.
Create a task that runs the script.
Create an Execution Plan that has the above task as a precedent. I normally call this Execution Plan ‘Initial Warehouse Set-up’
You can now keep all of your environments up to date easily by distributing the latest version of the SQL script and running the execution plan.
Tip. Make sure that your SQL is very robust, i.e. checking for existing tables before trying to create them again.


DAC Attacked


There are rumours that the DAC is going to be replaced by Oracle with something else. Whatever they do I hope it has all the useful features of the current DAC, plus more.

Next topic – Should Oracle keep developing OWB or just try to buy Informatica??

Two weeks to go

With only two weeks to go we're getting very nervous.

This has nothing to do with UAT (Which is actually going very well), but all to do with this years skiing holiday!

We're off to La Plagne with the kids over xmas. There's a webcam service that was showing lots of grass last week and no snow. Happily there was a light downfall over the weekend, but the tempurature is due to rise so I bet it all melts away.

I'll be keeping my eye on the forecast below and praying for snow

Fingers crossed eh?

La Plagne Snow Forecast | Snow Report | by E-mail