Design and Implementation of OLAP System for Distributed Data Warehouse

Data warehousing and on-line analytical processing (OLAP) are essential elements of decision support, which has increasingly become a focus of the database industry. A distributed OLAP system is designed which uses multi microcomputers based local area network. The introduction distributes technology into OLAP system that can disintegrate the complicated query and analysis into different servers. In this paper, there are a lot of theoretical concepts associated with data warehouse and OLAP systems, and distributed data will be the implementation of several measures such as design cubic data and distribution algorithm and division of the data warehouse and decision support system DSS is performed to answer the complicated query. Practical results show that the distribution of data to multiple servers with OLAP system is faster according to the algorithm that has been dealing with client-server architecture to distribute the data warehouse. Statistical analysis concepts are used from current work to get predictable results which can be used to get suitable result DSS.


INTRODUCTION
On-Line Analytical processing (OLAP) and multidimensional analysis is used for decision support systems to find interesting information from large databases .OLAP stands for Online Analytical Processing. It uses database tables (fact and dimension tables) to enable multidimensional viewing, analysis and querying of large amounts of data. E.g. OLAP technology could provide management with fast answers to complex queries on their operational data or enable them to analyze their company's historical data for trends and patterns [1]. At Present, almost all the OLAP systems are based on only one server. It is excessively expensive to deploy these systems for most middle and small sized enterprises. By introducing distribute technology into OLAP, the system can disintegrate the complicated query and analysis into different servers, then the OLAP application can run on servers with low configuration or microcomputers. Thus, it can make use of available equipment to deploy OLAP system, reduces business cost effectively for companies. There is a profit forecast system for real estate investment that uses OLAP technology to do data analysis. It runs on only one server before, now the defects of OLAP service on the one server begin to emerge with more data and more complex query: such as more response time, slow query speed, slow dependability and scarce expansibility [2].

Data Cubes and OLAP
Multidimensional systems store data in multidimensional structures which is a natural way to express the multi-dimensionality of the enterprise data and is more suited for analysis. A "cell" in multi-dimensional space represents a tuple, with the attributes of the tuple identifying the location of the tuple in the multi-dimensional space and the measure values represent the content of the cell. Data can be organized into a data cube by calculating all possible combinations of GROUP-BYs [2].

A. Basic OLAP Operations
The basic OLAP operations are as follows. 1-Roll up: An operation for moving up the hierarchy level and grouping into larger units along a dimension. Using roll up capability, users can zoom out to see a summarized level of data. Roll up operation is also called the drill up operation. 2-Drill down: An operation for moving down the hierarchy level and stepping down the hierarchy. Using drill down capability, users can navigate to higher levels of detail. Drill down operation is the reverse of roll up operation. 3-Slice: Slicing performs a selection on one dimension of a cube and results in a sub cube. Slicing cuts through the cube so that users can focus on more specific perspectives. 4-Dice: Slicing defines a sub cube by performing a selection on two or more dimensions of a cube [3] and [4].
Proposed algorithm based on the OLAP is as follows: • Input: OLAP query • Output: Values, tables, or graphs of OLAP query result ‫ــــــــــــــــــــــــــ‬ Step one. User determines the required OLAP query Step two . Determine the required parameter for analysis Step three . OLAP Engine determines the required data to OLAP query by assistance of metadata from cube repository Step four .Select the dimensions and measure from the general table used to design the data cube Step five .OLAP Engine processes the request and, then displays the result to the user interface.
Step six. The result is presented to the user in the form of values, table, or graphs End Figure 2 shows the browsing of Reseller Sales Amount measure dimensioned by Country attribute of the Geography user-defined hierarchy in Cube Browser. This is the interest of defining the reference relationships.

Distributed Processing
Most networks use distributed processing, in which a task is divided among multiple computer. Instead of a single large machine being responsible for all aspect of a process, separate computers (usual a personal computer or server) handle a subset.

A. Fragmentation Transparency
A unified approach to the distributed design of distributed data warehouse is a data fragmentation, which refers to splitting a huge data set in the fact table into much smaller pieces that can be managed efficiently and minimizing response time to answer a complex query. At this stage, the horizontal fragmentation will be used since they include fragmenting the data and indexed together depending on the proposed framework.

OLAP Operation for SALES DW
The multidimensional view allows hierarchies associated with each dimension also to be viewed in a logical manner. Figure 3 explains the hierarchy for the times dimension and direction of Rull-up and Drill-Down. Show Rull-up operations on times Dimension to aggregating the time Dimension from day to year. In the Drill-down operation to aggregating from year to day.  Figure 4 shows the result of using slice operation of OLAP.

A. Resellers Sales Amount
When the report is previewed, the result will look like that in Figure 5 The Figure

B. Large Reseller Report
When this report is previewed the result will look like that in Figure 6. When you scroll down in the report you can see the Doughnut charts that show business type and product line information of large resellers.

Results and Discussion
In this system the results were compared to the previous results from the centralized DW and through the practical results obtained faster DDW in the query to the decision maker because of the volume of data and partition tables. the results from several aspects. The most important of which are(Practical, Interplay, Easiness to Implement, Extensible, Performance, Execution time) as follows: -Practical: The algorithmic previous system was working logically and can be used simply in data processing. -Easiness to Implement: Steps of algorithm can be implemented in any structured language. -Interplay With the user: The system interfaces are very clear to the user and are easy to to used and understood. -Extensible. We can develop the project, and other functions can be added to it.
-Performance: The system has good performance. This proposed system can be used to develop the DSS. -Execution time: To processing huge amount of data ,we need a few minutes only when we use the proposed system , and wile the speed vs. the high quality will be on it, the data is not affected, especially the processing is running once for all data (See  table 1).

Conclusion
1. The OLAP system with one server can be applied simply, but it is inefficient and expensive, so it cannot be applied in most middle and small-sized enterprises. 2. The introduction distributed of a technology into OLAP system can disintegrate the complicated query and analysis into different servers. So, the OLAP application can run on servers with low configuration or microcomputers. 3. Database design depends on the distributed prototype of the foundation, and the number of times, locations and access to application data. Data tables are placed near the application that needs thin more than others. 4. Partition table is useful in distributed database systems, where users and applications with afferent requirements access the same table. Division offers useful and quick access through the local reference of the table.