6 Replies Latest reply on Mar 20, 2008 12:00 AM by Sreenivas R

    OLAP Custom Aggregators

    JChanowitz
      Hi,

      I've been attempting to build a custom aggregator for an OLAP schema, and haven't been able to figure out how the cube makes calls to the aggregator when it's being built. Are there any code examples out there?

      I'm trying to build a cube that captures expense data within a company. My flatdata contains a "Department" attribute, and departments nest. The default behavior of the cube would be to aggregrate expenses tied to the specific department on each row in the raw data, but accounting practices also require that a department attract costs for all of its descendents as well. The department hierarchy is stored in another table (each dept has a parent), so the department name itself doesn't provide insight into this roleup.

      I couldn't think of any obvious way to do this, so when adding raw expense transactions, I created another attribute that contained the department's ancestor path back to the root (just a delimited concat of the names). I'd like to create a custom aggregator for a measure that when calculating a department's expenses, it selects rows in the flat data that contains a substring match in the ancestor path, as well as a match in the department attribute.

      I suspect that I need to perform my custom totaling in an overriden computeLoop method. My problem is I don't know how to determine what tuple is being calculated within a specific call. If I knew that, I could just add in my other rows and pass that along.

      Has anyone else solved a problem like this?

      Thanks!
        • 1. Re: OLAP Custom Aggregators
          Sreenivas R Adobe Employee
          Assuming the hierarchy has fixed number of levels before reaching the leaf level departments it is possible to use a new dimension defintion to compute the results without going for a custom aggregator.

          Sample soure is available here.

          http://sreenivas.ramaswamy.googlepages.com/Departments.zip

          Write to me directly if you need more info at sreenivas.ramaswamy@gmail.com
          • 2. Re: OLAP Custom Aggregators
            JChanowitz Level 1
            Wow. Very, very nice. Thanks for this example. I do need to support a variable depth hierarchy (basically any well formed tree), so let me see if I can't extend what you've done to support that. I'll ping you if I get stuck.

            I do have a couple of questions regarding some of the methods you're using in your Dimension subclass:

            1) You're writing to Dimension's "hierarchies" attribute, which the docs say is read-only. Are they incorrect?

            2) I couldn't find a Dimension.addAttribute function documented anywhere. Is it protected?

            3) I also couldn't find a Hierarchy.createLevel function documented. From you example, it should be public.

            Is the documentation missing these, or am I mssing something? Since this package is not open source, we need to rely on the docs.


            Thanks again for your help with this.
            • 3. Re: OLAP Custom Aggregators
              JChanowitz Level 1
              Oops. Forget about question 1. I see I was looking in the interface, not the class.
              • 4. Re: OLAP Custom Aggregators
                Sreenivas R Adobe Employee
                The source should get extracted and become available in the FB Pro installed directory once the proper license is entered.

                addAttribute is a mx_internal function for now and so is createLevel.

                They are mx_internal for now because we didn't want to expose too many APIs leading to confusion.
                • 5. Re: OLAP Custom Aggregators
                  JChanowitz Level 1
                  Oh OK. Getting my hands on the source is even better motivation to moving off the trial than getting rid of that watermark.

                  Did I naively missed the memory implications of your assumption of an evenly deep organization? Does Hierarchy perform a cross join of the levels in the background? If so, then if I have a user with an uneven tree, with say one deep path and a bunch of shallow paths, then the hierarchy would create a very sparse and memory inefficient cube, right?

                  If so, then a custom aggregator may be the better solution, since it wouldn't generate sparseness. I'd lose the nice formatting in the row header, but I guess custom compare and display functions could still provide that.

                  Is this a valid concern?
                  • 6. Re: OLAP Custom Aggregators
                    Sreenivas R Adobe Employee
                    It would depend on the difference between the shallow and deepnees of the tree. Doing it in custom aggregator may invovled too much data checking and retrieval.