Prerequisites for conformed dimension in stitch query

[Commander Cognos]

Hi everyone,

I'm working on a stitch query and tried to rebuild the situation with sample data with one conformed dimension, two fact tables (1:n cardinality) and two non-conformed dimensions. In one example it is working as expected (expected: data is filled down for the detailed rows). In the other derived example I tried to reproduce the challende in our model and it is not working.

Now the question. What are the prequisites or key points to identfy and to build a conformed dimensions. I see it is a topic which is very sensitive, because one change later Cognos might use a different key to stich the queries. The goal ist to have a result set where we have the detailed data with the grouped data from the other fact table combined. In the example where it is not working the aggregated data is only filled for one row. This happens once we bring in the second non-conformed dimensions. In theory it is all clear to me. The only thing I'm wondering is how to check where the mistake is. I defined also determinants already - this is clear to me, but is not bringing the expected results. Also, AutoSum is not a solution. For clarification I attached the model and the test results.

Thanks a lot in advance!

[cognostechie]

The picture you provided does not indicate the fields used to join the tables. What do you call 'Conformed' and 'Non-conformed' ?
The data showing up in only one row does not mean it is wrong. It could be correct too.

I can see some joins that are not as per the expectation according to the conformed dimension strategy. The Product and Supplier dimensions should be joined to the Fact (detailed Order table), not to Order Header. The Order header should be the Order dimension which should be joined to the Order Fact (detailed Order). The Order amount should only be in Order Detail table, not in Order Header. The only measure in Order Header should be the one which does not apply by the line (Ex: Freight charges).

All dimension keys should be in Order Fact (detail Order) table, not in the Order header. If you change the design of the tables then you will have a better idea of how cognos creates stitch queries.

To answer your question in one sentence - The two most important prerequisites are 1) Properly designed tables and 2) Properly joined in FM.

[Commander Cognos]

Hi cognostechie,

thanks a lot for your fast reply.
Well, actually the question wasn't really about the business logic (even though I know this is also crucial for the design), but rather about the technical parameter and prerequsistes in detail in FWM on a stich query.
In this example I thougt it is obvious, that Order and Order details are the facts, product is the conformed and supplier and tax are the non-conformed dimensions. product will be joined to order through product_type (F_ORDER_PRODUCT_TYPE = D_PRODUCT_TYPE) and order details to product through product (F_ORDER_PRODUCT = D_PRODUCT). Having two separate fact tables ist on purpose in this example to have two different levels of granularity. I've seen models with the setup, even though it might be possible to merge them. As I said in the first example it is working quite well and is giving the desired results.
The question is in example 2 where we have the order_header_id in the product dimension if this is the reason for the not working stitch query? Does it mean once we have a multi-grain stitch query, that in the conformed dimensions we need data for both levels of the fact tables? So do we need a key for the detailed level as well? Or can we join on the same level?

Thanks again!

[Commander Cognos]

Hi everyone,

an additional note, remark or finding or whatever... ::) Maybe someone can acknowledge or undermine this statement?

In a stitch query with the conformed dimension e.g. Time and two fact tables e.g Sales (on day level) and Forecast (on month level) in order to have results on day level and the aggregated month results from forecast for every row- the conformed dimension time MUST hold data for the lowest level of the fact tables, in this case day.

Otherwise the one subquery for forecast (with aggregate) will result in one row for month and the detailed fact for sales result in multiple rows (for each day). Cognos will then stich the queries and in best case only one row will match but all others are not filled with the montly data.

This will follow in the questions: how can I enforce Cognos to return the subquery for aggregated data on daily level?

Thanks and I hope you can follow my thoughts!  :-\

[cognostechie]

I think you need to take a training class for understanding the basic fundamentals. Without that, I know I will have to spend lot of time explaining things to you and nothing will make sense even if I do that.

Business Logic has nothing to do with this. The tables are created according to a star schema design regardless of how the business is conducted. I have seen this working in every industry  and all of them conduct business in different ways. That's where ETL comes in. Product and Product type are supposed to be in ONE table, not two. That table will be called Product Dimension. Splitting them in two tables means none of them is a Dimension. They remain Master tables. All dimensions should be joined to the Fact and in your case, there needs to be only one Fact, what you call Order Detail.

I will refrain myself from elaborating on this because you are headed in the wrong direction and this kind of approach has caused many projects to get a quick and dirty solution in the beginning and have failed miserably further down the road. It causes the tool to be blamed later.

So, sorry but can't help in this. Your problem is more of designing and ETL and less of modelling.