Duplicated values using aggregations
15 May, 2015
Hi,
We are developing our first BI project using YellowFin and are facing with a very simple problem (we hink so) but after some tries we can't find a solution.
We need to know which is the best way to handle with N-M relationships in order to avoid duplicate values when aggregate data.
As a simple example we have just 3 tables, Invoices , Category_level1 and Category_level2, an invoice can belong to several level1 categories (see images for mor detail). At this point we want to make a drill-down of categories (Level2-level1-invoices), here the problem comes when we aggregate a SUM to Cost column because at Level 2 ,as a sum aggreagte result, we are getting always wrong number because the SUM takes the same invoice several times.
I'm sure it is a really simple scenario that Yellowfin can handle with but we can't find the way... HELP!!!!
Regards
We are developing our first BI project using YellowFin and are facing with a very simple problem (we hink so) but after some tries we can't find a solution.
We need to know which is the best way to handle with N-M relationships in order to avoid duplicate values when aggregate data.
As a simple example we have just 3 tables, Invoices , Category_level1 and Category_level2, an invoice can belong to several level1 categories (see images for mor detail). At this point we want to make a drill-down of categories (Level2-level1-invoices), here the problem comes when we aggregate a SUM to Cost column because at Level 2 ,as a sum aggreagte result, we are getting always wrong number because the SUM takes the same invoice several times.
I'm sure it is a really simple scenario that Yellowfin can handle with but we can't find the way... HELP!!!!
Regards
Hi There,
We would love to help you get to the bottom of this issue. Unfortunately your images never came through. Could you please resubmit them as attachments, or if you uploaded them as images could you please click "insert inline" so that they are inserted into the forum post?
Alternatively you could forward all of this information to support@yellowfin.bi which would create a case in our case management system.
Lastly, do you think you could let us know what build of Yellowfin you are currently using? To check the build of Yellowfin you are using go to System Information under Administration > Admin Console > System Information. The version of Yellowfin is listed next to Application Version.
This information will help us replicate any bugs you might be experiencing.
Thanks in advance for gathering this information and we look forward to helping you find a solution.
We hope to hear back soon.
Kind Regards,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
We would love to help you get to the bottom of this issue. Unfortunately your images never came through. Could you please resubmit them as attachments, or if you uploaded them as images could you please click "insert inline" so that they are inserted into the forum post?
Alternatively you could forward all of this information to support@yellowfin.bi which would create a case in our case management system.
Lastly, do you think you could let us know what build of Yellowfin you are currently using? To check the build of Yellowfin you are using go to System Information under Administration > Admin Console > System Information. The version of Yellowfin is listed next to Application Version.
This information will help us replicate any bugs you might be experiencing.
Thanks in advance for gathering this information and we look forward to helping you find a solution.
We hope to hear back soon.
Kind Regards,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
Hi,
Example of the data model
A sample of data within the N-M table
As I told you, when we try to create a drill structure between category_l1 and category_l2 the total cost of category_l2 is wrong because invoice 1 and 4 are added twice.
Regards
Example of the data model
A sample of data within the N-M table
As I told you, when we try to create a drill structure between category_l1 and category_l2 the total cost of category_l2 is wrong because invoice 1 and 4 are added twice.
Regards
We are using
Application Version: 7.1
Build: 20141029
Application Version: 7.1
Build: 20141029
Hi Javi,
I've recreated your data structure, but I have questions about how you are creating your drill structure.
Could you please send me screenshots of how your report is built and how your drill is incorrectly summing the cost? When I drill from category 1 to category 2 the sum of cost is correct, however at the top level it is incorrect because Yellowfin is summing data irrespective of whether or not they are duplicates.
Is this what you are experiencing?
Please let me know when you find a moment.
Kind Regards,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
I've recreated your data structure, but I have questions about how you are creating your drill structure.
Could you please send me screenshots of how your report is built and how your drill is incorrectly summing the cost? When I drill from category 1 to category 2 the sum of cost is correct, however at the top level it is incorrect because Yellowfin is summing data irrespective of whether or not they are duplicates.
Is this what you are experiencing?
Please let me know when you find a moment.
Kind Regards,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
Hi Javi,
I think I might have come up with a work around for you but it involves using drill trough and drill down in combination. Please take a look at my below screen cast and let me know if you think that this solution will work for you:
http://screencast.com/t/qyugdvgCs
If you need more details please let me know. I look forward to hearing back.
Kind Regards,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
I think I might have come up with a work around for you but it involves using drill trough and drill down in combination. Please take a look at my below screen cast and let me know if you think that this solution will work for you:
http://screencast.com/t/qyugdvgCs
If you need more details please let me know. I look forward to hearing back.
Kind Regards,
Dustin
Best regards,
The Yellowfin Support Team
Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
Hi Dustin,
Our problem is exactly what you meant, in the top level of the drill the system is summing duplicated values.
Unfortunately your workaround doesn't meet our needs due it is only applicable to this simple data model and for a specific report , however our datamodel is bigger and have several other relationships from we may need to apply several filters (i.e. dates, company, other categorizations,...), so we'll need that yellowfin was able to handle this N-M relationships .
We are comming from other BI solution which was really hard to deal with , the main reason to move to Yellowfin has been the great UX and ability to work with an entity relationship data models, so... why can't we work with this scenario???
For us it is really weird that a good reporting tool as Yellowfin can't handle this situation, a N-M relationship within a datamodel is really common, I can't believe that any other YF client haven't faced the same problem , so It should have a better way to meet this requirement, isn't it??
We are a little nervous about this issue , because the successful of our project (it is a big one) lies mainly on how to meet this requirement.
Kindest Regards
Our problem is exactly what you meant, in the top level of the drill the system is summing duplicated values.
Unfortunately your workaround doesn't meet our needs due it is only applicable to this simple data model and for a specific report , however our datamodel is bigger and have several other relationships from we may need to apply several filters (i.e. dates, company, other categorizations,...), so we'll need that yellowfin was able to handle this N-M relationships .
We are comming from other BI solution which was really hard to deal with , the main reason to move to Yellowfin has been the great UX and ability to work with an entity relationship data models, so... why can't we work with this scenario???
For us it is really weird that a good reporting tool as Yellowfin can't handle this situation, a N-M relationship within a datamodel is really common, I can't believe that any other YF client haven't faced the same problem , so It should have a better way to meet this requirement, isn't it??
We are a little nervous about this issue , because the successful of our project (it is a big one) lies mainly on how to meet this requirement.
Kindest Regards
Hi ,
do you have more alternatives/solutions from your side?
Regards
do you have more alternatives/solutions from your side?
Regards