Multidimensional data analysis with Excel pivot table used as a research method technique- a research note

Multidimensional data analysis with Excel pivot table (EPT-based MDA) used as a research method technique- a research note

 JOSEPH KIM-KEUNG HO

Independent Trainer

Hong Kong, China

 Abstract: Though the topic of multidimensional data analysis and the Excel pivot table function have been much examined in the Computer Science (CS) and the Management Information Systems (MIS) fields, it has been neglected as a research topic in the Research Methods (RM) field. This article examined the value of multidimensional data analysis (MDA) with Excel pivot table (EPT) and found it valuable and user-friendly as a research method technique. It offers some illustration on how it is used to study a data set from a 2015 Facebook-based questionnaire survey conducted by the writer on perceptions of literature review practices and concerns in Hong Kong. It is recommended that the EPT-based MDA be considered as a useful RM technique with the researcher adopting a number of information user behaviours simultaneously.

 Key words: Computer Science (CS), Excel pivot table (EPT), Excel pivot table-based multidimensional data analysis (EPT-based MDA), executive information systems (EIS), information user, literature review, Management Information Systems (MIS), multidimensional data analysis (MDA), online analytical processing (OLAP), Research Methods (RM)

Introduction

The Excel function of pivot table (EPT) is widely known and used by people doing quantitative data analysis, e.g., for sales statistics study. It offers a usable way to conduct a multidimensional data analysis (MDA) on a set of records with multiple fields. In the academic literature, multidimensional data analysis (MDA) is very often treated as a topic of study in the Computer Science field (e.g., Westerlund, 2008; Che et al., 2011), notably on the topic of online analytical processing (OLAP) (Pilotsoftware.com, 2002; Forsman, 1997). In the associated Management Information Systems (MIS) field (Haag and Cummings, 2013; Laudon and Laudon, 2012), the function to study a company's statistics, especially as related to key performance indicators, in a multidimensional way has long been considered as a useful feature of an executive information system (EIS) (Partanen and Savolanien, 1995; Nord and Nord, 1995). Specifically, Lungu and Bâra (2007) maintain that an EIS offers "a friendly graphical interface with advanced capabilities of slicing and dicing through data and easily get a new perspective over data by rotating dimensions and drill down or roll up over hierarchical levels".   In short, the value of MDA, which the EPT function offers, for studying a structured data set with multiple fields, e.g., as hierarchies of dimensions, is well recognized in the CS and MIS fields. In the Research Methods (RM) field, however, the value of EPT-based MDA as a quantitative research method technique has been neglected.  This is reflected in the very few academic articles found on the EBT-based MDA in academic journal publisher websites. Another indication for its being neglected as a research method topic is that the terms of "Excel pivot table" and "multidimensional data analysis" do not appear in the subject index of Saunders et al. (2012) and Bryman and Bell (2011), both popular Research Methods textbooks for business students. In an attempt to raise this research interest on EBT-based MDA, this article takes up the task of discussing the value of EBT-based MDA as a research method analysis technique.

Multidimensional data analysis with Excel pivot table as a research method technique

It has been well acknowledged that MDA is capable to produce valuable business information. As Che et al. (2011) puts it: "Multidimensional data analysis can observe and process data from several angles, obtaining useful information for management decision-making departments and providing effective support by turning business data into management data". For our discussion, a starting point to apply multidimensional data analysis with Excel pivot table (EPT-based MDA) is to consider a structured file with a number of fields (i.e., dimensions). In the context of research methods practice, typically, such a structured file is one on questionnaire survey, which is very often done online; subsequently, data captured are exported to an Excel file. After some data cleansing, the survey data file can be analysed with the Excel pivot table function. This is illustrated in Exhibit 1 as follows:

Exhibit 1: a structured data file and the Excel pivot table function

The location of the Excel pivot table function is in Insert, then PivotTable. Field names can be chosen as row or column dimensions for the pivot table while the value field(s) are the ones to be averaged or summed up, etc., in the pivot table. In the subject of multidimensional data analysis (MDA), the data file is called a data cube; it represents data along some measure of interest and can be 2-dimensional or more (www2.cs.iregina.ca, n.d). Each pivot table that draws on the data cube represents a view of it. Summarization and drill-down of values by column and row with hierarchies of dimensions can be shown in a pivot table. In the jargons of multidimensional data analysis, summarization (or rollup) of the data cube can be done "by traversing upwards through a concept hierarchy. A concept hierarchy maps a set of low level concepts to higher level, more general concepts" (www2.cs.iregina.ca, n.d).

When studying a data cube, the researcher could examine the following figures in a set of constructed pivot tables (i.e., views), with different combinations of fields (i.e., dimensions and value fields):

a. Row and column subtotals in a pivot table

b. Specific cell (i.e., an intersection cell between a row and a column) values in a pivot table [cell values can be count, average, sum, maximum, minimum and product figures]

c. Cross comparison of cell values in a pivot table or between pivot tables to discern data patterns, associations and clusters

d. Extreme, average and blank values in various cells to form a more critical and holistic evaluation of cell values

To facilitating spotting, exploring and evaluating patterns of value distribution in a set of constructed pivot tables, the researcher, an information user[1] (Inmon, Imhoff and Sousa, 2001), is recommended to behave concurrently like:

a. a tourist, who, albeit unpredictable, "knows where in the structure of things to find almost anything" (Inmon, Imhoff and Sousa, 2001);

b. a farmer, who "operates as comfortably on detailed data as on summary data" (Inmon, Imhoff and Sousa, 2001);

c. an explorer who "operates with a great degree of unpredictability", "looks over massive amounts of details" and "creates assertions and hypotheses"(Inmon, Imhoff and Sousa, 2001); and

d. a miner, who attempts to prove assertions and hypotheses' validity (Inmon, Imhoff and Sousa, 2001) .

An information user (in our case the researcher), is recommended to adopt an array of simultaneous information usage behaviours with the Excel pivot tables; the rationale of it is to make full use of the pivot table function and information from it to support diverse research tasks in relation to  data exploration/ exploitation as well as deductive and inductive reasoning based on the set of constructed pivot tables. Consequently, he/she should be in a position to (i) form an overall impression of the questionnaire survey findings in terms of data clustering and association, (ii) formulate some crude theories on their research themes based on the observations of the pivot tables and (iii) identify additional information needs and gaps for further research, e.g., arising from some noticed strange and unexpected patterns in the pivot tables. Consequently, some kind of knowledge discovery can be made from this EPT-based MDA. More specifically, with the pivot table, inductive reasoning can be employed to come up with some hypotheses or theories by studying the pivot table figures. At the same time, deductive reasoning with specific theories can also be attempted to explain the pivot table figures. All in all, the EPT-based MDA is capable of complementing other quantitative research methods, e.g., multiple regression analysis, chi-squared test and various hypothesis testing as well as other qualitative research methods, e.g., interview and observations, (possibly as a follow-up investigation based on the pivot table analysis). In addition, the pivot tables also serve as a means to convey data analysis findings in the findings and analysis chapter of a dissertation report. The next section is an example of EPT-based MDA on a data set of a Facebook-based questionnaire survey conducted by the writer in 2015 on the perceptions of literature review practices and concerns in Hong Kong.

An illustration of using multidimensional data analysis with Excel pivot table to study survey questionnaire data

A brief EPT-based MDA is conducted on a Facebook-based survey data set covering perceptions on literature review practices and concerns in Hong Kong. This questionnaire survey serves both descriptive and analytical survey purposes by documenting current literature review perceptions and practices as well as explaining why these perceptions and practices exist. The questionnaire survey was conducted in January 2015 and reported in Ho (2015). For the brief account of it here with regard to multidimensional data analysis, the writer treats initially some of the survey questions (re: appendix 1) as constituting row and column dimensions while others as making up the calculated value fields for counting, summation and averaging. This way, the row and column dimensions are conceived for the data analysis exercise as independent variables while the calculated value fields are considered as dependent variables to spot and evaluate data patterns and associations. They are listed as follows:

Survey questions (Ho, 2015)	Dimensions (row or column) or calculated value fields
Question 1: What is your gender? ·      Male ·      Female	Dimension
·      Question 2: What is your age? ·      18 to 27 ·      28 to 37 ·      38 to 47 ·      48 to 57 ·      58 to 67 ·      68 or above	Dimension
Question 3: What is your education background? ·      Not yet a degree-holder ·      Finished University Undergraduate Degree study ·      Finished Master Degree study ·      Finished Ph.D. Degree study (or equivalent)	Dimension
Question 4: What is your field of education? Business related Non-business related Both business and non-business related Unclassified	Dimension
Question 5: Did you (or are you) learn the subject of “Literature Review” in Research Methods in your formal education? ·      Yes ·      No ·      Cannot remember	Dimension
Question 6: Do you (or did you) feel that you have difficulty to understand the subject of Literature Review during your study of Research Methods (or other courses) for your formal education? ·      Yes, I strongly feel so ·      I have this feeling mildly ·      I feel it is not difficult to understand ·      No feeling at all/ Not applicable	Calculated value field
Question 7: Do you (or did you) feel that academic journal articles are difficult to understand during your study for your formal education? ·      Yes, I strongly feel so ·      I have this feeling mildly ·      I feel it is not difficult to understand, in general ·      No feeling at all	Calculated value field
Question 8: Do you (or did you) use the University e-library to access academic journal articles to do your course assignments and dissertation projects? ·      Yes, I do ·      No, I don’t ·      Cannot remember	Dimension
Question 9: Do you (or did you) feel that academic articles are useful for literature review? ·      Yes, very useful ·      It is basically useful ·      Not useful ·      No idea	Calculated value field
Question 10: Do you (or did you) feel that reading academic journal articles is able to improve your professional competence? ·      Yes, I strongly feel so ·      I have this feeling mildly ·      I don’t think so ·      No idea	Calculated value field
Question 11: Do you have access to academic journal libraries (not Google scholar) when you are not studying for a formal education program? ·      Yes, and convenient ·      Yes, but not convenient ·      Not able to access at all ·      No idea	Dimension
Question 12: Are you interested in improving your literature review skill in the near future? ·      Yes, I am strongly interested ·      I am mildly interested ·      No, not interested ·      No idea	Calculated value field
Question 13: Do you feel that you are able to improve your literature review skill without reading academic journal articles? Yes, I strongly fee so I have this feeling mildly No, I do not feel this way No idea	Calculated value field
Question 14: Do you enjoy reading academic journal articles? ·      Yes, I enjoy it very much ·      I do, basically ·      No, I don’t ·      No feeling	Calculated value field

Two brief pivot table analyses on the survey data, i.e., analysis 1 and analysis 2, are now reported as follows for illustration purpose.

            Pivot table analysis 1 on "perceived difficulty to study academic journal articles"

This analysis adopts the following coding scheme for the data analysis on survey question 7: Do you (or did you) feel that academic journal articles are difficult to understand during your study for your formal education?

Yes, I strongly feel so:	3
I have this feeling mildly:	2
I feel it is not difficult to understand, in general:	1
No feeling at all:	filtered; not used in the analysis

                                   

Table 1: perceived difficulty to study academic journal articles with the row dimension on education background and the column dimension on field of education.

Average of Perceived difficulty to study academic journal articles	Column Labels
Row Labels	Both business and non-business related	Business-related	Non-business related	Unclassified	(blank)	Grand Total
Finished Master Degree study	1.9	1.6	1.5	2.0		1.7
Finished Ph.D. Degree study (or equivalent)		3.0				3.0
Finished university Undergraduate Degree study	2.0	1.8	2.0	2.0		1.9
Not yet a degree-holder	2.5	2.1	2.0			2.1
(blank)
Grand Total	2.0	1.9	1.8	2.0		1.9

Interpretations: On perceived difficulty to study academic journal articles, those with both business and non-business-related fields experienced more difficulty to study academic journal articles than others. Those with Ph.D. degrees also have more difficulty to study academic journal articles.  It appears important to also take into consideration the numbers of respondents in each cell on interpreting this set of pivot table values.

Table 2: perceived difficulty to study academic journal articles with the row dimension on education background and the column dimension on age range.

Average of Perceived difficulty to study academic journal articles	Column Labels
Row Labels	18 to 27	28 to 37	38 to 47	48 to 57	(blank)	Grand Total
Finished Master Degree study		2.2	1.6	1.0		1.7
Finished Ph.D. Degree study (or equivalent)				3.0		3.0
Finished university Undergraduate Degree study	2.0	1.9	1.8	1.8		1.9
Not yet a degree-holder	1.8	2.4	1.8	3.0		2.1
(blank)
Grand Total	1.8	2.1	1.7	2.1		1.9

Interpretations: On perceived difficulty to study academic journal articles, those in the age ranges of 26-37 and 48-57 have more difficulty to study academic journal articles than others. Those in the age range of 48 to 57 with Ph.D. degrees and no degree yet also have more difficulty to study academic journal articles.

Table 3: perceived difficulty to study academic journal articles with the row dimension on education background and the column dimension on gender.

Average of Perceived difficulty to study academic journal articles	Column Labels
Row Labels	Female	Male	(blank)	Grand Total
Both business and non-business related	2.0	2.0		2.0
Business-related	1.9	2.1		1.9
Non-business related	1.3	1.9		1.8
Unclassified	2.0			2.0
(blank)
Grand Total	1.9	2.0		1.9

Interpretations: On perceived difficulty to study academic journal articles, male respondents have more difficulty to study academic journal articles than female respondents. This is especially the case for respondents with non-business related education background.

            Pivot table analysis 2 on "perceived relevance of academic article study to professional competence improvement"

This analysis adopts the following coding scheme for the data analysis on survey question 10: Do you (or did you) feel that reading academic journal articles is able to improve your professional competence?

Yes, I strongly feel so:	3
I have this feeling mildly:	2
I don't feel so	1
No idea	filtered; not used in the analysis

                                   

Table 4: perceived relevance of academic article study to professional competence improvement with the row dimension on education background and the column dimension on field of education.

Average of Relevance of academic article study to professional competence improvement	Column Labels
Row Labels	Both business and non-business related	Business-related	Non-business related	Unclassified	(blank)	Grand Total
Finished Master Degree study	2.2	3.0	2.2	2.0		2.4
Finished Ph.D. Degree study (or equivalent)		2.0				2.0
Finished university Undergraduate Degree study	2.5	2.3	2.2			2.3
Not yet a degree-holder	2.0	2.1	3.0			2.1
(blank)
Grand Total	2.3	2.3	2.3	2.0		2.3

Interpretations: On perceived relevance of academic article study to professional competence improvement, respondents with a master degree or no degree yet perceive higher relevance of academic journal article study to professional competence development.

Table 5: perceived relevance of academic article study to professional competence improvement with the row dimension on education background and the column dimension on age range.

Average of Relevance of academic article study to professional competence improvement	Column Labels
Row Labels	18 to 27	28 to 37	38 to 47	48 to 57	(blank)	Grand Total
Finished Master Degree study		2.0	2.5	3.0		2.4
Finished Ph.D. Degree study (or equivalent)				2.0		2.0
Finished university Undergraduate Degree study	2.0	2.3	2.3	2.5		2.3
Not yet a degree-holder	2.2	2.4	1.7	2.0		2.1
(blank)
Grand Total	2.2	2.3	2.2	2.4		2.3

Interpretations: On perceived relevance of academic article study to professional competence improvement, respondents in the age range of 48-57 perceive higher relevance of academic journal article study to professional competence development. Non-degree holders in the age range of 38-47 perceive the lowest relevance of academic journal article study to professional competence development.

Table 6: perceived relevance of academic article study to professional competence improvement with the row dimension on gender and the column dimension on age range.

Average of Relevance of academic article study to professional competence improvement	Column Labels
Row Labels	18 to 27	28 to 37	38 to 47	48 to 57	(blank)	Grand Total
Female	2.3	2.2	2.3	3.0		2.3
Male	2.0	2.4	2.1	2.3		2.2
(blank)
Grand Total	2.2	2.3	2.2	2.4		2.3

Interpretations: On perceived relevance of academic article study to professional competence improvement, female respondents perceive slightly higher relevance of academic journal article study to professional competence development than male respondents. On the other hand, male respondents in the age range of 28-37 has the highest perceived relevance of academic journal article study to professional competence development than others.

Overall, after the necessary data cleansing done, both the generation of the prime Excel pivot tables and the exploration of the table by trying different row/  column dimensions and calculated value fields are simple and user-friendly. More importantly, the pivot table analysis is able to reveal relatively detailed patterns of result values that provide information for inductive and deductive reasoning. If the file size for multiple data analysis is large, it also becomes more feasible to consider more than one dimension in a pivot table row or column in the EPT-based MDA. The pivot table analysis stimulates a researcher to generate additional questions that require further research investigation using other research methods. Lastly, the literature on EIS and OLAP sharpens the researcher's objective and clarifies his/her information roles in conducting the pivot table analysis. As such, the EPT-based MDA should not be considered merely a tool to produce descriptive statistics in a quantitative research method employed in a dissertation research project.

Concluding remarks

The Excel pivot table function is not a sophisticated tool for multidimensional data analysis. Nevertheless, it's application value for an EPT-based MDA should be recognized more. The discussion and the brief EPT-based MDA illustration serve to establish (a) the EPT-based MDA as a very useful research method technique and (b) the EPT function for MDA as a useful decision support system for researchers. Although multidimensional data analysis and the Excel pivot table function have been much examined in the computer science and MIS literature, they deserve more attention from the Research Methods field to study. For example, the decisional support ideas in multidimensional data analysis and EIS from the CS and MIS fields should be transferred to the EPT-based MDA as a research method technique in the Research Methods field; in this regard, the researcher is recommended to take up multiple information user roles to benefit more from the analysis exercise. Doing so enables the EPT-based MDA to make much more contribution to enhance research methods practices in the Research Methods field.

References

Bryman, A. and E. Bell. 2011. Business Research Methods, 3rd edition, Oxford University Press.

Che, L., F. Ding, W. Cui, A.X. Zhang and Z.H. Chen. 2011. "The Application of Multidimensional Data Analysis in the EIA Database of Electric Industry" Procedia Environmental Sciences 10, Elsevier: 1210-1215.

Forsman, S. 1997. "OLAP Council White Paper" OLAP Council (url address: http://www.symcorp.com/downloads/OLAP_CouncilWhitePaper.pdf) [visited at April 5, 2018].

Haag, S. and M. Cummings. 2013. Management Information Systems for the Information Age, 9th Edition, McGraw-Hill.

Ho, J.K.K. 2015. "Examining Literature Review Practices and Concerns Based on Managerial Intellectual Learning Thinking" International Journal of Interdisciplinary Research in Science Society and Culture (IJIRSSC) 1(1): 1-13.

Inmon, W.H., C. Imhoff and R. Sousa. 2001. Corporate Information Factory, 2 edition, Wiley.

Laudon, K.C. and J.P. Laudon. 2012. Management Information Systems: Managing the digital firm, 12th edition, Prentice Hall.

Lungu, I. and A. Bâra. 2007. "Executive Information Systems's Multidimensional Models" Revista Informaica Economică 3(43): 87-90.

Nord, J.H. and G.D. Nord. 1995. "Executive information systems:  A study and comparative analysis" Information & Management 29, Elsevier: 95-106.

Partanen, K. and V. Savolainen. 1995. "Perspectives on Executive Information Systems" Systems Practice 8(6): 551-575.

Pilotsoftware.com. 2002. "An Introduction to OLAP: Multidimensional Terminology and Technology" A white paper, Pilot Software Acquisition Corp., One Canal Park, Cambridge, MA.

Saunders, M., P. Lewis and A. Thornhill. 2012. Research Methods for Business Students, sixth edition, Pearson.

Westerlund, P. 2008. Business Intelligence: Multidimensional Data Analysis. Master Thesis in Computing Science August 20, 30 ECTS Credits (url address: http://www.diva-portal.org/smash/get/diva2:1137039/FULLTEXT01.pdf) [visited at April 5, 2018].

Www2.cs.uregina.ca. n.d. "Data Cubes" www2.cs.uregina.ca (url address: http://www2.cs.uregina.ca/~dbd/cs831/notes/dcubes/dcubes.html) [visited at April 6, 2018].

Appendix

Appendix 1: The Facebook-based survey questions (14 questions) dated January 23 to 30, 2015 and responses statistics (Ho, 2015).

Survey questions	Survey statistics
Question 1: What is your gender?	Male: 57 (44.5%) Female: 71 (55.5%)
Question 2: What is your age?	18 to 27: 6 (4.7%) 28 to 37: 60 (46.9%) 38 to 47: 52 (40.6%) 48 to 57: 10 (7.8%) 58 to 67: 0 (0.0%) 68 or above: 0 (0.0%)
Question 3: What is your education background?	Not yet a degree-holder: 34 (26.6%) Finished University Undergraduate Degree study: 70 (54.7%) Finished Master Degree study: 22 (17.2%) Finished Ph.D. Degree study (or equivalent): 2 (1.6%)
Question 4: What is your field of education?	Business related: 97 (75.8%) Non-business related: 14 (10.9%) Both business and non-business related: 15 (11.7%) Unclassified: 2 (1.6%)
Question 5: Did you (or are you) learn the subject of “Literature Review” in Research Methods in your formal education?	Yes: 86 (67.7%) No: 33 (26.0%) Cannot remember: 8 (6.3%)
Question 6: Do you (or did you) feel that you have difficulty to understand the subject of Literature Review during your study of Research Methods (or other courses) for your formal education?	Yes, I strongly feel so: 24 (18.8%) I have this feeling mildly: 58 (45.3%) I feel it is not difficult to understand: 30 (23.4%) No feeling at all/ Not applicable: 16 (12.5%)
Question 7: Do you (or did you) feel that academic journal articles are difficult to understand during your study for your formal education?	Yes, I strongly feel so: 26 (20.3%) I have this feeling mildly: 60 (46.9%) I feel it is not difficult to understand, in general: 35 (27.3%) No feeling at all: 7 (5.5%)
Question 8: Do you (or did you) use the University e-library to access academic journal articles to do your course assignments and dissertation projects?	Yes, I do: 93 (72.7%) No, I don’t: 31 (24.2%) Cannot remember: 4 (3.1%)
Question 9: Do you (or did you) feel that academic articles are useful for literature review?	Yes, very useful: 65 (50.8%) It is basically useful: 47 (36.7%) Not useful: 3 (2.3%) No idea: 13 (10.2%)
Question 10: Do you (or did you) feel that reading academic journal articles is able to improve your professional competence?	Yes, I strongly feel so: 47 (36.7%) I have this feeling mildly: 59 (46.1%) I don’t think so: 16 (12.5%) No idea: 6 (4.7%)
Question 11: Do you have access to academic journal libraries (not Google scholar) when you are not studying for a formal education program?	Yes, and convenient: 23 (18.0%) Yes, but not convenient: 30 (23.4%) Not able to access at all: 61 (47.7%) No idea: 14 (10.9%)
Question 12: Are you interested in improving your literature review skill in the near future?	Yes, I am strongly interested: 41 (32.5%) I am mildly interested: 46 (36.5%) No, not interested: 28 (22.2%) No idea: 11 (8.7%)
Question 13: Do you feel that you are able to improve your literature review skill without reading academic journal articles?	Yes, I strongly fee so: 13 (10.2%) I have this feeling mildly: 17 (13.3%) No, I do not feel this way: 75 (58.6%) No idea: 23 (18.0%)
Question 14: Do you enjoy reading academic journal articles?	Yes, I enjoy it very much: 9 (7.0%) I do, basically: 58 (45.3%) No, I don’t: 51 (39.8%) No feeling: 10 (7.8%)

---

[1] While information users, in their managerial roles, are primarily interested in gaining knowledge with business value, those as researchers, e.g., doing applied business research, are preoccupied with obtaining knowledge with business as well as academic values.