Data Warehouses, OLAP, and Data Mining - Assignment Example

Index PART A: Data Warehouses 2 Fundamental Characteristic 3 Guidelines for a Success Data Warehouse 3 Dimensional Modeling 4 Active Data Warehousing 5 Criteria for Selecting Data Warehouses 6 Advantages and Disadvantageous 6 Conclusion 7 PART B: OLAP and Data Mining 8 OLAP and its Main Components 8 Multidimensional Data Cubes 8 Data Mining 9 Data Mining Pattern Types and Web and Text Data Mining 10 Case Application of Data Mining 10 PART C: UAE Car Dealership Case 12 Designed Data Warehouse 12 Proposed Data Cube 13 Drilling and Slicing 14 Examples of Data Mining Utilization 16 References 17 Data Warehouses, OLAP and Data Mining PART A: Data Warehouses Introduction to Data Warehouses Data management can be performed using different types of data analysis and reporting options. One of such options is a data warehouse. Data warehouses are used by organizations to collect data from multiple sources on a large scale in order to perform analysis on the accumulated data for trends and predictive forecasts. The nature of the data warehouse is such that is a repository of data that lies at the heart of a data management system. They are made up of several components, some of which include the means of data sources, data transformation, reporting, metadata, operations as well as optioning components that might be required by specific organizations (Berson and Smith, 1997). The data warehouse is explored in detail in the following sections. Fundamental Characteristic The fundamental characteristics of a data warehouse pertain to the inherent nature of the data warehouse which depicts the data warehouse to be subject oriented, including time variance, while being non volatile an providing for integration of data. Aside from this the data warehouse is basically a repository and it stores mainly historical data. The data warehouse should be implemented and employed when large amounts of historical data need to be managed, which are sourced from many disparate sources. Guidelines for a Success Data Warehouse In order to develop and implement a data warehouse successfully, some guidelines need to be followed. The business should start off by having a clear and stated goal and set objectives for why the data warehouse is to be implemented. This helps in clarifying the reason for the data warehouse. The decision of building the data warehouse in house and buying an off the shelf product needs to be addressed as per the requirements of the company. The gap between the IT and the business needs to be addressed for effective implementation. The implementation of the data warehouse should be done in a gradually incremental manner, while allowing for growth and scalability. The architecture and the structure if the data warehouse need to be set up to provide for efficiency. Aside from this, it is important for the data to be stored, to be cleaned in the extraction, transformation and the loading process. In addition, querying capability should be enabled and allowed for at the time of implementation. Dimensional Modeling Dimensional modeling is a particular type of data modeling that is associated with data warehouses. It is significantly different from the entity relationship modeling that is employed for databases. The dimensional modeling instead involves denormalization of data that is grouped as per the star schema (Kimball and Ross, 2002). The star schema corresponds to a fact being associated with several dimensions. A fact table stores facts and foreign keys to the dimensions, while the dimensions are referred to by the fact and can be hierarchical in their different levels. The dimension table and the fact table association is depicted through the following diagram which depicts the star schema for a sample retail store. The fact table is titled store sales, while the store, payment type, time, product and customers are dimension tables. Active Data Warehousing The information that is stored in a data warehouse is historical in nature, therefore any analysis and query processed on this data will result in historical results. IN order to make data warehousing more dynamic and current in terms of analysis results, active data warehouse is employed. Through this approach near real time results are obtained. Active data warehousing involves setting up queries to make way for event based triggering. The main purpose for this is to allow management to determine what will happen next on the basis of what has been happening in the past based on similar events (Brobst and Rarey, 2003). Criteria for Selecting Data Warehouses In order to select the best possible data warehousing solution, an organization needs to link its goals with its decision regarding the choice of the data warehouse. Firstly the company should identify the purpose of buying and implementation of a data warehouse highlighting the specific needs that need to be satisfied. Then keeping these in consideration a data warehouse should be selected based on the capability of the organization to develop one in house or buy one off the shelf. The other thing that is of utmost importance when selecting for a data warehouse is to ensure scalability of the selected data warehouse over time. Advantages and Disadvantageous The advantages that can be earned through the employment of data warehouse include, having clean data which is indexed as per the different types of data stored. In addition it is possible to run queries on the data using tools like SQL based queries. The data is integrated, while provided for in terms of security with the access and retrieval of data being management through the DBMS. The disadvantages include the fact that it can be complex to manage, as it requires significant investment, with the initial cost of implementation very high. Adding new data sources after the first implementation can be a time consuming process. There is limited flexibility and the data stored in the data warehouse is historical therefore dated and non current. The drill-down capabilities are not present while the users can loose control over data ownership. Conclusion Data warehousing is a complex technique for data management, which involved significant insight and consideration in terms of its implementation. Improperly managed and set up data warehouses can be costly and dragging instead of efficient and effective. As a result organizations should seek to invest in data warehousing when required, and then spend considerable importance of the integration and installation of the data warehouse with the current business systems to allow for long term effective benefits in decision making PART B: OLAP and Data Mining OLAP and its Main Components OLAP is a technical acronym for Online Analytical Processing. The concept is often employed in data management specific to data warehousing. It provides a collection of protocols that can be employed for reporting of data by a business through the use of relevant data management techniques. The main components of OLAP include a data server, a specific server for OLAP and a client on which it is operated. Other components of OLAP can include the MOLAP which allows for multi dimensional data processing. OLAP enables users to perform analytical data processing for the purpose of reporting and decision making in a rapid and efficient manner. Multidimensional Data Cubes The multidimensional data cube is a form of OLAP array for data which allows online analytical processing of data through multiple dimensions. In order to comprehend a data cube, it can be imagined as a Microsoft Excel based spreadsheet where multiple fields of data can be attributed to a single item of theme. Similarly the data cube presents the data through multiple dimensions. The cube can have multiple segments with multiple sides. These segments are referred to as cell and depict different aspects of business data. An example that can be considered is a cube with its different planes depicting the sales of the product, the product itself, the time period of the sale etc. A pictorial example of the data cube is represented below. Data Mining Data mining is mostly employed using the data warehousing strategy. The process of data mining is one that takes the form of data discovery with the business data analyzed from different perspectives to observe and discover trends in the data. Data mining is possible on only historical as data collected over a period of time is required in which it is possible for trends to be present. While both data mining and OLAP are techniques employed for business intelligence based on data warehousing approach, the main difference between the two is that data mining specifically involves identification of patterns in the data while OLAP basically deals with queries that can be employed to analyzed databases that are multidimensional in nature. Data Mining Pattern Types and Web and Text Data Mining There exist different data mining patterns which take the form of associations, sequences, classifications, clustering and forecasting. Associations in data mining seek to identify relationships that may exist between the variables in the data sets. An example of this can be employment of the technique by a retailer to identify products that are purchased complementarily, with their sales linked together. This data can be employed for developing marketing strategies to target sales of such products. Clustering involves grouping data together which is similar in attributes therefore devising structures in the data. The classification technique on the other hand involves categorizing new data as per the generalized categorized through their inherent characteristic attributes. Regression and forecasting involves using the existing trends and patterns in the data to forecast future behavior. This can for example be employed by the travel agencies to predict based on existing historical data through data mining as to which seasons have high flow of customers using traveling packages, and more specifically in which season the customers are more likely to travel to specific holiday destinations like Bali, Phuket, Dubai or Morocco. Case Application of Data Mining Data mining is extensively employed in the baking industry and those relating to the financial sector. Financial tools used by the customers like debit and credit cards provide financial companies with large amounts of data pertaining to the behavior of the customer and their card usage. The companies use data mining to discover patterns in the usage behavior of the customers. An example of this can be employing data mining for cross selling products to the customer. Employing data mining baking and financial institutions can determine the stage of the lifecycle in which the customers currently are and then market to sell appropriate products to such customers. For example the banking industry employs data mining to determine newly married couples who have tied in the knot in the last year or two, and they approach such customers to cross sell their mortgage and house loan products as typically such family units at this stage of life are likely to be seeking investments for house and property. PART C: UAE Car Dealership Case Designed Data Warehouse The following depicts the sample architecture for the as derived from the case description and the provided integrative sample data that the dealership may want reported. The proposition includes a central data warehouse repository with sup0portive OLAP servers for processing and respective clients. In addition the data repository would be storing information that would be retrieved from the regions of operations of the dealership specifically from Abu Dhabi, Dubai, Sharjah, Alain and Fujairah. Proposed Data Cube The following is the proposed data cube for the car dealership in UAE, specifically for the development of the report depicted in the case Drilling and Slicing Data Slicing is specific to the OLAP cube. It involves slicing a dimensional subset of the data from the entire data cube to arrive at a smaller data cube which has fewer dimensions. Pertaining to the case the previously exhibited OLAP cube has been sliced in two ways to provide two examples of slicing. The first depicts a slice of data as per the sales of all products of the dealership pertaining to Abu Dhabi only that took place in December 2011. The second example depicted below shows the slice of data as per the Alfa Romeo sales for all months from august 2011 to December 2011, as per the different regions of the dealership operations. The drilling of data involves the navigation through data in terms of an in depth perspective and a summarized overview perspectives. An example of drill down includes looking into the segment for Nissan only, with the specific models of Nissan in terms of sales per month or per region. The drill up version of the same pertains to looking at the sales of Nissan cars as a whole, regardless of their color, attributes, model of make. These examples are made specific to the data provided and the created multidimensional data cube . Examples of Data Mining Utilization The car dealership can make use of data mining to forecast future sales of its products. By employing data mining the company can determine which brands of the cars are most popular in the specific regions of its operations respectively through the volume of sales data per product and region. The company can then market these cars aggressively in the markets where they are well received. Another strategy that the company can employ is to market other non popular car brands in alternate regions. The company can also use the above mentioning data mining example to plan for the supply chain of its products, and making logistics based decisions regarding which car is best suited to the specific market per region and therefore concentrating on the sales of the specific model to derive high sales and profitability. Other strategies that can be employed by the company is to add more data to the repository specific to the customer and use data mining to determine which types of customers buy a specific make of the automobile and any car financing scheme where applicable. The company can then cross sell its products using this information. References Andrew, (2011), Difference Between Data Mining and OLAP, retrieved from Berson, A., Smith, S.J., (1997), Components of a Data Warehouse, The Data Administration Newsletter, retrieved from Brobst, S. and J. Rarey, (2003), Five Stages of Data Warehouse Decision Support Evolution, DSSResources.COM DWSA, Technical Guidelines for Successful Data Warehousing, retrieved from Kimball, R., Ross, M., (2002), The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, Wiley 2 edition Oram, A., Credit card company data mining makes us all instances of a type, O’Reilley Radar Retrieved from Pareek, D., (2006), Business Intelligence for Telecommunications, Taylor & Francis Slaughter, A.H., OLAP, retrieved from Wilcox, C., Dubler, C., Just What Are Cubes Anyway? (A Painless Introduction to OLAP Technology), MSDN Microsoft Corporation, retrieved from < http://msdn.microsoft.com/en-us/library/aa140038%28v=office.10%29.aspx#odc_da_whatrcubes_topic2> Read More