Spoon feeding SSAS…Part 186 and the first part in my “Parenting
Your Adolescent SQL Server” series.
A Case Study…Deleting Dimension Relationships…Information
At some point in your career you will discover that, even
though your SQL Server is usually well behaved, it can indeed be provoked to
the point your years of attention will be rewarded with outright rebellion.
Case in point; a normally happy and productive SSAS cube is…due to the nature
of first time parenting and the “if one is good, more must be better” concept…configured
with a multitude of extraneous dimension-to-dimension relationships…all keys
should be linked. On the surface this may look like a good thing to do. Why
not? Common keys exist in all of the dimensions so let’s link them up. Oh no
Mr. Bill! My previously beautiful star schema now looks like a spider web;
lines everywhere! Given that it is hard for me, a human, to view and
understand, and presuming my cognitive skills exceed those of the SSAS binaries,
it must be impossibly confusing for our adolescent cube…lets’ call him “Fred”.
Fred’s usually simple life and clear direction now has more left turns than a
traffic circle. Sorry Fred.
Realizing our mistake and wanting to instill the most
virtuous of principles in Fred, we choose to undo the damage and remove the thirty
undesirable relationships. Easy enough. Right click and “Delete”. All gone.
Reprocess the cube and all is well…or not??? Fred is not happy…too much to
comprehend at one time…rebellion ensues…cryptic messages appear…”I’ve fallen
and I can’t get up” or, more precisely, “Deserialization Failed…”. Uh oh. I
don’t remember seeing this in my parenting manual and Fred is now comatose. We
exercise the best and possibly the only option…Fred has a “reset” button in the
form of restoring a backup…reverting to the last known-good state. Most
convenient. Respiration resumes, color returns. But now what? Let’s check the
Lesson 1 to 1000. SQL Server likes to be “spoon
Lesson 1001 to 2000. SQL Server likes “simple”.
In the words of the very unconventional Buckminster Fuller… (the
guy who, in the 1930’s, understood wind resistance and demonstrated that the
cars of the day could go faster backwards because of the more streamlined
profile in that direction)…”Less is More”. Good words and probably the most
important words for a SQL Server DBA/Developer/Architect to hold dear to their
heart whether moving forward or backwards! (If you haven’t met “Bucky”, check
him out. Most interesting guy. Think Einstein for humans.)
In conventional SSAS speak, it appears we have overloaded
Analysis Services with too many changes to be applied to the underlying xmla (xml
for Analysis Services) at one time, and have corrupted the file resulting in
the deserialization error. The clue is the word “deserialization” which always
refers to a string parsing operation. Xml is just a string with tags and
formatting. If you happen to be an SSAS jedi it may be possible to find the
issue in the xmla and fix it. The more likely outcome is things will get worse.
The best course of action is to revert to the last known-good backup then take
baby steps forward…remembering our lessons and the single most important
trouble-shooting practice of “one-change-at-a-time”.
Bucky to the rescue. Goal- “less”. Best practice- “spoon
fed” (one change at a time). Simply put, let’s delete one or two
relationships…from one dimension…and reprocess the cube after each deletion. If
that passes muster we will proceed to the next unwanted relationship,
completing one dimension at a time. As forward progress is made let’s protect
our investment…script the cube as each dimension or other reasonable milestone
Disaster averted; Fred recovers nicely after each reprocess.
Lesson learned. I can’t help but recall a line in a song which seems totally
applicable…”the road to hell is paved with good intentions”. Today that means
“if it isn’t necessary…don’t do it”.
On that note- Lesson Learned-
Simple. Relationships in an SSAS data source view are a
must. Simplicity is also a must. One relationship between dimension and fact is
the first choice. Complicate only as required to support bridge, reference,
degenerate, or outrigger dimensions.
Welcome to the show and good luck.