Working with Excel data source

Oracle BI Server can access data stored in

Normalized schema databases
Star or snowflake schema databases
Flat files
Excel
XML etc.

Today I am gonna tell you how to use excel data source in OBIEE.

1. Create a new excel file.

2. Each sheet present in an excel file will represent a table.

3. Select the range (i.e. rows and columns) and give the name of the table as shown in figure.

 

or you can go to Formulas>Define Name and give the scope and range.

4. The above figure shows the EmpTable and DepTable. These tables are present in separate sheet of same excel file.

5. Now create a System DSN for Excel data source. Select Driver to Microsoft Excel from the list of drivers.

6. Give the name of DSN, select the workbook i.e. your excel file and click on Ok.

7. Go to Administration tools and click on File>Import from Database. Select the DSN which you created for Excel.

8. When you click on Import, it will import all the tables present in that excel file.

9. Once you get the tables in physical layer, you can play around with those tables to create subject area.

How to Find Index For a table – Indexing

Got struck with one of the query and need to explore indexes for a resolution.

It is not unusual to wonder what indexes a specific table might have, and what columns in the table are assigned to those indexes. The following query will provide this information to you. It draws on the information in the dba_ind_columns data dictionary view:

SQL> column table_owner format a15

SQL> column table_name format a20

SQL> column index_name format a20

SQL> column column_name format a20

SQL>

Select owner, table_name, index_name, column_name

2  FROM dba_ind_columns

3  Order by owner, table_name, column_position

4  Where owner=’SCOTT’

5  AND table_name=’EMP’;

You will get the output like this !

TABLE_OWNER     TABLE_NAME           INDEX_NAME           COLUMN_NAME

————— ——————– ——————– ———-

SCOTT           EMP                  PK_EMP               EMPNO

In this example we find that the EMP table in the SCOTT schema has one index called PK_EMP. This index is built on a single column, EMPNO.

Siebel EIM – How to create IFB file

Siebel Enterprise Integration Managers is used to populate, update or delete data in Siebel database by using EIM tables.

To use the EIM engine, we need to populate the EIM tables, either from legacy database to populate or update or from Siebel base tables themselves to delete.

We invoke the EIM tool by giving a text file as a parameter. the text file (.ifb extension) indicate the EIM table used and the base tables to populate, update or delete from.

Points to remember in EIM

What is EIM?

Bidirectional exchange of data between non siebel database and siebel database. Siebel Enterprise Integration Manager (EIM) is a server component in the Siebel eAI component group that transfers data between the Siebel database and other corporate data sources. This exchange of information is accomplished through intermediary tables called EIM tables.The EIM tables act as a staging area between the Siebel application database and other data sources.

Functions of EIM?

• Export
• Import
• Delete
• Merge

Delete

There are a number of ways of deleting in EIM

DELETE EXACT

Deletes rows from the base table where the keys match those previously populates in the EIM table (remember to set CLEAR INTERFACE TABLES = FALSE)

[Siebel Interface Manager] UPDATE STATISTICS = TRUE USING SYNONYMS = FALSE PROCESS = Clear Subscriptions

[Clear Subscriptions] TYPE = SHELL INCLUDE = “Delete Subscriptions”
[Delete Subscriptions] TYPE = DELETE BATCH = $myBatch TABLE = EIM_QUOTE USE INDEX HINTS = TRUE CASCADE DELETE ONLY = TRUE CLEAR INTERFACE TABLE = FALSE ONLY BASE TABLES = S_DOC_QUOTE DELETE EXACT=TRUE

DELETE MATCHES

DELETE MATCHES is used with a WHERE Clause where you would delete only selected records by filtering out the records in the table.

EIM Process

EIM is two step process.

1. Loading legacy data to interface tables.
2. Loading siebel base table from interface table thru EIM task.

Loading legacy data to interface tables

1. Legacy data-any text file or excel file (data mapping)
2. Convert the data to load into i/f table into above file format

use sql loader or any other tool based on your database to load into i/f table

1. 1. DTS-MSSQL
   2. SQL LOADER-ORACLE
2. Do the appropriate mapping in excel sheet b/w columns in i/f table and columns in Siebel base tables.

Loading siebel base table from interface table thru EIM task

1. Use the EIM task to run the second step
2. Create ifb file as per your requirement from already existing default.ifb
3. IFB FILE has two parts
4. Header-credentials
5. Process-name of process (given by your own)
6. Description of process
   1. [procees name]
   2. TYPE
   3. BATCH NUMBER
   4. TABLE
   5. ONLY BASE TABLES and ONLY BASE COLUMNS
7. Save it and provide as one job parameter during EIM task. I think you know other parameter like trace flag, error flag, etc. Then run the task.
8. EIM errors
   1. Ambiguous-now row id is provided
   2. Duplicate-with same row id
   3. Foreign key mapping-failed to map
   4. Required columns-failed to load some required columns
   5. Picklist-fail to load the values given in picklist

Siebel EIM – Introduction to SSIS

Let me start with an era when we use to import or export data with DTS import/ Export tool or Microsoft Data transoformation services tool. DTS is nothing but an ETL tool (Extract, Transforms and Load), Which allows data base administrator to createset of  stepsthat describe that how data to be transformed.

These “packages,” as they’re called, can then be executed via a number of different programming languages or directly within SQL Server itself.

The idea was and still is a good one, but ultimately too limited — it remained a glorified if convenient version of the bulk-copy system used in earlier versions of SQL Server.

With SQL Server 2005, Microsoft decided to turn DTS into a whole new kind of ETL tool — SQL Server Integration Services (SSIS). Moving data from one database to another or exporting a database in another format are only the most basic things you can do with SSIS.

Why should i use SSIS or what are the things that will be helpful while using SSIS ? –> Good question

Let me try –> in next post …

How to get started with Siebel

Even though we have a local Siebel environment in our own machines, it is very important to sync up with the existing server environment. There are multiple people working on one project or on one Siebel server instance. Each one configures his portion of the project and then checks-in onto the server. So when a person joins a project and before s/he starts to work, we have to adapt to the environment. This process is explained under “Generate Database”, “DB Extract” and “Get Process”. These are normally a one time process but could be done multiple times if desired. Also note that this process is done on Development Environment.

After we adapt to the environment and before we start to make any changes, we “Check Out” projects to avoid multiple developers working on the same project.

After Check-Out we do the actual configuration. Configuration can be anything small like adding a pick list or big like adding a new screen with complex functionality in it. More information on actual development is explained under Configuration section.

After necessary changes are made, we compile the object definitions into the SRF file and test. If need go back and make changes compile again to test. This process is repeated until the desired result is achieved.

After the changes have been tested and the result is positive we check-in this onto Siebel server. This will now have object definitions both locally and in Siebel server. So now your changes have to be made available for the end users. For this we compile the server object definitions and deploy the SRF.

All the above process is explained in detail in the following tutorials.

Siebel Overview

Hopefully the previous topics have given you a little idea about what is Seibel CRM and how it can help a company to be effective.

We also learnt how to install Siebel and what the different entities are that Siebel application talks. Below is the summary. Pictorial representation of Siebel functions.

• Configuration File: It is normal text file with a file extension of cfg. It contains all the parameters that Siebel uses during startup. It is a place holder for parameters.
• Siebel Client: This is the User Interface of Siebel to which the employee or the customer access. There are different types of clients
• Siebel Tools: It is an Interface or development environment used to configure Siebel Application. This is used by Seibel Developers.
• Siebel Repository: This is a subset of tables in the Siebel Database which stores all the object definition or configuration details including scripts.
• Siebel SRF: Also called as Siebel Repository File (SRF) is a file with extension srf and is the compiled version of Siebel Repository. In short it is a compiled file.

Below is the visual representation of how different components connect.

Next we will see how to get started on developing Siebel application.

Siebel SRF

Siebel SRF file also known as Siebel Repository File (SRF) is a compressed, read only file that contains compiled version of Siebel repository. So all the object definitions and scripts that are defined in Siebel Tools are compiled into one single file which is will be used by Siebel Application at runtime.

This SRF file is normally found under “[install directory]\Siebel\7.7\web client\OBJECTS\ENU” with the file extension srf.

It is very important that the active Siebel Repository should always be in sync with the SRF otherwise you could see weird problems. So we will have to recompile every time an object definitions is changed in Tools.

To compile, in Siebel Tools go to Tools => Compile. Here you will have the option of selecting individual projects, multiple projects or you could do a full compile by selecting all projects.

There is also an option to choose the SRF you want to compile to by browsing for the SRF as shown the screenshot below.

So select the projects and the SRF file then hit compile.

Siebel Repository

Siebel Repository refers to a subset of tables in which Siebel objects definitions and scripts are stored. These tables are Siebel database tables and contain all the configuration information that is shown in Siebel Tools. So Siebel Tools helps as a user interface to look at the object definitions stored in the database. All the object definitions are modified or added using Tools.

Note that, all the initial Siebel repository definitions that you would see in Tools get populated during the installation.

Just to illustrate that these object definitions are stored in the database, we can run a simple query on the Siebel database. (You can run this on Local DB also. See how to connect to Local Database)

Example:
select * from siebel.S_BUSCOMP; — This lists all the business components that are defined in Tools.

select * from siebel.S_APPLET; — This lists all the applets that are defined in Tools.

Select * from siebel.S_PROJECT; –Lists all the Projects defined in Tools.

Note that, all the initial Siebel repository definitions that you would see in Tools get populated during the installation.

Multiple repositories can exists in Siebel but only one can be active at a time. In Siebel Tools if you select the object type “Repository” on the object explorer, you could see multiple repositories listed but only one of them will be active.

Tip: If you are not able to see the object type “Repository” in Siebel Tools, then go to View => Options => Object explorer and select the object type “Repository” to show up in Siebel Tools explorer.

Importing and exporting of repositories is done using the Database Configuration Utility. Multiple repositories are created using this Database Configuration Utility while either backing up or importing from other environments. Example: Moving all the repository definitions from Development environment to Testing Environment.

Now that we know where these object definitions are stored, we will next learn where these object definitions are compiled and how it gets used by Siebel Application.

Siebel Tools

Siebel Tools is an integrated environment for configuring the Siebel Application used by Siebel developer. It is a powerful application development environment used to configure to meet the business requirements.

Siebel Tools is not a programming environment like Visual Studio. Rather it is an interface primarily used to create or modify object definitions.

Below is the snapshot of Siebel Tools.

The left pane is called the Object Explorer. This displays all the object types like Business Components, Applets, Screens and Workflows. In other words it is the grouping of object definitions. This Object Explorer consists of hierarchical tree structure user interface displaying child object types.

The Right pane is called the object list editor window. This lists all the object definitions like Tables, Business Components and Applets. On navigating on the hierarchy it would also lists the child object type definitions.

Next we will see where these object definations are stored.

Siebel Client

Siebel Client is the User Interface of Siebel for users. In other words it is the Siebel software users use to access Siebel Database.

There are three types of Siebel Clients

• Mobile Client
• Dedicated or Thick Client
• Web or Thin Client

Mobile Client:
They have Siebel Client Software installed locally, Have their own database, Have their own File System and directly access and modify their own database. This mobile client periodically connects to their designated Siebel servers for updating the server database. It meets the need of Field professionals who do not need to have continuous access to the Siebel Network
Example: any HandHeld device in which siebel is installed.

Dedicated or Thick Client:
They have their Siebel Client Software installed locally but however they are always connected to the Siebel Server. They do not have their own database or Siebel File System. All changes that are done locally are immediately reflected on the server.
Example: Siebel Developers Machine

Web or Thin Client:
Siebel Web Client does not need Siebel Client Software installed nor it has its own database. Web client users access Siebel via the web browser like Internet Explorer. Web clients are given a specific URL to access Siebel. All transactions are committed immediately. Web clients are served with the help of Siebel Web Servers.
Example: Typical end user having access to the URL with login and password accessing through Internet Explorer