In Part 1 I discussed the topic of Month and Year being a joint dimension in OneStream which is a change from some historical systems. We went through how to break up Month and Year in a way that lets us split them into rows and columns using a series of Cube View Overrides. As with most things in OneStream there is never just one way to do anything. Since writing that post I learned a new approach, and if I’m being honest, I like this way better so had to share with all of you. The Override method limited you to four years of history. Getting creative with some dynamic member formulas you can go back an unlimited number of years, even to 1989 if your data allows.
A Brief Anecdote
We know the age-old story all too well: Month and Year broke up and were never, getting back together… like ever. But then Month and Year were spotted together again. Month whispered to Year, “You belong with me.” Year reciprocated, professing that Month was “the one,” and inevitably, Time reconciled. This is what brings us to another method of breaking up Month and Year.
Now for the Important Stuff
To do this you will need to do some Metadata setup. If you are using your UD8 Dimension to house reporting variances, as is common practice, this will be a good dimension to add these in. You will need to set up 13 new dimensions, one for each month and a parent to roll them all up. Call it what you want but I named mine “Reporting Months”. I always recommend using an underscore instead of a blank space so you can reference it in your Cube View without brackets.
The next thing you will need to do is set all your base members to a Formula Type of DynamicCalc.
Before you get worried, I promise you this isn’t a labyrinth of code, all you need is two lines!
You will dynamically retrieve the year from the Point of View in your Cube View and then append the month to the end of the year. The only thing you will need to change between each member formula is the month referenced. Notice the $ after GetDataCell, this allows you to reference your declared variables using an interpolated string, simply reference year surrounded by curly brackets as shown below to call the year.
Now it’s time to build a relatively simple Cube View. In your rows you will put the time as the year (you can make this dynamic off your POV, but to keep things simple I hardcoded the year) and in the rows you will call your new parent with the base expansion.
The Finished Product
There you have it, a Cube View nicely displaying the year and time in an alternative style.
Another way I have seen this used was in a UD dimension that had car model and trim detail. It allowed the end users to split the trim of cars into the rows and models into the columns. Since this is using dynamic calculations which run when called and do not store data in the Cube, we do not have to worry about storing duplicate data.
