Look-up value in cube

I noticed this doesn’t work yet in 2019.2. Is this going to be implemented in the future?

Could we add an option to also sort rows and columns according to the lookup? Sorting alphabetically isn’t always preferred. I tend to resort to appending a number to the values that end up as column names. This even requires an additional column in the table with a sorting number because solutions such as row_number() in an expression don’t work (they result in all 1’s for an entire series).

I support his idea, if this change does not have a significant negative influence on the performance of cubes with a large amount data.

I recently had the same problem: vote = vote + 1

I have the same problem. Is there any word if this is something that is being looked into?

Thanks.

I assumed this was already working in the cube, but unfortunately this community idea shows different.

This would be a great addition and also avoids complexity in cube views.

 Hi all,

We’ve evaluated this feature. There are a couple of challenges I’d like to share.

Given the following data structure and data set:

In this scenario, a semantic key is used for milestone (milestone_nr).

The result is that not every unique milestone number will have the same milestone name.

Consider a cube that is set up on milestone activity with the project id and the milestone id as dimensions and the number of hours as value. The resulting cube would use the following dataset:

When the data is aggregated per milestone, the resulting cube would look as following:

These can no longer be translated as look-ups, as the value ‘10’ has multiple translations.

This scenario would favor grouping based on the translation of a look-up dimension, as the semantic key is not meaningful here. This would look as following:

However, this would immediately change cubes in an unfortunate way where the semantic key is actually meaningful, and/or situations where the translation is not unique and should always be considered in context of other fields.

For instance, consider a database which only uses identities. Employee name is unique, but only per company.

When you create a cube of travel time and you do not include the company as a dimension, an employee named ‘John Dough’ in both companies will be aggregated. Even though they have different records and are in fact two different persons.

In this scenario, the end user shouldn’t aggregate based on a translated employee_id, but should always place the employee_id in a group with company_id.

When this rule is enforced, it will be easier for the GUI to translate an employee as the full look-up context is present as dimensions.

I’d love to hear your thoughts on this.

Should we aggregate based on the translation instead of the foreign key value?

Or should we aggregate based on the foreign key values and afterwards translate, provided that the full foreign key is placed as dimensions in the cube?