Taking a backup of an SSAS database

Backups?  No problem - just set up a maintenance plan in SQL Server and schedule the backup to run every night while the world is sleeping, or my bit of it at least.  Or in my current job, use Red Gate Backup to do the same thing.  Both do a good job, both are easy to set up.

So what's the problem?

I'm starting to get involved with Microsoft's Business Intelligence tools.  I've blogged before about SSRS, the Reporting Services tool which lets you create and deploy nice-looking reports.  In fact SSRS was one of the first things I got involved with when I started using SQL Server.  But until I started my current job I had little practical experience of SSIS - Integration Services or SSAS - Analysis Services.   I plan to write more about these in future.

The idea behind SSAS (practiced egg-suckers can skip forward past this bit) is that you extract data from your regular transactional database, and create a de-normalised database showing Facts, Dimensions, and Analysis Cubes.  Never mind the details, the point is that you can do very quick analyses of things like sales of widgets by type and colour and salesperson and area and month and whatever else might possibly matter.  Yes, I know you can do all that stuff by writing an SQL query, but SSAS does it a damn sight quicker than writing individual queries.  Honestly, once it's set up, it's just a click and drag thing.  Very impressive, and the software comes free with SQL Server, so a whole lot better than the million dollars that other vendors are likely to charge for their competing products.  It takes a bit of setting up, (well, a lot) but once you have your SSAS database in place, it's very whizzy indeed.

Here's the problem.  SSAS databases are different.  Check out the files it creates - none of that boring .mdf and .ldf stuff.  And you can't take a backup with Red Gate - I rang and asked them, and they may be putting it into a future version, but not yet.  You can't take a backup with your maintenance plan, which is geared up to the bog-standard OLTP databases I'm used to.  No.  Welcome to the wacky world of OLAP!

What you CAN do is back up your SSAS database by hand.  Just right click on it, and save it to the location of your choice.  All you have to do is come into the office every night at midnight and take a backup.  Er, no thanks.  Maybe I'm a bit spoilt by the automation tools I'm used to, but I really don't fancy going back to the days of 24 hour computer operators, even if spinning tape drives have a certain retro appeal to Thunderbirds fans...

Here's how to do it.  Find the SSAS database, right click on it, and up comes the following dialog box.  Notice the backup file is in .abf format?  Analysis Backup Format, I suppose - in any event, it ain't the familiar .bak format that native SQL backup gives you, or the .sqb format of Red Gate.  It's something else... <cue spooky Outer Limits music>.

You can compress the database and encrypt it, at least in version 2008 onwards.  If you click on OK, it takes the backup.

But instead of OK, click on Script.  Suddenly you have a little chunk of XML which will backup your database.

Unfortunately the top secret encryption password shows up in plain text, so frankly there isn't a great deal of point in encrypting something with a password that any hacker can read, in my opinion.  Ho hum, maybe the next version will address this.

Set up a new job under SQL Server Agent.  Tell the dialog box that you want to run a SQL Server Analysis Services Command (don't worry, it's on the pull-down menu, you don't have to type it in) and paste the XML stuff into the box.  Schedule it to run in the middle of the night when no-one's about, and Bob's your uncle.

Here's another little wrinkle:  You probably won't want to overwrite the backup every night, so set up a second step which changes the name to include the date.  Instead of MIS.abf, you save 20120817_MIS.abf.  Next time you run the backup, it'll be 20120818_MIS.abf.  And so on.  Here's the code for doing it:

DECLARE  @Command NVARCHAR(512)

DECLARE  @DatePrefix CHAR(8)

-- convert date to 20121105 format

SELECT @DatePrefix = convert(CHAR,GETDATE(),112)

 --print @DatePrefix

--format command.  Note " " in case filenames contain spaces

SELECT @Command = 'ren "G:\OLAPBackups_Test\IPO MI.abf" '

                              + '"'

                              + @DatePrefix

                              + '_IPO MI.abf"'

                 

--print @Command

EXEC master.dbo.xp_cmdshell @Command

Essentially you create a variable for the date, and put today's date into it.  Create a variable for the command, and construct a Rename command.  Wrap it all up in quotes just in case someone wants a database name with a space in it.  Then you run that command using the xp_cmdshell stored procedure.  There are issues with using xp_cmdshell - see this helpful article.  But it can be useful.

There's a problem with this, of course - sooner or later your backup drive is going to fill up with .abf files.  Probably sooner, as your production SSAS database could easily end up containing millions of de-normalised grains of data.  So set up a third step to clear out the older versions every time the job runs.  ForFiles is a good way of doing this - and I wrote about this in my first ever blog post.  Here's the code:

-- delete abf backup files older than 4 days

exec xp_cmdshell 'forfiles

                        -p G:\OLAPBackups_Test\

                        -m *.abf 

                        -d -4      

                        -c "cmd /C del @FILE" '

So three steps then - the XML to do the backup itself, a bit of code to add a date prefix, and finally a bit of code to sling the oldest copy.  Schedule this to run nightly, set up an alert to email you if something goes wrong, and you are sorted.  

Book Review - Defensive Database Programming by Alex Kuznetsov

What are the assumptions made when code is written?  Kuznetsov gives the example of a string search - what should happen if the string contains wildcard characters like square brackets or percent signs?  You could ban wildcards, or you could write code to handle them.  But doing nothing will give you a wrong result further down the line. 

If you find a bug, look for it throughout your code - it has probably slipped through before.  Fix problems pro-actively - it’s faster and easier than waiting for a problem to occur, then troubleshooting and implementing the same bugfix.

He describes how upgrades can screw up older code - code that works perfectly when using the read_committed isolation level may fail to work if someone turns on the new read-committed-snapshot option.  There’s not much you can do about things that Microsoft haven’t released yet, but when they do happen you have to check their implications and change existing code. 

I’m reading this as an eBook and it is hard to follow the sample code, but it repays a bit of effort.  The key lesson is that it’s better to spend your time planning your code upfront than fixing cockups later.  

No Results Found

Suppose you run a query and it comes up blank?  Now, is that because there are no matching records, or is it because you got something wrong in the code?  Obviously at first the answer is (b) - there is always an error in code.  That’s why you test and test again, and then get someone else to have a look because they see immediately the things that you miss. 

But once you have got the code working properly, and you deploy a report to SSRS, you need a way of telling people that there are genuinely no matching records that satisfy the query’s criteria.  Otherwise they might ring up and tell you that the report isn't working, and disturb your meditation, or at least make your tea grow cold as you deal with their problems.  

Here’s how: add a textbox saying “no matching records” or words to that effect.  This is SSRS 2005, but subsequent versions look much the same:

Now edit the visibility property of that textbox. 

=iif(Len(Sum(Fields!bodid.Value, “MIDAS”))>0,True,False)

In this example, Midas is the name of the dataset, and the Bodid is an integer, a unique reference for each person in the table.  (Note that the field names are case sensitive, at least in 2005.  I haven't checked 2008 - so  bodid <> BodID).  

If records are selected, the length of the sum of the Bodids will be greater than zero, and the textbox will be invisible.  On the other hand, if there are no records, the length is zero and the message in the box appears.

Gone Skiing

It’s a bit chilly in Scheffau; -25C promised tonight, Friday, so the ski shop guys say.  Nice and warm inside though.  My wife dropped my mobile outside the underground car park in the snow, a bit of a shock to the nipple when I found it an hour later and put it back in my pocket.  A good day for wearing a fur hat.  I might even think about wearing some thermal underwear. 

Scheffau is a little village at the edge of Austria’s Skiwelt, claimed to be the largest inter-connected area in the country.  More details in this recent Blog by Brave SkiMom, Kristen Lummis.  Almost all the runs are red or blue - the few blacks are not very hairy, the reds usually have an easier side to them.  

We averaged one run to one hot drink on Saturday. Plus the occasional nip from the hipflask.  I wasn’t too cold considering it was somewhere below -17C - one advantage of wearing a helmet.  But if I see a thin balaclava I might get one to keep my forehead warm. 

The sun has been trying to break through the clouds but I think it might now have given up.  Still there is always Jägertee.  This is literally Hunter’s Tea, principal ingredient hot rum, and very acceptable if you are out hunting on a cold winter day.  The locals have taken to spelling this Jagatee, perhaps as part of the German language’s recent spelling revolution.  Or maybe it’s a brand name?

It’s Sunday and the snow diamonds are sparkling in the sunshine.  -17C at the Osthangbahn lift and very chilly indeed when I tuck down into a schuss.  Maybe I should buy a Hannibal Lecter face mask?  After a day’s skiing my face is tingling... I shall need to slap on loads of after sun tonight. 

Excellent lunch at the Brenneralm above Going.  Tirolergröstl mit Spiegelei - sauté potatoes with diced ham and a fried egg on top.  Schmeckt!  It’s a big, modern, efficient waiter-service place,  but perhaps a little soulless.  Big fire in the middle, panoramic views over the mountains.  I think my favourite is still the Rübezahlalm though.   

Unfortunately I have caught an ear worm today.  It goes Ich kann dich einfach nicht vergessen, Mary Jane, tum ti tum ti tum auf Wiedersehen,  round and round in my head.  They were playing it in the Aualm bar and earworms, being ferociously catchy, get into your head and stay there until displaced by something else. 

Over the mountain to Westendorf Monday; the run down to Brixen is a bit disappointing.  Maybe it seems a bit cramped after the runs higher up - this time last year it was a narrow strip of white  between brown fields, and this year I expected better.  But it was still narrow.  Still the diamonds in the air make everything beautiful.  

Went into the self-service place at the Choralpe but the smell of cigarette smoke was too off-putting; there didn’t seem to be a Nichtraucher area so we walked out and skied down the mountain to the Gassnerwirt.  An old favourite, but they have sadly taken Käsekräuterkräpfel off the menu this year.  Great for alliteration and umlaut fans, these were herby cheesy things, a bit like Greek spinakopitta.  Had Oma’s Suppentopf instead, very nice veg soup.  Oma (granny) gives you a very tasty, generous bowl, and I’m not a big soup fan. 

 

Tuesday, and Holzfällerpfandl in the Stoagrubnhütte. - don’t you just love German spelling?  This is pre-revolutionary stuff, and who can blame the Germans for storming the barricades to overthrow the tyranny of words like that?   The Woodcutter’s Pan was a pork chop with sauté potatoes and fresh veg with garlic sauce, served in an individual frying pan on a wooden platter.

Much warmer today (Wednesday) at -6C.  I have new skis and they are a big improvement - about 10cm longer and when I put them on edge they stay there instead of wobbling about.  I suspect my technique is mostly to blame - if I was a better skier they wouldn’t wobble. 

Lunch at the Gasthof Hochschwendt, which is tucked away at the bottom of the Schmiedlalm; very pleasant and I might even friend it on Facebook as the menu politely requests, although sadly I can't find it on facebook....  Geselchtes mit Sauerkraut und Bratkartoffeln.  They call this Kassler in northern Germany - I think it would be boiled gammon in English.   There is a problem with Sauerkraut unfortunately, delicious as it is, but let’s not go into that. I didn’t know this restaurant or even this run existed - it’s on the way to Going, on the Schmiedlalm, itself off to one side of a run that I’ve always zoomed straight past under the Ellmi’s 6-er chair.  Shame that it is served by one of the few T-bars left in the Skiwelt.  Wide, varied and well-groomed like most of the runs in the Skiwelt, there’s a ski-route from the bottom of the run down to the lifts at Scheffau in the next valley, although I didn’t try going that way. 

Thursday and a mid morning stop at the Bergkaiser motorway services high above Ellmau for a strange and slightly sickly concoction called chiocco intense - made of coffee, chocolate sauce and whipped cream.  Long wait for service as everything is prepared fresh; what can it be like at busy times?  Not bad for such a big place, and very civilised loos with an escalator down.  There’s allegedly a word in German which translates as “the sense of self-loathing and emotional collapse men feel as they walk down the steps into a beer hall toilet”.  That would not apply here.  

We were heading over to Going for another visit to the Brenneralm, but got sidetracked and decided to have lunch at the Hausleiternsstube just above Ellmau.  Another restaurant new to me - it’s a tiny self-service place, obviously family run and very friendly.  I had  Kartoffellauchsuppe mit Würstl.  That’s garlicky potato soup with a couple of sausages floating in it.  Tasty, but I think I prefer my frankfurters in a roll with mustard. 

Over to the Aualm and a quick  Leberkäsesemmel mit Senf on what is nearly the last run of the week.  Liver Cheese is neither liver nor cheese - it’s not unlike Spam (cue chorus of Vikings).  It makes a very tasty snack in a roll with mustard.

And finally down the blue to Scheffau.  I usually take the black (it’s not that steep, used to be marked as a red) and will do that next time.  The blue is a narrow icy road, while the red/black is wide and uncrowded.  At the middle station the red and blue runs come together and cross over.  My advice is stick to the red, although sometimes the poor signposting makes this tricky. 

Bis nächstes Mal!

Using SSRS? Then use Table Variables

I won't go into the difference between temp  tables and table variables - here's a blog which does this for me:

Temporary Tables versus Table Variables

and another one:

What's the difference?

The way I read these descriptions, there is little to choose between the two techniques. They certainly look  very alike - here's the grammar to create a temporary table, pretty much the same as creating a regular, permanent table except for the # character:

--create temporary table
create table #MemscalesA
(
bodid               int,
familyid            int,
strForename     nvarchar(100),
strSurname       nvarchar(100),
coverid             int,
strCover           nvarchar(100),
IsMember         tinyint,
IsSpouse          tinyint,
IsDependant     tinyint
)

So when Microsoft announced table variables,  for SQL Server 2000 I think it was, this revolutionary concept must have stunned and amazed the community:

-- declare table variable
declare @MemscalesA table
(
bodid               int,
familyid            int,
strForename     nvarchar(100),
strSurname       nvarchar(100),
coverid             int,
strCover           nvarchar(100),
IsMember         tinyint,
IsSpouse          tinyint,
IsDependant     tinyint
)


Wow, eh?  Sit back and catch your breath.

I'm being a bit unfair,of course - there are differences, and they depend on what exactly you are doing with your tables. Dimitri Tsuranoff puts it well (in the first link above) when he says "There is no universal rule for when and where to use temporary tables or table variables. Try them both and experiment."

So I was a bit surprised when I found that there was a circumstance when you MUST use table variables if you want your code to work.  It's if you want to use it in an Reporting Services report.

My colleague wrote some code to find people who had purchased unsuitable  insurance cover (single people with family policies, families with policies excluding dependants, that sort of thing.  Chances are that their circumstances changed after the purchase, but obviously it isn't right and the business wanted to put it right).  He did it using temporary tables and it worked like a charm.  Muggins here then tried to set up an SSRS report so that it could be delivered automatically to the people that needed it every month.  It plain refused to work.  

Your report will work fine whichever technique you use, while you are building it.  But SSRS almost certainly uses a different permission to you - and crucially, that permission is something like a db_datareader - it can read data, but isn't allowed to create a table, permanent or temporary.

Fun with data types

Are protected underwriting terms applicable?  (Never mind what it means).  The answer is either Yes or No – they either are or they aren't.  No maybes possible, no special situations - so the perfect data type would be Boolean - a value of 1 means True, and 0 means False.  Named after 19^th century mathematician George Boole, who presumably worked out that the opposite of True was in fact False.  Isn't maths wonderful?

Unfortunately, in SQL Server, there is no Boolean data type.  This might be something of a setback.  Still, the nearest thing that can be used in place of Boolean data is the BIT data type, which is an integer data type that can accept a value of 1, 0 or NULL value only.  And it only uses up one bit so it is very efficient.  

So, problem solved, I hear you say?  Ho, ho, ho; that is hollow laughter echoing round the IT crowd’s squalid basement.   Because 1 is fine for Yes, but 0 won’t do for No.  Oh dear me no - the bulk upload software to which I want to transfer data expects either 1 or blank.  It could be changed of course, but that would mean months of development and testing. 

Hokay, how else can we do this?  Tinyint, Smallint and no doubt Int and Bigint all implicitly convert the desired blank into a 0.  So no joy in that direction. 

A fixed size character string?  So I tried it as char(1), and did a CASE statement to specify ‘1’ for Yes and “ for No.  But that changed ‘’ to ‘ ‘ in other words, a space or ASCII code, er, 32.  Not the same thing at all, as far as a computer is concerned.  I asked for a single character – I get a single character – a space is a character, it just happens to be an invisible character. 

The answer was to use a variable length character string – the varchar datatype is usually used for strings that can vary greatly in length.  Names, for example.  You don’t know who is going to order a widget next – could be Jim Lee, could be Felicity-Jayne Anstruther-Gough-Calthorpe.  Using a varchar allows the length to vary – from 0, which is what we want, to 1, so it’s now varchar(1), which looks bizarre but works!  There’s a length overhead to using a variable character field, but we can live with that. 

I still think it should be Boolean!

How to Identify Twins

I'm trying to find a list of twins in my source database - reason being, twins share a surname, a date of birth, and a postcode.  Unfortunately, those are exactly the criteria that the destination database uses to identify and reject duplicates <sigh>.  So it's handy to have a list of twins beforehand and do some manual jiggerypokery to make things work as desired. 

(In an ideal world, perhaps our skilled team of Oracle experts would modify the destination database so that it considered the forename as well, and said to itself "Hey, twins!  Maybe I won't reject this guy after all."  But that puts us into a world of development changes and testing and pain - it's easier and quicker to leave the damn thing alone and manually wrestle with the input data.)

So the first step is to identify surnames which occur more than once in the data.  Notice that I'm grouping by Surname and Date of Birth:

      Select mb1.surname

      From   tblbods    mb1

      Group BY    mb1.surname, mb1.dob

      having count(mb1.surname) > 1

Which gives us:

Surname

SMITH

JONES

FORTESCUE-SMYTHE

MURPHY

MCDONALD

PATEL

Actually it gives us an awful lot more than this - there are 200,000 records in the table.  But I thought you would get bored if I listed more, plus I have had to change all the names and dates of birth so as to anonymise the data. 

Second step is to do the same for dates of birth which occur more than once.

      Select mb2.dob

      From   tblbods    mb2

      Group BY    mb2.surname,      mb2.dob

      having count(mb2.DOB) > 1

DoB

10/05/1993

11/06/1996

11/06/2007

25/12/2005

11/07/2010

14/02/2001

And then finally I want to select data where the surname is in the list of surnames from Step 1, and the date of birth is in the list of DoBs from Step 2.

Select

      mb.BodID, mb.Title, mb.Forename, mb.Surname,

      mb.relationship, mb.sex as Gender, mb.DoB

from

      -- Table of individual members

      tblbods     mb

where  mb.surname in

(

      -- Step 1 - surnames which occur more than once

      Select mb1.surname

      From   tblbods    mb1

      Group BY    mb1.surname ,     mb1.dob

      having count(mb1.surname) > 1

)

and mb.dob in

(

      -- Step 2 - Dates of Birth which occur more than once

      Select mb2.dob

      From   tblbods    mb2

      Group BY    mb2.surname,      mb2.dob

      having count(mb2.DOB) > 1

)

And the end result is:

BodID	Title	Forename	Surname	relationship	Gender	DoB
523189	MISS	MARIA	SMITH	Child	F	11/07/2010
523190	MISS	ALANA	SMITH	Child	F	11/07/2010
531637	MR	JASPER	FORTESCUE-SMYTHE	Child	M	14/02/2001
531638	MR	SIMON	FORTESCUE-SMYTHE	Child	M	14/02/2001
466662	MASTER	RAHUL	PATEL	Child	M	10/05/1993
466664	MISS	SAMIRA	PATEL	Child	F	10/05/1993
519007	MASTER	JOSEPH	MURPHY	Child	M	11/06/2007
519008	MISS	ANNETTE	MURPHY	Child	F	11/06/2007
339731	MISS	PATRICIA	JONES	Child	F	11/06/1996
339736	MISS	IMOGEN	JONES	Child	F	11/06/1996
419768	MASTER	WINSTON	MCDONALD	Child	M	25/12/2005
419769	MASTER	DOUGLAS	MCDONALD	Child	M	25/12/2005

Simples!  Hope you find this useful.