Analysis Services Writeback – Working with Weight Expressions
While writeback has been around for a while, it didn't really get easy to do until Excel 2010 was released because wrapping a UI around it was cumbersome. Now that we have a simple writeback UI via the What-If analysis dialogs in Excel, it's pry worth poking around at how to get things to allocate the way you want.
Here's our starting point in the Adventure Works cube (note, I commented out all the scope statements in the MDX script that distribute quarterly quotas/targets to months - in AW, quotas only exist in the data warehouse at the calendar quarter level):
Let's start with something simple - Equal Allocation:
Getting a useful FTP error message out of SSIS
The plan: Upload a zip file for a bunch of different clients to various user-specified ftp servers - looks something like this:
A quick note on what that "Update FTP Connection" task is doing - it's modifying the properties of the "FTP" connection manager to the appropriate Server/Username/Password for this particular client:
1 2 3 4 5 6 7 8 9 10 | public void Main() { ConnectionManager ftp = Dts.Connections["FTP"]; ftp.Properties["ServerName"].SetValue(ftp, (string)Dts.Variables["FTPServer"].Value); ftp.Properties["ServerUserName"].SetValue(ftp, (string)Dts.Variables["FTPUser"].Value); ftp.Properties["ServerPassword"].SetValue(ftp, (string)Dts.Variables["User::FTPPassword"].Value); Dts.TaskResult = (int)ScriptResults.Success; } |
The problem: Anyone who has tried to upload multiple files to multiple FTP sites in an SSIS package very quickly ran in to FTP errors (that's what happens when you let users tell you what their FTP url and authentication is). You can handle these errors and log them via the standard OnError handler dumping the ErrorCode, ErrorDescription, and usually SourceName out to a flat file or table.
Excel 2007 Hangs When Trying to Edit an OLAP Cube Filter
Ok, it doesn't hang every time you try to edit an OLAP cube filter, but sometimes - it appears to. In reality, I've never seen it permanently hang - just kind of go away for a while. Here's the basic symptom that the business will report to you:
"Excel freezes (or hangs) when I try to change this filter"
Most of us have seen this at one point or another and shrugged it off as a busy time or processing is going on or there are cats clogging up the tubes, etc. Tonight, I finally decided to figure out what's causing it.
Locating Rampant Database Growth
Every now and then you get a helpful alert from one of your database servers about disk space being low (you're monitoring that, right?), or a bunch of autogrowth alerts (you're monitoring that too, right?) - but what happens when you get these for a database that you don't expect growth in? Further, what happens when that database is growing rampantly (say like 1GB/hr in my case) and it's a canned database from a 3rd party product? This time it was the database that SolarWinds uses for collection - and apparently it was collecting a lot of something that it wasn't collecting before.
- you send out an email asking the end users of the system (IT in this case) if anything changed
- adjust so said system stops trying to fill up your drives
Unfortunately, there's often quite a few possibilities for "why" a database is growing - and when it's a canned product, you don't always have the best understanding of why it does what it does when it does it. As a Database Admin, you can help diagnose the problem by letting everyone know what exactly is growing:
Script sp_configure Output To Migrate Settings Between SQL Servers
Sometimes when setting up a new server, all you want is for it to work and be configured exactly like the old server. In some cases, the new server is almost identical in every way already. If you've worked much with SQL Server, you know very well that there are a billion switches and knobs and every time you do this, you forget at least one. I had a similar situation recently and figured I'd be lazy about it and just turned the output of sp_configure in to a script I could execute on the new server:
Script RESTORE DATABASE … WITH MOVE Stub
I think most people setup the drives on their Development servers to match their Production servers - this is so that restores go smoothly and files go where they're supposed to, things match up nicely, etc. Unfortunately, when you create a full backup all the backup file contains is the logical name of all the files - no physical paths. This means that if the database doesn't exist on the destination server yet, the engine has absolutely no idea where to put the files or what to name them.
The following script can be executed in the context of the source (original) database that was backed up to script out the MOVE statements if your plan is to put the files in the same place on the destination server.
VersionOne Nag: Reminding Us To Burn Our Points
You'll forget - we all do. After a hard day of work, the last thing that pops in to your head is "hey, I should go update this story in VersionOne before I go home." Oh no, the only thing you're thinking about is how bad traffic will be, what you're having for dinner, your trip to Seattle, which bar has the best drink specials tonight, etc. So accepting that we forget to burn points now and then, why don't we just make a script to remind us? While you're at it, might as well add a reminder for when you accidentally close a story without setting the ToDo points to zero.
Luckily, since you obviously use VersionOne (because it's the gold standard), there's an API for that:
Clone Analysis Services Partitions with PowerShell
Most of us with large Analysis Services cubes partition our cubes by month or year or some other time-based slice and we have all, at one point or another, developed some way to create partitions for new months on-demand. Often, the solution to this seems to be a C# console application or SSIS package using AMO to create a new partition based off an existing partition. The problem I see with this is that maintaining it requires opening up the project or package, making changes, re-compiling, deploying, testing, deploying to production, verifying, etc. It also requires that whoever is going to maintain it is comfortable with C#.
To simplify the maintenance and get rid of the "black box" factor that utility apps like this tend to have, I put together a PowerShell script to do the same thing and a stored procedure to call the script. Really, it doesn't matter what you use as you're most likely using an almost identical chunk of code to get your new partition created - my argument is that using PowerShell instead of C# or SSIS reduces the cost of maintenance, improves readability, and facilitates better understanding throughout your team.
Trending SQL Server Agent Job Duration by Hour
Earlier today I noticed a SQL Server Agent job taking a little longer than usual (or what I thought was longer than usual). Let's face it, we're not staring at the Job Activity monitor all day, so unless you've written a report to monitor job run times - on occasion you ask yourself "is that a normal run time for this thing?" The job I was curious about happened to be a job that runs throughout the day and should only have real work to do once or twice an hour - and it should run roughly the same amount of time on any given business day for a given hour (i.e. at 1:00 PM on any given business day, this thing should do the same amount of work).
So I came up with the following query to PIVOT the run duration on the hour the job executed:
Wouldn’t it be fun if Cubes could talk?
I didn't say "wouldn't it be useful" because after putting a test together, asking a cube questions with no context tends to return answers that it probably shouldn't have returned. In BI, it is incredibly important to understand what exactly it is you're asking for - if we just say we want "sales" and return an answer, nobody really knows what we meant by "sales." Sure, in various circles, "sales" means the same thing - but once you start talking to different areas, departments, etc - the meaning of the word starts to shift.
But I digress - asking cubes questions is still pretty fun and some of the random things it returns when you point it at your own cubes can be flat out hilarious.
Here's a few questions thrown at the Adventure Works cube in the Adventure Works DW 2008 Analysis Services database
Social Networks
Recent Posts
- Automating PowerPivot Data Refresh in Sharepoint 2010
- Slowly Changing Dimensions with MD5 Hashes in SSIS
- SSAS Cache Isn’t Making Cents
- Uncivil UNIONs
- Hiding SSRS Schedule Jobs In SSMS
Bloggers of Note
Family
People who take my money
Categories
- Golf (2)
- How-to Guides (6)
- Scripts & Code (55)
- Thoughts (10)
Archives
- March 2012 (1)
- February 2012 (3)
- January 2012 (1)
- November 2011 (1)
- October 2011 (3)
- July 2011 (2)
- June 2011 (1)
- April 2011 (7)
- November 2010 (2)
- October 2010 (1)
- April 2010 (1)
- March 2010 (1)
- February 2010 (3)
- January 2010 (1)
- December 2009 (1)
- November 2009 (1)
- October 2009 (3)
- September 2009 (2)
- May 2009 (4)
- April 2009 (1)
- March 2009 (8)
- February 2009 (1)
- November 2008 (4)
- October 2008 (1)
- September 2008 (4)
- July 2008 (2)
- June 2008 (1)
- April 2008 (1)
- October 2007 (1)