Stop confusion with empty parameters in Dashboards
Tired of opening your dashboard to find no pre-populated values? Frustrated when your combo boxes and list boxes don’t display a value upon runtime? This brief blog post will assist admins/super users in populating those values to avoid confusion for end users when they open a dashboard devoid of any values.
Spreadsheet or Excel Add-in?
Spreadsheet or Excel Add-in? As we know, the Spreadsheet and Excel Add-in capabilities extend OneStream's functionality, providing financial analysts and FP&A professionals with a familiar interface to access, manipulate, and submit company data. The use of Spreadsheet and the Excel Add-in is manly focused around two areas: Data Retrieval and Analysis: They allow users to connect directly to the OneStream database to retrieve, analyze, and refresh live financial data. Users can leverage Excel's comprehensive formula and charting capabilities while ensuring that the underlying data is accurate and sourced directly from the single version of the truth within OneStream. This capability is vital for ad-hoc analysis, detailed drill-downs, and creating custom management reports that may be too complex or specialized for the standard built-in reports. Data Submission and Workflow: In some contexts, they are used to facilitate the submission of budget, forecast, or journal data back into OneStream. Templates can be designed in the tool, which then uses the specific functions to upload data, maintaining the integrity of the data submission process by adhering to security and validation rules. Now, what are the differences between these two options? Well, the differences between the Excel Add-in and the Spreadsheet feature lie primarily in their environment, functionality scope, and dependence on Microsoft Excel. Here is a breakdown of the key features and distinctions: So, when you should use one option over the other? Well, you should use the Spreadsheet feature over the Excel Add-in when you need an easy access to the tool and, most important, governance and control: And when you should use the Excel Add-in over the Spreadsheet feature? As we know, the Spreadsheet feature is excellent but cannot replace the power of the dedicated Add-in for advanced tasks. You must use the Excel Add-in when you need: VBA Macros/Automation: Any time you need to use Visual Basic for Applications (VBA) to automate data retrieval, processing, or submission actions; External Data Integration: When you need to combine live OneStream data with data from external sources or other Excel files within the same workbook; Advanced Excel Functions: When you need to leverage highly technical Excel features like Goal Seek, Solver, or specific advanced charting types that are not fully supported by the internal Spreadsheet feature; Complex Report Formatting: For highly customized, production-quality financial reports that require the full fidelity of desktop Excel's formatting, printing, and charting capabilities. In summary: if a user needs the full, uncompromised power of Excel (VBA, Solver, etc.), they must use the Excel Add-in. If a user only needs basic Excel functionality, ad-hoc reporting, and data submission inside the OneStream platform for a seamless experience, the Spreadsheet feature is all they need to use.Locking (and Unlocking) OneStream Security
How often are you, as a consultant or OneStream administrator, scrambling before UAT or go-live to get all your business rules working, all your workflows setup and tying out data? And you completely forget about security (because you as an Administrator can see and do everything), until the final hour? Sound familiar? Do not let security be an afterthought to your OneStream implementation, project, or on-going maintenance. Security leaves a first impression and has a direct and tangible impact on your end users. So, getting security right from the get-go (UAT or go-live) can go a long way to user acceptance! Let’s talk about some common security strategies and things you need to consider before you get to the week prior to UAT! These things will help you think about security along the development cycle or even as you add in new elements to an already live application, so that you can layer security in as you go, avoiding that last minute scramble. User Roles During the design phase, get a list of everyone who will be end users of OneStream, whether you have 10 or 1,000+ users! Put those users into broad categories or types of roles those users will have in OS. These are some examples (Chapter 4 of the Security Essentials book 😊): VIEWERS: People who can view all data and reports with no restrictions DATA LOADERS: People who will be involved with bringing data into OS whether via Imports, Journals or Manual forms entry APPROVERS: People with oversight of a process with the ability to certify, lock or determine when the data is final FORECASTERS / PLANNERS: People who will be involved in inputting data and running calculations related to a forecast or budget process POWER USERS / LOCAL ADMINS: People who will have broader access than the average VIEWERS with the ability to perhaps modify reports, metadata, etc within OS Role Access For each role or type of user you will have, outline what each role should be allowed (or not) to do. For example: VIEWERS Ability to see all cube data meaning all entities, all scenarios, all cubes, etc Access reports for such data from: OnePlace Spreadsheet Dashboards Be allowed to view the dimension hierarchy page to understand how things roll up Be allowed to view FX rates Once you have determined a framework for what a given role or set of users is allowed to do, you are read to put the building blocks together to achieve that. Pre Security Build Before I start to set up security and/or ahead of a UAT, I like to start by making all application objects (app roles & pages, cube views, metadata, dashboards, BRs, etc) set from “Everyone” to “Administrators.” Why do I do this? I like to keep things inside OneStream under lock and key and only grant access on an as-needed basis. In this way, as you start to put together your end user security groups users will only be allowed to see and do things to which you have granted them access. Not only does it help you control data access, but it also helps simplify the end user experience. You will only open up access as needed so as not to overwhelm your users. If you leave all doors and windows opened and unlocked (as with a house), well you can imagine what a security nightmare that could be! This approach works well for complex organizations or companies who have strict governance around data. If your company does not have strict security needs or your user base is small, then this approach of locking everything down to start, may not be the best approach for your security model. If you are early in build and/or because you have not done anything with security to-date, things will generally be wide open (“Everyone”). This is because as you add new elements to a OneStream application (cube views, entities, scenarios, etc.) the default security group, if you do not change it, is “Everyone.” So before heading into a UAT I like to start by locking everything down (e.g. “Administrators”) and then proceed with putting groups in place to open things back up. What is the easiest and quickest way to lock everything down? I like to do a full application extract to XML. Then I methodically open each XML file: And make the following edit and replaces: CURRENT VALUE REPLACE WITH VALUE accessGroup="Everyone" accessGroup="Administrators" maintenanceGroup="Everyone" maintenanceGroup="Administrators" "WorkflowExecutionGroup" attributeValue="Everyone" "WorkflowExecutionGroup" attributeValue="Administrators" "CertificationSignOffGroup" attributeValue="Everyone" "CertificationSignOffGroup" attributeValue="Administrators" "JournalProcessGroup" attributeValue="Everyone" "JournalProcessGroup" attributeValue="Administrators" "JournalApprovalGroup" attributeValue="Everyone" "JournalApprovalGroup" attributeValue="Administrators" "JournalPostGroup" attributeValue="Everyone" "JournalPostGroup" attributeValue="Administrators" readDataGroup="Everyone" readDataGroup="Administrators" readWriteDataGroup="Everyone" readWriteDataGroup="Administrators" "CalculateFromGridsGroup" value="Everyone" "CalculateFromGridsGroup" value="Administrators" "ManageDataGroup" value="Everyone" "ManageDataGroup" value="Administrators" readDataGroup2="Everyone" readDataGroup2="Administrators" readWriteDataGroup2="Everyone" readWriteDataGroup2="Administrators" Then load each XML file back into your OneStream app. Alternatively, you can consider using the Solution Exchange tool called XML Security Remover: Which will allow you to take an app extract zip file, run it through this Solution Exchange tool and set all security to “Administrators” via this tool. Both should accomplish the same goal of changing all security groups to “Administrators.” Then, if you poke around within the application, you should generally see “Administrators” applied to everything. To test this you can set up a native user ID and log in as that user. If everything has been locked down, that user should not see any applications. Their drop-down box at the log in screen will be empty. Goal achieved – every window and door in your OneStream castle is locked! Now what? Now you are ready to start putting user groups together and layering on the security that will allow your users to unlock and walk through certain doors (e.g. pages, objects or data in OneStream). Building Security Groups and Roles Create a user group 000_GRP_VIEWALL (see Chapter 3 of the Security Essentials book for security group naming conventions). Figure out the pages or application roles to which they will need access and create those groups (or decide to leave some open to “Everyone”). 3. Now that you have a user group (000_GRP_VIEWALL) and have allowed that group to get to certain pages within an application (001_APP_OPENPROD, 001_APP_VIEWALLDATA, 001_APP_DIMPAGE, 001_APP_FXPAGE, and 001_APP_SPREADSHEETPAGE), you are ready to move on to determine what, if any, other object or data security this user group needs. 4. Being that this is the View All user group and because you have granted 001_APP_VIEWALLDATA, this user group should not need any additional data access for 002 Cubes, 003 Scenarios, and 004 Entities. The View All app security role supersedes those things. However, you will likely still want to grant this view all user group access to a WF profile such as an actual WF group: 005_WF_ACTUAL. Testing The final step is to nest groups and test that user’s security role access. The easiest way to achieve this is to set up a native User ID, e.g. “Test1”, and log in as that user. For obvious reasons, you do not want to log in with your “Administrators” access because you can see and do everything. You want to mimic an end user by either logging in as a native user set up with a particular user group, or have yourself temporarily removed from “Administrators” and placed into whichever group you wish to test. Here are some basic steps to testing: Nest or combine the various 001 – 005 security groups into your 000 user group as shown below: Log in as Test1 user (or your own user ID if necessary) and test the end user security. Confirm that you can get to the screens for which this user group (000_GRP_VIEWALL) should have access as well as NOT get to pages and places for which you do not want them to. Part of your testing should be to focus on the end user experience and navigation options available to the end user. You want to confirm that the user can get to what is needed, but also not get to that which they do not need access or which could serve to confuse them. Conclusion You will want to repeat similar steps as you put together your additional user groups: 000_GRP_DATALOAD_ACTUALS 000_GRP_APPROVE_ACTUALS 000_GRP_FORECAST_WRITE 000_GRP_POWERUSER 000_GRP_LOCALADMIN_PROD The process should be to establish the user groups (000) listed above, determine what application roles (001), cubes (002), scenarios (003), entities (004), workflows (005) and other data objects to which they need access. Create these security groups and attach them to various application objects, embed them in your 000 user groups and then test. The same holds true if you have already established security and are making changes to your security model. You will want to log in as the user before you make changes, make the group changes, then log back in after and ensure you see the intended consequences of your changes. The same approach can be used whether you are establishing security for the first time, or making changes years down the road. They key with implementing or changing security is to test and retest. Only you, as the consultant or company OneStream administrator, knows to what each group should or should not have access. So who better to test than you?! Of course, your end users will ultimately test their security as a part of a UAT or parallels, but the hope is that you have worked out all, if not most, of the kinks before the end user logs in. This can go a long way to building end user confidence, acceptance, lessening frustration, and speeding adoption of OneStream. You could build the nicest house (OneStream application) on the block, but if you do not provide the right people the right keys to the front door, it will be for not! And, it will likely save you headaches in the long run. Security should not be an after thought. It should be considered as a part of the overall OneStream design, put in place during the build cycle and tested ahead of a UAT, go-live or before you turn things over to your end users. For more information on all these topics, please see the OneStream Security Essentials publication now available.
Maximizing Cube Utilization in OneStream Applications
Cubes control how data is stored, calculated, translated and consolidated based on the Dimensions assigned to it. Cubes are flexible and can be designed for a specific purpose (HR or People Planning Cube, Budget/Sales/Cost Drivers Cube or even Tax Cube) or data type in a OneStream XF Application. Dimensional assignments can vary by Scenario Type. An application can have multiple Cubes that can share Dimensions and data Separate Cubes may be used to hold data outside of the main financial Cube.Menu Component in Practice
If you create the new Menu Component in your Maintenance Unit, you will quickly notice that it has very few properties. That's because its configuration will actually come from an attached Data Adapter, which must produce a set of tables containing all menu items and their configuration. The format of such tables has to be somewhat precise, matching what the component expects. For this reason, the best way to produce them (at least while you familiarize yourself with this mechanism) is to create a Dashboard DataSet using a couple of utility classes built for this specific task. The first thing we will do, in our rule, is to create an XFMenuItemCollection object. This represents our menu, which we will populate with items and eventually return (as a DataSet) at the end of the function. Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object Try Select Case args.FunctionType Case Is = DashboardDataSetFunctionType.GetDataSet If args.DataSetName.XFEqualsIgnoreCase("MainMenu") Then ' create the menu object Dim menu As New XFMenuItemCollection() Menu items will be created by instantiating objects of type XFMenuItem. These objects will hold all the configuration properties for the item, including the font and colors that it will use. There are a few different constructors you can use, to specify all sorts of properties; the one used here is the shortest one! ' create top-level item ' XFMenuItem(string uniqueName, string headerText, string foreground, string background, bool isBold, bool isItalic, bool isEnabled, bool isSeparator, string parameterValue) Dim parentMenuItemOne As New XFMenuItem("1", "Parent", _ "White", "SlateGray", False, False, True, False, Nothing) ' create items for the second level Dim childMenuItemOne As New XFMenuItem("1.1", "Child 1", _ "Black", "White", True, True, True, False, Nothing) Dim childMenuItemTwo As New XFMenuItem("1.2", "Child 2", _ "Black", "White", True, True, True, False, Nothing) ' create item for the third level Dim grandChildMenuItemOne As New XFMenuItem("1.1.1", "Grandchild 1", _ "White", "SlateGray", True, True, True, False, Nothing) Most of the properties are self-explanatory, but you might wonder what "parameterValue" is. That's the value that will be pushed into the Bound Parameter specified on the actual component later on, so that Actions or other components can pick it up. We will come back to this later. Now we need to define the relationships between items. We do that by manipulating the .Children property of each parent item, which must be a list containing the children (duh!) items. ' create the hierarchy by adding children to direct parents as a List ' attach 1.1.1 as a child of 1.1 childMenuItemOne.Children = New List(Of XFMenuItem) From {grandChildMenuItemOne} ' attach 1.1 as a child of 1 parentMenuItemOne.Children = New List(Of XFMenuItem) From {childMenuItemOne} ' you can also manipulate the list once created. ' attach 1.2 as a child of 1 parentMenuItemOne.Children.Add(childMenuItemTwo) In a similar way, top-level items are added to the list contained in the .MenuItems property of our XFMenuItemCollection instance. ' add item 1 as a top-level members of the menu menu.MenuItems.Add(parentMenuItemOne) Before we return the resulting menu, if you are dealing with dynamically-generated structures with a lot of members, you might want to perform a safety check and purge extra members: While menu.IsAboveMaxMenuItemLimit(si) menu.MenuItems.RemoveAt(menu.MenuItems.Count - 1) End While Last, we use the .CreateDataSet method of our menu object to return the DataSet. ' generate the dataset and return it Return menu.CreateDataSet(si) Now that we have the rule, we can create a DataAdapter to execute it. Notice how, when testing it, it produces two tables: one with item properties, and one with their relationships. Now that we have an Adapter, we can create the Menu Component and attach the Adapter to it. You can then assign it to a Dashboard and preview it, after you save it. This is fun but a bit pointless! We want menus so that the user will actually choose something and we'll get the result of that choice. In order to do that, we need to specify the Bound Parameter. Whenever the user selects an item, the "parameterValue" associated with that item will be pushed into the specified Parameter; we can then reference that Parameter in an Action or extender rule, to trigger something like navigating to a website. Note that the Parameter doesn't need to exist! OneStream will just create one for you in the background. Then we place an Action on our Component, referring to the Parameter. The last step is to go back to our rule and specify a different parameterValue for "leaf" items, so that the Parameter will contain something. Dim childMenuItemTwo As New XFMenuItem("1.2", "Main Page", _ "Black", "White", True, True, True, False, "https://www.onestream.com") ' create item for the third level Dim grandChildMenuItemOne As New XFMenuItem("1.1.1", "OneCommunity", _ "White", "SlateGray", True, True, True, False, "https://community.onestreamsoftware.com") Et voilà! You can now execute the Dashboard and verify that it works!
