A viewer of my Pluralsight courses wrote to me asking what I thought was an excellent question. “What is the advantage of having an SSAS Cube?” or at it’s root, why do we need cubes at all? What do cubes provide that we can’t get directly from the database?

It’s an excellent question because understanding why is the foundation for all other learning about cubes. If you know why you are doing something, you’ll better understand what you are doing as you do it. I think the answer can be best illustrated by an example.

Let’s say you are an analyst for a huge  chain of international retail stores. The big box kind that sell everything: groceries, household items, clothes, and the like. You’ve been put in charge of toilet paper sales. When starting your analysis, the first thing you want to know is “How much toilet paper have we sold over the last five years?”

This may seem easy at first. A simple SELECT SUM(Amount) FROM SALES WHERE Product=’Toilet Paper’ would yield the result. But wait, is the query really that simple?

Think about it terms of scale. On any given day you likely sell at least a million rolls of toilet paper. Amplify that by years, and now you are looking at billions of individual rows the database has to crawl over to get the results, if not more! Even a high performance database could take hours to process such a query.

Of course you wouldn’t stop there. You’d then want to break down sales for each year. Then for each month in the year. For each month by state/province. Then by sales territories. Then by individual stores.

But wait! You might decide to analyze by state/province first, then by year/month. As you can see, your queries would soon be taking hours, if not days to run.

In addition, your queries would be brining the database to its knees, your coworkers would be ready to spread nasty rumors about you on social media, and the DBA Police would be bursting into your cube, disconnecting your computer from the network and putting you in handcuffs.

By now you’ve probably guessed the answer to this is the cube, but it circles back to the original question, why a cube?

While the focus of the question was SSAS (SQL Server Analysis Services) the answer applies to pretty much any cube based software. Analytic cubes work by aggregating values ahead of time. Whenever the cube is processed, the cube engine (SQL Server) contacts the source database, reads in all the records, and sums them together. The totals are what is stored in the cube.

During processing the cube engine will analyze the data, and determine the best way to organize the data into small pieces that can quickly be added up to answer most questions. Of course, during the design of the cube the developer can provide hints to the cube engine in various ways, such as creating hierarchies.

A hierarchy provides guidance to both the end user as well as the cube engine. For example, you may have one for Year / Month / Day. There may be another for Country / State / County / Store, and perhaps another for Sales Region / Sales Territory. Building these hierarchies gives the cube hints on how to store the data aggregations so they can quickly be combined at query time.

Note I said aggregations, as a cube can store many different types of totals. Of course there is the natural one of sum, but a cube can also contain averages, maximum and minimum values, and more. In addition, it can also create calculated values to hold things like year over year growth.

You might be thinking “well wait, wouldn’t it take forever to process the cube everyday?” True, if you processed the entire cube daily. However, most cubes (and SSAS for sure) provide the ability to do incremental processing. This means the engine only has to read in new records in the source database, records that have appeared since the last time the cube was processed, and add those values to the stored totals.

All of this together helps us to answer our question, “Why do I need a cube?”

Speed

Being able to analyze data fast is the primary benefit of cubes. Being able to slice and dice data in an almost infinite combination is why cubes are so valuable to data analysts across the world.

Reducing the workload on the source systems (whether a relational database or a data warehouse) is an additional benefit. The aggregations are created once and stored in the cube, as opposed to having to query the data warehouse each and every time we need information.

Some may argue they can achieve similar goals with tools like PowerBI, and you could. But you need to understand that under the hood, PowerBI is using an SSAS (Tabular) cube to hold the data. So on the back end you are still using a form of SSAS for your analysis. In addition, using a cube provides a centralized data source for many reporting tools, including PowerBI.

By now you can see what benefits a cube provides, and why you need one!

Anyone who works with SSAS (SQL Server Analysis Services) knows the DSV (Data Source View) is the key to the project. It is through the DSV that everything else is built on. Unfortunately, in most projects I’ve worked on, it is generally the biggest mess.

Take this simple example cube based on the Adventure Works data warehouse.

What a mess! Fortunately there is a very simple way to clean it up.

Go up to the toolbar area. Right click to bring up a list of available toolbars, and pick the Layout.

Now you see a new toolbar appear:

Hover your mouse over each item, you’ll see tool tips such as Left Alight, Right Alight, Align Tops, and more. Note that in the menus there is a menu named Format. The same items on the toolbar also appear in the menu. I find it a little easier to use the toolbar, but do what you are comfortable with.

OK, now that we have our tools ready, we can start cleaning up that messy DSV. There are two ways to select the tables (or views) that we want fix up. First, you can simply click in an empty area of the design surface and drag the mouse. A little dotted line outline will appear showing you which tables will be in the selection.

The other option is to click on the first table, what is known as the “reference”. You’ll know the reference because it has white border handles. Then CTRL+Click on the other tables you wish to align, or make the same size as, the reference table. You’ll know these because they have a thick black square on the sides and border.

Now go to the layout bar or the menu, and find the button for align lefts. Click, then click the button for make same width. Repeat the process for the other tables in the DSV. When you are done it could look this pretty:

With just a few minutes work your DSV is now organized into neat rows and columns of uniform width. This makes it much easier to read. Your eye is not distracted by the jagged alignment and the uneven widths. Instead, you can much more easily focus on the text inside the boxes, which is after all the important part.

One last tip, if you wish to move the selected table (or tables) a bit, hold down the CTRL key, then use the arrows to move everything in tiny steps to the position you want.

I did the above example using SQL Server 2008R2 BIDS, this technique also works with the SQL Server Data Tools that shipped with SQL Server 2012 (SSDT, aka Visual Studio 2010) and with the newer SSDT for Visual Studio 2012.