Don’t do it, the manual bit

In our last exciting installment, Yr. Obt. Svt. took you on a trip through the most basic of dimension building processes, to wit, building a dimension by hand.

That’s all well and good but it doesn’t really reflect true application development and administration as doing just about anything by hand (sawing wood, cutting the lawn, filling out that 1040A, casting a budget, etc.) is painful for anything other than one-offs, a small scope, or something that hardly ever changes. Repetitive actions should be automated else we revert to paper ledgers and spreadsheets, fountain pens (actually, I use one), and green eyeshades. Instead, I daresay and hope you’ll do most of your loading from batch.

But how oh how oh how does your average geek load a dimension?

One way might be to take an existing dimension, extract it to a file (in OneStream XF aka XF that’s an XML file whose format I’ll cover in the next post – see how I’m stringing you along?) and then use that file to load said dimension in again to this application or to another. Hint: this sentence just described how to migrate dimensionality across apps or environments. XML files arent that hard to understand but most normal – even me – humans don’t want to hack that kind of file unless necessary and even then only if the editing is simple and limited. What to do?

NB – Please note that the Geography dimension gets cleared, and then loaded, and then cleared, and loaded, pro infinito. There wouldn’t be much point in showing you how to load a dimension if the dimension was already fully populated.

Go shopping

I hate shopping or at least I hate spending a lot of time doing it. Let’s hope this is fast.

If you’re an employee (me), a partner, or a customer of OneStream, there’s this magical thing called the XF Marketplace which has…stuff, magical stuff. As with most of A n00b’s Journey, I’m not going to be able to even scratch the surface of what the Marketplace has on offer. I’ll do my best later to bring you (and me) up to speed as this blog progresses.

For now, I’ll log into the Marketplace:

Someone has a sense of humor when it comes to shopping trolleys. Okeydoke, off to the Store.

There are a ton(ne for those following the metric system) of features and modules and utilities and knowledge here. Almost all of which I will ignore for the time being and go to the Software Download link and thence to the Excel Metadata Editor:

As you can read from the partial description below, the metadata builder takes simple Excel sheets and creates the XML that XF uses to load dimensions.

Download it, follow the six step (I love short and sweet instructions). xlam registration step, and you now have a workbook with custom functions that create the XML needed to load a dimension.

Wot’ll she do, mister?

Let’s do it, let’s use the Metadata Builder

Format and purpose

Here’s what it looks like:

The book is pretty easy to suss out – each dimension type has its own tab. Within each dimension tab there are different properties, e.g. Account has unique properties like Allow Input versus Entities’ Currency.

Formulas for building

This parent-child table with attendant properties has to get converted into XML. How? As the screenshot of Marketplace download states, through functions. Here are a subset:

Note that these are not the functions from the XF Excel add-in. That’s a different subject and (one day) a different post.

Formula-driven

Assuming I can figure it out, in the next (or two or so) blog posts I’m going to try a quick-and-dirty addition to these formula functions to help with a different part of metadata management. I hope.

The formula itself is simple:

In the case of Total Geography in the relationship section this (plus a whole lot more off to the right aka columns A through N with a few skipped in the middle) content and the GetEntityRelationshipXML function:

From Excel to XML

Manifests itself as this result in cell AU17:

And copied and pasted into a text editor. Here’s what it looks like with a few tabs and carriage returns for your amusement, admiration, and edification.

I would point out that only the sad and hopeless curious and inquisitive actually care about the content of the file. Why? This is like people who take photos of food and then posting on social media: food is for eating just as dimension files are for loading.  Social media rant over.

Create the XML dimension build file

The above relationship is for but one element of one member. To get a load file’s content, copy the range of cells and paste into a text editor.

Build that dimension

Save it as a file with a .xml extension, and then return to the XF client (I am using the Windows client – another future blog post – because browser UIs give me agita unless absolutely necessary) click on Application->Load/Extract:

Select a file:

Pick the file I just saved:

Load it to the application:

Click on Refresh Page or Refresh App:

Ta da:

Which is exactly what matches this:

No manual member creation required!

Yes, you’re looking at a lot(ish) of text and quite a few images but it takes longer to read this than it takes to perform.

To recap, you:

1. Perform a one-time download and install of the metadata builder workbook.
2. Enter a list of members and parent/child relationships, inserting lines and copying formulas if your dimension is longer than six lines which I daresay it will be.
3. Copy the XML off to the right from the cell formulas.
4. Paste the clipboard into a text file.
5. Load the text file into the OneStream client.
6. Bask in the beauty of your built dimension.

Seriously, this takes like a minute after the first time. Easy peasy no big deasy.

And that’s the end, almost

So far in this series I’ve covered how to build a dimension by hand and how to load it from the Metadata Builder’s parent/child format.

What else does one do with a dimension? Make mistakes, obvs., curse yourself (or others if you’re the blaming type), and then delete either manually or in batch and rebuild. And that’s exactly what I’m going to show you next time round.

Be seeing you.