Development Enhancements


Analysis Services has undergone a number of development enhancements with the SQL Server 2005 release. The Business Intelligence Development Studio has replaced the Analysis Manager as the primary development tool. In addition, Analysis Services is now accessed using an entirely new protocol, XML for Analysis (XMLA), and there are two completely new .NET object frameworks: one for management, Analysis Management Objects (AMO), and the other for applications development, ADO MD.NET.

Business Intelligence Development Studio

The new Business Intelligence Development Studio is the primary tool for developing Analysis Services applications. With SQL Server 2005 there’s a clear delineation between Analysis Services Management and the development of Analysis Services databases. While the SQL Server Management Studio is used to manage Analysis Services, the Business Intelligence Development Studio is used to develop Analysis Services solutions. Like the SQL Server Management Studio, the Business Intelligence Development Studio is based on the Visual Studio shell. More information about the Business Intelligence Development Studio is presented in Chapter 2.

Online and Offline Modes

Unlike the previous version of the Analysis Manager, which always worked in a connected mode, the new Business Intelligence Development Studio functions in both online and offline modes. By default, the Business Intelligence Development Studio works in an offline mode. In this mode the Business Intelligence Development Studio is not connected to the Analysis Services server and all of the changes and objects that you define reside within the development environment until you choose to deploy the project. When the project is deployed, the Business Intelligence Development Studio creates and executes an AMO deployment script. You can track the progress of this script in the output pane shown at the bottom of the Business Intelligence Development Studio window.

In contrast, online mode works much like the old Analysis Manager, where saving the changes that you make in the Business Intelligence Development Studio immediately updates the Analysis Services database on the server. You can switch from offline mode to online mode by selecting the File | Connect To Analysis Services Database option on the Business Intelligence Development Studio menu.

Data Sources and Data Source Views

The first step in creating new SQL Server 2005 Analysis Services projects is selecting the data source and creating a data source view. Very much like a relational data source, the Analysis Services data source essentially defines the server and the database where the data originates as well as encapsulating the authentication information. You define a data source by right-clicking the Data Source node in the Solution Explorer window and then selecting the New Data Source option to start the Data Source Wizard, which guides you through the process of selecting the desired server and database. Analysis Services supports database connections to SQL Server, Oracle, DB2, and Teradata databases.

After the data source is created, you define a data source view to define the fact and dimension tables that you want to use to build your cube. To create a new data source view, you right-click the Data Source View node in the Solution Explorer window and then select the New Data Source View option. This starts the Data Source View Wizard. The first step in the wizard allows you to select the appropriate data source. After selecting the data source, you then select the fact and dimension tables using the Select Tables And Views dialog that you can see in Figure 10-4.

image from book
Figure 10-4: Creating a data source view

The Data Source View Wizard’s Select Tables And Views dialog enables you to optionally filter the list to show a subset of the available database, which can be handy when dealing with large numbers of tables. To select tables and views, double-click them in the left-hand column. This action populates the Included Objects list that you can see in the right side of the figure. When you finish the Data Source View Wizard, the Data Source View Designer, shown in Figure 10-5, is automatically started.

image from book
Figure 10-5: Data Source View Designer

The data source view is essentially an abstraction of one or more underlying data sources, and the Data Source View Designer enables you to customize the information that goes into the data source view. Using the Data Source View Designer, you can customize a number of different aspects and control how the data is presented. For instance, you can define and change database relationships, rename the tables, and create calculated columns. The changes that are made to the data source view are not propagated back to the underlying data sources. They reside only in the data source view.

Cube Wizard

To help you design your cubes, Analysis Services has an all-new Cube Wizard. After you’ve created your data sources and data source views, you run the Cube Wizard by right-clicking the Cube node in the Solution Explorer window and then selecting the New Cube option from the pop-up menu. This starts the Analysis Services 2005 Cube Wizard, which you can see in Figure 10-6.

image from book
Figure 10-6: Cube Wizard

The Cube Wizard in SQL Server 2005 is much more powerful that the Cube Wizard in the version of Analysis Services that was part of SQL Server 2000. The SQL Server 2005 Cube Wizard enables you to build a cube either in bottom-up fashion by selecting the data source and data source view that you defined or in top-down fashion by first designing the cube and its metadata. In the bottom-up scenario, the new IntelliCube feature will analyze the tables that were selected and automatically suggest fact and dimension tables to match their schema attributes. Alternatively, to build the cube in top-down fashion, you can select the Build The Cube Without A Data Source option. In this scenario, you manually define all of the cube’s attributes. You can see the results of the IntelliCube’s table selection in Figure 10-7.

image from book
Figure 10-7: Identify Fact and Dimension Tables

IntelliCube does a pretty good job of automatically selecting the appropriate fact and dimension tables, but it’s not perfect and you can freely change the table classifications that IntelliCube generates. After selecting the desired tables, the Cube Wizard guides you through the process of selecting the measures for the cube. Basically, IntelliCube picks all of the numeric columns as possible measures. You can then select the columns that you want to use. You can see the Cube Wizard’s Select Measures dialog in Figure 10-8.

image from book
Figure 10-8: Select Measures

After the fact and dimension tables have been selected and the appropriate measures have been defined, the Cube Wizard samples the data looking for possible relationships and creating data hierarchies.

Cube Editor

Once the Cube Wizard is finished, the Cube Editor is automatically displayed. The Analysis Services 2005 Cube Editor is significantly enhanced and offers a great deal of functionality beyond the features provided in the previous versions of Analysis Services. You can see the Cube Editor in Figure 10-9.

image from book
Figure 10-9: The Cube Editor

The SQL Server 2005 Analysis Services Cube Editor provides nine separate tabs, and each tab enables you to work with a different aspect of the cube. The nine cube views provided by the Cube Editor are

  • Cube Builder Works with the cube measures

  • Dimensions Works with the cube dimensions

  • Calculations Works with calculations for the cube

  • KPIs Works with Key Performance Indicators for the cube

  • Actions Works with cube actions

  • Partitions Works with cube partitions

  • Perspectives Works with views of the cube

  • Translations Defines optional transitions for the cube

  • Browser Enables you to browse the deployed cube

After the project has been defined, you can select the Build | Deploy Solution option to build the cube on the Analysis Services server. The project options control whether the cube will be processed after it is deployed to the server. By default, the cube will be processed when it is initially deployed.

Cube Browser

Once the cube has been deployed and processed, you can view and navigate through the cube’s dimensions and measures using the Cube Editor’s built-in browser. You can see an example of the Cube Browser in Figure 10-10.

image from book
Figure 10-10: The Cube Browser

The Cube Browser is built using the Office Web Component (OWC). You use the Cube Browser by dragging and dropping dimensions from the cube’s attributes shown on the left side of the screen onto the OWC’s row and column axes shown in the middle of the screen. Then you select the desired measures and drop them into the data field. The Cube Browser automatically retrieves the data and displays it in the browser window.

Profiler

With SQL Server 2005, Analysis Services is no longer a black box server. SQL Server 2000 Analysis Services and SQL Server 7 OLAP Services didn’t really provide any way for the administrator to see what the server was doing. With SQL Server 2005, the Profiler is capable of tracing all of the different functions that are running on Analysis Services just as it does for the relational SQL Server database. You start the Profiler by selecting the Profiler option from Window’s Start | All Programs | Microsoft SQL Server 2005 menu. You can see the Profiler running against Analysis Services in Figure 10-11.

image from book
Figure 10-11: Profiling Analysis Services

The Profiler is also a great tool for learning more about MDX. In addition, the Profiler is also a powerful troubleshooting tool for tracing the activities of the server. You can use the Analysis Services Profiler to capture and replay events on the server.

XML for Analysis (XMLA)

XML for Analysis (XMLA) is a platform-independent protocol that’s based on web services and SOAP. Microsoft SQL Server 2005 Analysis Services uses XMLA to handle all client application communications to Analysis Services. This includes both of Microsoft’s own management and development tools: the SQL Server Management Studio and the Business Intelligence Development Studio. XMLA is optimized for the Internet and is designed to reduce round-trips to the server. In Figure 10-12 you can see how OLAP client and management applications use XMLA to connect to the Analysis Services platform.

image from book
Figure 10-12: XMLA connects clients to Analysis Services

XMLA supports two basic types of functions: execute requests and discover requests.

Execute Requests

As their name suggests, execute requests perform an action; they change the state of objects on the server. You can use execute requests to create, alter, and delete objects as well as process cubes.

Discover Requests

Discover requests are used to retrieve information about objects on the server. Using discover requests, you can retrieve partition, cube, and security definitions. You can also query the system state of the server to learn about the number of connections in use as well as the resource utilization of the server.

ODL Enhancements

One of the most important management enhancements in Analysis Services for SQL Server 2005 is the new Object Definition Language (ODL). ODL brings the same type of scriptable object creation capabilities to Analysis Services that the relational SQL Server has always had. Analysis Services ODL enables you to write scripts that can automatically create all of your Analysis Services database objects. You can also use these scripts to control Analysis Services database versioning.

SQL Server 2005’s new Analysis Services DDL is an open specification that’s built on XML for Analysis (XMLA). Based on an open standard, Analysis Services ODL scripts can be created using any XML-aware editor.

MDX Enhancements

MDX remains the core query language for Analysis Services databases, and in SQL Server 2005 MDX has several important enhancements.

MDX Scripts

One of the biggest changes for MDX in SQL Server 2005 is the ability to group together multiple MDX statements into a script. Scripting enables multiple MDX statements to be executed in sequence. Each MDX statement in the script is separated from the next by a semicolon.

Simplified Syntax

MDX now provides a new shorthand syntax for calculated members. The new shorthand syntax assumes the dimensions for identifying members, making it unnecessary to repeatedly explicitly specify the dimensions in your MDX statements.

Automatic Type Conversion

Automatic type conversions makes it possible to automatically convert from a member to a couple to a set and vice versa. This eliminates the need to write brackets and parentheses when specifying sets, making the MDX simpler and more readable.

Handling Missing Members

Another enhancement in SQL Server 2005 MDX is the ability to handle missing members. With SQL Server 2000, when you defined a report and one or more of the members the report used were no longer present, the report would fail. Various factors could cause this type of condition, including explicit changes to the underlying cube structure, as well as slow changes to dimensions. In SQL Server 2005’s new MDX, you can use the MDXMissingMemberMode dimension property to enable a report to continue to function even when members of a dimension are missing.

Aggregation of Distinct Count Members

Adding another capability that was not present in SQL Server 2000’s Analysis Services, MDX in SQL Server 2005 is now able to aggregate the contents of distinct count members.

Sets in the WHERE Clause

Using sets in the MDX WHERE clause is another important enhancement to Analysis Services in SQL Server 2005. Using sets in the MDX WHERE clause is essentially the same as using the OR keyword in the SQL WHERE clause. Sets enable the query to return the results from all of the members that are contained in the WHERE clause.

ADOMD.NET

ADOMD.NET is an all-new native .NET data provider that’s designed to access multidimensional data sources. ADOMD.NET is designed to be the replacement for the older COM-based ADO MD multidimensional data access object library. Client applications built using any of the .NET languages such as Visual Basic, C#, Managed C++, or J# can use ADOMD.NET to retrieve data and metadata information from SQL Server 2005’s Analysis Services. Under the covers, ADOMD.NET uses the XMLA protocol to connect to the Analysis Services server. ADOMD.NET applications can connect to Analysis Servers in two ways: XMLA over HTTP or XMLA over TCP/IP.

Analysis Services Management Objects (AMO)

Analysis Services Management Objects (AMO) is another entirely new object framework that Microsoft has introduced with SQL Server 2005. AMO is the successor to the older COM-based Decision Support Objects (DSO) that was provided with SQL Server 2000. DSO has not gone away. A version of DSO is included with SQL Server 2005 but is primarily available for backward compatibility.

AMO is built using the .NET framework and is designed exclusively to manage Analysis Services. AMO works at a higher level than XMLA, and like the other new Analysis Services object frameworks, AMO uses XMLA to communication with the Analysis Services server. Both the SQL Server Management Studio and the Business Intelligence Development Studio utilize AMO.

AMO supports secure management connections to Analysis Services. It provides support for Windows authentication as well as an encrypted communications channel between the client application and the server. AMO also optimizes the connection to the server by compressing the XML that is sent between the client and the Analysis Services server.

AMO provides several advantages over the old DSO model. First, AMO intelligently enumerates objects, giving it better performance when listing large numbers of items. AMO also provides the ability to back up and restore the system. In addition, AMO makes impact analysis available to your applications. An impact analysis enables your application to determine what Analysis Services objects will be affected by a given action. For instance, if a statement alters a cube’s dimensions, it could require the cube to be reprocessed. Impact analysis helps to show you the effects of your statements.




Microsoft SQL Server 2005 New Features
Microsoft SQL Server 2005 New Features
ISBN: 0072227761
EAN: 2147483647
Year: 2005
Pages: 80
Authors: Michael Otey

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net