Need transform an existing property into a dimension

,
1

Hi there, I am looking for some help/advice.

I currently have an Item Dimension with something called a ‘Product Segment’. This is currently a property of the Item Dimension. My users want this to become a Dimension so they can run analytics in Nested View & Export without showing the Item Code.

The issue is, when I remove Product Segment as a Property and add it as a dimension, we have to rebuild the widgets and the old property falls off of existing favorites. Users must then go in and add the new dimension for all of their favorites.

I have found that while hiding the Code For Item ID and keeping the Product Segment as a property works on the browser, but when the data is exported, the Item Code still appears so that solution wouldn’t work.

I can create a ‘copy’ of the property Product Segment and drag the copy over as a new dimension while maintaining the old property. This keeps it clean on the user side, but I wondered what the implications were of doing this multiple time on a database as users analytics needs change?

Does anyone have any insight on how much creating a clones of a properties to create new dimensions would bog down our databases over time? I am trying to find perfect line to prevent our users from getting frustrated, but this doesn’t seem to be a ‘clean’ solution on the backend.

1 Like
2

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:

  1. 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.
  2. 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

1 Like
3

I 100% agree with this suggestion; I can easily do this in Power BI with DAX which has very little impact to performance. In EDA, as Jon mentioned, this requires you to clone an additional field just in order to do so. Now, not only is the build performance impacted by the additional dimension, it also requires more resources to load the additional bytes; so we’re really adding insult to injury.

1 Like