OCSV Hierarchy over tree levels produces incorrect data

As stated here: https://www.flexmonster.com/question/how-do-i-define-hierarchy-on-ocsv-data-generated-from-sql-server-database/
 
We took this approach to bring data from our server and make hierarchies work with flexmonster with database data.
But we’ve reached five levels of nesting in our data and we began to see some mixed results within the sheets. 
 
Well, we have an hierarchy table using Self Join Query syntax. So it looks like this:
 
parentId | departament
null         |   1
 
parentId | department
1             |    2
 
I’m not going through all hierarchy, but it goes 5 levels deep, so we get the idea :). Now for the complex part:
From the database we query more or less like this
 
Please, pay attention that I’m not writing actual SQL syntax per se, I’m only giving an example so you have the idea:

department as department
category as department:category
subcategory as department:category:subcategory
segment as department:category:subcategory:segment
subsegment as department:category:subcategory:segment:subsegment
product as department:category:subcategory:segment:subsegment:product

Now we download the data correctly, but we don’t know if the problem happens when it goes through the compressor or the data parsing, but we have inconsistent data showing in my tables. As shown in attached print, “hortifruti” should be the most parent (department) of “maca”, “banana” and other fruits in that dataset. But, somehow, it confuses the children data as parent data and show them in the wrong hierarchy levels. 
 
Knowing that, we have tried many solutions: we’ve tried ordering them by parent, separating them by id or even using less nesting. However, everything we tried, gave us different results inconsistently. 
So we did go through forum and documentation, but nothing helped. 
What the procedures we have to take to make an self join table to show results consistently ?
Technology Stack used:
PostgresSQL
NodeJS 11
Angular 6
VSCode

Attachments:
untitled.png

8 answers

Public
Vera Didenko Flexmonster March 22, 2019

Hello, Allan,

Thank you for writing to us and describing the problem in such detail.
 
However, the problem is not reproducible on our side. 
Please send us the ocsv response that is sent from the compressor and the report.json file from your Flexmonster pivot table, so we may help you solve the problem.

We are looking forward to hearing from you.

Best Regards,
Vera

Public
Allan Lima March 22, 2019

We attached an exemple of how data should be shown and the OCSV generated automatically by the compressor
Instead of generate a hierarchy, it is repeating the same value in all hierarchy entries 

___ocsv2___2.7.1/2.213
+empresa,+filial,d+data,+descricao,+departamento,+departamento:categoria,+departamento:categoria:subcategoria,+departamento:categoria:subcategoria:segmento,+departamento:categoria:subcategoria:segmento:subsegmento,+departamento:categoria:subcategoria:segmento:subsegmento:produt,-valor_total_custo,+fornecedor,+comprador,-quantidade_vendida,-preco_custo,-preco_vendido,-preco_margem_zero,-valor_total_vendido,-valor_lucro,-margem_lucro
nova empresa,LJ 10,1545357600,SANDALIA IPANEMA CLASSICA MASC,BAZAR,UTILIDADE DO LAR,SANDALIA,SANDALIA FEMININA,SANDALIA FEMININA,SANDALIA IPANEMA CLASSICA MASC,1379.4,,,2,689.7,15.98,948.05,31.96,10,10
^0,LJ 1,1545357600,^0,^0,^0,^0,^0,^0,^0,689.68,^0,^0,1,689.68,15.98,948.02,15.98,10,10
^0,LJ 2,1545357600,^0,^0,^0,^0,^0,^0,^0,1379.36,^0,^0,2,689.68,15.98,948.01,31.96,10,10

 

Attachments:
image.png

Public
Allan Lima March 22, 2019

And this is a sample of how we should show the data based on an interface sample

Attachments:
classificacao.png

Public
Allan Lima March 22, 2019

I was thinking, there is anyway to determine how hierarchy should work in code from the front end ? I mean, when hierarchies go five levels deep or more, the database truncate the column name, making it unreadable.
 
Instead of receiving the data from back end directly, there is a way to configure this in flexmonster itself ?

Public
Vera Didenko Flexmonster March 25, 2019

Hello, Allan,

Thank you for providing us with an example of your ocsv response and for the additional screenshots.

We have noticed that the ocsv you sent us truly doesn’t behave as needed. 
Please send us a CSV data sample and the equal OCSV so we can investigate the issue.
 

In Flexmonster the first line in CSV and OCSV files specifies the data types ( and how hierarchies should be shown).
At the moment, this is the only way how to configure the hierarchy structure.

We are looking forward to hearing from you.

Best Regards,
Vera

Public
Allan Lima March 26, 2019

Hello, Vera
 
Well, we’ve still tried to make it work, but no sucess.
 
About the way flexmonster organizes hierarchy, well, no problem about that. We have pretty deep hierarchies and we just want to make it work, because the column name from the database truncates the string. If you have any sugestion, I’m listening 🙂
About the CSV, we did the folowing: 
 

 It is important to have both data to compare, because we suspect that the compressor might be making pointers to values as a way avoiding duplicates, but in doing so, it is discarding important data, wich may be present into the JSON.
 
Hint: The dataset is pretty big, so be sure to disable syntax highlighting in your code editor ( it consumes a lot of processor and ram not doing so )
 
 
Although we are testing large datasets, we do know that we may need ElasticSearch to manage all the data in a fashionable way, but we’ll implement it later. For now, we’re working with smaller datasets ( about 100.000 rows ) to make it work as intended.
Let me know if you have problems downloading the data. I’ll be here
For now, thank you for all your efforts 

Public
Vera Didenko Flexmonster March 27, 2019

Hello, Allan,

Thank you for your reply and for sending us the sample OCSV and JSON.

We managed to successfully download and analyze the files and with their help, we were able to reproduce the problem you have encountered.
Our team will take a more detailed look at the problem and possible fix, and notify you about the result on the 23rd of April.

A temporary workaround is to use only JSON (avoid using the compressor).

Please note that it is best to define data types in JSON for Flexmonster the following way:

 

[
{
"empresa": {type: "string"},
"filial": {type: "string"},
"data": {type: "datetime"},
"descricao": {type: "string"},
"departamento":{type: "level", hierarchy: "departamento", level: "departamento"},
"categoria":{type: "level", hierarchy: "departamento", level: "categoria", "parent": "departamento"},
"subcategoria":{type: "level", hierarchy: "departamento", level: "subcategoria", "parent": "categoria"},
"segmento":{type: "level", hierarchy: "departamento", level: "segmento", "parent": "subcategoria"},
"subsegmento":{type: "level", hierarchy: "departamento", level: "subsegmento", "parent": "segmento"},
"produt":{type: "level", hierarchy: "departamento", level: "product", "parent": "subsegmento"},
"valor_total_custo":{type: "number"},
},.....
]

 
 
Here is a link to our data types in JSON guide for more details.
 
 

Please let us know if this works for you and if you have further questions.

 

Best Regards,
Vera

Public
Allan Lima March 27, 2019

Hello, Vera
 
Thank you! We’ll be using in JSON for now and wait your return. As we have so much data, we will find a way to make it work as intended
 
Best regards

Please login or Register to Submit Answer