Hi everyone,
I'm a relatively new developer and have been tasked with writing new Cognos Framework to assist our business with a lot of pain points we currently have. So I may have additional questions down the road if ya'll are willing to help.
I've been working with Report Studio and SQL querying our data warehouse for a few years now and become very intimate with our data and it's structure. I've also attended the Framework Manager IBM training and have the 8.4 books. I really wish they let us take a copy of the example framework too as reference.
So one thing I believe I've discovered, is that if a Fact table does not have any measures, simply referencing dimensional information based on date spans, it performs much better with accurate results if I keep it's relationship 1..1 and not 1..n to it's related dimensions (this is often used with another fact and it prevents the stitch query which was not returning accurate results due to a nonconformed dimension not repeating to match a different granularity).
So far, all my joins are 1..1 or 0..1 unless there are measures involved, and then I make those ..n .
I am now working with a Fact table that has a large amount of attributes and measures on it and am trying to figure out how to relate this to the rest of my database. One thought was to split this table into a virtual fact and virtual dimension of itself splitting the measures and attributes apart and thus causing a different join depending which data is pulled. This concerns me though because it's a rather large table (60 Mil rows) and calling it twice with alias may cause a performace hit. Also the massive amount of joins I would need to duplicate...
Would anyone be willing to share insight into the Cognos Framework version of Facts (..n) and Dimensions (..1) and how they interact with each other?
Oh and one more question... if two facts have two ( or more) shared dimensions, and both dimensions are called, how does the framework resolve that?
I hope this is the appropriate place for these questions :)