OAC - modeling quiz with multiples answers per question

Daniel Vornicu

Hello,

I have a table with multiples questions/answers per person. Sometimes one question cas have multiple answers like bellow

I have a semantic model and in the logical layer a create a calculated column for each posible anwser for the Question 1 (3 possible answers in this exemple)

So I created 3 calculated columns(indicators) for replacing the dimension 'Question 1'

Question_A1 to count the first answer as: FILTER(CountAnswers USING Question1 like '%Answer A%')

Question_A2 to count the second answer as: FILTER(CountAnswers USING Question1 like '%Answer B%')

Question_A3 to count the third answer as: FILTER(CountAnswers USING Question1 like '%Answer C%')

CountAnswers is also a column for counting the number of rows.

Now In OAC in the workbook I have these values for each indicator:

Question_A1 : 2

Question_A2: 2

Question_A3: 1

I want to make a Horizontal bar Graph to display the values for each reponse. For better readability I want to display 'Answer A', 'Answer B', 'Answer C' not Question_A1, Question_A2, Question_A3 for the graph legend/axes

In put a description in the logical layer column or even an alternative name but I didn't know how to display these fields in the OAC workbook

Question_A1 has the description 'Answer A' and the alternative name 'Answer A' also.

Question_A2 has the description 'Answer B' and the alternative name 'Answer B'…the same for Question_A3

How can I diplay these alternative names ( the anwser value instead of the calculated column name) ?

Any other solution is welcome of course, but I don't want to create a second table or something like that.

I have 35 questions and maybe 8-9 questions have multiple answers (in general more that 10 possible answers for the question)

Thanks!

Daniel

SteveF-Oracle

@Daniel Vornicu

I think some others BI tools have this posibility to extract data from a field with a separator (like 'Value 1; Value 2; Value 3') and convert the result as a dimension.

You see if creating a data flow would meet your needs for transform, extract, etc.
https://blogs.oracle.com/analytics/post/oracle-analytics-data-flow-cheat-sheet-making-sense-of-large-data

OAC also interacts within an ecosystem, such as Oracle Document Understanding, and many other Oracle services

Juste an example: For a table viz I drag 2 times an indicator and I can display it as 'value' and as 'percent of'. But only for table viz I have this possibilty (not for bar chars, pies etc). So I must create an extra calculation for the percent column. Sometimes it's frustrating.

Please do log Idea Lab requests with details and use-case for enhancements that vizualizations and other workflows. They do get reviewed regularly.

Federico Venturin - Oracle

Hi @Daniel Vornicu ,

I can't think any way to achieve your goal other than manually changing the column header for each Question_XX column in the visualization.

I'd really recommend to transform your table into something more manageable as the current approach looks cumbersome and inefficient (given that 8-9 questions have more than 10 possible answers, it means that you have to build at least 80 calculated columns in the RPD and eventually rename the column headers in each visualization).

Gianni Ceresa

Data visualization doesn't replace to design a storage for your data that is adapted to what you want to do with it. Your current data format is maybe the format you are given by the source of the data, but doesn't mean you have to stick to it for visualization and analysis.

What would be a cleaner model for your data would be a storage by row: PERSON_ID | QUESTION_ID | ANSWER

The questions with a single answer will be a single row, the questions with multiple answers will be multiple rows.

This data model would then let you do everything you ask for, without having to write and maintain a large number of expressions.

It's definitely worth to add a data transformation step to turn your data into a better format allowing you to use it, instead of spending hours in trying to "hide" the fact your data model isn't adapted to your needs.

Daniel Vornicu

Hello,

Thanks @Federico Venturin and @Gianni Ceresa for your answers. Of course there is a cleaner model based on PERSON_ID | QUESTION_ID | ANSWER fields.

I wasn't very accurate on the quiz. I have finally one single question with 10 possibles and combined answers (no 8-9 like as I said). So the table has 3500 lines (one line per person that answer to the quiz) and I have 35 questions as columns (34 questions have unique answer, no problem for these). Fot this single question I have made 10 calculations like I said.

I think some others BI tools have this posibility to extract data from a field with a separator (like 'Value 1; Value 2; Value 3') and convert the result as a dimension.

If I change the model to pivot the 34 + 1 questions in lines I will have minimum 34 x 3500= 119 000 lines in my table, ignoring the multi-anwser question. For this reason I decided to make 10 new calculations. Each person has also a name, address, email, telephone and others realated fields. To avoid the multiplication of these fields I will have to create a parent-child structure with 2 tables and so on…

Yes, Data visualization doesn't replace to design a storage. For my case having the possibilty to display the indicator Description instead of indicator name would have been better. For example I can display this description only on a Tile viz. I don't know the usage of the 'Alternative name' (that I put in the Presentation Layer column).

There are many missing properties on some types of visualisation but that is an another discution.

Juste an example: For a table viz I drag 2 times an indicator and I can display it as 'value' and as 'percent of'. But only for table viz I have this possibilty (not for bar chars, pies etc). So I must create an extra calculation for the percent column. Sometimes it's frustrating.

Thanks for your help anyway,

Daniel

Mostafa Morsy-Oracle

@Daniel Vornicu

I would suggest to create a calculated item to substring the output of Question1 , Question2 and so on and use it in your chart that will be easy to use