16 Replies Latest reply: Aug 14, 2015 6:52 AM by Priyanka Rao RSS

    Nested Apply Map !?

    Priyanka Rao

      Hi,

      I have a Table with over 30 million records.

      It is to be used as to crete multiple ApplyMap wherein the checking field (the first field) is always the same for those ApplyMaps.

      Cannot use the Associative concept here.

       

      Is there any way to optimize it , so that the Table is loaded only once but still maintain the ApplyMap.

       

      Appreciate any help.

      Thanks !

        • Re: Nested Apply Map !?
          Krishna Nagulapally

          So your 30mil record table is a mapping table or other fact/dimension table?

          • Re: Nested Apply Map !?
            Massimo Grossi

            don't think

            you can try to store the fields you need (first field + .....) in a qvd and load many times from that qvd (for the mapping tables) instead of many resident load

            • Re: Nested Apply Map !?
              Clever Anjos

              Would you mind sharing your script?

                • Re: Nested Apply Map !?
                  Priyanka Rao

                  Hi,

                   

                  Its not a complex script actually.. its just the Data that is huge !

                  For your assistance I have mocked it ;

                   

                   

                  TabA:

                  Key_ID,

                  Sys_Key,

                  Sys_Name,

                  Sys_Description;

                   

                  GetSysKey:

                  Mapping Load

                  Key_ID,

                  Sys_Key

                  resident TabA;

                   

                  GetSysName:

                  Mapping Load

                  Key_ID,

                  Sys_Name

                  resident TabA;

                   

                  ....So on.

                   

                  These  Mapping tables will be used to fetch the respective fields... For all the Mapping Tables 'Key_ID' Is the Matching Key.

                  Is it helpful ?

                    • Re: Nested Apply Map !?
                      Clever Anjos

                      As maxgro suggested, you could retrieve them from your QVD that could be more efficient than a resident read

                       

                      GetSysKey:

                      Mapping Load

                      Key_ID,

                      Sys_Key

                      from yourqvd(qvd);

                       

                      GetSysName:

                      Mapping Load

                      Key_ID,

                      Sys_Name

                      from yourqvd(qvd);


                      and so on


                        • Re: Nested Apply Map !?
                          Massimo Grossi

                          I suggested from qvd because usually is faster than resident but, after some test, it seems that creating the mapping table with a resident load is faster than creating the same tables from a qvd

                          The reason seems to be the mapping load from a qvd isn't optimized (without mapping 00:00:35).

                           

                           

                          this is the log with the time, 10M (not 30M) of records

                           

                          12/08/2015 21:25:16

                          TabA << AUTOGENERATE(10000000) 10.000.000 lines fetched

                          12/08/2015 21:26:24

                          time create TabA=00:01:08

                          12/08/2015 21:26:24

                          12/08/2015 21:26:54

                          time create qvd=00:00:30

                          12/08/2015 21:26:54

                          GetSysKey << TabA 10.000.000 lines fetched

                          GetSysName << TabA 10.000.000 lines fetched

                          GetSysDescription << TabA 10.000.000 lines fetched

                          12/08/2015 21:28:41

                          time create mapping table resident=00:01:47

                           

                          12/08/2015 23:36:08

                          GetSysKey << Taba 10.000.000 lines fetched

                          GetSysName << Taba 10.000.000 lines fetched

                          GetSysDescription << Taba 10.000.000 lines fetched

                          12/08/2015 23:38:18

                          time create mapping table qvd=00:02:10

                           

                           

                           

                          and this is the script if someone else want to check on his pc (you have to comment / uncomment the script to test the 2 diffferent way)

                           

                           

                          SET ThousandSep=',';

                          SET DecimalSep='.';

                          SET MoneyThousandSep=',';

                          SET MoneyDecimalSep='.';

                          SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

                          SET TimeFormat='hh:mm:ss';

                          SET DateFormat='DD/MM/YYYY';

                          SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

                          SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

                          SET DayNames='lun;mar;mer;gio;ven;sab;dom';

                           

                          ////////////  make 10M records qvd

                          //let start=now(1); trace $(start);

                          //

                          //TabA:

                          //load

                          //rowno() as Key_ID,

                          //'key ' & rowno() as Sys_Key,

                          //Hash128(rand()*100000) as Sys_Name,

                          //Hash128(rand()*10000) as Sys_Description

                          //AutoGenerate 10000000;

                          //

                          //let end=now(1); trace $(end);

                          //let diff= interval(rangemax('$(end)')-rangemax('$(start)'));

                          //TRACE time create TabA=$(diff);

                          //

                          //////////// store qvd

                          //let start=now(1); trace $(start);

                          //

                          //store TabA into TabA.qvd (qvd);

                          //

                          //let end=now(1); trace $(end);

                          //let diff= interval(rangemax('$(end)')-rangemax('$(start)'));

                          //TRACE time create qvd=$(diff);

                          //

                          //////////// mapping from resident

                          //let start=now(1); trace $(start);

                          //

                          //GetSysKey:

                          //Mapping Load

                          //Key_ID,

                          //Sys_Key

                          //resident TabA;

                          //

                          //GetSysName:

                          //Mapping Load

                          //Key_ID,

                          //Sys_Name

                          //resident TabA;

                          //

                          //GetSysDescription:

                          //Mapping Load

                          //Key_ID,

                          //Sys_Description

                          //resident TabA;

                          //

                          //let end=now(1); trace $(end);

                          //let diff= interval(rangemax('$(end)')-rangemax('$(start)'));

                          //TRACE time create mapping table resident=$(diff);

                          //

                          //drop table TabA;

                           

                           

                           

                           

                          //////////// mapping from qvd

                          let start=now(1); trace $(start);

                           

                          GetSysKey:

                          Mapping Load

                          Key_ID,

                          Sys_Key

                          from TabA.qvd (qvd);

                           

                          GetSysName:

                          Mapping Load

                          Key_ID,

                          Sys_Name

                          from TabA.qvd (qvd);

                           

                          GetSysDescription:

                          Mapping Load

                          Key_ID,

                          Sys_Description

                          from TabA.qvd (qvd);

                           

                          let end=now(1); trace $(end);

                          let diff= interval(rangemax('$(end)')-rangemax('$(start)'));

                          TRACE time create mapping table qvd=$(diff);

                           

                           

                          • Re: Nested Apply Map !?
                            Priyanka Rao

                            Hi,

                            Thanks for the reply  cleveranjos .

                            Tried the suggested way,  Alas , isn't helping the load time!

                            appreciate any other suggestions.

                            Thanks!

                          • Re: Nested Apply Map !?
                            Srikanth P

                            You can try with one mapping table like below  and extract the values with sub field function.

                             

                            MAP_FIELDSTRING:

                            LOAD

                                 KEY_ID,

                                 Sys_Key & '|' & Sys_Name & '|' & Sys_Description AS ConcatedString

                            From source;

                             

                            LOAD

                                 KEY_ID,

                                 subfield(ConcatedString,'|',1) as Sys_Key,

                                 subfield(ConcatedString,'|',1) as Sys_Name

                            .

                            .

                            .

                            .

                            .

                            LOAD

                                 KEY_ID,

                                 ApplyMap('MAP_FIELDSTRING',KEY_ID,null()) AS ConcatedString

                            From AnotherSource

                            ;

                              • Re: Nested Apply Map !?
                                Priyanka Rao

                                Hi,

                                Thanks response.

                                 

                                You mentioned creating 'one' Mapping table with the approach using SubField() on ConCat Field.

                                Won't 3 Apply map need 3 Diff Mapping Loads ?

                                 

                                Would you mind explaining it again please ?

                                 

                                Thanks !

                                  • Re: Nested Apply Map !?
                                    Srikanth P

                                    Basically you have 30M rows in dimension table. I believe you have some resource concerns to join the Dimension table into fact table. So you doing with ApplyMap. But with ApplyMap, you can add one one field with one mapping table. If you want 5 fields you need 5 mapping table assuming all the mapping tables haring one common key field..

                                     

                                    Instead of doing 5 mapping tables, create one mapping table first field is your Key Field and second key is concatenated of all 5 fields  needs to add to Fact table.

                                     

                                    Map_Dim:

                                    Mapping Load KeyID, Dim1&'|'&Dim2&'|'&Dim3&'|'&Dim4&'|'&DIm5 as Value

                                    From DImension;

                                     

                                    So use above mapping table to add the dimension values list & separate the values with subfield function.