 I am Dr. R. V. R. Githi from CAC department of W. A. D. Shalapur. The today's video topic is on online analytical processing that is OLAP. It is a very important topic of a data warehouse and mining. The learning outcome of this video at the end of this session, student will be able to acquaint with OLAP operations that is online analytical processing. They will be able to utilize OLAP for data mining and knowledge discovery activities. Basically, we are doing the mining of data to infer some knowledge. Now content of the video introduction to online analytical processing, then OLAP operations, different operations, advantages, disadvantages of OLAP operations. Now, first of all, OLAP, let us see the definition of OLAP. Online analytical processing is a computing method that enables users to easily and selectively extract and query the data in order to analyze it from different point of view. Means extracting the data, then extracting the data by making the query and doing the analysis to get some inference that is OLAP. OLAP databases are divided into one or more cubes. The cubes are designed in a such a way that creating and viewing reports become easy. The OLAP cube is a data structure optimized for very quick data analysis. It consists of numeric facts called measures which are categorized by dimensions, OLAP cube is also the hyper cube. Now look at this figure one that is cuboid. This is a cube where the product, date and the country, these are the three fields are shown. Here it is country, it is data and here it is a product. The products are VCR, computer, TV and the countries are that is sale that has happened in the country USA, Canada, Mexico and summation of all those. And in the third, in the third axis that is that sale has happened in the quarter, in the second quarter, in the third quarter or in the fourth quarter of the year. So this is an example of sales of VCR, PC, TV in which quarter or in which duration and in which country the sale has taken place. Now OLAP operations, there are four type of operations in OLAP. The first one is called as a roll-up operation, second one is called as a drill-down operation, third one is called as a slice and dice and a pivot. Let us see one by one, first roll-up. Now if you look at this figure, if you look at this figure, it shows that the PC book show clothes, okay on the left hand side quarter one, quarter two, quarter three and quarter four that is the first three months, second three months, third three months and fourth three months of the year and on this side, on this side the name of the country that is Sydney, Perth, Los Angeles or name of the cities are displayed of various countries. Now these are rolled up on a location, on a location means I want to see, I want to see the sale which has happened in the quarter one from USA. Now in USA there are two cities are there, so roll up those two cities roll up those two cities and give me the one data adding those two data of Los Angeles and New Jersey into USA. Here roll-up is also known as a consolidation or aggregation, the roll-up operation can be performed in two ways. It is reducing dimensions means Los Angeles and New Jersey are rolled up into one that is USA, reducing dimensions, then combining up concept hierarchy. Then similarly in this example cities New Jersey and Los Angeles are rolled up into country USA, then the sales figure of New Jersey and Los Angeles are 440 and 1560 respectively, they now become 2000 after rolling up. This is what the effect of roll-up operation. In this aggregation process data in location hierarchy moves up from city to country. In the roll-up process at least one or more dimensions needed to be removed. In this example quarter dimension is removed. Now let us see the drill down operation. Now this is exactly opposite of that roll-up operation. On the top of the cube quarter 1, quarter 2, quarter 3, quarter 4 that is three months data is shown over here. Now that can be drilled down to January, February and March, then quarter 2, April, May, June and July and so on. And here instead of name of the country, well let us look at the time and dimension. In drill down data it is fragmented into smaller parts, it is the opposite of roll-up process. It can be done via moving down the concept hierarchy, increasing the dimension from one to three or four to four into three that is twelve. Then one quarter is drilled down to months that is January, February, March corresponding cells are shown or displayed can be viewed. In this example dimension months are added. This is what the drill down operation. Let us go for the third operation that is slice and dice. Now in the slice and dice operation, the quarters are displayed on the left hand side over here. Those quarters can be further divided or fragmented into months that is January, February, March to December and drill down is taking place on the attribute time. And in the second part of this figure, this is an example of a slice, only the quarters and the first part of the cube, this part will be displayed as a slice, means we are taking the slice of bread. Similarly, only that much part will be displayed or that data is required. Whenever that data is required, the slice operation is used. So this is how slice and dice operations are taking place. Now this slice operation is for quarter one. Now all of operations pivot. In the pivot, we can rotate the data access to provide a substitute presentation of data. The same data can be shown in different manner. Now in the first figure over here, the item on the left hand side, it was the name of the cities and on the X axis, they were product. Now the same thing, if you rotate this, so what will happen on the Y axis, the product will be displayed and on the X axis, the name of the city will be displayed. Well, take this question, compare roll up and drill down operations in OLAP, take a pause over here and think and give answer of this particular question. Now roll up operation, the difference between these two. Roll up performs aggregation on data cube, drill down is the reverse operation of roll up. It is performed by climbing up a concept hierarchy for dimension and here it is stepping down that is opposite to that of roll up, climbing up and stepping down a concept hierarchy for dimension. Now here reduction will take place and here introducing new dimensions. On rolling up, the data is aggregated by ascending the location hierarchy from the level of city to the level of country. On drilling down, the time dimension is descended from the level of quarter to the level of month. Now, let us see what are the advantages of OLAP operations. In OLAP operation, OLAP is a platform for all type of business, included planning, budgeting, reporting, analysis, information, calculations are considered in an OLAP cube Quickly create and analyze, easily search OLAP database for broad and specific terms. OLAP provides the building blocks for business modeling tools, allows users to slice and dice of the cube for the various dimensions, measures and filters. It provides faster response time. It is good for analyzing time series, finding some clusters and countries easily. The disadvantages of OLAP operations are it requires organizing data into a star or in a snowflake. These schemas are complicated to implement and administrate. You cannot have large number of dimensions in a single OLAP cube, that is one disadvantage. Transactional data cannot be accessed with OLAP system. Any modification in OLAP cube needs to be pulled, updated of the cube. This is time consuming. These are the references, I hope you understood. Thank you.