On – Line Analytical Processing. Functions that allow creating much better organized databases, in case the databases are too large.
[ hide ]
- 1 General
- 2 Fundamentals
- 1 Levels of detail
- 2 Levels in a geographical dimension
- 3 Dimensions and cubes
- 3 OLAP systems
- 1 ROLAP (Relational OLAP)
- 2 MOLAP (OLAP Multidimensional)
- 3 HOLAP (OLAP Hybrid)
- 4 Features
- 1 Recover OLAP data
- 2 Create cube files for offline use
- 3 Create cubes from relational databases
- 5 Software components for OLAP access
- 1 OLAP provider
- 6 Sources
On-Line Analytical Processing (OLAP) are functions that provide the ability to return the rank row, numbering, and existing information. An OLAP function can be included in expressions in a select list or in the ORDER BY clause of a select statement.
An OLAP function cannot be used as an argument to a column function. The result of the query to which the OLAP function is applied is the result table of the innermost subselect that includes the OLAP function. OLAP databases are designed to streamline data recovery . Since it is the OLAP server that calculates the summary values. This possibility allows working with much larger amounts of source data than if the data were organized in a traditional database.
OLAP databases organize data by level of detail, using the same categories that are used to analyze the data. For example, a sales database may have fields corresponding to the country , region , city, and location of each sale. This information can be arranged from lowest to highest level of detail.
Levels in a geographical dimension
This organization makes it easy for a PivotTable or PivotChart report to show high-level sales summaries, such as total sales for a country or region, and details of sites where sales are especially high or low .
Dimensions and cubes
The set of levels that encompasses one aspect of the data, such as geographic areas, is called a dimension. Similarly, information on when sales were made could be organized into a time dimension with levels for year , quarter, month, and day . OLAP databases are called cubes because they combine various dimensions, such as time, geography, and product lines , with summary data, such as sales or inventory figures.
ROLAP (Relational OLAP)
This is the type of OLAP implementation that stores data in a relational engine . Normally, the data is detailed, in this way aggregations are avoided and the tables are also normalized. The most common schemes that are worked on are the star scheme and the snowflake scheme, although it is also possible to work on any relational database. The architecture of these databases is made up of a relational database server and the OLAP engine is on a dedicated server just for this.
MOLAP (OLAP Multidimensional)
This other type of OLAP implementation what it does is store the data in a multidimensional database. To optimize response times, the information summary is calculated in advance. Some systems use data compression techniques to decrease disk storage space due to pre-calculated values.
HOLAP (OLAP Hybrid)
It stores some data in a relational engine and others in a multidimensional database. This type of implementation uses the two techniques described above. Hence it is a hybrid technique.
Recover OLAP data
Can connect to data sources (data source: stored set of “source” information used to connect to a database. A data source can include the name and location of the database server, the name of the controller of the database and information needed by the database when a session is started. OLAP as you would other external data sources. you can work with databases created with OLAP services Microsoft SQL server , the server product OLAP from Microsoft Corporation. OLAP data can only be displayed as a PivotTable or PivotChart report, not as a range of external data. You can save PivotTable and PivotTable OLAP reports to report templates that include one or more queries or PivotTable reports based on external data.
Create cube files for offline use
Offline cube files allow you to work with OLAP data without being connected to the network. You can only create cube files if you use an OLAP provider (OLAP provider: A set of software that provides access to a specific type of OLAP database. This software may include a data source driver and other client software required to connect to a database.), such as Microsoft SQL Server OLAP services , that supports this function.
Create cubes from relational databases
OLAP cubes allow you to organize queried data from relational databases, such as Microsoft SQL Server , into OLAP cubes. The wizard is available from Microsoft Query. A cube enables you to work with larger amounts of data in a PivotTable or PivotChart report than in other circumstances, and also streamlines data retrieval.
Software components for OLAP access
To configure OLAP data sources, one of the following OLAP providers is required:
- Microsoft OLAP Provider: Requires a data source driver (program file used to connect to a specific database. Each program or database management system requires a different driver.) And client software that you will need to access databases created with Microsoft’s OLAP product, Microsoft SQL ServerOLAP Services .
- Other OLAP Providers: For other OLAP products, you will need to install additional drivers and client software. For information on how to install and use third-party OLAP providers, contact your system administrator or the vendor of OLAP products.