If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Only one table with all information

Started by dssd, 08 Mar 2013 11:40:10 PM

Previous topic - Next topic

dssd

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

cognostechie

Good for a single report, bad for everything else  ;D

dssd

Could you please give details on why its good and bad

blom0344

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? 

dssd

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?

CognosPaul

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.

Rahul Ganguli

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

dssd

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


dssd

One last question. What happens if i join fact to fact without any conformed dimension

blom0344

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?