In this blog, we are going to discuss weekly reporting in OneStream and answer some frequently asked questions when considering how to handle weekly data in the system. Within the OneStream delivery teams, we regularly get questions about reporting weekly data and, hopefully, this blog will help when designing an application that will contain weekly data.
It’s all about design….
At OneStream, we are adamant that the design phase should include a discussion about the time dimension. Not only should those discussions consider the phase or process(es) in scope but also future phases. This is important because the time dimension needs to be configured prior to creating the application. Let’s not lie, it can be difficult to raise this topic at design time since the initial phases are often processes that require a monthly calendar (e.g. Actuals or FPA) and not everyone wants to think about the future but a conversation now can save a headache later.
When looking at weekly reporting, there are a couple of options that are usually considered:
1. Create a weekly calendar in the time dimension
The default time dimension contains months, as the lowest level, which roll up to quarters and then half years and finally, the year itself. The time dimension can be configured to include weeks which roll up to months and so on. Setting up a weekly calendar in the time dimension at the start of the project allows you to create a highly customised time dimension including varying the number of weeks by month and by year (e.g. the requirement may be that each Monday represents a new week, so Dec 2023 would require four weeks, but Dec 2024 would require five). You can assign up to 6 weeks for each month.
This approach may be preferred if there is a requirement to execute the workflow on a weekly basis. It can also be useful if there are large volumes of data being loaded since each time dimension member represents its own data unit. The “downside” of this approach is that it requires some thought during design to anticipate needing the weeks in the future and also defining the calendar itself up front, i.e. how many weeks are required in each month.
2. Use a UD dimension for the weeks
Another frequently considered option is to use a second dimension alongside a monthly or yearly time dimension to differentiate between the weeks in a particular month or year respectively.
This approach can be useful if the requirement is purely to be able to report a small number of weekly balances. This approach will require some work by the implementer to ensure that data is loaded to the correct week member in the UD dimension and ensure report headings are generated correctly, probably involving XFBR strings.
Which option should I use?
Here is the difficulty that we all face, there isn’t one approach which will always supersede another, but here are some of the questions you should be asking yourself:
Is there a need for a data to be loaded, processed and locked weekly?
As mentioned above, using a weekly time dimension also allows for a weekly workflow to be processed and locked. If you decide to use the UD approach, then the implementer needs to handle this, since the workflow will only be able to facilitate locking the month.
What data volumes are being considered, how many entities / Accounts are being loaded?
If the amount of data being collected is for a handful of accounts and / or entities and isn’t significant volumes of data then the UD approach may be suitable. If larger volumes of weekly data are being loaded, it may make sense to consider breaking this data out into their own data units which in turn would indicate the use of a weekly time dimension.
Is the requirement for weekly point in time balances or do you need different views (periodic / YTD) of the data?
If there is a need to see the weekly data in Periodic, YTD and QTD views or rolling totals/averages, this would influence you to consider using a weekly Time Dimension since the availability of these views is standard functionality using the view dimension. If you decide to use the UD approach, you would then need to create your own calculations to replicate any of those default view members (Periodic, YTD, rolling averages)
I want to report daily balances, can I have days in the time dimension?
You can’t go any more detailed than weeks in the time dimension. There is no option to configure it to include days. That would need an approach using a UD dimension for the days and so you would probably put your weeks in the time dimension. In this situation, consider whether that data needs to be stored in the cube. Daily data typically tends to be throwaway data and is often replaced the following day by a new batch of data for analytic purposes.
Should I create a weekly time dimension in every application?
Probably not, a weekly calendar has some end user trade offs, e.g. end users potentially seeing weeks across different screens in the application, irrespective of whether they are being used for a particular process or scenario. There will also be some additional education required for any users writing reports or producing ad-hoc reporting to understand the time dimension.
If I create a weekly time dimension, does that impact my other processes, do all my scenarios need to use weeks?
No, when setting up a scenario you define the workflow tracking frequency and the data input frequency. OneStream is intelligent enough to understand what are valid input cells for each of these scenarios and also the correct workflow granularity:
So you can have an actuals scenario being processed monthly in the same application, and cube, as a weekly or yearly scenario.
I don’t like to see 2024W1, 2024W2 etc when I’m looking at my workflow or in my reports...
I have heard people say that the UD approach is easier to control the descriptions that are shown in cubeviews and reports. Often when using the UD approach, an implementer will use XFBR strings to show the correct date or week number in a report. It can be fun writing those rules but I think people often forget that you are not limited to the default descriptions of the weeks (W1, W2, W3) when taking the weekly calendar approach.
The descriptions of the time members are configurable within the time profile settings. This means you can decide what description is given to the time member (typically shows up in reports) and what description is shown in the workflow page. It can remove any need to use XFBR strings for these members.
But, we're way past design...
Reading this far, you will have realised that this blog is very much focussed on considerations that should be in your mind during the design of a new application. As we wrap up, you may be thinking to yourself, “this is all well and good but I already have a live application and now I realise I need weeks”. We’ll close our discussion with three options:
1. Convert your application to include a standard weekly calendar
Using the OneStream Database Configuration Utility, it is possible to convert from fixed monthly data record storage to Binary Record Storage and as part of this introduce a standard weekly calendar. Instructions are available in Knowledgebase article KB 0012689. There are a few things to remember if you attempt this:
- You can only convert to a standard weekly calendar using this approach i.e. each year will contain the same number of weeks (52 or 53) and these weeks will be allocated using a standard approach (4,4,5 or 4,5,4 or 5,4,4) across each quarter. You cannot vary the settings by year.
- You must backup, backup, backup your application before attempting this.
- There will likely need to be some remediation work to your existing application in terms of how business rules, cubeviews, member filters etc have been defined since you have introduced another level of detail to your time dimension.
2. Use the UD dimension approach (detailed above)
Everything we’ve talked about above will still apply, you will need to think about data volumes, process, data locking, use of the view dimension etc.
3. Rebuild your application
Arguably, this will seem like the nuclear option but you may decide its an option, if you are early enough in build, or you are already thinking of making significant changes to your application which would benefit from a rebuild anyway.
Hopefully, this blog has given you some food for thought when considering reporting weekly data in OneStream and whether or not to set up a weekly calendar. If you want information on the steps to take to create a custom time dimension, take a look at OneStream Essentials: Implementing OneStream
