create a insert all with Data Manager

[BriannaS]

Hi Everybody,

I use Cognos Data Manager 8.4 and Oracle 9i.
I want to load 2 tables D1 and D2 from 2 tables S1 and S2.
I read that the commit is done at the end of each Data Manager build.
I can do this with 2 sequentials builds, but if a hardware error occurs, I don't wan't to be on a state where D1 is load and D2 empty or an ancient state.
So i want to have the commit after the second loads.

In other words, I would like to create a jobstream behaving like the sequence :

Code Select Expand

INSERT ALL
INTO /*+ append */ D1
(PEOLPLE,AA)
VALUES(PEOLPLE,AA)
INTO /*+ append */ D2
(CAR,AB)
VALUES(CAR,AB)
from(
select S1.PEOPLE,S1.AA,S2.CAR,S2.AB
from S1 JOIN S2 ON S1.PK1=S2.FK1);

But currently, I only suceed in creating a cross join DataStream and I load duplicates rows.

Does a solution exist in Data Manager ?

Regards,
Brianna

[MFGF]

Hi,

You can do this all in a single fact build. Add two data sources to the build - one selecting rows from S1 and the second selecting rows from S2. Add a literal to each data source - maybe 'a' for the first and 'b' for the second. Map the columns from each data source to separate sets of items in the datastream and the Transformation Model, all except the literal, for which you map the literals from both sources to a single datastream item, and map this to a single Transformation Model element.

Add two relational fact deliveries to the build, delivering to D1 and D2. In the properties of D1 on the Table Properties tab, uncheck all the columns from S2, go to the Filters tab, and add an Output Filter with an expression of <your literal item> = 'a'. In the properties of D2 on the Table Properties tab, uncheck all the columns from S1, go to the Filters tab, and add an Output Filter with an expression of <your literal item> = 'b'. Finally, check that on the Module Properties tab of each delivery, the "Share Database Connection" checkbox is ticked.

Cheers!

MF.

[eknight]

This is the right answer but I think you swapped the D and S tables. I think D = Destination and S = Source.

MFGF says: Oops! Well spotted! I have edited my post to switch them around. I was tired when I wrote the answer last night. That's my excuse and I'm sticking to it :)