asked on December 20, 2019
Good afternoon!
We have some multi-level/dimensional hierarchical data (not always a 1:1) which is currently stored across multiple different platforms & tables. We're looking to bring all of this data into FlexMonster, and we were wondering if you could give us some advice/feedback in regards to the best way to set this up, and potential information on what technology would be the best to use for integration with FlexMonster. (We currently only use tw-dimensional tables via MSSQL but could expand to other integrations types as needed.)
Here is an example of how the data is laid out:
Level 1: Customer data
Level 2 (I): Order data (one customer will have many orders)
Level 3 (I): Task-list data (one order will have one task-list, but one task-list may be associated with many orders)
Level 4: Task data (one task-list will be associated with many tasks)
Level 3 (II): Hard-cost data (one order will have many hard-costs)
Level 2 (II): AM-costs (one customer will have many AM-costs)
They key is that we would like to be able to slice & dice information across all of these levels without potentially duplicating information. For example, one order (Level 2(I)) may have a cost of $300 associated with it, but when we pull task data (Level 4) for the order, we don't want that revenue to be duplicated across each of those tasks.
Here are a couple of articles we found that may be along the right track, but we're not entirely sure:
Any help that you can potentially provide to us would be greatly appreciated.
Thanks!
-Lee-ann
Voice Media Group
Leeann.Dunton@voicemediagroup.com
⋅ December 24, 2019
Hello, Lee-ann,
Thank you for writing to us.
We would like to explain that when many to many or many to one relationships are present, duplicated data is inevitable.
Depending on the use case, our team kindly suggests managing the many to many and many to one relationships in the following ways:
1) Specifying the needed data types:
For some use cases, the data structure can be adjusted by using our data types (for example, defining hierarchies): https://www.flexmonster.com/doc/managing-data-presentation-json
Please keep in mind that defining data types on the client-side may not cover certain cases.
2) Adjusting the output via certain modifications:
In some cases, adjusting the report representation can help to avoid certain misunderstandings caused by many to many or many to one relationships.
For example, a possible solution to the situation you described earlier (about the $300 value being duplicated) could be to apply the Max or Min aggregation to the measure. This way the Grand Totals will display $300 instead of, let's say $900, thus eliminating possible misunderstandings.
3) Preprocessing the data on the server-side:
For more complex situations, we kindly recommend preparing/re-organizing the fetched from the database data on the server-side before passing it to Flexmonster. This approach provides a wider range of possibilities to represent your data.
We hope this helps.
Please let us know if you have any additional questions.
Best regards,
Vera
