Cognos Data Source question

[Accpac2010]

Hi all,
I am planning to use Cognos to create reports for our finance group. I want to get some views whether I should use the financial database as the data source or should I create a new database only for reporting where I copy the desired tables into it. The requirement are to drill down to the line details so I am kind of concerned to have to copy daily transaction tables to the new database.
What's the best practice in this regard,
Thanks in advance.
Bruce

 

[drlex]

Bruce,
The choice is often made by the report users and/or the DBA. Use of the financial database directly will give you "live" data, but this can bring with it two issues
- a report can have different values at different times of the day (underlying data has changed);
- performance, as you are "competing" with other users for DB access.
Be wary that what starts as a couple of reports can often end up in an entire analysis suite, with the resultant draw of the DB services.
Lex

soi là, soi carré

 

[blom0344]

If the financial analysis permits it (the latency), then there is also the option to perform a complete database copy overnight. On Oracle/Unix this was called a 'flashcopy'. (No idea how they did it, but Terabytes of data where involved.)

The benefit was the ability to use existing models and reports on 'life' data for special purposes (like in-time fraude detection)


Ties Blom

 

[Accpac2010]

If I understand correctly does this mean I have to create a daily copy of the operation transaction database to a database lets say copy_operation and off of that create another database that would be like datawarehouse lets say reporting_datase with less joins for reporting? Is this correct.
Bruce

 

[blom0344]

No ,the idea is that transaction database and reporting one have the same structure. Performance may be an issue then, but with the right setup heavy queries will not interfere with transactional traffic.

As said, this is a heavy compromise, not a substitute for a real DWH (that is designed for optimized reporting)

Ties Blom