Best Implementation practice for high number of dimension members

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. 

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.

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?