10 Replies Latest reply on Oct 6, 2013 11:34 PM by Prem Reddy

    Visualizing Fly Paths

    Balazs Gunics

      I have an SAP ERP data source, with the ODBC driver made by Starschema. I'm trying to visualize the fly paths in Tableau which are coming from ECC.

       

      These paths are in the SPFLI table, I can query that table with a select *, no problems so far. I get the following columns:

      Country from

      City from

      Departure time

      Country to

      City to

      Arrival time

      Distance ID (km or mi)

      Fltype (null or X)

      Connid (the key to connect the table with other tables like SBOOK, SFLIGHT)

      Distance (the flight distance)

      Flight time

       

      I can visualize the cities, and countries by assigning them a geographic role. However I can't make routes between them. If i could double the data, in a country-city, time, order format, It wouldn't be a problem, but because of the SAP limitations It's not possible.

       

      I can add the data source again, thus having the data available 2 times.

       

      Any help would be much appreciated

        • 2. Re: Visualizing Fly Paths
          Balazs Gunics

          I checked those, but it doesn't solve my problem.

           

          Basically a solution could be to extract SAP data to Excel, then do some transformation, then load the data to tableau, but it's need additional tools to extract SAP data, and modify it, also I'd like to keep the possibility to refresh data from SAP.

           

          Edit:

          I found this topic:

          Mapping route's with Origin and Destination in one row

          But I cannot use custom SQL against SAP.

           

          Regards,

            Balazs

          • 3. Re: Visualizing Fly Paths
            Allan Walker

            Balazs,

             

            It doesn't sound like you have start x,y and end x,y - although you do have City From and City too.  The "distance" intrigues me, was this calculated using Great Circles, or is it the blackbox logged (actual route) distance?  If you have azimuth or heading, you might be able to get the routes that way.

             

            To get the routes into Tableau, here's what I did.  I used ESRI great circles. I then used ET tools to create a point across every path (i.e. line format to point).  This gives me all the co-ordinates along route x,y.  Note, I had to do a lot of extra processing for flights that crossed the International Date Line, and antipodean routes.


            Thanks for reading the blog.  I'd be interested in your approach.  I suspect you are going to have to do quite a bit of pre-processing.


            Tip: use flight duration ([arrival time]-[departure time]) as path order.

            • 4. Re: Visualizing Fly Paths
              Balazs Gunics

              Allan,

               

              Here's what I've tried to do:

              Since Tableau supports adding additional data to an existing dataset, I've tried to make 2 query:

              SELECT SPFLI.AIRPFROM as „Airport”, SPFLI.COUNTRYFR as „Country”,
              SPFLI.CITYFROM as „City”, SPFLI.DEPTIME as „Time”,
              SPFLI.CARRID, SPFLI.CONNID,
              SPFLI.DISTANCE, SPFLI.DISTID,
              SPFLI.FLTIME, SPFLI.FLTYPE
              FROM SPFLI

               

              SELECT SPFLI.AIRPTO as „Airport”, SPFLI.COUNTRYTO as „Country”,
              SPFLI.CITYTO as „City”, SPFLI.ARRTIME as „Time”,
              SPFLI.CARRID, SPFLI.CONNID,
              SPFLI.DISTANCE, SPFLI.DISTID,
              SPFLI.FLTIME, SPFLI.FLTYPE
              FROM SPFLI


              The problem that the current version of the SAP ODBC driver doesn't supports Column Aliases, and after we import SAP data, Tableau stores it in TDE with its original name (Table.Column). Appending is only possible if the source and target column names are the same. If it would be possible to append rows, then we could use either the Time field or have a calculated field in the FROM query with a value of 1, and another field in the TO query with a value of 2. Currently the only approach to use Talend Open Studio to extract-transform SAP data into excel.

               

              I think in a few week I could solve this problem without using anything but the Starschema SAP ODBC driver and Tableau

              • 5. Re: Visualizing Fly Paths
                Ian White

                Hi Balazs

                 

                If you want to do this in Mapfluence, we'd only need the origin/destination pair (can be airport code, lng/lat, address, etc...), with any attributes. Generating the great arcs/calculating distance is trivial in Mapfluence, and they can be visualized as background map layers. If you are interested, I'd suggest evaluating our service. The solution that Allan proposes leverages WMS, a fairly antiquated enterprise mapping standard that hasn't evolved for the better part of a decade. If you just 'need it done,' our commercial service might be of interest, but if resources aren't available or you are having fun hacking, Allan's approach could be suited for you.

                • 6. Re: Visualizing Fly Paths
                  Allan Walker

                  Hi Ian,

                   

                  Sorry, this doesn't involve WMS (unless of course, the user wants a different background mapping solution).


                  It's about importing flight paths in as a TDE, so that the data source can be analysed.

                  • 7. Re: Visualizing Fly Paths
                    Allan Walker

                    Balazs,

                     

                    What I did was take the OpenFlights .dat files, converted them into csv, removed all "N/A" entries.  Then I pre-process in ERSI with a custom formulae for routes crossing the international date line and convert line to point using ET tools.

                     

                    I dumped the CSV files into MySQL, and then I join the route, airport, and airline tables; and pull the extract into Tableau.

                     

                    Is there an option to export your SAP tables into CSV, to get round the driver issue?

                     

                    Best Regards,

                     

                    Allan

                    • 8. Re: Visualizing Fly Paths
                      Balazs Gunics

                      Allan,

                       

                      It is possible to extract SAP data using our PL/SAP connector but I like challenges, so I've tried to do this with our ODBC driver which is in beta stage.

                      Thank you for the tips, I'll post a reply with a complete solution as soon as I could do this without any 3rd party tools.

                       

                      Best Regards,

                      Balázs

                      • 9. Re: Visualizing Fly Paths
                        Allan Walker

                        Awesome! looking forward to it!!!

                        • 10. Re: Visualizing Fly Paths
                          Prem Reddy

                          Hi Balazs,

                           

                          I'm eagerly looking to set up an ODBC connection from Tableau to SAP using ODBC driver made by starschema but couldn't find it. Please let me know if the ODBC connector is a freeware so that I can proceed further or do I need to purchase it. Also before purchasing the same I even want to test it by connecting to SAP Infocube tables.

                           

                          Also I'm looking to create TableauDataExtracts from Talend Open Studio but couldn't as I'm blocked with following error. If you have ever come up with this issue before providing your insights on the same would be very helpful.

                           

                          Talend Tableau Error.png

                           

                          Thanks,

                          Prem