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