Empty columns when joining tables (inner and outer)

[maevr]

I am having a problem with a report missing values from certain tables (look at design below) when combining inner joins and outer join tables from Framework Manager.
When I create a list in report Studio with the following columns everything is fine. I get values in all fields.

Tbl1.id     Tbl2.tblValue2     Tbl3.tblValue3     
1             Test1                   Test2         

When I add Tbl4 then the columns for Tbl2 and Tbl3 is empty(Tbl4 contains 3 rows with the id 1), WHY????

Tbl1.id     Tbl2.tblValue2     Tbl3.tblValue3     Tbl4.tblValue4
1                                                                    Test4
1                                                                    Test5
1                                                                    Test6


Tbl1 (main table)
id
tbl1value1

Tbl2 (1-n relation to Tbl1)
id
tbl1.id
tbl2Value

Tbl3 (1-n relation to Tbl2)
id
tbl2.id
tbl3Value

Tbl4 (0-n relation to Tbl1)
id
tbl1.id
tbl4Value

[bdbits]

An outer join says "include rows from this table, even when there is no related data from the other table(s)". So you will always see rows from tbl4, regardless of whether there is related data in the other tables.

[maevr]

Ok, but why is the other columns empty from tbl2 and tbl3, I want them to have values on the other rows as well, nevermind if it duplicates.

[Lynn]

Which tables are supposed to be facts and which are supposed to be dimensions? A table on the "n" side of all relationships is considered a fact table and the generated SQL is going to be affected by this. A multi-fact query may be generated with the results coalesced locally.

I'm not convinced you have a correct model but it is difficult to tell for sure based on what you have provided. You should take a look at the native and also the cognos SQL to see if it is what you would expect or not.

[maevr]

Thanks for your response!
When I generate the native SQL from the report I get two queries one for the inner joins and one for the outer join.

[Lynn]

Thanks for your response!
When I generate the native SQL from the report I get two queries one for the inner joins and one for the outer join.

Post back when you are ready to answer the rest of the questions.

Ideally you should speak with the person responsible for the Framework Manager model because if you aren't getting expected results then there are probably issues with the way it is modeled.

The two native queries are probably getting stitched together locally, but you didn't respond back about the Cognos SQL or the question about what should be fact and what should be dimensions so there isn't anything more I can offer.

[maevr]

OK, well as far as I know all tables are used as facts. The tables in the model is not of the type dimension.
Do you know of any good tutorials regarding modelling in Cognos Framework Manager.

Tbl1 is the main table with the key id.
Tbl2 is of type inner join to Tbl1 using id (1-n).
Tbl3 is of type inner join to Tbl2.
Tbl4 is of type outer join to Tbl1 (0-n)

I tried combining the tables using a join in the report instead and it all worked.
The problem as I see it is that the report creates two queries atd connects them locally.

I have full control over the environment.

[Lynn]

I would suggest you start with the Framework Manager user guide. There are some good sections in there about how to model your data and especially about understanding cardinality.

You are getting multiple queries generated (I suspect) because the query engine sees everything as a fact table. You didn't answer back about the Cognos SQL, but you will likely see the stitch happening there.

If you are going to be responsible for modeling you might seek professional training to help you get off to a good start.

[maevr]

Here is a picture of the model (other table names) with cardinality and relations (diagram is from databasview)

ss_ed_fakta = Tbl1
ss_fastighet = Tbl2
ss_byggnad = Tbl3
ss_atgforsl = Tbl4