Best Implementation practice for high number of dimension members

[vmanojrc30]

We are implementing a consolidation solution where customer will need the ability to analyze data by each Project. Currently they have 12000 projects, and the list grows every year/month. Some Projects may deprecate however we need to retain them to maintain history.

Is it advisable to setup 12000 Project members in the cube considering the potential to grow every month? Appreciate any advice to solve this requirement.

[Henning]

Hi,

in general, OneStream can handle 12k members. It depends on the rest of the data model (usage of extensibility), the data per data units and the system configuration as well as the business rules how performant this will be.

However, the general recommendation is to put projects such as the ones you describe into a relational table and report the details from there. I.e., those should ideally not be put in the cube. Projects such as this are impractical due to their temporary nature, "polluting" the data model with members that have a relatively short lifespan. Maintenance is also not as pleasant, though this often gets automated. Personally, I am a little hesitant over-automating this, as this makes it a little too easy for end users to load too many unnecessary members into the metadata.

If you need to report projects using a dimension's hierarchy, BI Blend may be used for that. 

[FredLucas]

Hi @vmanojrc30,

I agree with @Henning, when considering design options for dimensions with a very high number of members, especially when these are transactional in its nature (like it seems to be the case here e.g.: projects finish their life cycle and are no longer active and new ones are created on a regular basis) the first question I usually ask is: "Do we need to input / plan / adjust data at that level of detail or is this for reporting / detailed analysis only?"

If it's only for reporting purposes then you may consider loading it to stage (but not to the cube) and use relation blend to provide the detailed analysis / reports. OS offers a number of really nice ways to blend cube and transactional data in a way that is seamless to the end user. Another option, especially for large volumes of data that must be analysed in an hierarchical way is BI Blend.

In case users need to input / plan / adjust at that level then register type solutions such as Thing Planning could be considered and if that does not fulfill the requirements then custom relational tables can be created and accessed / edited using Dashboards etc.

In summary, leaving this type of more transactional type data in a relational database (instead of a cube) will help us ensuring a scalable, performant and future proof solution while also improving the maintenance of it.

[vmanojrc30]

Hi @FredLucas @Henning 

Thanks for your advice!

With the Stage load and using relation blend for reporting, will I be able to report/analyze the Project details in Stage by a Cube dimension member?

For example, my GL data is by Cost Center and Project.1 Cost Center rollup multiple Projects. I load the data by Cost Center to Cube and keeping the Project details within Stage. Will I be able to report the balances by Project (reside in Stage) for a given Cost Center Parent member reside in OS Cube?

[Henning]

Hi, yes, absolutely. You can e.g. click on the cost center parent in a cube view and this point-of-view information can be used to filter the data from stage in a grid view. In this case, the filter would filter the stage table (of the same time, entity, etc.) by the base member cost centers of the selected parent, displaying all relevant loaded projects.

As long as there is a 'relation' between the data in the cube and the data in a (relational) table such as stage, the data can be filtered accordingly and analyzed accordingly. Hence the name 'relational blend'. Especially for stage data, this is a very common and easy use case, as data is being mapped from source to target, and one can leverage this mapping easily to blend the data together.

[FredLucas]

If it's the first time you'll be doing this, you might be struggling to picture it, here's an example that works a little bit like linked cube views where you click one cell on the cube view and, in this case, the lower part of the dashboard (that queries data in staging or other relational tables) refreshes based on the selected POV:

In this case I'm selecting Total Cost Centres (drop down above), UK, and Other Benefits and based on that POV running a query against:

1. The PLP register table (employee roaster) that shows me all the related inputs (i.e.: for UK and any cost centre) initially done
2. A separate query against the PLP Calc data that shows me the detail on how the Other Benefits were calculated by Employee.

In this example I went a step further and added a dynamically calculated translated amount column (based on the FX rates loaded to OS) so you can not only show the detail but also analyse it in group currency and have it tying back to your aggregated cube data (assuming we exclude any adjustments made directly in the cube).

I hope this helps illustrating the power and flexibility that you can get with relational blending.