Data Warehouse and Formal OLAP Tool - Essay Example

6216 Data Warehousing & Data Mining ASSIGNMENT Data Warehousing Academic Year 2009/0 Data Warehousing and Section # of course> Answer 1 - a The data mart provided in Microsoft Access format was exported to Microsoft Excel and its PivotTable functionality and the entries in the fact table 3 were analyzed. Though it is not possible to perform comprehensive data mining on the data using Excel tools, the data was converted to a customer versus product pivot table displaying total sales amount and total sales quantity. It was analyzed that there was a correlation of 0.76 between the measures: sales amount and sales quantity. This significantly high correlation value means that the higher the sales quantity, the higher the sales figure. Though this is a logically true relationship, the fact that this is one of the findings from the data in the warehouse makes it an analysis of the data from the data mart. The data was analyzed with respect to the time dimension - week. Sales were analyzed against the customers weekly. It was identified that the sales amount graph for the six weeks of 2005 for ALL the customers generated the following trend: The above graph displays that there was a drop in sales amount in the third week of 2005. Since this is a graph of cumulative sales, we can assume it to be a fair representation (normalized) of the entire dataset. After the third week, the sales picked up again in the fourth week, however, this was not sustained: there was a consistent drop in the fifth and sixth week of 2005. This pattern is an interesting one from an analytical perspective. It shows the cumulative pattern of the sales of the company for the six weeks. The pivot table capabilities of Excel can allow drilling down to a specific customer too, however, this pattern represented in the graph is an important one for the company to analyze the potential reasons for the rise or drop. Comparing the trend with changes in ther variables, for example, the firms strategies at those times the company can understand the best practices that led to the changes in their sales. There are several other trends in the company that can be analyzed against actions that must have been taken earlier, however, it a time-based analysis is often the best analysis of any dataset. The analysis provides ample opportunity for the company to analyze its customers' purchases too. In a formal OLAP tool, the customers can be drilled down according to the concept hierarchy and various other aspects of weekly sales can be unearthed. For example, the customer group can be used as an important field to analyze the customers' purchases week-wise. The customer dimension does not have enough fields for a triple concept hierarchy, otherwise, it could lead to a detailed group-wise analysis for sales. The trend could also be analyzed product-wise. It is important to understand again, that a time-based analysis of sales is the most beneficial one as it leads to comparison of all trends of sales with other actions that have been taken in the past. The following graph shows the trend of product sales, detailed against customers, week-wise: This shows again the importance of analysis of data through pictorial representations: the outlier lines in the data depict extreme values. Using a more user-friendly OLAP tool, an analyzer can drill down to the extreme value and find out a possible reason for that value. It is necessary to understand the dimensions of a graph when analyzing the results. A manager needs to be able to pull down and across the dimensions of a data warehouse in order to analyze the data from different perspectives and get the best results. Answer 1 - b The use of a data warehouse or a mart is to facilitate the analytical requirements of a manager interested in finding trends in data or correlating the actions of a company in the past with changes in business profits or sales. Thus, there are several forms of presentation that can be considered suitable to present data to such a manager. Some of the widely used OLAP tools to present the data are: Microsoft Business Intelligence OLAP Browser Microsoft Excel PivotTable ProClarity Professional There are several other tools looming the market, however, the above are a few popular tools for quick and visual representations of data warehouses. Measures have to be analyzed cumulatively; the effect of graphs and charts for presentation can reduce the decision making time and actually make analysis possible in some cases. It is not possible for managers to analyze tons and tons of measures against plain fields of data. Thus some of the most widely sued forms of representing data are as follows: Line Graphs: Trends, most often pertaining to time, are best represented using line graphs. It is easier to understand the changes in measures with respect to time, which can always be drilled in/out. Bar Graphs: These are more useful in substituting line graphs and explaining non-time based analysis of other dimensions. Pie Charts: Grouped data can be easily analyzed. The need to drill down for specific analysis is there, but it provides a great picture of group statistics. Scatter Graphs: used to find out correlations in between two measures often. It can also be used in scenarios where the need is to identify any trend between two dimensions. PivotTables: These are used to analyze data when actual numbers are needed for analysis: instead of finding trends and group-performance statistics, pivot tables are used to analyze such numbers. Answer 2 - a Business Questions: Question 1: Which age group of customers has bought the highest number of apples in the first week of 2005 The highest buyers of dairy products for all the weeks in 2005 lie in which income category Question 2: Which supplier has provided materials with the least average cost In effect, the analysis is to find out the average cost of products purchased from different suppliers - analyzed against product category and supplier category. The above business questions are important from a marketing perspective. Age group and income group are two different demographics, that are targeted by marketers. Since these are independent it is important to break these into two fields. The MegaSave database has combinations of these two demographics and has a set of possibilities saved. However, from an OLAP analytical perspective the field cannot be broken down for analysis. Thus, it is important for there to a structure that supports this kind of an analysis. Answer 2 - b The following is the stand-alone star schema that will be used to answer the first business question: The above design incorporates elements of the basic data warehousing theory. Income and age group could have been part of the dimension: customer group. But that would have had resulted in greater redundancy. It is also possible for a customer to be part of one income group and an age group independently: i.e. income and age group are independent. Thus, it is necessary for us to segregate them in the design and form two dimensions out of them. The following schema answers the second business question: The above start schema can be used to answer the second business question. The analysis would be performed by using the division of the measure: amount by quantity. This would yield the average price of the product purchased in a particular period from a particular supplier. OLAP tools can be used to analyze the data and present graphical representations accordingly. The two standalone schemas can be merged to form the following schema: The above is the combination of the shared dimensions and the two subject areas to complete a fact constellation. This schema can be used to answer both the business questions and will result in a comprehensive data warehouse. Answer 3 - a Though the use of the word is not confined to a single context, the various interpretations of the term point in a very similar direction. OLAP stands for "online analytical processing" however, there are various uses of the term in varied contexts. OLAP is often used, more correctly so, to refer to an analysis engine that would provide an analytical window into the warehouse data. This is generally a referral to the pivot-table type structures that provide data and dimensional analysis. The term is often also used to refer to an interface representation of the warehouse. Though is it the same analytical window, the representation is a lot different from the traditional pivot-table. Often, pivot-tables are said NOT to be part of an OLAP and graphs and charts with drill down functionality are considered to be part of OLAP. A third reference of the term is often made to the data warehouse tools that allow preliminary analysis. These include the Excel built-in functions of correlation, averages, etc and covers data mining too. Mostly data mining is also considered to be an OLAP tool and thus this reference is often inadvertently made. These references of a single term to three different perspectives of a single subject area are due to the regular connotation uses of a terminology rather than logic. Most people like to refer to OLAP when they actually referring to data mining techniques. There is a subtle difference between the two which is often overlooked upon. Answer 3 - b SAS Web OLAP Viewer is a Java-based online analytical processing tool that allows cubes and OLAP engines to be created on the web. It is an intelligent tool that allows for OLAP creation on the web just like Microsoft Business Intelligence allows for OLAP creation in a desktop environment. The tool allows for multiple analysis by different clients by having a central OLAP server. Though there are different architectures available in SAS Web OLAP Viewer, the tool is an excellent modeling tool for data that needs to be analyzed at different fronts through a web server. The web OLAP integrates data from different clients and houses an optional data mart that fills the data warehouse when needed. SAS Web OLAP Viewer also allows for combined OLAP deployment in an offline environment on the server. This not only saves the time lost in data uploading from links, but allows for quicker deployment. Though there are a variety of web OLAP solutions, this is amongst the most popular solutions on the web and allows managers the flexibility and speed of a standalone OLAP engine on the web. This software is mostly used by corporations interested in analysis of data where the different analyzers (managers) are spread across a wide geographical area. The MegaSave database has three fact tables and almost ten dimensions if the dimensions from the previous part are considered. The size of the database and the different subject areas in the database make it ideal for this database to be deployed on SAS Web OLAP Viewer for Java. Deployment of MegaSave data warehouse on the web tool will allow for different managers to continue their analysis by accessing the warehouse from their own offices via web connectivity. It will result in a more comprehensive, less costly and flexible solution for Mega Save to build and deploy the warehouse in SAS Web OLAP Viewer for Java than any other desktop solution. References: Approaches to Data Storing. (n.d.). Retrieved November 15, 2009, from Storing and Retrieving Insurance customers data: http://www.goodstorage.com/insurance/ju789r.htmlindex=-u7h Introduction to Relational Databases. (n.d.). Retrieved November 14, 2009, from RDBMS: www.databasejournal.com/sqletc/article.php/1469521 Read More