Introduction to OLAP - Power Pivot Tutorial

Introduction to OLAP is a free tutorial by Optima Train from Power Pivot course
Link to this course(Special Discount):
This is the best Power Pivot Course
Course summary:
Import external data for analysis in PowerPivot
English [Auto]
In advanced discussions of pivot tables you will probably hear the term colab sooner or later. This is an acronym for online analytical processing and was created as an alternative to OLTP or online transactional processing. Each of these refers to a type of database but they are used for different purposes and OLTP database is used for day to day company operations such as sales to individual customers. Its purpose is to keep track of individual transactions so that if for example a customer complains about a late delivery information about that transaction can be found and used to solve the problem. In contrast OLAP databases were developed for a longer run view. They are designed to allow a company to analyze trends rather than to respond to individual transactions. Specifically they are designed to answer the same sorts of questions answered by pivot tables for many companies OLAP databases are the key to their data warehouses for business intelligence. They are typically structured around dimensions and facts in a so-called star schema similar to what you see here. For example dimensions for a supermarket chain might include customers products stores and dates and the facts also called measures might contain data such as revenue on line item sales. The structure allows the company to break down the facts by any of the dimensions. In fact pivot tables are used in such analyses. There are at least two differences between OLAP databases and Excel datasets for creating pivot tables. First all data sets are pre-processed for very quick analysis. The results are stored in and olap cube file. You can think of this like a Rubik's Cube where each face of the cube corresponds to a dimension and each individual sub cube of the overall cube contains aggregate results for a combination of categories such as total revenue for product 3 at store 7 on August 3rd. Because the aggregates have been pre-processed you get extremely fast results when you build a typical pivot table. Even though the size of the underlying database is typically huge. The other difference is the presence of hierarchy's in dimensions these hierarchy's let you drill down in the Pivot Table. For example the date dimension has a natural hierarchy from year to quarter day month today. So you could start by looking at yearly totals and eventually draw down daily totals. As another example the product mentioned typically has a natural hierarchy such as from product dept. to product category to product subcategory to individual product or as a third example. The store dimension might have the hierarchy continent to country to region to city in earlier versions of Excel. You could create your own OLAP cubes. However Microsoft discontinued this functionality in Excel 2007 and provided it instead in analysis services as part of its sequel server package. Therefore if you want to create OLAP cubes at least with Microsoft software you have to use sequel server Analysis Services abreviated Sosias among other things. This software allows you to create off line cubes which you can then view and excel within the tables. It's not likely that you will be doing this but I will briefly show what it looks like. I have an off line cube called Food Markets see you be with supermarket data. This is not an Excel file and I can't open it directly in Excel. However I can create a pivot table from it by using the external data option and browsing for the cube file. This opens a blank pivot table as usual but the pivot table fields list looks somewhat different. The facts are in one group and the dimensions are in groups below the facts. Now there are certain restrictions. Only facts can go in the values area and only dimensions can go in the other areas. In addition facts must be summarized by sums. This is because of the preprocessing of the data in the cube file to show how drilling down works. I will first place revenue in the values area then I will dry date Y Q M D a hierarchy to the Rosaria and then drill down Alternatively I will do the same with a product for hierarchy from family to department to category to subcategory to brand to product. Obviously this ability to drill down lets a manager see the big picture or the fine details very quickly note that when a pivot table is based on an olap source the olap tools dropdown on the pivot table tools analyze ribbon is enabled. Otherwise this dropdown is disabled. OLAP is actually a huge topic and Mic