https://www.gooddata.com/ logo
Join Slack
Powered by
Hi, I'm currently working via the API, debugging a...
# gooddata-cloud
k
Hi, I'm currently working via the API, debugging an issue with missing values and WDF. Here's a highly-simplified data model:
Copy code
Main Table:
- id
- tenantId
- ...
- middleTableId

Middle Table:
- id
- tenantId
- ...
- endTableId

End Table:
- id
- tenantId
- ...
- name
The query looks something like this: • mainTable.id (showAllValues: false) • mainTable.field2 (showAllValues: true) • mainTable.field3 (showAllValues: true) • endTable.name (showAllValues: true) We have a WDF on tenantId for every table. (Any associated records will have the same tenantId, since they originate from the same tenant.) Without the WDF, endTable.name is properly nullable. Once the WDF is active, entire rows, where 
middleTable.endTableId
is empty, are not returned. I'm guessing this is because the WDF doesn't pass on endTable (since endTable.tenantId == null, for a null record). However, shouldn't this instead return a null value for 
endTable.name
(and still return the rest of the row)?
Here's some example records, for clarity! This record is always returned:
Copy code
{  // mainTable
  id: 4
  tenantId: "abcd",
  middleTableId: 25
  {  // LEFT JOIN middleTable ON mainTable.middleTableId = middleTable.id
    id: 25,
    tenantId: "abcd",
    endTableId: 12
    {  // LEFT JOIN endTable ON middleTable.endTableId = endTable
      id: 12,
      tenantId: "abcd",  // WDF passes.
      name: "Kayden"  // this value is returned properly.
    }
  }
}
This entire record is only returned when the WDF is inactive:
Copy code
{  // mainTable
  id: 99
  tenantId: "abcd",
  middleTableId: 88
  {  // LEFT JOIN middleTable ON mainTable.middleTableId = middleTable.id
    id: 88,
    tenantId: "abcd",
    endTableId: null
    {  // LEFT JOIN endTable ON middleTable.endTableId = endTable
      id: null,
      tenantId: null,  // WDF fails! This stops the mainTable record from ever being returned.
      name: null  // when the WDF is inactive, this value is returned properly.
    }
  }
}
And, to be clear, this is an issue in production today. We have a metric which counts 
mainTable
records, and one which shows details (including its 
endTable.name
, if one exists). Customers are concerned about the inaccurate reporting, since they see "57 records" but then can only view the details from 54 of them.
i
Hi Kayden, WDFs are always applied to a specific dataset and affect only that particular dataset. But If I understand it correctly, you expect some distribution or hierarchical logic across multiple datasets here, correct? Records with no value in the specified column are treated as not meeting the WDF condition and therefore are not available. Also, if you setup a WDF and the filter value is missing, report execution should result in a 400 status code error with a prompt to specify the missing filter value.
I have to admit that I am bit confused of what are you trying to achieve with this setup. Could you explain a bit why is the "tenantId" null at the "endTable", please?
k
Hi Ivana, Thanks for the prompt response. I'd be glad to clarify! We have a 
tenantId
field on each table, which is non-null – the WDF is on that field. In my example query above, I'm fetching four values: • mainTable.id (showAllValues: false) • mainTable.field2 (showAllValues: true) • mainTable.field3 (showAllValues: true) • endTable.name (showAllValues: true) The only field that is required is mainTable.id. In fact, there may not even be an endTable record at all! Hence why endTable.name is an outer join (showAllValues: true). The two example objects are as follows: 1. mainTable has an endTable record -> WDF passes for endTable.name -> returns entire record properly 2. mainTable does not have an endTable record -> WDF does not pass for endTable.name -> does not return entire record So in my results table, mainTable.id 
4
would show up, but mainTable.id 
99
would not. If I removed 
endTable.name
from the query entirely, both records would show up.
Effectively, I'm wondering how to write this API query – where 
endTable.name
is actually nullable, since the WDF seems to override that.
Technically, this would be more accurate for the second object. Perhaps this can clarify things:
Copy code
{  // mainTable
  id: 99
  tenantId: "abcd",
  middleTableId: 88
  {  // LEFT JOIN middleTable ON mainTable.middleTableId = middleTable.id
    id: 88,
    tenantId: "abcd",
    endTableId: null
    // LEFT JOIN endTable ON middleTable.endTableId = endTable
    // > the above JOIN statement returns null, thus endTable.tenantId = null
    // > since null <> "abcd", WDF fails! This stops the mainTable record from ever being returned.
    // > When the WDF is inactive, rather than not returning the mainTable record at all,
    // > it returns null for endTable.name, still returning the rest of the mainTable data.
    }
  }
}
j
Hi Kayden, Please allow me to jump in here. I can confirm that no other customer has so far complained with any use case where WDFs were not working. I believe that this might rather be a misunderstanding of how should WDFs actually work. Unfortunately, it’s rather hard to understand the issue from the provided description as we are not familiar with your data and how exactly have you implemented the filters within your environment. Therefore, I think the best course of the action here would be if you create some simple real example table Visualisation directly in the UI which involves the use-case which you are describing and tell us exactly what do you currently see vs. what would you expect to see with current set up. Once you create it, feel free to send us the direct link to it (or send a DM if you prefer). We can double-check from our end.
k
Thank you Julius. I will create a real-world example which you can easily step through – I will DM today/tomorrow once I've made it.
6 Views
Open in Slack
PreviousNext
Powered by