Analysis Services OLAP Tools Overview
Analysis Services tools in 2005 introduce a whole new way to work with the data and the OLAP and Data Mining objects. In Analysis Services 2000, the very first step a user had to take to design or manage a server was to physically connect to an Analysis Services server. In 2005, you have the choice of two tools to use, depending on the task to be accomplished: BI Development Studio or SQL Server Management Studio. Also, with BI development Studio, you have two choices: work live against a physical server (also called immediate mode) or work in project mode. With SQL Server Management Studio you always work against a live server. Both modes are presented and discussed in the next section.
BI Development Studio
The BI Development Studio is the environment to use for the following type of tasks:
Figure 13-1. Cube designer hosted in the BI Development Studio.
The BI Development Studio is a fundamental revolution for BI development activity. The first major change is that the first operation is to choose either to connect live to a database or work with a project.
A project is a collection of XML files that are saved a disk, either local or remote. Additionally, these files can also be saved in a source control system. When a project is loaded or created, graphical designers read from each XML file, parse the information, and display the same data graphically (see Figure 13-1).
To create a new project, you start as if creating any other project in Visual Studio. The main difference is that new project types are made available to you (see Figure 13-2).
Figure 13-2. New Project dialog.
Thus the database professional can elect to create a solution using any of the available BI project types:
The other two projects are just shortcuts for specific tasks:
Additionally, after the solution has been initially created with one project, other projects can be added later to this solution, whether they are BI projects or any other projects available with Visual Studio (ASP.NET, VB.Net, C+, and so on).
The counterpart to Project mode is Immediate mode or Live mode. In this mode, using the File/Open menu, you can select a physical server and database to connect to, as shown in Figure 13-3. Thus any change made through the BI development studio happens immediately and directly in the live production environment.
Figure 13-3. Connect To Database dialog.
So one of the first questions you will ask yourself is "Which mode should I use?" This is indeed one of the most important decisions you will make when starting a new project. Project mode is recommended when working with a professional development team made up of several developers or designers. Immediate mode is usually fine when a single person designs and manages the OLAP database and Mining model. Figure 13-4 illustrates the interaction between BI development Studio and the file or live database, as well as interaction between SQL Server Management Studio and the live server.
Figure 13-4. SQL Server development and management tools.
Avoid as much as possible mixing the two modes. Working against the same database using both modes can be very dangerous because one of your teams or developers may be changing the structure of the Cube in Project mode while another may be changing the same object in Immediate mode. The changes made in Immediate mode will be overwritten next time the project is deployed.
Inside a BI development Studio Analysis Services Project
After you have created an Analysis Services project or are connected live to an Analysis Services database, you find yourself presented with various designers.
Unlike Analysis Manager in SQL 2000, every designer here has been built natively as modeless. That means that you can open as many as you want at the same time.
Let me briefly describe the various designers available in such a project because I will be referencing them further in this chapter:
The Data Source Editor dialog enables the database professional to set up a connection to various data sources. In Analysis Services 2000, ODBC as well as OLE-DB providers could be used. In Analysis Services 2005, OLE-DB as well as native connectors can be used. In addition, the Data Source editor also provides the environment to customize connection properties such as time-out and impersonation credentials.
The Data Source View Designer is a designer of a new object: the Data Source View (DSV). The DSV lets you model your relational data. One may ask, "Why can't I model it directly in my relational database?" Well, for starters, your source may not be relational. Also, if you are trying to build a reporting and analytical application directly against an OLTP or production database, the database may be read-only to the database professional, thus making it impossible to create additional relationships or views for the purpose of this BI application. Finally, you may need to access multiple heterogeneous data sources. The Data Source View, beyond allowing you to reduce the scope and number of tables you work with, also enables you to access tables coming from multiple data sources (i.e., Oracle and SQL Server, or Teradata and DB2) in a single relational modeling environment and create logical relationships between them or views on top of them.
The DSV Designer is a great environment for doing such modeling. Adding relationships, creating named queries (Views), and creating calculated columns are all common tasks that can be accomplished in this designer.
When the schema contains a large number of objects (tables, views, and so on), do not hesitate to create multiple Diagram views for portions of the schema. This will make it easier to read and navigate relationships.
Use the CTRL + Mouse wheel up and down to rapidly zoom in and out in the diagram. This technique works in all other designer diagram views as well.
If the schema contains a lot of tables that cannot be seen on the screen all at once, use the Diagram Navigator, which you can access by clicking on the double arrow in the bottom right of the diagram (see Figure 13-5) to get an overview (see Figure 13-6) and navigate through the diagram.
Figure 13-5. Diagram Navigator Anchor.
Figure 13-6. Diagram Navigator Overview.
You can access the Database Designer by right-clicking on the Project name (in this case, AdventureWorksDW) in Solution Explorer. Because the database is the container of the cube, Dimension Object, it doesn't really find its place in the Solution Explorer. This is why we found that the most logical place for the menu to invoke it was a context menu on the project name itself. Experience and time have shown that although it is very logical, it makes it hard to discover for the novice user. The Cube Designer has been much enhanced since Analysis Manager 2000. Because a cube is now a much more powerful and complete object, the Cube Designer now is made up of nine tabs. Because it has so many tabs, we thought originally of using the Microsoft Outlook Metaphor and displaying the tab along the left vertical side of the designer. Usability studies showed that it was consuming too much screen real estate, which as you have probably noticed already, is one of the most important things to preserve in the VS shell. Each tab enables the database professional to update the components of the cube definition, which are usually referred to as second-class objects. (First-class objects are things such as cubes, dimensions, and Mining Models. Second-class objects are things such as Measures, Actions, KPIs, Attributes, and hierarchies.) Because it is sometimes necessary to display the same information in two different tabs, the Cube Designer ensures that when some information is updated through one tab, the rest of the designer is refreshed with the same updates as well. This way, the Cube Designer ensures that a consistent and coherent view of the second-class object is displayed at all time.
The Dimension Designer, like the Cube Designer, has been enhanced as well. It now includes three tabs: Dimension Structure, Translation, and Browser. You will notice that the pane organization inside the Dimension Structure tab is reorganized if you select between the Tree, List, and Grid views (see Figure 13-7). This is again to optimize screen real estate usage to a specific view. It is recommended that you play around with the view, and then find the one that best suits your need. For example, I know I like the Tree view to set the Attribute member properties relationship (which is important for aggregation design), but I like the Grid view to review attribute key properties all at once.
Figure 13-7. Dimension Structure tab view selection drop-down.
To view and modify multiple dimensions at the same time that you're editing a cube, we recommend that you split the screen by creating Vertical Tab groups. Right-click on the Cube tab, for example (see Figure 13-8). That way you can see Dimensions and Cube Designers side by side.
Figure 13-8. Create New Vertical Tab Group.
The Data Mining Model Designer is probably one of the richest of all the designers. It hosts over 25 data browsers. Rich visualization is critical to being able to analyze the findings of the mining model algorithm. Although the designer only has five main tabs (Mining Structure, Mining Models, Mining Model Viewer, Mining Accuracy Chart, Mining Model Prediction), you will rapidly notice that the Mining Model Viewer tab contains its own set of sub-tabs. The list of sub-tabs is dependent on the mining model algorithm selected.
Each of the Data viewers available in the mining Model Designer is a component that can be reused as-is in an application.
The Role Designer is a unique designer because it is the only Designer that was designed from day one to be hosted by both the BI Development Studio and the SQL Management Studio. Because setting security is an activity that may be done during the development phase or deployment phase, it is indeed needed in both environments. Thus you will notice that the Role Designer in BI development Studio and the Role Dialog in SQL Management Studio have the same structure. They actually share the same framework and code. Only the hosting frame is different.
Unlike in Analysis Manager 2000, the Testing Role is not accessed from the Role Designer. This functionality has been moved to the Cube and Dimension Designers' browser tabs. An icon is available in the toolbar that lets the database professional impersonate a different user or set of roles. The browser can then display the data with the security context of the user or role(s) (see Figure 13-9).
Figure 13-9. Testing roles and users using the Security Context dialog.
Finally, many wizards are available throughout the BI Development Studio. There are wizards like those in SQL 2000 for creating each first-class object: Cube, Dimension, Mining Model. Some new wizards are also introduced in this release, such as the Deployment Wizard or the BI Wizard. The Deployment Wizard assists the database professional to deploy a specific database to another server. The BI Wizard was designed to assist with enhancing the cube or dimension design with more complex tasks such as adding time intelligence, semi-additive aggregation, or currency conversion. The list of available tasks is different, depending upon the object for which the BI wizard was invoked. More and different tasks are available if the wizard is invoked from a cube rather than a dimension object. Also, this wizard was designed with a specific framework that will enable us to easily enhance the list of available tasks in a future release. We didn't have the time to make this framework fully extensible by external parties in the SQL 2005 release. We hope it will be made possible in the future. This wizard doesn't build anything that cannot be done by hand through the designers directly. The goal of this wizard is to help you complete more complex business tasks in less time, thus improving productivity.
SQL Management Studio
The SQL Management Studio is the environment to use for the following types of tasks:
Unlike BI development Studio, in SQL Management Studio your first task will always be to select and connect to a specific server, whether local or remote (see Figure 13-10). After you are connected to this server, then all management tasks related to this server become enabled. The server content is displayed in the object browser that comes up on the left side of the window. In some ways connecting to an Analysis Services server with SQL Server Management Studio is equivalent to connecting to an Analysis Services server with Analysis Manager in SQL Server 2000, less all the development tasks.
Figure 13-10. Managing Analysis Services using SQL Management Studio.
In SQL 2005, the database professional can actually customize some connection properties when connecting to a server with the Advanced option button on the bottom right of the connection string. Clicking the Advanced button yields the properties page shown in Figure 13-11.
Figure 13-11. Advanced Connection Properties can be set when connecting to Analysis Services with SQL Server Management Studio.
The SQL Management Studio interface is mainly divided into two areas: Object Explorer on the left side and query editors or a summary pane on the right side. In addition, you can achieve most management tasks by invoking a dialog or a wizard to complete these tasks.
Through the Object Explorer, you can access every Analysis Services Database object and trigger task for individual objects. The available task list is different for each Analysis Services object. So, for example, migrating a database or editing Server Properties are available only from the server node. The Running Usage-Based Optimization Wizard is available only from a specific partition or set of partitions.
One of the best features of the Object Explorer is the capability to script every single object. Scripting an object creates the portion of the XML/A command, which is now the native protocol for Analysis Services 2005 server, that can either be scheduled or run directly from within this environment or copied into an Integration Services package or application code.
As explained in previous chapters, the Summary pane displays statistical and detailed information about the object selected in the Object Explorer.
Although the object browser itself doesn't allow multi-selection of nodes, it is possible to select multiple objects by using the Summary page on the right pane. For example, if the Cubes node is selected in the Solution Explorer, all cubes can be selected together in the summary pane and the Process command can be invoked on all of them at once.
The MDX sample utility that was so useful with Analysis Services 2000 has been integrated and enhanced in the SQL Management Studio. Now you can directly send MDX, DMX, or XMLA commands, using MDX, DMX, or XML/A query editors. As they do for SQL, these editors support enhanced color coding, tooltips, and region collapsing. As many of these editors as needed can be opened at the same time.
To complete tasks, a set of dialogs and wizards are also available in this environment. The dialogs have been designed to run independently from the SQL Management Studio shell. That means they continue to run even if the shell is closed. That also means they each run as separate executables. This is especially useful for the database professional who can now, for example, run a usage-based optimization wizard while at the same time running a backup on a different database (see Figure 13-12) These dialogs are very powerful because they are themselves entirely scriptable as well as schedulable.
Figure 13-12. Backup Database dialog.
So pretty much everything that can be done graphically through the SQL Management Studio environment can be scripted and scheduled at a specific time. This really brings the level of manageability of Analysis Services objects on par with the SQL relational database objects.