Posted on

OneStream XF 5.0 Review: The Excel Add-In

OneStream XF 5.0 is the biggest release of the platform in several years. Here’s a link to our recent press release announcing it publicly. There are a lot of reasons to be excited about OneStream XF 5.0:

  • Faster Processing with Environmental Sensors and Smart Load Balancing
  • Conditional Formatting in Cube Views New look and feel of the OneStream Client, which is even more sleek than the previous versions
  • New features with the Excel Quick Views Add-In

As someone who spends most of his time with users, the Excel Add-In is very important to me. First of all, I think it is a real pleasure to deal again with an Excel Add-In for someone who used to work with Essbase back in the days when Hyperion was relevant.

In this post, we will look at 4 features:

  • Type-in members in Quick Views: type your members, refresh your Quick View et voila!
  • New and Improved Member Dialog Box: select members from anywhere and find members with a dialog box
  • Show User Defined dimension names in Quick Views
  • Reversed hierarchies

Type-In Members in Quick Views

Create a new Quick View by typing in members

This is by far the star of this new Excel Add-In: you can type member names and create a Quick View on the spot, I think it was the only feature missing compared to the old Essbase add-in.

Type your members and select them:

Click on the ‘Create Quick View button:

And that’s it, your Quick View is created!

Extend an existing Quick View by typing in members

Type the members you want to add, in our case, we want to add the UD1 member Ground Coffee

Then, hit the ‘Rebuild Quick View’ button:

The new dimension is automatically added to the Quick View

Type-in to add members to an existing dimension

You can also add members of an existing dimension:

Note that I don’t need to add the dimension name before the member as OneStream already knows it and I don’t need to add the other dimension in the Quick View.

Hit Rebuild:

And you’re done:

This is truly a life changing feature.

Member Extension Type-In

You can also type member extensions!

In this particular case, we have base members of the Time dimension and Accounts with a member extension:

From there, your imagination is the limit.

The Member Dialog Box

This is quite a big update as well: the Excel Add-in has a new and improved Member Selection dialog box. Imagine you have a Quick View and you want additional members, select a cell in the dimension you want to change and click on the ‘Select Member’ button.

This will open the Member Dialog box:

Note that the current dimension and member and automatically selected. From there, you can add whatever members you need:

You can use the Ctrl and Shift keys to expand your selection, after you click ok, your spreadsheet is updated:

Click on Rebuild Quick View and you’re done:

Display User Defined Dimension Alias During Drill & in POV

It is one of the new features with OneStream XF 5.0, you can name user defined dimensions. Go to Application -> Application properties and set the name of the UD Dimension in the Dimensions tab. In our case, the UD1 is the Products dimension.

As soon as you create a new Quick View, OneStream will use the name of the dimension based on the application you selected when you connected.

These are details that helps a lot, particularly when you roll out a solution for hundreds of users!

Tree Descendants Reversed

There is a new feature with member expansion where users can select to see Tree Descendants in the natural order of the hierarchy or in reverse. It is very useful because a lot of reports are one way or the other.

To select a hierarchy in the natural order, use the usual expressions:

It will return members in the orders of the hierarchy you’ve selected:

To select a hierarchy in the reversed order, add a ‘R’ at the end of the expression:

And you will see a reversed hierarchy!!!

This feature will save a lot of work when creating reports and dashboards.

If you want to learn more, check the official OneStream blog articles on OneStream 5.0.

Posted on

Data Entry 123 Part 4 – Seamless integration in the OneStream Workflow

We’re almost there: in the first episode, we installed and configured Data Entry 123 from the XF Marketplace, then we created a selector and 2 forms in the second episode, then we added calculations to the forms. Now, we are going to add the forms in the Workflow. 

First Things First

Create a new Workflow Profile, in our case, we create a simple Forms Workflow Profile:

Assign Forms to Workflows with ‘Forms Usage’

Go back to the Data Entry 123 Form Builder and select ‘Form Usage’ in the upper left corner.

This is where we are making the forms available for a Workflow Profile.

Again, you will have the same logic where you can create a Form Set with Form Set Members.

Now, go to the Workflow Assignment tab to assign Form Sets to Workflow Profiles. All these steps will give you a lot of flexibility.

In our case, we are assigning the Volume Form Set we just created to the Forms.Forms Workflow Profile. Note that you can have different assignments for different scenarios.

As always, make sure you save your work…

Attaching Data Entry 123 Forms to a Workflow Profile

Go back to the Workflow Profile and change the workflow name to ‘Workspace’ or ‘Workspace, Certify’

Then select DE123AsWorkspace_DFM – Data Entry 123 (Workspace)

Save your work and voila, you’re done!

With one Workflow Profile, you can have multiple forms and folders, automatic calculations and a very nice and pleasant interface to use. You can jump from one cube to the other and create a mini application that will look very professional and that will satisfy your most exigent clients!

Posted on

DataEntry 123 Part 3 – Compute more with Calculations

On the first episode, we installed and configured Data Entry 123, on the second opus, we created a selector and two entry forms. That’s a very good start, it is time to look at calculations, we are going to copy data from one cube to the other when a form is opened or data is saved.

Adding Calculations to a Data Entry 123 solution

We wrote a simple calculation that takes the numbers from the Fiscal Year Cube and shifts periods to match the Calendar Year periods. There are plenty of Time functions within OneStream,that can be a very good subject for a later blog post and we used a Data Buffer to copy data from one slice of the cube to the other, that can be another very good blog post.

Let’s get started and open the Calculation Builder from Data Entry 123:

Again, the same logic is applied with Calculation Group and Calculations: Groups, lists, all modular…

In our case, all calculations are executed from the FY forms: we need to execute the rule FY2CY when we save data and execute CY2FY when we open the form. For this, we check the appropriate boxes: FY2CY with ‘Run On Save’ and CY2FY with  ‘Run On Open’:

Good to know: when you check ‘Show in List’ box, the calculation will show in the form. You can also personalize the order to which the calculations will show in the drop down.

Tweaking Calculations POVs

What I like the most with executing calculations is that you can tweak the POV for your rule specifically:

The form pulls data from the ‘Sample FY’ cube, the cube POV can be whatever you want, the forms will take this value because it is hardcoded in the Cube View. Now, what’s great is that with Data Entry 123, I can run the calculation from the ‘Sample FY’ cube for the rule ‘CY2FY’ and from the ‘Sample’ cube for the ‘CY2FY’ rule. The OneStream calculation engine will take the POV setup in the Calculation builder and it gives a lot of freedom to developers.

Same, you have flexibility for the Time dimension:

Note that the rule will use the POV value of the Time and add some tweaks to it for all Calc Time Types except the last one ‘Member Filter’ where you can use a value from a selector. I try to avoid ‘Member filter’ and prefer to use what is on your POV because I prefer to manage Time exclusively from the POV for more consistency but I am sure there are use cases where ‘Member Filter’ comes very handy.

Attaching Calculations to a Form

Now, all we have to do is to attach the rule to the form:

And verify results:

And we are almost done! We created a form with calculations and all data is in sync now! Last, we need to integrate our beautiful Data Entry 123 forms in the OneStream Workflow to create a killer application.

Posted on

DataEntry 123 Part 2 – Build more with less with Selectors

In the previous episode, we installed and configured Data Entry 123. Now it is time to put the solution to work! In this post, I will show you how to create selectors. You will see that selectors are extremely configurable: your imagination is the limit. 

Let’s imagine our favorite coffee company, the AVBS Co has been acquired by a Japanese conglomerate like Mitsui (a company that started with miso soup by the way). We need to input and report numbers in both Calendar (January to December) and Fiscal Year (April to March).

Creating a Cube View

Let’s say we have a Cube View where we want to enter coffee volumes in tons for each product and for each Region. We need to create a selector for the Entity member (the region) and then we need to create two forms: one for a Calendar view and one for a Fiscal view.

Creating a Selector

Let’s go to Data Entry 123 to create a fancy selector:

Go to Selector List and open click on the + to create a new list:

We need to create a list of Entities, for this, you need to add a ‘List Name’, a ‘Label’, the ‘Member Filter’, which members do you want to show? And you need to select the dimension for your members and filter.

Don’t forget to hit the floppy disk button to save your work.

You can see your list with the Preview button:

Now, we need to include this list in a Selector Bar. Everything is modular in OneStream so the same list can be used in different bars that can be used in different forms. It’s up to you.

Create a Selector Bar, save it then add the Selector List to the Selector Bar. Each list refers to a Parameter. You will call this parameter from the CubeView, the parameter is E_DFM in our case. Don’t forget to save your work.

Calling a Selector from a Cube View

Go back to your Cube View and open the Object Lookup (the pair of glasses)

From there, find the object E_DFM, you can type part of the name if you don’t want to scroll down and look for it:

Copy to Clipboard and paste it for the Entity field:

Save your work and now let’s look at the results. For this, we need to link the form and the selector together with the Form Builder.

Creating your first Data Entry 123 Form

Go back to Dashboards -> Data Entry 123 and select Forms

The logic is the same as creating a Selector: you create a Group first then the item, so you can have the same form with different selectors giving different results.

If you cannot find your Cube View, make sure you are pulling from the correct Cube View Group in the Settings page

You can Preview the result clicking on the Preview button: the highlighted button

You will get a nice preview:

It’s not looking too bad, note that the rows in Green are totals and cannot be a direct input (you need to spread values to enter data at this level)

Duplicate Entry forms at a crazy fast pace

Now, let’s create a form for the Fiscal year in no time:

We need to duplicate the Cube View then use the form Designer to create the form in Data entry 123

Select the new cube:

Give a name your new CubeView. The only change you need to make is to change the Cube  property for your Cube View and use the Sample FY Cube

Create the form

Preview:

Note that the first period is April. Great, we now have 2 forms in Data Entry 123: one for Fiscal Year and another one for Calendar Year. In the next chapter, Compute more with Calculations, we are going to attach calculations to the forms to move data from one cube to the other.

Posted on

DataEntry 123 Part 1 – Getting Started

Context

I was on a project where users needed a highly customized interface to input data. Moreover, as it happens often with Asian companies, their fiscal calendar was from April to March. The same data entry had to be made on both a Fiscal Year basis and on a Calendar Year basis. Our application had 2 cubes, one for Calendar Year and one for Fiscal Year and both cubes need to always be in sync. I am not going into the details of why we decided to design our application with 2 cubes, it can be the subject of another blog post, but it was the best decision and the replication of the design in place with the legacy system (Oracle Essbase).

We also wanted to make the use of the application simple for users, we did not want them to have to guess if they had to run a sync or not and we did not even want them to be aware that there was one or multiple cubes, we wanted users to be using one application, the number of cubes don’t really matter here.

And of course, we had a relatively short timeline.

We decided to extend the XF Platform with a MarketPlace solution. What is an OneStream Marketplace solution you might ask? In a few words, the Marketplace is the equivalent of the App store for your smartphone, it is an easy way to extend the functionalities of your existing platform with solutions for Task Manager, People Planning, Thing Planning, etc… You can find more here . To me, having the OneStream Platform that can be extended indefinitely is a key element why companies should use OneStream and make the most out of their investment. Once you have the base in place, extending your Platform is a simple thing.

For our specific needs, we chose to use the Data Entry 123 solution in the XF Marketplace:

  • Forms are organized in folders and users can pick and choose from there.
  • Need to run calculations on Open and on Save without users knowing it (calculations must be lightning fast as well)

In this blog post, I will show you how to install Data Entry 123 (you will be surprised how easy it is), how to use Selectors and why you’ll love it, how to attach calculations to a form (and how you can do things you’ve never thought it could be possible), and how to integrate Data Entry 123 Forms in the Workflow.

I will use the AVBS application for this, yes the application Cameron created for the ACE Company.

Installing Data Entry 123

XF Marketplace

Go to the XF Marketplace then Store 

find the ‘Data Entry 123’ solution: 

And download it:

Loading the Data Entry 123 solution in your Application

Open your OneStream Application and go to Application -> Load / Extract

Select the zip you just downloaded and load it into your application.

Let the load finish (it just takes a few seconds).

Setting up Data Entry 123

The XML components are added to the Application, now we need to setup the solution, go to OnePlace -> Dashboards, open the new Dashboard Group: Data Entry 123 (DFM):

Click on ‘Create Tables’:

And now, let’s launch the Solution:
The last thing we need to do to setup the solution is to look at the settings, find the settings icon to the upper right corner:

 

here, you can select the Security Role and most importantly, the Cube View Group you want to use with Data Entry 123. If you don’t have a Cube View Group yet or need to change it later, it’s not a problem, you can create the Cube View Group and come back to this screen and update the Cube View Group.

Last and not the least, don’t forget to Save Settings!!

You’ve added the solution to your application! Now let’s configure it, that is what we will discover in the next chapter of this blog series: DataEntry 123 Part 2: Build more with less with Selectors.