BI through OLAP
It’s hard to over emphasize the importance of business intelligence (BI) in today’s world. Strategic business decisions are impossible to make without analysis of past business performance; businesses are increasingly investing heavily in tools and services that let decision makers visually analyze data in myriad ways.
While it’s possible to create analysis and reporting tools that let you visualize business data from traditional relational databases, performance deteriorates rapidly as you begin to visualize multiple dimensions of data from such databases. This is why relational databases are inherently unsuitable for BI applications. A new model for the storage of multidimensional data is required to reasonably perform BI analysis and develop reporting tools.
Several solutions have emerged to resolve this issue—wars have been fought, lessons have been learned, and currently online analytical processing (OLAP) is the de facto standard for persisting BI data. There are now hundreds of thousands of applications built using OLAP, including sales reports, executive reports, forecasting analysis, etc.
OLAP Vendors and Standards
Every traditional database vendor now has an OLAP option—Microsoft, Oracle, SAP, etc. Wikipedia’s OLAP entry lists, among other things, the most popular OLAP vendors.
While OLAP defines the persistence mechanism of multidimensional data, the industry standard for accessing such data is “XML for analysis” (XMLA). This standard, put forth by Microsoft and other leading OLAP vendors, specifies the protocol for accessing OLAP data using other standards like XML, SOAP, and HTTP. It also defines MDX as the query language, by way of SQL, for accessing relational data. While XMLA is the most widely used standard, unfortunately, not all OLAP vendors support it. Oracle, for example, does not support XMLA and instead supports SQL extended for OLAP, another lesser followed standard. However, there are other third-party vendors who provide XMLA providers for Oracle. Wikipedia’s XMLA entry lists the popular OLAP vendors who support XMLA.
OLAP Client Options
Every OLAP database vendor has its own OLAP reporting and analysis solution. Additionally, there are several OLAP client tools that provide reporting solutions for multiple OLAP databases. Here are some common solutions:
- Microsoft provides a set of tools as part of its SQL Server Reporting Services as its reporting solution. For OLAP analysis, Microsoft provides ProClarity.
- SAP’s Business Object Voyager provides a reporting and analysis platform.
- IBM Cognos is another leading provider of OLAP visualization and reporting.
- Microsoft Excel is by far the most common OLAP client for Microsoft’s SSAS OLAP database.
While the above are out-of-the-box solutions for OLAP analysis and reporting, several vendors also provide an SDK with which you can build your own OLAP clients. Microsoft’s ADOMD is one such SDK that lets you access and retrieve OLAP data from specific dimensions and with specific filter criteria.
So, you have a choice to either buy or build an OLAP solution—the pros and cons of which we will discuss next.
Buy vs. Build
There are several BI visualization vendors that offer reporting and analyzing solutions that you could buy, deploy against your OLAP database, and start creating and publishing live and subscription-based reports. The advantage of this approach is that it’s much faster to get started, allowing you to publish and analyze OLAP data in less time. The disadvantage is that such solutions, though capable of customization, often fail to accurately reflect your requirements. You may want to visualize OLAP and relational data side-by-side in a dashboard that might not be supported. You may want to mix chart types in an OLAP Chart (like Column and Line), which might not be possible. Costs could also begin to increase as you add report developers and report consumers to the installation.
The other option is to build your own reporting and analyzing solution. In this case, you would build the framework and applications that provide OLAP reporting and analysis functionality for the managers and executives in your organization. The disadvantage with this approach is that it takes time and resources to build your solution. But once built, it would accurately reflect your requirements; also, there would be no additional deployment and consumption-related costs.
The argument isn’t necessarily buy versus build, as you could potentially benefit from doing both. You could possibly augment out-of-the-box reports with custom-built ones, while in the process fixing any shortcomings of the out-of-the-box reports.
OLAP Data Access
Our BI library provides a higher level of abstraction over ADOMD.NET and lets you easily create Report Definitions specifying the dimensions and measures you want to visualize as well as providing support for single-line data binding with our OLAP controls, as mentioned below. Easily bind to SQL Server Analysis Services or any other XMLA-compliant OLAP database.
There is also a “Report Builder” utility that lets you visually build a report by dragging and dropping dimensions and measures in a RAD environment. You can also customize the appearance of a chart within this utility. After constructing a report, persist it as XML, load it back in your reporting applications, and simply data-bind to the OLAP controls again with just a few lines of code.
Build highly intuitive dashboards and scorecards in WPF, Silverlight, and ASP.NET using our BI Grid, BI Chart and BI Gauge controls. These controls are highly interactive and put business-critical data at the fingertips of your company executives.
BI Grid is a highly interactive cross-tab/pivot-table implementation that lets you visualize multiple dimensions and multiple levels in a tabular format with drill-down or drill-up capabilities. Built-in support for summaries and ToolTips enhance the user experience. Styles support also allows you to fully customize the appearance of a grid.
An OLAP grid
There is also built-in support for showing KPI value, status, and trend within a grid via built-in or custom icons.
An OLAP grid showing KPI values