1 Reply Latest reply on Sep 5, 2012 1:26 PM by DavidGreenfield

    Mapping to Relational tables

    954011
      We are new to Oracle OLAP and AWM.
      These are the questions we have :

      1. Is there a way to map to external files rather than RDBMS for populating the cubes ? If so, how in AWM ?

      2. If i want to have the cubes populated every day for 1 year time, should the RDBMS also (that maps the cube) should have one year data ?.
      Once the cube is populated for yesterday from RDMS, can i not remove the yesterday's data from RDMS and load only the new data in RDBMS.
      In such a case, the cube should have yesterdays data and the newly added data from today.
      The intent is that, the RDBMS should have just 1 day or 1 week data but the cube should have 1 year data...
        • 1. Re: Mapping to Relational tables
          DavidGreenfield
          1. Is there a way to map to external files rather than RDBMS for populating the cubes ? If so, how in AWM ?
          Define an RDBMS external table that maps to the external files and then map the cube to this table. See http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_concepts.htm for more details.
          2. If i want to have the cubes populated every day for 1 year time, should the RDBMS also (that maps the cube) should have one year data ?.
          Once the cube is populated for yesterday from RDMS, can i not remove the yesterday's data from RDMS and load only the new data in RDBMS.
          In such a case, the cube should have yesterdays data and the newly added data from today.
          The intent is that, the RDBMS should have just 1 day or 1 week data but the cube should have 1 year data...
          The default behaviour of cubes defined in AWM is to add new data during each cube build and keep existing data (unless it is overridden in a cell). So if you build once a day from a fact table that contain only one day of data, then eventually the cube will contain a year of data. This is controlled by the "default cube script" for the cube. You need a cube script without a CLEAR command.

          Given this setup, you should think about using the LOAD SERIAL option in the DBMS_CUBE.BUILD script. For example
          exec DBMS_CUBE.BUILD('MY_CUBE USING (LOAD SERIAL, SOLVE)')
          This will cause the server to access the external table only once instead of once per partition. The per partition load makes sense if there are indexes on the fact table, but this is not going to be true in your case. It also make sense (in all cases) if you plan to partition the cube by DAY.