Category Archives: SSAS

Using the Analysis Services Execute DDL Task

My grandfather used to say, “There’s more than one way to skin a cat.”  I always thought it was a disgusting saying, I mean who would ever want to “skin a cat”?!  Gross!  But the underlying meaning was clear, there’s always more than one way to do something.  And no my grandfather never actually skinned any cats, he was just a country boy from a small town in Iowa, where you quite often had to come up with creative ways to get things done.

There are often times when we get so focused on what we are doing, we forget to look around.  This happened to me a few weeks ago.  We were in the middle of a big upgrade at work, not only where we upgrading our ERP system, we were changing platforms as well (read this as:  no one got more than 3-4 hours of sleep per night for about two weeks, including myself).  We also have a BI solution that uses SQL Server Integration Services (SSIS) to pull data from our ERP system, stores it in a data warehouse on SQL Server then has a multi-dimensional SQL Server Analysis Services (SSAS) cube that sits on top of the warehouse.  Pretty common scenario.

Last part of our ETL process is to process the Dimensions and Facts for the newly loaded data.  I have always used the Analysis Services Process Task in SSIS to accomplish this.  Works great, it’s stable and easy to use.  Just click a few items and you are off and running.

My ETL code had been stable for so long, I hadn’t needed to make any changes since I upgraded from Visual Studio 2015 to Visual Studio 2017.  Little did I know that there was a bug in Visual Studio 2017/SSDT 15.5.2 that will not allow you to use the Analysis Services Process Task if your target version of SSIS is 2016.  You will get a couple of different errors depending on what you are doing.  If you are trying to modify an existing package that uses an Analysis Services Process Task and your target version of SSIS is 2016 you will get the following error:

Post71-EditExistingError

If you try to create a new package using an Analysis Services Process Task and your target version of SSIS is 2016 you will get the following error after it allows you to configure your processing tasks and you click OK:

Post71-NewPackageError

In both of these scenarios you will not be able to save the package.  So what the heck are you supposed to do?!  Here’s where my tunnel vision (and panic) sets in.  How was I supposed to get my SSAS objects processed?

I could always script out my processing tasks using SSMS and drop them in a SQL Agent job step.  But I have multiple environments and multiple cubes so each one would have to be hard coded.  Not a great idea, so scratch that.

Post71-ScriptProcesinSSMS

I could just create a new SSIS project, targeting SSIS 2014, just for the processing tasks.  (Thanks to Teo Lachev for the idea.  And yes, you can deploy SSIS 2014 targeted packages/projects to SSIS 2016.)  It’s better than my first idea.  I could parameterize all the connection managers so nothing would be hard coded, but now my code lives in two different projects.  Not ideal, but it would definitely be better.

Then Gerhard Brueckl pointed me to the Analysis Services Execute DDL Task.

Post71-ASDDLTask

I did some quick research on it here and turns out, this was exactly what I needed to “skin this cat”.  It would allow me to keep my SSAS processing packages in the same project as the rest of the ETL and I could parameterize it to my hearts content (queue beams of light from above and singing angels).  Why hadn’t I thought of this before?!  To be honest I had never paid attention to this task.  It had the term DDL in it, and I wasn’t writing DDL, so why would I ever need it, especially since I was dealing with SSAS.  Yes, I know, need I remind you about my lack of sleep and my tunnel vision.

Post71-ASDDLTaskOptions

The cool thing about the Analysis Services Execute DDL Task is that you have a choice when it comes to your source for the DDL.  I opted for the variable because I needed to parameterize it with the SSAS database name.

Post71-SourceTypes

I was able to script my processing tasks, dump that code into a variable that I built with expressions so that it could be parameterized.  Now one thing to keep in mind is that the XMLA that is produced for SSAS processing tasks has double quotes in it.  You will have to either a) escape the double quotes or b) create a variable for the double quote to use in the XMLA in your expression to build your variable.  I opted for b, create a variable to hold the double quote, it seemed cleaner to me.  Here’s a good post on StackOverFlow on how to do both.

As you can see below, it looks very intimidating with all the XMLA & expressions, but it creates a very pretty XMLA processing task in the end.

Post71-VariableExpression

I created one task for processing dimensions and one task for processing facts.  Mostly because I want to be able to track performance respectively, but you could create one task to do it all.

I was able to add these new packages to my SSIS project and deploy it without issue.  They’ve been running ever since with no problems.

I hope this post helps someone else break out of their tunnel vision.

PSA:  No cats were harmed in the writing of this blog post.

Aggregation Design is Back!

If you use SQL Server Data Tools (SSDT) and SQL Server Data Tools – BI (SSDT-BI) for your SQL Server 2012 development, then you have no doubt been frustrated, like me, by the fact that if you have both of these installed you no longer have the ability to create new Partitions and AggregationDesigns when working with the SSAS MOLAP model.   You can find others that have run into this issue here.

The solution I found was to install both SSDT & SSDT-BI on my laptop then have a VM with just SSDT-BI on it. That way when I needed to work on Partitions or Aggregation Designs (which is very infrequently), I just fire up the VM and I’m off and running.

Well, with SQL Server 2016 development we get to use Visual Studio 2015 and SSDT is now included in that install (although you do not get the BI project types, more on that here), no more do you have to have separate machines. I tested CTP 3 and Partitions and Aggregation Designs work once again. Hooray!

Aggregation Design

How to Add Users to Roles in SSAS, a.k.a., Where the Heck are the Buttons?

I’ve been responsible for administering Analysis Services (SSAS) on a regular basis for the past 7-8 years via SQL Server Management Studio (SSMS), but developing is a whole different ball game (and set of tools). I only recently started doing development work with SSAS on a regular basis. Prior to that I dabbled once every two to three years, enough to be able to say, “Yes, I can do that, but I can’t remember exactly how.” Fortunately the tool used for SSAS development is a tool that I am very familiar with, Visual Studio (or as we data professionals now call it, SQL Server Data Tools).

I recently had to change some security for our cube and was tasked with modifying the role membership. Now this most likely seems like a no brainer and it is, as long as you know where the buttons are. What buttons am I referring to? Those precious Add and Remove buttons that allow us to modify the members. When doing development work in SQL Server Data Tools (SSDT), there are all these nice menus and toolbars at the top or on the left hand side. So when I went to do some work on the role membership, imagine my dismay when I could find no way to add/remove users to the role via the menu or toolbar.

I must have spent a good 10 minutes looking through all the menu options and toolbars, only to discover there is no way to add/remove members in any of those. So I of course freak out mode ensued. I must have the wrong version of SSDT, I must not have the right permissions to be able to add/remove users, the list goes on and on. I took another look, I mean look at all that screen real estate. You have that HUGE white space that is just dying to be used and no way to use it.

Post26Pic02

Then it happened, I the saw the light (or buttons as it were), hiding way down there at the bottom of my screen.

Post26Pic03

Like I said, this may seem like a no brainer, especially for someone who does SSAS development work on a daily basis, but for those that are new or only use it every so often, it’s frustrating. After seeing someone ask where the buttons were via #sqlhelp on Twitter, I decided I needed to write this up.

I hope this saves some other unsuspecting soul the frustration that I experienced.

Automating SSAS Backups

Backing up databases is one of the most important jobs of a DBA. If your data is not safe, your job is not safe. Data is the lifeblood of a DBA. That said, there are so many products out on the market that will help with backing up transactional databases in SQL Server, but when it comes to Analysis Services (SSAS), you are on your own. That’s what I discovered when I became responsible for a SSAS database.

The good thing, is that there’s a very simple way to back up your SSAS databases. SQL Server Management Studio (SSMS) has this great feature that allows you to script just about anything you need to do, including backing up a SSAS database.

Here’s how:

  1. Open up SSMS and select the Analysis Services server type in the Registered Servers window.

Connect to Analysis Services

  1. Double-click your server name, so that it appears in the object explorer, then expand the databases folder. Right click on the database you want to backup and select Back Up…

Right-click your database

  1. The Backup Database dialog opens. Fill out the values appropriate for your environment. I highly recommend encrypting your backup files, just don’t forget what the password is otherwise you will never be able to restore your database.

Backup Database dialog

  1. Instead of clicking the OK button when you are done, click the little arrow next to the Script button at the top of the screen and select Script Action to New Query Window. Click the Cancel button to cancel the Backup Database dialog.

Script backup

  1. You should now have an XMLAQuery window in SSMS that contains the commands to back up your database.

XMLA Code

Wow, that was easy. Now you can create a SQL Agent job and just paste this XMLA query in the job step (be sure to select SQL Server Analysis Services Command as the job step type) and call it a day. But you probably shouldn’t. As you will notice, I selected the Allow file overwrite option in the Backup Database dialog and that is reflected in my XMLA script with the AllowOverWrite tag set to true. So, if I created a SQL Agent job to run every day and used this as my job step, I would never have any backup history, I would only have the most current backup. For some shops, this will be okay, for others, it won’t. In my shop it wasn’t enough. Policy dictated that I keep one week of backups, regardless of whether it was a transactional database or an OLAP database.

Luckily, PowerShell and I have become good friends. I was able to quickly create two additional steps in my SQL Agent job that utilized PowerShell commands to achieve my goal of maintaining one week of backups. I created one step to rename the backup file by appending the current date to the file name and the other step I created to clean up any old backup files, so that I didn’t fill up my hard drive with backup files. Here are my scripts.

Rename file:

cd c:
$today = get-date -uformat "%Y%m%d"
$oldname = "\\uncfilepath\Databasename.abf"
$filepath = "\\uncfilepath\"
$newname = $filepath + "Databasename_" + $today + ".abf"
rename-item $oldname $newname

 

Clean up old files:

cd c:
$RetentionDate = (Get-Date).AddDays(-6)
$FilePath = "\\uncfilepath"
Get-ChildItem $FilePath -recurse -include "*.abf" | Where {($_.CreationTime -le $RetentionDate)} | Remove-Item –Force

 

I won’t go into detail about my PowerShell script here, it’s mostly self-explanatory, with the exception of the first line in each, cd c:. I discovered that since I was using a UNC path, I needed to add this little tidbit to the beginning of each script otherwise the steps would fail. This is because the version of PowerShell that is being invoked inside a SQL Agent job is not EXACTLY the same version that is invoked outside of SQL Server.