Overview of the Analysis Services Development and Management Environments

 

Microsoft Corporation

July 2004

Applies to:
   Microsoft SQL Server 2005

Summary: SQL Server 2005 introduces a rich set of tools supporting the development and management of Business Intelligence applications. This includes applications using Analysis Services (OLAP and Data Mining), as well as Reporting Services and Data Transformation Services (DTS). (22 printed pages)

Contents

Introduction
Business Intelligence Development Studio
Solutions
Tools Options
Customizing the Environment
SQL Server Management Studio
Main Elements
Other Elements
Profiler
Utilities

Introduction

SQL Server 2005 introduces a rich set of tools, supporting the development and management of Business Intelligence applications. This includes applications utilizing Analysis Services (OLAP and Data Mining), as well as Reporting Services and Data Transformation Services (DTS).

This paper provides an overview of the various environments for users who develop or manage OLAP or Data Mining applications. It does not provide details of all the different designers hosted within these environments. These details can be found within the product documentation.

The SQL Server 2005 Development & Management Environments

The two primary development and management environments are:

  • Business Intelligence (BI) Development Studio. This environment is used to build and maintain BI applications. Hence a DBA or developer defining a cube or mining model would use the BI Development Studio. The functions that can be carried out include:
    • Defining all details of Analysis Services objects (cubes, dimensions, mining models, security roles...), and testing/debugging those definitions (including browsing the data).
    • Defining all details of other components of the application, including Reporting Services reports and DTS packages.
  • SQL Server Management Studio. This environment is used to manage all server types in the SQL Server family, including Analysis Services, Reporting Services, and DTS servers. This supports the management of a deployed application, and the functions that can be carried out include:
    • Management tasks, such as backup/restore, and processing.
    • Defining those details of Analysis Services objects that are deemed to be relevant to management activities e.g. adding a new partition, or adding a user to a security role. There are no design tools provided to define or change other details e.g. adding a new dimension to a cube is not considered a management activity.
    • Browsing data, and issuing MDX and DMX queries.
    • Scripting objects and tasks, and issuing XMLA queries (to create objects, delete objects, process etc.).
    • Changing a server property e.g. the log file location.
  • Hence the two distinct sets of activities of 'development' vs. 'management' are partitioned between the two environments. This is in contrast to the SQL Server 2000 tools, where Analysis Manager was targeted at both development and management.

Both the BI Development Studio and SQL Server Management Studio are based on the Visual Studio Development Environment. The plan is that after Beta 2, the BI Development Studio will simply use the Visual Studio Development Environment, providing an environment that supports the development of all components of an application e.g. including Visual Basic and Visual C# code.

The following tools and utilities are also provided with SQL Server 2005:

  • SQL Server Profiler. The profiler allows trace events raised by the various servers, including Analysis Services, to be captured, inspected, and replayed.
  • Migration Wizard. This wizard migrates Analysis Services objects from SQL Server 2000 to SQL Server 2005.
  • Deployment Wizard. This wizard assists in the deployment of Analysis Services objects, as built within the BI Development Studio, to test and production environments.

The remainder of this paper outlines these tools and utilities in more detail, particularly focusing on the BI Development Studio.

Business Intelligence Development Studio

This environment focuses on development activities. It introduces a new style of development for Analysis Services, known as "project mode". It also supports the "online mode" familiar to users of Analysis Manager. Both these modes of working are described below.

Introduction to Projects

In project mode, a developer creates an Analysis Services (AS) project, containing a set of Analysis Services objects (data sources, cubes, dimensions, mining models etc). The definitions of the project, and all the objects it contains, are stored as files in the file system, using an XML representation. In order to test and debug the definitions, the developer would frequently 'deploy' the project to a designated AS server, to create the objects on the server.

All the objects in a single project get deployed to a single AS database. The objects are stored in a set of files, with each data source, cube, dimension etc. being stored in a separate file. All the files for a project are stored within the file system directory in which the project was created.

To create a new empty Analysis Services Project:

  1. On the File menu, point to New, and then click Project.
  2. In the dialog displayed, select the template Analysis Services Project, and (as needed) change the location in which the project files will be created.

It is possible to import an AS database from a server to create a new project. This is particularly relevant following migration from SQL Server 2000 to SQL Server 2005, where a migrated database would commonly then be imported to a project for any further development.

To create an Analysis Services Project from an existing database:

  1. On the File menu, point to New, and then click Project.
  2. In the dialog displayed, select the template Import Analysis Services 9.0 Database, and (as needed) change the location in which the project files will be created.

Whilst a project is frequently deployed to a server for testing/debugging, the project remains the primary 'source' of the definitions. Hence subsequent changes would be made by opening the project, making the change, and again deploying.

To open an existing Analysis Services Project:

  1. On the File menu, point to Open, and then click on Project/Solution, or;
  2. On the File menu, point to Recent Projects, and select the project from the list of the most recently used projects.

There are also other project types covering the other elements of a BI application:

  • A Reporting Services Report project contains a set of report definitions (and associated data sources), that will be deployed to the same Reporting Services server\folder.
  • A DTS project contains a set of DTS package definitions (and associated data sources).

Solutions

Multiple projects can be grouped together into a 'solution'. A solution is nothing more than a container of related projects. For example, a solution "Sales Reporting" might contain

  • An AS project defining a cube of Sales data.
  • A reporting project containing some reports against that cube.
  • A DTS package that periodically processes the cube.

The solution acts to group these projects together for convenience, and it does not imply that they will be deployed to the same location.

When a new project is created, a new solution is also always created. By default this solution is visible in the Solution Explorer, though tool options allow it to be hidden if preferred. Hence there are always at least two files created for a new AS project:

  • A file with suffix .slnbi containing details of the solution as a whole;
  • A file with suffix .dwproj containing details of the AS project.

To add a Report project to an existing solution containing an Analysis Services Project:

  1. In the Solution Explorer, right-click the top most node (that represents the solution), point to Add and click on New Project;
  2. In the dialog displayed, select the template Report Project.

Elements of the Environment

This section describes the elements that make up the BI Development Studio.

Main Elements

The illustration below shows the BI Development Studio with an example AS project open, and highlights the main elements of the environment.

ms345127.ovasdmen01thumb(en-US,SQL.90).gif

Figure 1. Business Intelligence (BI) Development Studio

The main elements are as follows:

  1. Solution Explorer. The Solution Explorer is a window displaying the currently opened solution (in this example, containing a single AS project called "Adventure WorksAS"). The files containing the data sources, cubes, dimensions etc. are displayed, and opening a file will launch the appropriate designer allowing the definition of the relevant object to be edited .

    The following folders cover each of the allowed object types:

    • Data Sources.
    • Data Source Views.
    • Cubes (including the contained partitions).
    • Dimensions.
    • Mining Models (the structures, and contained mining models).
    • Assemblies. This contains any references to assemblies defining stored procedures.
    • Roles.
    • Miscellaneous. This contains any supplementary files e.g. Excel spreadsheets, or text files, that are relevant to the project (these files are not deployed to the AS Server).

    To create a new object in the project:

    1. In the Solution Explorer, right-click the appropriate folder, and click New <Object Type>. This will launch the relevant wizard allowing creation of the object, and (for the main objects) upon completion of the wizard the designer will be opened.
    2. or (in rarer case, to include an existing object):
    3. In the Solution Explorer, right-click the project node, point to Add, and click Existing Item. In the dialog displayed, select the relevant file. This will result in the selected file being copied into the project folder, and added to the project.
    4. Files may be deleted and copied in the solution explorer. Entire solutions and/or projects can be copied simply by copying the entire solution or project directory in the file system.
  2. Analysis Services Designers. The illustration shows two designers currently opened, for the "Adventure Works" cube and the "Product" dimension, with the "Adventure Works" cube being the currently 'active' designer. Each of the object types have their own specific designers (except for some exceptions of simple object types where there is a simple dialog). For the more complex object types, there are separate tabs within the designer, each tab covering a different part of the definition of the object. In this example of the Cube Designer, there are different tabs for defining the basic cube structure, the usage of dimensions, calculations etc, as well as a tab allowing browse of the cube data. In addition, each tab in the designer contains a toolbar supporting the most common operations relevant to that tab.

    Each designer provides two views. Almost invariably, the 'Designer' view is used, as shown in the example. Alternatively, for the more advanced and stout of heart, a file can be opened using the 'Code' view, that simply shows the raw XML content of the file.

  3. Properties Window. The Properties window shows the properties of the currently selected object. The properties can either be shown categorized, or alphabetically (this view option being controlled by the buttons at the top of the window). When a file in the solution explorer is selected, the properties displayed are those of the file itself, rather than the object within the file.

    There is a slightly subtle difference between the name of the file, and the name of the cube, dimension etc. stored within the file. However, if a file is renamed, the option is given to also rename the object, as this is normally what is desired.

  4. Main menu. The main menu contains menu items that are always present, as well as additional menu items that are added based upon the currently active designer. In the example above, the menu items "Cube" is only present whilst the Cube designer is active.

  5. Toolbar. The toolbar contains items for the most common operations. Again, additional toolbars are sometimes added based upon the currently active designer. Whilst building AS projects, the most relevant toolbar item by far is the SaveAll button.

Other Elements

The following illustrations include other elements of the environment that are less central, in that they often do not need to be present at all, but instead are displayed as needed.

ms345127.ovasdmen02thumb(en-US,SQL.90).gif

Figure 2. Non-essential environmental elements

  1. Deployment Progress Window. When a project is deployed to the target server, this window is displayed to indicate the progress of the deployment (which by default includes processing the objects). Details of any processing errors are written to this window.

    By default, this window is 'tabbed' with the properties window, meaning that upon deploy, it will be displayed in the same location as the properties window, but in front of it. Selecting the Properties tab brings the properties window to the fore again.

    ms345127.ovasdmen03thumb(en-US,SQL.90).gif

    Figure 3. Deployment Progress Window

  2. Error List Window. This window is displayed if any errors are detected during validation, or during deployment. It contains the error text, and (in the case of errors detected during validation) double clicking on the error will launch the relevant dialog. Generally, it is advisable that this window be closed once all errors have been rectified, to avoid wasted screen area.

    ms345127.ovasdmen04thumb(en-US,SQL.90).gif

    Figure 4. Error List Window

  3. Output Window. This window is displayed by default during the build (validation) phase of deployment, and contains textual progress information. Considering AS projects alone, it offers limited value given the Deployment Progress window, and it is advisable to switch off this auto display, to avoid wasted screen area.

    To switch off the auto-display of the output window:

    1. From the Tools menu, click Options.
    2. In the dialog displayed, click the Projects and Solutions node in the left pane.
    3. Uncheck the check box Show Output window when build starts.

    Not shown on the illustrations are the following additional windows:

  4. Toolbox. This window contains 'toolbox items' e.g. 'List' and 'Chart' items that can be dragged onto a report. It offers no value considering AS projects alone.

  5. Task List. The task list simply contains any user added tasks (e.g. "To do" items) that are associated with the project. It is only ever displayed if explicitly opened.

Tools Options

The environment offers a large number of options controlling details of its behavior (we already met one above, related to the display of the output window).

ms345127.ovasdmen05thumb(en-US,SQL.90).gif

Figure 5. Tools options window

To display the options:

  • From the Tools menu, click Options.

    The following are the small subset that are the most relevant when working with AS projects.

  • Environment\Fonts & Colors. This allows control of the fonts used, including (by selecting "Business Intelligence Designers" from the dropdown), those used within the AS designers.

    The fonts used for the majority of the environment (e.g. the solution explorer, and main menus), is actually obtained from the standard system fonts.

    To change the relevant system font:

    1. Go to Control Panel, and select Display.
    2. Click the Advanced button on the Appearance tab.
    3. Select icon from the Item drop-down list.
    4. Define the required font to be used.
  • Business Intelligence Designers\ Analysis Services Designers. This allows control of various settings of the designers, including the query timeout and connection timeout to use on any connection to the AS server, and the default target server to use for each new project created.

  • Projects and Solutions\ General. This allows control of various settings of the projects, most importantly whether the output window should be displayed automatically (as discussed above) and whether solutions are displayed in the (common case) where the solution only contains a single project.   

Similar to a tool option is the option controlling whether text will wrap or not within the designers that allow authoring of MDX. The relevant menu item is Edit\Advanced\Word Wrap.

Customizing the Environment

Whilst in many regards customizing the environment is an advanced topic, such customizations do allow simplifications and preferences to be applied to what otherwise can be a complex and daunting environment.

The various customizations that are possible are as follows:

  • Window position and state. Windows such as the Solution Explorer, Properties Window, and Error List Window are known as 'tool windows'. They can be displayed in any of the following states:
    • Dockable: This means that the window is 'docked' (aligned) with other tool windows (e.g. the Solution Explorer and Properties window are both docked by default). They can be repositioned by drag/drop, to position relative to other tool windows and the edge of the environment. They can also be 'tabbed' with other tool windows.
    • Floating. Less commonly, a tool window can be made 'floating', such that it appears as a free-floating window within the environment, positioned as required.
    • AutoHide. In this state, the window is displayed only as a small tab at the edge of the environment. Mousing over this tab, however, will result in the window being expanded out, and then upon clicking outside the window, it will again collapse back to the tab. By default, the toolbox is displayed in this state.
    • Hidden. The tool window can simply be closed. Once hidden, it can be redisplayed using the View menu (e.g. on the View menu, Other Windows\Task List will show the task list).
    • Tabbed Document. A window can also appear as a tabbed document, akin to the designers.

The Progress Reporting window is slightly special in that its visibility is controlled by a menu item on the project context menu (as there could be one window per project, if multiple AS projects were included in the solution).   

To change the state of a window:

  • Click the down arrow in the window heading, and select the required state.

    The 'pin' icon is simply a shortcut for taking a window from AutoHide to Dockable, and the 'cross' icon is obviously a shortcut for 'Hide'.   

To revert back to the initial window layout:

  1. From the Window menu, click Reset Window Layout.   
  2. Toolbars. The set of toolbars visible at any time can be customized. In addition, the buttons appearing on each toolbar can be totally customized, and new toolbars can be created. One advantage of removing infrequently used toolbar items is to reduce space, particularly when toolbars might otherwise wrap to multiple rows. The toolbar customization is handled the same as in other applications like Microsoft Word.

To customize a toolbar:

  • Click on the right hand border, and on the menu displayed, either select or unselect the items listed, or choose Customize to launch a dialog allowing complete customization.

To move a toolbar:

  • Click on the left hand border, and drag/drop.    
  • Menus. The set of menus visible can also be customized, again the same as in other applications like Microsoft Word.

To customize a menu:

  • Right click on the menu region, and choose Customize from the context menu
  • Select the Commands tab
  • Drag commands to the appropriate menu and/or remove/rearrange existing menu items.

All customizations are of course saved across sessions. In addition, it is possible to explicitly export and import settings (including tool option settings and window position etc).

To export\import settings:

  • From the Tools menu, click Import\Export Settings.
  • The dialog displayed allows selected settings to be exported to or imported from a specified file.

Deployment

As already discussed, editing and saving object definitions simply saves to files within the project. In order to actually test against a live, running cube or mining model, it is first necessary to deploy the project to a database on a designated target server. The name of the database, and the target server, is set as a property of the project. By default:

  • The database name is the same as the project name (though if the project is renamed, it does not automatically result in the target database being changed).
  • The target server is the default target server specified as a tool option, as described above (or "localhost" if no specific server is defined as a tool option).

To set the target database and server for a project:

  1. In the Solution Explorer, right-click the project node, and click Properties
  2. In the dialog displayed, click the Deployment node in the left pane.
  3. Set the target server and database. If the target server is a named instance, it is entered as <server name>\<instance name>

To deploy a project:

  1. In the Solution Explorer, right-click the project node, and click Deploy, or
  2. On the Build menu, click Deploy <project name>.

To cancel deployment:

  • On the Build menu, click Cancel.

There are various parts of the AS designers that require the objects to have been deployed. Most notably, the cube and dimension browsers simply display a grey background if the necessary object is not yet deployed and processed. Other parts of tools can be operative, but less functional (e.g. whilst authoring calculations, the metadata browser will display the metadata only for a deployed, processed cube).

The unit of deployment is the entire project. Hence the act of deployment is always to take the current definition in the project, and apply it to the target server. It is not possible to elect to deploy part of a project. However the tools will (by default) perform incremental deploy. Hence upon deploying a project, then changing a single object in the project and redeploying, only the single changed object will be resent to the server. This is purely an optimization, and the net result is the same as if the entire project were deployed (this optimization can be switched off using the DeploymentMode project property).

By default, the objects will be processed during deployment. The processing option used will be "ProcessDefault", which is a server processing option that results in the object being processed only if it is not already in a processed state. A project property allows control over the level of processing to be carried out during deployment.

The net effect of the Default processing option is that upon making a non-structural change to a cube (e.g. changing its description), then deploying will not require reprocessing of the data. However, making a structural change (such as adding a dimension) will require reprocessing, possibly taking considerable time.

Note   Any changes to the data source or data source view are not considered structural changes (nor of course are changes to the source data itself). Hence upon such changes, it would be necessary to fully process the cube following deployment to ensure such changes were reflected.

The entire philosophy around deployment is that developers are isolated, and 'own' the target database to which they are deploying. Hence it would be unworkable for two different developers to be independently working on different copies of the same project, yet be deploying to the same database. The preferred approach to such team development is discussed later.

The Deploy process actually consists of two steps:

  • Build the project. In this step, the contents of the project are validated, and the 'output' files are created.
  • Deploy the output from the project. In this step, the output definitions are sent to the target, and optionally processing is carried out.

Any errors will be output to the Error List window. Whilst validation occurs during build, certain kinds of errors (particularly data errors encountered during processing) will only be detected during deploy.

The output of build consists of the full database definition as defined by the project, along with some supporting files. This set of files is akin to the exe resulting from the build of a C# project. By default, the output is created in the "\bin" directory within the project directory, but this can be controlled via a project property.

The Build step can also be invoked independently.

To build a project:

  • In the Solution Explorer, right-click the project node, and click Build.

    Rebuild is the same as Build, except that it first cleans out the output directory, and deletes the details from which incremental deployment is derived, such that the next deployment will be certain to deploy all objects in the project.

Related to Build and Deploy is the notion of 'Debug'. Invoking Debug will first result in each project in the solution being built and deployed. The behavior then depends upon the currently active context. For example, if the user was editing calculations in the cube editor, then the MDX Script debugger will be invoked. If they were in a dimension editor, then the dimension browser will be invoked.

To start debug:

  • On the Debug menu, click Start (or simply hit F5)

Configurations

The BI Development Studio provides support for different 'configurations'. This is a somewhat advanced feature, primarily targeted at the following scenarios:

  • Two different developers are working on the same project, but each has their own environment i.e. certainly each developer uses a different target database, and possibly even use different relational data sources so they do not interfere with one another during development and testing.
  • A single developer is working on the project, but wants to work against different data sources at different times (e.g. a data source with very small data volumes for most development, but then switching to a data source with large data volumes when evaluating performance).

Within the tools, any property that is specific to a particular physical environment is deemed 'configurable'. Examples are data source connection strings, log file locations, data file locations, as well as all the project properties (including the target server & database). It is then possible to add multiple named 'configurations', where each configuration can have different values for such configurable properties. For example, the "Development" configuration might use a source database called "AdventureWorks Small", whilst the "Performance" configuration uses "AdventureWorks Full". The values that are displayed and used are those that are set for the currently active configuration. By default, a single configuration called "Development" is created (and for most cases no further configurations would be needed).

To create a new Configuration:

  1. On the Build menu, click Configuration Manager.
  2. Click <New...> in the Active Solution Configuration dropdown.
  3. Enter the name of the new configuration. This creates the new configuration, and makes it the currently active one.

To change the active configuration:

  1. On the Build menu, click Configuration Manager.

  2. Select the required configuration from the Active Solution Configuration dropdown.

    Also, the drop-down of configurations is displayed in the default standard toolbar.

    Nothing special need then be done to set a configurable property. The regular designers and property window will display the value associated with the currently active configuration, and therefore changing the configuration might result in different values coming into effect. Note that these values are stored separate from the files, hence the property shown in the XML view would not necessarily reflect the actual property value in effect for the configuration.

Team Development and Reuse

The recommended approach to team development, meaning multiple developers working on a single project, is that as far as possible developers work on individual files, likely coordinating changes via a Source Code Control System. For example, the first developer checks out the "Product.dim" and "Warehouse.dim" files and makes changes to those dimensions, whilst a second developer checks out the "Employee.dim" file. Whilst they are developing and testing, they are isolated (and as stressed before, would be deploying to different databases). Later, their changes would be combined.

In those cases where parallel work on the same file is unavoidable, it would be necessary to manually combine the changes (possibly using standard tools that diff/merge text files).

Online Projects

The discussion above has focused on the project mode style of development, where all changes are saved to files in a project, and later deployed to a server.

There is an additional mode of working provided, where instead the user connects directly to a server, and edits the objects directly on the server (all changes being immediately applied to the server). There is of course the need to still process objects as necessary.

This mode of working is known as "online projects", and is similar in concept to the style of SQL Server 2000 Analysis Manager. The user first connects to an existing database, or creates a new one, and then the objects within that database are displayed in the solution explorer, as for regular projects. Designers can then be launched from the nodes in the solution explorer, as described above. The behavior of the actual designers is almost identical in the two different modes. The difference is that upon saving changes, the server is updated immediately.

To create a new Analysis Services database using an online project:

  1. On the File menu, click Connect to Analysis Services Database.

  2. In the dialog displayed, select the option to create a new database, and enter the database and server name.

    There is still a solution file created as before, and it is possible to group multiple online (or even online and regular projects) into a solution.

    Note   It is largely a 'style' difference involved in the choice between working with either online projects and regular projects. Certainly it would be unwise to mix both styles of development for the same project, given the confusion this would create as to which was the master copy.

SQL Server Management Studio

This environment focuses on management activities. It covers the management of all types of servers in the SQL Server family, including Analysis Services. It would be used both by users focused solely on the management of deployed AS databases (backup, processing etc), as well as by developers who need to perform management orientated tasks.   

Elements of the Environment

The illustration below shows the SQL Server Management Studio, connected to an example AS server, and highlights the main elements of the environment.

ms345127.ovasdmen06thumb(en-US,SQL.90).gif

Figure 6. SQL Server Management Studio

Main Elements

The main elements are as follows:

  1. Object Explorer. The object explorer is a window displaying the contents of the currently connected servers (in this example, there is a single AS server, containing two databases "Inventory" and "Adventure Works DW").

  2. Query Editors. The query editors allow queries to be executed against Analysis Services. These cover MDX (i.e. queries against OLAP cubes), DMX (i.e. queries against data mining models), and XMLA (covering all command types that can be sent to Analysis Services). The MDX and DMX editors include a pane displaying the metadata for the currently selected database.

  3. Management dialogs and wizard. There are a set of management dialogs and wizards that can be launched from objects in the Object Explorer, in order to perform the various management tasks (e.g. Process, Backup, Create partitions, Design Aggregations). This includes Property Dialogs allowing the 'management orientated' properties to be set. For example, for a dimension it is possible to control physical aspects like the proactive caching settings, but not logical, design orientated properties like whether an "Unknown" member will be present. The dialogs and wizards are modeless. The illustration shows a single open management dialog for Backup.

    To connect to an Analysis Services server:

    1. Select the Object Explorer, then on the File menu, click Connect Object Explorer to display the Connect to Server dialog (this dialog is also displayed upon launching the SQL Server Management Studio).
    2. In the dialog, select Analysis Server as the server type, then enter the name of the instance.

    To create a new MDX query:

    1. On the File menu, point to New, and then click File.
    2. In the dialog displayed, select the MDX Query template from the Analysis Services Queries folder.
    3. In the Connect to Analysis Server dialog, enter the name of the instance to connect the editor to the target server. The target database can either be entered by displaying the Options then Connection Properties on this dialog, or from the dropdown of databases displayed in the toolbar after connecting to the server

Other Elements

The following illustrations include other elements of the environment that are less central:

ms345127.ovasdmen07thumb(en-US,SQL.90).gif

Figure 7. Non-essential environmental elements

  1. Registered Servers. The registered servers window allows commonly used servers to be registered. Double clicking on a registered server results in the Object Explorer being connected to that server.

  2. Solution Explorer. Users of the SQL Management are frequently working with files e.g. files containing saved MDX queries or XMLA scripts. Like the BI Development Studio, the SQL Server Management Studio also has the notion of projects, displayed in the solution explorer. These 'script projects' are a very different kind of project to those in the BI Development Studio, and act as containers of related files and connections.

  3. Properties Window. The properties window displays properties of the currently selected query window or file (note that separate Property dialogs cover the management properties for objects displayed in the Object Explorer).

  4. Summary Page. The Summary page displays information about the selected object in the Object Explorer. For Beta 2, the Summary Page displays very limited information.

    ms345127.ovasdmen08thumb(en-US,SQL.90).gif

    Figure 8. Summary page

  5. Template Explorer. The template window displays templates for common MDX, DMX, and XMLA queries. Selecting a template opens a query editor containing the template query.

The visibility of the various tool windows can be controlled via the View main menu, and the environment can be customized in a very similar manner to the BI Development Studio.

Management Dialogs

A set of management dialogs and wizards are provided that support common management tasks (e.g. backup, process, aggregation design). These dialogs and wizards are launched from the relevant object in the Object Explorer. For each of the dialogs, it is possible to either invoke the action immediately, or script the action to create an XMLA script, that can be saved for later execution.

Each of the AS objects also has a Property dialog, allowing management orientated properties to be set. These dialogs do not allow properties to be changed that are considered to be 'design' properties. Hence quite a clear distinction is made between management properties, that would commonly be changed in the SQL Server Management Studio, and other properties that cannot, and that must therefore be    changed within the BI Development Studio. This distinction is also made in the Deployment Wizard, described below. Note that the distinction is only reflected in the level of tool support that is provided, hence any property could be changed by, say, issuing the necessary XMLA command.

Scripting

Each Analysis Services object can be scripted, to obtain an XMLA script containing the appropriate CREATE, ALTER, or DELETE command for the object.

To script an entire AS database:

  1. In the Object Explorer, right click the node for the relevant database.
  2. Point to Script Database as, then CREATE To, and click New Query Editor Window.

Profiler

This environment allows server events to be captured. It covers all SQL Server server types including Analysis Services. In addition to capturing events, the events can be replayed.

The illustration below shows a simple trace being captured for an Analysis Services server.

ms345127.ovasdmen09thumb(en-US,SQL.90).gif

Figure 9. Analysis Services server simple trace

To start a trace for an Analysis Services server:

  1. On the File menu, click New Trace.

  2. In the dialog, select Analysis Server as the server type, then enter the name of the instance.

  3. Confirm that the trace definition file should be retrieved (this applies only the first time after install).

  4. Click OK to accept the default trace properties, including the default event types to be captured.

    All activity against the server will then be displayed in the trace.

Utilities

This section briefly introduces two of the utilities provided in the SQL Server 2005 toolset (both are available on the Analysis Services menu of the Microsoft SQL Server menu).

Migration Wizard

The Migration Wizard will migrate an existing Analysis Services 2000 database, to a designated SQL Server 2005 server. A new database will be created, and the old Analysis Services 2000 database will be left unchanged. The migration covers metadata only, and following migration the new database should be processed.

Deployment Wizard

The Deployment Wizard is used to deploy the output from the BI Development Studio Analysis Services project to test and production environments.

The output from building an Analysis Services project is a set of files in the build directory. Primarily this consists of an .asdatabase file, that contains the full XML definition of the database. Additional files contain:

  • The values of the configuration settings (e.g. connection strings) that commonly are changed during deployment;
  • The target server and database to which the project should be deployed, and;
  • The various options controlling the behavior of deployment (e.g. whether cubes should be processed following deployment).

The deployment utility uses these files as input, and can either carry out the deployment, produce the appropriate XMLA script for later execution, or simply update the input files to allow later 'silent' deployment.

The utility supports the relatively common cases that upon deployment of a new version from the development environment, it is also necessary to consider changes made to the existing deployed version e.g. the addition of users to security roles, or the creation of new partitions. The utility helps preserve such changes, whilst applying the changes from the development environment. Note that this reflects the distinction made between 'management' details versus 'development' details, and would not cater for arbitrary changes to the definition of a deployed object, where performing any form of merge at the time of deploying later versions would be inappropriate. If such changes have been made to a deployed object, they should be fed back into the project.

The term 'developer' is used throughout the rest of this paper, but is intended to cover anyone building cubes, hence certainly includes DBAs and analysts, as well as true developers.

This is development time 'deployment', not deploying the definitions to test and production servers (as would occur later in the development lifecycle). Hence the designated server might frequently be a server installed on the same development machine as the BI Development Studio.

In fact, each project is deployed independently.

There is also a designer that can be launched from the project node itself, allowing the details of the database as a whole to be edited.