Millions Of Rows Research Articles

Comparison of Apache SparkSQL and Oracle Performance: Case Study of Data Cleansing Process

A dataset with good quality is a valuable asset for a company. The data can be processed into information to help companies improve decision-making. However, the data increased more and more over time to decrease data quality. Thus, good data management is important to keep data quality meeting company standards. One of the efforts that can be done is conducting data cleansing to clean data from errors, inaccuracies, duplication, format discrepancies, etc. Apache Spark is an engine that can analyze large amounts of data. Oracle Database is a database management system used to manage databases. Both have their own reliability and can be used to analyze SQL-shaped data. This study compared Spark and Oracle performance based on query processing time. Both were tested on queries used to perform data cleansing of millions of rows of the dataset. The research focuses on finding out Spark and Oracle's performance through quantitative analysis. The results of this study showed that there were differences in query processing times on both tools. Apache Spark is rated better because it has a relatively faster query processing time than Oracle Database. It can be concluded that Oracle is more reliable in storing complex data models than in analyzing large data. For future research, it is suggested to add other comparison aspects such as memory and CPU usage. The researchers can also consider using query optimization techniques to enrich query experiments.

Filtering non-balanced data using an evolutionary approach

Abstract Matrices that cannot be handled using conventional clustering, regression or classification methods are often found in every big data research area. In particular, datasets with thousands or millions of rows and less than a hundred columns regularly appear in biological so-called omic problems. The effectiveness of conventional data analysis approaches is hampered by this matrix structure, which necessitates some means of reduction. An evolutionary method called PreCLAS is presented in this article. Its main objective is to find a submatrix with fewer rows that exhibits some group structure. Three stages of experiments were performed. First, a benchmark dataset was used to assess the correct functionality of the method for clustering purposes. Then, a microarray gene expression data matrix was used to analyze the method’s performance in a simple classification scenario, where differential expression was carried out. Finally, several classification methods were compared in terms of classification accuracy using an RNA-seq gene expression dataset. Experiments showed that the new evolutionary technique significantly reduces the number of rows in the matrix and intelligently performs unsupervised row selection, improving classification and clustering methods.

Change management: A framework for measuring and implementing organisational change

In 2018, Mayo Clinic completed the largest transformational project in its 150-year history. Mayo Clinic was prepared to take on the implementation of new software systems (Epic Systems Corp) as an integrated electronic health record (EHR) and revenue cycle management (RCM) system across all sites of the enterprise. Three foundational teams were launched for this project: leadership, project management and change management. In a project such as this, a common pitfall is to focus completely on the system and lose sight of the end users. For Mayo Clinic, the people side of change was at the forefront of the project. A robust change management team was budgeted, staffed and engaged with an experienced outside consulting partner. A goal for the change management team centred on the best way to measure the change management impact and the degree to which it was making a positive contribution in driving successful adoption of the new EHR and RCM system. With literature lacking in this area, the team moved forward with courage and quickly learned that the easy part was the development of a survey with questions mapped to the six components of the change management strategy and the phases of the awareness, desire, knowledge, ability, reinforcement (ADKAR) Model (Prosci Inc). The hard part was building the infrastructure to visually display millions of rows of data that needed to be positioned, filtered and analysed for all affected locations. The authors share the methods to measure change and explain the importance of developing a comprehensive and robust means of gathering and displaying large volumes of change management data at the project onset. This paper prepares the reader to confidently develop an organisational framework to measure and report the effectiveness of a change management strategy.

NOAH

Spreadsheet systems are by far the most popular platform for data exploration on the planet, supporting millions of rows of data. However, exploring spreadsheets that are this large via operations such as scrolling or issuing formulae can be overwhelming and error-prone. Users easily lose context and suffer from cognitive and mechanical burdens while issuing formulae on data spanning multiple screens. To address these challenges, we introduce dynamic hierarchical overviews that are embedded alongside spreadsheets. Users can employ this overview to explore the data at various granularities, zooming in and out of the spreadsheet. They can issue formulae over data subsets without cumbersome scrolling or range selection, enabling users to gain a high or low-level perspective of the spreadsheet. An implementation of our dynamic hierarchical overview, NOAH, integrated within DataSpread, preserves spreadsheet semantics and look and feel, while introducing such enhancements. Our user studies demonstrate that NOAH makes it more intuitive, easier, and faster to navigate spreadsheet data compared to traditional spreadsheets like Microsoft Excel and spreadsheet plug-ins like Pivot Table, for a variety of exploration tasks; participants made fewer mistakes in NOAH while being faster in completing the tasks.

A Detailed Study of Distributed Indexed Search Techniques using SOLR

For any web application running on RDBMS databases as the backend, it might be a huge performance impact if a search needs to be performed on a table with millions of rows or if a query needs to be executed which joins multiple tables. In general, such kind of backend services make the website extremely slow. Document based reverse indexing can be a useful solution in these cases. SOLR is a standalone enterprise search server with a REST-like API. It has major features which include powerful full-text search, hit highlighting, faceted search, near real-time indexing, dynamic clustering, database integration, NoSQL features and rich document (e.g., Word, PDF and more) parsing, geospatial search, Security built in. Databases and SOLR have complementary strengths and weaknesses. SQL supports very simple wildcard-based text search with some simple normalization like matching upper case to lowercase. The problem is that these are full table scans. In SOLR all searchable words are stored in an "inverse index based", which searches orders of magnitude faster. However, designing this framework is quite challenging. This paper discusses the techniques that are highly reliable, scalable and fault tolerant which can help in setting up the distributed indexing, replication and load-balanced querying with a centralized configuration.

Design and Analysis of a Relational Database for Behavioral Experiments Data Processing

In this paper, the results of a comparative analysis between different approaches to experimental data storage and processing are presented. Several studies related to the problem and some methods for solving it have been discussed. Different types of databases, ways of using them and the areas of their application are analyzed. For the purposes of the study, a relational database for storing and analyzing a specific data from behavioral experiments was designed. The methodology and conditions for conducting the experiments are described. Three different indicators were analyzed, respectively: memory required to store the data, time to load the data from an external file into computer memory and iteration time across all records through one cycle. The obtained results show that for storing a large number of records (in the order of tens of millions of rows), either dynamic arrays (stored on external media in binary file format), or an approach based on a local or remote database management system can be used. Regarding the data loading time, the fastest approach was the one that uses dynamic arrays. It outperforms significantly the approaches based on a local or remote database. The obtained results show that the dynamic arrays and the local data sets approaches iterated much faster across all data records than the remote database approach. The paper concludes with proposal for further developments towards using of web services.

TOPCAT: Desktop Exploration of Tabular Data for Astronomy and Beyond

TOPCAT, the Tool for OPerations on Catalogues And Tables, is an interactive desktop application for retrieval, analysis and manipulation of tabular data, offering a powerful and flexible range of interactive visualization options amongst other features. Its visualization capabilities focus on enabling interactive exploration of large static local tables - millions of rows and hundreds of columns can easily be handled on a standard desktop or laptop machine, and various options are provided for meaningful graphical representation of such large datasets. TOPCAT has been developed in the context of astronomy, but many of its features are equally applicable to other domains. The software, which is free and open source, is written in Java, and the underlying high-performance visualisation library is suitable for re-use in other applications.

A computational experience for automatic feature selection on big data frameworks

The classification rule system is one of the predictive analytical techniques used in Big Data problems, where finding datasets with millions of rows but also with dozens of variables (attributes) is common. Classifica tion rule systems consist of rule sets which have a so-called antecedent (variable or set of variables that can be numeric or nominal) and a consequent (target variable, provided nominal). If the antecedent variables are numerical, many generator algorithms of classification rules employ traditional methods of automatic feature selection, based on techniques already established in the scientific field, such as discriminant analysis or cluster analysis. In this paper, the authors propose the comparison of their own method of feature selection and classifi cation, RBS (originally designed to manage only nominal variables) and classical methods of feature selection. After the formal definition of our own method, this paper presents the design of a computing experience that allows a qualitative and quantitative comparison of the adapted RBS and other methods for feature selection. Finally, optimal conditions of application of each method are discussed and future research areas in the field of automatic feature selection are identified.

Mining billion-scale tensors: algorithms and discoveries

How can we analyze large-scale real-world data with various attributes? Many real-world data (e.g., network traffic logs, web data, social networks, knowledge bases, and sensor streams) with multiple attributes are represented as multi-dimensional arrays, called tensors. For analyzing a tensor, tensor decompositions are widely used in many data mining applications: detecting malicious attackers in network traffic logs (with source IP, destination IP, port-number, timestamp), finding telemarketers in a phone call history (with sender, receiver, date), and identifying interesting concepts in a knowledge base (with subject, object, relation). However, current tensor decomposition methods do not scale to large and sparse real-world tensors with millions of rows and columns and `fibers.' In this paper, we propose HaTen2, a distributed method for large-scale tensor decompositions that runs on the MapReduce framework. Our careful design and implementation of HaTen2 dramatically reduce the size of intermediate data and the number of jobs leading to achieve high scalability compared with the state-of-the-art method. Thanks to HaTen2, we analyze big real-world sparse tensors that cannot be handled by the current state of the art, and discover hidden concepts.

Fast foreign-key detection in Microsoft SQL server PowerPivot for Excel

Microsoft SQL Server PowerPivot for Excel, or PowerPivot for short, is an in-memory business intelligence (BI) engine that enables Excel users to interactively create pivot tables over large data sets imported from sources such as relational databases, text files and web data feeds. Unlike traditional pivot tables in Excel that are defined on a single table, PowerPivot allows analysis over multiple tables connected via foreign-key joins. In many cases however, these foreign-key relationships are not known a priori, and information workers are often not be sophisticated enough to define these relationships. Therefore, the ability to automatically discover foreign-key relationships in PowerPivot is valuable, if not essential. The key challenge is to perform this detection interactively and with high precision even when data sets scale to hundreds of millions of rows and the schema contains tens of tables and hundreds of columns. In this paper, we describe techniques for fast foreign-key detection in PowerPivot and experimentally evaluate its accuracy, performance and scale on both synthetic benchmarks and real-world data sets. These techniques have been incorporated into PowerPivot for Excel.

Shared-memory and shared-nothing stochastic gradient descent algorithms for matrix completion

We provide parallel algorithms for large-scale matrix completion on problems with millions of rows, millions of columns, and billions of revealed entries. We focus on in-memory algorithms that run either in a shared-memory environment on a powerful compute node or in a shared-nothing environment on a small cluster of commodity nodes; even very large problems can be handled effectively in these settings. Our ASGD, DSGD-MR, DSGD++, and CSGD algorithms are novel variants of the popular stochastic gradient descent (SGD) algorithm, with the latter three algorithms based on a new "stratified SGD" approach. All of the algorithms are cache-friendly and exploit thread-level parallelism, in-memory processing, and asynchronous communication. We investigate the performance of both new and existing algorithms via a theoretical complexity analysis and a set of large-scale experiments. The results show that CSGD is more scalable, and up to 60 % faster, than the best-performing alternative method in the shared-memory setting. DSGD++ is superior in terms of overall runtime, memory consumption, and scalability in the shared-nothing setting. For example, DSGD++ can solve a difficult matrix completion problem on a high-variance matrix with 10M rows, 1M columns, and 10B revealed entries in around 40 min on 16 compute nodes. In general, algorithms based on SGD appear to perform better than algorithms based on alternating minimizations, such as the PALS and DALS alternating least-squares algorithms.

Fast Methods for Denoising Matrix Completion Formulations, with Applications to Robust Seismic Data Interpolation

Recent SVD-free matrix factorization formulations have enabled rank minimization for systems with millions of rows and columns, paving the way for matrix completion in extremely large-scale applications, such as seismic data interpolation. In this paper, we consider matrix completion formulations designed to hit a target data-fitting error level provided by the user, and we propose an algorithm called LR-BPDN that is able to exploit factorized formulations to solve the corresponding optimization problem. Since practitioners typically have strong prior knowledge about target error level, this innovation makes it easy to apply the algorithm in practice, leaving only the factor rank to be determined. Within the established framework, we propose two extensions that are highly relevant to solving practical challenges of data interpolation. First, we propose a weighted extension that allows known subspace information to improve the results of matrix completion formulations. We show how this weighting can be used in the context of frequency continuation, an essential aspect to seismic data interpolation. Second, we propose matrix completion formulations that are robust to large measurement errors in the available data. We illustrate the advantages of LR-BPDN on collaborative filtering problems using the MovieLens 1M and 10M and Netflix 100M datasets. Then we use the new method, along with its robust and subspace reweighted extensions, to obtain high-quality reconstructions for large-scale seismic interpolation problems with real data, even in the presence of data contamination.

Scuba

Facebook takes performance monitoring seriously. Performance issues can impact over one billion users so we track thousands of servers, hundreds of PB of daily network traffic, hundreds of daily code changes, and many other metrics. We require latencies of under a minute from events occuring (a client request on a phone, a bug report filed, a code change checked in) to graphs showing those events on developers' monitors. Scuba is the data management system Facebook uses for most real-time analysis. Scuba is a fast, scalable, distributed, in-memory database built at Facebook. It currently ingests millions of rows (events) per second and expires data at the same rate. Scuba stores data completely in memory on hundreds of servers each with 144 GB RAM. To process each query, Scuba aggregates data from all servers. Scuba processes almost a million queries per day. Scuba is used extensively for interactive, ad hoc, analysis queries that run in under a second over live data. In addition, Scuba is the workhorse behind Facebook's code regression analysis, bug report monitoring, ads revenue monitoring, and performance debugging.

Feature Article—Merging AI and OR to Solve High-Dimensional Stochastic Optimization Problems Using Approximate Dynamic Programming

We consider the problem of optimizing over time hundreds or thousands of discrete entities that may be characterized by relatively complex attributes, in the presence of different forms of uncertainty. Such problems arise in a range of operational settings such as transportation and logistics, where the entities may be aircraft, locomotives, containers, or people. These problems can be formulated using dynamic programming but encounter the widely cited “curse of dimensionality.” Even deterministic formulations of these problems can produce math programs with millions of rows, far beyond anything being solved today. This paper shows how we can combine concepts from artificial intelligence and operations research to produce practical solution methods that scale to industrial-strength problems. Throughout, we emphasize concepts, techniques, and notation from artificial intelligence and operations research to show how these fields can be brought together for complex stochastic, dynamic problems.

SIGEST

In discussions of the tremendous advances in scientific computation over the past few decades, it has often been written and demonstrated that as large as the increases in computer speed have been, they have been matched by improvements from more efficient algorithms. For many problem classes, over the last two to four decades, each has led to speedups of many, many orders of magnitude. The SIGEST paper in this issue, from the SIAM Journal on Scientific Computing, is an example of this tremendous type of algorithmic improvement. It also is an excellent example of how increasingly significant algorithmic advances are obtained by drawing upon a broad range of sophisticated techniques. In “On Large-Scale Diagonalization Techniques for the Anderson Model of Localization” by O. Schenk, M. Bollhöfer, and R. Römer, the authors demonstrate a greatly improved computational approach for an important, common problem: computing a few interior eigenvalues and eigenvectors of large, sparse, real symmetric, and highly indefinite matrices. By “large” they mean matrices with millions to hundreds of millions of rows and columns. The computational improvements that are demonstrated extend to a factor of a thousand or more over previous approaches, while keeping memory requirements modest. The application class is the Anderson model of localization, an important problem in quantum physics with a wide range of applications, and it appears likely that the approach can be applied successfully to large symmetric indefinite eigenvalue problems more generally. An interesting aspect of the paper is that is draws heavily upon recent advances in solving large sparse symmetric indefinite systems of linear equations. Solution of such systems is central to each of the eigenvalue solvers that the paper considers, two Lanczos-based methods and the Jacobi–Davidson method. In considering how to solve the linear systems in a manner that exploits sparsity as well as symmetry, the paper combines a diverse range of topics that includes preprocessing the matrix with symmetric weighted matchings, solving the linear system with Krylov subspace methods, and accelerating the linear system solution with multilevel preconditioners based upon incomplete factorizations. This makes for a very fitting paper for SIAM Review, highlighting a variety of important topics in numerical computation. We expect that a wide range of SIAM readers will find this to be a very readable paper that provides an interesting glimpse into a range of modern research approaches.

Policies for Caching OLAP Queries in Internet Proxies

The Internet now offers more than just simple information to the users. Decision makers can now issue analytical, as opposed to transactional, queries that involve massive data (such as, aggregations of millions of rows in a relational database) in order to identify useful trends and patterns. Such queries are often referred to as Online-analytical processing (OLAP). Typically, pages carrying query results do not exhibit temporal locality and, therefore, are not considered for caching at Internet proxies. In OLAP processing, this is a major problem as the cost of these queries is significantly larger than that of the transactional queries. This paper proposes a technique to reduce the response time for OLAP queries originating from geographically distributed private LANs and issued through the Web toward a central data warehouse (DW) of an enterprise. An active caching scheme is introduced that enables the LAN proxies to cache some parts of the data, together with the semantics of the DW, in order to process queries and construct the resulting pages. OLAP queries arriving at the proxy are either satisfied locally or from the DW, depending on the relative access costs. We formulate a cost model for characterizing the respective latencies, taking into consideration the combined effects of both common Web access and query processing. We propose a cache admittance and replacement algorithm that operates on a hybrid Web-OLAP input, outperforming both pure-Web and pure-OLAP caching schemes

Integrated Cheminformatics and Bioinformatics

For every experimental compound in the drug discovery pipeline, pharmaceutical companies generate huge amounts of data: chemical and physical data derived from various analytical techniques, and biological data that flow out of large-scale screening programs as well as lead optimization work. Drug discovery efforts have been hampered not by a lack of lead compounds or a dearth of experimental data, but by the need for effective and efficient computational tools to collect, store, manipulate, and analyze large amounts of data. Scientists in many of the major pharmaceutical and biotechnology companies, including GlaxoSmithKline, Aventis, AstraZeneca, Hoffmann-La Roche, Merck, Novartis, Millennium, Exelixis, and Immunex, Cytokinetics, Evotec and Monsanto are using ActivityBase (Figure 1), an integrated data management system, to collect and analyze data generated by high throughput screening (HTS), to store chemical structures and register novel compounds, and to integrate cheminformatic and bioinformatic datasets. ActivityBase manages data produced by HTS (with sustained screening volumes exceeding 30,000 to 40,000 wells/working day) and ultra-HTS (sustained volumes of greater than 100,000 wells/working day) and some companies are populating ActivityBase databases at the rate of approximately 20 million data points per six months. Many operational databases exceed tens of millions of rows, and the software’s search engine can respond to typical queries in a matter of seconds. An abundance of data does not necessarily add value to an experimental compound. The data do not imply therapeutic efficacy, infer bioavailability, predict toxicity, or suggest drug-like properties. Successful discovery research depends on the ability to integrate diverse datasets from multiple sources and to extract information from raw data. It is this information that will guide and expedite decision-making, improve productivity, and add value. It is this information that will allow a company to decide whether to pursue a lead compound or to “fail” it early in the discovery process. ActivityBase is based on IDBS’ generic data model designed for discovery research and can capture, manage, and store data from biological, chemical, and robotic systems. The ActivityBase 5.0 Suite seamlessly integrates cheminformatic and bioinformatic data. It provides the framework for converting data into information that can be applied to lead discovery and optimization processes. (Figure 2) New functionalities introduced in version 5.0 enhance the flexibility of data collection and analysis and expand data integration capabilities. Joining AssayBase, which manages biological data are three new software modules: · StructureBase for registering chemical compounds and searching molecular structures and related physicochemical data. (Figure 3) · ReactionBase for storing, managing, and searching chemical reactions and reaction schemes. (Figure 4) · Natural Products for managing the process of isolating active compounds from natural materials; it generates a genealogic trail that tracks the derivation of new chemical compounds from natural products.