Siebel Tech - Home of MSMQ Explorer Tool — Proof that there is... Life After Siebel

Loading Siebel Sample Data into Oracle Server

July 15, 2016 by Oli 2 Comments

Oracle XE is a a bit of beast, a pain in the proverbial to install and overkill for the purpose of a local Siebel database. However, it is at least an Oracle database and not a Sybase database, as it was in days gone by.

What this means is that it is possible to use Oracle tools to access and manipulate the data. Gone are the days of disql and it’s more than limited functionality. We can now access the local and sample databases using SQL Developer and other Oracle tools, such as import / export and data pump. I used this fact to my advantage, recently, to populate a fresh Siebel 16 server installation with demo data. This lets me use the full gamut of thin client apps, with useful data and users.

Terry Smythe’s Accounts!

Install the Sample DB

First up, we need to install the XE sample using the Tools or Client installers. All straight forward (as of 16.2, anyway) and you’ll be left with an XE Service that’s running and listening on localhost, port 1521.

Reset the System Password

We now need to get access to the system account, so that we can mess around. Bring up a command prompt and enter:

set ORACLE_SID=SAMPLE_XE
sqlplus /nolog
connect / as sysdba
alter user system identified by system;
quit

set ORACLE_SID=SAMPLE_XE

sqlplus /nolog

connect / as sysdba

alter user system identified by system;

quit

This will reset the “system” password. Note that SADMIN and SIEBEL use the equivalent value for logging in.

Export the XE Sample Data

You can use Oracle Data Pump (impdp / expdp) to do this, but I found it to be a bit of a hassle – my target is an RDS instance on Amazon, so I’d have to push a load of files up in order for data pump to suck them in. As it is, I used the old fashion import / export (imp / exp) commands in XE to do the job.

First up, we don’t want all the data from sample – we really only want “S_” tables and we want to exclude the Repository tables. As such, I selected out the tables I wanted directly from S_TABLE:

SELECT NAME FROM SIEBEL.S_TABLE
WHERE TYPE <> 'Repository'
AND NAME LIKE 'S_%'
ORDER BY NAME ASC

SELECT NAME FROM SIEBEL.S_TABLE

WHERE TYPE <> 'Repository'

AND NAME LIKE 'S_%'

ORDER BY NAME ASC

I then saved this to a text file (tables.par) and modified this to work as a parameter file input into the “exp” command:

TABLES=S_ACCNTRT,
S_ACCNTRT_ACCNT,
S_ACCNT_AGR_CUT,
S_ACCNT_ATT,
...

TABLES=S_ACCNTRT,

S_ACCNTRT_ACCNT,

S_ACCNT_AGR_CUT,

S_ACCNT_ATT,

...

I then initiated an export from my XE database:

exp SIEBEL/SIEBEL BUFFER=16384 FILE=C:\Oracle\Exp\Sample.dmp GRANTS=N TRIGGERS=N CONSTRAINTS=N STATISTICS=NONE PARFILE=tables.par

1	exp SIEBEL/SIEBEL BUFFER=16384 FILE=C:\Oracle\Exp\Sample.dmp GRANTS=N TRIGGERS=N CONSTRAINTS=N STATISTICS=NONE PARFILE=tables.par

This took roughly 15 minutes to complete and I’m left with a “.dmp” file of around 4GB in size.

Import the Sample Data into Oracle Server

You can probably guess the next step – using the “imp” command to import the data into my Server Oracle instance. The process is much the same as for export – the main point of note here is that we want to explicitly ignore errors. Even though we’re pushing sample data into a “clean” Siebel Database, there’s still a load of seed data in there that we don’t want duplicated. By ignoring errors, the indexes will keep duplicates out while allowing the whole process to complete without terminating:

imp SIEBEL/SIEBEL@TARGET FROMUSER=SIEBEL TOUSER=SIEBEL BUFFER=16384 FILE=C:\Oracle\Exp\Sample.dmp IGNORE=Y PARFILE=tables.par

1	imp SIEBEL/SIEBEL@TARGET FROMUSER=SIEBEL TOUSER=SIEBEL BUFFER=16384 FILE=C:\Oracle\Exp\Sample.dmp IGNORE=Y PARFILE=tables.par

Create our test users

Now that we have all the sample data in the server DB, we must create some users to match the thousand or so S_USER records that have been created for our use across the sample system. Again, I put Oracle’s own tools to good use and created a SQL script in SQL Developer:

SELECT 'GRANT CONNECT TO ' || LOGIN || 'IDENTIFIED BY ' || LOGIN || '; ' || 'GRANT SSE_ROLE TO ' || LOGIN
FROM SIEBEL.S_USER
WHERE LOGIN NOT IN ('SADMIN', 'SIEBEL', 'GUESTCST', 'GUESTERP', 'LDAPUSER')

SELECT 'GRANT CONNECT TO ' || LOGIN || 'IDENTIFIED BY ' || LOGIN || '; ' || 'GRANT SSE_ROLE TO ' || LOGIN

FROM SIEBEL.S_USER

WHERE LOGIN NOT IN ('SADMIN', 'SIEBEL', 'GUESTCST', 'GUESTERP', 'LDAPUSER')

Execute this script and users will be created. Be careful to exclude any users you don’t want to affect (SIEBEL and SADMIN being the main ones) as the script will reset their passwords. You can find a list of relevant test users for each Siebel vertical in this Bookshelf Guide.

Login as Casey Cheng!

It’s what you’ve always wanted, right? Fire up Siebel Call Center and login as CCHENG. Ah, memories!

Now sit back and enjoy perusing the sample data from the comfort of your full Siebel installation.

Installing Siebel 16 on AWS

July 13, 2016 by Oli 2 Comments

Well, it was only a matter of time before I ended up working with Siebel again!

I’ve been introduced recently to Amazon Web Services – AWS to you and me. So this is what Cloud Computing is all about! I’m utterly gob-smacked by the power of this platform, the stuff you can do and the speed at which you can do it.

I was tasked with building a Siebel 16 installation using AWS and it is amazingly straight forward. Not to mention incredibly cost effective, compared to using physical hardware and on-premise virtualisation.

I thought I’d share with you the process of spinning Siebel up “in the Cloud”!

First up, my architecture involves two AWS instances – an RDS instance for the database and an EC2 instance for the Application and Web Servers. Using RDS, I’m able to spin up an Oracle Enterprise Edition 12c database in less than 10 minutes. It’s literally a few clicks and you’re done – no more slaving away with OUI, downloading files and the installation and configuration process. AWS captures parameters from simple web pages and does the rest – lightening fast. AWS will even keep my Oracle installation up to date, automatically applying minor patch updates during a maintenance windows that I can define.

SiebelDBAWS

So, I have a database instance up and running. I then spin up a new Windows 2012 R2 server. Again, the process is ludicrously quick. I choose from a number of types that have varying RAM, CPU and bandwidth allocations, allocate C and D drives with 60 and 120GB respectively and click a button. Less than 10 minutes later, I have an RDP file to download to my laptop that allows me to connect to the new server. I then jump on to Oracle eDelivery and download the Siebel installation media – I’m getting speeds of nearly 70MBs (that’s mega BYTES, not BITS!) and download the full Siebel installation in under 15 minutes. I unzip the media and backup the installation files to Amazon’s storage offering, S3.

SiebelE2App

I download and install a Java SDK, Oracle 12c 32-bit Client and SQL Developer then set up my TNSNAMES. I then run SNIC to extract everything and prepare my Siebel installation media and kick off my Siebel installation. Within another couple of hours I have a fully functioning Siebel environment.

While this is all pretty much “installing Siebel on a remote server”, the real benefit comes of now saving my Siebel Server installed as an Amazon Machine Image (AMI). This will allow me to VERY quickly spin up new Siebel 16 server environments for whatever I need – training, dev environments, the list is endless.

I’ve always been a bit of a cynic when it comes to “Cloud” computing but AWS has got me converted. This stuff is HUGE and it is AWESOME – with this stuff, server rooms are sure to become a thing of the past.

Box Authentication in Python

June 30, 2016 by Oli 4 Comments

I’ve been working on orchestrating an ETL load process from text files stored in the Cloud based file management tool, Box. Working with Python for the first time, I’ve found it to be an extremely flexible and straightforward language to use for such a task. There’s even a Box SDK that provides direct access to the bulk of the services needed to manage files in Box.

However, the Box API uses OAuth2.0 for authentication. Part of the process for permitting access to the Box API is to present the user with a web page, requesting permission to access the Box account. This does not fit well with an automated orchestration process.

I’ve written a small Python script that uses the keyring Python module as secure storage for the authorisation and refresh tokens required to connect to Box. Using the Box SDK, the tokens are refreshed automatically when they expire and you’ll see in the code below how I leverage the Box SDK to retain the tokens indefinitely in the secure key store. After “priming” the store with an initial set of tokens, the script will maintain the tokens automatically, provided it runs within the 60 day periods before the refresh token expires. Note that you’ll need to install the Box SDK and keyring Python modules, using PIP, prior to running the script. You’ll also need to specify the client_id and client_secret values from your Box Developer App in the script below.

"""An example of Box authentication with external store"""

import keyring
from boxsdk import OAuth2
from boxsdk import Client

CLIENT_ID = 'specify your Box client_id here'
CLIENT_SECRET = 'specify your Box client_secret here'


def read_tokens():
    """Reads authorisation tokens from keyring"""
    # Use keyring to read the tokens
    auth_token = keyring.get_password('Box_Auth', 'mybox@box.com')
    refresh_token = keyring.get_password('Box_Refresh', 'mybox@box.com')
    return auth_token, refresh_token


def store_tokens(access_token, refresh_token):
    """Callback function when Box SDK refreshes tokens"""
    # Use keyring to store the tokens
    keyring.set_password('Box_Auth', 'mybox@box.com', access_token)
    keyring.set_password('Box_Refresh', 'mybox@box.com', refresh_token)


def main():
    """Authentication against Box Example"""

    # Retrieve tokens from secure store
    access_token, refresh_token = read_tokens()

    # Set up authorisation using the tokens we've retrieved
    oauth = OAuth2(
        client_id=CLIENT_ID,
        client_secret=CLIENT_SECRET,
        access_token=access_token,
        refresh_token=refresh_token,
        store_tokens=store_tokens,
    )

    # Create the SDK client
    client = Client(oauth)
    # Get current user details and display
    current_user = client.user(user_id='me').get()
    print('Box User:', current_user.name)

if __name__ == '__main__':
    main()

"""An example of Box authentication with external store"""

import keyring

from boxsdk import OAuth2

from boxsdk import Client

CLIENT_ID = 'specify your Box client_id here'

CLIENT_SECRET = 'specify your Box client_secret here'

def read_tokens():

"""Reads authorisation tokens from keyring"""

# Use keyring to read the tokens

auth_token = keyring.get_password('Box_Auth', 'mybox@box.com')

refresh_token = keyring.get_password('Box_Refresh', 'mybox@box.com')

return auth_token, refresh_token

def store_tokens(access_token, refresh_token):

"""Callback function when Box SDK refreshes tokens"""

# Use keyring to store the tokens

keyring.set_password('Box_Auth', 'mybox@box.com', access_token)

keyring.set_password('Box_Refresh', 'mybox@box.com', refresh_token)

def main():

"""Authentication against Box Example"""

# Retrieve tokens from secure store

access_token, refresh_token = read_tokens()

# Set up authorisation using the tokens we've retrieved

oauth = OAuth2(

client_id=CLIENT_ID,

client_secret=CLIENT_SECRET,

access_token=access_token,

refresh_token=refresh_token,

store_tokens=store_tokens,

)

# Create the SDK client

client = Client(oauth)

# Get current user details and display

current_user = client.user(user_id='me').get()

print('Box User:', current_user.name)

if __name__ == '__main__':

main()

The key here, if you’ll pardon the pun, is in the store_tokens function. This is invoked automatically by the Box SDK, whenever it detects the expiration of the access token. The SDK will automatically request new tokens, using the active refresh token, and pass those two new token values back to the pass back function defined in the OAuth2 object initialisation. In the example above, we use the keyring module to store the values securely with the Windows Credential Manager. Before invoking any methods, we read these values and use them in the authentication constructor. Great stuff!

I’m back, baby!

June 30, 2016 by Oli Leave a Comment

Having spent the last month away from Siebel, I’ve missed posting about the cool software that I work with day to day. I’ve moved on with a very different focus, mainly around Business Intelligence and Cloud Computing. I’m having a lot of fun with Birst at the moment (check it out – the company was founded by ex-Siebel Analytics leaders) and also developing Enterprise orchestration tools in Python.

BackBaby

Over the coming months and years, I’ll be posting some tips, tricks and reviews of some of the amazing products that are out there. To me, Siebel was a cornerstone in the history of Sales Force Automation and the automation of business processes the world over, across every industry imaginable. My experience with it, and the processes that feed it, will stay with me for the rest of my career and continue to help me focus on delivering excellent solutions to customers.

So, I can officially confirm that there is… Life After Siebel!

Farewell and Good Luck!

June 16, 2016 by Oli 4 Comments

As they say in Scotland, “tattie bye!”

Scottish_wave

Siebel Tools has finally driven me to the point of no return and I have said farewell to the world of Siebel. It’s been fun and I really hope Oracle make something of the product – it would be such a shame to waste a couple of decades of blood, sweat, love and toil.

Wishing you all the very best of luck in the future!

Oli