Efficient approach for view materialisation in a data warehouse by prioritising data cubes

Efficient approach for view materialisation in a data warehouse by prioritising data cubes

For access to this article, please select a purchase option:

Buy article PDF
(plus tax if applicable)
Buy Knowledge Pack
10 articles for $120.00
(plus taxes if applicable)

IET members benefit from discounts to all IET publications and free access to E&T Magazine. If you are an IET member, log in to your account and the discounts will automatically be applied.

Learn more about IET membership 

Recommend Title Publication to library

You must fill out fields marked with: *

Librarian details
Your details
Why are you recommending this title?
Select reason:
IET Software — Recommend this title to your library

Thank you

Your recommendation has been sent to your librarian.

Selecting an appropriate set of views for materialisation is an important problem in a data warehouse, and is referred to as the view selection problem. The existing state-of-the-art cost models select a set of views based on parameters, such as query frequency, view size, view update frequency, and view update costs. The existing methods do not consider query priority as a parameter for selecting views that can lead to shorter query processing times. Thus, in this paper, 'priority’ is selected as a new selection parameter. Priority values are assigned to each query per user requirements, as well as using query type, user's level, and department preference in an organisation. As analytical queries require aggregated data cubes, priority values are assigned to each data cube based on priority value of the queries accessing them. Finally, a modified cost model is designed that integrates cube priority along with other selection parameters. The authors’ proposed model uses the particle swarm optimisation algorithm for selecting a set of prioritised cubes by minimising the total query running cost under storage constraints. The experimental results shows that the proposed cost model leads to better cube selection, and consequently, shorter query running times.


    1. 1)
      • 1. Inmon, W.H., Kelley, C.: ‘Rdb-VMS: developing a data warehouse’ (John Wiley & Sons, Inc., New York, NY, USA, 1993).
    2. 2)
      • 2. Yang, J., Karlapalem, K., Li, Q.: ‘Algorithms for materialized view design in data warehousing environment’, Proc. Very Large Data Bases (VLDB), San Francisco, CA, USA, 1997, pp. 136145.
    3. 3)
      • 3. Lee, M., Hammer, J.: ‘Speeding up materialized view selection in data warehouses using a randomized algorithm’, Int. J. Coop. Inf. Syst., 2001, 10, (3), pp. 327353.
    4. 4)
      • 4. Lin, W.Y., Kuo, I.C.: ‘A genetic selection algorithm for olap data cubes’, Knowl. Inf. Syst., 2004, 6, (1), pp. 83102.
    5. 5)
      • 5. Gupta, H., Mumick, I.S.: ‘Selection of views to materialize in a data warehouse’, IEEE Trans. Knowl. Data Eng., 2005, 17, (1), pp. 2443.
    6. 6)
      • 6. Harinarayan, V., Rajaraman, A., Ullman, J.D.: ‘Implementing data cubes efficiently’, ACM SIGMOD Record, 1996, 25, (2), pp. 205216.
    7. 7)
      • 7. Hung, E., Cheung, D.W., Kao, B.: ‘Optimization in data cube system design’, J. Intell. Inf. Syst., 2004, 23, (1), pp. 1745.
    8. 8)
      • 8. Zhang, C., Yao, X., Yang, J.: ‘An evolutionary approach to materialized views selection in a data warehouse environment’, IEEE Trans. Syst., Man, Cybern., Part C (Appl. Rev.), 2001, 31, (3), pp. 282294.
    9. 9)
      • 9. Yu, J.X., Yao, X., Choi, C.H., et al: ‘Materialized view selection as constrained evolutionary optimization’, IEEE Trans. Syst., Man Cybern., Part C (Appl. Rev.), 2003, 33, (4), pp. 458467.
    10. 10)
      • 10. Gou, Y.L., Gou, G., Yu, J.X., et al: ‘A/sup*/search: an efficient and flexible approach to materialized view selection’, IEEE Trans. Syst., Man Cybern., Part C (Appl. Rev.), 2006, 36, (3), pp. 411425.
    11. 11)
      • 11. Li, J., Talebi, Z.A., Chirkova, R., et al: ‘A formal model for the problem of view selection for aggregate queries’. East European Conf. Advances in Databases and Information Systems, Berlin, 2005, pp. 125138.
    12. 12)
      • 12. Huang, R., Chirkova, R., Fathi, Y.: ‘Deterministic view selection for data-analysis queries: properties and algorithms’. Proc. East European Conf. Advances in Databases and Information Systems, Berlin, 2012, pp. 195208.
    13. 13)
      • 13. Gosain, A., Heena, : ‘Materialized cube selection using particle swarm optimization algorithm’, Procedia Comput. Sci., 2016, 79, pp. 27.
    14. 14)
      • 14. Loureiro, J., Belo, O.: ‘A discrete particle swarm algorithm for olap data cube selection’. Proc. ICEIS, Paphos, Cyprus, 2006, pp. 4662[A5] .
    15. 15)
      • 15. Sauter, V.L.: ‘Decision support systems for business intelligence’ (John Wiley & Sons, Hoboken, NJ, USA, 2014).
    16. 16)
      • 16. Priorities’, 2009. Available at:, accessed November 2009.
    17. 17)
      • 17. Hammer, J., Schneider, M., Sellis, T.: ‘Data warehousing at the crossroads’. Proc. Actas del Dagstuhl Perspectives Workshop, Dagstuhl, Germany, 2004.
    18. 18)
      • 18. Lowering the priority of a postgresql query’, 2005. Available at, accessed November 2005.
    19. 19)
      • 19. Silvers, F.: ‘Building and maintaining a data warehouse’ (CRC Press, Boca Raton, FL, USA, 2008).
    20. 20)
      • 20. Browning, D., Mundy, J.: ‘Data warehouse design considerations’ (Microsoft Corp. MSDN Library, 2001).
    21. 21)
      • 21. Kimball, R., Caserta, J.: ‘The data warehouse ETL toolkit: practical techniques for extracting, cleaning, conforming, and delivering data’ (Wiley Publishing Inc., New York, NY, USA, 2004).
    22. 22)
      • 22. Vaisman, A.: ‘Data quality-based requirements elicitation for decision support systems’. Data warehouses and OLAP: concepts, architectures, and solutions, IGI Global, 2007.
    23. 23)
      • 23. Gosain, A., Madaan, H.: ‘Query prioritization for view selection’. Proc. ICACNI, 2016, pp. 403410.
    24. 24)
      • 24. Saaty, T.L.: ‘Decision making with the analytic hierarchy process’, Int. J. Serv. Sci., 2008, 1, (1), pp. 8398.
    25. 25)
      • 25. Schniederjans, M.J., Hamaker, J.L., Schniederjans, A.M.: ‘Information technology investment: decision-making methodology’ (World Scientific, Singapore, 2010).
    26. 26)
      • 26. Eberhart, R.C., Kennedy, J.: ‘A new optimizer using particle swarm theory’. Proc. Int. Symp. on Micro Machine and Human Science, Nagoya, Japan, vol. 1, 1995, pp. 3943.
    27. 27)
      • 27. Hassan, R., Cohanim, B., DeWeck, O., et al: ‘A comparison of particle swarm optimization and the genetic algorithm’. Proc. AIAA Multidisciplinary Design Optimization Specialist Conf., Austin, TX, USA, 2005, pp. 1821.
    28. 28)
      • 28. Marini, F., Walczak, B.: ‘Particle swarm optimization (PSO). A tutorial’, Chemometr. Intell. Lab. Syst., 2015, 149, pp. 153165.
    29. 29)
      • 29. Kennedy, J., Eberhart, R.C.: ‘A discrete binary version of the particle swarm algorithm’, Proc. Comput. Cybern. Simul., 1997, 5, pp. 41044108.
    30. 30)
      • 30. Nezamabadi-pour, H., Rostami-Shahrbabaki, M., Maghfoori-Farsangi, M.: ‘Binary particle swarm optimization: challenges and new solutions’, CSI Computer Sci. Eng., 2008, 6, (1), pp. 2132.
    31. 31)
      • 31. Microsoft contoso BI demo dataset for retail industry’, 2018. Available at, accessed March 2018.
    32. 32)
      • 32. Wide world importers documentation, 2018. Available at, accessed March 2018.
    33. 33)
      • 33. Kotidis, Y., Roussopoulos, N.: ‘Dynamat: a dynamic view management system for data warehouses’, ACM SIGMOD Record, 1999, 28, pp. 371382.
    34. 34)
      • 34. Albrecht, J., Bauer, A., Redert, M.: ‘Supporting hot spots with materialized views’. Proc. DaWaK, Berlin, Heidelberg, 2000, pp. 4756.

Related content

This is a required field
Please enter a valid email address