topic Re: Data Model in Dashboards & Analytics Discussions

Data Model

Naruto — Mon, 02 May 2022 18:05:51 GMT

I have table A(Parent), TableB(Child) and Table C(Parent), im pulling records from Table A and Table C, i have set table b as base field of table c. In the manager filters &Prompts when added field from table A and Table C.

Re: Data Model

JoeM — Tue, 03 May 2022 14:22:34 GMT

Hi @Naruto - thank you for your questions. Would you be able to provide a bit more detail on the issue you are running into? Is the filter/prompt behavior not acting as expected?

Re: Data Model

Naruto — Wed, 04 May 2022 15:50:43 GMT

Hi @JoeM ,

Lets take few tables obj_item_A table, obj_inv_item_c, obj_item_B(alias of A) and some other attribute table.

as per the model obj_inv_item is child table for both obj_item_A abd obj_tem_B. In the prompts i have added columns from obj_item_A and obj_item_B but not pulling anything from obj_inv_item.

in the prompt i can see the same data for the fields from both table, as per data data obj_item_b will show only records which obj_inv_id same as obj_item_b_id.

Re: Data Model

JoeM — Thu, 05 May 2022 03:02:45 GMT

I observed a different behavior, where obj_item_A and obj_item_B showed all values wether or not they were in obj_inv_item. To pull a prompt of only values for each table, I created two new prompts with the case statement:

int( case( obj_item_A.id = obj_item_inv.id, obj_item_A.id, null() ) )

Re: Data Model

Naruto — Thu, 05 May 2022 06:19:43 GMT

Hi @JoeM ,

Your suggesting to go with Applied fielted and write the formula. currently that's how i have achived the functionaly.

what i'm looking is in the insight we have option to set base field and at business schema we have option to set base table, if there is any similar approach it would resolve my issue.

Re: Data Model

JoeM — Thu, 05 May 2022 14:45:50 GMT

Hi @Naruto - I may have misunderstood your issue.

Could you correct my understanding of your issue?

Issue - Prompt value displaying not as expected.

When obj_item_A and obj_item_B are added to prompts, obj_item_A will show all values in the prompt despite whether the fields are related to obj_inv_item_c or not. Meanwhile, obj_item_B will show only values in the prompt that are related to obj_item_B.

Solution A:

Create a prompt via formula (not applied filters), that the prompt behavior of obj_item_B to obj_item_A.

Solution B:

Are you looking to do the flip? Making the prompt behavior of prompt behavior of obj_item_A to obj_item_B?

Re: Data Model

Naruto — Wed, 11 May 2022 02:37:10 GMT

Hi @JoeM ,

Let me explain my model

from apps.obj_item_id A,
apps.obj_inv_item B,
apps.obj_item_id A
where 1=1
and C.obj_id = B.obj_id
and B.rel_id = A.obj_id

C.Column1 has all the details after the join i'll get only inv_item_id, if i add A.column1 to prompt it will display all the records.

and in future if i have to add a new fields also i have to go with creating a new formula correct.