Forum Discussion

kmd's avatar
kmd
Contributor
2 years ago

Clearing data for a very specific intersection / Data Unit

Hi all,

Trying to delete an unnecessary member from the metadata but it won't delete due to 'existing' data.  There isn't actually a value where it says a value exists but OS believes there is data there in currencies that are non-local.

I've tried the DM Clear Data job but it only seems to see the Cube, Entity, Time periods and Scenario.  It appears to ignore any other dimensions specifications I include in any of those filters (i.e. additional UD dimension specifics).  Which makes it too broad for our requirements.  Tried to test this and even though the DM job succeeded for that cube/entity/scenario/time period, it STILL thinks there's data there and won't allow the deletion of the member.

Hoping someone out there has run into a similar situation and has some suggestions to offer on how to truly clear a member completely so it can be removed from the metadata.  Thanks

Data Integration
Data Management
  • JackLacava's avatar
    JackLacava
    2 years ago

    If you have a problem, if no one else can help, and if you can find them, maybe you can use... finance Api rules!

     

    api.Data.ClearCalculatedData(True, True, True, True, "A#MyAccountParent.Base", "F#MyFlowParent.Base") '... other filters if necessary
api.Data.SetDataCell("A#MyAccount:F#MyFlow", 0,True) ' other members as necessary, if not specified will fall back to None

     

    Drop that in a Custom Calculate, execute it with the related Data Management step, and you should be able to clear the specific intersections you need.

  • MikeG's avatar
    MikeG
    2 years ago

    There could be translated data at a non-local currency.  Go to System > Database tab > then Data Records.  Put in the Time in the format T#2023 (full year) and in this view Value1, Value2, Value3 represent the Months M1, M2, M3.  This will at least identify the intersection of data where the data exists.

    If it is only translated data, and the source data in Local has been deleted, then you just need to run a Force Consolidate to clear the translated data.  

    Lastly, I have a BR that will do a targeted clear of a data cell.  If you paste a reply here of the intersection you are trying to clear (Entity, Account, Origin, etc) I'll mock it up and post the code here for you.

    Hope that helps.

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    2 years ago

    If you have a problem, if no one else can help, and if you can find them, maybe you can use... finance Api rules!

     

    api.Data.ClearCalculatedData(True, True, True, True, "A#MyAccountParent.Base", "F#MyFlowParent.Base") '... other filters if necessary
api.Data.SetDataCell("A#MyAccount:F#MyFlow", 0,True) ' other members as necessary, if not specified will fall back to None

     

    Drop that in a Custom Calculate, execute it with the related Data Management step, and you should be able to clear the specific intersections you need.

    • kmd's avatar
      kmd
      Contributor
      2 years ago

      Thank you so much for this - it will be very handy for us as we have to clear > 2000 members.  Very much appreciated!!

  • MikeG's avatar
    MikeG
    Contributor III
    2 years ago

    There could be translated data at a non-local currency.  Go to System > Database tab > then Data Records.  Put in the Time in the format T#2023 (full year) and in this view Value1, Value2, Value3 represent the Months M1, M2, M3.  This will at least identify the intersection of data where the data exists.

    If it is only translated data, and the source data in Local has been deleted, then you just need to run a Force Consolidate to clear the translated data.  

    Lastly, I have a BR that will do a targeted clear of a data cell.  If you paste a reply here of the intersection you are trying to clear (Entity, Account, Origin, etc) I'll mock it up and post the code here for you.

    Hope that helps.

    • kmd's avatar
      kmd
      Contributor
      2 years ago

      Thank you so much for this.  You are exactly right.  After I posted the question, I wondered about the same thing and went ahead with a force consol and voila - it worked.  Thank you again

    • kmd's avatar
      kmd
      Contributor
      2 years ago

      Hey Mike - thanks so much for the offer!  I'm going to take you up on it.
      Below is the exact intersection that needs to be cleared.
      But a few questions:

      1. Is there a way to insert a variable so that it runs for multiple currencies?
      2. I will eventually want to parameterize this so that I can be prompted for every dimension each time I want to run it.  What is the best way to set up my DM job so that this is possible?  I assume my DM job is a Run Business Rule but that doesn't seem to provide any options to include parameters within the step itself - am I adding the parameters to the Sequence?  And if so, would my syntax in the BR then reflect this as say, E#|!myparam!| where myparam is the same parameter set in the Sequence for the BR?

      Sorry for all the questions - I'm clearly not a coder ðŸ˜¬

      api.Data.ClearCalculatedData("Cb#CE_GP_Cube:E#BU_Total_Mena:C#USD:S#2022M9_E3:T#2021:A#GPStnd_Freight:F#EndBal_Load:O#Import:I#None:U1#Other_Plan_Customer:U2#CE_BU_NoBU:U3#None:U4#CESKU_207995:U5#SINGAPORE_LOC:U6#None:U7#None:U8#None", true, true, true, true)

       

      Thanks again so much for your assistance.  Very much appreciated!

  • MikeG's avatar
    MikeG
    Contributor III
    2 years ago

    Keep that code handy that JackLacava posted in this thread.  That is solid.  On your OS journey, you'll need to know how to do what Jack pasted here at some point.

    Glad that worked for you.