Hi gurus,
I'm developing a FM model with multiple fact tables.
Because I have many conformed dimensions the result is that attached (see the image, please)
Do you think is possible semplify this model? Did you ever developed model with many conformed dimensions?
Do you think shall I apply any best practices to reduce the complexity of this model?
Thank a lot for each advice you could give me.
There is nothing wrong with having multiple fact tables sharing many conformed dimensions. It is good to understand how Cognos handles it. Basically, each star schema (single fact table and it's dimensions) will be identified and treated as a separate query, then all the separate star schema queries will be stitched together. Stitching is outer joining the different star schema queries on the conformed dimensions.
Cognos identifies a query subject as a fact table when all relationships involved in a query are defined with 1:n or 0:n cardinality.
More detailed information on stitch queries in Cognos:
http://www.ibm.com/developerworks/data/library/cognos/reporting/advanced_report_design/page605.html
This works well in scenario where reports are being developed by Cognos report authors.
What can be done in case of ad-hoc reporting where user can pull the columns across the different areas and loop comes into picture given the case if only say one Product folder is published.
How do we take care of this?
Quote from: tjohnson3050 on 22 Jul 2013 10:22:34 AM
There is nothing wrong with having multiple fact tables sharing many conformed dimensions. It is good to understand how Cognos handles it. Basically, each star schema (single fact table and it's dimensions) will be identified and treated as a separate query, then all the separate star schema queries will be stitched together. Stitching is outer joining the different star schema queries on the conformed dimensions.
Cognos identifies a query subject as a fact table when all relationships involved in a query are defined with 1:n or 0:n cardinality.
More detailed information on stitch queries in Cognos:
http://www.ibm.com/developerworks/data/library/cognos/reporting/advanced_report_design/page605.html
I can give several examples where it might go a horrible way when you have several facts and conformed dimensions in one namespace. For example if you use complex reports with multifact multidimensions. You will never know for sure which way the fact tables will be stitched (usualy the wrong way) when user randomly selects data items.
I would sugest using 1 namespace for each project and publish it in separate packages this would give users a simplier view and they can drag any data items and it would still show the right results.
Also i noticed you use folders for different facts. Do you use business layer and presentation?
Quote from: velniaszs on 23 Aug 2013 04:11:38 AM
I can give several examples where it might go a horrible way when you have several facts and conformed dimensions in one namespace. For example if you use complex reports with multifact multidimensions. You will never know for sure which way the fact tables will be stitched (usualy the wrong way) when user randomly selects data items.
I would sugest using 1 namespace for each project and publish it in separate packages this would give users a simplier view and they can drag any data items and it would still show the right results.
Also i noticed you use folders for different facts. Do you use business layer and presentation?
I would suggest you spend more time learning how Cognos Stitch queries work. If you only publish packages based on a single fact, you are missing out on some of the key functionality in Cognos. If you design your model correctly, you will get very consistent stitch queries every time using multiple facts and multiple conformed dimensions.
If you need to report on a dimension that has no relation to one of the multiple fact tables in your query, you need to understand how the stitch query will handle it. Again:
http://www.ibm.com/developerworks/data/library/cognos/reporting/advanced_report_design/page605.html
Quote from: tjohnson3050 on 24 Aug 2013 06:51:05 PM
I would suggest you spend more time learning how Cognos Stitch queries work. If you only publish packages based on a single fact, you are missing out on some of the key functionality in Cognos. If you design your model correctly, you will get very consistent stitch queries every time using multiple facts and multiple conformed dimensions.
If you need to report on a dimension that has no relation to one of the multiple fact tables in your query, you need to understand how the stitch query will handle it. Again:
http://www.ibm.com/developerworks/data/library/cognos/reporting/advanced_report_design/page605.html
Agree, agree, agree!!
We are big on conformed dimensions here and have models much uglier than that one. ;-)
Seriously though, a lot of our warehouse subject areas would be impossible to cover with one-fact-table packages. Yes, stitch queries can be problematic, but are certainly manageable. You may need to educate users on how their own data is structured, and in the end you cannot completely stop users who insist on doing dumb things. Empowering users can be risky business but in the end it makes your work a lot more interesting and I daresay fun, compared to spending hours every day writing reports. In my opinion, of course.
Although I just skimmed it, that article looked pretty good and would certainly be a good read if you are not familiar with more complex modeling.
Quote from: tjohnson3050 on 24 Aug 2013 06:51:05 PM
I would suggest you spend more time learning how Cognos Stitch queries work. If you only publish packages based on a single fact, you are missing out on some of the key functionality in Cognos. If you design your model correctly, you will get very consistent stitch queries every time using multiple facts and multiple conformed dimensions.
If you need to report on a dimension that has no relation to one of the multiple fact tables in your query, you need to understand how the stitch query will handle it. Again:
http://www.ibm.com/developerworks/data/library/cognos/reporting/advanced_report_design/page605.html
You completely missed my point. I do use multifact projects and i don't say not to use stitched queries. But for this particular situation where you have a lot of fact tables (lets say for the sake of argument 50) that it might be more elegant solution from a user perspective ( and cleaner) to have 5 packages rather than all in one.
Quote from: velniaszs on 23 Aug 2013 04:11:38 AM
I can give several examples where it might go a horrible way when you have several facts and conformed dimensions in one namespace. For example if you use complex reports with multifact multidimensions. You will never know for sure which way the fact tables will be stitched (usualy the wrong way) when user randomly selects data items.
I would sugest using 1 namespace for each project and publish it in separate packages this would give users a simplier view and they can drag any data items and it would still show the right results.
Also i noticed you use folders for different facts. Do you use business layer and presentation?
Thank you very much for your advice. It's the truth: user select items randomly and this behavior can report wrong results.
I could create create a package for each namespace but I'm afraid that is a big constraint.
As reguard as the folders, I use 3 layers: physical, business and presentation