Data Warehouse Development and Management: Practices of Some Large Companies

In a short time, the data warehouse (DW) technology took an important place in the academic and industrial landscapes. This place materialized in the large majority of engineering and management schools that adopted it in their curriculum and in the small, medium-size and large companies that enhanced their decision making capabilities thanks to it. The 1990s saw the advent of conferences such as DaWaK and DOLAP that carried the acronyms DW and OLAP in their titles. Then, all of a sudden, this technology has been upset by the arrival of Big Data. Consequently, those actors have replaced DW and OLAP by Big Data Analytics. We are well placed to assert that this brutal move may have a negative impact on schools, academia, and industry. This technology is not dead, today’s context, with the connected world and Web of Data, is more favorable than when building DW merely stemmed from company internal sources. In this invited paper, we attempt to answer the following question: how does DW technology interact with Linked Open Data (LOD)? To answer the question, we provide a complete vision to augment the traditional DW with LOD, to capture and quantify the added value generated through this interaction. This vision covers the main steps of the DW life-cycle. This value is estimated through two different perspectives: (i) a source-oriented vision, by calculating the rate of the DW augmentation in terms of multidimensional concepts and instances, and (ii) a goal-oriented vision where the value is calculated according to the ability of the DW to estimate the performance levels of defined goals that reflect the strategy of a company, using the defined DW of the case study of a leading Algerian company.

A company, majorly company that active in commercial (profit orientation) need to analyze their sales performance. By analyzing sales performance, company can increase their sales performance. One of method to analyze sales performance is by collecting historical data that relates to sales and then process that data so that produce information that show company sales performance. A data warehouse is a set of data that has characteristic subject oriented, time variant, integrated, and nonvolatile that help company management in processing of decision making. Design of data warehouse is started from collecting data that relate to sales such as product, customer, sales area, sales transaction, etc. After collecting the data, next is data extraction and transformation. Data extraction is a process f or selecting data that will be loaded into data warehouse. Data transformation is making some change to the data afte r extracted to be more consistent. After transformation processing, data are loaded into data warehouse. Data in data warehouse is processed by OLAP (On Line Analytical Processing) to produce information. Information that are produced from data processing by OLAP are chart and query reporting. Chart reporting are sales chart based on cement type, sales chart based on sales area, sales chart based on plant, monthly and year ly sales chart, and chart based on customer feedback. Query reporting are sales based on cement type, sales area, plant and customer. Keywords: Data warehouse; OLAP; Sales performance analysis; Ready mix market

Purpose: This research examines the utilization of machine learning (ML) in data warehousing systems and the extent to which it will transform business intelligence and analytics. It aims to know how ML improves conventional data warehousing systems to support prediction and forecasting. Methodology: This research uses a literature review together with a case analysis. It discusses the issues that may arise when implementing Machine Learning models with data warehouses, such as issues to do with data quality, scalability, and real-time processing. The work examines integration patterns like in-database ML computations, feature stores, and MLOps. Case studies are discussed to demonstrate the value of the use of integration in different fields. Findings: Combining machine learning with DW systems provides significant advantages in different fields. This synergy boosts analytical aptitudes, allowing the organization to go a notch higher than descriptive analytics in predictive and prescriptive analytics. However, such a decision is not simple as it has implementation matters such as data quality problems, scalability, and real-time processing problems. Integration best practices include in-database machine learning processing, a feature store, and proper MLOps practices. Real-life examples from the healthcare industry, banking and financial services, retail, and manufacturing industries show that this integration brings operational enhancements for the business and positive effects on customers and overall organizational performance. Recommendations: This work offers a useful framework for studying and constructing the integration of ML into the data warehouse, which is a transition from the theoretical perspective to the actual one. It provides practical advice for organizations and stresses the integration strategies related to the business goals, data quality, the choice of architecture, security, and training. This study also envisions future trends such as edge computing, AutoML, and Explainable AI and offers a guide on how to harness this technological complementarity. The generated insights help decision-makers and practitioners understand the possibilities of leveraging ML-data warehouse integration as a strategic asset in the contemporary business environment shifting towards data-driven approaches.

Information systems allow companies to collect a large number of operational and transactional data. Data warehouses are increasingly used by organisations to extract concise information supporting decision processes. However, data warehouse design strategies as well as the structure and content of the original database largely influence the effectiveness of such tools. Our research is focused on data-driven approaches, and in this paper, we present a solution that, based on a set of metrics measuring different characteristics of the original data sources, effectively supports the creation of data warehouses. Moreover, combining the indexes computed by our metrics on selected dimensions and measures, it is also possible to derive quantitative information on the design quality of the final data warehouse. To demonstrate the effectiveness of our solution, we briefly present the results obtained from the analysis of a selling data warehouse derived from a real-word ERP system.

The popularity of data warehouses for data analysis has grown tremendously. Data warehouse systems have emerged as the core of management information systems. Data warehouse is part of a larger infrastructure that includes legacy data sources, external data sources, data acquisition software, a repository, analytical tools, and user interface. The difficulties of data warehouse implementations have been widely cited in the literature but research on the factors for initial and ongoing data warehouse implementation success is rare and fragmented. Through a comprehensive review of the literature, 10 factors were found to be critical to DW implementation success — Organizational, Technical, Project, Environmental, Infrastructure, Information Quality, System Quality, Service Quality, Relationship Quality, and Net Benefits. A proposed research model is developed in this paper to determine the impact of quality and success factors on the implementation of data warehouse by adapting the updated (2003) DeLone and McLean Information System Success Model, new dimensions are proposed to the model.

Very little effort has been put into developing a data warehouse for research literature mining due to research literature being semi-structured in nature where there is not any definitive design framework for such case. This study proposes a feasible data warehouse framework that can support the semi-structured research literature mining. Furthermore, this study also determines the core functional components that are unexplored yet required to realize the implementation of the design. In order to identify the challenges of implementing a semi-structured data warehouse, a prototype data warehouse was developed using the real world data sources of research literature published in “Computer in Human Behaviour” and “Information Systems” journals from the year 2008 to 2017 as the sample semi-structured data for proof of concept. The study results show that the designed data warehouse framework was able to support semi-structured data mining for revealing hidden pattern or trend of certain aspects of the research literature data.

Research in data warehousing and OLAP has produced important technologies for the design, management and use of information systems for decision support. Much of the interest and success in this area can be attributed to the need for software and tools to improve data management and analysis given the large amounts of information that are being accumulated in corporate as well as scientific databases.However, even though the high maturity of these technologies, new data needs or applications currently run at companies not only demand more capacity, but also new methods, models, techniques or architectures to satisfy these new needs. Some of the hot topics in data warehouses (DWs) include distributed DWs, advanced OLAP for business intelligence, web warehouses, DWs for new applications such as XML documents, stream data, spatial or GIS data or biomedical data. Moreover, there are other aspects very developed in other software areas such as security or quality, which still remain uncovered by current design methods or technologies for DWs.Like the previous successful DOLAP workshops held in conjunction with CIKMs, the eighth edition of the Workshop on Data Warehousing and OLAP (DOLAP'05) aims to synergistically connect the research community and industry practitioners. It provides an international forum where both researchers and practitioners can share their findings in theoretical foundations, current methodologies, and practical experiences. This year, DOLAP'05 will be specially focused on new research directions and emerging application domains in the areas of data warehousing and OLAP.This year, we received papers from 18 different countries distributed over all continents such as The Netherlands, France, Spain, Israel, Korea, USA, Canada and Argentine. We received 31 submissions and, after a careful review, only 12 papers were selected by the Program Committee, making an acceptance rate of 38.7%.These proceedings contain the papers selected for presentation at the workshop. The accepted papers were presented in 5 sessions: (i) querying OLAP databases, (ii) data warehouse models, (iii, iv) data warehouse design, and (v) query processing and view maintenance. A keynote address was given by Jens Lechtenburger on Schema transformations. We hope that these proceedings will serve as a valuable reference for data warehousing and OLAP researchers and practitioners.

Extraction-transformation-loading (ETL) process in data warehouse development perform data extraction from various resources, transform the data into suitable format and loadit into data warehouse storage. In the ETL process, there is data cleansing process function that handles redundancy, inconsistency and integrity data. ETL process will move data from the source to the integration layer (data store in data warehouse). In the integration layer, the data can be grouped into smaller scope and more specific for the requirement in other repositories called data marts. Reporting program of data warehouse will be associated with a data mart as its data source. In this research, the data warehouse is built to handle the ETL process. The data warehouse build metadata to support the process. The metadata construction for ETL processes will lead to ETL programs with high degree of reusability. The conclusion from this research is the use of dynamic ETL process (using metadata ETL) is required when ETL process is dealing with the operational system that still unstable and likely to change the database schema. Dynamic ETL process is also needed to address the increase requirement for report from the users.

The company's performance must be continuously improved to be able to face the increasingly fierce global competition. The improvement of the company's performance can be made by implementing supply chain practices. The implementation of supply chain practice is a policy set by the company's top management by making the system integrated. Integration of all functions, internally and externally, is done by using integrated information technology. This study takes data from companies in East Java, engaged in machinery that has a workforce of more than 100 people categorized as medium and large companies. Respondents were set on employees with a minimum of 2 yr of working experience and a middle manager position. There were 55 respondents in this study, with data analysis using partial least square (PLS) to test the research hypothesis. The result of the data processing found that the commitment of top management affects the supply chain practices and the use of information technology. This is because the management has shown its commitment by providing the necessary human resources. The top management commitment directly influences the company's operational performance. The use of information technology directly affects supply chain practices and the company's operational performance. Supply chain practices affect the company's operational performance. This is because the company has carried out the process of information sharing with the company's suppliers to control and facilitate the procurement of quality raw materials.

Organizations depend increasingly on information systems, which rely upon databases and data warehouses (DWs), which need increasingly more quality and security. Generally, we have to deal with sensitive information such as the diagnosis made on a patient or even personal beliefs or other sensitive data. Therefore, a final DW solution should consider the final users that can have access to certain specific information. Unfortunately, methodologies that incorporate security are based on an operational environment and not on an analytical one. Therefore, they do not include security into the multidimensional approaches to work with DWs. In this chapter, we present a comparison of six secure-systems design methodologies. Next, an extension of the UML that allows us to specify main security aspects in the multidimensional conceptual modeling is proposed, thereby allowing us to design secure DWs. Finally, we present how the conceptual model can be implemented with Oracle Label Security (OLS10g).

Conceptual modeling is widely recognized to be the necessary foundation for building a database that is well-documented and fully satisfies the user requirements. In particular, from the designer point of view the availability of a conceptual model provides a higher level of abstraction in describing the warehousing process and its architecture in all its aspects. Typically conceptual models rely on a graphical notation that facilitates writing, understanding, and managing conceptual schemata by both designers and users. The Entity/Relationship (E/R) model (Chen, 1976) is widespread in the enterprises as a conceptual formalism to provide standard documentation for relational information systems; nevertheless, as E/R is oriented to support queries that navigate associations between data rather than synthesize them, it is not well-suited for data warehousing (Kimball, 1998). Actually, the E/R model has enough expressivity to represent most concepts necessary for modeling a Data Warehouse (DW); on the other hand, in its basic form, it is not able to properly emphasize the key aspects of the multidimensional model, so that its usage for DWs is expensive from the point of view of the graphical notation and not intuitive (Rizzi, 2006). Some designers claim that star schemata are expressive enough for conceptual modeling. Actually, a star schema is just a (denormalized) relational schema, so it merely defines a set of relations and integrity constraints. Using star schema for conceptual modeling is like starting to build a complex software by writing the code, without the support of any static, functional, or dynamic model, which typically leads to very poor results from the points of view of adherence to user requirements, maintenance, and reuse. For all these reasons, in the last few years the research literature has proposed several original approaches for modeling a DW, some based on extensions of known conceptual formalisms (e.g. E/R, Unified Modeling Language (UML)), some based on ad hoc ones. Remarkably, a comparison of the different models made by Abello (2006) pointed out that, abstracting from their graphical form, the core expressivity is similar, thus proving that the academic community reached an informal agreement on the required expressivity. This paper discusses the expressivity of an ad hoc conceptual model, the Dimensional Fact Model (DFM), in order to let the user verify the usefulness of a conceptual modeling step in DW design. After a brief listing of the main conceptual model proposals, the basic and advanced features in DW conceptual modeling are introduced and described by examples. Finally, the current trends in DW conceptual modeling are reported and the conclusions are drawn.

As necessidades de informações para tomadas de decisão ocorrem em qualquer tipo de empresa. Informações de baixa qualidade podem prejudicar estas decisões. Empresas de manufatura baseiam muitas decisões nas informações sobre a produção realizada, que é gerada pelo processo de controle da produção. Os sistemas de informação normalmente utilizados para suporte a este processo são limitados para a geração de informações abrangentes e integradas. O objetivo deste artigo é apresentar a proposta de um Data Warehouse (DW) para geração de informações de suporte ao processo de controle da produção, com a garantia de melhoria na qualidade da informação disponibilizada aos usuários. Para isto, serão analisadas quais informações devem ser disponibilizadas, com base no alinhamento entre o desenvolvimento do DW e a estratégia de manufatura da empresa. O projeto do DW será baseado em algumas atividades que garantem a qualidade da informação. Como resultado, será apresentada uma proposta de melhoria da qualidade da informação no controle da produção como uma seqüência de etapas para o desenvolvimento do DW.

Efficient maintenance of basic statistical functions in data warehouses

Data is an important asset and a fundamental requirement for building valuable information for organizations. Association of Information Systems Students of Unsika (Himsika) as a university organization provides many events to develop student’s academic and professional skills. A post-event evaluation through a feedback survey was conducted and stored in Google Sheets spreadsheet format. However, the current analysis process using spreadsheets lacks standardization, making it difficult to compare satisfaction rates over time and between events. Additionally, the lack of standardization leads to semi-structured data on spreadsheets, with varying question formats and meanings. To address these limitations, implementing a centralized data warehouse is proposed as a solution. The data warehouse would provide a structured and standardized approach to analyzing event feedback, enabling better comparisons and evaluation of management quality within Himsika. The research aims to design a data warehouse that supports multidimensional analysis. As a way to simplify and optimize analytical queries, the data structure is standardized in the data warehouse. The Four-step Dimensional Design method is applied in designing dimensional modeling on the data warehouse, consisting of four stages including selecting the business process, declaring the grain, identifying the dimensions, and identifying the facts. The design process resulted in 4 dimensions of events, dim_instances, dim_degree_programs, and dim_professions, and a fact table called fact_rates_by_responses. Overall, the proposed data warehouse and dimensional modeling approach aim to enhance the analysis and evaluation of Himsika’s events.

This case highlights factors that provided the impetus for changing a successful EIS into a data warehouse at the VF Corporation. The data warehouse was developed to aid JeansWear, a division of VF, with its point-of-sale/replenishment activities. The data warehouse provides greater reporting and OLAP capabilities, giving replenishment analysts a detailed and synthetic view of the marketplace. It is estimated that about $100 million in 1998 alone might be attributed to the improved Replenishment decision making due to the data warehouse. The case discusses the basic concepts and architecture of this data warehouse and outlines the development process and the problems that the development team had to overcome. It also examines the essential role that this data warehouse is currently playing in the success of VF Corporation. Finally, the case outlines and discusses a number of factors that should be considered and questions that should be asked prior to initiation of a data warehouse project in order to assure a successful outcome.