Cognos Analytics

Cognos Analytics

Connect, learn, and share with thousands of IBM Cognos Analytics users! 


#Product
#Analytics
#CognosAnalytics
#Analyticstools
#TechXchange Partner
#TechXchange Session
#TechXchange Presenter
#AI
 View Only
Back to discussions
Expand all | Collapse all

Modelling in Framework Manager

  • 1.  Modelling in Framework Manager

    Posted 03/16/21 10:05 AM
    Hi all,

    I am looking for a solution to the following problem.

    From my database I get a table in which I get 1 - N entries for an ID. If I evaluate this table in reporting, e.g. with a repeating element table, the performance is poor.

    I would therefore like to concatenate elements into an ID.

    original
    ID   text
    01   ABC
    01   4711
    01   D47 FFF
    02   ZGT
    02   DD4 DSD
    03   THG
    and so on.

    Target
    ID   text
    01   ABC, 4711, D47 FFF
    02   ZGT, DD4 DSD
    03   THG
    04   ....

    Does anyone have any idea how I can best do this in Framework Manager?

    Thanks a lot 

    Peter

    ------------------------------
    Peter Hess
    ------------------------------

    #CognosAnalyticswithWatson


  • 2.  RE: Modelling in Framework Manager

    Posted 03/16/21 11:19 AM
    Hi Peter,
    Take a look at the thread in the link. Maybe this helps.
    You probably end up creating a view in Framework Manager.
    Repeater Object Issue in the CSV

    ------------------------------
    Thomas van der Meer
    ------------------------------



  • 3.  RE: Modelling in Framework Manager

    Posted 03/16/21 11:34 AM

    Hi Thomas,

     

    that looks like a great idea. I'll test it very soon.

     

    Peter Hess

     

    ITC/TG

    Security MB Trucks D&A Boards TE-TG

     

    Daimler Truck AG

     

    Tel.:   +49 160 8647725

    Mail:  peter.ph.hess@daimler.com

     




    Original Message


  • 4.  RE: Modelling in Framework Manager

    Posted 03/16/21 10:02 PM
    Hi Peter,
    This question came up a while ago in another conversation:
    https://community.ibm.com/community/user/businessanalytics/communities/community-home/digestviewer/viewthread?MessageKey=4a212db4-2b70-426e-a521-ed44040e252a&CommunityKey=6b10df83-0b3c-4f92-8b1f-1fd80d0e7e58&tab=digestviewer#bm4a212db4-2b70-426e-a521-ed44040e252a
    // Henk

    ------------------------------
    HENK CAZEMIER
    ------------------------------



  • 5.  RE: Modelling in Framework Manager

    Posted 03/17/21 04:19 AM
    Hi Peter, 

    As Henk mentioned, you can use string_agg() method or depending on your database, you can use join/concat etc methods and create a view in the database (or a database query subject in FM with a direct sql) and use it in FM models. Typically anything done to data at database will be faster than doing the same using cognos methods. 

    Regards

    ------------------------------
    Kiran Passumarthi
    www.linkedin.com/in/kiranpassumarthi
    ------------------------------

    Original Message


  • 6.  RE: Modelling in Framework Manager

    Posted 03/19/21 11:07 AM

    Hi Kiran,

     

    unfortunately I cannot do anything on the database side – I have to solve the problem on Framework Manager. Your ideas are not running for me.

     

    THX

     

    Peter

     




    Original Message


  • 7.  RE: Modelling in Framework Manager

    Posted 03/22/21 02:36 AM
    Hi Peter, 

    As mentioned in my earlier post, whatever view you would want to create in the database, use that same SQL to create a database query subject in FM instead. That way you will not have any dependency on the database (or the DB/DBA team) to create views. 

    Regards

    ------------------------------
    Kiran Passumarthi
    www.linkedin.com/in/kiranpassumarthi
    ------------------------------

    Original Message


  • 8.  RE: Modelling in Framework Manager

    Posted 03/19/21 11:08 AM

    Hi Henk,

     

    unfortunately I cannot do anything on the database side. The problem has to be solved in Framework Manager itself. Your ideas are not running for me.

     

    Best Regadrs Peter

     




    Original Message


  • 9.  RE: Modelling in Framework Manager

    Posted 03/22/21 07:58 AM
    using a DB2 as database this SQL (passthrough) 
    with "SQL2" as (
    select ID, string1
    from ( SELECT
    ID,
    string1
    FROM (
    select "ID" as ID, "string2" as string1 FROM (

    select '0815' as ID, '1234' as string2
    from SYSIBM.SYSDUMMY1
    union all

    select '0815' as ID, '5678' as string2
    from SYSIBM.SYSDUMMY1

    ) ) as t1

    ) t2

    )
    select "SQL2"."ID" "ID" ,
    xmlserialize( xmlagg(xmltext(CONCAT( ', ',STRING1))) as CHAR LARGE OBJECT (200) ) AS "keyword concatenated"

    from "SQL2" GROUP BY "SQL2"."ID" FOR FETCH ONLY

    results in  
    | 0815 | , 1234, 5678 |

    The SQL can be used in Frameworkmanager or Report-SQL.

    In my tests I had to use the option "pass-through" instead of "native".
    The part: 
    select 1 as ID, 2 as string1 FROM (

    select '0815' as ID, '1234' as string2
    from SYSIBM.SYSDUMMY1
    union all

    select '0815' as ID, '5678' as string2
    from SYSIBM.SYSDUMMY1
    Should be replaced by some query against your real database, e.g. select t1.ID as ID, t2.car_color as string2 FROM mydb.table1 as t1 join mydb.table2 as t2 on table1.ID = table2.ID

    The trick is done by GROUPING the ID column and using xml-functions to concat the rows.

    On Oracle there are other functions, that do the trick. See: https://stackoverflow.com/questions/1076011/

    Speaking in general, the steps to be done are similar. Have a SQL that is grouped and use database functions to transform the results from the rows to just row haveing the former results concated.

    Hope this helps.


    ------------------------------
    Ralf Roeber
    https://linkedin.com/in/ralf-roeber-470425a/
    ------------------------------

    Original Message


  • 10.  RE: Modelling in Framework Manager

    Posted 03/24/21 09:15 AM
    Good Morning,

    I am not an export on embedding subqueries, but if you can not perform those tasks at the database, then here is my suggestion:

    Warm Regards,
    Eli



    Original Message


  • 11.  RE: Modelling in Framework Manager

    Posted 03/23/21 12:27 AM

    Henk's reply suggested using a vendor specific function which transposes several rows into a single column value.

    Since 10.2.2-era, Dynamic Query has provided optional syntax which can precede referencing a vendor function name in an expression.

    Specifically, indicating that the expression must be generated in a SQL query block which is trying to GROUP data.

    ORACLE, DB2 - see LISTAGG

    MySQL - see GROUP_CONCAT

    PostgreSQL - see STRING_AGG

    ....

    I suspect you may want the windowed versions vendors offer, plus potentially their suboptions, which means you would be using type-in SQL at that point.

    Other techniques, such as turning into an XML fragment, PIVOT etc are just other methods you may see on the net. May deliver look-and-feel but your mileage may vary re performance. Hence, best to test outside of CA first re your typical list sizes etc.

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/LISTAGG.html#GUID-B6E50D8E-F467-425B-9436-F7F8BF38D466

    https://www.ibm.com/support/producthub/db2/docs/content/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0058709.html



    ------------------------------
    NIGEL CAMPBELL
    ------------------------------

    Original Message


Related Content