This discussion is archived
6 Replies Latest reply: Oct 30, 2012 2:50 PM by DavidGreenfield RSS

Delete data for a single dimension member

855715 Newbie
Currently Being Moderated
Is there anyway to delete all the data for a single dimension member from a particular cube?

olap version: 11.2.0.3
  • 1. Re: Delete data for a single dimension member
    Nasar Journeyer
    Currently Being Moderated
    David Greenfield provided an example in this posting:
    CUBE_DFLT_PARTITION_LEVEL in 11g?

    You can change the WHERE condition to one dimension member, instead of IS DESCENDANT logic mentioned in the post.
  • 2. Re: Delete data for a single dimension member
    DavidGreenfield Expert
    Currently Being Moderated
    Nasar's approach should work, so I would use it so long as it performs well. But I have (several years ago) seen performance issues with CLEAR VALUES when it is applied to something smaller than an entire partition. So here is another method that also uses the FOR...BUILD construct. It clears the value of the PRICE measure in PRICE_COST_CUBE for PRODUCT = '48'. The cube has only two dimensions, TIME and PRODUCT, and I only want to assign to the leaf level of TIME, which is MONTH.
    BEGIN 
      DBMS_CUBE.BUILD(Q'!
       "PRICE_COST_CUBE" USING(
        FOR 
         "TIME" LEVELS ("TIME"."MONTH"),
         "PRODUCT" WHERE "PRODUCT".DIM_KEY = '48'
        BUILD (SET PRICE_COST_CUBE.PRICE = NULL ))!',
        PARALLELISM=>0, 
        ADD_DIMENSIONS=>FALSE);
    END;
    /
    You can increase parallelism here if your cube is partitioned. If you need to clear out other measures at the same time, then add additional SET command in the central block. E.g.
    BUILD 
    (
      SET PRICE_COST_CUBE.PRICE = NULL,
      SET PRICE_COST_CUBE.COST = NULL
    )
    After this step you can re-aggregate the cube to make the aggregate values reflect the change.

    BEGIN 
      DBMS_CUBE.BUILD(Q'!
       "PRICE_COST_CUBE" USING(
        FOR 
         "TIME" LEVELS ("TIME"."MONTH"),
         "PRODUCT" WHERE "PRODUCT".DIM_KEY = '48'
        BUILD (SET PRICE_COST_CUBE.PRICE = NULL ),
        SOLVE)!',
        PARALLELISM=>0, 
        ADD_DIMENSIONS=>FALSE);
    END;
    /
    The simple assignment to NULL will generate a simple assignment in the OLAP DML.
    PRICE_COST_CUBE_STORED=NA
    You can force the code to loop over the existing composite by replacing NULL with something that evaluates to NULL, but involves the composite.
    SET PRICE_COST_CUBEW.PRICE = NVL2(NULL, PRICE_COST_CUBE.PRICE, NULL)
    Here is the generated OLAP DML assignment.
    PRICE_COST_CUBEW_STORED=NVL2(NA, GLOBAL.GLOBAL!PRICE_COST_CUBEW_PRICE
    , NA)  ACROSS GLOBAL.GLOBAL!PRICE_COST_CUBEW_COMPOSITE
  • 3. Re: Delete data for a single dimension member
    855715 Newbie
    Currently Being Moderated
    Thanks.

    Once I do above steps the deleted member will also disappear from the cube composite as well?

    Do I also need to run below commands or is just for the information?



    PRICE_COST_CUBE_STORED=NA

    You can force the code to loop over the existing composite by replacing NULL with something that evaluates to NULL, but involves the composite.

    SET PRICE_COST_CUBEW.PRICE = NVL2(NULL, PRICE_COST_CUBE.PRICE, NULL)

    Here is the generated OLAP DML assignment.

    PRICE_COST_CUBE_STORED=NVL2(NA, GLOBAL.GLOBAL!PRICE_COST_CUBEW_PRICE
    , NA) ACROSS GLOBAL.GLOBAL!PRICE_COST_CUBE_COMPOSITE
  • 4. Re: Delete data for a single dimension member
    DavidGreenfield Expert
    Currently Being Moderated
    Both my and Nasar's methods will set the value of the variable to NA for the selected dimension member. It will not actually remove the member from the composite (because this is very expensive given the structure of the composites). NA values are suppressed at the cube view level, so they are effectively gone.

    If you delete the member from the dimension itself, then it will be deleted from the composite (at least logically). But the cost of this is that you will need to completely re-aggregate the cube. The two methods described should allow incremental aggregation of the cube.

    You do not need to execute the additional OLAP DML -- that is for information only. It will also be of interest to long time OLAP DML gurus, who learn through hard experience to care about such things.
  • 5. Re: Delete data for a single dimension member
    855715 Newbie
    Currently Being Moderated
    Hello David,

    Do I need to do this in two separate steps or can I combine both of them(Set NA , Solve) into a single step?

    Step1:

    BEGIN
    DBMS_CUBE.BUILD(Q'!
    "PRICE_COST_CUBE" USING(
    FOR
    "TIME" LEVELS ("TIME"."MONTH"),
    "PRODUCT" WHERE "PRODUCT".DIM_KEY = '48'
    BUILD (SET PRICE_COST_CUBE.PRICE = NULL ))!',
    PARALLELISM=>0,
    ADD_DIMENSIONS=>FALSE);
    END;

    Step2:

    BEGIN
    DBMS_CUBE.BUILD(Q'!
    "PRICE_COST_CUBE" USING(
    FOR
    "TIME" LEVELS ("TIME"."MONTH"),
    "PRODUCT" WHERE "PRODUCT".DIM_KEY = '48'
    BUILD (SET PRICE_COST_CUBE.PRICE = NULL ),
    SOLVE)!',
    PARALLELISM=>0,
    ADD_DIMENSIONS=>FALSE);
    END;
  • 6. Re: Delete data for a single dimension member
    DavidGreenfield Expert
    Currently Being Moderated
    You don't need to do both of these. What you call "Step2" can be thought of as "Step1 with an additional SOLVE". So I should try just running Step2 (sutably adjusted for your schema). If you really want to separate into two steps you would do this
    BEGIN
     DBMS_CUBE.BUILD(Q'!
     "PRICE_COST_CUBE" USING(
      FOR
       "TIME" LEVELS ("TIME"."MONTH"),
       "PRODUCT" WHERE "PRODUCT".DIM_KEY = '48'
        BUILD (SET PRICE_COST_CUBE.PRICE = NULL ))!',
      PARALLELISM=>0,
      ADD_DIMENSIONS=>FALSE);
    END;
    /
    
    BEGIN
     DBMS_CUBE.BUILD(Q'!"PRICE_COST_CUBE" USING(SOLVE)!', PARALLELISM=>0, ADD_DIMENSIONS=>FALSE);
    END;
    /
    The only reason to do this is if you need to NULL out many different cells and then run a final SOLVE (aggregation) afterwards.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points