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
1 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'
Home
Help
Search
Calendar
Login
Register