RSS
 

Archive for the ‘Data Warehousing’ Category

IPC Tips #2: Duplicated Integration Service in Workflow Monitor

22 Sep

In the last months I have been working for a customer with a huge ETL logic built with Informatica Power Center.

Many times I had to import/export the repository in different environments and I faced the following issue at least once: after an import action I have found a duplicated Integration Service entry. One Integration Service was working and the other one was invalid.

Googling around for the solution, I have found useful the following information.

As first action, try to truncate the  repository log files by running the following command:

pmrep truncatelog -t all

If this doesn’t fix the problem, follow these steps:

1. Check the OPB_SERVER_INFO Informatica repository database schema table for any duplicated rows. If any, execute the following sql command:

DELETE FROM OPB_SERVER_INFO WHERE SERVER_ID = [ID];
COMMIT;

Where [ID] identifies the old Integration Service that has been duplicated.

2. In Workflow Manager, right click on the invalid Integration Service and then delete.

3. Restart the Informatica services.

 
1 Comment

Posted in Data Warehousing

 

IPC Tips #1 – ORA-01007 while executing an Informatica workflow

07 Sep

In this brief post I will describe what looks to be one of the most common problems you may face while executing Informatica Power Center’s workflows.

I have got exactly this problem while working for a customer and it took me long to figure out the reason.

Usually an Informatica mapping that takes data from multiple sources starts with the usage of the Source Qualifier operator that does basically a join between the input tables (in the screenshot we qualify only one input table).

The join query could be user-defined or auto-generated.

While using a user-defined query we have to take care of the number of columns we specify in the select statement that must match the number of columns linked from the Source Definition to the Source Qualifier.

Whenever these numbers mismatch, we get an ORA-01007 at runtime, even if the query itself it’s syntactically and logically correct.

A typical scenario in which you might face this problem could be the following: someone updates a source table metadata definition by adding new columns and then updates the related ETL mapping to include the new columns in the ETL logic. Then asks you to import the new stuff in production.

If the workflow that execute the modified mapping has an overwritten SQL in the Source Qualifier definition and we forget to update it, we may face the problem as well, even if the number of columns specified in the select statement of the related mapping’ Source Qualifier matches the number of linked columns from the Source Definition.

Infact when an overridden SQL is used in the worflow, Informatica executes the overridden script instead of the one from the mapping. So if the worflow is part of a previous release of the software, it still has the old number of columns in the select statement that might not correspond to the numbers of the linked columns from the source definition.

 
2 Comments

Posted in Data Warehousing

 

Connect ESSBASE to OBIEE 10g’s repository

18 Oct

1 – Introduction

In this topic we will see how to connect essbase to the OBIEE 10g repository and use it as data source for our reports.

2 – Prerequisites

2.1 The following product versions need to be installed:
OBIEE 10.1.3.3.2 or higher.
Essbase 7.1.6 or higher.

Essbase libraries need to be installed in the same machine where OBIEE server is installed.
We will assume that an Essbase server instance is already running somewhere as well as the OBIEE 10g, so we just need to link the two products; in order to connect Essbase to OBIEE 10g we need to install the Essbase client.

2.2 To install Essbase Client, you need to download the following software from http://otn.oracle.com (aproximately 8 Gb):
Hyperion EPM System Foundation Services (System Installer, Foundation Services, Architect)
Oracle Essbase (Client, Server, Provider Services)

Read the rest of this entry »

 
No Comments

Posted in Business Intelligence, Data Warehousing, Database

 

Pivot table: generic sql query and Oracle Database 11g PIVOT operator

13 Oct
1 – Definition

A pivot table is the result of the action that allows data tables to be rearranged in many ways for different views of the same data.
Technically, if we compare a table with a matrix we can think to the pivot table as a transposed matrix, where the original row can be read as a column in the resulting table.

2 – How to generate a pivot table

Immagine to have the following data set:
select practice, office, count(1)
from organization
group by practice, office
Now you want the office’s numbers to be columns.  We have 4 offices (1,2,3,4) and we want to have the following columns in our resulting pivot table: OFF_1, OFF_2, OFF_3, OFF_4.
The query to generate the desired pivot table will look like the following:
select practice,
max( decode( office, 1, cnt, null ) ) off_1,
max( decode( office, 2, cnt, null ) ) off_2,
max( decode( office, 3, cnt, null ) ) off_3,
max( decode( office, 4, cnt, null ) ) off_4
from (
select practice, office, count(1) cnt
from organization
group by practice, office
)
group by practice

CNT column has been pivoted by OFFICE across PRACTICE.
Remember that this works only if you know the domain of the column you pivote by.
 
1 Comment

Posted in Data Warehousing, Database, Development