8 Replies Latest reply on Aug 23, 2018 1:59 PM by Okechukwu Ossai

    Trouble with 1-n relationship

    Manuel Schrenk

      Hey,

       

      i would like to do some reporting with fleet management data. In this case There are a few 1-many relationships:

       

      1 vehicle - many invoices

      1 vehicle - many damages

      1 vehicle - many fuel data

      etc.

       

      Each vehicle has a manufacturer and a specific model. The goal is to create reports to answer question like that:

      - Sum of refuelling costs for each manufacturer

      - Which models are expensive? -> Mean of invoices

      - etc.

       

      I tried to add invoices as primary data source and vehicles as secondary data source. If i create a dashboard then drill-down is not possible. I read a lot about 1-many relationships and data blending. If someone can give me some hints how to start the best way i would be very thankful.

       

      Thanks in advance. Best wishes

      Manuel

        • 1. Re: Trouble with 1-n relationship
          Okechukwu Ossai

          I won't suggest blending these datasets.

           

          I will keep Vehicle on the left as the primary dataset and then join Invoices, damages and fuel data to it. A left join or inner join will be fine depending on what your requirements are.

          • 2. Re: Trouble with 1-n relationship
            Manuel Schrenk

            Thanks for your reply. In that case i get a lot of duplicated vehicles:

             

             

            To avoid that i used blending.

            • 3. Re: Trouble with 1-n relationship
              Ankit Bansal

              Manuel,

               

              Do you have 4 data sources each for

              vehicle,invoices,damages, fuel data??

              and if the relationship is like :

              1 vehicle - many invoices

              1 vehicle - many damages

              1 vehicle - many fuel data

               

              Then I would suggest keep vehicle as primary data source and other 3 as secondary.

              • 4. Re: Trouble with 1-n relationship
                Manuel Schrenk

                Thanks for your reply. I have a data source for each. In the screenshot below (sorry a bit german) you can see a dashboard. I can filter manufacturer and model (BMW). I would like to the the invoice costs for a manufacturer and/or model but the drill down is not working for the invoices (secondary data source). Do you know how to include it in filtering (drill-down)?

                 

                • 5. Re: Trouble with 1-n relationship
                  Okechukwu Ossai

                  You get a lot of duplicates probably due to the way you are joining the datasets together. What fields are joining on? You probably need to add more fields to the join. If the datasets contain location (country, region) and date fields, you may add that as well.

                   

                  If you add the dimensions to the row shelf in Tableau, you can discover what is causing the duplication and how to make your join criteria stricter.

                  • 6. Re: Trouble with 1-n relationship
                    Ankit Bansal

                    The reason drill down is not working for invoices is you have not created a relationship between the manufacture and invoices in your Invoices worksheet. You need to blend the data sources for drill down to work.

                    • 7. Re: Trouble with 1-n relationship
                      Manuel Schrenk

                      Dear Ankit,

                      here you can find my data set attached.

                       

                      Data sources should have a relationship. I can not change primary and secondery thats a bit weird. In the sheet 'Fuel' there are refueling data sets for vehicles which are not listed in the primary data source. I would like to avoid that. And it would be great to drill-down the dashboard. But as explained the filter das not apply for Fuel. It would be great if you could have a look. Thank you.

                      • 8. Re: Trouble with 1-n relationship
                        Okechukwu Ossai

                        Hi Manuel,

                         

                        The Manufacturer worksheet in your original workbook fails to filter the Fuel worksheet because the Manufacturer sheet is not blended.

                         

                        I previously suggested that you join the datasets instead of blending. I still believe that's the best solution after looking at your data. I have joined the fuel data to the vehicle data using an inner join on fahrzeug id. Drill down is easier in this joined dataset. An inner join does not duplicate your data. Total number of records is controlled by Belegdatum in the Tankungen table. The FAHRZEUGE table is less granular. So, variables from FAHRZEUGE will be repeated for each date in Tankungen table. There is no real data duplication, since every row contains a unique fahrzeug id - Belegdatum combination.

                         

                        The worksheets "Data Relationship" and "Data Relationship - Blend", each created from joined and blended datasets, are exactly the same. So, data join is a better alternative. See attached workbook.

                         

                         

                         

                         

                         

                         

                         

                        Hope this helps.

                        Ossai