Two Fact Tables

system · July 7, 2004, 7:39pm

Hi,

When i want to generate the report from the universe with two fact tables, BO is generating two independent queries.
I want to have the measures in both the fact tables those have the same dimension like same date.

ravik (BOB member since 2004-06-02)

system · July 7, 2004, 7:45pm

Ravi, can you just clarify that what you see in the Query Panel is the sql split into two ‘grey’ bits of sql? Do you see the word Synchronised? Are you not able to put all the measures and dimensions in the same report block?

Nick Daniels (BOB member since 2002-08-15)

system · July 7, 2004, 9:53pm

Hi,

can you just clarify that what you see in the Query Panel is the sql split into two ‘grey’ bits of sql?
Ya SQL split into two Select Statements

Do you see the word Synchronised?
Ya I can see word Synchronized in SQL Viewer

Are you not able to put all the measures and dimensions in the same report block?
Two separate report blocks are generated

ravik (BOB member since 2004-06-02)

system · July 7, 2004, 10:09pm

You have a problem and need to investigate using Contexts. You also should search for things relating to “Multiple SQL Statements for Each Measure”.

Steve Krandel (BOB member since 2002-06-25)

system · July 7, 2004, 11:13pm

Hi,

There are seperate Contexts for fact tables.

“Multiple SQL Statements for Each Measure” is enabled in Universe Parameters->SQL

ravik (BOB member since 2004-06-02)

system · July 7, 2004, 11:25pm

Since you have 2 contexts, you WILL have multiple queries. That’s what it’s supposed to do.

Are you retrieving any dimensions from the fact tables? You’re not supposed to do that when you have a dimensional model.

This is going to be really difficult to debug without seeing your universe.

Steve Krandel (BOB member since 2002-06-25)

system · July 8, 2004, 9:23am

hi,

My DB and Universe are of Multiple star. type.
I did not use dimension from Fact tables. I use the dimension from Dimension table which is common to both the fact tables.

ravik (BOB member since 2004-06-02)

system · July 8, 2004, 11:01am

Try creating a seperate context involving both these fact tables. Or if you feel that the amount of data in your query is less with minimum calculations then try creating 2 different queries and join it at the report level.

See if this is a feasible option.

Ashokkumar (BOB member since 2004-05-20)

system · July 8, 2004, 2:44pm

Hi,

I created seperate context for these two fact tables, but it is showing 21 loops and 21 context errors.
It is huge amount of data, so i am not infavour of joininig at report level.

So the problem remain as it is. i.e.

“When i want to generate the report from the universe with two fact tables, BO is generating two Synchronized queries.
I use the dimension from Dimension table which is common to both the fact tables and measures from each fact table”

Is anybody gone through this situation successfully? If so please guide me.

ravik (BOB member since 2004-06-02)

system · July 8, 2004, 2:47pm

How are your measures defined at the universe level? Do they use SQL Group By functions?

This should be resolved!

Andreas (BOB member since 2002-06-20)

system · July 8, 2004, 4:41pm

AS per Asok Kumar suggession i created combined context ( may be misunderstood) then there are 21 errors.

When there is separate context for each fact table, there are no errors

ravik (BOB member since 2004-06-02)

system · July 8, 2004, 4:51pm

You still have not answered the question:

Andreas (BOB member since 2002-06-20)

system · July 8, 2004, 5:21pm

Hi Andreas,

I did not use any group function for measures.

ex : avg(schema1.table1.column1)

ravik (BOB member since 2004-06-02)

system · July 8, 2004, 5:29pm

Your example seems to indicate you did use a SQL Group By function .

If not use a SQL Group by function in the Select box for your measures (in Designer) such as SUM or AVG etc. and see if you still get a synchronized query.

Andreas (BOB member since 2002-06-20)

system · July 8, 2004, 8:08pm

Hi Andreas,

Thanks. You are right. When i take the measure group by (avg), BO is creating Join queries insead of Synchronisation queries.

Validating the data now (whether it gave the measures for common dimension items or not)

Before when i tried basic measure (fact columns only, not group by) Bo generated Synchronisation Queries. Is there way to use basic measures?

ravik (BOB member since 2004-06-02)

system · July 9, 2004, 3:18am

You’re missing the point. There is no such thing as a basic measure. A measure is really not a measure unless it has SQL aggregation on it.

I actually wish BO would require this.

Steve Krandel (BOB member since 2002-06-25)

MarkP · July 9, 2004, 8:23am

Ravi,

Why would you want to bring back a million rows if, as Andreas quite rightly advocates, you should use group functions on measures to return only 1000 rows to provide the correct report. I have always stuck with trying to provide the solution via the universe first and then in the report, given that someone later would have faced the same problem.

Just my two EuroCents worth,
Mark

system · July 12, 2004, 12:40am

Hi,

I am using the dimension from Dimension table which is common to both the fact tables and measures (group by) from each fact table
Now When i generate the report from the universe with two fact tables, BO is generating two join queries. Also the report is of one block (instead of two blocks)
However the theres is no data from the measure of second fact table (there is data in the columns of dimension and measure of first fact table).
Please adivce me.

ravik (BOB member since 2004-06-02)

system · July 12, 2004, 6:03am

Hi,

Quick review of present situation again

I created a Universe with two fact tables and Seven Dimension Tables in Star Schema (DB also Star Schema).
I created two contexts(one each for Fact Table)to avoid loops.

User wants to generate the report with measures (group by) from each fact table for common dimension values present in both fact tables. This Dimension table is joined to both the fact tables.

User wants like
SELECT
dim1.columnX, avg(fact1.columnY), avg(fact2.columnZ)
FROM
dim1, fact1, fact2
WHERE
(fact1.key1 = dim1.key1)
AND (fact2.key1 = dim1.key1)

BO is generating two Select queries (Join). When this query is run, Bo generated a one block report.

However there is no data in some rows of the measure for second fact table and there is data in the columns of dimension and measure of first fact table.

Also these rows are not matching with output generated directly through Toad.

Also i want to know the effect two Select (join) queries, in-addition to resolving the issue of resolving multiple fact table.

Please advice me.

ravik (BOB member since 2004-06-02)

system · July 12, 2004, 6:28am

Obviously the merged query and the two queries generated in BusObj will return different results…

The merged query that you ran in TOAD will return lesser rows than the two queries in BusObj… And what BusObj does gives you the best result that you could have…

Let’s consider the below…


Fact1
Dim1        Meas1
A               10	
B               15	

Fact2
Dim1        Meas2
A               20
C               30

The BusObj report (2 queries) will return,


Dim1       Fact1         Fact2
A              10             20
B              15           [BLANK]
C          [BLANK]        30

Your merged query will return only


Dim1       Fact1         Fact2
A              10             20

and skip the other two records.

Hope this explains you…
Mohan

cpmohanraj (BOB member since 2002-09-23)