Category Archives: SSAS

Why Do I Need An SSAS Cube?

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!

Advertisements

So You Think MDX is Hard? Presented at SQL Saturday Nashville Jan 17 2015

At SQL Saturday Nashville, on January 17 2015, I presented “So You Think MDX is Hard?”. Unfortunately the SQL Saturday website is having issues with code samples, so I have uploaded the presentation to my Technet Code site. You will find it at http://bit.ly/acmdx

Inside are three files, a PDF of the slide deck, the MDX script I ran, and the analysis services database as a backup file (abf) that you can restore to a server on which you have administrative rights. This sample was created in SQL Server 2012, although should work on 2014 and (although I haven’t tested) should work on 2008R2.

SQL Saturday Jacksonville #298–So you think MDX is hard?

For SQL Saturday #298 in Jacksonville, FL on May 10, 2014 I am presenting “So you think MDX is hard?”.

A lot of people have this perception that MDX is difficult. It really isn’t, when you understand what it is trying to accomplish under the hood. In this session we’ll begin with a fundamental understanding of what MDX will do for you. Then we’ll roll up our sleeves and dive into MDX code, starting from the simplest select statement and winding up building calculations you can put into your own SSAS Cubes.

You’ll find the presentation slide deck and code at:

http://sqlsaturday.com/viewsession.aspx?sat=298&sessionid=19934

Make Your SSAS Data Source View Pretty

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.

image

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.

image

Now you see a new toolbar appear:

image

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.

image

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.

image

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.

image

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:

image

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.

SSAS Duplicate Attribute Error – Another Cause

I had  a real head banger this afternoon and I’m not talking about the heavy metal playlist I was jamming to in my iPod.

I had a table that, in addition to the surrogate key, business keys, etc had these columns:

Level1 Level2
Phineas and Ferb Phineas
Phineas and Ferb Ferb
Phineas and Ferb Perry

I had a dimension in SSAS where I had a Level1 -> Level2 Hierarchy built. When I tried to process the dimension, SSAS kept kicking out “duplicate attribute error” on Perry. I did the usual checking, yes my attribute relationships were OK, the Key property was built correctly, etc.

So then I moved to look at the data itself. I first did a SELECT * FROM CoolShow WHERE Level1 = ‘Phineas and Ferb’ and Level2 = ‘Perry’.

I got back 4 rows. Hmm. After some more head banging (Guns ‘n Roses, Paradise City) I wound up doing a SELECT * FROM CoolShow WHERE Level1 = ‘Phineas and Ferb’ and I get back 42 rows with Perry. Hmm, I say to myself, “self, that looks odd”. To which self replied “duh”.

Then self suggested I do a SELECT ‘*’ + Level2 + ‘*’ FROM CoolShow WHERE Level1 = ‘Phineas and Feb’

This yielded some interesting results, 4 rows read *Perry* the other rows read *Perry *   (Note the blank space between y and * .)

Well obviously I needed a RTRIM, which I dutifully added then reran the query. Only to get the *Perry * again in the output. At this point self said I was on my own and abandoned me to drown its sorrows in a pitcher of margaritas.

I took the output and copied it into an editor that would do hex mode. So what do I see but a 0D 0A in the space between the y and the *, causing me to scream “AH-HA” as Queen’s Bohemian Rhapsody hit its crescendo. I also scared the cat, but I only mention that because cute cat things are supposed to be popular on the internet and I figure it might help my SEO. For those who don’t speak HEX, 0D 0A is 13 and 10, which turn into a Carriage Return and Line Feed.

Now by this point most of you have probably given up on this handy tip, deciding a pitcher of margaritas sounded pretty good and left to find some. But if you are still hanging in, I modified the view with this code:

RTRIM(REPLACE(REPLACE([Level2], CHAR(13), ”), CHAR(10), ”) ) AS [Level2]

Returning to the cube I was able to process the dimension successfully and answer the question of “Where’s Perry?” (Answer: He’s at the bar trying to keep a drunken self from using his evil margaritainator invention.)

So the moral of the story, if you get duplicates error, and your dimension looks okey-dokey, check the data to see if you have some errant CR/LFs. Apparently SSAS doesn’t handle them very well.

Now if you’ll excuse me, I’m going to join self at the bar before self guzzles all the margaritas (self is such a drunken sot). AC/DC, take me away with some “Highway to Hell”!

SSAS Training Resources

I’ve been asked to provide links to some useful resources for learning about SQL Server Analysis Services. Below are a list of my favorite blogs, books, and other sites to learn from.

A quick disclaimer, some of the links below are by co-workers or other people I have an affiliation with, financial or otherwise. That’s because I’m lucky enough to work with some of the best people in the field. Also, in the case of the books I’ve linked to the Kindle version where possible, mostly because I’m a Kindle junkie. There are paper versions of the books, and you are free to buy from your favorite retailer.

Books

Microsoft SQL Server 2008 Analysis Services Step by Step – This is a great beginners book. If you are starting at ground zero, this is the book to start with.

Professional Microsoft SQL Server Analysis Services 2008 with MDX – If you are looking for one book that has everything, this is it. It’s a huge book that covers pretty much everything you need to know about SSAS.

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services – There’s one book that is universally hailed as the “experts’” book, and this is it. Be warned, this is not a beginners book. Don’t try to tackle this until you’ve had at least a little SSAS experience. But it should definitely be on your “must buy” list at some point.

Blogs

Devin Knight – My coworker Devin posts about all aspects of SQL Server BI, but his SSAS posts are ones I often refer back to, or refer others to, when learning SSAS concepts.

Marco Russo – Marco’s blog is a great source of in depth SSAS content. He’s also one a co-author of the “Expect Cube. . .” book listed above.

SSAS Info – This last link isn’t exactly a blog, but more like a blog aggregation site. It’s very useful though, and should be on your list of regularly read sites.

Videos

Pragmatic Works Webinars – On our website we have a big catalog of past webinars (all of which are free to watch), many of which focus on SSIS. 

Pluralsight – Pluralsight has an extensive catalog of courses, including some great SSAS content by Stacia Misner. It’s subscription bases so there is a modest fee (starts at $29 US per month last I checked) but well worth it for the training you can get. There’s also a free trial.

SQL Share – This site takes a new twist on videos, in that each video is very short and very focused on one specific task. 

 

For a quick link direct to this post, you can use http://bit.ly/arcanessas 

Using TFS2010 with Visual Studio / BIDS 2008 and SQL Server Management Studio

When I come to a customer site, I often have to help them get setup with TFS (Team Foundation Server) 2010, Microsoft’s source code control / ALM (application lifecycle management) system. This is so they can work with their BIDS (Business Intelligence Developer Studio) projects as a team, giving the added benefit of source code control. I’ve had to do this often enough I wanted to record the steps for my own use, and hopefully others too.

Installing the TFS 2010 tools for Visual Studio / BIDS 2008

First off, thanks to Derek Miller for covering most of the steps involved in his blog post http://derekjmiller62.wordpress.com/2010/10/19/using-tfs-2010-with-bids-2008/. I won’t go into the detail he did, but will summarize into these basic steps.

1. If you haven’t installed Visual Studio 2008 Service Pack 1, do so by downloading it and installing.

2. Next, you will need to install the Visual Studio 2008 Team Explorer.

3. After installing Team Explorer, you will have to go back and reinstall VS SP1 (from step 1). Don’t skip this step! Team explorer has some older components that overwrite the SP1 components, and you will have reinstall them.

Now this next part I really haven’t seen anywhere else and was a real pain to find, and thus is the main reason for this post. During the SP1 install, we often see “Visual Studio SP1 Installation Failed”. Checking the error log, buried deep you will find “Returning IDOK. INSTALLMESSAGE_ERROR [Error 2902. An internal error has occurred. …”

When you see this, go to your Control Panel, and then to Add Remove Programs. Look for a program called “Microsoft Visual Studio Web Authoring Component” and uninstall it. This is actually installed as part of the Office suite, and you don’t really need it since you likely have much more powerful web authoring tools, or since you are doing BI development won’t be doing an web development in Microsoft Office.

After uninstalling it, SP1 should then install, and you are ready for step 4.

4. Install the Visual Studio Team System 2008 SP1 Forward Compatibility Update for Team Foundation Server 2010. That probably took you longer to read than it actually will to install. After installing, it may prompt you to reboot. Even if it doesn’t ask you should reboot anyway, we’ve seen a few times when we weren’t able to connect until we rebooted.

After that you should be able to go into Visual Studio and go to Tools, Connect to Team Foundation Server. If you still have problems connecting, I will refer you to Derek’s post where he describes some registry entries you can try. So far we haven’t found them necessary, but you may.

Installing the TFS 2010 Tools

Note that there is one big limitation to using TFS 2010 with VS2008. You can connect to a TFS site and upload your solutions and projects, but you can’t create a new team site with VS2008. To do so, you will need the VS2010 shell with the TFS components, a free download.

Installing TFS 2010 for SQL Server Management Studio (SSMS)

Now that you have BIDS all setup to work with TFS, it only makes sense to make your SQL Server Management Studio (SSMS) also work with TFS. Joseph Jun has a great blog post that goes into all the nitty gritty of how to do this. The short version though, is after you install the TFS 2010 tools in the step above (and they are a prerequisite) you need to install the Team Foundation Server MSSCCI Provider 2010.

After the install, you should see a new Source Control menu option under the File menu in SSMS. From here you can launch the TFS 2010 management shell or open an existing SSMS project / solution. If you have a solution you need to add, simply right click on the solution in the Solution Explorer window and pick Add to Source Control.

Visual Studio Database Projects

Note that if you are using Visual Studio Database Projects, any SQL Server 2008R2 development must be done in Visual Studio 2010. VS2010 is already setup to talk to TFS 2010. If you are using VS 2008 database projects to build a SQL Server 2008 (non-R2) database, then with the steps above you should be good to go for checking in your database project into TFS.

And away we go!

And with that you should be setup to manage your BI Development in Team Foundation Server 2010. It’s a lot of work, but well worth the effort. Using TFS will let your BI staff work as a team to develop projects. Additionally you have the benefit of source code control, something invaluable in the case of package corruptions or needing to track history.