Business intelligence has become much more mainstream than ever before in recent years. This is happening perhaps because it represents one of the last mountains left to climb in classic information technology.
The idea behind business intelligence is that organizations have the ability to gather huge amounts of information about their operations that when viewed in detail are often meaningless—the classic problem of not being able to see the forest for the trees. If you can collate that data and browse it in an ordered way, insights are there to be found in any business.
By employing the tools and practices of business intelligence, a retailer might discover that their sales of clocks increase shortly after a drought, or that unfinished furniture is more in demand in stores closest to the coast during summer months. There might be reasons behind the things that are discovered, but the real value in the data is the ability to exploit it and maximize positives or minimize negatives based on the nature of the insight.
Business intelligence is a great example of the adage “knowledge is power” put into practice. When I first heard it in conversation, business intelligence was the term used to describe the business-shaping data that could be gleaned by leveraging a data warehouse, as that was the primary tool for this kind of introspection back in those days. Data warehousing is about gathering all information about every aspect of a corporation’s operations into a single place and aggregating it across many dimensions.
This requires a special capability in the database server that Microsoft has supplied with its Analysis Services. This kind of data processing differs from most line-of-business systems. We refer to typical business systems as Online Transaction Processing (OLTP), but Analysis Services supports Online Analytical Processing (OLAP). OLTP uses database tables and lets the user read and write data as part of running the business. OLAP is the after-action review that figures out what is driving or holding back the business.
Rather than classic relational tables, Microsoft SQL Server Analysis Services provides the ability to shape this data into multi-dimensional cubes. To wrangle these cubes, Microsoft SQL Server Analysis Services leverages a special form of SQL syntax known as Multi-Dimensional Expressions, or MDX for short. MDX provides a language capable of navigating the OLAP database cubes.
For a number of years, OLAP was the only path to understanding what is going on under the covers of the business. This is still the case, but the information becomes more accessible once it is collected into one place. Correlations and buried synergies hide well among the data, and without end-user tools up to the task, the insights can only be searched for by the keepers of the data. This middleman has been the standard for a long time and has obvious inefficiencies that are in need of repair. Ultimately, the multi-dimensional cubes of OLAP will yield the data, but not present it in a way that is easy to consume or recognize. Excel exports were the order of the day for years.
The good news is that this situation is really the part of the landscape that has been changing drastically in recent times. Interfaces that really make the data and trends understandable with tracking across time and other dimensions have emerged with recent product acquisitions and their integration into the Microsoft business intelligence bag of tricks. Perhaps the most major development in this area is that Microsoft SharePoint Server has become a major vehicle for enabling consumption of business intelligence data.
#!
How we got here
This process of evolution toward more direct access to the data has been in motion for years. Microsoft has been continually adding to the capabilities and arsenal for business intelligence with its offerings. In 2006, Microsoft announced the introduction of PerformancePoint Server 2007, which added scorecards and planning functionality to the tool set. When Microsoft originally announced this first version of PerformancePoint Server, it called it “BI for everyone.” Microsoft recognized that the barriers were starting to come down and access to these tools was democratizing one of the last bastions of proprietary knowledge. That is what it is all about in this round, bringing the ability to delve into the guts of the business through the data to anyone.
Each business is somewhat unique, but there are always commonalities. By providing scorecards, you can measure what matters for your particular business. Before the introduction of PerformancePoint Server, the construction of a scorecard was a major effort that really required a custom development effort to get right. The same goes for dashboards. A dashboard enables users to explore the data from within a browser to find their own answers by filtering and drilling down.
Dashboards are where business intelligence meets SharePoint. Since the very earliest versions of SharePoint, Web Parts have been a basic component, and they are the building blocks of dashboards, so it makes sense that SharePoint is also a big part of the story that brings business intelligence to the masses, as we will see a bit later.
I have been in and out of working on OLAP projects over the years, but have spent most of my time and efforts with OLTP projects. About 15 years ago, MDX was about all we had to get answers from the data, and just as in the old days of computing on mainframes, there was no self-service model: Business intelligence required someone technical to read the tea leaves. For this reason, business intelligence used to be the province of the giant companies with legions of analysts and data warehouses that pulled data from every corner of the globe. Some enabling technology developments—along with a good deal of focus by Microsoft and other vendors to bring these powerful tools down to earth—have conspired to eliminate most of the hurdles for even smaller companies that want the best insight into their own operations.
I discussed how things have changed with Andrew J. Brust, founder and CEO of Blue Badge Insights, and coauthor of the upcoming book “Programming Microsoft SQL Server 2012.” Since Brust has been knee-deep in this space, I asked him what he thought the smallest business was that could and should look to use business intelligence now. He said that “no business is too small, now that the tools are so much easier to use. Even sole practitioners should be visualizing their data so they understand better how to optimize their time, revenue and marketing.”
The next crop of Microsoft tools
Even if most hurdles have been bypassed, that does not mean that obstacles do not still remain. Some things are still hard in the business intelligence space. Brust pointed out that “building out data warehouses and data marts is still a huge undertaking that requires elusive enterprise-wide consensus on what data is important, and enterprise-wide adoption to make the project successful and continually valuable.”
Once you get past this stumbling block, self-service tools like PowerPivot pave the way to getting at the data without the end user having to know how to formulate their own MDX queries. PowerPivot is a free add-in that works with Microsoft Excel 2010. It effectively extends Excel to make it more suitable for self-service business intelligence, such as removing the current row and current column limitations, enabling imports of larger data sets and providing relationships between data layers so that data can be imported from various sources. This takes Excel and puts it on steroids specifically for business intelligence purposes.
PowerPivot and the new “tabular” mode of Analysis Services in SQL 2012 are examples of the columnar technology that is working to our advantage. In fact, SQL Server 2008 R2 has a redesigned SQL Server Analysis Services engine that has moved away from standard disk-based storage in favor of the columnar approach. The compression capabilities provided by this approach, along with the dramatic expansion of available RAM on most systems, makes in-memory methods usable like never before.
We talked about column store indexes in a past issue, but we did not talk about a technology called VertiPaq that provides the speed needed to pull off these feats and make the performance acceptable when dealing with this much data. Even with huge numbers of rows, the in-memory technology makes the results in PowerPivot appear in seconds instead of minutes.
On this topic, Brust commented, “In-memory/columnar technology is making it much easier to build analytical databases and boost their query performance significantly. New data visualization tools, like Power View, are making it much easier to explore these analytical data sources or understand the data in them, and it does it all without a ton of formal training or a waterfall style build-out.” Maybe we needed this breakthrough to free these kinds of computing tasks from the penalty of disk I/O in order for business intelligence to finally go mainstream.
As someone who often serves in the role of system architect, I saw the writing on the wall that these changes were coming, but it was a demo I saw last year that told me that the times were really changing. I saw a demonstration of what was then called Project Crescent. In the demonstration, business intelligence data was pulled almost effortlessly in a drag-and-drop designer that allowed for ad hoc reporting the likes of which I had not seen before.
The part of the demonstration that really brought it home was when they showed the ability of Power View to track over time and show in time-lapse fashion how the data changed. Power View is included as part of SQL Server 2012, the upcoming version of SQL Server that was formerly codenamed Denali. Both of these former code-name holders were christened with their final names at PASS Summit 2011.
This same demonstration of tracking of data over time was repeated at the Convergence Conference in 2011 during the keynote. Also highlighted during that keynote is business intelligence being a major feature of Microsoft Dynamics NAV. Business intelligence is described this way on the Microsoft Dynamics website: “Provide self-service reporting tools and real-time business intelligence that help your people make fast, informed decisions and reduce ad-hoc requests to your IT department.”
A simple, but still impressive version of the demonstration is outlined on Jason Thomas’ blog. He makes it easy for you to leverage SQL Server Virtual Labs to recreate this yourself. I think it will reveal quite a lot of those hidden insights far faster than staring at rows and columns. Everyone who was with me when I first saw the demo was quite impressed as well.
Clean data is another challenge, since the wrong conclusions can be drawn if the data is wrong or “noisy” in the sense that invalid data needs to be purged. Microsoft has also addressed these issues with SQL Server 2012. Brust pointed this out, saying, “I think that the new Data Quality Services and the improved Master Data Services components of SQL 2012 are making it easier to have better, cleaner data, which engenders better end-user buy-in on corporate BI projects.” This is especially important for enabling the users to trust the data and by extension trust the conclusions drawn from their own analysis.
#!
SharePoint as a business intelligence interface
As mentioned before, SharePoint is virtually the birthplace of Web-based dashboards. I have been involved in SharePoint development and implementations since it was first released, and even before with its precursors such as Site Server. It is only in the most recent versions that SharePoint has formally entered the business intelligence space. With SharePoint 2010, that formal presence is codified as one of the six core pillars of SharePoint. In this case, the “Insights” pillar is all about business intelligence and is supported by PerformancePoint Services for SharePoint, PowerPivot for SharePoint, and close integration with the advancements as they are released in SQL Server.
SharePoint is a platform for development and designed to be the hub of enterprise interactions. Project sites, document management and more are all built in and provided via Web interfaces. As with almost all Microsoft server products, it works much better with Internet Explorer. By tying all of the major tools of business intelligence reporting into SharePoint, Microsoft gets to attract more enterprise customers to adopt SharePoint and it works well in the “one interface to rule them all” pitch that makes SharePoint very appealing to large organizations that are very interested in the single sign-on SharePoint provides. This is a good move as it pulls together the elements for self-service business intelligence as they are brought to market.
To bring all of these components together in a comprehensible way, SharePoint provides an out-of-the-box template called the Business Intelligence Center. This template not only provides how-to guides for getting the business intelligence capabilities working, it provides examples of how things such as the Chart Web Part works.
As mentioned before, PerformancePoint Server is provided as part of SharePoint 2010. One of the features of PerformancePoint is the support for Decomposition Trees. A Decomposition Tree is a report type that lets you dig through the data and perform a root-cause analysis. Since this style of report lets the user dig through the data and gives them choices as they navigate, they can choose whichever dimension they want to use at each level of detail.
Thanks to this, a non-technical user can iterate over many paths and scenarios very quickly. The implementation of Decomposition Tree support is done in Silverlight, which requires the user to have Silverlight installed on their client system, but aside from that and the aforementioned preference for Internet Explorer, the client requirements are minimal.
Development tools
Now we come to the ecosystem part of the picture. As we have seen, Microsoft has done a good deal to make business intelligence data accessible and inexpensive to leverage throughout the enterprise. This brings us to the core question of what is a developer to do: Is the best course to jump in and learn MDX and how to provision OLAP cubes in SQL Server? Probably not, since if that was the answer, it would have happened long ago and these skills would have ended up as basic skills in the developer’s bag of tricks.
If Microsoft bridges the gap and ultimately succeeds in bringing business intelligence “to the masses” as it has been claiming for the last few years, then it means that there will be huge opportunities to fill in the niches it does not serve and to create the experiences around that bridgehead. This is where we find ourselves when SQL Server 2012 releases later this year, and the time to get a handle on the developer tools landscape is now if you want to be ready for it when it comes.
Ignoring business intelligence has been a winning strategy for most developers up until now, but the important thing to understand is that this is changing. I do not intend to be overly dramatic; of course if you are a solid developer, you will still likely be employed in a few years without deep knowledge of this area. However, there will be elements expected and ultimately demanded in solutions that you will be incapable of servicing if the world of business intelligence is a black hole in your knowledge base. This process of mainstream tool development in business intelligence has been gradual enough that the mature ecosystem of developer tools that support Visual Studio has some impressive solutions that add to capabilities in this space, and more importantly fill in the gaps—especially in niches that Microsoft might never get around to servicing.
This is where the opportunity lies, since if you have the right tools or even just know that they exist, you are in a position to recognize situations where they (and by extension you) can save the day. GrapeCity has a component named ActiveAnalysis that provides what appears to be drag-and-drop business intelligence reporting. ActiveAnalysis is to business intelligence reporting what the component grids were to the early days of .NET development. It allows you to get up and running with a self-service business intelligence browser without much code at all.
I talked about how ActiveAnalysis is being used in the field with Russ Fustino, senior developer evangelist with GrapeCity, while he was presenting at the CodeMash event. The design goal, like with many of the latest offerings from Microsoft, was to create a “component that empowers users to analyze data by letting the developer set up the data source and let the user do the actual analysis.”
This is what Power View and other tools are doing as well. The problem is that sometimes you cannot use the out-of-the-box solution. Without development tools to support this new style of reporting, organizations would have to simply be satisfied with what they were offered by the commercial packages. In my experience, this gets old very quickly. ActiveAnalysis currently supports Silverlight, Windows Forms and ASP.NET, though I expect with the success of this market that GrapeCity will keep pace and support new platforms from Microsoft as they emerge.
Another component maker, Dundas, has introduced a new version of Dundas Dashboard Version 3.0, which now includes OLAP features that Brust has used for some projects. He feels that “Without a doubt, Dundas Dashboard is an excellent dashboard product, and it is especially customizable by Silverlight developers, as it is Silverlight-based.” Dundas, a longtime player in the component space, has also adopted the term “Self-Serve BI” in its marketing materials.
This is where everyone is taking us in this space. If you want to explore, you should also take a look at OfficeWriter by SoftArtisans, which offers template and .NET code-based creation of Word and Excel documents, and offers authoring and rendering integration with SQL Server Reporting Services that is very powerful. If you want to journey deeper into the cube space, then CubePlayer provides tools around MDX, including IntelliSense, which is remarkably handy if you do not delve into MDX all the time. I see these and other projects that support homebrew business intelligence solutions as affirming the direction of things.
#!
Business intelligence beyond SQL Server 2012
SQL Server 2012 is so close to release that there are few surprises expected to be in the final product. While that is great for planning at this point, it is interesting to think about where the next round of innovation will take us.
The indications are that Big Data is on the hit list, and that means Hadoop. Brust agrees: “The worlds of Big Data and BI are colliding, and Microsoft is taking a lead there. Their project, ‘Isotope,’ brings Hadoop to Windows and Windows Azure, and then uses an ODBC driver to integrate it with PowerPivot, Analysis Services, Power View and Reporting Services. This will do wonders to bring the rather segregated world of Big Data into the mainstream BI fold.”
So it looks like the data-tools side of development will not be resting anytime soon. After the big lull between Microsoft SQL Server 2000 and Microsoft SQL Server 2005, the last two releases of SQL Server have been more in tune with the pace I think we can expect going forward.
With the coming release of SQL Server 2012 (the first half of this year if press releases are to be believed), if an organization can make that first difficult step of collecting its business data in a way that is repeatable, then the new tools will help a company or organization of any size gain insight into its business. There are even choices to be made regarding development and reporting tools.
This has been a steady progression, with better tools coming to the fore in almost every round. With Denali coming soon, business intelligence implementations will get even easier, and you can bet we have not reached the end of the innovations yet. It is likely that the SharePoint road map will continue to add business intelligence innovations and will make adoption of a comprehensive business intelligence strategy essential.
We are still not at the point where business intelligence is an out-of-the-box experience, and it might never get there. That makes it perhaps a perfect space to differentiate yourself and your team as business intelligence becomes mainstream and even creeps into more and more specifications.
