Topic
17 replies Latest Post - ‏2013-09-27T16:29:25Z by kalyankanu
adennis
adennis
4 Posts
ACCEPTED ANSWER

Pinned topic Save the results in a table using Cognos

‏2009-08-13T19:04:19Z |
Do you know if a user can use the Cognos tool to process and enhance the database data? For example, we have some time-trending data collected and stored in a DB2 database. Now, I need to do some time series analysis of this time-trending data set and save the results in a DB2 table. Can I do this kind of data processing operations using Cognos tool?
Updated on 2012-07-09T12:05:44Z at 2012-07-09T12:05:44Z by dsg1002
  • SystemAdmin
    SystemAdmin
    15496 Posts
    ACCEPTED ANSWER

    Re: Save the results in a table using Cognos

    ‏2009-08-13T20:09:07Z  in response to adennis
    Cognos BI is for reporting (read-only). Cognos' tool "Data Manager" can do that, but it's an ETL tool that might be bigger than you need. Cognos BI "Event Studio" module says it can update a database with store procedures but I've never looked into how.
    • adennis
      adennis
      4 Posts
      ACCEPTED ANSWER

      Re: Save the results in a table using Cognos

      ‏2009-08-14T13:52:03Z  in response to SystemAdmin
      Thanks Angela

      Also i suspect that Cognos Planning could do the task of writing back into a DB, do you know is it possible?
      • SystemAdmin
        SystemAdmin
        15496 Posts
        ACCEPTED ANSWER

        Re: Save the results in a table using Cognos

        ‏2009-08-18T20:20:10Z  in response to adennis
        I have used basic stored procedure to save prompt values into a table of a db. But would not recommend for extensive updating DB work. Its too much pain.
  • mgibson
    mgibson
    592 Posts
    ACCEPTED ANSWER

    Re: Save the results in a table using Cognos

    ‏2009-08-14T03:42:23Z  in response to adennis
    You're kinda barking up the wrong tree in looking at Cognos to perform this sort of task. As Angela said, it's not designed for that sort of job.

    The only practical way to have Cognos involved is to prepare a report that renders the data in the desired format, and then to export it in CSV or MS Excel format, then you can use another tool (eg. MS Access) to get it into the DB.

    But this is a pretty dodgy practice to say the least! And better tools, such as ETL tools are in-keeping with best practice.

    Mick
  • mgibson
    mgibson
    592 Posts
    ACCEPTED ANSWER

    Re: Save the results in a table using Cognos

    ‏2009-08-19T05:21:42Z  in response to adennis
    Cognos Planning and TM1 (which has effectively replaced Cognos Planning) do have the ability 'write-back' to the DB, so perhaps tm1 is worth investigating.

    But according to best practice, any data manipulation should be handled by an ETL toolset.

    Regards
    Mick
  • VijayK.Aggrawal
    VijayK.Aggrawal
    25 Posts
    ACCEPTED ANSWER

    Re: Save the results in a table using Cognos

    ‏2009-08-21T09:24:40Z  in response to adennis
    Yes It is possible to update the database tables using cognos report studio...
    But it needs a procedure and javascript on report...
    I have implemented in few of my reports...
    if you want i will provide you with report XML...

    Regards
    Vijay
    • SystemAdmin
      SystemAdmin
      15496 Posts
      ACCEPTED ANSWER

      Re: Save the results in a table using Cognos

      ‏2009-09-01T05:45:52Z  in response to VijayK.Aggrawal
      Hello VijayK.Aggrawal

      Can you please send me the xml. This is very much required for our work.

      thanks
      • Cerbers_5
        Cerbers_5
        39 Posts
        ACCEPTED ANSWER

        Re: Save the results in a table using Cognos

        ‏2009-09-01T22:57:35Z  in response to SystemAdmin
        Along the lines of a previous post, its really not that complicated - you just use a stored procedure query subject. This is called by your report which passes in prompt values. Stored procedure query subjects were not intended to write to the database but they can.

        We do this a lot at clients where for example we need to write some system settings or kick of a process or do some minor data entry. I really don't think its that much of an issue.
        In a full end to end BI system with automation there really is a need to write back minor control settings and things.
        You can create large complex forms using this method if you wish but, at this stage that you should go the ASP (or whatever your favourite web language is) route.

        TM1 will let you write back to a TM1 cube. It won't let you write back to a database.

        Planning will let you write back to a Planning cube but it then has to be published to a database and there are some complexities there. It does enable some nice automation via macros from Cognos Connection but you'd need to have a planning expert to set it up for you.
      • SystemAdmin
        SystemAdmin
        15496 Posts
        ACCEPTED ANSWER

        Re: Save the results in a table using Cognos

        ‏2009-09-07T16:52:25Z  in response to SystemAdmin
        ciao

        do you have the VijayK.Aggrawal's xml?

        can you send me please?

        thanks, very much
    • SystemAdmin
      SystemAdmin
      15496 Posts
      ACCEPTED ANSWER

      Re: Save the results in a table using Cognos

      ‏2009-09-07T16:15:00Z  in response to VijayK.Aggrawal
      Hi Vijay Aggrawal

      I have the same problem :O(
      can you send me too the html with exmple...

      thanks very very much
    • NisarAhmed
      NisarAhmed
      1 Post
      ACCEPTED ANSWER

      Re: Save the results in a table using Cognos

      ‏2011-08-20T04:06:50Z  in response to VijayK.Aggrawal
      Could you please send detail how to update table using cognos. its urgent
    • SystemAdmin
      SystemAdmin
      15496 Posts
      ACCEPTED ANSWER

      Re: Save the results in a table using Cognos

      ‏2012-03-21T06:49:41Z  in response to VijayK.Aggrawal
      Hi Vijay:

      Could you help me send the report XML...?

      I also have the problem about how to write back to DB through cognos.

      I saw you said you finished.Could you help me?
    • dsg1002
      dsg1002
      1 Post
      ACCEPTED ANSWER

      Re: Save the results in a table using Cognos

      ‏2012-07-09T12:05:44Z  in response to VijayK.Aggrawal
      Hi Vijay,

      Can you please post the steps to achieve this functionality.

      Thanks
      dsg
    • kalyankanu
      kalyankanu
      4 Posts
      ACCEPTED ANSWER

      Can we insert data through cognos to db table

      ‏2013-09-27T16:29:25Z  in response to VijayK.Aggrawal

      Hi all, we have similar issue, I have some static tables, Can we insert data through cognos to oracle db? these tables would require to use for prompt selection...

       

      Appreciate your help.

      Thanks,

      Kumar

      Updated on 2013-09-27T16:30:22Z at 2013-09-27T16:30:22Z by kalyankanu
  • SystemAdmin
    SystemAdmin
    15496 Posts
    ACCEPTED ANSWER

    Re: Save the results in a table using Cognos

    ‏2009-08-21T16:13:58Z  in response to adennis
    cognos major studios are designed for analysis not for any db tasks. However it is possible you can do some db tasks in Event Studio which uses store procedure(SP) to update db. you need to set up threadholder to trigger SP.
    You may also be able to invoke SP in RS, i.e. use a drill through/prompt to pass in nessary parameter values to SP.
    the best way will be use cognos report output(csv or xml) then use DB2 or other db import tool. Much cleaner.And of course the most complex ones could be done with SDK combination.
  • RMeyers
    RMeyers
    194 Posts
    ACCEPTED ANSWER

    Re: Save the results in a table using Cognos

    ‏2009-08-21T17:48:34Z  in response to adennis
    On the topic of possibilities,

    You can create an ASP web service and use JScript to send data to the webservice which can do the database insert. Obviously this is a more complicated route then the Stored Procedure method but I was able on a simple test basis to get this working.
  • WasimIsmail
    WasimIsmail
    12 Posts
    ACCEPTED ANSWER

    Re: Save the results in a table using Cognos

    ‏2011-08-20T09:38:04Z  in response to adennis
    Nisar its possible through report studio

    And I dont recommend going into this, Cognos is not a user input application.
    You're exposing yourself to a whole lot of complications if do this.

    Create a stored procedure - that inserts values into your DB. The catch here is that the procedure must return a value to make cognos to think that its returning a set of data.

    Obviously you would be passing parameters to the procedure to insert those values.
    In framework manager import the stored procedure and link those parameters to prompts(in FM, using prompt macros).

    On the report define prompts that take in the values for the stored procedure on the prompt page.
    On the report page drag and drop the SP (as a list and enable "local processing" for its query)
    you can hide the list if you like.

    you're ready to go - there are a lot of possibilities here. You can mix and match with JS to enhance the functionality.