The slice operation creates a sub-cube by selecting a single dimension from the main OLAP cube. For example, you could move up in the concept hierarchy of the “location” dimension by viewing each country's data, rather than each city. Roll up is the opposite of the drill-down function-it aggregates data on an OLAP cube by moving up in the concept hierarchy or by reducing the number of dimensions. For example, if you view sales data for an organization’s calendar or fiscal quarter, you can drill-down to see sales for each month, moving down in the concept hierarchy of the “time” dimension.
The drill-down operation converts less-detailed data into more-detailed data through one of two methods-moving down in the concept hierarchy or adding a new dimension to the cube. OLAP cubes enable four basic types of multidimensional data analysis: Drill-down In practice, data analysts will create OLAP cubes containing just the layers they need, for optimal analysis and performance. (An OLAP cube representing more than three dimensions is sometimes called a hypercube.) And smaller cubes can exist within layers-for example, each store layer could contain cubes arranging sales by salesperson and product. In theory, a cube can contain an infinite number of layers. For example, the top layer of the cube might organize sales by region additional layers could be country, state/province, city and even specific store. The OLAP cube extends the single table with additional layers, each adding additional dimensions-usually the next level in the “concept hierarchy” of the dimension. And it requires a lot of work to reorganize the results to focus on different dimensions.
SQL and relational database reporting tools can certainly query, report on, and analyze multidimensional data stored in tables, but performance slows down as the data volumes increase. Each data “fact” in the database sits at the intersection of two dimensions–a row and a column-such as region and total sales. The core of most OLAP systems, the OLAP cube is an array-based multidimensional database that makes it possible to process and analyze multiple data dimensions much more quickly and efficiently than a traditional relational database.Ī relational database table is structured like a spreadsheet, storing individual records in a two-dimensional, row-by-column format. OLAP extracts data from multiple relational data sets and reorganizes it into a multidimensional format that enables very fast processing and very insightful analysis. For example, sales figures might have several dimensions related to location (region, country, state/province, store), time (year, month, week, day), product (clothing, men/women/children, brand, type), and more.īut in a data warehouse, data sets are stored in tables, each of which can organize data into just two of these dimensions at a time. Most business data have multiple dimensions-multiple categories into which the data are broken down for presentation, tracking, or analysis.
OLAP (for online analytical processing) is software for performing multidimensional analysis at high speeds on large volumes of data from a data warehouse, data mart, or some other unified, centralized data store. A core component of data warehousing implementations, OLAP enables fast, flexible multidimensional data analysis for business intelligence (BI) and decision support applications.