Skip to main content
Springer Nature Link
Log in

Reconciling tuple and attribute timestamping for temporal data warehouses

  • Regular Paper
  • Published:
The VLDB Journal Aims and scope Submit manuscript

Abstract

Data Warehouses (DWs) requir e storing and analyzing time-varying data to reflect changes that occur in the business world. Solutions to this problem build on the field of temporal databases and adopt the tuple-timestamping approach, where tuples are timestamped with their validity interval. Alternatively, the attribute timestamping approach represents a time-varying attribute with a list of its evolving values and the time when these changes occurred. The SQL:2011 standard has favored the tuple timestamping approach, which has also been used for temporal DWs, despite that it yields very long and complex SQL queries. This paper aims at reconciling both approaches and advocates for a database that can support both models, in a way such that they complement each other. We show that, to efficiently operate with tuple timestamping, we need appropriate time data types and operations for representing and manipulating temporal elements. We also show that many applications are more naturally and efficiently modeled and implemented using attribute timestamping. To prove the feasibility of our proposal, we implemented a portion of the TPC-DS benchmark using three alternative approaches, two of them based on classic tuple timestamping (including the well-known slowly-changing dimensions model), and a third one, based on our proposal. For the latter, we used MobilityDB, a novel spatiotemporal database built on top of PostgreSQL, that integrates both models in a natural way. Experiments showed that our proposal outperformed the other two ones, in many cases, by orders of magnitude.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Log in via an institution

Subscribe and save

Springer+ Basic
$34.99 /Month
  • Get 10 units per month
  • Download Article/Chapter or eBook
  • 1 Unit = 1 Article or 1 Chapter
  • Cancel anytime
Subscribe now

Buy Now

Price excludes VAT (USA)
Tax calculation will be finalised during checkout.

Instant access to the full article PDF.

Institutional subscriptions

Fig. 1
Fig. 2
Fig. 3
Fig. 4
Fig. 5
Fig. 6
Fig. 7
Fig. 8
Fig. 9
Fig. 10
Fig. 11
Fig. 12
Fig. 13
Fig. 14
Fig. 15
Fig. 16
Fig. 17

Similar content being viewed by others

Notes

  1. https://www.postgresql.org.

  2. https://postgis.net/.

  3. MobilityDB, the database we use later to implement the abstract model, also includes geometry and geography as base types but they are not considered in this paper.

  4. https://mobilitydb.com/.

  5. The span and span set types in MobilityDB correspond to the range and multirange types in PostgreSQL, but they have a more efficient implementation.

  6. https://libmeos.org/documentation/datastructures.

  7. Currently, MobilityDB provides temporal types only at the timestamptz granularity.

  8. MobilityDB also provides the temporal types tgeompoint and tgeogpoint, which are based on the PostGIS types geometry and geography restricted to 2D and 3D points.

  9. https://libmeos.org/documentation/aggregation/.

  10. Kimball also proposed SCD Types 4 through 7, but we omit them since they are particular cases not related to this paper.

  11. https://github.com/MobilityDB/MobilityDB-TPCDS.

  12. https://www.citusdata.com/.

References

  1. Ahmed, W., Zimányi, E., Vaisman, A.A., Wrembel, R.: A temporal multidimensional model and OLAP operators. Int. J. Data Warehous. Min. 16(4), 112–143 (2020)

    Article  Google Scholar 

  2. Bakli, M., Sakr, M., Zimányi, E.: Distributed mobility data management in MobilityDB. In: Proceedings of the 21st IEEE international conference on mobile data management, MDM 2020, pp. 238–239. IEEE Computer Society Press (2020)

  3. Bakli, M., Sakr, M., Zimányi, E.: Distributed spatiotemporal trajectory query processing in SQL. In: Proceedings of the 28th international conference on advances in geographic information systems, SIGSPATIAL’20. ACM (2020)

  4. Bliujute, R., Saltenis, S., Slivinskas, G., Jensen, C.S.: Systematic change management in dimensional data warehousing. Technical Report TR-23, Time Center (1998)

  5. Böhlen, M.H., Dignös, A., Gamper, J., Jensen, C.S.: Temporal data management: an overview. In: Proceedings of the 7th european summer school on business intelligence and big data, eBISS 2017, 324, 51–83 (2017)

  6. Böhlen, M.H., Dignös, A., Gamper, J., Jensen, C.S.: Database technology for processing temporal data. In: Proceedings of the 25th Int. Symp. on Temporal Representation and Reasoning, TIME 2018 (2018)

  7. Böhlen, M., Gamper, J., Jensen, C.S.: Towards general temporal aggregation. In: Proceedings of the 25th british national conference on databases, BNCOD08, pp. 257–269 (2008)

  8. Böhlen, M.H., Snodgrass, R.T., Soo, M.D.: Coalescing in temporal databases. In: Proceedings of 22th international conference on very large data bases, VLDB’96, pp. 180–191. Morgan Kaufmann (1996)

  9. Chen, C.X., Kong, J., Zaniolo, C.: Design and implementation of a temporal extension of SQL. In: Proceedings of the 19th international conference on data engineering, 2003, pp. 689–691. IEEE Computer Society (2003)

  10. Clifford, J., Tansel, A.U.: On an algebra for historical relational databases: two views. In: Proceedings of the 1985 ACM SIGMOD international conference on management of data, pp. 247–265. ACM Press (1985)

  11. Dignös, A., Böhlen, M.H., Gamper, J., Jensen, C.S.: Extending the kernel of a relational DBMS with comprehensive support for sequenced temporal queries. ACM Trans. Database Syst. 41(4), 26:1-26:46 (2016)

    Article  MathSciNet  MATH  Google Scholar 

  12. Gadia, S.K.: A homogeneous relational model and query languages for temporal databases. ACM Trans. Database Syst. 13(4), 418–448 (1988)

    Article  MathSciNet  MATH  Google Scholar 

  13. Gamper, J., Ceccarello, M., Dignös, A.: What’s new in temporal databases? In: Proceedings of the 26th European Conference on Advances in Databases and Information Systems, ADBIS 2022, LNCS 13389, pp. 45–58. Springer (2022)

  14. Garani, G., Adam, G.K., Ventzas, D.: Temporal data warehouse logical modelling. Int. J. Data Min. Model. Manag. 8(2), 144–159 (2016)

    Google Scholar 

  15. Garani, G., Helmer, S.: Integrating star and snowflake schemas in data warehouses. Int. J. Data Warehous. Min. 8(4), 22–40 (2012)

    Article  MATH  Google Scholar 

  16. Güting, R.H., Böhlen, M.H., Erwig, M., Jensen, C.S., Lorentzos, N.A., Schneider, M., Vazirgiannis, M.: A foundation for representing and quering moving objects. ACM Trans. Database Syst. 25(1), 1–42 (2000)

    Article  Google Scholar 

  17. Hümmer, W., Lehner, W., Bauer, A., Schlesinger, L.: A decathlon in multidimensional modeling: Open issues and some solutions. In: Proceedings of the 4th international conference on data warehousing and knowledge discovery, DaWaK, LNCS 2454, pp. 275–285. Springer (2002)

  18. Hurtado, C.A., Mendelzon, A., Vaisman, A.A.: Maintaining data cubes under dimension updates. In: Proceedings of the 15th international conference on data engineering, ICDE’99, pp. 346–355 (1999)

  19. Jensen, C.S., Soo, M.D., Snodgrass, R.T.: Unifying temporal data models via a conceptual model. Inf. Syst. 19(7), 513–547 (1994)

    Article  MATH  Google Scholar 

  20. Khatri, V., Ram, S., Snodgrass, R.T., Terenziani, P.: Capturing telic/atelic temporal data semantics: generalizing conventional conceptual models. IEEE Trans. Knowl. Data Eng. 26(3), 528–548 (2014)

    Article  MATH  Google Scholar 

  21. Kimball, R., Ross, M.: The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd edn. John Wiley & Sons (2013)

    MATH  Google Scholar 

  22. Kulkarni, K., Michels, J.-E.: Temporal features in SQL:2011. SIGMOD Record 41(3), 34–43 (2012)

    Article  MATH  Google Scholar 

  23. Lu, W., Zhao, Z., Wang, X., Li, H., Zhang, Z., Shui, Z., Ye, S., Pan, A., Du, X.: A lightweight and efficient temporal database management system in TDSQL. Proc. VLDB Endow. 12(12), 2035–2046 (2019)

    Article  Google Scholar 

  24. Mahlknecht, G., Dignös, A., Kozmina, N.: Modeling and querying facts with period timestamps in data warehouses. Int. J. Appl. Math. Comput. Sci. 29(1), 31–49 (2019)

    Article  MATH  Google Scholar 

  25. Malinowski, E., Zimányi, E.: A conceptual model for temporal data warehouses and its transformation to the ER and the object-relational models. Data & Knowl. Eng. 64(1), 101–133 (2008)

    Article  MATH  Google Scholar 

  26. Nambiar, R.O., Poess, M.: The making of TPC-DS. In: Proceedings of the 32nd international conference on very large data bases, VLDB, pp. 1049–1058. ACM (2006)

  27. Paredaens, J., De Bra, P., Gyssens, M., Van Gucht, D.: The nested relational database model. In: The structure of the relational database model, pp. 177–201. Springer (1989)

  28. Snodgrass, R.T. (ed): The TSQL2 Temporal Query Language. Kluwer Academic (1995)

  29. Snodgrass, R.T.: Developing Time-Oriented Database Applications in SQL. Morgan Kaufmann (2000)

    MATH  Google Scholar 

  30. Snodgrass, R.T., Ahn, I.: A taxonomy of time databases. ACM SIGMOD Rec. 14, 236–246 (1985)

    Article  MATH  Google Scholar 

  31. Tansel, A.U., Clifford, J., Gadia, S., Jajodia, S., Segev, A., Snodgrass, R.T.: Temporal Databases: Theory, Design, and Implementation. Benjamin Cummings (1993)

    Google Scholar 

  32. Toman, D.: Point vs. interval-based query languages for temporal databases. In: Proceedings of ACM PODS, pp. 58–67. ACM Press (1996)

  33. Toman, D.: Point-based temporal extension of temporal SQL. In: Proceedings of the 5th international conference on deductive and object-oriented databases, DOOD’97, LNCS 1341, pp. 103–121. Springer (1997)

  34. Tsikoudis, N., Shrira, L.: RID: deduplicating snapshot computations. In: Proc. of the 2020 Int. Conference on Management of Data, SIGMOD 2020, pp. 2087–2101. ACM (2020)

  35. Tsikoudis, N., Shrira, L., Cohen, S.: RQL: retrospective computations over snapshot sets. In: Proceedings of the 21st international conference on extending database technology, EDBT 2018, pp. 600–611 (2018)

  36. Vaisman, A.A., Zimányi, E.: Mobility data warehouses. ISPRS Int. J. Geo-Inf. 8(4), 170 (2019)

    Article  MATH  Google Scholar 

  37. Vaisman, A.A., Zimányi, E.: Data Warehouse Systems: Design and Implementation, 2nd edn. Springer (2022)

    Book  MATH  Google Scholar 

  38. Zhou, X., Wang, F., Zaniolo, C.: Efficient temporal coalescing query support in relational database systems. In: Proceedings of the 17th conference on database and expert systems applications, DEXA, pp. 676–686 (2006)

  39. Zimányi, E.: Query evaluation in probabilistic relational databases. Theoret. Comput. Sci. 171(1–2), 179–219 (1997)

    Article  MathSciNet  MATH  Google Scholar 

  40. Zimányi, E.: Temporal aggregates and temporal universal quantifiers in standard SQL. SIGMOD Record 32(2), 16–21 (2006)

    Article  MATH  Google Scholar 

  41. Zimányi, E., Sakr, M., Lesuisse, A.: MobilityDB: a mobility database based on PostgreSQL and PostGIS. ACM Trans. Database Syst. 45(4), 19:1-19:42 (2020)

    Article  MathSciNet  MATH  Google Scholar 

Download references

Acknowledgements

A. Vaisman and L. Gómez were partially supported by Project PICT 2017-1054, from the Argentinian Scientific Agency. The authors also thank Maxime Schoemans for his invaluable help in the analysis and optimization of the queries studied in this paper.

Author information

Authors and Affiliations

  1. Université libre de Bruxelles, Brussels, Belgium

    Waqas Ahmed & Esteban Zimányi

  2. Instituto Tecnológico de Buenos Aires, Buenos Aires, Argentina

    Leticia Gómez & Alejandro Vaisman

Authors
  1. Waqas Ahmed
    View author publications

    You can also search for this author inPubMed Google Scholar

  2. Leticia Gómez
    View author publications

    You can also search for this author inPubMed Google Scholar

  3. Alejandro Vaisman
    View author publications

    You can also search for this author inPubMed Google Scholar

  4. Esteban Zimányi
    View author publications

    You can also search for this author inPubMed Google Scholar

Corresponding author

Correspondence to Alejandro Vaisman.

Ethics declarations

Conflict of interest

The authors declare no Conflict of interest.

Additional information

Publisher's Note

Springer Nature remains neutral with regard to jurisdictional claims in published maps and institutional affiliations.

Rights and permissions

Springer Nature or its licensor (e.g. a society or other partner) holds exclusive rights to this article under a publishing agreement with the author(s) or other rightsholder(s); author self-archiving of the accepted manuscript version of this article is solely governed by the terms of such publishing agreement and applicable law.

Reprints and permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Ahmed, W., Gómez, L., Vaisman, A. et al. Reconciling tuple and attribute timestamping for temporal data warehouses. The VLDB Journal 34, 11 (2025). https://doi.org/10.1007/s00778-024-00889-2

Download citation

  • Received:

  • Accepted:

  • Published:

  • DOI: https://doi.org/10.1007/s00778-024-00889-2

Keywords