Chapter 13. Inside Analysis Services OLAP Tools

Microsoft SQL Server 2000 provided two main tools for developing and managing OLAP cube and Mining Models. Analysis Manager was the main tool. Built on the Microsoft Management Console (MMC) infrastructure, it enabled the Database professional to develop and manage OLAP servers and mining models against a live server. In this tool, Design and Management tasks were combined in one unique environment. The other tool that was available was the MDX Sample. Although it was shipped on the CD as sample code, it turned out to be one of the most useful tools to issue MDX queries against the Analysis Services servers. It was particularly important for testing data and the performance of specific queries.

With SQL Server 2005, Microsoft has redesigned its tool approach to better fit different roles played by different individuals or teams in a company. Most aspects of functionality are now split between the BI Development studio for design tasks and SQL Management Studio for management and other operational tasks. When we started early designs and prototypes in 2000, the decision had not yet been made to integrate the BI development environment with Visual Studio, but very early it was clear that we wanted to provide an experience to the "BI developer." Now the BI developer is often an odd role; it doesn't necessarily map with any existing title in the IT organization. Traditionally, the person in charge of designing the Cubes or Mining model fits one of the two possible molds:

• He or she is in IT yet has a good knowledge and understanding of user requirements or situations.

• He or she is not in IT, belonging instead to the user community, but has good technical ability. This person is fairly comfortable with some DB concepts and writing some business-oriented logic (MDX)

So we rapidly had to make a decision about the level of technical ability that our tool should require as well as what the best Integrated Development Environment (IDE) was for such an environment. Although we decided that we should leverage the Microsoft IDE, which is Visual Studio, we also decided that we would focus on designing tools that would be appropriate for not only developers (coders) but non-developers as well. This is why every single task can be achieved through graphical wizards and designers as well as through scripting and coding using our APIs. We do realize, though, that even though we have made tons of investment in providing a very business-oriented wizard, the IDE environment can still be somewhat overwhelming for the non-developer. Look at the development tools in SQL 2005 as our first step in this direction. I'm sure that as we learn more from customers' experiences and hear more feedback from users and developers, we will refine and provide tools in the future that are even better suited to individual roles, levels of expertise, and tasks.

This chapter tries to walk the reader through many scenarios that cover most of the key functionality of the tools while also providing insights, tips, and techniques. The reader will find a lot of valuable "How to" techniques throughout this chapter.