Expert Cube Development with Microsoft SQL Server 2008 Analysis Services: Design and implement fast, scalable and maintainable cubes with Microsoft SQL Server 2008 Analysis Services with this book and eBook

Before we start developing with Analysis Services, we need a clear idea of which edition of Analysis Services we're going to be developing for. There are two choices: Standard Edition, which is cheaper but missing some features, and Enterprise Edition, which is more expensive but feature complete. Licensing cost is likely to be the major factor in this decision. If money is no object, then you should use Enterprise Edition. If it is an issue, then you'll just have to live with the limitations of Standard Edition. Of course, if we install Analysis Services on a server that already has SQL Server installed then there are no extra license costs involved, but as we'll see in Chapter 11, we have to be careful they don't compete for resources. This document on the Microsoft website gives a detailed breakdown of which features are available in each edition: http://tinyurl.com/sqlstdvsent.

Don't worry about having to use the Standard Edition though. Some...

The first step towards creating a new cube is to create a new Analysis Services project in BIDS. Immediately after doing this, we strongly recommend putting your new project into source control. It's easy to forget to do this, or not bother, because building a cube doesn't seem like a traditional development project, but you'll be glad that you did it when you receive your first request to rollback a change to a complex MDX calculation.

As you're probably aware, there are two ways of working with Analysis Services projects in BIDS:

You'll only be able to use source control software effectively if you work in the project mode. Therefore, it's a good idea...

Once we've created a new project and configured it appropriately, the next step is to create a data source object. Even though you can create multiple data sources in a project, you probably shouldn't. If you've read the previous chapter, then you'll know that we recommend that all of the data needed for your cube should already be present in a single data mart.

You are then faced with the choice of which OLE DB provider to use, since there are often several different options for any given relational database. For SQL Server data sources, you have the option of using the SQLClient .NET data provider, the Microsoft OLE DB provider for SQL Server and the SQL Server Native Client (often referred to as SNAC). You should always choose the SQL Server Native Client since it offers the best performance. For Oracle data sources, the choice is more complicated since, even though Oracle is a supported data source for Analysis Services, there is a long list of bugs and issues....

In an ideal world, if you've followed all of our recommendations so far, then you should need to do very little work in your project's Data Source View—nothing more than selecting the views representing the dimension and fact tables and setting up any joins between the tables that weren't detected automatically. Of course, in the real world, you have to compromise your design sometimes and that's where a lot of the functionality available in Data Source Views comes in useful.

When you first create a new Data Source View (DSV), the easiest thing to do is to go through all of the steps of the wizard, but not to select any tables yet. You can then set some useful properties on the DSV, which will make the process of adding new tables and relationships much easier. In order to find them, right-click on some blank space in the diagram pane and click on Properties. They are:

Next, let's build some dimensions. As this is one of the more complicated steps in the cube design process, it's a topic we'll return to again in future chapters when we need to deal with more advanced modeling scenarios. Right now we'll concentrate on the fundamentals of dimension design.

Using the 'New Dimension' wizard

Running the New Dimension wizard will give you the first draft of your dimension, something you'll then be able to tweak and tidy up in the Dimension Editor afterwards. The first question you'll be asked, on the Select Creation Method step, is how you want to create the new dimension and there are effectively two choices:

• Create the dimension from an existing table or view in your data source (the Use an Existing Table option)

• Have BIDS create a dimension automatically for you and optionally fill it with data (the other three options)

In keeping with our line of argument that all relational modeling work should be done outside BIDS, we recommend you...

With some dimensions built, the next step is to run the cube wizard to create the cube itself. Remember that at this stage all we want to do is build a very simple cube so that we can test-drive the data, so we're not going to do anything other than run the wizard. You'll be doing a lot of work in the Cube Editor in the next stage of development, but if you've set up the DSV in the way we recommend, then you'll find that when you've finished running the wizard, you will have something that you can deploy, process and browse immediately with no changes required.

With processing complete, you can take a look at your cube for the first time, either in the Browser tab of the Cube Editor or in your client tool of choice. Now is a good time to reflect on what we've seen of the cube development process so far: