5 Replies Latest reply: Nov 27, 2017 10:48 AM by Quy Nguyen RSS

    Storing Fact

    Quy Nguyen

      Dear expert,

       

      I have 1 fact table with many dimension and measures like this: (TYPE 1)

      DateDimension 1Dimension 2Dimesion nTOTALTotal ATotal A1Total A1-aTotal A1-bTotal A2Total A2-aTotal BTotal B1Total B1-aTotal B1-bTotal B2Other measures

       

      In detail:

      TOTAL = Total A  + Total B

      Total A = Total A1 + Total A2

      Total A1 = Total A1-a + Total A1-b

       

      It stores data horizontally. If i change to vertical, it will be like this: (TYPE 2).

           

      DateDimension 1Dimension 2Dimension nLevel 1Level 2Level 3Level 4ValueOther measures
      TOTALTotal ATotal A1Total A1-a
      TOTALTotal ATotal A1Total A1-b
      TOTALTotal ATotal A2Total A2-a

       

      I want to ask about the storage/performance between 2 types above. Because I am working with larger amount of data. For storing data as TYPE 1, it takes more than 20 millions rows and 2.3 GB on disk per day. If i change it into TYPE 2, does it need more space to store the data? (i didnt try). If you have experience with this, please share with me.

        • Re: Storing Fact
          Ali Ahmad

          Hi,

           

          These dimension columns. Are they filled with text or IDs pointing to dimension tabels?

           

          I would load them into QlikView and look at the memory info, but from a sheer amount of rows I would think that if you change it to vertical and these columns are filled with dimension info that would take more memory storage space.

           

          Check out Qlik Document Analyzer.

          • Re: Storing Fact
            Vineeth Pujari

            it actually depends largely on the data itself,

            things like strings ie long lines of text and also the number of unique values in a field

             

            the results will not be the same for everyone; so go ahead and try it to find what works best for your dataset

            • Re: Storing Fact
              Quy Nguyen

              Dear all,

              Thanks for your response.

              I tested with the vertical and it nearly double the size of storage (i have nearly 50 measures), it's not good at all.

              If i keep using TYPE 1 to store the data, how can i create drill down dimension, e.g. a pie chart that show total A , B -> when i click in A, it shows A1 and A2...

              I just think about create a dimension hierarchy in text file and maybe using pick, match to get the corresponding expression.

              Any idea?

                • Re: Storing Fact
                  Ali Ahmad

                  Quy,

                   

                  Replace the dimension columns with ID's pointing to tables and that should take down the storage needed to store this significantly seeing as you replace text with numbers and store much less information in the fact table.

                   

                  This is for the table that is vertical.

                   

                  You can create ID's using AutoNumber and load distinct from fact table to create the dimension tables needed.