2 Replies Latest reply: Dec 7, 2012 3:18 AM by Hesh RSS

    Data modeling with calculated filelds

    Hesh
      Hi,

      I have a requirement where I have to model the (tables?) for my huge warehouse data base to reflect some recomputed features for my clients business

      It would be easy for me to explain by giving some table structure along with some data.

      My base table looks like following...
      ARTICLE_NUMBER     YEAR_AND_WEEK     SALES_QUANTITY     SALES_PRICE
         10000001          201140          110          24.3
         10000002          201140          120          13.5
         10000003          201140          100          5.7
         20000001          201140          80          57.1
         20000002          201140          70          28.9
         20000003          201140          60          56.3
      Following are the things I have to model

      1) Need to have 'Previous year_and_week' values along with current. Tried this to bring in reporting rather then at DB level but taking long time to compute and decide to store them physically instead of calculation during the run-time. Like following way....
      ARTICLE_NUMBER     YEAR_AND_WEEK     SALES_QUANTITY     SALES_PRICE     PREV_YEAR_AND_WEEK     PREV_SALES_QUANTITY     PREV_SALES_PRICE
         10000001     201240          110          24.3             201140                    110               24.3
         10000002     201240          120          13.5             201140                    120               13.5
         10000003     201240          100           5.7             201140                    100               5.7
         10000004     201240          100           5.7             201140                    NULL          NULL
         20000001     201240          NULL           NULL            201140                    80               57.1
         20000002     201240          70          28.9             201140                    70               28.9
         20000003     201240          60          56.3             201140                    60               56.3
      Things to note here is few articles may not be there in the future year and may few new articles come as well. This will effect next aggregate level for article where we have to show the sum of article values at Product level which is the next level of hierarchy. For assumption take 1000000* range as one Product and 2000000* as another product then aggregation will be summed values of these.

      2) Need to show additional columns for currency columns to show conversion values to Euro, like for SALES_PRICE column we need additional column SALES_PRICE_EURO ( converted from local currency to Euro). Additional challenge here is business wants a static EURO conversion each year, means all EURO column values needs an updated values at every year.

      3) Third one is truly complected one to understand, but let me try. As this data granularity is week level, business don not want to do comparison some results for two weeks i.e. first&Last weeks of the year. I have a solution in my mind for this to use one degenerated dimension column to show at every fact record whether that week is first or last and use 11g new feature virtual columns to compute COMP columns.

      so the end of additional columns for just one SALES_PRICE will be like this
      SALES_PRICE     SALES_PRICE_EURO   PREV_SALES_PRICE     PREV_SALES_PRICE_EURO  SALES_PRICE_COMP
      Looks very complex requirement ! isn’t it?

      Appreciate your inputs.

      Thanks
      Hesh