Get Free Trial
Get Free Trial

OCSV Hierarchy over tree levels produces incorrect data

Allan Lima asked on March 22, 2019

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

25 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

Public
Vera Didenko Flexmonster April 26, 2019

Hello, Allan,
 
We are glad to announce that the issue with compressing level data in the Data Compressor for Node.js is now resolved and the fix is available on our website.
 
You are welcome to update the component.
 
Please let us know if everything works fine for you.
 
We are looking forward to hearing from you.
 
Best Regards,
Vera

Public
Allan Lima April 26, 2019

Hi Vera!

That’s great news. Which version is it ? In the one published 4 days ago ? 2.7.4 ?

Public
Vera Didenko Flexmonster April 26, 2019

Hello, Allan,
 
Thank you for your prompt reply.
 
Yes, the fix is available on our website in the latest version 2.7.4 of Flexmonster.
Here is a link to the release notes: https://www.flexmonster.com/release-notes/
 
Please let us know if everything works for you and if you have further questions.
 
Waiting for your feedback.
 
Best Regards,
Vera

Public
Allan Lima May 2, 2019

Hi, Vera, we’ve tested the 2.7.4 version and the data is still coming wrong.

Do you know if something in the syntax changed to make it work ? We didn’t find anything about the subject. 

We are sending into the results of our tests. To further help you understand the problem and give a better picture:
Bebida” is portuguese for “drink“.
bebida alcoólica” is portuguese for “alcoholic drink“.
So, “bebida alcoólica” should be inside “Bebidas”. This is how we put this inside the query, but it is not how it showed us.

You know if we’re doing something wrong ? If so, can you help us to fix the problem ?
 

Public
Tanya Gryshko Flexmonster May 2, 2019

Hello, Allan,

Thanks for an update.

Since the fix was made inside the Data Compressor for Node.js, we kindly ask you to update your version of Flexmonster Data Compressor as well.

Please run this command to update Flexmonster Data Compressor npm package:

npm update flexmonster-compressor --save

Let me know if it helps resolve the issue.

Regards,
Tanya

Public
Allan Lima May 3, 2019

Hello, Tanya, good morning
I’m not sure, but we may be doing something wrong. We’ve updated flexmonster compressor, but the result is the same, sadly
 
We are sending you the two images, One contains the updated version of flexmonster, the other contains the result we received

we hope it helps 🙂
 
 

Public
Tanya Gryshko Flexmonster May 3, 2019

Hello, Allan,
Thank you for your quick response.

We suggest checking once more that your JSON data has a proper structure:
1) it is an array of objects
2) all hierarchies are defined as recommended in the documentation: https://www.flexmonster.com/doc/data-types-in-json/

After completing the steps above, could you please share your sample JSON data with us? We would check it on our side to see the result.

Waiting for your reply.

Regards,
Tanya

Public
Allan Lima May 3, 2019

Hi, Tanya, thank for your response.
We separated the results from our backend and created two files. One with the JSON, using the configuration of data we need to have and another file with the compressed OCSV data we got from the server.
 
We are sending a print of how the results behave when using JSON ( and that’s how they should behave when using OCSV )

If there’s something more I can do, just ask.
The image files both represent how data should behave inside our trees. They’re for reference. The JSON contains the data needed to make it work, the OCSV contains the compressed data we’re receiving
 
link with the files and the json -> https://drive.google.com/drive/folders/1c-q54ZPizCDvgcOIOoGuuPMYvc9Jm9SA?usp=sharing
 
Regards,
Allan
 
 

Public
Dmytro Zvazhii Flexmonster May 7, 2019

Hello Allan,
Thank for providing us with your data sample.
 
We have looked through it and found that the level fields are not defined correctly. When using a JSON data source the data types should be defined in the metaobject. Please refer to a small sample in the test.json file. The first object of the array is a metaobject and it defines the structure of JSON fields.
 
In case you need to set additional data types, please refer to our docs of JSON data types: https://www.flexmonster.com/doc/data-types-in-json/.
 
Let us know if everything works fine for you.
Regards,
Dmytro

Attachments:
test.json

Public
Allan Lima May 7, 2019

Dmytro, sorry, I think I didn’t understand. 

I’ve came to open this issue because I can’t use JSON for my data. My actual json has several lines, wich makes hard for the browser to parse. So we’ve switched to the compressor.

We had problems with the data compressor, so we contacted you and we’ve sent examples of what we need.
We’ve waited to the compressor updates. So far, so good

The update was released, we have tested and we’ve detected that the problem persists. We contacted you again and told what happened and tried the suggestion you gave. 

The last time we chatted Tanya asked us to send examples of how data should behave and the actual data with it. We have sent it. 

I saw that we forgot to send the header with the correct configuration. But I’m confused, what now ?

You sent me a file with the configuration that is correct. We’ve used the same configuration and it worked on our end. But we don’t want to use JSON. Our data in JSON comes in almost 10mb and the browser can’t parse it. 

There’s something I need to do now, another step I can take here ? 

Public
Dmytro Zvazhii Flexmonster May 8, 2019

Hello Allan,
Thank you for your feedback.
It seems there was some sort of misunderstanding so let’s clarify some points.
 
As far as we know, you are using PostgreSQL and you need to handle hierarchical data. We do realise that using the raw JSON data is not an option for you. 
In such a case Flexmonster Compressor is a good choice.
 
During Compressor testing, you found an issue with compressing hierarchical JSON data in Node.js compressor. The issue did exist and we have successfully fixed it in version 2.7.4.
 
Could you please confirm that you are using compressJsonStream API call to compress the output from PostgreSQL?
If the answer is yes, the first recommendation that you found on our forum: https://www.flexmonster.com/question/how-do-i-define-hierarchy-on-ocsv-data-generated-from-sql-server-database/ led to misunderstanding. The approach described in that thread is only applicable to the CSV based response and does not work with the JSON based response. In such case we recommend the following steps:
 
– remove the SQL alias from your query
– add the metaobject as the second parameter of compressJsonStream method (options.header)
Here is an example:

let resultStream = connection.query("SELECT time FROM time").stream();
let outputStream = compressor.compressJsonStream(resultStream, {
header: {
time: {
type: "time"
}
}
}

Using such an approach, you can define the structure of the data without editing the SQL query.

 

Please let us know if we understand your case correctly. Also, please notify us if the suggested approach helped to resolve the case.
Regards,
Dmytro

Public
Allan Lima May 9, 2019

Hello, Dmytro
Thank you for your response!
Yes! It was a misunderstanding. We implemented as you sugested and here are our results
 
When using the flexmonster compressor with only one “departamento” entry, it goes well. It is fine tuned and work as intended. 
 
We ran into problems as we added more “departamento”‘s into the mix. I’ll send you samples of what happened. I think we evolved pretty well and we are in the right direction. We just need to fill the gaps now. We appreciate all your efforts to make it work. If we’re doing something wrong on our side, tell us and we’ll retry here
 
Now, we’ll send what happened to us: 

The first two examples ( bebidas-right and flexmonster-bebidas-right) show that the data, using a single “departamento”, is correct. Everything is in their places. 

The other examples ( expected-bebidas and received-bebidas ) show that, when we’ve put more than one “departamento”‘s it confuses the data and spill out “REFR COLA PET” as “departamento” when it should be inside “BEBIDAS”
This happens with any other combinations of data, but not when using those data alone.

We guess that, somewhere along the line, the compressor is confusing the stream and classifying the data incorrectly when the input comes from different parents. 

Is there something we can do here to solve ? Is something that you can solve ? If there’s something we can do, just ask 🙂 
 
Also the dataset is the same we’ve send in last comments. And it still in the google drive 
 

Public
Dmytro Zvazhii Flexmonster May 14, 2019

Hello Allan,
Thank you for giving us some time.
It seems that we are on the same page now.
We have made the tests with the suggested data sample and the issue is reproducible on our side.
Our dev team will continue the research and notify you with the results ETA Jun 3.
Also, could you please provide us with a smaller slice of data from your screenshots? The original data sample is pretty big and it is easier to work with a smaller one.
Regards,
Dmytro

Public
Allan Lima May 14, 2019

Yes! It seems that we are!
Thank you! 🙂
 
Sure we can send you the data. We have limited that data output to ~6500 entries. They’re in json and they have the same pattern we exposed earlier
It’s attached to this message 
 
Regards
 

Attachments:
flexmonster-data.json

Public
Vera Didenko Flexmonster May 15, 2019

Hello, Allan,
 
Thank you for providing us with a smaller slice of data.
This will help our team a lot when testing the solution.
 
We will notify you about the results ETA Jun 3.
 
Please let us know if you have any questions.
 
Best Regards,
Vera

Public
Vera Didenko Flexmonster June 4, 2019

Hello, Allan,
 
We are glad to announce that the issue with compressing similar member names on different levels as well as the issue with appending compressed data are fixed.
 
The fix is available in the 2.7.7 version of Flexmonster
 
You are welcome to update the component
 
A kind reminder is to update Flexmonster Data Compressor also.
 
Please let us know if everything works fine.
 
We are looking forward to hearing from you.
 
Best Regards,
Vera

Public
Allan Lima June 4, 2019

Hi Vera!

Glad to know that! We’ll be testing the results and will return soon!

Thank you!

Please login or Register to Submit Answer