Building a Data Warehouse for Social Media Analysis: The case of Twitter and Facebook

doi:10.21203/rs.3.rs-3738146/v1

Download PDF

Research Article

Building a Data Warehouse for Social Media Analysis: The case of Twitter and Facebook

https://doi.org/10.21203/rs.3.rs-3738146/v1

This work is licensed under a CC BY 4.0 License

Version 1

posted

You are reading this latest preprint version

“ The proliferation of data generated and stored through social media has experienced a significant surge over the past decade. Consequently, the analysis and interpretation of such data have emerged as valuable sources of insights across diverse contexts, serving as aids for researchers and businesses in making informed decisions. However, the data is widespread, stemming from diverse sources with distinct formats, and is generated at a rapid pace. These characteristics collectively contribute to the intricacy of extracting knowledge from this data, transforming the process into one that is both complex and resource-intensive. The central scientific contribution of this paper lies in the formulation of a social media data integration model, built upon the foundation of a data warehouse. This model is designed to alleviate the computational costs associated with data analysis while concurrently facilitating the application of techniques aimed at discovering meaningful insights. Notably, this study differentiates itself from existing literature by concentrating on both the Facebook and Twitter social media platforms. Additionally, we introduce a model that covers data acquisition, transformation, and loading processes, enabling the extraction of valuable insights even when the data's complexity surpasses human understanding. The results of our study showcase that the proposed data warehouse enhances the effectiveness of data mining algorithms in contrast to related works. Notably, this improvement in quality is achieved while simultaneously reducing execution time.”

Social media

Data warehouse

OLAP

“In recent years, social media have revolutionized the ways of interacting and exchanging information on the Internet. Millions of users interact frequently and share a variety of digital content with each other. In fact, Social media are a vital part of our life nowadays because of how they have transformed the world. For instance, they altered how we conduct business, contact with friends and family, and view job postings.

Social media's widespread use in recent years is due to their ease of use. It provides access to everyone worldwide, irrespective of their age, profession, or social status (Girsang et al. 2020). These platforms are used by plenty of people for various things. Some people utilized them to communicate with their loved ones and share images, movies, and other aspects of their daily lives. Additionally, users use social media in the context of business to share and enhance their products, advertise their services in real-time, and connect with and draw in clients. Thus, a successful business should be able to assess the available data (such as customer feedback, competitor product prices, and reviews of services and products), identify what has occurred, and forecast what will happen in the near future (He et al. 2013). Businesses have realized that they may benefit from their social media presence, especially through developing distinctive and ongoing relationships with their clients and partners.

Storing, pre-processing, and analysis of social media data provide a significant difficulty in this situation (Gutiérrez-Batista et al., 2021). A data warehouse functions as a central repository where an organization may store all of its data from different sources. In order to enhance their reputation on these platforms, these businesses must understand how to load them into the data warehouse and, in particular, how to evaluate them.”

“However, social media analysis is a particularly recent axis that covers research topics of business intelligence, data analysis, and information research. As a result, social media data is extensively employed by organization analysts for a variety of analytical activities, including sales or trend analysis and opinions or feeling analysis. Implementing and modeling a social media data warehouse may actually prove to be a highly beneficial and essential activity for businesses looking to make better decisions. Nonetheless, due to its capability for multidimensional analysis, the data warehouse effectively analyzes the large and semi-structured data from social media. Furthermore, several research studies have concentrated on the creation of the independent data warehouse schema from one or two social media because of the significance of this discipline's integration of social media into business intelligence of a corporation (Rehman et al. 2012; Gallinucci et al. 2013; Moalla and Nabli 2014; Cuzzocrea et al. 2016). However, the information from several social media platforms relates to the same firm in actual case studies. In order to promote the capitalization of knowledge, it is therefore interesting to reduce the heterogeneity and variety of information that combine and integrate them into a single data warehouse schema.

By making their public stream available through a set of APIs, Twitter and Facebook have triggered a wave of research initiatives aimed at analysis and knowledge discovery from the data about its users and their messaging activities. Most of works provided in the literature of this domain are tailored towards solving specific requirements. These tasks include but are not limited to trend discovery, content enrichment, user profiling, topic-based clustering, sentiment analysis, etc.

However, OLAP offers an interactive online analysis of data in an environment capable of holding large volumes of data. In addition, it provides a simple and flexible modeling to perform different types of analyses based on a predefined multidimensional model and including pre-calculated data that accelerate the analytical processing. Nevertheless, so far, very few studies have focused on the multidimensional modeling of data issued from social media. This modeling, if it includes the entire data extracted from social media could be a judicious opportunity to explore them through an OLAP process and then, helps solving a wide variety of analytical needs (Chaudhuri and Dayal, 1997).

That’s why our main objective in this paper is first to model the data extracted from Twitter and Facebook and, secondly to develop an independent application and universal analytical platform that promotes multidimensional storage of contents of these data in order to allow analyzing ad-hoc requirements without being restricted to a set of predefined needs.”

The remaining of this paper is organized as follows. In the next section we discuss related work. An overview of the proposed model is presented in Section 3. Sections 4, 5 and 6 detail our contribution. In section 7, results and analyses for testing this multidimensional model on data extracted from tweets are presented. Section 8 concludes the paper with an outline of the main perspective in this work.

“Social media, as a new data provider, has largely contributed to the appearance of new issues related to the modeling and manipulation of data. In this context, the analysis of data issued from social media is a promised research topic that has attracted the attention of many researchers and has given birth to novel analysis areas, such as Social Media Analysis. The works related to this area can be subdivided into two major categories: Those designing Data Warehouse (DW) models only from one social media (Twitter, Facebook,…), while others deal with the problem of modeling data warehouse schema from two social media (Moalla et al. 2017; Kurnia, P.F 2018; Valêncio et al. 2020).

We start by studying works that have been interested in designing Data Warehouse (DW) models only from Twitter (Bringay et al. 2011; Rehman et al. 2012; Cuzzocrea et al. 2016). The first research on designing DW from Twitter was conducted by (Bringay et al. 2011). The authors defined a multidimensional star model for analyzing a large number of tweets. However the proposed model was dedicated to a particular trend. In order to do this, the authors have proposed an adapted measure, called "TF-IDF _adaptive", which identifies the most significant words according to level hierarchies of the cube (the location dimension). Nevertheless, their case study deals with a specific area: the evolution of diseases, referring to the thesaurus MeSH (Medical Subject Headings) by adding to their multidimensional model a dimension called MotMesh (MeshWords).

(Rehman et al. 2012) proposed a system for warehousing Streams from Twitter. Their system lies on an architecture consisting of five layers: i) The data source layer is represented by the available Twitter APIs, ii) The ETL layer (Extract, Transform and Load) for the extraction of data from tweets and processing in a suitable format for the target database, iii) The Data warehouse layer for the storage of data issued from tweets, iv) The Analysis layer dedicated for OLAP analyses of the tweets, and v) The Presentation layer of analysis results. Then, the authors present an extension of their work (Rehman et al. 2013) where they focus on integrating extensive natural language processing capabilities in OLAP to perform multidimensional social media analysis. A rather similar approach is proposed in the works of (Cuzzocrea et al. 2016). Considering Twitter, the authors emphasize the role of implicit information that could be derived or discovered in the tweets beyond explicit available metadata. Hence, they presented an extension of their previous work where they focus on the definition of multidimensional data model for the storage of tweets’ data to deal with the OLAP analysis. To do so, the authors defined the data cube where the dimensions are of two types: (i) Semantic Dimension: this dimension is extracted from Wikipedia knowledge base, and it makes use of the titles of Wikipedia articles and of the Wikipedia category graph. (ii) Metadata Dimension: Metadata refers to the information about the tweet that can be derived from its metadata, such as: timestamp, user, hashtag, location, etc. In addition, the authors propose a measure that exploits Wikification service to represent a sentence with a set of Wikipedia concepts. For OLAPing purpose, the authors proposed a summarization algorithm to select the best tweets that represent the data of each cube according to OLAP dimensional fact model.

Another study, with similar objectives, is presented by (Jenhani et al. 2019). An approach based on distributed storage and parallel processing was developed to extract events from streaming social media data at a large scale. To achieve this, Storm was used to process data extracted from Twitter's Streaming API, and Hadoop was used to process large volumes of data for integration into the data warehouse. The authors proposed a snowflake schema for modeling event data, which allows for both independent analysis of social media events and their integration with the existing enterprise data warehouse. A bridge table was employed to establish the connection between the Social Media Data Warehouse (SMDW) and the Enterprise Data Warehouse (EDW). In a similar vein, (Girsang et al. 2020) introduced a Business Intelligence (BI) application dashboard that utilizes a data warehouse to provide the journalistic community with a solution for accessing powerful, effective, and extensive news sources. To achieve this, the authors collected data from Twitter using the available API and periodic crawlers. The retrieved data were then stored in the database as raw data and subjected to text classification using the Support Vector Machine (SVM) algorithm. The resulting data were stored in the database as analysis data, which was then processed through the Extract-Transform-Load (ETL) process and placed in the data warehouse. The data warehouse enabled the creation of a BI dashboard that could assist journalism in analyzing news information.

Despite the growing interest in designing data warehouses from social media, there are few studies that focus on modeling schema from two different social media platforms (Kurnia, P.F 2018; Valêncio et al. 2020; Moalla et al.2017, 2022).

For their part, (Moalla et al. 2017) propose a new method of opinion analysis based on machine learning that determines the polarity of users ‘comments shared on different social media. The latter will be integrated in the ETL (Extract, Transform and Load) process to analyze the users' opinions. The proposed method is based on the n-grams technique to construct a semi-automatic dictionary for positive and negative keywords that is used in the learning phase to establish the prediction model. In addition, they propose a new features vector specific for social media for classifying the comments as positive, negative or neutral. The evaluation results performed on both advertising data sets Stanford Twitter Sentiment (STS) and Sanders's dataset showed a high accuracy level. In 2022, the authors presented an extension of their previous work. They propose a new approach for building a DW from social media for opinion analysis. The proposal consists of four phases: Data extraction and cleansing, Transformation, loading, and analysis. They have presented the different stages of data extraction and cleansing. These steps are intended to model data marts for each social media. In the transformation phase, the authors have detailed the mapping and merging step to obtain a generic DW schema. In addition, they have summarized the opinion analysis step. After that they presented the implementation of a DW under a database NoSQL- oriented documents. Finally, in the analysis and reporting steps, they performed some queries on our DW. A rather similar approach is proposed in (Valêncio et al. 2020). The authors proposed a constellation schema to model data from Facebook and Twitter. Their normalized model utilizes quantitative attributes from social media posts to reduce duplicate data and minimize the execution time of data mining algorithms. To support this schema, the authors introduced a Configurable Load and Acquisition Social Media Environment (CLASME), which allows for data preparation and knowledge discovery from Facebook and Twitter. The ETL phase begins with the selection of public data from pages and accounts, followed by loading qualitative data, classifying comments into positive, negative, and neutral, and determining opinions about the post. Quantitative data is then loaded. Once ETL and data warehousing are complete, data mart algorithms are applied in the data analysis phase to validate the classification model. The results are interpreted to facilitate the decision-making process. In a same context, (Kurnia, P.F. 2018) developed a business intelligence dashboard to assess the performance of topics posted on Facebook and Twitter. To achieve this, they implemented a data warehouse model and software for the business intelligence system that consists of four stages. First, data is collected by extracting information from Facebook and Twitter using the available APIs. Text classification techniques, such as Naive Bayes, decision tree, and SVM, are then applied to attribute a category or class to the data based on the document characteristics. The data warehouse design stage follows the Kimball method, which proposes a star schema model to represent the number of comments, tweets, likes, etc., for each topic. Finally, the business intelligence design stage involves creating a business intelligence dashboard using the CodeIgniter framework. Despite the strengths of existing approaches, they also have certain limitations. Notably, most of the proposed design approaches in this domain are confined to using a single social media platform (Twitter) as a data source and fail to consider other social media platforms. Additionally, the design process is not presented in detail, with little to no discussion of the rules that govern the data warehouse schema. To overcome these limitations, our approach leverages multiple social media platforms to gather additional information. Specifically, we utilized two social media platforms that are relevant to the same subject matter. To effectively combine and aggregate the data from these platforms, we developed a schema integration method that generates a unified data warehouse schema.”

“Further to this study, we may conclude that most of these works ensure a special treatment of data extracted from social media but don’t offer tools for the decision maker to manipulate the information contained in the combined meta-data associated their posts.

Hence, our aim is to provide a multidimensional model dedicated to the content, metadata and social aspect of posts that is generic (i.e., independent of the special needs pre-defined a priori) and taking into account the structural specificity and possibly semantic data.”

To define a data warehouse design approach for social media, we suggest five steps namely:

Data extraction and cleaning
Data mapping
Multidimensional schema design
Transforming and loading
Reporting and querying

Next, we explain each of these steps.

Data extraction and cleaning. Our purpose is to analyze data that has been extracted from various sources, such as photos, links, videos, and status updates, from a Facebook page and tweets (with a maximum size of 140 characters) posted on a Twitter account. To achieve this, we start by collecting data from Facebook using the Graph API and from Twitter using available APIs. Afterward, we perform a cleaning step to filter out irrelevant data, remove inconsistencies, and eliminate duplicates.

Data mapping. As the data is obtained from multiple sources, it is essential to perform a mapping step. This involves identifying the relationships between the data extracted from Facebook and the data retrieved from Twitter, to identify any equivalence or synonymies between the data. This task is usually carried out either manually or semi-automatically, which can make it a costly process.

Multidimensional schema design. In this phase, we define the multidimensional schema of the data warehouse based on the extracted data. This process involves two primary steps: subject analysis and determining multidimensional concepts.

Transforming and loading. This step takes care of capturing the original data stream, bringing it into a format compliant with the target database and feeding automatically the various components of the multidimensional model (fact, dimensions, and parameters) issued from the Twitter and Facebook.

Reporting and Querying. Once the multidimensional model is generated and loaded with data, the decision maker can perform OLAP analyses on tweets using the OLAP tool offered by the implementation platform (e.g., Oracle Discoverer).

In the upcoming sections, we will provide an in-depth explanation of the multidimensional concepts used to design the data warehouse schema. We will also discuss the initial data extraction and cleaning process. Finally, we will present the data mapping procedure and the multidimensional schema design.

Data extraction and cleaning are crucial steps in obtaining useful insights from social media platforms such as Facebook and Twitter. With billions of active users on these platforms, extracting relevant data can be a daunting task, but it is essential for businesses and researchers to make informed decisions. Therefore, it is important to prioritize data extraction and cleaning to ensure reliable and accurate data for analysis.

During the initial stage, we gathered general information about the Facebook page, including its ID, name, website, description, category, and other relevant details. Similarly, we collected general information about the Twitter account, such as its ID, name, screen name, location, followers count, and friends count. After that, we extracted information about the posts and tweets, including their respective IDs, messages, creation dates, comments, likes, number of shares, re-tweet count, favorite count, and language. The dataset generated was semi-structured and formatted using the JavaScript Object Notation (JSON). The extraction process involves creating an application to access the available APIs and select the appropriate data access permissions. In the data warehouse, data cleansing was performed in the second stage to ensure accurate and consistent data, eliminate duplicate information, and consolidate various data representations. The decision-maker participated in this step to select relevant data.

Hence, we examined all the extracted data in order to judge those that could be potentially useful for OLAP analysis. Following this review, we excluded some data which we considered a bit useful or even useless such as Data describing the user profile (e.g., User profile’s photo, Background image chosen by the user for his own twitter page, colors for characters and bars...).

As the data is drawn from diverse social media channels, the mapping phase is critical. This step involves the alignment of the data with a standardized format, enabling it to be integrated into a coherent data warehouse. Proper mapping helps to ensure that the data can be effectively analyzed, which is essential for making informed decisions. Without proper mapping, the data may be incomplete, inconsistent, or incompatible, rendering it unusable for analytical purposes. As a result, the mapping process is a vital step in the data integration pipeline, facilitating the creation of a unified and high-quality data warehouse. Thus, it is possible to obtain similar information from both Facebook and Twitter. However, it is also likely to find information in one platform that is not present in the other. To address this issue of inconsistency and redundancy in the data, we propose using a mapping step. This step involves integrating data from both sources to ensure that there is no duplication or missing information. To achieve this, we utilized data extracted from Facebook pages and Twitter accounts. We observed two types of heterogeneity: structural and semantic. To address structural heterogeneity, we mapped the schemas from both platforms using Custom scripts with programming languages Java. The retrieved data needs to be transformed and mapped to ensure consistency and relevance. This step involves handling differences in data formats, converting timestamps, and aligning user profiles, posts, tweets, likes, comments, and other interactions between the two platforms. While there is some overlap between the attributes that Facebook and Twitter use to describe user profiles and content, there are also some differences. Here is a general mapping of some of the key attributes for Facebook and Twitter:

Profile Picture: Both Facebook and Twitter allow users to upload a profile picture, which is a small image that appears next to the user's name or handle.
Cover Photo/Banner Image: Facebook and Twitter both allow users to upload a larger banner image or cover photo that appears at the top of their profile or page.
Bio/About: Both platforms provide a section where users can write a short description of themselves or their business.
Location: Facebook and Twitter both allow users to specify their location or hometown.
Interests: Facebook provides a section where users can list their interests, while Twitter does not have a dedicated field for this. However, users can indicate their interests by following relevant accounts and using hashtags related to their interests.
Followers/Friends: Twitter refers to people who follow an account as "followers," while Facebook refers to them as "friends." Both platforms allow users to connect with other users and see their updates.
Likes/Favorites: Facebook allows users to "like" content posted by others, while Twitter allows users to "favorite" tweets. Comments/Replies: Both platforms allow users to comment on or reply to content posted by others.
Hashtags: While both platforms support the use of hashtags, they are more commonly used on Twitter as a way to categorize content and make it more discoverable.

. According to the state-of-art (Moalla et al., 2016), the three possible semantics are: Identical, equivalent and complementary. Such relationships represent a way to integrate the data in the DW. Each of these relationships plays a crucial role in understanding and categorizing the connections between various elements in our dataset. By discerning and leveraging these semantic relations, we aim to enhance the efficiency and accuracy of our data analysis, enabling us to draw meaningful insights and make informed decisions based on the underlying relationships among the elements. Attributes that have the same name and meaning are considered identical, while attributes with different names but the same meaning are considered equivalent. Complementary attributes have no relationship and provide additional information specific to one platform.

Using the proposed semantic relations, we created a mapping table that links the data extracted from Facebook and Twitter for both general information and information related to individual posts and tweets. Table I displays a sample of the mapping table that we developed specifically for the general information.

The first column of the table displays the attributes related to general information extracted from the Facebook page, whereas the second column shows the corresponding attributes obtained from the Twitter account. The third column provides a description of the semantic relations between these attributes.

Table 1

Mapping table.
Attributes Facebook	Attributes Twitter	Semantic relation
id	id	Identical
description	description	Identical
likes	favorite-count	Equivalent
username	screen-name	Equivalent
Creation date	Created-at	Equivalent
website	url	Equivalent
Location	Location	Equivalent
Hashtags	Hashtags	Equivalent
Friends-count	Followers-count	Equivalent
Comments	Replies	Equivalent
–	location	Complementary
–	friends-count	Complementary
birthday	–	Complementary
“

Understanding these differences and similarities is crucial for businesses and researchers looking to make informed decisions based on social media analytics. For example, if a business is looking to expand its reach and grow its customer base, it may want to focus on increasing its number of followers on Twitter. Alternatively, if a researcher is looking to study social media sentiment on a specific topic, they may want to track the use of specific hashtags across both Facebook and Twitter.

Overall, mapping between Facebook attributes and Twitter attributes can help users tailor their social media strategies to each platform and make more informed decisions based on social media analytics. By understanding the similarities and differences between the attributes of each platform, users can extract and clean data more efficiently, optimize their social media presence, and drive better results for their business or research.”

“Referring to our multidimensional model dedicated to the On-Line Analytical Processing (OLAP) of data exchanged through social media, our motivation example is built upon the ‘Tweet Constellation’ proposed in Ben Kraiem et al. (2014).

“To perform a complete analysis on data extracted from Twitter and Facebook, we propose a constellation composed of two facts: User-Activity and a reflexive fact called Post Activity.

User-Activity-Fact: Corresponds to observations on user accounts and allows the analysis of the user activity over time. It contains data about the connections between users, such as friend/follower relationships. This fact could be used to track the growth of user networks over time. For this fact we define the two numerical measures as follows:
- Post_C: Number of posts of a user.
- Fre_C: Number of followings.
Post-Opinion-Fact: it is a reflexive fact. It models links between a post and the person concerned by the answer (answered person) and then allows participants and other readers to easily follow the exchange of posts (cf. Figure 1). Being reflexive, it links instances of the same entity. It is composed of :
- The content posted by users, such as text, images, and videos.
- A measure of type List of elements (Hashtags),
- Three Numeric measures:
- Nb-Sha-Retw characterizing the number of times a post was re-tweeted or the number of times the post was shared and to indicate the degree of importance of exchanged posts,
- Nb-Lik-Fav characterizing the number of times the fans have clicked on the button like of post or tweet,
- Nb-Com-Tw characterizing the number of times the users have written comments on post or status.

This fact directly involves opinion analysis, where data mining (DM) algorithms play a vital role in extracting valuable insights and predicting trends. Through the application of these algorithms, we can uncover useful information and categorize posts based on their sentiment, identifying whether they are positive, negative, or neutral. This enables a deeper study of user sentiments, allowing us to pinpoint the most positive, negative, and neutral posts. The analysis of such sentiments provides valuable knowledge for businesses and organizations to make informed decisions, improve customer experiences, and stay attuned to the ever-changing social media landscape.

“While loading dimension records that we have set for the modeling of social media data, we can identify four dimensions:

Users: Contains the primary key for each user along with their name, email address, and any other personal information.
Place: Contains geographic location data such as country, state, city, and zip code. This dimension could be used to link users to their respective locations.
Time: Contains time-related data such as year, month, day, and hour. This dimension could be used to track trends in user activity over time.
Source: Contains information about the source of data, such as whether it was collected from Facebook or Twitter.

Figure 1 depicts the proposed multidimensional model extended with some specifics and enriched with these extensions. The cardinality 0 of a reflexive fact means that a post is not necessarily an answer to another tweet or the post was not shared. The second specificity is relative to the possibility of having posts without any associated locality (absence of the PLACE dimension). This aspect is taken into account by our model. Indeed, we defined a relationship of type 1:0 between the fact Post-Activity and the PLACE dimension. This occurs when the user did not allow, during the configuration of his account, the identification of the place which he associated with posts.”

“To assess the effectiveness of our methodology, we have created a software prototype called OLAP4Tweets. This prototype was developed using JAVA and utilizes an ORACLE 10g database, known for providing a stable and reliable environment for handling data. Figure 2 provides a functional overview of the entire data warehousing process, which involves extracting data from Twitter and Facebook. The process is divided into five modules, each serving a specific function in the overall data warehousing procedure.

Data extraction and cleaning
Data mapping
Multidimensional schema design
Transforming and loading
Reporting and querying”

For our experiments, we consider the dataset obtained for the final show of The Voice France (a French music talent show for aspiring singer’s broadcasts by TF1 TV) which was held on June 3, 2023 at 18:00 GMT. We were able to crawl about half a million posts encompassing (501.500 posts) 3 hours starting from the beginning of the final show and collected from Twitter and Facebook. These posts are written in different languages

Once the data warehouse has been populated with values, OLAP queries could be executed. We now demonstrate the power of applying OLAP when solving specific social media-related analysis tasks. We start by studying the distribution of the loaded posts per language. For language recognition, we have used Language detection APIs, the one offered by JSON. When detected, the language information can be used for analysis and aggregation.

The decision maker is now interested in the variation of the number of users by their location. The results of this query are illustrated in Fig. 5. Based on this Figure, we find that there is a significant number of users tend to post comments on social media from the France and US regions.

The content of each post field is fundamental for the semantic analysis as they deliver valuable information about users and their opinions. We have used AlchemyAPI to semantically analyze the Tweet-Content. Table 2 shows the distribution of results for the Sentiment Analysis performed on the dataset of our experiment. Figure 6 depicts sentiments across the top contestants with a variety of talents during the final show.

Table 2

Sentiment Analysis statistics
Sentiment	Post count
Negative		39,532
Neutral		71,247
No Sentiment		65,996
Positive		324,725

To verify the developed environment, we implemented the DW model described in (Valêncio et al. 2020) as a point of reference. A thorough comparison between our approach and the DW approach proposed by (Valêncio et al. 2020) was carried out to assess the efficacy of each method in enabling valuable knowledge discovery through the utilization of Data Mining techniques.

The Data Mining algorithms were employed within each Data Warehouse using the software WEKA. These algorithms represent established techniques for extracting pertinent and valuable insights from data, as per the current state-of-the-art (Batrinca and Treleaven, 2015; Balazs and Velásquez, 2016; Ghani et al., 2018).

For Experiment 1, the focus was on the utilization of the Naïve Bayes (NB) algorithm. This algorithm was applied to categorize opinions from both Data Warehouses (DWs), utilizing the source of the post as a label. The classifier operates as a supervised method that, once trained, has the capability to deduce the social media platform from which a given piece of data originates. In order to assess the validity of the results, various metrics were taken into account: execution time measured in seconds, accuracy (as described by Eq. 1), precision (defined by Eq. 2) and recall (expressed in Eq. 3). These evaluation metrics were chosen in alignment with similar methodologies employed in related research within the scientific literature (Balazs and Velásquez, 2016). In the aforementioned equations, TP, FP, TN, and FN correspond to the counts of true positive results, false positive results, true negative results, and false negative results, respectively.

$$Accuracy=\frac{(TP+TN)}{(TP+TN+FP+FN)} \left(1\right)$$

$$Precision=\frac{TP}{(TP+FP)} \left(2\right)$$

$$Recall=\frac{TP}{(TP+FN)} \left(3\right)$$

Table 3 presents the obtained results. DW M represents the proposed data warehouse and DW V represents the data warehouse proposed by (Valêncio et al. 2020).

Table 3

Naïve Bayes results
Metric	DW M	DW V
Execution time (s)	35.8000	102.4900
Accuracy (%)	98.4000	96.3000
Precision (%)	97.1000	98.4000
Recall (%)	98.4000	97.2000

All experiments conducted over our approach have finished the execution in less than 30 seconds. On the other hand, the DW designed by (Valêncio et al. 2020) reached times greater than 100 sec. In terms of quality, the four experiments confirmed that the designed DW brought improvement. An important property is the problem of absence of pattern in modeling the DW. Those DW are highly variable depending on the context. The approach to build them depends on a specific problem. In addition, most of the literature works deals on building a model only for one social media. The model proposed by (Valêncio et al. 2020) had some issues related to the data redundancy. Still, it was taking into account only the business rules. With this approach, DM algorithms had their performance and quality prejudice, as shown in this paper. According to the algorithm used in experiment 1, the data organized in the developed model was better. In this experiment, the results showed a higher accuracy and higher recall. The precision, yet, is better in the DW presented by presented by (Valêncio et al. 2020), it means it was better in classifying the true positives, but it doesn’t take into account the true negatives.

Experiment 2 was executed utilizing the K-means algorithm, which was applied for clustering the data based on the source of the posts as labels. In this context, the value of K was defined as two, given the existence of only two potential social media platforms. The Euclidean distance metric was employed for this purpose. The evaluation encompassed an analysis of execution time measured in seconds, classification error rate, and the count of interactions, as per the approach outlined by (Hossny et al. 2018). In fact, experiment 2 aimed to compare the data clustering proficiency of both Data Warehouses (DWs) based on the origin of opinions. The outcomes of this technique are presented in Table 4. To compute the error, the origin of the opinion was provided as input.

Table 4

K-means results
Metric	DW M	DW V
Execution time (s)	0. 3000	2.2800
Error (%)	32.3571	57.2147
Number of interactions	24.0000	64.0000

In Experiment 2, the clustering technique yielded superior outcomes. Both performance and quality exhibited improvements. The results underscore that the algorithm excelled in partitioning the organized data derived from the developed model. Furthermore, the reduced error value was accompanied by a notably diminished computational cost in terms of iteration count, which was three times lower.

“In this paper, we applied the data warehousing technology to enable comprehensive analysis of massive data volumes generated by the Twitter and Facebook.

Our approach involved three key steps, the first of which was focused on data extraction and cleaning from social media platforms. Once we had extracted the data, we encountered the challenge of structural heterogeneity between source schemas. To address this issue, we developed a mapping process that allowed us to map the data from different sources onto a common schema. This involved identifying common data attributes across sources and mapping them to a standardized format. The next step involved designing a multidimensional schema for the data warehouse, which allowed us to structure the data in a way that facilitated analysis and reporting. This involved identifying key dimensions, such as time, location, and user, and organizing the data according to these dimensions. Finally, we created a multidimensional conceptual schema specific to social media data. This schema incorporated the mapping process we had developed earlier, as well as the dimensions and measures we had identified during the multidimensional schema design process. This allowed us to create a comprehensive and flexible data warehouse that could support a wide range of analysis and reporting tasks. Our conducted experiments demonstrated that our model is proficient in aiding analysts during the decision-making process, offering the advantage of swifter execution compared to related works.

Several perspectives for this work are possible. We suggest considering the implementation of a NoSQL database to handle issues concerning both the scarcity and the excess of attributes. Additionally, it would be valuable to explore the adaptation of the Data Warehouse (DW) to encompass content from various other social networks. This step holds significance despite the prevailing trend in related research, which predominantly focuses on individual platforms.

Author Contribution

In this collaborative effort, my contributions to the article encompassed the conceptualization and design of the research, data collection, and the drafting of the original manuscript. I was actively involved in the analysis of the data and contributed significantly to the intellectual content during the review and editing process. My role extended to providing critical insights and supervising various aspects of the project. Additionally, I played a key role in securing funding for the research. This Author Contributions Statement serves to transparently acknowledge my multifaceted involvement in the development of the article.Maha BEN KRAIEMAll authors reviewed the manuscript.

Ben Kraiem M., Feki J, Khrouf K, Ravat F, Teste O (2014). OLAP of the Tweets: From Modeling toward exploitation. 8th International Conference on Research Challenges in Information Science (IEEE RCIS’2014), May 28–30, 2014, Marrakesh, Morocco, pp. 45–55, ISBN #978-1-4799-2393-9.
Balazs, J.A. and J.D. Velásquez, 2016. Opinion mining and information fusion: A survey. Inform. Fus., 27: 95–110. DOI: 10.1016/j.inffus.2015.06.002
Batrinca, B. and P.C. Treleaven, 2015. Social media analytics: A survey of techniques, tools and platforms. Ai Society, 30: 89–116. DOI: 10.1007/s00146-014-0549-4
Bringay S, Béchet N, Bouillot F, Poncelet P, Roche M, Teisseire M (2011) Towards an On-Line Analysis of Tweets Processing, 22nd International Conference on Database and Expert Systems Applications, DEXA, Toulouse, France.
Chaudhuri S and U. Dayal (1997), Data Warehousing and OLAP for Decision Support. DOOD, pp. 33–34.
Cuzzocrea. A, C. De Maio, G. Fenza, V. Loia, Parente M (2016). OLAP analysis of multidimensional tweet streams for supporting advanced analytics. In: Proceedings of the 31st annual ACM symposium on applied computing. ACM, pp. 992–999
Gallinucci. E, M. Golfarelli, Rizzi. S (2013). Meta-stars: multidimensional modeling for social business intelligence. In: Proceedings of the sixteenth international workshop on Data warehousing and OLAP. ACM, pp 11–18
Gallinucci. E, M. Golfarelli, Rizzi. S. (2015). Advanced topic modeling for social business intelligence. Inf Syst 53:87–106
Ghani, N.A., S. Hamid, I.A.T. Hashem and E. Ahmed, 2018. Social media big data analytics: A survey. Comput. Human Behav., 101: 417–428. DOI: 10.1016/j.chb.2018.08.039
Girsang. AS, SM. Isa, N. Natasya, Ginzel ME. (2020). Implementation of journalist business intelligence in social media monitoring. System. https://doi.org/10.25046/aj0506182
Gutiérrez-Batista. K, MA. Vila, Martin-Bautista MJ (2021). Building a fuzzy sentiment dimension for multidimensional analysis in social networks. Appl Soft Comput 108:107390. https://doi.org/10.1016/j.asoc.2021.107390.
He, W., S. Zha and L. Li (2013). ‘Social media competitive analysis and text mining: a case study in the pizza industry’, International Journal of Information Management, 33, pp. 464–472.
Hossny, A.H., T. Moschuo, G. Osborne, L. Mitchell and N. Lothian, 2018. Enhancing keyword correlation for event detection in social networks using SVD and k-means: Twitter case study. Soc. Netw. Anal. Min., 8: 49–49. DOI: 10.1007/s13278-018-0519-9
Jenhani F and Gouider MS (2022) Large-scale system for social media data warehousing: the case of twitter-related drug abuse events integration. Int J Data Warehousing Min (IJDWM) 18(1):1–18. https://doi.org/10.4018/IJDWM.290890
Kimball R (1996) The data warehouse toolkit: practical techniques for building dimensional data warehouses”, John Wiley & Sons, ISBN 0-471-15337-0.
Kurnia PF, (2018). Business intelligence model to analyze social media information. Proced Comp Sci 135:5–14. https://doi.org/10.1016/j.procs.2018.08.144
Moalla I, A. Nabli, and Hammami, M. (2017). Integration of a multidimensional schema from different social media to analyze customers' opinions. In: 11th International conference on research challenges in information science (RCIS) (pp. 391–400). IEEE, DOI: https://doi.org/10.1109/RCIS.2017.7956564
Moalla I, A. Nabli, and Hammami, M. (2022). Data warehouse building to support opinion analysis in social media. Soc. Netw. Anal. Min. 12, 123 (2022). https://doi.org/10.1007/s13278-022-00960-2.
Rehman N, Mansmann S, Weiler A, Scholl M.H (2012) Building a Data Warehouse for Twitter Stream Exploration, ACM Fifteenth International Workshop on Data Warehousing and OLAP, DOLAP.
Rehman N, Weiler A, Scholl M.H (2013) OLAPing Social Media: The case of Twitter, IEEE/ACM International Conference on Advances in Social Networks Analysis and Mining (ASONAM 2013)
Valêncio CR, LMM. Silva, W. Tenório, GFD. Zafalon, AC. Colombini, Fortes MZ (2020). Data warehouse design to support social media analysis in a big data environment. J Comput Sci. https://doi.org/10.3844/jcssp.2020.126.136

No competing interests reported.

Download PDF

Version 1

posted

You are reading this latest preprint version

Building a Data Warehouse for Social Media Analysis: The case of Twitter and Facebook

Status:

Version 1

Abstract

Figures

1 INTRODUCTION

2 RELATED WORK

3 OVERVIEW OF THE PROPOSED APPROACH

4 DATA EXTRACTION AND CLEANING

5 DATA MAPPING FROM SOCIAL MEDIA

6 MULTIDIMENSIONAL SCHEMA CONCEPTS

7 EXPERIMENTAL RESULTS

8 CONCLUSION

Declarations

Author Contribution

References

Additional Declarations

Status:

Version 1