5 Replies Latest reply: Mar 28, 2013 5:54 AM by DavidGreenfield RSS

    Custom aggregated cuts

      I am planning to build an AW which supports date range aggregations on Time dimension(Basically users can select start date and end date for a report) . Does Oracle Olap support custom aggregates? Are there any special techniques for this kind of problems? We still want to support synchronous requests means we need to serve data as soon as possible after user request.

      any ideas?

        • 1. Re: Custom aggregated cuts
          Not sure, if you already know about Calculated-Member functionality of OLAP. I will mention it here and you can figure out if it is helpful or not.

          AWM does not provide functionality for Calculated Members (or Custom Members), but we can do it through XML or through new OLAP dml programs.

          I think Simba uses this method also, when a custom-aggregate is defined in Excel.
          I found one old posting (there are probably more) where David Greenfield discussed MODELs.
          Re: Building Model in 11g

          Take a look at ADD_CUBE_MODEL and other related statements here:

          You can also use XML to do that.

          In the example below:

          (1). Two new dimension members (i.e., Calculated members or custom members) are added to ACCOUNT dimension, which are CM1 and CM2 (or any name you like to give). Pay attention to Expression of 'CM2', which maybe helpful to you.

          (2). The equation is actually OLAP Expression Syntax

          (3). Then we are adding a MODEL in the AW, naming it CALC_MOD and assigning CM1 and CM2 members to the MODEL.

          (4). After that if you describe the AGGMAP, you will see the MODEL included in the AGGMAP
          You will also see a new MODEL definition in the AW.

          *<StandardDimension Name="ACCOUNT">*
          *<CustomMember Name="CM1" Expression="A1 + A2 / A3"> </CustomMember>*
          *<CustomMember Name="CM2" Expression="AGGREGATE(MEASURE) OVER (ACCOUNT IN ('A', 'B', 'C'))"> </CustomMember>*
          *<Model Name = "CALC_MOD">*
          *<Assignment Name = "CM1"/>*
          *<Assignment Name = "CM2"/>*

          The above XML will do four things:

          (1). add two new dimension members into the ACCOUNT dimension in the AW

          (2). add a new model in the AW, called ACCOUNT_CALC_MOD_MODEL

          (3). add those two dimension members into that model

          (4). Add the following line (as last line) in the AGGMAP of each cube that uses ACCOUNT dimension

          Note that if you query the CUBE_TABLE view that automatically gets generated by olap api, then in the column MEMBER_TYPE you will see value of 'C' for the above two new dimension members. For others it will be 'L' which means loaded.

          Since these are calculated members, OLAP will never load any data in the cube corresponding to these calculated members, even if you provide data in your cube source sql table/view for these two members, as these members are tagged as 'C' or Calculated.
          • 2. Re: Custom aggregated cuts
            Thanks Nasar for the information.

            I am not sure I am looking for a way to add calculated members(custom members) because even if I consider one single year there will be around 65K members to add to a dimension. I am looking for a way where I can say "Time between start_date and end_date" type of queries. I have data at day level so depending upon whatever dates user choose I should be able to aggregate data between those two dates.
            • 3. Re: Custom aggregated cuts
              Are you looking to achieve this thru some kind of OLAP dml function?

              Can you do the aggregation in SQL, instead of OLAP, based on what users have selected?
              • 4. Re: Custom aggregated cuts
                I have total 15 dimensions and millions of records with complex calculated measures so I do not think SQL would be a better solution. Yes, I would like to use OLAP objects like custom aggmaps to achieve this.
                • 5. Re: Custom aggregated cuts
                  Does this all need to be built in to a SQL view or do you have the opportunity to run a PL/SQL procedure before the query? I am thinking of a procedure that would add the required calculation on the fly. E.g.

                  1) User request comes in
                  2) You call a PL/SQL procedure 'execute do_something_magical(start_date, end_date, ...)'
                  3) You run a SQL query to get the results
                  4) [Optional] You call a PL/SQL procedure 'execute clean_up_again(...)'
                  5) You return results to the user