We have a number of Cognos 10 reports that are currently driven by stored procedures. These stored procedures have cross-database dependencies to a database that is core to the organization. This core database and the application it supports are well past their best before date and are being replaced with a new system. This system is off-site and we don't have access to the database. We do however have an odata service that we can consume to access this data. We still need to access other data via the stored procedure.
Does anyone have a suggestion as to how we can refactor our reports/stored procs/other to deal with this?
My first instinct would be to refactor your reports to reflect that the data comes from 2 separate sources now, by modifying your stored proc to access only the local data, and writing a new WCF DataServices wrapper to access the newly off-site OData service. Then refactor your reports to consume both the updated stored proc, and the new WCF DataServices wrapper. For more info on using WCFDS in general start here: http://msdn.microsoft.com/en-us/library/cc668794.aspx, and for a quick start example of a client take a look here: http://msdn.microsoft.com/en-us/library/dd728278.aspx
Hope this helps!
Mike Reed