Cognos Analytics

 View Only
Expand all | Collapse all

Use SQL to Create Data Module

  • 1.  Use SQL to Create Data Module

    Posted Mon October 17, 2022 07:04 AM
    We just got Cognos 11.0.13 and I am trying to make some data modules for the group to use.  Is the drag n drop the only way to do this or is there a way to create it with SQL?  We have 513 fields in a table and I just want a DM with about 30, so it appears I will need to click and delete 483 fields to do this.  If not SQL is there another method?  I will need to do something similar in other tables as well.


    Brian

    ------------------------------
    Brian Hobbs
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Use SQL to Create Data Module

    Posted Mon October 17, 2022 10:23 AM
    Hello Brian,
    That's quite an old version. The most recent released version is 11.2.3 it has many more capabilities.
    In the newest version, the quickest way would be to build a Data Module on the Data source. It will give you tables that contain all the columns. Then create a view using this table, it will show a dialog where you can select the columns that you want in the view. 
    In the newer versions you can also create tables using SQL, though I find that a more cumbersome way to operate.
    In 11.0.13 you're pretty much stuck with the approach that you described.

    ------------------------------
    Kind regards,
    Henk Cazemier
    ------------------------------



  • 3.  RE: Use SQL to Create Data Module

    Posted Mon October 17, 2022 12:29 PM

    Yes that is the approach which you would need to take.  SQL tables are there for situations where no other approach is possible.  

    If you are familiar with FM modelling best practices, there are some analogues.  The loaded schemas are like the data base layer, with a similar purpose, in which these objects are referenced by the query engine to avoid unnecessary metadata callbacks. SQL tables will tend to preclude that functionality.

    The query subjects which you use in the module itself are like the modelling layer.  In FM you could create relationships between the query subjects in the data base layer, using shortcuts as aliases, or you could just have the data source query subjects in the data base layer and define the relationships between the query subjects in the modelling layer.  The latter is quite similar to how you would go about things in a data module.  

    Much of what you need to do in modelling is determined by the structure of your data warehouse or whatever else you have available although a data warehouse, properly designed for query response, will be what will be best.

    513 is a rather large number of columns.  What do they contain?  Why are they there in your data warehouse when you will just need to hide or remove them?



    ------------------------------
    IAN HENDERSON
    ------------------------------



  • 4.  RE: Use SQL to Create Data Module

    Posted Thu October 20, 2022 09:38 AM
    Thanks, that is helpful.  Most of the 513 are not used they just come with the COTS Product - Maximo that we use.  I think people too often think, 'Wow, think of all the data we can store' instead of thinking: 'that many fields will lead to confusion and inefficiency, we need to trim it down.'

    ------------------------------
    Brian Hobbs
    ------------------------------



  • 5.  RE: Use SQL to Create Data Module

    Posted Tue October 18, 2022 09:37 AM
    Why not create a view in the database, with the columns you need, and import that into data module?

    ------------------------------
    Sandeep DHIRAD
    ------------------------------



  • 6.  RE: Use SQL to Create Data Module

    Posted Thu October 20, 2022 09:41 AM
    A view is a good idea, although I would prefer a reporting tool (as the case is with say tableau or BIRT) that the report designer can do that kind of thing for themself.  Most of our report people don't have the rights to make views.

    ------------------------------
    Brian Hobbs
    ------------------------------



  • 7.  RE: Use SQL to Create Data Module

    Posted Tue October 18, 2022 12:21 PM
    Hi Brian,

    I'm curious why your company went with 11.0.13 and not the 11.1 or 11.2 branches.

    While 11.2 is tagged as the "unstable, fast-changing" branch, 11.1.7 is tagged as the stable, long-term release for 11.1.x.  "Stable" kind of being a misnomer as we've been stuck on 11.1.5 for 2 years now because of show-stopper (for us) bugs first in 11.1.6 and carried over into 11.1.7.  Even on FixPack 5 I've had 1 support ticket open for over 2 months now and have 5 more tickets yet to open.  Which is why we're not even considering the 11.2.x branch with it known as being even less stable.

    Again, I'm just interested to know why you went 11.0.x

    Regards,

    Wayne

    ------------------------------
    Wayne Westlake
    ------------------------------



  • 8.  RE: Use SQL to Create Data Module

    Posted Thu October 20, 2022 09:45 AM

    Thanks Wayne,

    It is an old version because nobody has asked that question.  I was just told yesterday we can and will get upgraded to 11.1.7, although it won't be right away because 'it will take a lot of resources to do the upgrade'.  This sounds fishy, is it really a big job to upgrade from 11.0.13 to 11.1.7?

    Brian



    ------------------------------
    Brian Hobbs
    ------------------------------



  • 9.  RE: Use SQL to Create Data Module

    IBM Champion
    Posted Fri October 21, 2022 05:45 AM
    If you consider moving to 11.1.7 LTS it may be worth waiting a couple of weeks more to get 11.2.4 LTS :-)

    ------------------------------
    Philipp Hornung
    ------------------------------



  • 10.  RE: Use SQL to Create Data Module

    IBM Champion
    Posted Fri October 21, 2022 06:26 AM
    Hi,

    Taken from Ask-Me-Anything: Cognos Analytics (October edition):
    Cognos Analytics v11.2.4 is planned for at the end of this year, likely early mid-December. This version will be LTSR (Long Term Support Release).

    Best regards,

    ------------------------------
    Patrick Neveu
    Positive Thinking Company
    ------------------------------



  • 11.  RE: Use SQL to Create Data Module

    Posted Mon October 24, 2022 12:41 PM
    Hey Brian,

    Without knowing your environment I can't answer that question.  For us to go from 11.0.13 to 11.1.5 was pretty straightforward.  Just did a fresh, clean install of 11.1.5 and then brought Cognos up on the existing content store and it did the upgrade.  Then we had to run a Report Upgrade from the Administration --> Configuration --> Content Administration portal.

    We hadn't played with DataSets so there was none of that to upgrade.

    The only real work was we had to recreate a few dozen Events because there was a bug in 11.0.12 that corrupted Events and some Jobs if you edited them.  We worked with IBM on several patches of 11.0.13 when they finally said they weren't going to fix it in 11.0. and we needed to move to 11.1.

    Before you cement plans to upgrade test the heck out of the version you plan to go to.  We so far haven't found a stable release since 11.1.5 - well, actually we had issues on 11.1.5 and finally got an Interim Fix.  We've been on that for over 2 years.  Numerous tickets later and we're still trying to get bugs fixed, now trying 11.1.7 FixPack 5.  11.2.4 might be the LTS but the 11.2.x branch was known as the "unstable" branch, which is ironic since 11.1.7, being the stable branch, is anything but!  XD

    Regards,

    Wayne

    ------------------------------
    Wayne Westlake
    ------------------------------



  • 12.  RE: Use SQL to Create Data Module

    Posted Thu October 27, 2022 11:55 AM
    Thanks Wayne, that's...interesting.  Right now we don't use Cognos for production reporting at all, it's a new tool for us meant to allow business users to make their own reports, so at least we can't break anything.

    ------------------------------
    Brian Hobbs
    ------------------------------



  • 13.  RE: Use SQL to Create Data Module

    Posted Fri October 21, 2022 11:52 AM
    Hi Wayne,

    Can you share the open issue you see on 11.1.7 FP5. We are planning for FP1-> FP5 upgrade want to make sure these wont impact our migration.

    Rajesh

    ------------------------------
    Rajesh Kumar Vanam
    ------------------------------



  • 14.  RE: Use SQL to Create Data Module

    Posted Mon October 24, 2022 12:31 PM
    Hi Rajesh,

    This isn't specific to FixPack 5, it's long been an issue but we have a good dozen or two Events, Jobs and Reports fail every day with "java.lang.NullPointerException".

    Our ticket is nearly 4 months old and IBM is still "working on it".  They did log it as an APAR (software defect) but so far no answers.

    Other failures we get:

    CNC-SDS-0403 Unable to add history details in service [monitorService]
    QE-DEF-0285 The logon failed.
    CNC-ASV-0025 Agent Condition invalid: CNC-MON-0035 Invalid request.
    Unavailable in a job step that is a legitimate job/report (one was every single step in the job)
    Zombie processes that persist even after a reboot

    Whether these are specifically due to FixPack 5 I can't say for certain, but IBM requires a separate ticket for each, so for now we're working on the java.lang.NullPointerException since it's the most frequent.

    I mentioned we've been stuck on 11.1.5 IF1004 because starting with 11.1.6 and up until 11.1.7 FixPack 5, our schedules and events would stop running after about 8-12 hrs unless someone logged into the Cognos portal.  It took IBM and year and a half to fix it - that fix being in FP5.  Well, they never actually fixed the error but added code that looks for a failure and then does a retry - the retry keeps the schedule going but things can run 3, 6, 9, 12, etc minutes later than scheduled.

    We had looked at 11.2.1 but the issue was there as well.

    We may be forever on 11.1.5 until we are forced to scrap Cognos.  All these fancy whistles and bells they're adding are no good if the core product doesn't work.  And I've seen in the community we're not the only ones having these issues.

    Regards,

    Wayne

    ------------------------------
    Wayne Westlake
    ------------------------------



  • 15.  RE: Use SQL to Create Data Module

    IBM Champion
    Posted Thu November 10, 2022 06:59 AM
    Hi, Are you sure the errors are version specific and not update specific? In some cases Gateway, Cognos Admin or Cognos Configuration settings may be outdated and can have an impact.

    ------------------------------
    Philipp Hornung
    ------------------------------