I have a requirement where we would have only one table with both dimensional and factual information.. Is it good or bad for Cognos? There is no need to join with any other table
Good for a single report, bad for everything else ;D
Could you please give details on why its good and bad
In most cases the database will make sure data is joined between tables; the resultset (often aggregated) is then returned for handling by Cognos. It is extremely naive to think that storing everything within 1 big table would solve anthing. There are possibly more drawbacks, since you will need to scan the entire table to come up with lists of prompt values if you need these. Depending on the size of the table and whether indices exist, this may be very slow compared with drawing these values from relative small dimension tables. You need both more data AND index space as well. So, why store data this way?
I have been provided such a table. I didnt design. I feel its wrong. Prompt, duplicate data hence more space are two issues i see, as you mentioned. Any else?
No possibility of multi grain facts is the biggest problem I can see.
If you have a columnar database, like Greenplum, then there won't be any table scans when pulling prompt info. That being said, it's still a bad idea. A single large fact table will make your model very delicate. How often is data entered? You also lose the ability to play with the model and find unexpected ways of presenting the data.
Not a good idea to go with a single table approach.
If you have dimension ids present in the table, you can ask your Database person to create dimension tables from the existing table. And you can model and create relationship between these table in Framework Manager.
But, I think before jumping into report development we should always do a warehouse design as per the requirements.
Regards,
Rahul
Quote from: PaulM on 10 Mar 2013 04:53:53 PM
No possibility of multi grain facts is the biggest problem I can see.
Could you elaborate on the above
Here is a link explaining what multigrain facts in detail; http://pic.dhe.ibm.com/infocenter/cbi/v10r1m1/index.jsp?topic=%2Fcom.ibm.swg.ba.cognos.ug_fm.10.1.1.doc%2Fc_dim-multifact-multigrain.html
Thanks,
RK
One last question. What happens if i join fact to fact without any conformed dimension
That is not a supported type of model. Cognos needs the conformed dimension to generate a stitch query (create 2 sets; roll them up and 'stitch'). You will get bloated measures by overcounting due to the n:m cardinality.
But, in all honesty, is there a reason to combine data from 2 facts within your report queries in the first place?