8 Replies Latest reply on Apr 4, 2020 10:42 PM by Vlad Ponomarenko

    Partitioning concept

    Saswata Dutta Active Member

      Hi experts

      I have below questions regarding partitioning in powercenter. Lets say if i have following transformation

       

      source->SQ->Filter->exp->sorter->aggregator->target.

       

      lets say, I want to create three partition.

      • Do i need to create three partition at SQ always? Can i create 3 partition at filter transformation?
      • If i create 3 partition at filter, then is it all other transformation before filter and after filter will be processed by three threads?
      • Now lets say if i create 3 partition at SQ, can i increase the # partition at sorter to 4? If i make it 4, then all downstream transformation will be processed by 4 threads?

       

      Thanks

      Saswata

        • 1. Re: Partitioning concept
          Nico Heinze Guru

          Once upon a time (back in PowerCenter 6, when the session partitions were initially introduced) you could set up an arbitrary number of partition points at any active transformation (it memory serves me right). So your idea from your last question would have worked in PowerCenter 6.

           

          This probably was so confusing to most customers that Informatica has changed that in PowerCenter 7; since then you can have either one session partition or n session partitions. The number of partitions cannot be changed within a single session.

          (There is also the concept of "dynamic session partitioning" which enables the Integration Service to select a suitable number of session partitions when the session starts, but still the total count of partitions is fixed once the session starts.)

          Technically that means that with n session partitions you have n reader threads per source qualifier, n writer threads per target instance, and (at least) n transformation threads running while the pmdtm process (which executes a session) is running.

           

          Does that answer your questions?

           

          Regards,

          Nico

          • 2. Re: Partitioning concept
            Saswata Dutta Active Member

            Hi Nico

            Thanks for replying. It partially answers my question. My next question is where the partition is created? Do i need to create partition always on source qualifier? If i create 3 partition in the middle of the mapping on any transformation, will it create all three reader transformer and writer thread? What if i create partition at target only?

            Thanks

            Saswata

            • 3. Re: Partitioning concept
              Nico Heinze Guru

              As far as I understand it (this iis not my area of expertise), you cannot create partitions at any transformation, you create partitions for the whole session. That means that you automatically get (for example) three partitions per source qualifier, per target instance, and per transformation.

              What you can (and sometimes should) do is to create re-partition points; here you can define on your own how the data are distributed between the partitions.

               

              For example, I have seen cases where a customer runs a session in six partitions, but for only one partition (usually the first one, for no special reason) the Source Qualifier delivers all the data, the other five source qualifier threads simply don't deliver anything (source filter "1=0").

              At the next possible point, a partition point is set up which distributes all records coming from the SQ over all six partitions evenly.

              Why?

              Because one source thread delivered the best read performance in this case, so it would have made the mapping run slower if they would have had more than one source thread deliver data.

               

              Again, this is not my area of expertise, you might want to study the Advanced Workflow Guide for PowerCenter; the first three chapters explain everything around session partitions in detail.

               

              Regards,

              Nico

              • 4. Re: Partitioning concept
                Sachin Kumar Guru

                Can you refer the chapter "partition points" and "partition types" for complete understanding on this topic in Informatica documentation in Advanced workflow guide.

                Partitioning is a vast topic as there are many partitioning types. Hope it will address all your queries.

                • 5. Re: Partitioning concept
                  Saswata Dutta Active Member

                  Hi Satish

                  I have gone through the documents and but these documents are not very good for a beginner like me. I have understood some and something is not clear. If you have good understanding for my problem, can you please answer?

                  Thanks

                  Saswata

                  • 6. Re: Partitioning concept
                    Vlad Ponomarenko Guru

                    Hi,

                     

                    When you add Partition Point at any transformation, additional thread is created in this point..

                    Partition points mark the boundaries between threads (Reader, Transformer(s), Writer) in a pipeline. The Integration Service can redistribute rows of data at partition points according to partition type, for example, Round Robin, Hash, Key Range. Pathtrough Type does not redistribute rows.
                    You can add partition points to increase the number of transformation threads and increase session performance.

                     

                    Next, when You add partition at any partition point, additional pipeline is created. So the number of all threads in your session = NPrt * NThr
                    where

                    NPrt- number of partitions you added

                    NThr -Number of threads in one pipeline - Reader, Writer, Trans1, Trans2 etc


                    Regards, Vlad

                    • 7. Re: Partitioning concept
                      Saswata Dutta Active Member

                      Hi

                      Thanks for your reply.

                      Let me try to understand with and example. Lets say i have following mapping.

                       

                      SQ->EXP->FIL->AGGR-TARGET

                       

                      I have created 3 partition at SQ. If i create 3 partition at SQ, will there be 3 thread for reader, 3 for tranformer and 3 for writer?

                      Lets say i did not create 3 thread at SQ, instead i created 3 thread on AGGR and i did not create any more. Will it be 3 thread for reader write and transformer?

                      Lets say i created 3 thread in SQ, and in AGG i added a partition point and again created 3 partition. Does it mean on AGGR will work with 9 thread? 3 thread per partition? What if there is any more transformation after AGGR. Will they work with 9 thread or will they work only with 3 thread?

                       

                      Thanks

                      Saswata

                      • 8. Re: Partitioning concept
                        Vlad Ponomarenko Guru

                        Hi Suswata,
                        My answers are in bold.

                        I have created 3 partition at SQ. If i create 3 partition at SQ, will there be 3 thread for reader, 3 for tranformer and 3 for writer?

                        Yes

                        Lets say i did not create 3 thread at SQ, instead i created 3 thread on AGGR and i did not create any more. Will it be 3 thread for reader write and transformer?

                        Yes

                        Lets say i created 3 thread in SQ, and in AGG i added a partition point ( the 3 partitons/threads are created automatically on AGG) and again created 3 partition.

                        Does it mean on AGGR will work with 9 thread? 3 thread per partition? What if there is any more transformation after AGGR. Will they work with 9 thread or will they work only with 3 thread?

                         

                        You can find more informaton in these video resources
                        Partition and Partition point parallel data processing and data redistribution - YouTube
                        Informatica Partitioning and Interview Questions Explanation | Informatica Training - YouTube

                        Regards, Vlad