5 Replies Latest reply: Feb 25, 2014 8:31 AM by Srikanth P RSS

    "Dimension" fields present in (almost) every table - what to do?

    Friedrich Hofmann


      Hi,

       

      my scenario is this:

      - In my current app (for the QM area) I load data from a big Excel table.

      - I make this into so many distinct tables in Qlikview, roughly one for every individual KPI.
        => Most of those contain a field "plant" which can have two values (the list is used for two plants)

      => Now I'd like to keep this a bit slimmer (and do away with the need to endlessly rename fields to avoid links)

      <=> A link_table seems a bad idea as most of my tables are somehow linked to a master_calendar, so that would create circular references in quite some of them, wouldn't it?

      => Is there any other way?

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: "Dimension" fields present in (almost) every table - what to do?
          Peter Cammaert

          How are your different internal tables linked together? If you use a constructed key field, put plant in this key as well. Is there still a big fact table attached? Leave the Plant dimension overthere, and remove it from all others. If you haven't got a facts table, you'll need a link table. In that case, attach both calendar and all dimensions that are part of the key to this link table.

           

          Peter

            • Re: "Dimension" fields present in (almost) every table - what to do?
              Friedrich Hofmann

              Hi Peter,

               

              thanks a lot for helping!

              Well, my data_model in this app is pretty much star-shaped as it should be. Almost all of the fact tables (as I said, I've created roughly one separate fact_table for every KPI a somehow linked to the master_calendar - by a simple keyfield, no compound_key.

              I guess I'll need a link_table that sits inbetween the master_calendar and all other tables, yes?

              Well, I'll put that on my agenda for when there's time - around 2040 ... True, renaming the "plant" fields all the time to avoid unwanted links and synthetic keys is a bit annoying, but easier and faster for now than working out a clean solution to it.

              Thanks a lot!

              Best regards,

               

              DataNibbler

                • Re: "Dimension" fields present in (almost) every table - what to do?
                  Peter Cammaert

                  Ah, this makes things a bit clearer.

                   

                  There is a simple solution. If there is a Plant field in every KPI facts table, move the Plant value to the key, eg make every key into some concatenation of Plant & '-' & Date. Drop the Plant field from the facts (or just don't load it as a separate column).

                  Now convert your central master calendar to this Plant thing as well by JOINing all master calendar rows to a list of unconnected Plant names (controlled cartesian product). The master calendar will double in size (only two plants) and will have a new Plant column. Then convert the master calendar key to the same concatenated value, but leave the Plant column inside the master calendar. This will be your central Plant dimension. It's a bit of loading/reloading/dropping tables, but it'll fix your problem.

                   

                  Good luck,

                   

                  Peter

                    • Re: "Dimension" fields present in (almost) every table - what to do?
                      Friedrich Hofmann

                      Hi Peter,

                       

                      that sounds good! I thought about putting the plant in the master_calendar, but I wasn't sure.

                      Then I'll have just one "plant" field in the master_calendar that I can then use in all of my fact tables.

                      <=> I just have to wait until the people have enriched the base_data_list to encompass the complete data for both plants. Otherwise I'd run into problems with those fact_tables that do not have the plant.

                      Thanks a lot!

                       

                      Best regards,

                       

                      DataNibbler

                • Re: "Dimension" fields present in (almost) every table - what to do?
                  Srikanth P

                  Hi, If you have more than one Fact table, the Link Table is the better solution or concatenate all the Fact tables into one Big Fact table. Either of any case it would help you.

                   

                  Please post sample script and your existing data model for clear understanding.