Introduction

Multidimensional cubes and transactional databases are two very different things. From experience I have seen even veteran DBAs avoid the subject of cubes completely because it is too much of an unknown area for them. Often cube work is passed on to developers because of their comfort with using Visual Studio. This is a great pity because it is, in reality, not very difficult at all to create an OLAP cube. It is safe to say that most of the work needs to be done in a traditional SQL Server database engine / SSIS environment from creating the data warehouse model to keeping it fed with ETL packages. This article assumes you already have a data warehouse and uses AdventureWorksDW2012 as an example. Following these steps should put you on the road to a decent SQL Server business intelligence solution based on a read-optimized OLAP cube.

• *Please note: You will need SQL Server Data Tools or SQL Server Business Intelligence Developments Studio (BIDS). You can get it here.

Getting started

Every OLAP cube is created with an analytics solution. To create one, open SQL Server Data Tools or BIDS and create a new “Analysis Services Multidimensional and Data Mining Project”

This will open a blank solution and will create the basic folders needed in the solution explorer. Something that is quite handy to know about SQL Server Analysis projects is that the order of creation or the workflow follows the order of the folders in the Solution Explorer.

So the order of creation has to be:

1. Data Source
2. Data Source View
3. OLAP Cube
4. Dimensions

So let’s start by creating a new data source. To do this, right click on Data Sources and click “New Data Source”. This will open a wizard that will allow you to make a normal database connection to your data warehouse:

In the next step you will be asked to specify the type of connection. It is important here to think of the type of security you will need for your OLAP cube. I recommend creating service account on your Active Directory and using the “Use a specific Windows user name and password” option to set this user. You can find more information about this here.

Having completed this step you now have a source of data for your OLAP cube. Next you have to create a data source view and specify which data you want to use for your cube. This is done by right clicking on the Data Source Views folder and adding a new one. The wizard will ask you which connection you would like to use and which fact / dimension tables you want to base your cube on. Generally you will want to select all dimension and fact tables and deal with the partitioning later in the cube, so you can go ahead and do this. You also have the option of choosing only parts of your data warehouse. For simplicity I have done this and only chosen the Internet Sales sections of the AdventureWorksDW2012 database.

It is important to have correct foreign key relations in your data warehouse beforehand because SQL Server Data Tools is able to create a decent view diagram using existing relations in the DW. If you have not done this you will have to explicitly specify the relations between your fact and dimension tables. If all goes well you will have a view similar to this:

Now comes the fun part: creating the cube structure. Once you have a data source view set up you can create a cube. Right click on “Cubes” and create a “New Cube”. In this wizard you should choose to “Use existing tables”. You will then be prompted to select your “Measure Group Tables”. These are your fact tables. Select them and click next. The wizard will then automatically detect fields that can be used as measures. Click next again. Assuming your foreign keys were correct in the DW or you did the explicit relations correctly in the data source view, the wizard will now automatically select the necessary dimensions. In order to explicitly declare the relations between tables the easiest way is to drag and drop the joins between the table key / foreign key in the interface / data source view model. Once this is done, click next once again. Give your cube a name and click “Finish”.

Voila! You now have your very first cube structure. It should look something like this:

You are nearly done. The basic configuration is complete but your cube only exists as a blueprint at this stage. In order to create it on your Analysis Services server you first need to tell Data Tools where your server is located. This is done by clicking on Project > [YourProjectName] Properties and then going to the deployment tab. Here you can specify the location of your Analysis Services server and the name of your database (if it does not exist it will be created):

To create the cube and process the data from the data warehouse to the new cube you need to click Build > Process… in the superior Visual Studio toolbar or by right clicking on your cube file within the solution.This will bring up a screen saying your database is out of date (which is normal as it does not exist yet), agree to build and deploy your project and Data Tools will create the database. Next a “Process Cube” screen will appear:

At this screen you can leave everything as default and click “Run…” and if you have no configuration errors your cube will process:

Once you have processed the cube for the first time you can explore the data through the solution by opening the cube and clicking on the “Browser” tab. You can also bring up the same screen by opening SQL Server Management Studio and connecting to your Analysis Server (careful not to connect to the usual database engine) and right clicking on the cube. There you can choose “Explore”.

On this screen you can verify the data in the cube and also generate basic queries to use in your reports. The reports also have this query designer window if you choose an analysis source instead of a normal SQL Server database source. The queries created are in MDX format which is a whole other language. You can get a feel for it on the MSDN website. However, the subject of creating SSRS reports based on MDX queries will be covered in a later article.

Where to from here?

Once you have a basic cube up and running there are many things you may want to do to enhance the user experience. Some examples of advanced features are calculated measures (to create year to date sums or current month sums that are pre-calculated), hierarchical dimensions which can be used to order dimensions and allow for a more meaningful drill down to the data a user may need. These types of things can be added to the cube by using the selection of tabs in the Cube screen in Data Tools:

Once the changes have been made to your cube solution you can apply them to the cube by reprocessing from the analytics solution as you did above.

In conclusion, you may want to think of the following things:

• Add a cube processing step to your ETL package that is used to update the data warehouse.
• Set up your report server and start creating reports
• Add advanced calculated measures, hierarchical dimensions and cube perspectives
• Investigate the utility of SharePoint and self-service reports

References

The Basic MDX Query
SSAS Impersonation Smackdown – Specific Windows Acct vs Service Acct How to Create an Analysis Services Cube