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.

BI Documenter

When I came to work for Pragmatic Works, I was naturally given the opportunity to use their (well our now) tools. Of all of them I think BI Documenter is my favorite. Boy is this thing complete.

Of course, like some of the other SQL Server documentation packages it will do a great job of reverse engineering an existing relational database. BI Documenter will output either HTML or a compiled help file (CHM) file. It places your database structure into a drill down tree, with all the expected bits and pieces. Tables, columns, stored procedures, functions, all with the code needed to create it.

It doesn’t stop there though. Got Analysis Services? Not a problem. It will generate the same drill down structure that you are used to seeing for a standard SQL Server database. Cubes, Measures, Dimensions, KPIs, Calculations, complete with all the meta data and MDX you could ever want.

Of all the features though, the absolute coolest is it’s support for Integration Services. You can point it at either your SSIS server, or to a file store, or just to the directory with your solution. Here’s a sample that uses the package from my Intro to SSIS presentation:

image

Not only does it provide detailed info (it appears below the pic) but it will also reproduce the graphical flow! And if that wasn’t cool enough, you can drill down into the other executable parts, such as the data flow.

image

 

Here’s a small sample of the details:

image

It doesn’t stop there either, providing complete support for SSRS as well. Just point it at your Reporting Services server and away it’ll go!

Now, I realize this sounds a lot like a commercial, and since I now work for Pragmatic Works probably more so. Bu I just think this is an awesomely cool product, I can think of a lot of uses for it too. Providing turn over documentation, running it weekly to create version snapshots so you can track changes to your SQL Server, providing reference material for developers, and probably a zillion other things I haven’t thought of. (If you have thought of some, by all means leave a comment, would love to hear how YOU are using this.)

I’m sure I’m not doing the product justification, so if you want to see some video demos head on over to http://pragmaticworks.com/ for more info. And again, I apologize if this sounds like a commercial but I am blown away by how cool this product is so I just had to share.

 

 

 

Disclaimer: I do work for Pragmatic Works, and received my copy of this really cool software as a result of my employment.

Data Warehousing / Business Intelligence at Pluralsight

It’s been another busy month, and I’m pleased to announce my two newest modules were published today on Pluralsight. If you’re not familiar with Pluralsight, they are a training company that has a vast library of training videos.

The description of my new course can be found here:

http://www.pluralsight-training.net/microsoft/olt/Course/Toc.aspx?n=intro-dwbi-course

There are two modules, the first is an introductory session for DW/BI. It’s primarily slides, by the end you’ll have a grasp on the arcane terms around Business Intelligence such as facts, dimensions, surrogate keys, OLAP, and more.

The second module is an overview of the Microsoft tools for doing DW/BI. It starts with the Adventure Works Lite database. From there a data warehouse is built, on top of which an analysis services cube is created. Finally a report will be generated off the cube that meets a specific business need.

Along the way attendees will get to see Visual Studio 2010 Database Projects, along with the SQL Server toolkit: SQL Server Integration Services, SQL Server Analysis Services, and SQL Server Reporting Services. Attendees will also get a look at SQL Server Management Studio.

Enjoy!

Follow

Get every new post delivered to your Inbox.

Join 101 other followers