1 Reply Latest reply: Aug 16, 2017 5:11 PM by Alfred Yip RSS

    Table properties

    Jasmina Karčić

      Hi,

       

      I need your help for creating one table. I need to create one table with structure:

       

                  DIMENSION                            MEASURE                                             MEASURE

       

                       A                                             F11                                                      F12                               

                       B                                             F21                                                      F22

                       C                                             F31                                                      F32

                       D                                             F41                                                      F42

       

       

       

      The fact is that i don't have this dimension like dimension from database. A, B, C and D are names of this measures, that I don't have in database in one dimension. But I want make one table that will include all this names of measures, and every field has different formula.

       

      How can I make it?

       

      Thank you,

       

      Best regards from Bosnia and Herzegovina

        • Re: Table properties
          Alfred Yip

          May I offer another example to see if I understand you correctly?  Are you trying to arrive at something like this:

           

          Measure Type          2017          2016

          Sales                        1000            500

          Units Sold               12000          7000

          Customers                  500            300

           

          and you are starting with a fact table that looks like this?

           

          Year          Sales          Units Sold          Customers

          2016            500                   7000                     300

          2017          1000                 12000                     500

           

          If so, you could do something to manufacture a dimension with the measure type and manipulate your data to the final format.  There are a few ways you could do this.  For example,

           

          Result:

          Load

              'Sales' as [Measure Type],

              Year,

              Sum(Sales) As sum_amount

          From  Source_Data_Set

          Group By

             Year

          ;

          Concatenate

          Load

              'Units Sold' as [Measure Type],

              Year,

              Sum([Units Sold]) As sum_amount

          From  Source_Data_Set

          Group By

             Year

          ;

          Concatenate

          Load

              'Customers' as [Measure Type],

              Year,

              Sum([Customers]) As sum_amount

          From  Source_Data_Set

          Group By

             Year

          ;

           

          You could then create a crosstab of Measure Type x Year and aggregate the sum_amount.

           

          Or, I could have misunderstood you and just wasted five minutes of your life.  Sorry.