4 Replies Latest reply: Mar 31, 2012 4:24 PM by Karl Pover RSS

    What to place in facttable?

    Göran Hofstedt

      Hi

      A bit silly question but what do I need to include in a fact table? I create the facttable by concatenate %datekey from diffrent sourses and a ID-key that link to diffrent tables. What else should I include? A bit vauge question but I´m not really getting it

       

      from qvd:s...

       

      By loading diffrent datekeys in format sign-upDate & '_' & sign-upDate as %datekey, By-Date & '_' & By-Date as %Datekey. The calendar gives me what have happend on every date. But I think I load to much into the fact-table.

       

      Do I really need something more in facttable than the datekey and a ID-key to the table with the info about the ID-key?

        • What to place in facttable?
          Karl Pover

          A fact table contains a measureable event, so usually a fact table contains something that is you aggregate in some way like sum, average, min, max, etc.  For example, amount of dollar sales, quantity, margin, etc.

           

          Karl

            • What to place in facttable?
              Göran Hofstedt

              so basic I put the fields I want to measure in the facttable, and if I want to measure two diffrent events from the same table I put both in the facttable? Should I use one of this as link to the dimension or should I use another field as key/link field? /thanks!

                • What to place in facttable?
                  Karl Pover

                  If you can put the different aspects of 1 event as different columns with the same dimensions.  For example, invoicing a customer is an event that has one column that is sales amount and another that is quantity.  In a database a different event like a sales order may be in another table, but in QV it's better to put the 2 different events in the same fact table.  Sales order also has a sales amount and quantity, but to differentiate between invoice and sales orders you should add a column that serves as a flag that tells you is the row tells you about a sales order or an invoice.  All rows should be at the same level of detail which is usually the lines of the invoice or sales order.  The dimensions usually include customer, product, date and you should use an ID to link the rows of the fact table with the dimension catalogs.

                   

                  Karl

              • What to place in facttable?

                I think you can add a column by naming the source.

                 

                Example, Fact Table contain "Budget", "Sales Transaction", "Purchase Transaction".

                 

                You can add a column to distinguish the source of each record.

                 

                Source, TransactionDate, CustomerName, Amount

                Budget, 20120301, CustomerA, 1000

                Sales, 20120301, CustomerA, 2000

                Purchase, 20120301, CustomerA, 3000