4 Replies Latest reply: Nov 14, 2012 3:57 AM by DavidGreenfield RSS

    Dimension Attribute Write Back

    938723
      Is it possible to write-back and update a dimension attribute. If so, could someone please post an example doing this.

      Thanks!
        • 1. Re: Dimension Attribute Write Back
          Nasar-Oracle
          I assume that your question is about Oracle-OLAP dimension attribute (and not Essbase).

          You will have to come up with a custom front-end (web or windows) where you will enter (or change) the attribute value. Then behind the scenes, you will call DBMS_CUBE.BUILD procedure to update the attribute value for that dimension member.

          Here is an example:
          Dimension = 'ACC', Hierarchy = 'ACCHIER', Dimension Member = 'CM1' and we want to update an attribute value called "LONG_DESCRIPTION" for this member 'CM1'.

          begin
          dbms_cube.build(q'! "ACC" USING (
          SET "ACC"."LONG_DESCRIPTION"["ACC" = 'CM1'] = CAST('Custom Member 1' AS VARCHAR2),
          MERGE INTO HIERARCHIES ("ACC"."ACCHIER")  VALUES ('CM1', 'ALL_ACC', NULL),
          COMPILE)!');
          end;
          • 2. Re: Dimension Attribute Write Back
            DavidGreenfield
            The feature Nasar mention is also available as an OLAP DML call. Search for documentation on UPDATE_ATTRIBUTE_VALUE.

            http://docs.oracle.com/cd/E11882_01/olap.112/e17122/dml_commands_2070.htm#CCHBIGEG
            CALL UPDATE_ATTRIBUTE_VALUE(dim_member_id, attribute_name, attribute_value [, auto_compile ])
            This is all available in version 11.2.0.2 and higher.

            Edited by: David Greenfield on Nov 14, 2012 9:57 AM
            Corrected UPDATE_ATTRIBUTE_MEMBER to UPDATE_ATTRIBUTE_VALUE
            • 3. Re: Dimension Attribute Write Back
              938723
              The DML call did work. Call is, UPDATE_ATTRIBUTE_VALUE. There is no function UPDATE_ATTRIBUTE_MEMBER.

              Also, I used a call similar to:

              dbms_cube.build(q'!
              "PRODUCT" USING (SET "PRODUCT"."LONG_DESCRIPTION"["PRODUCT" = 'ITEM_OPT MOUSE']
              = CAST('Optical Mouse' AS VARCHAR2))
              !');

              Found here:

              http://docs.oracle.com/cd/E11882_01/appdev.112/e23448/d_cube.htm

              Worked like a charm!

              Edited by: user7853353 on Nov 13, 2012 7:18 PM
              • 4. Re: Dimension Attribute Write Back
                DavidGreenfield
                Thanks for catching the UPDATE_ATTRIBUTE_MEMBER error. That will teach me to copy and paste from the documentation! I will correct my previous post for the record.