BI Strategy – Approach based on First Principles

Business Intelligence Strategy definition is typically the first step in an organization’s endeavor to implement BI (Business Intelligence). This phase is very crucial as the overall execution direction hinges on decisions taken in this stage.

The precise approach to the BI Strategy definition includes the following steps:

1. Business Area Identification - Identify and prioritize the business area(s) for which BI is considered. Ex: Human Resource Analytics, Supply Chain Analytics, Enterprise Performance Analytics etc.
2. Process Mapping Document - Once the business area is identified, map out the individual processes involved in that particular domain. This can be a simple flow-chart that shows the entry and exit criteria for each sub-process.
3. Business Questions Enumeration – Based on the subject areas involved in the business domain, enumerate the list of questions that are to be answered by the analytical layer.
4. Data Elements Segregation – For each of the process steps, identify the data elements. These data elements, after subsequent validation (in conjunction with business questions) would translate into dimensions and facts during the data modeling stage.
5. Data Visualization – Develop a prototype (set of screenshots) on how the data would be visualized for each business question. Business Analysts and domain experts are typically involved at this stage.
6. BI Architecture Synopsis – At a fundamental level, BI architecture is fairly straightforward. The architecture is almost always a combination of the following processes: Extraction (E), Transformation (T), Loading (L), Cubing (C), and Analyze (Z). The number of layers, type of reporting etc. are a combination of ETLCZ components. Ex: ETLZ, ETLTLCZ, ELTZ, ELCZ are some options for BI architecture definition.
7. Next Steps Document – The ‘Next Steps’ document would list down the other requirements of / from the analytical infrastructure. These can be points around Tool Evaluation, User profiles, Data volumes, Performance considerations, etc. Each of these requirements would translate to an assessment to be carried out before the actual construction begins.

The most common mistake is to start thinking about technology aspects before the actual business requirement is finalized. A precise definition of business questions goes a long way in designing a scalable and robust BI infrastructure. 

Read More about  BI Strategy

Issues and workarounds for the Siebel DAC compare and Merge strategy

The Siebel provided DAC compare and merge strategy is a nice tool that helps us identify differences between a source DAC repository and the target DAC repository. Also helps to figure out which objects are to be moved to the target.

But, there onwards the merge process is manual and a big waste of time. One cheat used by many administrators is to simply copy the repository from the lower environment to the higher environment. This works, but it has one major drawback. The last ETL process id is inherited from the lower environment.

The implication is that the run ids stored in the Run Stats table has no relation to the run number stored in the W_PARAM_G table and it becomes a major challenge to figure out which ETL run did what. Then one has to meddle with the DAC repository tables to get the last ETL process being stored in the DAC repository in sync with the numbers stored in W_PARAM_G. This is a risky proposition especially if one has not understood the relationships between the repository tables and the significance of all the long keys stored by Siebel in the repository to maintain the relationship between the tables.

So, here is a solution which we have tried out (with success) to move a DAC repository from a lower environment to a higher environment without making a hash of the run history.The basic idea here is to move only few selected tables from the source environment to the target instead of doing a complete repository overwrite.

The steps are as below:
Step 1: Take backup (Export) from Target DAC Repository make it separate folder(Just for Backup)
Step 2: Import Schema DAC repository into to Target DAC repository
Step 3: Export below mention Tables from Source DAC Repository. Make a separate folder which should contain below mention tables (20) only.

Sr. No Entity Main Table 1 Database Connections W_ETL_DBCONN 2 Database Indices W_ETL_DB_INDEX 3 Database Tables W_ETL_TABLE_DT 4 Execution Plan W_ETL_DEFN 5 Execution Plan—Database Connections W_ETL_DEFN_DB 6 Execution Plan—Subject Area W_ETL_DEFN_SA 7 Execution Plan—Pre-PostSteps W_ETL_DEFN_STEP 8 Group W_ETL_GROUP 9 Group Table W_ETL_GRP_TBL 10 Index Columns W_ETL_INDEX_COL 11 Indices W_ETL_INDEX 12 Informatica Folder W_ETL_FOLDER 13 Tables W_ETL_TABLE 14 Tasks W_ETL_STEP 15 Task Dependencies W_ETL_STEP_DEP 16 Task Phase W_ETL_PHASE 17 Task Tables W_ETL_STEP_TBL 18 Subject Area W_ETL_SA 19 Subject Area/Group W_ETL_SA_GROUP 20 System Properties W_ETL_SYSPROP

Step 4: Import this tables into Target DAC Repository.
Step 5: After importing we need to make some changes in the Setup tab, Database connection and informatica server setup in Target DAC Repository.
Step 6: Refresh all dates (for full load)
The steps to refresh are: Tools–>ETL Management–> Reset Data Warehouse
Step 7: Before running Full Load truncate below mentioned tables, please take backup before truncating.

Tables:

1. S_ETL_RUN from OLTP
2. W_ETL_RUN_S from OLAP

Any better ideas out there?

Inputs from Raghunatha Yadav & Sanjay Rao

you can read it More SiebelDAC

Introducing Siebel Street

I would like to welcome you to Hexaware’s Siebel blog– “Siebel Street”. Hexaware’s Siebel practice has been doing wonderful things and we would like to share some of them with you through Siebel Street. In this first post I will introduce the Siebel CRM and describe its capabilities. I will end with giving a sneek peek at some of the forthcoming posts in this blog.

Siebel has been the market leader in the CRM space for more than a decade. It started with a sales-force automation software and then expanded into marketing and customer service applications. It was taken over by Oracle in 2005. Today, Siebel CRM is at the forefront of Oracle’s CRM product strategy. Siebel’s CRM Suite caters to clients in the BFSI, Telecom, Pharmaceuticals , Retail , HI-tech Manufacturing and Transportation verticals among others. Siebel’s Technical architecture provides a top-notch user interface, strong integration, diversified industry-wide applications, easy upgrades and reliable customer support.

Most customers need to change the Siebel application by modifying delivered functionality or by implementing new modules. The customizations in the Siebel applications are implemented using Configuration, Scripting and Workflows. Siebel also provides excellent data management capabilities. Siebel EAI integrates third party applications/objects with Siebel and Siebel EIM populates external data in interface tables and user data in base tables. Siebel Analytics is the business Intelligence application from Siebel Systems. It has been rebranded as OBIEE and will form the centerpiece of Oracle’s BI Strategy.

In Siebel Street, contributors from Hexaware’s Siebel practice will blog about their experience with Siebel CRM and Siebel Analytics/ OBI-EE. We will describe the business and technical challenges, their resolutions and the benefits of our approach. We are working on the entire range of tools available on Siebel. Our team consists of experts in EIM, Config, Integration, ETL, Analytics, Administration and Testing. We will share our project experiences in these areas in the forthcoming posts.

In this month we will post on DAC, escripting etc. Watch this space..

Read More  about  Siebel Street

Improvement in DAC 7.9 – Subject Area Assembly

One of the nice new features in DAC 7.9 is the facility to assemble all the ETLs required to populate a fact using the Assemble button

In the Pre DAC 7.9 days, the process for building a star schema was as below

1. Think up all the tasks required to populate a star schemas (Basically the Informatica workflows)
2. Create the tasks and Identify Dependent and Depending on tables manually when creating a task.
3. Construct a Table Group where one had to figure out all tables that belong to the star schema i.e. the fact and related dimensions
4. And then at last build the Subject Area
5. Run the subject area and then curse yourself for not remembering some tables.
6. Start all over again.

Now

1. You don’t have to identify Dependent and Depending on tables. There is a sync feature which automatically identifies all the sources and targets which are affected/dependent on a task
2. The concept of a Table Group is dead. You just have to name the fact to be populated and click on the Assemble button
3. Click option: selected task only (unless you want to assemble for all subject areas delivered out of the box. Trust me : you don’t want to do this)
4. The DAC assembles the selected subject area by determining what dimensions and other related tables are required, and what tasks are needed to load these tables.
5. You will receive an informational message when the assemble process is comp
6. Hey presto, the Subject Area is ready

But wait a minute,

1. You still have to do some cleanup and remove unwanted tasks
2. Click the Tasks tab to view which tasks the DAC has determined are required for this subject area
3. Tasks that are automatically assigned to the subject area by the DAC are indicated with the Auto generated check mark.
4. You can inactivate a task from participating in the subject area by selecting the Inactive check box.
5. When the Inactive check box is selected, the task remains inactive even if you reassemble the subject area.
6. You can also remove a task from the subject area using the Add/Remove command, but when you remove a task it is only removed from the subject area until you reassemble the subject area
7. You still have to put in the implicit dependencies i.e. dependencies on tables that are not featured in the select statements or the workflow
8. Upgrade does not work for custom workflows setup in DAC

There’s still more

1. Unless you have a lot of time on your hands, don’t try clicking on the Assemble tab when DAC server has been up for some time
2. Stop the server, restart the client and then click on Assemble
3. It is much quicker. But don’t know why it works this way

So in conclusion Oracle DAC 7.9 is great and has some nice new features. But upgrading is going to be no fun. More on this later.

–Posted by Raghunatha Yadav/Sanjay Rao

Read More about  DAC 7.9

Visibility Enablement for Express Mail/Cargo industry

Value added services hold the key to differentiate one service provider from the other in the increasing competitive express mail/cargo market. Near real time visibility of shipments and proactive exception management are essential for improving the customer experience and operational efficiency.

However, there are significant challenges in providing these value added services – large number of players, huge volume of shipments, intermediate manual operations and lack of electronic data exchange. In addition, the current technology infrastructure has limitations with respect to providing new features like messaging, e-tracking, m-tracking, proactive alert generation as well as interfacing with multiple devices for recording shipments.

Since a large number of players and platforms/technologies are involved, messaging holds the key to providing seamless connectivity required for real-time visibility enablement. At each check-point, the various parties who have subscribed to the specific checkpoint, would receive messages indicating real-time status of the shipment. Messaging would connect different systems with minimal changes to existing applications and system interfaces. In addition, it will enable workforces to become increasingly mobile and facilitate rapid response to shipment related issues.

Read More about  Point to Point

Limitation of Scripting in Siebel

Siebel CRM is one of the most effective CRM solution available till date. Clients with complex business requirements opt for Siebel to help themselves in identifying, acquiring and retaining their respective Customers. As a Siebel Professional we use Configuration, Scripting and EAI in order to build an Application.

There are nearly 200 different types of objects that can be configured in Siebel to reflect the changes in the User Interface Layer, Business Objects Layer, and Data Objects Layer. Also, the Siebel objects have intertwined multiple relationships between them.

We need to write programs in case of complex requirements (According to Siebel best practices — scripting should be the last resort in situations where only configuration cannot accomplish the business and technical requirements).

There are primarily 4 programming languages used when customizing Siebel.
Siebel Visual Basic (Siebel VB), Siebel eScript, JavaScript (available as of Siebel 7) and Java (through Java Business Services).

But there are some limitations of scripting in Siebel which are cited below:

• It’s case sensitive.
• Siebel eScript code is executed one statement at a time, in the order in which it is read.
• If objects are not instantiated properly then it can lead to Memory leakages.
• Need to take extra care while declaring the variables at application level.
• Difficult to maintain the code.

Some of the biggest demerits of Scripting in Siebel are:

1. May not be fully upgradable. In Siebel upgrades, scripts undergo extensive retrofitting
2. 
   
3. One object can be scripted in only one programming language, i.e. if you are writing a BS in eScript and want to access some COM / ActiveX object then in most of the cases you need to switch on to a different Object which is written in VBscript. If the facility to use many scripting language in an Object has been given then one do not require to switch in between objects.
4. 
   
5. In one method of an Object one can write only 16KB of code, which some times becomes critical especially when one wants to modify some existing functionality.
6. 
   
7. If the script is written in Browser side then one can easily call Server side script and get it executed, but the vice-versa is not true.
8. 
   

There are numerous benefits of scripting in Siebel in comparison to its miniscule limitations. However it is very vital that one is aware of the limitations too.

Please feel free to Read More : Scripting in Siebel

PeopleSoft Fine Grained Auditing – Part I

We all know how critical it is to enable Oracle Database Auditing for our production environment. It is equally important to monitor the audit results and take actions. Though enabling auditing using the AUDIT_TRAIL initialization parameter plus using the AUDIT statements to enable different auditing options is the common approach, with the availability of FGA feature, it is time to take the next step.
FGA allows us to audit more specific business rules. Today, I will walk you through the steps to implement FGA for PeopleSoft.

Ensure that EnableDBMonitoring is set to 1 in psappsrv.cfg. This will enable PeopleSoft to populate CLIENT_INFO column in V$SESSION.

Identify the table and the criteria that we need to set for the policy. In this example, I will use the custom table PS_ABC_COMPANY_TBL. I need to audit any SELECT* statements on PS_ABC_COMPANY_TBL when user selects data related to
abc_company = ‘ABC Confidential’

We need to create a procedure that will populate the CLIENT_INFO so that we can identify the OPRID.

CREATE OR REPLACE PROCEDURE GET_OPRID (OBJECT_SCHEMA VARCHAR2, OBJECT_NAME VARCHAR2, POLICY_NAME VARCHAR2)

AS

V_CLIENT_INFO VARCHAR2(1000);

V_OPRID VARCHAR2(32);

BEGIN

V_CLIENT_INFO := SYS_CONTEXT(‘USERENV’,'CLIENT_INFO’);

IF ( LENGTH(V_CLIENT_INFO) IS NULL ) THEN

V_OPRID := ‘NOOPRID’;

ELSIF ( SUBSTR(V_CLIENT_INFO,1,1) = ‘,’ ) THEN

V_OPRID := ‘NOOPRID’;

ELSE

V_OPRID := SUBSTR(V_CLIENT_INFO, 1, INSTR(V_CLIENT_INFO,’,',1)-1);

END IF;

DBMS_SESSION.SET_IDENTIFIER (V_OPRID);

END;

Create a policy as shown below

begin

dbms_fga.add_policy (

object_schema=>’SYSADM’,

object_name=>’PS_ABC_COMPANY_TBL’,

policy_name=>’ABC_COMPANY_TBL_ACCESS’,

audit_column => ‘ABC_COMPANY’,

audit_condition => ‘ABC_COMPANY = ”ABC Confidential”’,

handler_module => ‘GET_OPRID’

);

end;

That’s it!!

In my next post I will share the results of enabling this feature.

 

Read More About PeopleSoft Fine Grained Auditing

Unleashing the Giant – Siebel CRM

The first “C” program that I wrote was about printing “Hello World”. On synonymous lines to my first “C” program, while blogging the first post I would like say “Welcome and Explore the Siebel Street”. Siebel has been the most active promoter of CRM for more than a decade. How many customers have been better served because Tom Siebel decided to help popularize Customer Relationship Management? The answer is innumerable.

Siebel CRM has been consistent market leader in CRM software arena. The best in-class customization, top-notch user interface, strong integration tools, diversified industry-wide applications, easy upgrades and reliable customer support are few key attributes of this Giant – Siebel CRM. Siebel CRM caters to its clients in various verticals like Finance, Pharma, Telecom, Hi-tech manufacturing, Insurance, Sales, Marketing etc.

The customizations in the Siebel applications are implemented using Configuration, Scripting and Workflows. Siebel EAI integrates third party applications/objects with Siebel and Siebel EIM populates external data in interface tables and user data in base tables. Siebel Analytics furnishes the needs of Report generation, analyzing of business trends, logistics etc. This Giant CRM’s robustness lies in the sheer depth and breadth of its functionality. Siebel products are continuously rated at the apex in sales, marketing, customer service and other CRM peripheral areas.

Post-Oracle’s big ticket acquisition Siebel CRM has been the lustrous feather in the cap of Oracle. Oracle already has PeopleSoft, JD Edwards, Oracle Apps under its belt and now the bellwether – Siebel CRM. With so-many CRM solution providers under its kitty, Oracle plans to roll-out Oracle-Fusion in near future.

Moving forward, Oracle plans to make Siebel as the center piece of its CRM product, while bringing together the functionality of all its acquisitions under Fusion. So if I was to choose between a newcomer, a struggling cameo or a strong veteran with no signs of fading off, the choice would be clear – Siebel CRM.

Watch out this space for more on various Siebel CRM topics, techno-functional issues, problems-resolutions, experiences etc.