Part four of three
In my innumerate fashion, we have now reached the fourth part of a three part series on building dimensions. Why can’t Cameron count? Putting aside the more obvious, “’Cos he’s an idiot” answer, the reason is I had planned part three to include code to generate dimension deletes via the Metadata Builder. That was the idea but it would have been an absolute monster of a post. The point of this blog is to inform you (and me), not bore you (and me) to death.
So that’s the excuse, but what’s to be covered in this post? Answer – create dimension load files programmatically through the Metadata Builder so that you never have to it again. Will it be as easy as selecting Flying Pizza (that’s a punch card in Jane’s left hand for those of you born after 1980 and also just what is Flying Pizza)? Just about, with a wee bit of customization.
Something that XF’s competitors ought to read
Yes, way back in the introduction to TheTruthAboutCPM when I said that this blog wouldn’t be about other products I meant it. Alas and alack, I fear I do need to address an important issue about a blog like this.
It has been pointed out to me that blog posts that go semi-deep into customization can be used as proof that XF is complicated or hard to use or tosses chewing gum on sidewalks or is otherwise morally impure or – and this is more likely – is at a competitive disadvantage because those very creative (ahem) customizable solutions are possible. To those of you (heh, you know who you are) for whom that particular shoe fits, For Shame; alternatively, You Don’t Get It.
XF stands for eXtensible Finance. That means that the tool can do practically anything if you need it to. Want to use the out-of-the-box functionality? Fine. In fact, beyond fine. In-built functionality that quickly and easily solve complex problems is why customers buy XF. They also buy XF because of its ability to enhance and extend a solution. This n00b’s Journey series will cover both how the product works natively and how it can be extended. If you (again, heh, you know who you are) want to view the ability – not the requirement but the ability – to do Cool Things That Other Products Can’t Do as a weakness, then you don’t really understand what solving an EPM/CPM problem is all about. Also, for shame ‘cos you’re not doing your customers any favors.
And with that, I very much hope that products that ought to stand on their own merits don’t have to slag off OneStream’s product as well as my blog.
On to the cool stuff
And it isn’t really all that hard. In fact, it’s dead easy as I’m simply modifying – there’s that concept of extending again – code that already exists in the Metadata Builder to build those dimension files.
In part two of this series, I outlined how to use XF’s Metadata Builder to create dimension build files via custom Excel formulas.
It occurred to me that if that Excel add-in can create dimension load files to build dimensions it could just as easily create dimension load files to delete dimensions. After all, it’s just a couple of XML tags.
Creating custom functions in Excel
The Metadata Builder’s raison d’être is to, natch, build metadata. It does that by providing preformatted worksheets for each type of dimension member property and uses custom formulas to create the necessary XML. All you need do is copy or extend (I slay me sometimes) the cell formulas in the worksheet to do so. The hardest bit of all of this is inserting rows and then doing a fill down. Easy.
Here’s a very partial list of the add-in’s custom functions. Again, you really don’t even need to look at these: go to the relevant sheet, insert rows for your 50 or 500 or 50,000 member parent/child dimension and have at it. If you have a perverse need to create the formulas at will, click on the Formulas ribbon and then the Insert Function button:
If loading a dimension is as simple as copying and pasting a formula, surely removing some or all members of a dimension can be done as well. And it can. All that is needed are a few (like two) custom Excel functions.
Where the code shouldn’t and should go
When I first wrote this code I put it directly into the Metadata Builder add-in file. This is a Bad Idea because when said add-in is upgraded, all of your wonderful work will be gone as in deleted. Don’t do that.
Instead, put the two functions into the Metadata Builder template file. Yes, you’ll have to move the code around but that is as easy as Save As with a new file name. Those of you who are Excel VBA jockeys can export the VBA module and then reimport it into a new template file but really, why bother when making a copy of the template file is so much simpler.
Another thing to think about: this code is not part of the Metadata Builder. Remember my mini-rant up above where I talked about extensibility? I have written simple extensible code to solve a problem and it isn’t OneStream’s job to
clean up my garbage code support my super elegant solution.
If writing moderately complex VBA code in Excel and navigating around a VBA project is not your thing, I don’t recommend implementing this. If however, you are an Excel VBA geek and are okay with using (and supporting) this code on your own, I think you’ll enjoy and get value out of this.
Again, please, please, please don’t call up OneStream support and say, “That idiot Cameron said this was easy peasey no big deasy and I used his code and it never worked/the Metadata Builder changed and his code didn’t work/I did it wrong and it didn’t work/it blew up the Metadata Builder and now nothing works, etc.” All semi-humorous bits aside, I’m deadly serious about this because: it’s unfair to dump my code on Support’s shoulders, I am a consultant, not a true software developer so there may be holes in the code that could drive a fleet of trucks through and I just don’t see it, and – and this is most important – if you do that I’ll almost certainly never write a blog post like this again and that would be a pity. I may even yank this one. Ugh.
You Have Been Warned.
A super-brief introduction to customer functions
If you’d like an overview of what custom functions are and how to create them, have a read of Microsoft’s old-but-still valid Create Custom Functions in 2007.
All done? Good. You see now it’s not that hard. In fact it’s as simple as:
Function functionname(parameter1, parameter2, etc.) amazing stuff End Function
And then in Excel you call it with:
That might look like:
And off you go with custom functionality. That really is it.
Assuming you’re comfortable with VBA, go into the VBA editor
- Insert a code module (you could put this onto a sheet but don’t as a sheet deletion will also be a code deletion.
- Enter your ultrasuperawesomemagic code in a function
- Reference it as a formula.
Yes, you have to use this and explicitly declare all of your variables. You’ll thank me later when this saves you from Variant Hell.
The Metadata Builder add-in uses a slew of constants to reduce coding between similar functions. The code in the workbook can’t see those constants (although the formulas in Excel can see the custom functions) so I’ve had to copy in a handful of these. There is as well a single custom constant for deletion.
NB – I could have just coded in the strings that make up these constants but that’s just ugly.
''' Constants required for formulas, lifted directly from the Metadata Builder 'Member Public Const g_StartMember As String = "<member" Public Const g_EndMember As String = "</member>" 'Properties Public Const g_EndProperty As String = "/>" 'Relationship Public Const g_StartRelationship As String = "<relationship" Public Const g_RelationshipParent As String = " parent=" Public Const g_RelationshipChild As String = " child=" 'General Public Const g_EscapeAmperstand = "&" Public Const g_Name As String = " name=" 'Action -- this is all that is unique for the delete Public Const g_ActionDelete = " action=" & """" & "Delete" & """"
This is the one other bit I lifted from the add-in. It replaces the ampersand (&) character with the constant g_EscapeAmperstand (you know, I think it’s misspelt but it doesn’t matter) which resolves to &.
''' Purpose: Get rid of invalid XML characters, lifted directly from the Metadata Builder Public Function EscapeInvalidXMLCharacters(xmlText As String) As String On Error GoTo Trap: 'Return the line EscapeInvalidXMLCharacters = Replace$(xmlText, "&", g_EscapeAmperstand) Exit Function Trap: MsgBox Err.Description, vbInformation, "Custom escape invalid XML function" End Function
This custom (mine as opposed to Metadata Builder’s) code generates the XML for a member delete.
''' Purpose: Create XML member delete string ''' Written by: Cameron Lackpour ''' Modified: 6 Nov 2017, initial write ''' Notes: Member deletes can only happen *after* relationship deletes occur ''' Example -- <member name="Pennsylvania" action="Delete" /> Public Function GetDeleteMemberXML(strMember As String) ''' Explicit declaration of variable type(s) Dim strLine As String ''' Error declaration On Error GoTo Trap: strLine = g_StartMember strLine = strLine & g_Name & """" & EscapeInvalidXMLCharacters(strMember) & """" strLine = strLine & g_ActionDelete strLine = strLine & g_EndProperty GetDeleteMemberXML = strLine Exit Function ''' Error handler Trap: MsgBox Err.Description, vbInformation, "Custom member delete function" End Function
This custom code generates the XML for a relationship delete.
''' Purpose: Create XML member relatioship delete string ''' Written by: Cameron Lackpour ''' Modified: 6 Nov 2017, initial write ''' Notes: Member deletes can only happen *after* relationship deletes occur ''' Example -- <relationship parent="East" child="Pennsylvania" action="Delete" /> Public Function GetDeleteRelationshipXML(strParent As String, strChild As String) ''' Explicit declaration of variable type(s) Dim strLine As String ''' Error declaration On Error GoTo Trap: strLine = strLine & g_StartRelationship strLine = strLine & g_RelationshipParent & """" & EscapeInvalidXMLCharacters(strParent) & """" strLine = strLine & g_RelationshipChild & """" & EscapeInvalidXMLCharacters(strChild) & """" strLine = strLine & g_ActionDelete strLine = strLine & g_EndProperty GetDeleteRelationshipXML = strLine Exit Function ''' Error handler Trap: MsgBox Err.Description, vbInformation, "Custom relationship delete function" End Function
And that’s the end of the code
Taking out all of the blank lines and comments, this is a grand total of 43 lines of code. That ain’t much considering what it does.
They’re called custom functions because they are in fact functions you can use in an Excel worksheet as shown below for member delete:
And here it is for relationship delete:
Put those formulas together with the metadata formulas that are already on the sheet like so:
Copy the result:
And here it is in a text editor. You’re done.
Load (or in this case, delete) away.
There, wasn’t that a lot easier than creating this by hand? It is after all called the Metadata Builder. So maybe this should be called the Metadata Destroyer of Dimensions?
As the build of the XML is now a formula you could easily create a second or third column that is just for relationship deletes or just for member deletes. Excel is really your only limit.
The one thing to remember about all of this is that you’ll need to move this code from workbook to workbook if you fall in love with it. Again, I purposely did not make this part of the true Metadata Builder add-in because I (and you) have no control over that product.
The end of this post and most definitely the end of this series
Part four of three is, I think most would agree, enough of the let’s-build-a-dimension theme. I didn’t mean to go so deeply into the process but it got – sadly, really – interesting.
I am going to cover dimensions going forward but it’s going to be much more on the functional and out-of-the-box functionality.
Be seeing you.