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.

References

1. 1)
  - 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).
2. 2)
  - 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. 458–467.
3. 3)
  - 1. Inmon, W.H., Kelley, C.: ‘Rdb-VMS: developing a data warehouse’ (John Wiley & Sons, Inc., New York, NY, USA, 1993).
4. 4)
  - 7. Hung, E., Cheung, D.W., Kao, B.: ‘Optimization in data cube system design’, J. Intell. Inf. Syst., 2004, 23, (1), pp. 17–45.
5. 5)
  - 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. 282–294.
6. 6)
  - 6. Harinarayan, V., Rajaraman, A., Ullman, J.D.: ‘Implementing data cubes efficiently’, ACM SIGMOD Record, 1996, 25, (2), pp. 205–216.
7. 7)
  - 22. Vaisman, A.: ‘Data quality-based requirements elicitation for decision support systems’. Data warehouses and OLAP: concepts, architectures, and solutions, IGI Global, 2007.
8. 8)
  - 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. 411–425.
9. 9)
  - 18. ‘Lowering the priority of a postgresql query’, 2005. Available at https://www.bignerdranch.com/blog/lowering-the-priority-of-a-postgresql-query/, accessed November 2005.
10. 10)
  - 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. 136–145.
11. 11)
  - 20. Browning, D., Mundy, J.: ‘Data warehouse design considerations’ (Microsoft Corp. MSDN Library, 2001).
12. 12)
  - 13. Gosain, A., Heena, : ‘Materialized cube selection using particle swarm optimization algorithm’, Procedia Comput. Sci., 2016, 79, pp. 2–7.
13. 13)
  - 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. 21–32.
14. 14)
  - 31. ‘Microsoft contoso BI demo dataset for retail industry’, 2018. Available at https://www.microsoft.com/en-in/download/details.aspx?id=18279, accessed March 2018.
15. 15)
  - 5. Gupta, H., Mumick, I.S.: ‘Selection of views to materialize in a data warehouse’, IEEE Trans. Knowl. Data Eng., 2005, 17, (1), pp. 24–43.
16. 16)
  - 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. 327–353.
17. 17)
  - 32. Wide world importers documentation, 2018. Available at https://docs.microsoft.com/en-us/sql/sample/world-wide-importers/wide-world-importers-documentation, accessed March 2018.
18. 18)
  - 23. Gosain, A., Madaan, H.: ‘Query prioritization for view selection’. Proc. ICACNI, 2016, pp. 403–410.
19. 19)
  - 19. Silvers, F.: ‘Building and maintaining a data warehouse’ (CRC Press, Boca Raton, FL, USA, 2008).
20. 20)
  - 33. Kotidis, Y., Roussopoulos, N.: ‘Dynamat: a dynamic view management system for data warehouses’, ACM SIGMOD Record, 1999, 28, pp. 371–382.
21. 21)
  - 24. Saaty, T.L.: ‘Decision making with the analytic hierarchy process’, Int. J. Serv. Sci., 2008, 1, (1), pp. 83–98.
22. 22)
  - 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. 125–138.
23. 23)
  - 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. 39–43.
24. 24)
  - 29. Kennedy, J., Eberhart, R.C.: ‘A discrete binary version of the particle swarm algorithm’, Proc. Comput. Cybern. Simul., 1997, 5, pp. 4104–4108.
25. 25)
  - 28. Marini, F., Walczak, B.: ‘Particle swarm optimization (PSO). A tutorial’, Chemometr. Intell. Lab. Syst., 2015, 149, pp. 153–165.
26. 26)
  - 25. Schniederjans, M.J., Hamaker, J.L., Schniederjans, A.M.: ‘Information technology investment: decision-making methodology’ (World Scientific, Singapore, 2010).
27. 27)
  - 14. Loureiro, J., Belo, O.: ‘A discrete particle swarm algorithm for olap data cube selection’. Proc. ICEIS, Paphos, Cyprus, 2006, pp. 46–62[A5] .
28. 28)
  - 34. Albrecht, J., Bauer, A., Redert, M.: ‘Supporting hot spots with materialized views’. Proc. DaWaK, Berlin, Heidelberg, 2000, pp. 47–56.
29. 29)
  - 16. ‘Priorities’, 2009. Available at: https://wiki.postgresql.org/wiki/Priorities, accessed November 2009.
30. 30)
  - 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. 18–21.
31. 31)
  - 15. Sauter, V.L.: ‘Decision support systems for business intelligence’ (John Wiley & Sons, Hoboken, NJ, USA, 2014).
32. 32)
  - 17. Hammer, J., Schneider, M., Sellis, T.: ‘Data warehousing at the crossroads’. Proc. Actas del Dagstuhl Perspectives Workshop, Dagstuhl, Germany, 2004.
33. 33)
  - 4. Lin, W.Y., Kuo, I.C.: ‘A genetic selection algorithm for olap data cubes’, Knowl. Inf. Syst., 2004, 6, (1), pp. 83–102.
34. 34)
  - 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. 195–208.

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

References

Related content