Skip to main content
SpringerLink
Log in

Requirements-driven data warehouse design based on enhanced pivot tables

Requirements Engineering volume 26, pages 43–65 (2021)Cite this article

Abstract

The design of data warehouses (DWs) is based on both their data sources and users’ requirements. The more closely the DW multidimensional schema reflects the stakeholders’ needs, the more effectively they will make use of the DW content for their OLAP analyses. Thus, considerable attention has been given in the literature to DW requirements analysis, including requirements elicitation, specification and validation. Unfortunately, traditional approaches are based on complex formalisms that cannot be used with decision makers who have no previous experience with DWs and OLAP. This forces a sharp separation between elicitation and specification. To cope with this problem, we propose a new requirements analysis process where pivot tables, a well-known representation for multidimensional data often used by decision makers, are enhanced to be used both for elicitation and as a specification formalism. A pivot table is a two-dimensional spreadsheet that supports the analyses of multidimensional data by nesting several dimensions on the x- or y-axis and displaying data on multiple pages. The requirements analysis process we propose is iterative and relies on both unstructured and structured interviews; particular attention is given to enable the design of irregular multidimensional schemata, which are often present in real-world DWs but can hardly be understood by unskilled users. Finally, we validate our proposal using a real case study in the biodiversity domain.

This is a preview of subscription content, access via your institution.

Access options

Buy single article

Instant access to the full article PDF.

USD 39.95

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

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

Notes

  1. http://aims.fao.org/fr/agrovoc, https://www.sciencedirect.com/science/article/pii/S1574954107000362.

References

  1. Arnold D, Corriveau J, Shi W (2010) Modeling and validating requirements using executable contracts and scenarios. In: Proceedings eighth ACIS international conference on software engineering research, management and applications, pp 311–320

  2. Benker T, Jürck C (2012) A case study on model-driven data warehouse development. Proc DaWaK 2012:54–64

    Google Scholar 

  3. Bimonte S, Edoh-Alove E, Nazih H, Kang M, Rizzi S (2013) ProtOLAP: rapid OLAP prototyping with on-demand data supply. In: Proceedings of the sixteenth international workshop on data warehousing and OLAP, ACM, pp 61–66

  4. Bimonte S, Sakka A, Sautot L (2018) A new methodology for elicitation of datawarehouse requirements based on the pivot table formalism. In: Proceedings of 14eme edition de la conference EDA, pp 263–272

  5. Bimonte S, Rizzi S, Sautot L, Fontaine B (2019) Volunteered multidimensional design to the test: The farmland bio-diversity VGI4Bio project’s experiment. In: Proceedings of the 21st international workshop on design, optimization, languages and analytical processing of big data

  6. Bonifati A, Cattaneo F, Ceri S, Fuggetta A, Paraboschi S (2001) Designing data marts for data warehouses. ACM Trans Softw Eng Methodol 10(4):452–483

    Article  Google Scholar 

  7. Boulil K, Bimonte S, Pinet F (2015) Conceptual model for spatial data cubes: a UML profile and its automatic implementation. Comput Stand Interfaces 38:113–132

    Article  Google Scholar 

  8. Bresciani P, Perini A, Giorgini P, Giunchiglia F, Mylopoulos J (2004) Tropos: an agent-oriented software development methodology. Auton Agent Multi Agent Syst 8(3):203–236

    Article  Google Scholar 

  9. Bruckner R, List B, Schiefer J (2001) Developing requirements for data warehouse systems with use cases. In: Proceedings 7th Americas conference on information systems, pp 329–335

  10. Chen L, Soliman K, Mao E, Frolick M (2000) Measuring user satisfaction with data warehouses: an exploratory study. Inf Manag 37(3):103–110

    Article  Google Scholar 

  11. Chouder ML, Rizzi S, Chalal R (2019) EXODuS: exploratory OLAP over document stores. Inf Syst 79:44–57

    Article  Google Scholar 

  12. Di Tria F, Lefons E, Tangorra F (2012) Hybrid methodology for data warehouse conceptual design by UML schemas. Inf Softw Technol 54(4):360–379

    Article  Google Scholar 

  13. Diamantini C, Genga L, Potena D, Storti E (2014) Collaborative building of an ontology of key performance indicators. In: Proceedings conference on the move to meaningful internet systems, pp 148–165

  14. Elamin E, Alshomrani S, Feki J (2017) SSReq: a method for designing star schemas from decisional requirements. In: Proceedings international conference on communication, control, computing and electronics engineering, pp 1–7

  15. Gallinucci E, Golfarelli M, Rizzi S, Abelló A, Romero O (2018) Interactive multidimensional modeling of linked data for exploratory OLAP. Inf Syst 77:86–104

    Article  Google Scholar 

  16. Giorgini P, Rizzi S, Garzetti M (2008) GRAnD: a goal-oriented approach to requirement analysis in data warehouses. Decis Support Syst 45(1):4–21

    Article  Google Scholar 

  17. Golfarelli M, Maio D, Rizzi S (1998) The dimensional fact model: a conceptual model for data warehouses. Int J Coop Inf Syst 7(2–3):215–247

    Article  Google Scholar 

  18. Golfarelli M, Rizzi S (2011) Data warehouse testing: a prototype-based methodology. Inf Softw Technol 53(11):1183–1198

    Article  Google Scholar 

  19. Golfarelli M, Rizzi S, Turricchia E (2011b) Modern software engineering methodologies meet data warehouse design: 4WD. In: Proceedings of 13th international conference on data warehousing and knowledge discovery, Springer, pp 66–79

  20. Gosain A, Heena (2015) Literature review of data model quality metrics of data warehouse. In: Proceedings international conference on intelligent computing, communication & convergence, pp 236–243

  21. Guo Y, Tang S, Tong Y, Yang D (2006) Triple-driven data modeling methodology in data warehousing: a case study. In: Proceedings 9th international workshop on data warehousing and OLAP, pp 59–66

  22. Hassine J, Amyot D (2016) A questionnaire-based survey methodology for systematically validating goal-oriented models. Requir Eng 21:285–308

    Article  Google Scholar 

  23. Horkoff J et al (2019) Goal-oriented requirements engineering: an extended systematic mapping study. Requir Eng 24:133–160

    Article  Google Scholar 

  24. Hwang H-G, Ku C-Y, Yen DC, Cheng C-C (2004) Critical factors influencing the adoption of data warehouse technology: a study of the banking industry in Taiwan. Decis Support Syst 37:1–21

    Article  Google Scholar 

  25. Hwang MI, Xu H (2007) The effect of implementation factors on data warehousing success : an exploratory study. J Inf Inf Technol Org 2:1–16

    Google Scholar 

  26. Jukic N, Nicholas J (2010) A framework for collecting and defining requirements for data warehousing projects. CIT 18(4):377–384

    Article  Google Scholar 

  27. Kaldeich C, e Sá JO (2004) Data warehouse methodology: a process driven approach. In: Proceedings 16th international conference on advanced information systems engineering, Springer, pp 536–549

  28. Kamalrudin M, Grundy J (2011) Generating essential user interface prototypes to validate requirements. In: Proceedings 26th IEEE/ACM international conference on automated software engineering, pp 564–567

  29. Kimball R, Ross M (2002) The data warehouse toolkit: the complete guide to dimensional modeling, 2nd edn. Wiley, Hoboken

    Google Scholar 

  30. Kumar M, Gosain A, Singh Y (2010) Stakeholders driven requirements engineering approach for data warehouse development. JIPS 6(3):385–402

    Google Scholar 

  31. Lechtenbörger L (2003) Vossen G (2003) Multidimensional normal forms for data warehouse design. Inf Syst 28(5):415–434

    Article  Google Scholar 

  32. Malinowski M, Zimányi E (2008) Advanced data warehouse design—from conventional to spatial and temporal applications. Data-centric systems and applications. Springer, Berlin

    MATH  Google Scholar 

  33. Martinez A, Pastor O, Mylopoulos J, Giorgini P (2006) From early to late requirements: a goal-based approach. In: Proceedings workshop on agent-oriented information systems, pp 123–142

  34. Mazón J, Trujillo J (2009) A hybrid model driven development framework for the multidimensional modeling of data warehouses. SIGMOD Record 38(2):12–17

    Article  Google Scholar 

  35. Mazón J, Trujillo J, Lechtenboerger J (2007) Reconciling requirement-driven data warehouses with data sources via multidimensional normal forms. Data Knowl Eng 63(3):725–751

    Article  Google Scholar 

  36. Mazón J, Trujillo J, Serrano MA, Piattini M (2005) Designing data warehouses: From business requirement analysis to multidimensional modeling. In: Proceedings of 1st international workshop on requirements engineering for business need and IT alignment, pp 44–53

  37. Nabli A, Feki J, Gargouri F (2005) Automatic construction of multidimensional schema from OLAP requirements. In: Proceedings of international conference on computer systems and applications, p 28

  38. Nair R, Campbell W, Srinivasan B (2007) A conceptual query-driven design framework for data warehouse. World Acad Sci Eng Technol 25:141–146

    Google Scholar 

  39. Niemi T, Nummenmaa J, Thanisch P (2001) Constructing OLAP cubes based on queries. In: Proceedings international workshop on data warehousing and OLAP, pp 9–15

  40. Paim FRS, Castro J (2002) Enhancing data warehouse design with the NFR framework. In: Proceedings of workshop emengenharia de requisitos, pp 40–57

  41. Pohl K (2010) Requirements engineering—fundamentals, principles, and techniques. Springer, Berlin

    Google Scholar 

  42. Prakash N, Prakash D (2019) A multifactor approach for elicitation of information requirements of data warehouses. Requir Eng 24:103–117

    Article  Google Scholar 

  43. Prat N, Si-Said Cherfi S (2003) Multidimensional schemas quality assessment. In: Proceedings conference on advanced information systems engineering, pp 341–352

  44. Ravat F, Teste O, Tournier R, Zurfluh G (2008) Algebraic and graphic languages for OLAP manipulations. IJDWM 4(1):17–46

    Google Scholar 

  45. Romero O, Abelló A (2006) Multidimensional design by examples. In: Proceedings international conference on data warehousing and knowledge discovery, pp 85–94

  46. Romero O, Abelló A (2009) A survey of multidimensional modeling methodologies. IJDWM 5(2):1–23

    Google Scholar 

  47. Sakka A, Bimonte S, Sautot L, Camilleri G, Zaraté P, Besnard A (2018) A volunteer design methodology of data warehouses. In: Proceedings of 37th international conference on conceptual modeling, Springer, pp 286–300

  48. Salinesi C, Gam I (2006) A requirement-driven approach for designing data warehouses. In: Proceedings of requirements engineering: foundations for software quality

  49. Serrano M, Trujillo J, Calero C, Piattini M (2007) Metrics for data warehouse conceptual models understandability. Inf Softw Technol 49:851–870

    Article  Google Scholar 

  50. Shimomura T, Ikeda K, Chen QL, Lang NS, Takahashi M (2007). Visual pivot-table components for web application development. In: Proceedings of the third conference on IASTED international conference: advances in computer science and technology (ACST’07). ACTA Press, Anaheim, CA, USA, pp 90–95

  51. Suranto B (2015) Software prototypes: enhancing the quality of requirements engineering process. In: Proceedings international symposium on technology management and emerging technologies, pp 148–153

  52. Vaisman AA (2006) Requirements elicitation for decision support systems: a data quality approach. In: Proceedings of eighth international conference on enterprise information systems: databases and information systems integration, pp 316–321

  53. Vaisman AA, Zimanyi E (2014) Data warehouse systems—design and implementation. Data-centric systems and applications. Springer, Berlin

    Google Scholar 

  54. Vrdoljak B, Banek M, Rizzi S (2003) Designing web warehouses from XML schemas. In: Proceedings international conference on data warehousing and knowledge discovery, pp 89–98

  55. Winter R, Strauch B (2003) A method for demand-driven information requirements analysis in data warehousing projects. In: Proceedings 36th Hawaii international conference on system science, p 231

  56. Yeoh W, Koronios A (2010) Critical success factors for business intelligence systems. JCIS 50(3):23–32

    Google Scholar 

Download references

Author information

Authors and Affiliations

  1. INRAE, UR TSCF, Université Clermont, 9 Av. B. Pascal, 63178, Aubiere, France

    Sandro Bimonte

  2. LIFIA, Facultad de Informatica, UNLP, 50 esq 120, La Plata, BsAs, Argentina

    Leandro Antonelli

  3. DISI, UniversitĂ  di Bologna, V.le Risorgimento 2, 40136, Bologna, Italy

    Stefano Rizzi

Authors
  1. Sandro Bimonte
    View author publications

    You can also search for this author in PubMed Google Scholar

  2. Leandro Antonelli
    View author publications

    You can also search for this author in PubMed Google Scholar

  3. Stefano Rizzi
    View author publications

    You can also search for this author in PubMed Google Scholar

Corresponding author

Correspondence to Sandro Bimonte.

Additional information

Publisher's Note

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

Rights and permissions

Reprints and Permissions

About this article

Check for updates. Verify currency and authenticity via CrossMark

Cite this article

Bimonte, S., Antonelli, L. & Rizzi, S. Requirements-driven data warehouse design based on enhanced pivot tables. Requirements Eng 26, 43–65 (2021). https://doi.org/10.1007/s00766-020-00331-3

Download citation

  • Received:

  • Accepted:

  • Published:

  • Issue Date:

  • DOI: https://doi.org/10.1007/s00766-020-00331-3

Keywords

search

Navigation