20 Replies Latest reply: Aug 26, 2013 2:58 PM by Gajapathy R RSS

    How do I get rid of $Syn table

    diegovanexter

      Hello,

      I am a newbie to Qlikview and am trying to create my own report for the first time. Unfortunately I am running into some issues with a $Syn table.

      My scenario: I have a table with "processed hour" records. Such a record contains an hourcode and a ProdHeaderDossierCode. I also have a HourBudgetTranslate table which translates the hourcode to an hourbudgetcode based on the HourCode, unless a ProdHeaderDossierCode or DossierCode exists in the HourBudgetTranslate table, then it should translate to the related hourbudgetcode. Since the DossierCode is not part of the ProcessedHour table, I also have to link to another table to retrieve the dossiercode based on the ProdHeaderDossierCode. So now I have 3 fields that end up in the $Syn table (DossierCode, HourCode and ProdHeaderDossierCode).

      I don't think renaming works since the HourBudgetTranslate table translates based on the actual codes.

      Can anyone help me out and point me in the right direction?

      Kind regards,

       

      Diego

        • How do I get rid of $Syn table
          Deepak Vadithala

          Synthetic keys are generally resource heavy and may slow down calculations and, in extreme cases, overload the application. They also make a document harder to understand and maintain.

          In your case, can you create a composite key on the columns?

          Concatenate both the colums and this removes synthetic key.

          Hope this helps!

          Cheers - DV

            • How do I get rid of $Syn table
              diegovanexter

              Hi DV,

              Not quite sure which columns I should concatenate. I have added a picture below for clarification. If I concatenate the columns I'm creating a unique column right? But I need the original columns to check against the HourBudgetTranslate table.

              In the HourBudgetTranslate table all HourCodes translate to an HourBudgetCode but when either the DossierCode or ProdHeaderDossierCode is filled in the HourBudgetTranslate table it should translate against that specific HourBudgetCode which will be different from the HourCode one.

              In my report I would like to be able to select the HourBudgetCode and the report should show me all ProcessedHour records with that specific HourBudgetCode, based on either the HourCode, DossierCode or ProdHeaderDossierCode. By the way, the DossierMain table is only there because I need the OrdNr as well.

              Do you still think concatenate will work? If so, which columns should I use?

              Kind regards,

              Diego

               

                • How do I get rid of $Syn table
                  CheenuJanakiram

                  Hi Diego,

                  I think this is LinkTable territory, but am not 100% sure as don't know what are the underlying values in each table.

                  It looks like you need a massive link table which will contain all the individual values from the 4 source tables in your image for the fields DossierCode, HourCode, ProductHeaderDossierCode. This link table will then need a concated field with HourCode & ProdHeaderDossierCode to link to the ProcessedHour table, a concat of DossierCode & ProdHeaderDossierCode to link to the ProductionHeader and a concat of the DossierCode&ProdHeaderDossierCode&HourCode to link to the HourBudgetTranslate.

                  None of the source tables should be loading DossierCode, HourCode or ProductHeaderDossierCode as a singular field only the concatenated fields needed to join to the link table.

                  Hope this makes sense. It's not complicated, but not easy to explain in a few phrases. What you are doing with the above technique, is taking posession of the ownership of created the synthetic keys, rather than relying on QlikView to figure out the structure of your resulting data model.

                    • How do I get rid of $Syn table
                      CheenuJanakiram

                      Btw, depending on the size of your datamodel and tables, an optimal way of creating link fields is to use the autonumber function. Apparently, QlikView resolves the fastest, queries over tables which use numbers as link field, as opposed to varchars.

                      Hence, for e.g. you should have:

                      Load

                      autonumber(HourCode & ProdHeaderDossierCode, 1) as Link_ProcHr,

                      autonumber(DossierCode & ProdHeaderDossierCode, 2) as Link_ProdHead,

                      autonumber(DossierCode&ProdHeaderDossierCode&HourCode, 3) as Link_HrBudTran

                      etc...

                      These linking fields should be copied and pasted to the associated source tables their are linked to.

                      Hope it helps.

                        • How do I get rid of $Syn table
                          diegovanexter

                          Hi, It's not clear to me how I can create the Link Table. I added the autonumber to the source tables so now I have 4 tables that are not linked. But how do I create the Link Table?

                          Kind regards,
                          Diego

                            • How do I get rid of $Syn table
                              Deepak Vadithala

                              Please can you share the QV file with sample data? Please share it with synthetic keys... I'll try this one :)

                                • How do I get rid of $Syn table
                                  CheenuJanakiram

                                  Hi Diego,

                                  Reload your script using the debug and option with the tick box in Limited Load which you set to 1. This will load the script with 1 row of data, then share the script and I'll give you a quick sample of what is meant by the link table.

                                    • How do I get rid of $Syn table
                                      diegovanexter

                                      Hi guys,

                                      Attached is the file with a dataset limited to 10 rows.

                                      Kind regards, Diego

                                        • How do I get rid of $Syn table
                                          Deepak Vadithala

                                          I thought you would provide QV document with synthetic tables. Anyway I will give you concatenation explanation...

                                          You can use concatenation (As a new column) in your dimension table and also add the same concatenation in the fact tables. You don't have to use the concatenated column to filter the data. You can still use the orginal columns to filter the data. While you filter this data on non-concatenated columns you still have the relationship to the linked tables through your concatenated columns.

                                          I am sorry if I am not clear in my explanation as english is not my first language!

                                          Good luck!

                                          Cheers - DV

                                           

                                           

                                           

                                           

                                            • How do I get rid of $Syn table
                                              diegovanexter

                                              Hi DV,

                                              Since I am not that familiair yet with the QlikView terminology I thought you meant the qvw file when you asked for the QV document. What are the steps to create this QV document?

                                              Kind regards,
                                              Diego

                                               

                                                • How do I get rid of $Syn table
                                                  Deepak Vadithala

                                                  Oops sorry! what you were thinking was right. qvw file and QV document are the same. I just call it document for my reference. So please ignore that bit.

                                                  Did you try CheenuJanakiram's solutions? I am not fimilar with link tables but it seems to be good approach to solve the issue.

                                                  In my previous email I am referrring to qvw file with synthetic keys as an example. However the document you shared does not hold any relationships among the tables...

                                              • How do I get rid of $Syn table
                                                CheenuJanakiram

                                                Hi,

                                                The attached script assumes that the HBT.xlsx contains ALL the values of the DossierCode, ProdHeaderDossierCode and HourCode in the data model. Otherwise, you have to load ALL the values from the separate XLSXs and then create one massive link table with all these values using the concatenate function between each XLSX table load.
                                                If all the values of the 3 above fields are synchronised between HBT and the other XLSX files, the script I provide you should be fine. Try and run it and see what happens.
                                                Hope it works.

                                                The "philosophy" of the link table remains unchanged. It just might need tweaking to create a link table that contains all the values and combinations of the fields that this table is supposed to link to the other tables.

                                  • How do I get rid of $Syn table
                                    John Witherspoon

                                     

                                     


                                    dvqlikview wrote: Synthetic keys are generally resource heavy and may slow down calculations and, in extreme cases, overload the application. They also make a document harder to understand and maintain.
                                    In your case, can you create a composite key on the columns?


                                    Synthetic keys are in and of themselves not resource heavy, will not slow down calculations, and will not overload the application. They are QlikView's version of composite keys, no more, no less. Creating direct replacement composite keys will only waste script time. The reason they are typically associated with horrible performance, script crashes and the like is that they typically result from data model mistakes, and those data model mistakes are what are really causing the problems. And often, data model changes made to remove the synthetic keys also fix the data model mistakes, reinforcing the idea that the synthetic keys are to blame.

                                    Let's look at this specific example. I'll start with with what Cheenu says, which sounds right to me:

                                     


                                    CheenuJanakiram wrote:It looks like you need a massive link table which will contain all the individual values from the 4 source tables in your image for the fields DossierCode, HourCode, ProductHeaderDossierCode. This link table will then need a concated field with HourCode & ProdHeaderDossierCode to link to the ProcessedHour table, a concat of DossierCode & ProdHeaderDossierCode to link to the ProductionHeader and a concat of the DossierCode&ProdHeaderDossierCode&HourCode to link to the HourBudgetTranslate.


                                    Agreed. But here's the thing - that is exactly what the synthetic key structure is. QlikView itself came to this exact same conclusion about the data structure, and it did it all for you.

                                    A synthetic key table is a concatenated key table. The only real difference is that QlikView builds it for you automatically, and you cannot reference the table or the new field, but there is a table and a field internally. And if you look at the structure of these tables and fields, you'll see that it exactly matches the description of the required link table and the keys necessary to connect all the tables to this link table.

                                    In other words, if we have correctly understood what sort of linkage table you need, then you already have that kind of linkage table. To try to create it manually when QlikView has already built it for you automatically would be a waste of time, even if it might be an interesting learning experience, and perhaps worthwhile for that reason alone (though you'd want to go back to synthetic keys if script performance became an issue).

                                    So, two possibilities here. If I've understood what sort of linkage table you need, you're done. Your data model is already correct, and should be producing correct results. Changing to a manually-created linkage table will only slow down the load, not speed up the application. But if you are not seeing correct results in charts, then I've misunderstood your data model, and I'd need to look more closely. But if you are not seeing correct results, then switching to a linkage table that exactly mimics the synthetic key structure will not fix the problem. You would need to make some other sort of data model changes.

                                    More information on synthetic keys in this thread:

                                    http://community.qlik.com/forums/t/31028.aspx



                                    I should probably turn it into a wiki entry at some point, but I think the discussion helps present a more balanced viewpoint than I'd be able to present on my own, given my perhaps obviously strong opinions on this subject. Smile

                                      • How do I get rid of $Syn table
                                        CheenuJanakiram

                                        Hi John,

                                        I forgot to state that the syntable was a link table at source and jumped to a few conclusions that the resolution would be in explicitly creating the link table rather than relying on QlikView to resolve that, i.e. I assumed reload time was an issue.

                                        However, seeing that you are a senior QlikView developer and have seen your name quite extensively, could you give me more info on something I have heard, but don't have experience of, notably "Apparently, QlikView resolves the fastest, queries over tables which use numbers as link field, as opposed to varchars.". I heard that this improves performance, especially in extremely large data loads. Do you have any experience/info regarding this?

                                        Regards,

                                        Cheenu

                                          • How do I get rid of $Syn table
                                            John Witherspoon

                                             


                                            CheenuJanakiram wrote:"Apparently, QlikView resolves the fastest, queries over tables which use numbers as link field, as opposed to varchars.". I heard that this improves performance, especially in extremely large data loads. Do you have any experience/info regarding this?


                                            I'm afraid I don't have much experience with this. You probably at least understand what this is saying, but to make sure, or explain for others who don't, let's say you have this data model:

                                            Customers:
                                            Customer, Credit Rating
                                            Billy Joe Jim Bob's Foreign Auto Wrecking Yard, B

                                            Invoices:
                                            Invoice, Customer, Amount
                                            Invoice 123, Billy Joe Jim Bob's Foreign Auto Wrecking Yard, 1500

                                            My understanding is that having a big text field like this as the link between two tables is inefficient when it comes time to process the data in charts. To improve efficiency, we could change to a numeric link between the two tables instead, typically created with autonumber(). The resulting data model might look like this, and you wouldn't expose the Customer Number anywhere to the user, just the Customer. So from their perspective, it should be exactly the same.

                                            Customers:
                                            Customer Number, Customer, Credit Rating
                                            1, Billy Joe Jim Bob's Foreign Auto Wrecking Yard, B

                                            Invoices:
                                            Invoice, Customer Number, Amount
                                            Invoice 123, 1, 1500

                                            However, I don't have much experience with this. Most of our linking fields ARE alphanumeric, but they are short alphanumeric codes, typically 10 bytes or under. Some of them are just numeric codes. It would likely still help slightly if the non-numeric ones were converted to numeric codes, but this has never really seemed worth the extra trouble and data complexity to me. On the other hand, I don't deal much with large data volumes. Most of our QVWs are under 100 MB.

                                          • How do I get rid of $Syn table
                                            diegovanexter

                                            Hello John, I removed the manual link table stuff, I let QV create the synthetic keys automatically and since I am not seeing the correct results I assume I have to do something else. Let me try to explain what I would like to accomplish.

                                            I have a table "ProcessedHour" that contains all workinghour records of our employees, the hours are booked against a specific hourcode (holiday, doctor ...10 categories in total.) or they are booked against a combination of HourCode and ProdHeaderDossierCode (Project or Product specific). Next to that table I also have a "HourBudgetTranslate" table that contains 21 categories. Out of those 21 categories 9 are the same as the ones from the ProcessedHour table but one is translated to different categories (Sales, Meeting, education etc.) based on the ProdHeaderDossierCode or DossierCode where DossierCode is a combination of ProdHeaderDossierCodes.

                                            I would like to be able to select one of the 21 categories from the HourBudgetCode table and the report should show me all processedhour records that belong to that specific category. The HourBudgetTranslate table contains manually entered links between HourCode - HourBudgetCode; ProdHeaderDossierCode - HourBudgetCode and DossierCode - HourBudgetCode. So if I enter a specific HourCode, ProdHeaderDossierCode or DossierCode in the HourBudgetTranslate table linked to one of the 21 categories the hours recorded in the ProcessedHour table against that DossierCode or ProdHeaderDossierCode should be included in the report. This is what seems to be missing right now.

                                            Hope this explains what I am trying to do. I have added to the 4 tables I started with (see picture below) but have not found a solution to the scenario above. I appreciate any help/tips you can give me.

                                            Kind regards, Diego

                                              • How do I get rid of $Syn table
                                                John Witherspoon

                                                Yes, if you aren't seeing the correct results, then we'll probably need to alter the data model in some way.

                                                But your explanation lost me, particularly when you started referring to "categories" which aren't in any of the tables. Is the "HourBudgetCode" or maybe the "Description Hour" the category? Simple but reasonably comprehensive example data would probably help tremendously.

                                                Unfortunately, the file you posted with the example data has had some of the most important fields converted to numbers that don't connect to each other, so it didn't help me understand the connections between tables.

                                                On the surface, I get the feeling that I'd be using the HourBudgetTranslate to translate the data during the script, and then dropping the table. But I can't say for sure.

                                                  • How do I get rid of $Syn table
                                                    diegovanexter

                                                    John, sorry for the confusion. You are correct, HourBudgetCode is the category in the HourBudgetTranslate table and HourCode is the other category in the ProcessedHour table. Attached is another file with a bit more data.

                                                    I have added a second tab with the HourBudgetCode including description and HourCode including description. What you will see is that HourCode 10 relates to several HourBudgetCodes and the rest of the HourCode relate to just one HourBudgetCode. I believe it is HourCode 10 where I need to script something so it will show me the associated ProcessedHour records. So if the ProcessedHour record contains HourCode 10 I need to check the HourBudgetTranslate table if the ProdHeaderDossierCode from the ProcessedHour record is in the HourBudgetTranslate table, if so it should link to the associated HourBudgetCode, if it's not then I need to check the HourBudgetTranslate table again for the DossierCode. To do this I first need to retrieve the DossierCode that belongs to the ProdHeaderDossierCode.

                                                    I noticed I forgot to add DossierCode to the ProductionHeader table I believe I need this to retrieve the DossierCode that belongs to the ProdHeaderDossierCode. I added the latest tableoverview below.

                                                    Hope this clarifies a bit more.

                                                      • How do I get rid of $Syn table
                                                        John Witherspoon

                                                        Thanks for the example to play with. I apologize for my slow response. I've been out sick and am now busy catching up. I don't know how quickly I can get to this, but it's on my to do list.

                                                        In the mean time, I would encourage others to see if they can come up with a solution for you.

                                                        • Re: How do I get rid of $Syn table
                                                          Gajapathy R

                                                          am not able to validate this, pls give a try. modify the script for HourBudgetTransLate and ProcessedHour tables as shown below.

                                                           

                                                          //***HourBudgetTranslate***

                                                          HourBudgetTransLate:

                                                          Load HourBudgetCode,

                                                          HourCode,

                                                          DossierCode,

                                                          ProdHeaderDossierCode,

                                                          HourCode + '--' + ProdHeaderDossierCode as HourCodeProdHeadDossierCode;

                                                          SQL SELECT *

                                                          FROM TestProduktieDB.dbo.CH_HourBudgetTranslate;

                                                           

                                                           

                                                          //***ProcessedHour***

                                                          ProcessedHour:

                                                          Load

                                                          //ProdHeaderDossierCode,

                                                          RegDate,

                                                          Month(RegDate),

                                                          Year(RegDate),

                                                          Week(RegDate),

                                                          EmpId,

                                                          MachGrpCode,

                                                          //HourCode,

                                                          HourCode + '--' + ProdHeaderDossierCode as HourCodeProdHeadDossierCode,

                                                          (CalculatedTotalTime/3600)/8 AS '[TotalTime in days]',

                                                          CalculatedTotalTime/3600 AS '[TotalTime in hours]';

                                                          SQL SELECT *

                                                          FROM TestProduktieDB.dbo.T_ProcessedHour;