AbstractExtract-Transform-Load (ETL) flows are used to extract data, transform it, and load it into data warehouses (DWs). The dominating ETL tools use graphical user interfaces (GUIs) where users must manually place steps/components on a canvas and manually connect them using lines. This provides an easy to understand overview of the ETL flow but can also be rather tedious and require much trivial work for simple things. We, therefore, challenge this approach and propose to develop ETL flows by writing code. To make the programming easy, we proposed the Python-based ETL framework in 2009. We have extended significantly since the original release, and in this paper, we present an up-to-date overview of the framework. offers commonly used functionality for programmatic ETL development and enables the user to efficiently create effective ETL flows with the full power of programming. Each dimension is represented by a dimension object that manages the underlying table or tables in the case of a snowflaked dimension. Thus, filling a slowly changing or snowflaked dimension only requires a single method call per row as performs all of the required lookups, insertions, and assignment of surrogate keys. Similarly to dimensions, fact tables are each represented by a fact table object. Our latest addition to , Drawn Table Testing (DTT), simplifies testing ETL flows by making it easy to define both preconditions (i.e., the state of the database before the ETL flow is run) and postconditions (i.e., the expected state after the ETL flow has run) into a test. DTT can also be used to test ETL flows created in other ETL tools. also provides a set of commonly used functions for transforming rows, classes that help users parallelize their ETL flows using simple abstractions, and editor support for working with DTT. We present an evaluation that shows that provides high programmer productivity and that the created ETL flows have good run-time performance. Last, we present a case study from a company using in production and consider some of the lessons we learned during the development of as an open source framework.

The volume of GPS data collected from moving vehicles has increased significantly over the last years. We have gone from GPS data being collected every few minutes to data being collected every second. With large quantities of GPS data available it is possible to analyze the traffic on most of the road network without installing road-side equipment. A very important key performance indicator (KPI) in traffic planning is travel time. For this reason, this paper describes how travel time can be computed from GPS data. Of particular interest is how the travel time is affected by the weather. The work presented here is an extension of previous work on computing accurate travel time from GPS data. In this paper, the logical data model is explained in more details and the result section showing weather’s impact on travel time has been significantly extended with previously unpublished material.

Massive quantities of data are today collected from many sources. However, it is often labor-intensive to handle and integrate these data sources into a data warehouse. Further, the complexity is increased when specific requirements exist. One such new requirement, is the right to be forgottenwhere an organization upon request must delete all data about an individual. Another requirement is when facts are updated retrospectively. In this paper, we present the general framework SimpleETL which is currently used for Extract-Transform-Load (ETL) processing in a company with such requirements. SimpleETL automatically handles all database interactions such as creating fact tables, dimensions, and foreign keys. The framework also has features for handling version management of facts and implements four different methods for handling deleted facts. The framework enables, e.g., data scientists, to program complete and complex ETL solutions very efficiently with only few lines of code, which is demonstrated with a real-world example.

Extract-Transform-Load (ETL) processes are used for extracting data, transforming it and loading it into data warehouses (DWs). The dominating ETL tools use graphical user interfaces (GUIs) such that the developer “draws” the ETL flow by connecting steps/transformations with lines. This gives an easy overview, but can also be rather tedious and require much trivial work for simple things. We therefore challenge this approach and propose to do ETL programming by writing code. To make the programming easy, we present the Python-based framework pygrametl which offers commonly used functionality for ETL development. By using the framework, the developer can efficiently create effective ETL solutions from which the full power of programming can be exploited. In this chapter, we present our work on pygrametl and related activities. Further, we consider some of the lessons learned during the development of pygrametl as an open source framework.

Up to 50% of delay in traffic is due to non-reoccurring events such as traffic accidents. Accidents lead to delays, which can be costly for transport companies. Road authorities are also very interested in warning drivers about accidents, e.g., to reroute them. This paper presents a novel and efficient approach and system for uncovering effects from traffic accidents by dynamic integration of GPS, weather, and traffic-accident data. This integration makes it possible to explore and quantify how accidents affects traffic. Dynamic integration means that data is combined at query time as it becomes available. This is necessary, because data can be missing (weather station down) or late arriving (accident not officially reported by the police yet). Further, the integration can be parameterized by the user, e.g., distance to accident, which is important due to inaccuracy in reporting. We present the integrated data on a map and show the effectiveness of the integration by allowing users to interactively browse all accidents or pick a single accident to study it in very fine-grained details. Using information from 31 433 road accidents and 38 billion GPS records, we show that the proposed dynamic data integration scales so very large data sets.

The focus on reducing CO2 emissions from the transport sector is larger than ever. Increasingly stricter reductions on fuel consumption and emissions are being introduced by the EU, e.g., to reduce the air pollution in many larger cities. Large sets of high-frequent GPS data from vehicles already exist. However, fuel consumption data is still rarely collected even though it is possible to measure the fuel consumption with high accuracy, e.g., using an OBD-II device and a smartphone. This paper, presents a method for comparing fuel-consumption estimates using the SIDRA TRIP model with real fuel measures to determine if the fuel-consumption model is sufficiently accurate. The model is implemented using a 2D, a simple 3D, and a high-precision (H3D) road map of Denmark. The original 2D map is lifted to a 3D map using a Digital Elevation Model (DEM). Results show that introducing a 3D map improves the accuracy of fuel-consumption estimates with up to 40% on hilly roads. There is only very little improvement of the high-precision (H3D) map over the simple 3D map. The fuel consumption estimates are most accurate on flat terrain with average fuel estimates of up to 99% accuracy. The fuel estimates are most inaccurate uphill/downhill and when the vehicles accelerate at speeds above 50 km/h.

Making the transition from conventional combustion vehicles (CVs) to electric vehicles (EVs) requires the users to be comfortable with the limited range of EVs. We present a system named ELVIS that enables a direct comparison of energy/fuel consumption, CO2 emissions, and travel-time between CVs and EVs. By letting users enter their everyday driving destinations ELVIS estimates the fuel consumption and whether the users can replace their CV with an EV, optionally by charging the EV at certain stops for a number of minutes. In this demonstration the popular CV Citroën Cactus is compared to the popular EV Nissan Leaf. It is shown that for a typical scenario it is possible reduce CO _2 emissions by 28

GPS data is often used for computing travel time in road networks. In addition, GPS data is often map matched to find the routes driven by vehicles. Today GPS data is collected with different sampling periods, however, both the computed travel times and the routes found by map matching algorithms actual depends on the sampling period. This paper proposes a generic approach to study how travel time and map matched routes vary with the sampling period. Two types of map matching algorithms are used, point based where each position is handled individually, and trajectory based where positions from a vehicle is consider a data stream. A baseline is created using a real-world data set of 455 million positions from 368 vehicles collected with a sampling period of 1 second. This data set is downsampled to 8 data sets with sampling periods between 2 and 120 seconds. This downsampling enables an apple-to-apple comparison of travel time computation and route restoration for different sampling periods. The main conclusion is that travel times are reasonably accurate if the sampling period is 5 second or below for the point-based method and 20 seconds or below for the trajectory-based method. GPS data collected with 60 second is to inaccurate to be used for computing travel times. Trajectory-based map matching works best if the sampling period is 20 seconds or below.

Increasingly large volumes of vehicle trajectory data are becoming available. This data holds the potential to offer detailed insight into important aspects of vehicular transportation and road networks. This insight can in turn be utilized to enable a range of important services. Specifically, we demonstrate a system that is capable of leveraging very large collections of GPS trajectories for enabling interactive analyses of traffic in road intersections, which are often bottlenecks in road networks. These analyses are able to provide detailed insight into the time-varying functioning of intersections, and they offer a solid, data-driven foundation for improving the capacity of intersections and the overall road network. The system enables more cost-effective analyses than what is possible with traditional techniques. Demonstration participants will gain first-hand experience with interactive analyses on top of a database of some 40 billion GPS records capturing more than a billion km of driving.

Accurate estimating travel times in road networks is a complex task because travel times depends on factors such as the weather. In this paper, we present a generic model for integrating weather data with GPS data to improve the accuracy of the estimated travel times. First, we present a data model for storing and map-matching GPS data, and integrating this data with detailed weather data. The model is generic in the sense that it can be used anywhere GPS data and weather data is available. Next, we analyze the correlation between travel time and the weather classes dry, fog, rain, and snow along with winds impact on travel time. Using a data set of 1.6 billion GPS records collected from 10,560 vehicles, over a 5 year period from all of Denmark, we show that snow can increase the travel time up to 27 % and strong headwind can increase the travel time with up to 19 % (compared to dry calm weather). This clearly shows that accurate travel time estimation requires knowledge about the weather.

The combined battery capacity in electric vehicles (EVs) is considered an integral part of balancing a smart power grid in the future. In addition, EVs can reduce the usage of fossil fuels in the transport sector because EVs can be charged using electricity from renewable energy sources, such as wind turbines. To both enable a smart grid and the use of renewable energy, it is essential to know when and where an EV is plugged into the power grid and what battery capacity is available. In this paper, we present a generic spatio-temporal data-warehouse model for storing detailed information on all aspects of charging EVs, including integration with the electricity prices from a spot market. The proposed data warehouse is fully implemented and currently contains 2.5 years of charging data from 176 EVs. We describe the date warehouse model and the implementation including complex operations such as spatially identifying charging station usage patterns. Further, we give examples of novel analyses, e.g., how the free battery capacity in the fleet of EVs changes over the day and how users can save money by charging the EVs when the electricity price is the lowest.

En åbenlys fordel ved GPS er, at den gør det muligt at beregne køretider for det meste af vejnetværket, herunder de mest trafikerede veje. En væsentlig ulempe ved at benytte GPS er, at data kun modtages fra en forholdsvis lille del af køretøjerne sammenlignet med de andre teknologier. I denne artikel vil vi kort præsentere, hvordan vi håndterer meget store mængder af GPS data fra køretøjer til beregning af køretider. Vi fokuserer på beregning af køretider i kryds og på strækninger. GPS data er specielt interessante i disse sammenhænge, fordi man med såkaldte turdata kan følge det enkelte køretøj meget nøjagtigt, når det fx laver et venstresving i et kryds. Dette kræver, at GPS dataene er opsamlet med høj frekvens. Dette er gældende for langt hovedparten af de data, vi har, hvor den typiske frekvens er en måling pr. sekund. Grundet pladshensyn rummer artiklen ikke en sammenligning af køretider beregnet vha. GPS med køretider beregnet vha. fx Bluetooth eller spoler. Vi henviser i stedet til eksisterende arbejde (Andersen, Lahrmann, & Torp, 2011) (Borresen, Jensen, & Torp, 2016).

Reduction in greenhouse gas emissions from transportation attracts increasing interest from governments, fleet managers, and individual drivers. Eco-routing, which enables drivers to use eco-friendly routes, is a simple and effective approach to reducing emissions from transportation. We present EcoSky, a system that annotates edges of a road network with time dependent and uncertain eco-weights using GPS data and that supports different types of eco-routing. Basic eco-routing returns the most eco-friendly routes; skyline eco-routing takes into account not only fuel consumption but also travel time and distance when computing eco-routes; and personalized eco-routing considers each driver's past behavior and accordingly suggests different routes to different drivers.

We propose a system for calculating the personalized annual fuel consumption and CO2 emissions from transportation. The system, named CO2NNIE, estimates the fuel consumption on the fastest route between the frequent destinations of the user. The travel time and fuel consumption estimated are based on 3.8 billion GPS records from 16 thousand cars and 198 million records from 218 cars annotated with fuel consumption data, respectively. The fuel consumption estimates from the system are validated using fuel-pump data. We find that estimates have good accuracy, i.e., are generally within 10% of the actual fuel consumption (4.6% deviation on average). We conclude, that the system provides new detailed information on CO2 emissions and fuel consumption for any make and model.

Eco-routing is a simple yet effective approach to substantially reducing the environmental impact, e.g., fuel consumption and greenhouse gas (GHG) emissions, of vehicular transportation. Eco-routing relies on the ability to reliably quantify the environmental impact of vehicles as they travel in a spatial network. The procedure of quantifying such vehicular impact for road segments of a spatial network is called eco-weight assignment. EcoMark 2.0 proposes a general framework for eco-weight assignment to enable eco-routing. It studies the abilities of six instantaneous and five aggregated models to estimating vehicular environmental impact. In doing so, it utilizes travel information derived from GPS trajectories (i.e., velocities and accelerations) and actual fuel consumption data obtained from vehicles. The framework covers analyses of actual fuel consumption, impact model calibration, and experiments for assessing the utility of the impact models in assigning eco-weights. The application of EcoMark 2.0 indicates that the instantaneous model EMIT and the aggregated model SIDRA-Running are suitable for assigning eco-weights under varying circumstances. In contrast, other instantaneous models should not be used for assigning eco-weights, and other aggregated models can be used for assigning eco-weights under certain circumstances.

An electric vehicle (EV) is an interesting vehicle type because it has the potential of reducing the dependence on fossil fuels by using electricity from, e.g., wind turbines. A significant disadvantage of EVs is a very limited range, typically less than 200 km. This paper compares EVs to conventional vehicles (CVs) for private transportation using two very large data sets. The EV data set is collected from 164 vehicles (126 million rows) and the CV data set from 447 vehicles (206 million rows). Both data sets are collected in Denmark throughout 2012, with a logging frequency of 1 Hz. GPS data is collected from both vehicle types. In addition, EVs also log the actual energy consumption every second using the vehicle's CAN bus. By comparing the two data sets, we observe that EVs are significantly slower on motorways, faster in cities, and drive shorter distances compared to CVs. Further, we study the effects of temperature, wind direction, wind speed, and road inclination. We conclude that the energy consumption (and range) of an EV is very sensitive to head wind, low temperatures, and steep road inclinations.