We're pulling project status' as our attribute 9 from an ERP integration what is the best way to pull those values into an existing summary dashboard? I was going the UD8 route but is there an easier way through Memberfilter builder that I can go directly to that data?
Hi, you are applying a relational blend logic here which is a great use of the OneStream Platform!
The thing here is that relational blend typically applies many to one logic in terms of reporting. I.e. in your example, I presume you have many rows loaded from your ERP to the OneStream Stage that aggregate into a single project (group?) in the Cube, i.e. a single row in your cube view. That means that there are multiple Attribute 9 values applicable to a single data point in the Cube I assume (aggregated to a single project / project group).
That means that you need to determine whether the loaded project status is always the same for each source line from the ERP for a given project. If so, you can filter the lines and just return the first Project Status for your Cube View. This works with a dynamic member such an one in a UD8.
If the status field can vary then you have a reporting problem as the system cannot decide for you which of the status' you wish to see. Is it "Closed", "Cancelled", "In Progress",...? In the latter case, I always remove a column such as your project status from the Cube View and add a click-dependent grid view to the dashboard that pulls the corresponding rows from the ERP load into the grid, depending on the cell a user clicked on in the Cube View. That way, a user can analyze the line item details in a grid for a selected CV cell, easily identifying the different status rows. (--> "Relational Blend")
This is also the reason why there is - to my knowledge - no such thing as a dimension-based member selector that one can use to just say "return Attribute 9 from Stage load". Attributes refer to the Stage table, not to a (Cube) dimension.
Adding this also for other users of the forum who might read this: Please note that projects should often not be added to the metadata as those may often be better served in a custom table. In my view, large projects of project groups may be added to the cube if that serves project reporting best, but if a company has tens of thousands of projects, I strongly advise against cube-based reporting and apply a more relational-blend centric logic. If in doubt, please discuss with the corresponding architect on a given project.
Henning- Your reply is fantastic, thank you! What do you mean when you say, "I strongly advise against cube-based reporting and apply a more relational-blend centric logic."? What would that look like? We have a lot of data coming in and our cube view to dashboards have a hard time supporting it.
Hi Ashlee,
As always, it depends on various factors. It depends on data volumes, reporting requirements, amongst other things.
On a high-level, I meant with my comment that having a large number of projects in the cube is not ideal in terms of data volumes, but, - IMHO - the maintainability aspect of it is also critical. If one is creating hundreds of new projects in a dimension each period, the additional workload for maintaining those as well as managing other aspects such as reports, security and others often surpasses the additional reporting benefit of having this in the cube.
Typically, such data is useful in the short-term only. One can pull short-term project data into a data table (OneStream Stage, BI Blend, custom table, etc.) and report from there. Reporting on this data mostly goes through grid views, BI Viewer reports, or Table Views (in spreadsheets); all possible to be used in dashboards. One can apply a UD8 reporting to pull the data into a cube view, though I would personally keep this to where needed only. And as outlined before, this can mostly only happen on a summarized level (due to the many-to-one relationship of the nature of the data).
This guide is lengthy but gets to the foundation of this (Why? How? Where?).
As for your follow-up question, you can load the data to Stage and other tables as already mentioned and report from there using e.g. the possibilities highlighted in bold in the earlier paragraph. What you describe here sounds like you are pulling a lot of data into the cube and have problems supporting it due to the short-termed nature of the data which means you have a huge amount of maintenance work and probably many user requests bombarding you all the time. This is of course all just a guess on my side. Nevertheless, this sounds like it might be worth the time taking back a step and reconsidering the way you load, store, process and report (parts of) your data.
