Feeds:
Posts
Comments

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.

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 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. DTS-MSSQL
    2. SQL LOADER-ORACLE
  1. 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

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 …

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.