Q&A with Peter Myers
BI Within Reach
SQL Server MVP and BI evangelist Peter Myers is a big believer in getting useful business information to the people who need it. And with SQL Server's accessible BI features, he says, transforming all the data that organization's collect and store into knowledge that supports better decisions is easier than most people imagine.
In his Nov. 2 PASS Summit Unite 2009 pre-conference seminar, "Delivering Business Intelligence to the Masses," he aims to convert us all, demonstrating how to use SQL Server 2008 and the Microsoft Office System to deliver state-of-the-art integrated reporting, analytic, and performance-management solutions into users' hands. Here, he gives us a sneak peek.
Q: What’s one of the most innovative and effective BI solutions you’ve seen recently using the Microsoft BI stack?
A: Commonly today, BI solutions built using SQL Server involve Analysis Services cubes that underpin the organization’s reporting and analysis needs. Increasingly, I’m witnessing these cubes becoming richer in content and in the number and sophistication of embedded calculations. As a result, whether it’s because of corporate policy or by choice, these cubes are becoming the “one version of truth” that support almost all data requirements, be it for reports, interactive analysis, scorecards, or dashboards. Also the increasing use of data mining—whether indirectly used in data integration tasks, embedded in applications, or directly delivered to end users for the purpose of discovery—really excites me. There’s a lot of potential and creativity occurring in this space.
Q: What are the biggest differences between a basic BI solution vs. a state-of-the-art integrated solution? What kind of time, effort, and cost is involved in getting from one to the other?
A: The basic BI solution usually involves delivering on immediate needs and isn’t designed to think “wide” in terms of scope. By implementing a state-of-the-art solution, organizations can support better business decisions. Evolving the initial efforts into a state-of-the-art solution can be a smooth or painful experience–it usually depends on how well you understood user requirements upfront and factored them into the initial design.
Q: What do you think of the new functionality coming in SQL Server 2008 R2?
A: Project Gemini is probably the jewel in the crown of the new functionality coming in SQL Server 2008 R2. It lets users integrate corporate data with unstructured data within Excel and then publish the data together with reports to SharePoint. SharePoint can then make the data and reports available to users within the organization and gives IT the ability to manage and support the published data and reports. I like that Gemini takes the analytical potential of Excel data analysis to the next level and beyond the limits of reporting only on data made available by IT.
Q: What BI deliverable has had the biggest impact on the functionality and usability of Microsoft’s offerings? What’s your favorite BI feature?
A: Without doubt, I see the SQL Server Data Mining Add-ins for Office 2007 delivering advanced and powerful technology into the hands of everyday users. Users can now easily perform functions such as anomaly detection, segmentation, and forecasting on their data in workbooks without having to understand how the data mining engine is producing the results.
SQL Server's data mining capabilities are my favorite BI feature simply because of their potential to improve existing processes and applications in innovative ways. SQL Server data mining makes this advanced technology available and within reach to organizations and users that have traditionally been excluded, usually due to the expense of the technology and the lack of expertise required to model and interpret the mining results. Almost all organizations now have the ability to investigate the potential of their data, and I suggest that my customers and students do just that. I encourage them to invest a little time to explore what data mining has to tell them about their own data, and I’m excited by the feedback I often hear.
Q: If you could change one thing to improve Microsoft’s BI offerings, what would that be?
A: I think the thin-client experience for cube browsing could be improved. What we see available today in Excel 2007 PivotTables is a rich set of functions, including drilling, sorting, filtering, and pivoting as well as the ability to interact with and modify the query axes. Only a subset of these capabilities is presently available in the thin-client experiences offered with Excel Services and PerformancePoint Server reports. I suspect and hope we'll see enhancements to the existing offerings in the upcoming release that will let you accomplish much more of what Excel 2007 PivotTables offer.
Q: In your experience, what scares people the most as they look at implementing a BI solution?
A: The number one barrier is probably the impression that to implement BI, they must start big. I think this is because many publications introduce BI with examples of enterprise data warehousing. While this is admirable goal, it can be daunting to embark on such an ambitious project from the outset. I suggest that organizations start small but design big. This is akin to building a house: There may be multiple levels in the future, so build strong foundations to begin with. To do this effectively, the BI architect or developer needs to take the time to understand requirements and map them to the potential of the data and the capabilities of BI platforms. To do this properly, time and know-how are the biggest roadblocks.
Q: Let’s say a company on a tight budget and with a small IT staff still wants to start taking advantage of BI to make better business decisions. What one or two things could it put in place to reap some immediate benefits?
A: I would suggest starting with a cube that focuses on a single subject area. A low-cost approach may be to produce a series of views based on a source database that mimic a star schema, and then develop the cube on top of this. Process the cube periodically during an off-peak time so that it doesn't impact the operations of the source database. Then, use Excel 2007 to interactively explore and report from the data. Organizations that have Analysis Services installed can also easily harness the potential of Office data mining add-ins.
Q: What are the most common mistakes people make in implementing BI?
A: I often see organizations rolling out BI solutions that solve an immediate problem but don’t plan for the growth of the design. The business can then have a big problem when that growth happens because it can result in radical changes to data structures that may invalidate published reports.
Q: Your seminar covers a lot of ground and features a lot of real-world demonstrations. What will attendees take away from the workshop?
A: Attendees will see comprehensive demonstrations that show pretty much all BI features and capabilities available to users, plus handy tips and best practices. My favorite demonstrations are the data mining capabilities because they are novel and very cool. For example, participants will see a demonstration that shows how to embed prediction results directly into an Excel workbook.
In the end, I think attendees are going to appreciate the many avenues they have to deliver BI to their users. They'll learn that sometimes there are a number of ways to achieve the same goal, and the seminar will help inform them about which approach to take given different circumstances and requirements. I also think attendees will realize that effectively delivering BI is easier than they imagined, and that some features such as Excel PivotTables and the data mining add-ins are just plain cool once you see how they're designed to work.
Hear more from Peter and other SQL Server and BI experts at PASS Summit Unite 2009 in Seattle, WA. See the lineup of pre/post-conference seminars, review the full conference agenda, and register today.
SQL Server MVP Peter Myers, a trainer and mentor with Solid Quality Mentors, has 12 years’ experience working in SQL Server OLTP database design and development, DBA support, and data warehousing/BI. Specializing in custom data-focused applications and reporting solutions, he provides architecting, development, implementation, and support services at all tiers of a multi-tier application. Peter frequently presents at SQL Server user group meetings and technical events, including TechEd (US and Australia) and VSLive.