For the past 15 years or so, business analysts have been blessed with a plethora of different types of data to support their day-to-day analysis and decision-making. One of the more popular (and powerful) of these data structures is OLAP, short for Online Analytical Processing. The most common characteristic of OLAP analysis is that it is based on a multidimensional representation of the underlying data.
When report authors use dimensional authoring skills, reports become more dynamic, interactive and meaningful. Report performance improves. But too often, junior analysts stop their report development skills at the traditional (relational) level, not understanding the value delivered by the dimensional authoring experience.
In this blog, we help you understand whether you have access to multidimensional/OLAP data, dig into the benefits and end with background on the architecture.
How to recognize an OLAP data source
Here are some typical OLAP data sources
- Cognos PowerCubes
- Cognos Dimensionally Modeled Relational (DMR)
- Cognos Dynamic Cubes
- TM1
- IBM Planning Analytics
- Microsoft SQL Server Analysis Services (SSAS)
The two slides below (from our Dimensional Report Authoring course) provide a synopsis of the differences between relational and OLAP data sources. We used Cognos as a front-end platform to show how these multidimensional sources will appear and how they differ from traditional, relational sources.
Now for some features of the dimensional data structure:
Does this look familiar to you? With Cognos packages, it is possible to have a hybrid package that contains some elements of both of the above relational and dimensional structures.
So now you realize you have access to multidimensional data. Why is that so important? Because OLAP offers significant benefits to both end users (data explorers) and professional authors. And authors may lack the knowledge to fully leverage the benefits.
The benefits of OLAP for data exploration
OLAP data exploration lets analysts look for patterns, trends, anomalies, correlations or other characteristics of underlying data that can provide insight and support decision making. Yes, this sounds like traditional data discovery; however, OLAP sources provide built-in features that make this process much more intuitive and user-friendly.
OLAP also allows users to drill up and down into data to look at different levels of detail. For instance, a report that shows total revenue for a year in a country, if based on an OLAP source, will allow users to access the information broken down by quarter, city or state level.
Additional features are built into multidimensional data sources which can allow for
- Top/bottom filtering
- Asynchronous or disjointed nesting and stacking of different levels of detail on a single crosstab or chart axis
- Expanding/collapsing of hierarchy levels
- Analysis on sets or individual members
- Define and save/share custom sets
- Exclude individual members
We’ll show you how to access these features during our Cognos Analytics Self-Service OLAP Data Exploration class.
The benefits of OLAP for professional report authoring
When report authors use dimensional authoring skills, reports become more dynamic, interactive and meaningful, right out of the box. Calculations and filters that seemed impossible, or didn’t behave correctly, start to work and add analytical value. Queries become more streamlined and report performance improves.
Report authors working with OLAP data are exposed to the entire structure of the underlying data source. As such, they need a functional knowledge of concepts such as hierarchies, levels, members, attributes, sets and tuples.
The gotcha with the report authoring learning curve is that traditional gestures and techniques from relational authoring will typically work up to a point. This can lead developers to believe that there are no crucial differences between the relational and the dimensional authoring experience. Again, that’s true but only up to a point At this stage, authors will either expand their skills or assume that something is wrong with the tool and abandon its use. We encourage users to forge ahead adding dimensional authoring skills. Doing so will open a new world of potential for managed reporting as well as ad hoc data discovery.
If you’re ready to take your authoring skills to the next level, register for Cognos Analytics Dimensional Report Authoring, our two-day instructor-led, online class in which you can create dynamic reports against multidimensional data sources such as PowerCubes, DMR packages and TM1.
Quick technical architecture overview
The bottom line with OLAP, or multidimensional data, is the delivery of a data source that is either physically or virtually structured based on a multidimensional model of the underlying business requirements. A universal example of a dimension is time. To understand hierarchies, or levels of a dimension, think of time as a dimension of your analysis. When using a traditional calendar, think of all time as the root of the dimension. If you organize time by year, then logical design would result in year as the top level of this dimension. The next logical level might be quarter, then month, followed by week and then day.
This modeling process is repeated across all other relevant analytical dimensions of a business. These logical hierarchies provide the ability to drill up or down from level-to-level. The underlying source data, whether it comes from traditional databases, flat files or other sources can be physically transformed into a new source, such as a PowerCube or reside as a logical design within the metadata, such as with a Framework Manager DMR. OLAP technologies also provide hybrid or in-memory options for deployment.
Conclusion
In our training courses, we encounter students at all levels of experience who are building their querying and reporting skills following a traditional (relational) learning path. We encourage analysts to build strong foundational skills starting with a relational background. However, in organizations that use OLAP sources as complimentary or primary sources of analysis, lacking knowledge of how to query from and report off of these structures is insufficient. Make sure your learning plan incorporates both relational and multidimensional concepts.
Senturus offers a large selection of Cognos relational and dimensional report authoring courses as well as many other Cognos and Tableau courses. These instructor-led, online courses are taught by real-life subject matter experts. See the course schedule to view the wide selections of classes we offer.
