Overview of the Microsoft Tools

Many readers will start to work with SQL Server 2005 by experimenting with its functionality in a single machine sandbox. If you have the time and bandwidth to do so, this is a great way to determine which product features are important to your business and users. In this environment, we recommend that you acquire a true sandbox machine: a new or rebuilt machine with a clean operating system and no other applications. You can use virtual PC technology to simulate a clean machine and run SQL Server well enough to evaluate functionality (although not performance).

Other readers are launching real projects and need to be more rigorous and thoughtful in setting up their environments. As we discuss in Chapter 14, plan from the outset for the standard three- tier system, with separate servers for development, test, and production. Your test system should be as similar to the production system as you can possibly make it. The more different your test and production systems are, the more difficult it is for your database administrators to evaluate alternative approaches to tuning and configuration before rolling those changes into production.

Most teams set up their development environment with a central server or two to hold relational and Analysis Services databases. Developers install the development toolsthe studio workbenches described in the next sectionon their own machines, and point those tools to the development database server. Early in the development cycle, developers may have personal databases, either on the shared server or on their own machines.

Which Products Do You Need?

At the time of the SQL Server 2005 launch there are three editions available that are interesting for DW/BI projects:

• Enterprise Edition

• Standard Edition

• Developer Edition

INTEGRATE DEVELOPMENT DATABASES EARLY

Dont delay for too long the integration of the development databases, as there are usually all sorts of surprising differences between databases that are supposed to be the same. The project manager should encourage developers to move to shared databases as soon as they can do so without harming productivity.

 

You will need to purchase and run either SQL Server 2005 Enterprise Edition or Standard Edition on your production servers. Enterprise Edition contains the entire product feature set, andunsurprisinglycosts several times as much as Standard Edition.

Reference 

You can find detailed information on each edition at www.microsoft.com/sql , and in Books Online. See the Books Online topic Features Supported by the Editions of SQL Server 2005.

Besides Standard versus Enterprise Edition, you need to decide on the 32-bit platform versus the 64-bit platform. As we discuss in Chapter 4, almost everyone should use the 64-bit platform for DW/BI projects.

There is no hard and fast rule for which edition you should purchase. A simple rule of thumb suggests that Standard Edition is probably sufficient for most small and some medium implementations . If your data volume, measured as data only without indexes, is 50 gigabytes (GB) or less, then you can do without the scalability features in Enterprise Edition. Depending on incremental load volumes , frequency, and uptime requirements, a medium- sized implementation of up to 250GB can also work on Standard Edition. Any large, real-time, or otherwise challenging implementation should plan to use Enterprise Edition.

Whichever edition you use in production, your developers should use Developer Edition. Developer Edition is extremely inexpensive (approximately $50 at the time of publication); it will run on desktop operating systems such as Windows XP; and it contains all the functionality of the Enterprise Edition.

EDITIONS AND FEATURES OF SQL SERVER 2005

Enterprise Edition features that are excluded from Standard Edition support scalability in the enterprise. Mostly, scalability refers to data volumes, and often has more to do with maintaining and operating very large systems than actually storing and querying them. Another dimension of scalability is complexity; some of the excluded features would help your business users navigate a complex enterprise-level system more easily.

Here well list, and comment on, our favorite enterprise edition features:

• Relational database engine

  • Relational database partitioned tables are a key feature for fast loading and improved maintainability of large tables. We talk about partitioning in Chapter 4 .

  • Relational database maintenance functionality, including online index operations and parallel index operations, is particularly important for loading new data into large tables in a short time frame and performing periodic maintenance.

  • Relational database failover clustering beyond two nodes is important for improving the availability of your relational data warehouse database.

• Integration Services

  • Integration with Analysis Services dimensions, cubes, and data mining models is a really nice feature, but not absolutely necessary. You could have Integration Services launch a script that updates the cube or data mining model.

  • Advanced transforms such as fuzzy lookup and text mining are very cool, but they may be too complicated for most small projects to deal with anyway.

• Analysis Services OLAP engine

  • Scalability and performance features, such as automatic parallel processing and partitioned cubes, are very important for delivering great performance with medium and large data volumes, greater than 100GB of source data.

  • Proactive Caching is a feature that automates the flow of data into the cube database. It improves system manageability, and is particularly important for real-time applications.

  • Features such as Account Intelligence, Writeback Dimensions, and particularly Perspectives, Semi-additive Measures, and Translations let you build more usable and complex OLAP databases.

• Analysis Services data mining

  • Parallelism for processing and prediction is important for large data volumes and heavy usage scenarios.

  • The statisticians in your organization will appreciate the advanced tuning and configuration options for the algorithms.

  • The integration with Integration Services, as weve already described, is useful but usually not absolutely necessary.

• Reporting Services

  • Scale-out report servers are an important feature for large-scale deployments. This is basically creating a web farm for the report servers.

  • Data-driven subscriptions may be useful for a large enterprise. With this feature, you can email each manager his or her personalized budget variance report, run from a single report definition.

 

SQL Server 2005 Development and Management Tools

Two toolsets are installed as part of the client tools installation. The SQL Server Management Studio (Management Studio) is used to operate and manage your DW/BI system. The Business Intelligence Development Studio (BI Studio) is used to design and develop your business intelligence system.

SQL Server Management Studio

Management Studio is the primary tool for database administrators. It replaces and extends the operations and management functionality from SQL Server 2000 Enterprise Manager and Analysis Manager. In most cases, the Management Studio client tools are the only component of SQL Server that is installed on database administrators workstations. The Management Studio screen is pictured in Figure 3.3.

Figure 3.3: SQL Server Management Studio

When you launch Management Studio for the first time, you are faced with a screen that is mostly empty. On the left, under the toolbar, is the Registered Servers window. (If this window is not showing, you can choose View Registered Servers.) In this window, you register all the servers that you manage. If you have the necessary permissions, you can start, stop, and rename those servers. Note that there are little icons at the top of the Registered Servers window that let you flip between managing relational, Analysis Services, SQL Server CE, and Reporting Services servers. In Figure 3.3, were looking at the registered Analysis Services servers. Developers and designers will not use the Registered Servers window very much; you can close or hide it to save screen real estate.

The Object Explorer Window is also on the left. In this window you can connect to a database server (SQL, Analysis, CE, or Reporting) and browse its contents. The user experience for browsing a database server is very familiar to users of earlier versions of SQL Server. While browsing a SQL Server instance, notice that user databases and tables have been separated from system objects. Notice also the new sample databases called AdventureWorks and AdventureWorksDWthese databases replace the old Pubs and North-wind. AdventureWorksDW is Microsofts dimensional DW/BI version of the AdventureWorks transactional database. Once you connect to an Analysis Services instance in the Object Explorer window, you can browse the corresponding Analysis Services demo database, Adventure Works DW.

The right-hand pane of Management Studio serves as the viewing area. The information that displays in the pane depends on what youre doing. In Figure 3.3, weve just launched Management Studio, and the right-hand side shows the objects in the database. In this area youll write and execute queries, and you can have multiple documents open at the same time. Figure 3.3 shows a very small screen. On your workstation, the right-hand side will probably dominate the display.

From within Management Studio you can script any object in your relational, Analysis Services, or Reporting serverany table, cube, database, data mining model, package, or report. The scripts for relational database objects are generated as familiar CREATE statements. Scripts for other BI objects such as cubes, packages, and reports are generated as XML.

It is technically possible to edit and re-issue the BI object creation XML. In fact, it is technically possible to write a complete Analysis Services cube definition or Integration Services package by typing an XML file. Just because something is possible doesnt mean its a good idea. Use BI Studio to design and debug BI objects (except for the relational database). In the rare cases when you need to automatically generate an object, you should use the appropriate programming object model rather than attempt to manipulate the XML directly.

If you generated a script for a database object, the first thing you probably noticed is that the query pane is integrated into SQL Manager, and supports SQL for the relational database; MDX, DMX, and XMLA for Analysis databases; and even XQuery for querying XML.

Readers who use Visual Studio will have noticed that Management Studio bears a strong resemblance to the Visual Studio development environment. Indeed, it is not just a resemblance: Management Studio and BI Studio are hosted in the Visual Studio shell. The integration with Visual Studio is particularly important for the BI Studio development environment.

Note 

Microsoft ships a short tutorial for SQL Server Management Studio, which you should run through. The tutorialsand sample databases, for that matterarent installed by default when you install SQL Server. If you didnt install them initially, run Add or Remove Programs, and modify the SQL Server installation.

Business Intelligence Development Studio

BI Studio is designed for all BI system designers and developers, with user interfaces for designing and debugging Analysis Services databases, data mining models, Integration Services packages, and Reporting Services reports. The obvious omission from this list is the design and development of the relational data warehouse database. Use SQL Studio for the relational part of the project and BI Studio for the rest.

Like SQL Studio, BI Studio is integrated with Visual Studio. This is great news for developers who already use Visual Studio because the interface will already be somewhat familiar. Even though the Visual Studio environment appears complex at first, everyone benefits from this integration. Your team can use integrated source control to manage project files; you can set breakpoints and debug Integration Services packages and MDX scripts; any code you may need to develop is integrated in the same environment; and all projects benefit from a unified approach to separating development from deployment.

Launch BI Studio from the Start menu (Start All Programs Microsoft SQL Server 2005 SQL Server Business Intelligence Development Studio). Once you use BI Studio, your recent projects will be listed for you to choose from. But the first time you launch BI Studio youll need to create a new project (File New Project), as illustrated in Figure 3.4. Ensure that the Business Intelligence Projects project type is selected on the left-hand side. The kinds of new projects available to you depend on what components of SQL Server (Analysis Services, Integration Services, Reporting Services) are installed on your workstation. If you use Visual Studio for programming in a language such as Visual Basic or C#, these projects will also show up on that list. In general, well assume that all components are installed locally, although we know many developers will install only the components they are actively working on.

Figure 3.4: Creating a new project in BI Studio

You can name both the solution and the project within the solution. Directories get created, by default at ..\My Documents\Visual Studio 2005\Projects. The directory name will correspond to the name of the solution. Its very easy to click on a new Analysis Services project and then hit OK and youve just created folders with names like Solution 1 and Project 1. The names can be changed, but its easier to name them intelligently from the outset. You can (and should) include multiple projects in a solutionfor example a project for the Analysis Services database, one for data mining models, several for Integration Services packages, and one for reports. Use a simple naming convention: prefix the project name with AS, DM, IS, or RS as appropriate.

DW/BI development teams should manage their files under source control, and BI Studio makes that very easy to do. You wont see this functionality unless youve installed a source control product like Visual Source Safe, and integrated it into Visual Studio. You can add other kinds of files, such as a Word or Excel document, by right-clicking on the project in the Solution Browser and choosing Add Existing Item, and locating the file in the file system. The file will be copied into the project directory.

The files in the project folder completely define the project: They are the source code for the project. During the development process ( especially if youre not using source control!), you may want to share your project definition with a colleague so she can view your work. You can simply send her a copy of the project folder, ensure she has appropriate database permissions, and she is set.

BI Studio shows different windows depending on whether you are working on an Analysis Services, Integration Services, or Reporting Services project. Figure 3.5 illustrates the BI Studio window for a Report Builder project. This is a new project that doesnt have much content yet.

Figure 3.5: Basic layout of the BI Studio windows and panes

All the different types of projects use a similar layout, imposed by Visual Studio. The Solution Explorer window, located by default in the upper right, lists the project files in the project and lets you navigate between them The Properties pane, located by default in the lower right, shows all the properties associated with an object; read-only properties are gray. As youll see in subsequent chapters, BI Studio contains extensive wizards whose job, in effect, is to help you set these properties.

BI Studio gobbles up screen real estate. It officially requires a screen resolution of at least 1024 — 768, but we cannot get anything done on less than 1280 — 1024. BI Studio is a good excuse to get an upgraded monitor and video card. You can maximize screen real estate by setting windows such as the Solution Explorer and Properties pane to auto-hide.

Many of the BI Studio components use the Toolbox pane on the left-hand side. The toolbox is empty for the start page displayed here, but Integration Services uses it a lot. The big section in the middle, which currently displays the start page, is used as a design surface. In here youll design Integration Services packages, Analysis Services dimensions, cubes, and data mining models, Reporting Services reports, and so on. You generally want this central area to be as big as possible.

Finally, the BI Studio tools use Visual Studios build, deploy, and debugging features such as watch windows. Sometimes it feels like there are dozens of extra little windows, located by default at the bottom of the screen where Error List and Output are displayed in Figure 3.5.

The other chapters in this book focus on the specific BI features such as Analysis Services and Integration Services. In those chapters, we spend more time describing how to use BI Studio, although this book is not intended to be a product tutorial. The tutorials that ship with SQL Server do a very good job of explaining how to use the product.