The data structures required to optimize analysis and reporting and the data structures required to optimize source application transactions can be (and often are) very different. On Line Analytic Processing (OLAP) is a BI method that enables long-term analysis and reporting of data that is stored in a data warehouse rather than the source relational, transactional database that is optimized for On Line Transaction Processing (OLTP). Regularly-scheduled ETL processes (Extract, Transform and Load), often daily, update the data warehouse from the relational database. This "separation of duties" assures that OLAP and OLTP processing remains separate and do not interfere with one another.
OLAP Cubes are usually formed by adding up numerical data from relational databases and categorizing them based on important business factors. For example, what day, week or month events occurred – a time dimension – or the locations the event occurred – a geographic dimension – or what product category that was involved at the event and location – a product dimension.
In the physical world, we generally think in terms of 3 dimensions – length, width, and depth. If you’re scientifically inclined, you may think of Time as a fourth dimension. With data cubes, the 3 dimensional connotation of physical reality goes away – you can combine any number of dimensions to satisfy a specific business need. And the Time Dimension is often the most important dimension of all, and is important in the majority of all cubes, because we almost always want to know how much money, orders, sales, costs, people, products, services or other measures are performing along or within a certain time period i.e. per month, per week, or at least per year.
Unlike physical dimensions, data dimensions can also have hierarchies. For example, Years contain Quarters, Months and Weeks, which in turn contain days, days of the week, which in turn contains hours, minutes, seconds, etc. By properly utilizing hierarchies in dimensions, we can optimize the processing of cubes.
In addition, there have been major advances in the OLAP option itself, especially the introduction of compressed composite dimensions. These offer a huge improvement in the performance of sparse data, especially where more than five or so dimensions are used.
Inforay offers expert knowledge of the OLAP option, and can help your organization to get the most from this powerful technology.