A geek’s reach should exceed his grasp, Or what’s a blog post for?

With extensive apologies to Rabbie Burns aka the Bard of Ayrshire aka the Ploughman Poet (I hope that every lit prof I had in school is now beyond happy that I can remember a line from Andrea del Sarto – see, I did pay attention, sometimes) I had high hopes that I could cover the entire OneStream dimension build process from thinking about it to building and editing it manually to loading and editing it from external sources. Whew. And that’s not even counting applying said dimension to a cube. Double whew.

It’s simply too much to cover in a single blog post so I’ll disappoint you bitterly relieve you and me considerably by reducing scope as splitting this into multiple posts is the only way to reasonably approach the subject.

Along the way I’ll try to explain as much of the architecture and philosophy behind OSXF’s (I’m too lazy to write out OneStream XF more than once per post) metadata management but there will be much that I shan’t and can’t cover yet. There’s a lot going on and a lot of functionality that isn’t germane to building a sample dimension, at least right now. I’ll do my best and I encourage you to ask what the different dimension and member properties are. With luck, I’ll even be able to answer your questions.

So what will I cover when covering the very basic bits in this post?

1. Dimensions live within applications, so I’ll build that first.
2. A super brief and frankly incomplete description of OSXF’s metadata manager. Yes, this is a teaser for a later post.
3. Manually building a dimension.

In subsequent posts I’ll cover:

1. Loading dimensions from external sources
2. Extracting a dimension.
3. Batch deletion of a dimension’s members.

At the end of it all we (you and me) should be masters of basic dimension builds.

Metadata within applications

If you’re reading this blog, you’re familiar with the concept of dimensions. If not, I encourage you to read this.

Within OSFX, dimensions exist within an overall application. However, this doesn’t mean that a dimension or a portion of a dimension is limited to a given cube. Unlike some other tools, an application can support so many analytical views that the need for multiple applications is largely obviated. I think you’ll see this as we go through this n00b’s journey together. For the time being, take it as read.

In any case:

• An application is a framework for metadata, data, and calculations that can support one or many cubes.
• A cube is a multidimensional database within the application.
• Dimensions are defined at the application level and then applied to cubes.

A use case: in an application, but not in a cube, and without data

I am coming at this from the perspective of a dimension (and a single one at that) within an application. Later posts will cover different dimension types, properties, referential data integrity, etc., etc., etc.

Creating an application

This is pretty straightforward: connect, password, and open the System Administration.

You care about the System Administration (SA) app because that’s where you do…administration. Once in, click on Applications.

Click on the Create Application Database icon.

Enter the name, description, server connection (in the case of my laptop, there’s no real choice), and a database schema name. Re the last bit, there’s a default “OneStream_ApplicationNameWithoutSpaces” name that resolves to a SQL Server database.

I click on “OK”.

And…KABOOM!

Huh? Whoops, I created an application with the very same name a couple of days ago. Duh. I’ll go off and delete the SQL Server database:

Enter the application name again and ta da:

Total time, excluding me forgetting to get rid of the old database, about 45 seconds. You may notice that I have a habit of forgetting to do things with SQL Server databases. Sigh.

Tabula rasa

Here’s our brand new app. Like a new day, full of promise. Let’s build that first dimension by clicking on – natch – Dimensions.

Here they are. The they are more dimension types than literal dimensions within a cube. Again, I’ll be diving into their meaning, purpose, and usage in future posts. Remember that this is n00b post no. 2, part 1. For the purposes of this post, the Entity dimension will be the focus and it’s going to be kept simple.

The dimension is empty.

The property list is large. Interesting, no? Powerful, yes? But Yr. Obt. Svt. is here to talk about dimensions, not members, not yet, not in this post.

For now, click on the Create Dimension icon:

I choose to name this Entity dimension “Geography”. Entity is a dimension type not a dimension name and yes, yet again, I’ll Cover That Later. As for the dimension name, I could have called it “Coffee” or “Happy” or “whatever”. There is no link between the dimension type (Entity) and the dimension name (Geography).

Great, so now I have a dimension named Geography within the dimension type Entity. Now what?

Adding, cloning, deleting, moving, ordering, and sharing members within dimensions

Add it up

I’m going to create a total geography member eponymously named Total Geography. If I wanted to, that member could have an alias (or description). There’s no requirement to do this but know that if a member name contains a space and is referred to in code, it must be delimited by [ and ]. As good practice and I are but vaguely acquainted, I’m not going to worry about that and will go forward with “Total Geography”. Actually, no, don’t do that, ignore what I do below – put spaces in descriptions and concatenate words in member names. You’ll thank me later.

Click on Create Member for that initial parent:

Enter in a name and optionally a description, not that I did the latter:

Ta da, we have our first child, Total Geography.

Clones R’ Us

Adding a new member is a matter of clicking on the Create Member toolbar icon or right-clicking on an existing member and “cloning” i.e. copying the properties of a member to a new one. In the case of this post there aren’t any real advantages to cloning because I’m not modifying the member defaults.

When I clone “East”, the Member Properties panel is available for input. I’m super-surprisingly creating a member called “West”.

And off I go, creating an unbelievably shortened view of the U.S. of A.

Delete ‘em

What if I entered a member by mistake? No, New Jerseyians, I don’t hate your state as after all you have a really cool song, great diners, someone actually pumps your gas for you, and there are trails like this. Regardless, Garden State, you’ve got to go.

All gone. Don’t worry, NJ, I’ll add you back later.

Speaking of later, when I cover deletions there’s an important consideration when deleting members above leaf. Can you think of what that might be and how OSXF might differ from other tools in that regard? Send your questions care of this post’s reader comments section.

Move it on over

Across hierarchies

Yes, New York is in the East, but it’s arguably in the North. To move it, right click on the member name and select “Copy Selected Members”.

Then right-click on North, select “Paste Relationships (Move)”, and then “As First Child”. Although there are options for First and Last there’s no functional difference in the case of a single parent. If I selected “As Previous Sibling” New York would be the next sibling of West; if I selected “As Next Sibling” it would be the next sibling of North.

And New York is moved:

A note: the member for New York is displayed as NewYork – New York. NewYork is the member name, New York is the displayed description. Use this naming approach instead of what I did with Total Geography.

Reordering

Changing the order of members within a subhierarchy is as simple as copying the member, selecting the target member, and then choosing “As Previous Sibling” or “As Next Sibling”.

 

Moving Pennsylvania is easy peasy.

It’s nice to share

I’ve added a member called Large States as an alternate reporting hierarchy. By alternate I mean that the base member New York should reside under both North and Large States but only be stored once.

By copying NewYork and then pasting it as the first child of Large States, it is created as a shared member across the two hierarchies:

With that, I’ve shared New York to Large States.

A shared member in OSXF really is shared. By that I mean if the Geography member NewYork has 3¢ or $3,000 or $3,000,000,000 in Sales for November 2017 for Product X in the Actual Scenario (I am describing an unbelievably simple cube but just pretend that Geography, Account, Time, Product, and Scenario are the only dimensions – yeah, not realistic) then that data value of 3¢ or $3,000 or $3,000,000,000 or whatever is stored just once and rolls up underneath North as well as Large States. If you wanted to have another reporting hierarchy called “States That Begin With The Letter N”, the data associated with the member NewYork would still be stored just once but aggregates into three different parents. If OSXF didn’t share that data value then the data would be doubled or even tripled. Who wants to do that, especially when a real cube is in play? Not me and probably not you either unless you enjoy a larger and potentially slower system.

With a wee bit of more work, et voilà! Here’s my finished dimension:

The end for now

But there has to be an easier way than all of this typing. As noted, this approach is acceptable for small dimensions that rarely change or ones that don’t have good metadata sources (hopefully also short as typing all of this could ruin your day), but what about larger ones? What is the format for loading data? How are they managed? What’s a lazy geek to do?

For that, you’ll have to wait till the next few (I think I can do this in three) posts on basic dimension management.

Be seeing you.