| > | | | | Understanding Data Warehouse design |
| | | | At a very global level, construction of the data |
| This tutorial covers OLAP solutions used by Data | | | | warehouse is a business project by itself. The |
| warehouses and understanding Data Warehouse | | | | enterprise needs to ask itself certain fundamental |
| design. The enterprise needs to ask itself certain | | | | questions before actually launching on the process of |
| fundamental questions before actually launching on | | | | designing the data warehouse. It must begin with a |
| the process of designing the data warehouse. It | | | | conviction that a data warehouse would really help its |
| must begin with a conviction that a data warehouse | | | | business and the return on investment will make it |
| would really help its business and the return on | | | | worth it. |
| investment will make it worth it. | | | | The general questions that are asked may be as |
| Defining OLAP Solutions | | | | below
. |
| The data warehouse offloads data from a multitude | | | | Do we need a data warehouse? |
| of sources. The cleaned, validated and loaded data is | | | | How will it help the business? |
| voluminous and daunting. This data needs to be | | | | What will it mean in terms of cost? |
| organized, categorized and arranged in meaningful | | | | What are the current data analysis methodologies |
| order for analytical purposes. OLAP solutions are | | | | being adopted? |
| specifically designed to cater to this need. | | | | In what way are they deficient? |
| OLAP solutions used by Data warehouses are: | | | | Will setting up the data warehouse help in reducing |
| Multidimensional views of data. Data in the data | | | | these deficiencies? |
| warehouse is organized into subject oriented | | | | What kind of reporting and analysis do we really |
| categories and tables. Fact tables are constructed | | | | want? |
| and linked to various dimensional tables in star or | | | | What is that we are getting now? |
| snowflake schemas or combinations of them to form | | | | Will such data analysis make the business more |
| multidimensional views of data. Cubes are built using | | | | efficient? |
| these multidimensional schemas. Rapid browsing and | | | | Will it help the business improve its services and |
| querying then becomes possible. These views are | | | | customer relations? |
| independent of the way in which data is stored in the | | | | Once the replies to the above questions have been |
| data warehouse. | | | | asked, the organization needs to examine other very |
| Interactive query and analysis of data is another | | | | crucial issues that will determine the wrap and hoof |
| OLAP solution that enables users drill down, drill up | | | | of the data warehouse that is being set up. |
| and slice data by using multiple passes. Users can drill | | | | What are the kinds of data that are being generated |
| down to successive lower levels of detail or roll up to | | | | by the enterprise? |
| higher levels of summarization and aggregation. | | | | What kinds of data storage technologies are |
| Analytical modeling is an OLAP tool that is a | | | | currently being used to backup and store historical |
| calculation engine for deriving ratios, variances etc., | | | | data? |
| involving measurements and numerical data across | | | | What other external sources of information do we |
| many dimensions. | | | | need to tap to make the data in the data warehouse |
| Functional models are made available by using OLAP | | | | meaningful for analysis? |
| for forecasting, trend analysis etc. They support | | | | What kind of hardware and software will be required |
| users in data analysis. | | | | to set up this data warehouse? |
| Graphical OLAP tools are used to display data in 2D | | | | Who will be the personnel to handle the process of |
| or 3D cross tabs and charts and graphs with easy | | | | creating the data warehouse? |
| pivoting of axis. This is important for users who need | | | | Which departments will benefit from the data being |
| to analyze data from different perspectives and the | | | | created? |
| analysis of one perspective leads to business | | | | Will the data warehouse be scaleable? |
| questions that need to be examined from other | | | | How will it connect to the different data sources for |
| perspectives. | | | | data? |
| Rapid response to queries is a must in any analysis of | | | | How will we ensure that quality data is generated? |
| data and the measure of success for the OLAP tool. | | | | What kinds of tools will be deployed to support end |
| Nigel Pendse and Richard Creeth, authors of the | | | | user needs for reports and analytics? |
| OLAP Report developed the FASMI (Fast Analysis of | | | | The answers that emerge from these questions will |
| Shared Multidimensional Information) test to judge | | | | be a set of business requirements. These |
| whether or not an application qualifies to an OLAP | | | | requirements will determine the kind of data |
| tool. Their contention was that an OLAP tool should | | | | warehouse that will be ultimately set up in the |
| provide fast browsing capabilities (< five seconds), | | | | enterprise. The first steps would be to define the |
| should contain analytical tools both for the developer | | | | global parameters that will shape the design of the |
| and the end user; the cubes must be able to handle | | | | data warehouse. The design can be a top down |
| the security requirements of sharing confidential | | | | approach as recommended by Bill Inmon or a bottom |
| information and it should present data | | | | up approach recommended by Ralph Kimball. It can |
| multi-dimensionally. | | | | be a combination of the two called the Hybrid |
| Multi dimensional data storage engine stores data in | | | | approach or it can be a federated approach. Let us |
| arrays. These arrays are logical representations of | | | | have a brief look at what these different approaches |
| the business dimensions. | | | | mean. |