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.
What does Syncfusion Provide?
Syncfusion Essential Studio for BI builds upon ADOMD.NET to provide a set of utilities and controls that let you build custom OLAP reporting and OLAP analyzing solutions to view multidimensional data in your OLAP data sources. A free, fully functional version is available for evaluation.
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
BI Chart is a richly interactive visualization tool for any size OLAP data. It comes with several chart types and also supports drilling down or up. The appearance of a chart as well as an individual series can be customized to your needs.
An OLAP chart
Essential BI Gauge is ideal for highlighting business critical KPI information in executive dashboards and report cards. Gauges let you present values against goals in a very intuitive manner.
An OLAP gauge
Combine all the controls with our powerful document publishing suite of libraries in Essential Studio Reporting Edition and you will have all the tools required to build a complete BI solution for your organization.
Use the Reporting studio to allow your end-users to subscribe to reports (via email for example) and publish reports in the following formats:
- Adobe PDF
- Microsoft Excel
- Microsoft Word
Create rich reports by embedding images of charts, grids, or gauges.
Static reports are a thing of the past. Decisions cannot be made just by looking at a static report. Any abnormalities in the reports need to be analyzed using more interactive analysis tools by drilling down or drilling all the way through to the relational data.
For example, poor sales in a division could be tracked down to poor sales of a particular product, and comparative sales reports of a product across divisions can give you a better picture of problems with that particular product.
The good news is that such analysis tools need not require deploying expensive solutions just for executives with exclusive privileges; such tools can easily be put together with minimum development cost and zero additional cost for server and client deployment.
Syncfusion Essential BI Studio, which includes OLAP Client—a rich and composite control that easily binds to any OLAP data source and provides a UI for effective drag-and-drop and slice-and-dice analysis—along with Essential Studio Enterprise Edition—an invaluable suite comprised of Grid, Diagram, Office 2007 UI, Heat Maps, etc.—can be used to build rich, interactive OLAP analysis applications.
The image below shows a potential OLAP analysis UI.
Essential Studio BI Edition
To see the functionality first-hand, feel free to download the fully-functional evaluation.