How Framework Manager generates SQL

[ricky_ru]

Dears,

Could anyone share me what is the way Framework Manager generates SQL statement. I am learning the GO SALES sample. I  tested the following items with auto sum.
RETAILER           ->COMPANY_NAME
SALES_TARGET  ->SALES_TARGET
ORDER_DETAILS->QUANTITY, UNIT_SALE_PRICE

But the SQL generated is complicated. Some other table are involved also. I really want to know what's the logic of generating SQL based on the query item. From the table relation ship (FK) and the fact column?

I appreciate any thought.

[MFGF]

Hi dearie,

It looks to me like this selection of items will result in a multi-fact request for data. If memory serves, sales targets are held at month level in one query subject while order details are held at day level in another. Using a single conformed dimension of company name, you should see a stitch query where company name is coalesced and the facts from sales target and order details are stitched via full outer join. Is this what you are seeing?

MF.

[ricky_ru]

Thanks for you reply.
Please check my attached image in which there are 2 SQL statements generated by the selected query item.

In the first one, the query items are as below. why is RETAILER_NAME not the conformed dimension.
ORDER_HEADER->RETAILER_NAME
SALES_TARGET->SALES_TARGET
ORDER_DETAILS->QUANTITY,UNIT_SALE_PRICE

In the second case, the SQL seems pretty reasonable but how cognos makes that(join table retailer, retailer_site, order_header and order_details together automatically)?  why not join order_header and sales_target with other tables in the first case(such as order_header, branch, country and sales_garget)?. Does cognos make it by checking the relationship plus the carnality?
RETAILER           ->COMPANY_NAME
SALES_TARGET  ->SALES_TARGET
ORDER_DETAILS->QUANTITY, UNIT_SALE_PRICE

Thank you~

[blom0344]

Not entirely clear what your question is, but the second scenario is a typical stitch query where 2 sets are aggregated first and then rejoined over the conformed dimension. A dimension that has a relationship with 1 fact is joined within 1 of the generated sets prior to applying the full outer join..

[ricky_ru]

Thanks.

You can check the my attached image for the SQL generated.

How cognos knows the relationship between dimention(COMPANY_NAME in RETAILER) and the fact(QUANTITY,UNIT_SALE_PRICE in ORDER_DETAILS)?
I just do not understand why cognos knows to join  retailer, retailer_site, order_header and order_details.

Thanks

[blom0344]

1. Defined joins
2. Applied cardinality

The n side of the cardinality defines the fact,  the  0/1 side the dimension.  Cognos certainly does NOT know how to join query subjects without explicit joins defined.
The joins however may may defined in another layer !!

[ricky_ru]

1) there is no direct relation ship between the dimention(COMPANY_NAME) and the fact(QUANTITY,UNIT_SALE_PRICE).
2) I do not know the layer you mentioned. I think we only have the table structure from database(relationship, cardinality)

So, I just guess that cognos will seek in the relationship network and finally to join that 4 tables retailer, retailer_site, order_header and order_details together.
But I am not sure my understanding is right.

[blom0344]

Well, the entire concept of Cognos framework (manager) is about creating an optimized model for reporting. This means adding a modelling layer based on imported database tables / views.  The ideal is to remodel as closely as possible to starschema's. This may involve merging tables to new model query subjects to get a proper model.  I get the feeling that this has been entirely skipped with your model. Perhaps you can dive into this matter , either through some formal training or by studying the FM guide for best practices?

[MFGF]

Hi,

One of the key factors for success when building a model in FM is to understand how the relationships and cardinality you define will cause queries to be generated when you select items for use in reports. The holy grail is to build a structure that will generate queries providing accurate, consistent results.

In any generated query, a query subject will either be identified as a "fact" or a "dimension". If it is at the N cardinality end of all relationships to it, it will be identified as a "fact". If it is at the 1 cardinality end of any relationship to it, it will be identified as a "dimension". Any query featuring more than one "fact" will be generated as a stitch query (as in the second example in your attachment). Otherwise the query will simply join the tables.

The best-practice guidelines for modelling for success in FM suggest that structures resembling star schemas will conform to the assumptions the query engine makes at runtime, and will provide a foundation for accurate, consistent results. If your tables do not represent star schemas, you will need to remodel your middle tier to fit the star schema paradigm. Your presentation layer will then be based on these structures.

If you haven't attended the official FM training from IBM, I can recommend it. All of these concepts are covered, and you even get to backwards-engineer what the query subjects and relationships look like in the model by analysing the generated SQL. It's fun and it helps your modelling skills enormously!

Cheers!

MF.

[ricky_ru]

Thank you two for your kindly support. I really learn much.