Impromptu Query Question

[Mackrel]

Has anyone had success using Impromptu to build a report where the measures come from different fact tables in the data mart. The measures are independent of each other and share a common set of dimensions.

 

[veracruz]

I don't believe that Impromptu is capable of correctly joining a conformed dimension to multiple fact tables. Business Objects can join multiple fact tables by defining "contexts" which set up multi-pass SQL. I've also just posted a question similar to this one on comp.databases.olap and on Cognos's support pages, so if I'm wrong and someone knows of a way I too would appreciate hearing about it.

Below, is the Oracle ddl and the SQL statement that demonstrates Impromptu's chasm trap/cartesian product problem. At the logical level though, think of it this way: if I join a parent to a child table, the primary key of the returned set IS NOT the primary key of the parent table, it's the concatenation of the parent primary-key and the child primary-key. If I then join this new result set to another child table, I'm apt to have a many-to-many relationship between the parent primary-key -- and hence a cartesian product or chasm trap.

create table t_parent (parent_id number, parent_dsc varchar2(20));
insert into t_parent values (1,'balls');
insert into t_parent values (2,'bats');
insert into t_parent values (3,'mits');
commit;

create table t_child1 (child1_id number, parent_id number, child1_dsc varchar2(20), sales number);
insert into t_child1 values (1,1,'feb sales',400);
insert into t_child1 values (2,1,'mar sales',300);
insert into t_child1 values (3,1,'apr sales',100);
insert into t_child1 values (4,2,'mar sales',600);
insert into t_child1 values (5,3,'mar sales',200);
commit;

create table t_child2 (child2_id number, parent_id number, child2_dsc varchar2(20), costs number);
insert into t_child2 values (1,1,'feb costs',200);
insert into t_child2 values (2,1,'mar costs',100);
insert into t_child2 values (4,2,'mar costs',300);
insert into t_child2 values (5,3,'mar costs',250);
commit;

select a.parent_id, a.parent_dsc, sum(b.sales), sum(c.costs)
from t_parent a, t_child1 b, t_child2 c
where a.parent_id = b.parent_id and
a.parent_id = c.parent_id
group by a.parent_id, a.parent_dsc;

Sales for bats is reported as 1,600 whereas it should be 800 according to the rows in t_child1. Costs for bats is reported as 900 whereas it should be 300 according to the rows in t_child2. These values of sales and costs for bats is erroneously reported because every row in t_child1 is joined with every row in t_child2.

 

[griffindm]

I will check this out on Wednesday, but I think the problem here is that you need to have a three table join, rather than a two one-table joins. There is correlated data in both child tables, which should be included in the join. This means that the select statement should be:

select a.parent_id, a.parent_dsc, sum(b.sales), sum(c.costs)
from t_parent a, t_child1 b, t_child2 c
where a.parent_id = b.parent_id and
a.parent_id = c.parent_id
AND B.CHILD1_ID = C.CHILD2_ID
group by a.parent_id, a.parent_dsc;

I have had to do this before in Impromptu, and it works well using the definition join option, rather than the diagram option. I'll set up the tables in Oracle and report back on the results then.

Dave Griffin

 

[griffindm]

Well, I tried the setup I have above, and I get promising results. The remaining issues revolve around the child table join values being apparently sequential unique synthetic key values, rather than month associations, which I had errorneously assumed. If you re-write the SQL to read:

select a.parent_id, a.parent_dsc, sum(b.sales), sum(c.costs)
from t_parent a, t_child1 b, t_child2 c
where a.parent_id = b.parent_id and
a.parent_id = c.parent_id
AND substr(B.CHILD1_dsc,1,3) = substr(C.CHILD2_dsc,1,3)
group by a.parent_id, a.parent_dsc;

you get the same result set:

PARENT_ID PARENT_DSC SUM(B.SALES) SUM(C.COSTS)
=========== ==================== ============ ============
1 balls 700 300
2 bats 600 300
3 mits 200 250

but the logic is better. The amounts are still off because of inner-join issues on the april sales without costs. Unfortunately you cannot outer-join a table to more than one table in Oracle.

 

[devul]

Impromptu lets you edit the SQL. The disadvantage is once you do so then you will have to maintain the SQL yourself. Impromptu will not do it for you. The advantage is you can write any valid SQL which can be the 3 table join.

 

[griffindm]

devul,

I was referring to setting up the three table join via the catalog join functions, using the definition radio button, rather than the diagram. This does not require editing the SQL of the report, which would have the undesirable effects you describe.

Regards,

Dave Griffin