5 Replies Latest reply: Dec 17, 2012 1:06 AM by Hesh RSS

    Calculated columns in a table

    Hesh
      Hi,

      I have a requirement where each column (measure) should have three calculated values for it
      ART_ID      WEEK_AND_YEAR     SALES_PRICE_LOCAL     SALES_PRICE_EURO     PREV_WEEK_AND_YEAR     PREV_SALES_PRICE     PREV_SALES_PRICE_EURO
      100001     201240          2000                    200          201140               1800               180     
      SALES_PRICE_LOCAL : Acual data in the source in local currency value
      SALES_PRICE_EURO : Converted value to Euro from local currency
      PREV_WEEK_AND_YEAR : As data is week level granularity, we need to compare same attributes with previous year week for the article
      PREV_SALES_PRICE : Last year Price of the article in local currency
      PREV_SALES_PRICE_EURO :Last year Price of the article in Euro currency



      I am planning to capture base values as is and create a materialized view on top of it, but a bit worried about the space as it is a vary large table and we are nowhere doing any aggregation and MV will do replication (of original cloumns from the base table) of data as is.

      Appreciate your suggestions.


      I did explain this in a very complicated fashion and closed this thread to make it simple...

      Re: Data modeling with calculated filelds


      Thanks,
      Mahesh
        • 1. Re: Calculated columns in a table
          thtsang
          How are you going to use the table? For example, do you need to compare and current and last week price for all articles?

          And what's the biggest concern of your current design?
          • 2. Re: Calculated columns in a table
            Hesh
            How are you going to use the table? For example, do you need to compare and current and last week price for all articles?
            Yes I have to compare all the articles from current to previous year-week.
            And what's the biggest concern of your current design?
            Existing design contains only original value, rest of the columns are calculated in the select query and its taking a long time ( not even minutes) to execute
            ART_ID      WEEK_AND_YEAR     SALES_PRICE_LOCAL     
            100001     201240          2000                    
            Thanks,
            Hesh
            • 3. Re: Calculated columns in a table
              thtsang
              So you need to join 2 very large rowsets (this year-week price and last year-week price), this can be slow anyway. Using a MV just shift the time of the job around.

              Note that retrieving a single very large rowset (i.e., the MV you considered) can also be slow.

              If you have partitioning option, it seems WEEK_AND_YEAR is a good candidate. Or is it partitioned already?
              • 4. Re: Calculated columns in a table
                Hesh
                If you have partitioning option, it seems WEEK_AND_YEAR is a good candidate. Or is it partitioned already?
                Yes it is already partitioned by this column, single year data is of 14 GB in size, is it required sub partitions as well?(like store, article at each store needs to be compared )

                Query Response time should not be in minutes, that what the business is concerned as quicker as possible. Its very challenging but also interesting, worrying and testing :)

                Thanks
                Hesh

                Edited by: Hesh on Dec 7, 2012 3:17 AM
                • 5. Re: Calculated columns in a table
                  Hesh
                  I did sub-partitions for each week in the year and its working fine now.

                  Thanks,
                  Hesh