Tag Archives: SSAS

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.