It’s Basic. It’s a Sample. It’s A Very Basic Sample.
Some people understand how to perform complex tasks or understand involved theory simply by reading some kind of supercalifragilisticexpialidocious documentation; I am not one of those people. Yes, I read the docs, yes, I think – insofar as I am able – quite a bit about what I’ve read, yes, I am capable of abstract thought (I hope, in an abstract way.), but at the end of the day, I learn best by doing. I’ve got to kick the tires, squeeze the apple (these are my favorite ones by a country mile), or employ whatever experiential learning metaphor you like best to truly understand.
Please try to put aside the irony of the above as the introduction to a blog post that attempts to explain how OneStream works as you, Gentle Reader, and Yr. Most Hmbl. & Obt. Svt. are about to go on a journey that illustrates our beloved CPM platform in as a practical manner as I can manage. In OneStream-land, I need to build a sample application.
But you say, (Do you? Do you? Really? Thought you did. Sad, but I’m glad you’re still here.), isn’t there already a comprehensive XF application that comes with the tool, to wit, GolfStream (non-OneStream customers/consultants, this is the for-real demo app)? Yes. But. It’s the showcase of everything XF can do. That’s all well and good, but if I am to ever stand on the shoulders of giants, I must first stand upright on my own. My sample application doesn’t have to do everything XF can do as that’s too much, too fast. I need something that’s so basic it’s practically laughable and yet can show me the, erm, basics.
In short, I need a simple, sample, and basic OneStream XF application ‘cos that’s what’s going to help me the concepts behind this fairly awesome tool.
And thus was born the XF app: “A very basic sample” aka AVBS.
It’s as basic as it is simple: a budgeting and planning application that tracks the somewhat unbelievably simple business of the Amalgamated & Consolidated CoffeE Company (ACE Co.) that, unsurprisingly given its name, sells my very favorite beverage in the whole wide world: coffee.
This app will track and predict the manufacture and sales of coffee across these United States. It couldn’t be simpler. What could be more enjoyable? Or more habit forming? Okay, seriously, don’t drink too much – it’ll make you Mr. Cranky Pants. But that’s coffee;A Very Basic Sample is good for you in practically unlimited quantities.
Along the way we’ll deal with the basics of dimensionality, data loading, calculations, data input, security, and just about anything else I (or you, via the comments section) can think of. Will it ever approach GolfStream? Nope. Will it act as that experiential learning path for we n00b’s? You bet.
So let’s get going.
A note – this post will only deal with the dimensions of A Very Basic Sample and at high level at that.
The core of a multidimensional application is…dimensions.
Hah! Surprised you, didn’t I? No? You hoped, I’ll bet, that dimensionality as a subject for this blog was as exhausted as exhausted could be but as I’ve only covered building (and deleting) the Entity dimension, and as XF is a multidimensional product, I sort of have to cover the other ones as after all that’s what defines what AVBS (No, not that pronunciation – this is a family-friendly blog. It’s pronounced “avbiz”) will be.
There’s a lot to dimensions in XF and I will thus not be able to cover all of the functionality in all of the dimensions as I would end up rewriting the relevant Design and Reference Guide section. As that clocks in at over 25 pages, you’ll get the bare basics for now. NB – The entire OneStream XF Design and Reference Guide is a somewhat stunning 616 pages in a pdf file. Never let it be said that the tool isn’t documented.
One other note – my perspective on these dimensions (and everything else really) is from a planning and reporting perspective as that’s my background and in a not terribly coincidental way, so is AVBS’. In future, I may occasionally allude to financial consolidation functionality but don’t count on it. Also, that stuff is hard and I am – as has been pointed out to me at multiple stages of my so-called professional life – lazy and also not particularly bright so I’ll stick to my knitting.
With that cop out firmly in place, off we go.
How many dimensions and what are they?
AVBS tracks the profitability of coffee sales. To do that, it must track: Time, Scenarios (there will eventually be a planning and budgeting component to this application although there is now just Actual), Accounts, Geography, and Products. That’s it. And yes, it’s very basic.
Whoops, I goofed. Again. There are actually four other required dimensions: Flow, View, Origin, and Consolidation that OneStream requires for each and every cube. I’ll explain these in a little bit but know that they differ in purpose and function from other products you may be familiar with. They’re actually kind of cool in a geeky way as well.
How many, really?
The short answer is that there are 18 overall dimensions in every XF application. Huh? What? Wait. Didn’t you just write that your app will have five(ish)? Surely even the math-challenged can see that one is 13 higher than the other.
This is where the difference between a OneStream application (an overall container for everything and all things in a given implementation) and an OneStream cube (a multidimensional view of some or even all of that data) becomes important. As a developer, you have 18 dimensions to play with; your cube’s requirements drive how many of them are actually used. I hope and pray there isn’t really anyone out there that truly has an 18 dimension cube. This isn’t for performance reasons (although that’s a consideration as well) but because actually understanding what a number split 18 ways means would be…confusing.
Regardless, XF allows you to do just that if you actually have the analytical need.
I think of OneStream’s dimension editor as a way to manage dimension types because within a developer-controlled dimension, e.g. Accounts, there can be many hierarchies which in turn can be shared as dimensions across cubes (think multidimensional models of which there are n per overall application) or be uniquely assigned to a cube. What this all means is that a single cube cannot have more than 18 dimensions but there can be many cubes and each one of them could have completely different dimension definitions within those cubes. The metadata manager really is just that – a very flexible way to manage dimensionality.
Yes, I know, a bit confusing but the key takeaway here is that you have more than enough dimension capacity.
Understanding a UD1 cube in a UD8 world
However an application or its sub cubes are defined, any single cube can have no more than 18. In fact, every cube must have 18 dimensions. Will anyone actually look at an 18 way intersection to view a single point of data? Maybe, but likely not.
What happens when you don’t use the full 18 in a cube? In your point of view (POV) simply uses the “None” member and in from a functional (and logical) perspective it’s ignored.
Lest this sound complicated, it isn’t: users see the dimensions they care about and see the word “None” where they don’t. It’s as easy as that because the cubes themselves – such as AVBS – have only the dimensions that matter.
Selecting UD4 (or UD3 or UD5, etc.) in AVBS shows this – it’s just noise. Ignore it.
What are XF’s dimension classes? They are: Accounts, Scenario, Entity, U(ser)D(efined)1 though UD8, Time, View, Flow, Origin, Consolidation, IC, and Parent (for AVBS, the Entity parent is generally ignored).
Dimensions have specific types, each of which have unique functionality. There are three types: custom, system, and derived.
Let’s review then, shall we?
Think of these as “do what you will” dimensions although that level of customization has some limits based on the dimension class, e.g. Accounts which deals with financial intelligence. The Accounts, Entity, Scenario, Flow, and the user-defined UD1 through UD8 dimensions fall into this category.
Accounts, Entity, Scenario, and Flow are required but what goes into them is up to you and your needs. The UD1 through UD8 dimensions are optional within a cube. I’m currently working on a for-real planning application that uses UD1 and UD2 across three separate cubes; I am ignoring (and don’t need) UD3 through UD8 as the UD1 and UD2 types contain unique dimensions (depending on the cube, sometimes they’re shared – OneStream is nothing if not flexible). AVBS will use just UD1.
Note that the Entity dimension differs from the others in that it’s used to tie together multi cube applications and is where consolidations, eliminations, and translations occur. It is also does not dynamically aggregate whereas the other dimensions do. Entity is special, just like you, Gentle Reader.
Every app (and every cube within the app) must have the required, predefined, and unchangeable dimensions Time, View, Origin, and Consolidation.
Uh oh (for we planning and budgeting sorts), consolidation talk: this is the Intercompany dimension used to, unsurprisingly, calculate and store intercompany transactions that are, uh, derived. Per the OneStream XF Design and Reference Guide, “Intercompany Elimination is the process of cancelling out account balances for intercompany partners for intercompany accounts with any unresolved balance being placed in a Plug Account”. That derived nature means that I can’t show you a lovely dimension selector screenshot as way of illustration.
I think I have to stop right there before I set a Twitterverse of angry CPAs/Chartered Accountants/OneStream consolidation consultants descend on me like a pack of hungry wolves. Seriously, the intercompany dimension is where intercompany eliminations are performed. Beyond that, you’re going to have to talk to a consolidations consultant of which there are many and of which I, alas and alack, am not. Whew.
Yes, you can do anything you want with the Account dimension, but no, you can’t not have it. I can’t imagine what you’d do in a financial model without an Account or Measure dimension but it’s a funny old world. Regardless, you must have one and I do.
There are 24(!) Scenario types in XF.
Does that mean your application/cube must have 24 scenarios? Nope. In fact, you can have more, many more, scenarios if you wish because each type can have multiple scenarios. Yup, those are 24 scenario types with unique scenario functionality (I’ll try to cover this in a later post). This application is going to have Actual (to begin with) and eventually Budget and Forecast. For the purposes of sanity, the scenario named, “Actual” will be in the Scenario type “Actual” but there’s no requirement that it be so. The scenario name/scenario type relationship is in the administrator’s/developer’s remit – the users won’t know or care how this is organized.
What they will care about is how Scenario types control behavior such as workflow, calculations, and even dimensionality.
Remember that you can actually (eh, I slay myself) contain Actuals, Forecast, Budget, Projection, etc., etc., etc. in the Actual scenario type. Or you can split them across the 24 Scenario types. It seems logical to group Scenarios that behave largely the same way within the same type. Also, there can be many individual Scenario names – I think fervent prayer is in order if you start getting into the teens or twenties of Scenarios across types but I can see that, maybe, when it comes to historical versions of a scenario there could be more some sort of multiple of 12 (12 Forecasts, 4 Budgets, 1 Actuals, and so on). But think about not overwhelming you and your uses.
AVBS’ is just about as simple as can be.
Entity is special. As noted previously, you are also special. But not in the same way unless you are a mandatory, stored, and aggregating and consolidating dimension with materialized aggregate views. You’re not? Good. That’s what OneStream’s Entity is for.
Think of Entity as the dimension that contains departments, legal entities, geography, etc. as the application’s primary organizational structure.
Entity is also used to assign security as well as workflow.
Here’s my ultra-simple Entity dimension:
The Flow dimension can be used for a lot of things, most of which this application will ignore: it’s going to be used to understand and view (heh, view as a verb, not View as a dimension type) account movements across time. As shown below, AVBS’ Flow dimension is all about balances.
Were this not an incredibly basic sample, the Flow dimension could be used to understand fx in conjunction with the Consolidation dimension although again AVBS is too simple for that as it uses nothing but the current global reserve currency.
As you might imagine, these are pretty straightforward – they are what they are and you can’t change them, only use them. Having said that, as Time, View, Origin, and Consolidation are vital to any kind of analysis, you’d end up spending time creating them anyway.
For the purposes of A Very Basic Sample, think of the months of the year with quarters and half years thrown in for good measure.
My fiscal and calendar year will be the same and while I’ll just keep to a calendar month for the present, in future I might switch over to a 4-4-5, 4-5-4, or 5-4-4 month pattern for time-intelligent formulas
The start date is up to me. As I’m writing this in 2018 (eh, it started in 2017 but that’s so last year), here we go:
While this dimension is fixed, oh my what a lot of choices it supports. Think of the View dimension as a way to categorize data.
It can address time periods, e.g. Periodic, QTD, HTD, YTD, etc. all automagically calculated for you.
View also supports Entity calculation status, annotations, comments, trailing months, averages…it goes on and on and all there for the choosing. AVBS loads data one month at a time (Periodic) but can be switched to period to date views simply by selecting YTD (or whatever) in the dialog box.
Origin is pretty interesting – it’s a way to manage different slices of data.
Although again this is something for a later post, as an exercise, think about how BeforeAdj as a data input member might work. Seriously, think about it – what it does is kind of brilliant. For those of you who have OneStream and haven’t played with it, try sending a value to BeforeAdj and then drill into it on the sheet. As noted, amazing.
AVBS will only use Import (simple loads) and Forms (direct input) with BeforeAdj controlling everything.
We’re at the last dimension. Whew.
As noted, I practice
pretend management accounting so real and true consolidations are somewhat beyond me. Yeah, I get it, conceptually, but as I’m a planning and budgeting guy, I’m going to avoid misdirection and thus crib shamelessly (‘cos surely writing this blog is an exercise in some kind or another of shamelessness so why not a wee bit more?) from the Technical Reference Guide, “The Consolidation tree of Dimension Members is how the data rolls up from the local currency to the final numbers. The tree gives opportunities to go from local currency to translated currency showing any intercompany eliminations and allows for the Entity to make adjustments up the tree before or after the final numbers.”
As there are no true consolidations nor are there any fx calcs in AVBS, each and every bit of data in the Consolidation dimension will reside in the Local member.
And that’s the end for now
16 pages is surely enough but at the same time not nearly as there is so much more to cover. That’s a promise, not a threat, btw.
I’ll leave you with a simple view of AVBS in Excel:
Be seeing you.