Experience with two time-entities in one Cube and data entry

Oliver Walter · 2025-04-04T07:37:52+00:00

There was an error rendering this rich post.

Oliver Walter

Hello everyone,

for our budget planning we would like to create a report with this setup

With the standard time entities this setup is not possible, since month and year are on one branch of the entitytree.

For other purposes we created a custom month-entity which groups all the same months (all Januaries, all Februaries,…)

If we would use those two entities as seperate dimensions in the cube, we could build the report as we wanted. (Especially for dataentry on empty fields).

But since the a time-entity is different, for there are a lot of background-calculations using them, we are not sure if this would work as intended. Especially regarding split&splat and time functions.

So my question is, does anyone have experience with this kind of setup? Mainly regarding dataentry.

Find more posts tagged with

Accepted answers

All comments

Helmut Heimann

Hi @Oliver Walter ,

you can set it up that way but you won't be able to use split&splat with that configuration when there are no values at the MM level.

I've set up a small example for that with a time range a bit more complex than yours, but it has the MM entity

The cube is set up according to this

with "MonthName" being the MM entity.
Now, I've set up two dataviews to show what will happen on data entry, one with Year in the columns (like you wanted to use) and one with Year and MonthName.

As you can see, you'll only be able to input values in the above dataview when there already are values on the MonthName level:

That said, you will have to initialize the cube to use it with split&splat on year level.

Best,
Helmut

Helmut Heimann

Hi @Oliver Walter ,

sorry, I forgot to show what will happen if the MonthName is in the rows (like you wanted)—that would look like this

You could, of course, select a year for data entry that would open up the columns per year—but, I assume that is not what you had in mind.

Best,
Helmut

Lorenzo Aiani

HI @Oliver Walter ,

From what I've seen, it's better to avoid having a cube with two branches of time ranges. I've noticed some issues, especially with time functions. Because at the end, for the system the data is not exactly at month level.

What if keeping the cube structured with the Month entity, then use the Year and the custom Month entity in reports?

For Data entry, if you want to enable data entry on a layout by Year and Custom Month entity, you need to menage the cases when you have an empty cell. In this case you can think on a Drill down or to define the Pattern-based allocation where your driver cube is nothing more than a cube flagged on each month.

This will require form your side some test to understand all corner cases.

Regards,

Lorenzo

Oliver Walter

Thx for the effort @Helmut Heimann, but I didn't aks for a how to. I asked for experiences using this kind of cube-structure. Especially regarding the internal calculations of board. The question is, do those calculations, like time functions and split&splat still work correct? Or does board get confused when there are two time entities in the cube.

Oliver Walter

Thx for the answer @Lorenzo Aiani .

So, did you use a cube like that or is your answer more based on your general experience with board?

We also thought of the workarounds you suggested, But if you drill down for example on January, you get a table with all the Januaries (2024.01, 2025.01…). And a pattern-based allocation would, in most cases, need a rework by the planners

Lorenzo Aiani

Hi, we tried to use it to understand different options,

but then we realized that we had problems with time functions (I don't remember fully all cases), and we decided to use the standard time entity. On drill down, yes, you can have that effect (depends on screen selections). On pattern base, it should be used only when you are at generic month-year level, so you have only one lower level which is the month (Board month entity), and when there are no values already entered, otherwise the normal split and splat works fine, no?

You can think to manage the data entry using the generic month and year (please test all cases to see if works properly), but I think you need to move data in a cube where you have the proper month entity to be used in other area of your project.

Oliver Walter

Thanks again. Our approach will now use two different cubes. One with the two time entities for the data entry and one on the base entity for everything else. This of course needs some extra dataflows in our procedures, but this way we should get the convenience for the users and also be save concerning all the background-calculations.