4 Replies Latest reply: Sep 13, 2016 5:52 PM by Clever Anjos RSS

    Binary Load x Mapping Table

    Clever Anjos

      I would like to have a mapping table coming from a datamart qvw loaded through binary load.

       

      Unfortunately, a table created like below is not loaded when the binary is executed

      Prefixos:
      mapping load * ;
      sql SELECT DISTINCT
        PREFIXO,
        isnull(b.OPERADORA_TRATADA,PRESTADORA) as PRESTADORA
      FROM TB_PREFIXOS a
      left join TB_OPERADORAS b on a.PRESTADORA = b.OPERADORA;
      
      

       

      I´m using a workaround like this that is not so optimized as I would like

      Datamart.qvw

      Prefixos:
      sql SELECT DISTINCT
        PREFIXO,
        isnull(b.OPERADORA_TRATADA,PRESTADORA) as PRESTADORA
      FROM TB_PREFIXOS a
      left join TB_OPERADORAS b on a.PRESTADORA = b.OPERADORA;
      
      

       

      Dashboard.qvw

      Binary [Datamart.qvw];
      MapaPrefixos:
      Mapping LOAD * Resident Prefixos;
      Drop Table Prefixos;
      
      

       

       

      Any ideas how could I improve it?

        • Re: Binary Load x Mapping Table
          Sunny Talwar

          I think the problem here is that the mapping table won't exist after the Datamart.qvw runs through and if it doesn't exits, you would not be able to do a applymap on it. I think what you have is one of the option, the other being bringing the mapping load table directly in your new qvw after saving it into a qvd.

           

          Prefixos:

          mapping

          LOAD DISTINCT

            PREFIXO,

            isnull(b.OPERADORA_TRATADA,PRESTADORA) as PRESTADORA

          FROM TB_PREFIXOS.qvd (qvd);

            • Re: Binary Load x Mapping Table
              Peter Cammaert

              Sunny is right. The lifetime of a Mapping table extends to the end of the current script run. Not a second longer.. Mapping tables do not survive the current cycle as they aren't really meant to be more than temporary but extremeley fast translation tables. Ad hoc solutions ("hacks") so to say. A mapping table is also - and by design - never a part of a data mart.

               

              If you need a mapping table in different places, recreate it in those places. It makes no use to load it in one run and use it only in the next run. Although you may have sensible use cases.

            • Re: Binary Load x Mapping Table
              Marco Wedel

              Hi Clever,

               

              maybe using some loop to identify tables with names like 'Mapa*' and mapping loading them resident, droping the source table afterwards.

               

              You just would need to purge the mapping prefix in your Datamart.qvw and could include such a code snippet as some sort of standard transformation in your target qvws.

               

              hope this helps

               

              regards

               

              Marco

              • Re: Binary Load x Mapping Table
                Clever Anjos

                Thank you all.

                As I was thinking a mapping table does not survive the end of script;

                I was hoping there´s a "hack", but it seems there´s not

                 

                Going to test what is faster:

                • Resident load of a table from datamart
                • Loading a qvd (unfortunately a mapping load seems to discard a optimized load too )