The final third

The last two posts on dimension building have focused on building dimensions either by hand or via the Metadata Builder. But what about deleting members?

You may not make mistakes but Yr. Obt. Svt. is, unfortunately, all too well familiar with, “Aw, bugger, I didn’t mean to add those 10,000 products/200 entities/14 departments/etc. I really didn’t mean to. Bugger. And now I need to get rid of some or all of them. Triple bugger.” Mayhaps you too have done so? Let’s hope so as how to do that is the point of this, the third and final (Hah! No, part four of three is now planned. Also, I can’t count.) part of this series on managing hierarchies.

Driving a manual

Stick shifts (or if you’re perverse and have an old car, a three on a tree, or if you’re really crazy, a Wilson preselector, or if you’re ultra cool, an epicyclic transmission which I finally learnt how to use) are a key component of driving enjoyment because of the control they provide. Wind out the engine way past a “reasonable” rpm just to hear the exhaust bark, hold it in gear round a corner to avoid upsetting the car’s balance, pretend you’re Speed Racer as you pull away from stoplights – all romantic stuff if you’re a gearhead. But manuals come with a cost: you must know how to clutch and shift (or, if you’re really cool, double declutch for straight cut gearboxes), know when to shift and not stall out the engine, and can sometimes feel like you’re a one-armed oarsman as you struggle along in traffic. So control, yes, but it can be tedious in the wrong context.

What about manual member deletion? Akin to driving a stick shift on a horrible commute, this is something best experienced in small chunks as OneStream XF (XF for the terminally lazy, like moi) makes member deletion a multistep process. This is both good and bad because of this effort although you, Gentle Reader, will see that it is ultimately good .

To wit, member deletion in XF really forces you to think about the impact of member deletion, in part because it makes you clutch-shift-clutch (It has occurred to me that this gearhead analogy is lost on many who are not super-sad enough to care. Briefly and wildly digressively, gearchanges in manual transmissions require you to push in the clutch, pull or push the gear lever across from 1^st to 2^nd or 5^th to 4^th or whatever, and then release the clutch, making sure to time that clutch/shift/declutch action correctly to prevent the transmission from grenading itself. Do it a while and it’s second nature which is more than a little bit like how manual member deletion becomes after some practice. Wow, for once I didn’t go 100% off topic as the analogy holds. Whew.) when deleting a member because it makes you really focus on the impact of the deletion. Which is great till it’s not as we will see.

The bad. No, wait, the good.

In other tools, looking at a dimension in an editor is a simple case of: see it, select it, delete it although potentially at a very great cost. And at first glance, that’s what XF allows you to do.

Are you sure? Extra special sure? Sure you’re sure? Sure.

And what happens? KABOOM!

Unpossible! What’s going on?

It’s saving you from yourself

Think about what’s under Total Geography: East and its child Pennsylvania. What happens if you want to delete Total Geography? The members East and Pennsylvania must be deleted as well as their parent has gone to the Metadata Field in The Sky. And that means that the data resides in East and Pennsylvania are gone, gone, gone.

Did you mean to do that? Maybe, but maybe not. Per the above “You cannot remove a member that would result in orphaned child members.” message, XF will not allow you to willy-nilly delete parent members unless and until you’ve broken the relationship between parent members and their descendants. And that, Gentle Reader, is what’s called data integrity enforcement. And that’s A Good Thing.

If XF didn’t enforce removal of the aggregation relationship between Pennsylvania and East and East and Total Geography, what would happen? In the case of a dynamic hierarchy, once the leaf or level 0 member is removed, all of the dimension’s data (at least in the case of a Total Geography->East->Pennsylvania hierarchy) is gone. A stored hierarchy wouldn’t suffer complete data loss but would end up with “ghost data”, e.g. if Pennsylvania is removed, what exactly is East or Total Geography? Dunno. Both of these outcomes are Bad Things.

Seriously, you’ve got to be awfully sure that deletion of parent members is what you want to do. XF requires you to think long and hard about it. That’s another Good Thing unless you have an unnatural liking for inexplicable or simply wrong data.

You can if you want to, so long as it’s level 0

But does XF stop you from deleting level 0 or leaf members? Sooner or later that has to happen and as deleting a level 0 member impacts no other data component, this is easy to do.

It’s as simple as select, right click, delete.

That special warning:

And it’s gone:

Remember, if Pennsylvania is gone, all of the data across all of the intersections of Pennsylvania’s data is gone, adios, arrivederci, tot ziens, etc. XF did ask, and you went ahead and did it.

So how could I delete Pennsylvania and not East? It’s the parent to child data relationship that matters; the child (Pennsylvania) has no data dependent relationship with its parent (East) but the inverse of the member order does.

It’s not you, it’s me

We all know what that means when a soon-to-be-former significant other says that: it’s over and it’s time to listen to “Suicide Sinatra”. Sob. But it’s the only way forward. Double sob.

But that’s the way it has to be in XF: the relationships must be removed.

Let’s restore The Keystone State, and then break the bond of love and family. Right click on the parent member “Pennsylvania”, and select “Remove Relationships”.

A quick check to make this really is a desired action:

And here it is: XF’s Little Orphan Annie moment.

Be cruel and cut the strings:

Pennsylvania is gone. Or is it?

Let’s go look at XF’s Oliver Twist fate:

Yes, an orphan. Here’s poor Pennsylvania, with his cruel tormentors East and Total Geography:

The great Gilbert and Sullivan addresses the notion of orphans in an even better way in the Pirates of Penzance: “Orphan, lost parents or often, frequently?” It’s the former, not the latter:

Pennsylvania still exists as does its data. See, I told you XF protects you from your more impetuous moments.

Do you really want to delete East? Now you can:

Sure you’re sure?

Note that Pennsylvania is still extant:

Let’s get rid of that too:

All gone.

So let’s review

1. XF will allow you to delete level 0/leaf members with a simple right-click.
2. XF will not allow you to delete parent members unless you remove their parent-to-child relationships.
3. Removing a relationship works up, that is removing Pennsylvania’s relationship breaks its child-to-parent relationship with East.
4. When relationships are removed, level 0 members are orphaned.
5. Once fully orphaned, members – whether they were level 1 or 2 or 99 – can be deleted.

That’s manual deletion. What happens when more than a handful of members need to be deleted. We’re right back to the manual vs. automatic analogy. Do you really want to delete 10,000 products by hand? No, you don’t. There has to be a better way. And there is.

Driving an automatic

It stands to reason that if members can be loaded in batch, they can be deleted the same way. And they can. But how? The answer is XML.

Create the file

Go back to the second part of this tripartite series to follow the initial steps for getting a pretty dimension export file:

Instead of selection “Items to Extract”, Select Members to Delete, Include in XML, hit the + sign, and type in “Pennsylvania”. Do that last step for “East” as well.

Remember that relationships need to be deleted. To do that, select Relationships To Delete, Include in XML, hit the + sign, enter the parent “East” and the child “Pennsylvania”, and finally click OK.

Then extract:

Save it to disk using the default name or one of your own choosing and then take a look at the contents. It’s pretty straightforward stuff: one node to delete the members, the other to break their relationship

Does it work? Does it? Sort of.

I can load dimension members in, so surely I can load deletions out. Or in. You know what I mean.

And…

KABOOM!

Click on the error warning. Bugger, KABOOM again:

Hmph. What’s in line 11? Why is it telling me that there’s an invalid XML atribute, specifically the one that refers to “East”. Had I created the file by hand I’d suspect rather strongly that I was at fault, but it was XF. Weird.

What actually happened in XF?

Unpossible! Again. East and Pennsylvania were deleted and yet there was an error? How? It’s oh so obvious although I will confess it took me about 45 minutes to figure it out so at least for me it wasn’t that obvious.

The XML delete file has two elements: members to delete (Pennsylvania and East) and a relationship to delete (East/Pennsylvania). Let’s look at that error message again:

The error message is saying that the XML attribute is invalid. We know that East no longer (somehow) exists, ergo the message stating that removing the parent/child East/Pennsylania relationship is invalid. After all, if East doesn’t exist, how can it have a relationship with Pennsylvania? That’s right, it can’t.

And how did this happen? Because Pennsylvania and East were deleted before their relationship was removed. Duh, on my part.

Doing it in stages, but not correctly

Let’s reload the dimension and then make East come before Pennsylvania:

KABOOM, again!

Now we’re back to the way things worked manually. East is the parent of Pennsylvania, it’s first in the file, ergo it gets deleted first and KABOOM! is the result.

So let’s recap how batch multi-level file deletes work:

1. You can delete members in child first, parent second order.
2. You cannot delete members in the more logical (or at least to me more likely) parent first, child second order.

Do it in stages, but correctly

But what if the file format is in the second format? It’s just as it was with a manual delete: break the relationship first, then delete the parent.

Comment out the member delete and load again.

Load, this time successfully:

And there’s PA made an orphan.

I can now delete East by commenting out the relationship delete (if you leave it in XF will complain about removing something that no longer exists as above) and uncommenting the member delete, not caring about member order:

Let’s give it a try:

What ho!

Order matters

If member delete order matters, does the member delete and relationships delete section order matter? In other words, if the relationship deletion happened first and then the member deletion happened second, would it all happen in one pass? Am I asking you this question if I didn’t already know the answer? Yes and yes.

Load it again into a rebuild dimension, and:

Perfection. One file, one pass, member deletes in any order. Finally.

The Rules of the Road

It’s actually pretty simple:

1. Manual deletes
   1. Level 0 members
      1. Level 0 members can be deleted without taking into consideration any parent/child relationship.
   2. Parent deletes
      1. Parent deletes fail because XF enforces data integrity through children
      2. To delete a parent, delete the relationship between parent and child.
2. File deletes
   1. Same general rules as manual deletes.
   2. Two possible load file sections: member delete and relationship delete.
   3. Level 0 deletes are easy as they do not require a relationship delete.
   4. If relationship deletes are too much bother and parent level deletes are required, use low to high level order to delete children and then parents, e.g. Pennsylvania first, East second.
   5. If the member order is high to low
      1. First delete the relationship(s) and then the members by commenting and uncommenting the relevant sections.
      2. Or even better, ensure that relationship delete section comes first and the member delete section comes second.

As with most of this n00b’s Journey series, there’s an awful lot of explanation and an awful lot of spelling out of steps so don’t think this is really complicated. It’s click, right click, or edit an XML file. Yeah, I can be verbose. Sorry.

Having said that, I think file deletes could be made easier, especially when you, Gentle Reader, are creating the file by hand instead of relying on XF to create the file.

Tune in next time

And that’s exactly what, in my innumerate fashion, will be part four of my three part series – a hack of the Metadata Builder to give us a few ways of generating the delete process along with the already extant dimension build functionality.

Be seeing you.