Monday, July 19, 2010

Required a Property for Relational querying !!!

I so wish there was a Property or setting for Relational Querying vs Dimensional Querying. We are dealing with a model that is ER modeled and is based on an OLTP system. We need to build an ad-hoc model based on this. And as you know this model is not supported by Cognos. So don't you think if there was a setting in FM that instructed Cognos to generate ER based queries rather than identifying facts and dimensions based on cardinalities that would make life so much simpler.

I mean, just think if there are 3 tables A, B, C and they are joined to each other, you would expect a query that relates the 3 tables based on the joins. I do not want Cognos to identify if A/B/C is a fact / dimensions and break-up my query.

I know you would ask me to move to a dimensional model but in our case it doesn't even make sense. The OLTP system doesn't even deal with millions of rows nor do we have an ETL system. SO moving to a dimensional model would mean a lot of expenditure in terms of architecting, getting an ETL tool, designing and developing ETL workflows, time, resources involved and so on...Just wish there was a setting..
 

2 comments:

Anonymous said...

Hello Zephyr,

We are in the same situation where we need to build reports out of an OLTP. Developing DWH or creating DMR model in FM would be time taking process and requires expertise. So, would like to know if you were able to figure out a solution for the above

Thanks in Advance...
Madhav

Zephyr said...

Hi Madhav, Unfortunately there is no work-around for this.

If you are planning on building a model for canned reports you can still get around the quirks by modelling queries the way you want in RS, but if you are planning on exposing the model for ad-hoc querying then I would suggest pushing for a DWH else your queries can get unpredictable.