OLAP (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. High-speed analysis can be accomplished by extracting the relational data into a multidimensional format called an OLAP cube; by loading the data to be analyzed into memory; by storing the data in columnar order; and/or by using many CPUs in parallel (i.e., massively parallel processing, or MPP) to perform the analysis.
OLAP CUBE
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. Analysis can be performed quickly, without a lot of SQL JOINs and UNIONS. OLAP cubes revolutionized business intelligence (BI) systems. Before OLAP cubes, business analysts would submit queries at the end of the day and then go home, hoping to have answers the next day. After OLAP cubes, the data engineers would run the jobs to create cubes overnight, so that the analysts could run interactive queries against them in the morning.
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. 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.
In theory, a cube can contain an infinite number of layers. (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 practice, data analysts will create OLAP cubes containing just the layers they need, for optimal analysis and performance.
OLAP cubes enable four basic types of multidimensional data analysis:
Drill-down
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. 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.
Roll up
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, you could move up in the concept hierarchy of the “location” dimension by viewing each country’s data, rather than each city.
Slice and dice
The slice operation creates a sub-cube by selecting a single dimension from the main OLAP cube. For example, you can perform a slice by highlighting all data for the organization’s first fiscal or calendar quarter (time dimension).
The dice operation isolates a sub-cube by selecting several dimensions within the main OLAP cube. For example, you could perform a dice operation by highlighting all data by an organization’s calendar or fiscal quarters (time dimension) and within the U.S. and Canada (location dimension).
Pivot
The pivot function rotates the current cube view to display a new representation of the data—enabling dynamic multidimensional views of data. The OLAP pivot function is comparable to the pivot table feature in spreadsheet software, such as Microsoft Excel, but while pivot tables in Excel can be challenging, OLAP pivots are relatively easier to use (less expertise is required) and have a faster response time and query performance.
You can read more about OLAP here.
Teknita has the expert resources to support all your technology initiatives.
We are always happy to hear from you.
Click here to connect with our experts!
0 Comments