Hi @emiller ,
Been there, done that and got the T-shirts. I think in most BI solutions, changing an attribute (property) to a dimension does cause some disruption. As businesses evolve thier data, quite often what was once a “nice to know” becomes a “must know”.
If you take away Phocas or any cube based BI tool, it becomes easy. For example in Excel you could use pivot tables to then group by any value you want. We need a similar solution within Phocas to allow nesting on any value you want. Unfortunatley the current design does not support this level of gourping. It is something I raised at a Phocas confrence back in 2018 where I wanted to group on Customer Name, but since the dimension was based on the customer number, I always got the code in nesting mode. In the end I had to bite the bullet and add a new dimension called customer name and remove it as a property. This did cause quite a bit of disruption as I didnt know how many widgets or favourites was using it.
I now take a different appraoch to maintain consistancy, I have the value twice, once as a property and a copy of the field (as an expression in the designer) and make it a dimension. This approach allows pre-existing widgets and dashboards to remain working and then provides a new means of reporting.
With regards the time a database builds, this is a concern for me too. I got to the point that our Sales DB has over 40 dimensions, I would prefer it to be far less, but for the same reasons as yourself, we needed to have these values as dimensions to support reporting needs.
I raised a long time ago the following post to request that nesting mode removed the requirement of having the code: Nesting mode - removal of code - #5 by dphelmken
I believe due to the nature of the current product it is not possible at this time. My argument for this abilty was two fold:
- Reduces demand of build time on DBs, as it would only need the codes to aggregate by. Also has a net benefit to Phocas as DB space would be reduced.
- Reduces technical debt and confusion with the end users. Having the value once only rather than multiple times.
Note: As far as I am aware the build times are not really impacted by the number of properties on the data, it is the number of dimensions it has to aggregate by. As a workaround, we requuested that the database in question was set to columnstore rather than full build. This change made the DB build times fall by 70%, the tradeoff is that more work is done on the query runtime as less aggregated values are stored within the DB. I.e. Sum sales of customer would be performed real-time rather than having the answer immediatley at hand.
So bottom line, with the current structure within Phocas I would reccomend you leave the propery alone and add a new dimension to cater for your needs, this does increase technical debt, but as far as I am aware this is the only way to achieve the outcome you desire. I would be over the moon if this restriction could be fixed but based on the time the PUG article was posted, I do not believe a solution is coming anytime soon.
If you believe the attached link is relevant, please would you mind liking the post to gain traction with the dev team.
Regards,
Jon