Jim Lawrence (AccessD)
accessd at shaw.ca
Mon Jun 14 19:41:32 CDT 2004
That is why it is better if you can start off copying the SQL code, out of the Cognos report, that is used for the report. If there are references to various views they will be listed, in the sequel script and they will run just the same in Access. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Fred Hooper Sent: Monday, June 14, 2004 2:46 PM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] ODBC - COGNOS If you don't have an equivalent view, Access will try to bring down too much of the database and take longer. However, if you look at the SQL tab of the current report's data source (there's a button that shows this), that tab should provide the SQL that you need in the view. If you give this SQL code to your dw people they should be able to provide the view in a few minutes. |-----Original Message----- |From: accessd-bounces at databaseadvisors.com |[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of |Mitsules, Mark S. (Newport News) |Sent: Monday, June 14, 2004 5:37 PM |To: 'Access Developers discussion and problem solving' |Subject: RE: [AccessD] ODBC - COGNOS | | |I'm definitely not creating cubes then...just straight |reporting. As for efficiency, I'm guessing I'm in trouble |then, because the current Cognos report has taken up to five |minutes to run. Who knows how long it will take if I try and |do this from Access. Perhaps my current method will prevail |based on time, not simplicity. | | |Mark | |-----Original Message----- |From: Fred Hooper [mailto:fahooper at trapo.com] |Sent: Monday, June 14, 2004 4:56 PM |To: 'Access Developers discussion and problem solving' |Subject: RE: [AccessD] ODBC - COGNOS | | |Impromptu is a reporting program. It can draw from many |different databases and files. (Not, peculiarly, from cubes.) | |A "catalog" is a file that tells Impromptu where to get data from. | |While Impromptu can create several types of files, mostly |reports, one of them is commonly used to provide data for cube |building -- an IQD file. This file is used by the cube |building program, Transformer, to read the data and create the cube. | |"Going directly against the database" could most easily be |linking Access to the database and running queries, views, |etc. against the data in the database. For example, you can |link Access to SQL Server, Oracle, etc. The tables then appear |to be local in the Accsss database. If the database is large, |efficiency considerations would lead you to create views or |stored procedures to access the data. Impromptu runs its SQL |directly in the database and doesn't need this step for efficiency. | | ||-----Original Message----- ||From: accessd-bounces at databaseadvisors.com ||[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of ||Mitsules, Mark S. (Newport News) ||Sent: Monday, June 14, 2004 4:26 PM ||To: 'Access Developers discussion and problem solving' ||Subject: RE: [AccessD] ODBC - COGNOS || || ||>> Good guess, this is exactly how we do it. << || ||I guess I'm having trouble with the terms. Cube, catalog, ||database... Currently I use Cognos Impromptu which connects to ||a catalog, then runs the report. How does "catalog" relate to ||a "cube" or "going directly against the database". Forgive me ||if these are simplistic questions...I don't work in data ||warehousing. Since my only experience with getting data out ||of Cognos has been with running Cognos reports, can you ||elaborate on "exactly how (you) do it"? As I said, my end ||goal would be to try and connect from Access to "something" ||directly and return the records, rather than the two-step ||process I described earlier. || ||Mark || || || ||-----Original Message----- ||From: Hale, Jim [mailto:Jim.Hale at fleetpride.com] ||Sent: Monday, June 14, 2004 4:00 PM ||To: 'Access Developers discussion and problem solving' ||Subject: RE: [AccessD] ODBC - COGNOS || || ||<My guess is, if you don't want to go against a cube, you're ||probably better off going directly against the database.> Good ||guess, this is exactly how we do it. Jim Hale || ||-----Original Message----- ||From: Fred Hooper [mailto:fahooper at trapo.com] ||Sent: Monday, June 14, 2004 1:40 PM ||To: 'Access Developers discussion and problem solving' ||Subject: RE: [AccessD] ODBC - COGNOS || || ||Mark, || ||If you want to use a report, Impromptu (the program that ||writes the catalogs), has an interface. You'd have to consult ||it's documentation to find out if you can return a recordset. ||I haven't used it but almost took a project automating report ||builds using Access and I checked that there was an interface ||to permit doing so at that time. || ||If you want to get data from a cube, PowerPlay has an ||interface that allows it. || ||If the report is long or complex, you may want to go directly ||against the database. (I once needed 5,000 cross tab reports ||on 80 tabs in one spreadsheet. Testing indicated a 3 week run ||time against the cube and writing individual cross tabs. It ||takes an hour directly against Sql Server using its "runsql" ||command and Excell's CopyFromRecordset once for each ||tab.) My guess is, if you don't want to go against a cube, ||you're probably better off going directly against the database. || ||If you check the proper Cognos reference, Access is an ideal ||environment to manipulate Cognos' programs from because of the ||ease of checking local tables to determine what to do. VB also ||works well (e.g. I use it to copy cubes, stopping and starting ||various Cognos services to enable the copy even though the ||cube is in use.) The IDE is a vast improvement over Cognos' ||macro facility. || ||Fred || |||-----Original Message----- |||From: accessd-bounces at databaseadvisors.com |||[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of |||Mitsules, Mark S. (Newport News) |||Sent: Monday, June 14, 2004 1:08 PM |||To: [AccessD] |||Subject: [AccessD] ODBC - COGNOS ||| ||| |||Group, ||| |||Has anyone ever returned a recordset from a Cognos catalog? |||Suggestions? ||| |||Mark |||-- |||_______________________________________________ |||AccessD mailing list |||AccessD at databaseadvisors.com |||http://databaseadvisors.com/mailman/listinfo/ac|cessd |||Website: |||http://www.databaseadvisors.com ||| || ||-- ||_______________________________________________ ||AccessD mailing list ||AccessD at databaseadvisors.com ||http://databaseadvisors.com/mailman/listinfo/accessd ||Website: http://www.databaseadvisors.com ||-- ||_______________________________________________ ||AccessD mailing list ||AccessD at databaseadvisors.com ||http://databaseadvisors.com/mailman/listinfo/accessd ||Website: http://www.databaseadvisors.com ||-- ||_______________________________________________ ||AccessD mailing list ||AccessD at databaseadvisors.com ||http://databaseadvisors.com/mailman/listinfo/accessd ||Website: http://www.databaseadvisors.com || | |-- |_______________________________________________ |AccessD mailing list |AccessD at databaseadvisors.com |http://databaseadvisors.com/mailman/listinfo/accessd |Website: http://www.databaseadvisors.com |-- |_______________________________________________ |AccessD mailing list |AccessD at databaseadvisors.com |http://databaseadvisors.com/mailman/listinfo/accessd |Website: http://www.databaseadvisors.com | -- _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com