Using SQL Server Management Studio

SQL Server Management Studio (SSMS) is ground zero for administering the Analysis Servers resident on your network; not only that, you can also administer instances of SQL Server, Reporting Services, Integration Services and SQL Server Mobile from SSMS. In this book you learn administering and managing Analysis Servers, and this chapter specifically discusses the Analysis Services object as shown in the Object Explorer. Administering Analysis Services is discussed in detail in Chapter 11.

The first step in the process of adding objects to the Object Explorer calls for connecting to the services you wish to manage. In fact, as soon as you start Management Studio, you get a dialog to connect to one of the services as shown in Figure 2-39. Create a connection to the Analysis Services through your login.

Figure 2-39

SSMS provides you a way to register your servers so that you do not have to specify the login credentials each time need to connect to a server. Click on View menu item and select Register Servers. You will now see a window called Registered Servers in SSMS as shown in Figure 2-40. In the Registered Servers pane, click the toolbar icon second from left; this enables you to register an Analysis Services instance instead of SQL Server. Now, right-click Microsoft Analysis Servers and select New Server Registration. In the resulting New Server Registration dialog (see Figure 2-41) you need to specify the name of the Analysis Services instances you wish to connect to and optionally specify some of the connection parameters such as time out or enabling encryption. If it was a named instance, enter the name of the instance in the Server Name field, otherwise, type in localhost — this means you want to register the default instance of Analysis Services on your machine. This is the most common configuration. Once you have filled in the Server Name field, you can test the connection by clicking the Test button at the bottom of the dialog. If the connection does not succeed, you need to make sure Analysis Services is running and that your authentication scheme is correct. Once the connection is working, click Save.

Figure 2-40

Figure 2-41

Object Explorer Pane

When you connect to an Analysis Services instance you see the server in the Object Explorer pane (see Figure 2-42). This section reviews the various objects in Analysis Services with the help of the sample database, Adventure Works DW. Open the Databases object to see your Analysis Services database called AnalysisServices2005Tutorial and expand each object. Now you should be looking at a list of seven major objects (Data Sources, Data Source Views, Cubes, Dimensions, Mining Structures, Roles and Assemblies) as shown in Figure 2-42.

Figure 2-42

The following list describes each of the objects:

• Databases: The Databases object is where all your deployed Analysis Services projects are listed; note that these objects could have been created in On-line mode or Project mode.

• Data Sources: The Data Sources object will, at minimum, contain a single object pointing to a data source like SQL Server 2005 if you have cubes or dimensions or mining models. Behind the scenes, they are objects that store connection information to a relational data source which can be a .NET provider or an OLE DB provider. In either case you can establish a connection to a data source. In Figure 2-42, you can see the Adventure Works DW data source, which is called "Adventure Works DW." For any real-world scenario, it is likely you will have multiple data sources.

• Data Source Views: The data source views object will contain at least one object pointing to a subset of the data originally identified by the data source object. The reason this object type exists is because in the enterprise environment the data source might contain thousands of tables, though here you're interested in working with only a small subset of those tables. Using the DSV object, you can restrict the number of tables shown in a given view. This makes working on even the largest database a manageable task. On the other hand, you might want to create a DSV to contain not only all tables in one database, but a portion of tables from a second database. Indeed, you can have a DSV that uses more than one data source for an optimal work environment.

• Cubes: You have already looked at the details of cubes in BIDS; they are the lingua franca of Business Intelligence. Well, those cubes can be viewed in the Object Explorer pane. Further, three sections under the cubes object provide information about how the cube is physically stored and whether or not it will allow you to write back unique data to the cube for "what if" analysis:

  • Measure Groups: Measure groups are comprised of one or more columns of a fact table which, in turn, is comprised of the data to be aggregated and analyzed. Measure groups help in grouping logical measures under a single entity.

  • Partitions: Partitioning is a way of distributing data across disparate systems. You typically partition the fact data if you have a large fact table. In this way you can make the queries return faster; this works because scanning partitions in parallel is faster than scanning serially. There is a maintenance benefit as well; when you do incremental updates (process only data changed since the last update) it is more efficient for the system to update only those partitions that have changed. One variation of the partitioning technique is to partition on data categorized by year. In this way, a single fact table might have only up to five years of the most recent data and is therefore subject to queries, whereas the older, less often accessed data can lay fallow in a fact table partition. If you right-click the partitions object under the FactInternetSales measure group, you will see a number of administrative tasks associated with partitions that can be dealt with directly in SSMS.

  • Writeback: Writeback provides a user the flexibility to perform a "what if" analysis of data or do perform a specific update to a measure such as budget where your budget for next year gets determined by using writeback. The writeback object is empty in AnalysisServices2005Tutorial because it has not been enabled. By default writeback is not turned on. To see what options are available, right-click the Writeback object.

• Dimensions: Dimensions are what cubes are made of and you can see what dimensions are available for use in a given project by looking at the contents of this object. Note that you can browse, process, and delete dimensions from here with a right-click of the mouse.

• Mining Structures: Data mining requires a certain amount of infrastructure to make the algorithms work. Mining structures are objects that contain one or more mining models. The mining models themselves contain properties like column content type, your data mining algorithm of choice, and predictable columns. You have to derive mining models based on a mining structure.

• Mining Models: This is where the Mining Model objects are stored. There is a one-to-one relationship between mining models and algorithms. If you want to use two algorithms based on the same mining model, you have to create a copy of your derived model and associate a different algorithm with it.

• Roles: Roles are objects that define a database-specific set of permissions; these categories can be for individual users or groups of users. Three types of permissions can be set for a role: Administrator level or Full control, Process Database level, and Read Database Metadata level. Roles are discussed with the help of a scenario in Chapter 19.

• Assemblies: You learned earlier in this chapter that assemblies are actually stored procedures (created with CLR or COM-based programming languages) used on the server side for custom operations. If you are familiar with Analysis Services 2000 and the UDF (User-Defined Function), note that assemblies can do anything the UDF can do and more. Also note that COM UDFs in Analysis Services 2000 form are also supported in Analysis Services 2005 for backwards compatibility of existing applications. The scope of these assemblies is OLAP database-specific; that is, an assembly can only operate on the database for which it is run.

• Server Assemblies: If you want to operate on multiple databases in Analysis Services, you have to create this sort of object, the server assembly. Server assemblies are virtually the same as assemblies, except their scope is increased; they work across databases in Analysis Services.