10 Replies Latest reply: Aug 17, 2011 6:40 AM by Daniel Garguilo RSS

    Data Architecture

      HI All

       

      I am having a little trouble trying to work out the best architecture for a document.

       

      I am looking at Reporter Productivity. I have:

      • a main table that has a field 'first reporter' and another 'second reporter'. Productivity takes into account both these fields
      • another table with the Reporter Details in it including the expected amount that should have been reported

       

       

      Basically what i would like to do is have a table that has: The reporter name and then a count of how many times they appear in the first reporter column and another count of how many times that appear in the second reporter column, A total and also the expected.

       

      Please see the attached example. Note i have not joined the tables yet as i am unsure on the best place to do this in this situation.

       

      Any help would be great.

       

      Thanks

       

      Dan

        • Data Architecture
          Or Shoham

          What I would do in this instance is load the "fact" table twice. In pseudocode:

           

          Load ActivityID, FirstReporter as Reporter, "First Reporter" as ReporterType

          From ReportsCompleted;

          JOIN

          Load ActivityID, SecondReport as Reporter, "Second Reporter" as ReporterType

          From ReportsCompleted;

           

          Load *

          From ReporterDetails;

            • Data Architecture

              Thanks for the reply. This will work but is there any other options? I am loading about 1million records and growing each month so i would prefer not to load it twice if i can avoid it.

               

              Thanks

               

              Dan

              • Re: Data Architecture

                Thanks for the reply. This will work but is there any other options? I am loading about 1million records and growing each month so i would prefer not to load it twice if i can avoid it.

                 

                Thanks

                 

                Dan

                  • Data Architecture
                    Or Shoham

                    I suppose you could create a complex key based on both reporters, and set up a table that includes every possible permutation to join it with.. but I would not go that way.

                     

                    Is there a reason not to load twice? 1m records is really not a lot - I have models with 10m+ that are loading on an hourly basis with some tables being read 3-5 times. If you're worried about I/O, you can just load the table from file once and then use resident loads - this should go very quickly, especially if your records are composed of just a small handful of columns each. Because of the way QV stores data, using a double-load should not significantly increase the storage space / RAM required, either.

                     

                     

                     

                    TemporaryLoad:

                    Load * from ReportsCompleted;

                     

                    Reports:

                    Load ActivityID, FirstReporter as Reporter, "First Reporter" as ReporterType

                    Resident TemporaryLoad;

                    JOIN

                    Load ActivityID, SecondReport as Reporter, "Second Reporter" as ReporterType

                    Resident TemporaryLoad;

                     

                    drop table TemporaryLoad;

                     

                    Reporters:

                    Load *

                    From ReporterDetails;

                      • Data Architecture

                        HI

                         

                        Thnaks again for the reply and the explanation of why it is not a problem to load the table multiple times. I really appreaciate it.

                         

                        One last question if you have time. I read somewhere that the Map Function and Join are the same. Is this correct? I thought the map function would map an extra field to a table. I guess join could be used to either join two tables toegether like CONCATANATE but also to add an extra field?

                         

                        kind regards.

                         

                        Dan

                        • Re: Data Architecture

                          HI

                           

                          Thnaks again for the reply and the explanation of why it is not a problem to load the table multiple times. I really appreaciate it.

                           

                          One last question if you have time. I read somewhere that the Map Function and Join are the same. Is this correct? I thought the map function would map an extra field to a table. I guess join could be used to either join two tables toegether like CONCATANATE but also to add an extra field?

                           

                          kind regards.

                           

                          Dan