joins for sample oracle database incorrect results

[jammaster]

Hi,
I downloaded sample database and tried to create report off of 3 tables ORDERS, CUSTOMERS and CONTACT but getting wrong results.  When I bring in columns from ORDERS, then add columns from CUSTOMERS, I get correct results but when I add columns from CONTACTS I get what I think is a Stitch Query.  Can you advise if thats whats going on?  Joins are CUSTOMERS.CUSTOMER_ID (1..1) = ORDERS.CUSTOMER_ID (1..n) and CUSTOMERS.CUSTOMER_ID(1...1) = CONTACTS.CUSTOMER_ID (1...n).  Looks like CUSTOMERS and CONTACTS tables can have a 1 to 1 relationship, but not sure why on the sample database download its not listed that way.  ERD can be seen here:  https://www.oracletutorial.com/getting-started/oracle-sample-database/     
can you please take a look at the url and let me know how to correctly do the joins on Framework?    This is the native sql that I am getting when running the report:

select "orders"."order_id" AS "C0", "orders"."customer_id" AS "C1", "orders"."status" AS "C2", "orders"."salesman_id" AS "C3", "orders"."order_date" AS "C4", "customers"."customer_id" AS "C5", "customers"."name" AS "C6", "customers"."address" AS "C7"
from "oracle"."dbo"."orders" "orders", "oracle"."dbo"."customers" "customers"
where "customers"."customer_id" = "orders"."customer_id"
order by 6 asc , 7 asc , 8 asc , 1 asc , 2 asc , 3 asc , 4 asc , 5 asc

select "customers"."customer_id" AS "C0", "customers"."name" AS "C1", "customers"."address" AS "C2", "contacts"."contact_id" AS "C3", "contacts"."customer_id" AS "C4", "contacts"."first_name" AS "C5"
from "oracle"."dbo"."customers" "customers", "oracle"."dbo"."contacts" "contacts"
where "contacts"."customer_id" = "customers"."customer_id"
order by 1 asc , 2 asc , 3 asc , 4 asc , 5 asc , 6 asc

Thanks for any insight

[bus_pass_man]

I am not familiar with the Oracle sample database but the diagram on their getting started page tells me quite a bit.

The query is doing exactly what you asked it to do.

You will observe that there are TWO queries being sent to the database in the native sql.   If you look at the Cognos sql you will see one query where the results are being stitched together.

Because of the cardinalities of the relationships, the query engine needs to generate a stitch query.  The added complication in your situation is that customers isn't a fact table.

Changing the cardinality can gloss over that but it will be wrong.  It is wrong as it is denying the reality of the data.  This is one reason why knowing your data, and what it represents, helps.

The Oracle diagram tells you very clearly what the relationship between customers and contacts is.  For any customer, there are potentially many contacts.  The diagram is actually saying that there could be zero contacts for a customer but that just re-enforces that the relationship is not 1.1.

In the Cognos proven practices, the first thing you do after a metadata import is confirm the relationships, especially their cardinalities, and that the usage of query items matches their role.  That is if a column in a table is key then it should have a usage of identifier.   If it is a fact then it should have a usage of fact.   Quite often, if a data base does not have primary key constraints then no relationships are created and columns which you want to be identifiers are set as fact.   The objective is to review the metadata import and correct what is not correct.

You should be able to go through the imported metadata and confirm that the mapping of the Oracle metadata to the FM model's metadata is valid. 

I'll walk you through the 3 tables that you are looking at.   I am using the Oracle diagram and the DDL from the Oracle getting started page and using my modelling knowledge. 

Usage:
Customers:  Customer_ID should have a usage of identifier.  The others should be attributes.  It is possible that because the data type of credit_limit is numeric that it is being imported as a fact but, judging from the cardinalities of the joins in the diagram, customers is not a fact table.  In the real world, credit_limit would not be a fact but an attribute.   

Orders:  Order_id, customer_id, and salesman_ID should be identifiers.
Orders is in a master/detail situation with order_items.   

Contacts: customer_id and contact_id should be identifiers.

Relationships:

The relationship between customers and orders should be 1.1  and 0.n but it is possible that it has been imported as 1.1 and 1.n .  Don't worry about that right now.

The relationship between customers and contacts is 0.1 and 0.n .   It is possible that it has been imported as 1.1 and 0.n.  Don't worry too much about that either.

Two of the next steps of the Cognos proven practices are to identify the dimensions and to identify their grains.

In your case, customers and contacts are elements of a dimension.  The problem is that the join between it and the fact table (orders/order items) is at a level above the leaf grain of the dimension.   

Fortunately, the key which joins customers to orders is also present in contacts.  If you create a 1.n relationship between contacts and orders using customer_ID and delete the relationship between customers and orders you should be OK.

Hope that helps and that you are having a happy Christmas.

[jammaster]

thanks for detailed response bus_pass_man.  I followed your join suggestion and its coming out properly.  Just so that I understand, CONTACTS and ORDERS tables were being looked as Fact and CUSTOMERS was being looked as Dimension, thats why the query was being stitched together?  Instead of following your suggestion,  could I also Merge CUSTOMER and CONTACTS as one query and join that one query to ORDERS table?  if I do this do I leave the  join for CUSTOMERS AND CONTACTS the way I had it?  CUSTOMERS.CUSTOMER_ID(1...1) = CONTACTS.CUSTOMER_ID (1...n)?  Merge this combined query and join with ORDERS?

Should I also Merge REGIONS, COUNTRIES, LOCATIONS and WAREHOUSES as one query and join that to INVENTORIES?  because COUNTRIES will be treated as a Fact table to REGIONS and LOCATIONS will be treated as a Fact table for COUNTRIES etc...  confused as how I should join in Framework according to best practices since following the ERD could potentially not bring back correct results.  Thanks for your help

[bus_pass_man]

You're asking several questions and they require rather more investment of time into trying to explain the fundamentals of modelling than can reasonably fit into the space for an answer and certainly for free while I'm on holiday.

i.
I think you have several terminology confusions.

I'm not sure what you mean by 'merge into query' and I'm afraid that you might not be as familiar with FM stuff as you might want to be.  In particular, I suggest that you familiarize yourself with the model query subject.

You might want to take Cognos training.

The objectives are to create a query model where all the facts and dimensions have been identified, all the objects in any dimension which is participating in a fact table have one and only one possible way towards the fact table, and that the query engine has sufficient metadata to know what fact grain any particular object is at.

You might also¹ want to read up on determinants.

ii.
You also seem to be a bit fuzzy on the industry-standard concepts of facts and dimensions.  Kimball or Adamson have fairly decent definitions.

Just to throw this in to make it clear, a dimension – the thing with hierarchies, levels, and members -- is not I mean by dimension.  I mean the Kimball concept of a dimension.   The concept of a dimension with hierarchies, levels, and members is built on top of that concept. 

iii.

Just so that I understand, CONTACTS and ORDERS tables were being looked as Fact and CUSTOMERS was being looked as Dimension

I need to quote my immortal prose:

Because of the cardinalities of the relationships, the query engine needs to generate a stitch query.  The added complication in your situation is that customers isn't a fact table.

Elaborating on that, if a thing is in a query, is on the many end of the relationship which brought it into the query, and there is nothing beyond it then the query engine will try to think of it as a fact table.

If the table is a fact table then everything will be OK.

If the table is part of a dimension then you could end up with a query projecting below a fact grain, which can produce double counting, which is not OK. 

The way that the two tables exist and the relationship between customers and orders indicate that contacts is below the grain of orders.  Customer_ID exists in the contacts table as well as customers.   If you created a model query subject using customers and contacts and created the relationship between the dimension and the orders fact using the key from contacts would allow you to capture that reality.   You would need determinants set to indicate what query items exist at the customer level and what exist at the contact level.

You need to understand that the existing relationship would need to be removed, as I stated in the original response.

You will get stitch queries anytime you have multi-fact situations.  The Cognos query engine will try to coalesce all of the conformed dimensions.   

iv.

Instead of following your suggestion,  could I also Merge CUSTOMER and CONTACTS as one query and join that one query to ORDERS table?  if I do this do I leave the  join for CUSTOMERS AND CONTACTS the way I had it?  CUSTOMERS.CUSTOMER_ID(1...1) = CONTACTS.CUSTOMER_ID (1...n)?  Merge this combined query and join with ORDERS?

No.  You would still be in the same situation, where the join from the dimension to the fact is above the fact grain.  The proper location of the join between that dimension and the fact is from contacts.

You need to analyze the data base and understand what each object is and its business purpose.  One good way of doing that is by saying what the entity relationship is and thinking about it in real life terms.

You also need to understand the application for which you are going to use the model.

If you have a dimension which is a perfect snowflake, that is each table has a 1.n relationship to the next table down and each table represents one level of detail, then the determinants which get created during metadata import from the primary keys will be sufficient metadata for multi-fact / multi-grain (MFMG) situations. 

If you have a dimension table without outriggers then you will need to augment the determinant which is created by creating determinants for each level of detail in the dimension table.

If you have something other than those situations you need to create a model query subject with the objects of the tables which belong to your dimension and create determinants for each level of detail in the dimension. 


There are 2 facts.  They are orders (orders / order_item) and inventory.

There are 4 dimensions.  They are Products, Employee, warehouse, and customers.

None of the dimensions have built-in joins in the db which make them conformed.   Products has a key in the order_item table which can be used to create a relationship. 

Warehouse dimension might have multiple levels in the locations table.  It depends on whether you want to identify state and city as levels.   

Depending on that. you might need to put the warehouse tables into a model query subject and define the appropriate determinants.


v.

because COUNTRIES will be treated as a Fact table to REGIONS and LOCATIONS will be treated as a Fact table for COUNTRIES

No they're not fact tables.   

It would be helpful to you if you invest some time into taking Cognos courses and working with the samples, which actually are a bit more sophisticated in terms of modelling problems.

vi.

confused as how I should join in Framework according to best practices since following the ERD could potentially not bring back correct results. 

You will need to explain what you mean.  Apart from customers/contacts, every table is joined to the other tables in the data base in a manner which will flow down from a higher level of dimensional abstraction to a more specific level.   


1. You seem to not be a native speaker of English.   'You might want to' really means 'You ought to and will utterly fail if you don't'

[Andrei I]

Jammaster,
Download and configure Cognos Samples for your Cognos version.The have FM models you can review.
https://www.ibm.com/support/knowledgecenter/SSEP7J_10.2.1/com.ibm.swg.ba.cognos.inst_cr_winux.10.2.1.1.doc/c_settingupsamplesbi.html