Abstract
The star schema model has been widely used as the facto DW storage organization on relational database management systems (RDBMS). The physical division in normalized fact tables (with metrics) and denormalized dimension tables allows a trade-off between performance and storage space while, at the same time offering a simple business understanding of the overall model as a set of metrics (facts) and attributes for business analysis (dimensions). However, the underlying premises of such trade-off between performance and storage have changed. Nowadays, storage capacity increased significantly at affordable prices (below 50$/terabyte) with improved transfer rates, and faster random access times particularly with modern SSD disks. In this paper we evaluate if the underlying premises of the star schema model storage organization still upholds. We propose an alternative storage organization (called ONE) that physically stores the whole star schema into a single relation, providing a predictable and scalable alternative to the star schema model. We use the TPC-H benchmark to evaluate ONE and the star schema model, assessing both the required storage size and query execution time.
This is a preview of subscription content, log in via an institution to check access.
Access this chapter
Tax calculation will be finalised at checkout
Purchases are for personal use only
Preview
Unable to display preview. Download preview PDF.
References
Pavlo, A., et al.: A comparison of approaches to large-scale data analysis. In: Proceedings of the 35th SIGMOD International Conference on Management of Data, pp. 165–178 (2009)
Patel, J.M., Carey, M.J., Vernon, M.K.: Accurate modeling of the hybrid hash join algorithm. In: ACM SIGMETRICS Performance Evaluation Review, NY, USA (1994)
DeWitt, D.J., Katz, R.H., Olken, F., Shapiro, L.D., Stonebraker, M.R., Wood, D.A.: Implementation techniques for main memory database systems. In: ACM SIGMOD Record, New York, NY, USA, pp. 1–8 (1984)
Harris, E.P., Ramamohanarao, K.: Join algorithm costs revisited. The VLDB Journal — The International Journal on Very Large Data Bases 5, 064–084 (1996)
Johnson, T.: Performance Measurements of Compressed Bitmap Indices. In: Proceedings of the 25th International Conference on Very Large Data Bases, pp. 278–289 (1999)
Zhou, J., Larson, P.-A., Goldstein, J., Ding, L.: Dynamic Materialized Views. In: International Conference on Data Engineering, Los Alamitos, CA, USA, pp. 526–535 (2007)
Costa, J.P., Furtado, P.: Time-Stratified Sampling for Approximate Answers to Aggregate Queries. In: International Conference on Database Systems for Advanced Applications, Los Alamitos, CA, USA, p. 215 (2003)
Stonebraker, M., et al.: C-store: a column-oriented DBMS. In: Proceedings of the 31st International Conference on Very Large Data Bases, pp. 553–564 (2005)
Zhang, Y., Hu, W., Wang, S.: MOSS-DB: a hardware-aware OLAP database. In: Proc. 11th International Conference on Web-Age Information Management, pp. 582–594 (2010)
Yma, P.: A Framework for Systematic Database Denormalization. Global Journal of Computer Science and Technology 9(4) (August 2009)
Sanders, G.L.: Denormalization Effects on Performance of RDBMS. In: Proceedings of the 34th Hawaii International Conference on System Sciences (2001)
Zaker, M., Phon-Amnuaisuk, S., Haw, S.-C.: Optimizing the data warehouse design by hierarchical denormalizing. In: Proc. 8th Conference on Applied Computer Science (2008)
O’Neil, P., O’Neil, E., Chen, X., Revilak, S.: The Star Schema Benchmark and Augmented Fact Table Indexing. In: Nambiar, R., Poess, M. (eds.) TPCTC 2009. LNCS, vol. 5895, pp. 237–252. Springer, Heidelberg (2009)
Kimball, R., Ross, M., Thornthwaite, W., Mundy, J., Becker, B.: The Data Warehouse Lifecycle Toolkit, 2nd edn. Wiley Publishing, Chichester (2008)
“PostgreSQL”, http://www.postgresql.org/
“TPC-H Benchmark”, http://www.tpc.org/tpch/
Author information
Authors and Affiliations
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2011 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Costa, J.P., Cecílio, J., Martins, P., Furtado, P. (2011). ONE: A Predictable and Scalable DW Model. In: Cuzzocrea, A., Dayal, U. (eds) Data Warehousing and Knowledge Discovery. DaWaK 2011. Lecture Notes in Computer Science, vol 6862. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-642-23544-3_1
Download citation
DOI: https://doi.org/10.1007/978-3-642-23544-3_1
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-642-23543-6
Online ISBN: 978-3-642-23544-3
eBook Packages: Computer ScienceComputer Science (R0)