Getting Started with SQL Server 2012 Cube Development: Learn to develop and query Analysis Services cubes and models, with a practical, step-by-step approach with this book and ebook

Analysis Services is a foundational component of the Microsoft BI. It has the ability to host analytical models that can be used with tools, such as Excel through PivotTables. The model can either be multidimensional OLAP models or in-memory models called tabular models. This model is called the BI Semantic Model (BISM).

The BI Semantic Model is a new model that includes both the classic multidimensional model or OLAP model and the newer tabular model.

The OLAP model is a true multidimensional model that allows the developers to include very complex business logic into the...

As you understand, the choice you make about the deployment mode is very important, it is a choice that you make during installation of the server.

So what determines the right model for you? From a user perspective, the models look the same; the user can use both of them from any of the user tools that can connect to Analysis Services. The difference between the models is mainly something that the developer gets exposed to. As you saw in the architectural diagram previously, there are some differences on the languages that you use when developing the logic and the queries.

In the multidimensional world, you use a language called MDX (Multidimensional Expressions) to query the model and to write the business logic. MDX is a hard language to learn but it is extremely powerful when you really master it.

In the tabular world, on the other hand, you...

Now the time has come to really get started with the development, and in order to do this you first need to install Analysis Services. In order to do this, you need to ensure that you have a server that meets the prerequisites. As you can see in the following table, they are pretty modest when it comes to hardware. However, these are the minimum recommendations for production solutions. You need to ensure that you have a server that is powerful enough to meet the demands of your users.

Now everything is set for starting the development environment for SQL Server 2012. Go to the Start menu, and start the SQL Server Data Tools. It is the Integrated Development Environment (IDE) that is used when building all Business Intelligence projects in SQL Server 2012, and is built on top of Visual Studio 2010.

The first time you start the IDE, you have to choose the look and feel of the tool; since you are going to develop cubes, you should choose Business Intelligence Settings.

Now that the IDE has started, you can start getting accustomed to it. Just as in Visual Studio, SQL Server Data Tools works with solutions and projects. A project is a group of source code files that are deployed together to the target. A solution is a group of projects that you want to handle together.

Depending on what you are developing you are working with different kinds of projects. In the tool you have several project templates with different targets, there are Reporting Services and Integration Services project templates used when developing ETL packages and reports. Then there is an Analysis Services section containing the different project templates that you can use.

There are two different project types for the multidimensional model: one where you create a model from scratch, and one that imports a cube and dimensions from an existing Analysis Services server. The Import from Server (Multidimensional and Data Mining) project type can be used if you...

When you are working in SQL Server Development Tools, you are working locally in an offline mode. This means that you are only making changes to your local source files, and not to the server. This is something that can be confusing if you have not worked in an IDE such as Visual Studio before.

One thing that you also need to be aware of is that the IDE is dynamic, meaning that it looks different depending on what project template you have opened. If you have created a project of a different type, it will have content relevant to that project type in all the windows.

Let us go through some of the key aspects of the development environment.

To configure the cube project for deployment follow the given steps:

Now we have configured the development environment for project mode development. There is another way that you can develop as well, and that is in online mode. This is something that is rarely used, but it can sometimes be useful when...

Analysis Services is a very flexible engine that can be used to host both in-memory models called tabular models and multidimensional models. Depending on what mode you choose, you will get different capabilities and features. You have got your first look into the development environment used to create Analysis Services' multidimensional cubes, and deployed your first Analysis Services database to the server. In the next chapter, we will continue the FirstCube project, connect it to a database, and start creating the first cube, dimensions, and measures.