"empty nester" parents and lingering data

[photon]

If a member has no parent then it's an orphan but if a parent member had children and they all get moved, the former parent is now...

Regardless of the term, this company loves to do big reorgs a couple times a year that leave whole branches of a hierarchy empty. It's put me in the weird situation of having to hunt down these obsolete (disowned?) parents and manually remove them because they're now base level members with data and consolidations don't clear them the way I expected.

From a technical viewpoint, I can see why this happens, as it's now just a base member that happens to have data and the consolidation doesn't particularly care *how* the data got there so it's not going to remove it. It's just... counterintuitive, unwanted, wrong, and generally annoying.

For the most part, they've just orphaned the offending ex-parents and ignored them, forever. The few they have deleted have been cleared via the rather laborious method of creating a new (empty) member under the obsolete parent, consolidating every scenario in every year, removing the child, then deleting the parent. This process is further complicated by the fact that in some scenarios these members are populated with durable calcs so I have to go out of my way to clear that data manually. In all, it can be a tremendous amount of work to remove one member and I can see why they've opted to just ignore the problem until now.

The question is, what approach can I use to find, clear, and (ideally) remove these obsolete members for all time?

[Henning]

Hi, am I right in assuming that you are referring to members in the entity dimension?

If so, is what you are describing for planning, management reporting, statutory reporting, or another process?

My first thought is, why removing those parents in the first place? Yes, sometimes it happens that members get moved and parents may get obsolete in the entity dimension, but for the most part, they remain as is at least for historical comparisons and base entities get duplicated into the new hierarchy rather than literally cut out of the old one and transferred to the next parent. Not preserving the historical structures makes time based reporting impossible, unless there is a central hierarchy that does not get changed which serves that purpose.

There are two posts on IdeaStream that you might like to give kudos to if you agree with what they are suggesting:

JIRA Ticket: PF-1825 (onestreamsoftware.com)

JIRA Ticket: PF-7988 (onestreamsoftware.com)

To answer you question directly:

To "find" entities that were a parent previously and that your business no longer requires is more of a process management item to me in which the reorganization of the structure needs to follow a defined process in which those former-parents are clearly documented as well and marked for removal or archiving. This can be assisted technically by driving and documenting this process within OneStream. But the process and requirements need to be defined first and the technical aspect of it (workspace) will be built based on that.

There are two other posts with ideas how to delete entities, but I assume that is not new to you (sharing, just in case it helps):

Unable to delete Entity member - OneStream Community (onestreamsoftware.com)

Unable to delete Entity member v2 - OneStream Community (onestreamsoftware.com)

[photon]

Not at all coincidentally, I had already kudoed the 7988 request but it looks like I'm nearly alone in this struggle though as it's been so long since I did that I forgot about it and there are still only a total of two. I did go ahead and kudo the other one as well though. It does seem odd that there's not a "no, for really real, I made this member on accident and it absolutely has to go, I don't care what data it has in it, I'm quite confident that it needs to be deleted, because I'm the boss and not you" function anywhere.

One of the weird situations when running this software is that a lot of requirements get dictated by finance directors and VPs and various managers who care nothing for practicality or common sense or technical limitations; they just need it done or the numbers will be wrong. If a department with four child entities gets dissolved and three of its entities go to an existing department and the fourth goes to a totally new department, that original department is well and truly dead. Having it stick around in the hierarchy is just plain wrong and causes double-counting. I get why it needs to be done, it's just a hassle.

I guess, in my head, I was wondering if I could find the offending members by writing some kind of rule that looked at number of children and the state of the data cells. Since the data at the parent level would have been consolidated and not entered directly, would that give me something to filter on?

Based on what I've seen so far, it seems like the general answer to my original questions is "no, not really, do it the hard way."

[Henning]

I cannot think of a reliable way that will get you there. You can try using O#AdjConsolidated as the data moves from AdjInput to AdjConsolidated in the entity dimension, but that assumes that journals have been booked on a level below the former parent you are looking for. If journals are used a lot in your organization, maybe that helps when you search for a base entity with data on that member.

Do parent entities use the same security in your solution? Maybe you can identify former parents based on this? Any other difference in your case? Any property that only base or parent entities were supposed to use?

Going forward - I know that does not help you now - you could establish a process where the parent / child status is stored on an entity's text property, that way you could filter base entities based on that easily.

[JackLacava]

> consolidations don't clear them the way I expected. [...] consolidation doesn't particularly care *how* the data got there

Well, that's not really the case... The Storage Type of the cells will determine what happens to the numbers when the DataUnit calculates, in combination with the "Clear Calculated Data During Calc" option on the Scenario and "If NoData" settings on the Cube. The Storage Type will depend on how the data got there.

> in some scenarios these members are populated with durable calcs 

That's the problem, lol... If you tell the product "this data should never be deleted unless I very explicitly say otherwise", you'll have to be very explicit when you change your mind 😉

> if I could find the offending members by writing some kind of rule that looked at number of children and the state of the data cells.

You probably can, but getting it right will depend a lot on the precise circumstances of your data. For example, something like this (untested) script should clear all cells that are not marked as Real data in the current Data Unit:

If Not api.Entity.HasChildren
	Dim filtered As New DataBuffer()
	Dim dbf As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZero(A#All)")
	For Each cell As DataBufferCell In dbf.DataBufferCells.Values
		If cell.CellStatus.IsRealData Then
			filtered.SetCell(si, New DataBufferCell(cell), False)
		Else 
			filtered.SetCell(si, New DataBufferCell(cell.DataBufferCellPk, Nothing, DataCEllStatus.CreateDataCellStatus(True, cell.CellStatus.Invalid)))
		End If
	Next
	api.Data.ClearCalculatedData("A#All", True, True, True, True)
	api.Data.SetDataBuffer(filtered, api.Data.GetExpressionDestinationInfo(""))
	If api.Data.GetDataBufferUsingFormula("RemoveZero(A#All)").DataBufferCells.Count = 0 Then
		brapi.ErrorLog.LogMessage(si, $"Delete candidate: {api.Pov.Entity.Name}")
	End If 
End If

This is very rough and probably excessive, but the point is to show that the CellStatus property of a cell has a few different methods to test the type of data it holds, so depending on what you're interested in clearing you can test for Real, "Real or Derived", Derived, result of a calculation, locked, etc - use Intellisense to look them up. I'd run this kind of thing in a custom calculate, targeting base members and C#Local, then Consolidate.

Once data is cleared, deletion can be automated too, but we wouldn't recommend it.