Skip to main content
On the backlog

Look-up value in cube

Related products:Windows GUIUniversal GUIIndicium Service Tier

Jop ter Horst
Vanguard
I'd like to see the look-up value of a table in a cube. For example when I want to show the employee, now I have to use employee_name, thats fine. But often I'd like to create a reference between the cube/view and employee, so I have to add employee_id too, now we have two columns what could be one if the cube supported look-up values.
Did this topic help you find an answer to your question?

Forum|alt.badge.img+13
  • Hero
  • October 30, 2019

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).


Arjan Sollie
Thinkwise blogger
Forum|alt.badge.img+14

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


Harold
Apprentice
Forum|alt.badge.img+1
  • Apprentice
  • November 5, 2019

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


  • Rookie
  • November 23, 2020

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

 

Thanks.


Jochem Pieper
Moderator
Forum|alt.badge.img+3

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.


Mark Jongeling
Administrator
Forum|alt.badge.img+23
The following idea has been merged into this idea:
https://community.thinkwisesoftware.com/ideas/lookup-values-in-cube-dimensions-4963
All the votes have been transferred into this idea.

Anne Buit
Community Manager
Forum|alt.badge.img+5
  • Community Manager
  • October 25, 2024

 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:

project_id milestone_id no_of_hours
1 10 500
1 20 300
1 30 100
2 10 13
2 20 44
3 10 9000
3 20 5000

 

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

milestone_id no_of_hours
10 9513
20 5344
30 100

 

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:

milestone_id no_of_hours
Preparation 500
Development 300
Completion 100
Support 1 13
Support 2 44
Research 9000
Construction 5000

 

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?



Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings