MS SSAS 2008 Unleashed - Chapter 9. Multidim - Models and BI Development Studio

Chapter 9. Multidimensional Models and Business Intelligence Development Studio

Analysis Services includes SQL Server Business Intelligence Development Studio (BI Dev Studio), which provides a user interface for developing multidimensional models. We are going to use it to work through an example from our sample database FoodMart 2008 to show you the structure of the model and the objects it contains. Along with the walkthrough, we show you how to use the BI Dev Studio wizards to create new objects for your model.

You can find the FoodMart 2008 sample database on the Internet at http://www.samspublishing.com and http://www.e-tservice.com/downloads.html. Those of you who are familiar with Analysis Services 2000 know about the FoodMart 2000 sample database. FoodMart 2008 is to some degree based on the earlier database, but it has been changed to take advantage of the flexibility and power of Analysis Services 2008.

Here’s what we will do with BI Dev Studio and FoodMart 2008 in this chapter:

• Create a data source

• Create a data source view

• Create a dimension

• Modify your dimension

• Create your cube

• Modify your cube

• Build cube perspectives

• Define translations for your cube

• Deploy your cube

To start your hands-on exploration of building a multidimensional model with BI Dev Studio, you will open a sample project. Download the new FoodMart 2008 sample database at http://www.e-tservice.com/downloads.html and restore it into one of the folders on your computer. Then, navigate to that folder.

Creating a Data Source

The first task of creating your multidimensional model is to create a data source. The data source is an object that defines the way that Analysis Services connects to the relational database or other sources.

Creating a New Data Source

You will use the Data Source Wizard to create your data source object. First, you will choose a data connection. If you are following along with the FoodMart 2008 sample project, you can use the data connection that appears by default. Or you can define a new data connection.

To define a new connection instead of using an existing one, click New on the Select How to Define the Connection page of the wizard. Doing so lands you in the Connection Manager, where you will define that new connection. (There really isn’t much defining going on if you are using an existing connection.)

When you are done with the Connection Manager, you will find yourself back in the Data Source Wizard, on the same page you were on before you were whisked away to the Connection Manager. Now you are ready to join the ranks of the people who selected an existing data connection.

Defining the Credential Analysis Services Will Use to Connect

Now that we are all back together, we are going to move on to the Impersonation Information page. Here’s where you are going to specify the credentials that Analysis Services will use to connect to the relational database (in our sample, FoodMart 2008 SQL Server database). For detailed information about specifying these credentials, see Chapter 18, “DSVs and Object Bindings.”

On the Completing the Wizard page, you give your data connection a name. And you are done—for now.

Modifying an Existing Data Source

There will likely come a time when you need to modify the data source that you created. Analysis Services provides a tool for that: the Data Source Designer. You use the Data Source Designer to change the properties of your data source object.

You open the Data Source Designer from BI Dev Studio. Right-click FoodMart 2008.ds, and then click View Designer from the menu that appears.

The most common editing task is to change the connection string. The connection string specifies the server that contains the relational database that Analysis Services connects to. It also defines the client object model that the Analysis Services uses for that connection (for example, SQL Native Client, ADO.NET).

To modify the connection string, click the Edit button that is just below it. Doing so takes you to the Connection Manager, where you can make your modifications.

You will find more information about using the Data Source Designer to modify other properties of your data source, including the properties that appear on the Impersonation Information tab, in Chapter 35, “Security Model for Analysis Services.”

Modifying a DDL File

So far, we have been working in the BI Dev Studio user interface. For every object in your project, BI Dev Studio creates a file (sometimes more than one) containing the Data Definition Language (DDL) definition of the object. In the process of deployment, BI Dev Studio validates all the objects, compiles one command to create or modify all the objects your project needs, and then sends the command to the server.

You can use Notepad to view all the DDL files in your project and change them yourself. However, BI Dev Studio provides a user interface you can use to edit a DDL definition of an object in an XML editor.

In Figure 9.2, you see the DDL definition of the FoodMart 2008 data source, FoodMart 2008.ds.

You can modify the DDL definition for an object directly in the XML editor. We have to admit, developers are more comfortable with this style of work than most people. And, we hope they are happy to find that they can directly change the DDL definition (the source code) for an object.

The XML editor is the same editor you find in SQL Server Management Studio. As in most XML editors, the XML tags and elements are color-coded. If you mistype or misplace a tag or parts of it, the color automatically changes.

The work you do in the XML editor is mirrored in the BI Dev Studio user interface. So, if you use the XML editor to modify the DDL definition of an object, save it, and later open your object (let’s say it is a dimension object) in the Dimension Designer, you will see the modifications you made earlier in the XML editor.

Often, an easier approach is to use the XML editor rather than the BI Dev Studio user interface. For example, you cannot change the ID of a dimension in the dimension editor. It is possible that the dimension could be used in more than one cube. In that case, changing the ID of the dimension requires going through all the cubes in the database to determine which cubes the dimension is used in, and then changing the ID of the dimension in all those. But, the DDL definition of the entire database, including all the objects, is a much better vehicle for this sort of change. You can simply do a search and replace and change the dimension ID wherever it occurs.

In our sample FoodMart 2008 data source, shown in Figure 9.2, you can easily modify the name of the server that your data source points to. All you do is change the content of the element.

Any time you are having a hard time finding a property of some object among all the other properties, you can switch to the DDL definition of the object. Then, you can easily search for the property and modify it directly in the DDL definition.

Throughout this book, we give you lots of examples of DDL definitions. Now with the View Code option, you can look directly at the DDL definition of one of your own objects in the XML editor, and you can make the changes you want.