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>.
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.
Nice article and enjoyed the practical app.
ReplyDeleteNice article, will try to implement this in my test first. Was hoping to find an article just like this which would explain me about SSAS Cube database backups.
ReplyDeleteThank you, this article was very helpful!
ReplyDeleteThrough this post, I know that your good knowledge in playing with all the pieces was very helpful. I notify that this is the first place where I find issues I've been searching for. You have a clever yet attractive way of writing on Msbi online training
ReplyDeleteadana escort - adıyaman escort - afyon escort - aksaray escort - antalya escort - aydın escort - balıkesir escort - batman escort - bitlis escort - burdur escort - bursa escort - diyarbakır escort - edirne escort - erzurum escort - eskişehir escort - eskişehir escort - eskişehir escort - eskişehir escort - gaziantep escort - gebze escort - giresun escort - hatay escort - ısparta escort - karabük escort - kastamonu escort - kayseri escort - kilis escort - kocaeli escort - konya escort - kütahya escort - malatya escort - manisa escort - maraş escort - mardin escort - mersin escort - muğla escort - niğde escort - ordu escort - osmaniye escort - sakarya escort - samsun escort - siirt escort - sincan escort - tekirdağ escort - tokat escort - uşak escort - van escort - yalova escort - yozgat escort - urfa escort - zonguldak escort
ReplyDeleteadanaescort01.com - adiyamanescortxx.com - afyonarackiralama.net - aksarayescort.net - antalyaoyunpark.com - aydinescortkiz.com - balikesirescortlar.com - batmanescortlar.com - bitlisescortlar.com - burdurescortlar.com - bursamalaysias.com - diyarbakirambar.com - edirnedespor.com - erzurumyolkosusu.com - eskisehirescortlari.com - gaziantepekspres.org - gebzeescortkiz.com - giresunmaraton.com - hataykoleji.com - ispartakpss.com - karabukteknik.com - kastamonuajans.net - kayserivalisi.com - kilisescort.com - kocaeliescortlar.com - konyaescortlar.com - kutahyaizemlak.com - malatyadataksi.com - manisaescortlar.com - marasatasoyemlak.com - mardinfanatik.com - mersinmoda.com - muglaapart.net - nigdeyapi.com - orduescortt.com - osmaniyeyorum.com - sakaryanur.com - samsunescortlar.com - siirteyatirim.com - sincanoto.com - tekirdagescortlar.com - tokatforum.com - usakbasin.com - vanescortilan.com - yalovadaemlak.com - yozgattanal.com - sanliurfadayim.com - zonguldakescort.com
ReplyDeleteno deposit bonus forex 2021 - takipçi satın al - takipçi satın al - takipçi satın al - takipcialdim.com/tiktok-takipci-satin-al/ - instagram beğeni satın al - instagram beğeni satın al - google haritalara yer ekleme - btcturk - tiktok izlenme satın al - sms onay - youtube izlenme satın al - google haritalara yer ekleme - no deposit bonus forex 2021 - tiktok jeton hilesi - tiktok beğeni satın al - binance - takipçi satın al - uc satın al - finanspedia.com - sms onay - sms onay - tiktok takipçi satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - instagram beğeni satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - instagram beğeni satın al - tiktok takipçi satın al - tiktok beğeni satın al - twitter takipçi satın al - trend topic satın al - youtube abone satın al - instagram beğeni satın al - perde modelleri - instagram takipçi satın al - instagram takipçi satın al - cami avizesi - marsbahis
ReplyDeletekayseriescortu.com - alacam.org - xescortun.com
ReplyDeletetakipçi satın al
ReplyDeletetakipçi satın al
takipçi satın al
marsbahis
ReplyDeletebetboo
sultanbet
marsbahis
betboo
sultanbet
seo fiyatları
ReplyDeletesaç ekimi
dedektör
instagram takipçi satın al
ankara evden eve nakliyat
fantezi iç giyim
sosyal medya yönetimi
mobil ödeme bozdurma
kripto para nasıl alınır
instagram beğeni satın al
ReplyDeleteyurtdışı kargo
seo fiyatları
saç ekimi
dedektör
fantazi iç giyim
sosyal medya yönetimi
farmasi üyelik
mobil ödeme bozdurma
bitcoin nasıl alınır
ReplyDeletetiktok jeton hilesi
youtube abone satın al
gate io güvenilir mi
referans kimliği nedir
tiktok takipçi satın al
bitcoin nasıl alınır
mobil ödeme bozdurma
mobil ödeme bozdurma
smm panel
ReplyDeleteSmm Panel
is ilanlari
instagram takipçi satın al
hirdavatciburada.com
Https://www.beyazesyateknikservisi.com.tr/
SERVİS
tiktok jeton hilesi
tuzla bosch klima servisi
ReplyDeletependik samsung klima servisi
pendik mitsubishi klima servisi
tuzla beko klima servisi
çekmeköy lg klima servisi
çekmeköy alarko carrier klima servisi
ataşehir alarko carrier klima servisi
çekmeköy daikin klima servisi
ataşehir daikin klima servisi