Debugging SSIS Variables—and a Happy Announcement from the Datachix.

Audrey and I have decided to blog regularly and alternately on Tuesdays. We’ve each had good runs of things and had months where we posted a lot, but we’ve never had great consistency. So now we’ve committed to a schedule.

From the Book of Bloggering Chapter 23, Verse 9: The Datachix shall blog weekly. The day of the week on which they shall post shall be Tuesdays. Mondays will not be the day the postings shall occur, unless it is only to load and schedule a post for the following day–Tuesday. Neither shall Wednesdays be the day of the posting, unless there be a time zone situation where the reader is residing in Wednesday time whereas the Datachix (the said aforementioned poster/bloggers) are residing in Tuesday, and therefore posting their blog. Thursdays are right out, as are Fridays. Don’t even mention Saturdays.

Continue reading

Adventures in MDX – Tuples

Personal Note: I wrote the bulk of this post last night. Before I read Chris Webb’s blog post about the future of SSAS and MDX. I almost didn’t post this after reading that. But you know what? Screw you guys, I’m learning MDX anyway. I hate the idea that Microsoft would potentially remove aspects of the BI stack because the learning curve is too high. If I can’t keep up, then put me out of a job. Don’t dumb-down the functionality. That being said, I think there’s a lot of value in understanding the language that accesses any data store. It forces you to think about the internal structure of what you’re working with, and therefore, I see value in learning MDX either way. (But really… I was so disheartened after reading about some of the coming changes. You MS peeps had better know what you’re doing!) On to the original post:

My favorite movie is My Fair Lady. I love Audrey Hepburn. I love the Pygmalion story. Quick aside: I used to tell people that my parents named me after her. They didn’t, and the true story is convoluted. My mom loved the name Audrey Dalton (my middle name is Dalton), which was the name of a movie star. My great-great grandmother was Dalton Harris, and she thought it would be cool to name me after her and the actress. Then she met my dad. His mom’s name was Audrey. (She went by her middle name, Geraldine, which I never understood… but I digress.) Anyway, when I was born, she told everyone that I was named for my paternal grandmother and my maternal great-great grandmother. When, secretly, I was just named after an actress with a name she liked. I’m glad she told me this. (Audrey Dalton was a total hottie.)

<–  Audrey Dalton, HOTTIE (courtesy of Ballybane Enterprise Centre  http://www.bbec.ie/blog/?p=708)

This week, I’ve decided to start digging into MDX. There are three reasons for this:

1) It’s PASS Summit week. While I’m not there, I’m trying to get into the spirit of things by learning something new.
2) I’m just not good at MDX. There is no excuse for this.
3) I’m gearing up for my MCITP exam in Business Intelligence 2008. I hear rumor that there are MDX questions.

So anyway, I feel a lot like Eliza Doolittle this week. If you’re not familiar with the story, she is the subject of a bet between Henry Higgins and Colonel Pickering. They bet that Prof. Higgins can’t pass her off as a Lady (with a capital “L”) in a year. She’s just a lowly flower girl, complete with cockney accent. In order to refine her, he has to teach her how to speak again. It’s her own language, but she has to learn how to use it in a totally unfamiliar way. Instead of saying, “In ‘artford, ‘ereford, and ‘ampshire, ‘urricanes ‘ardly h-ever ‘appen”, she has to learn to say, “In Hartford, Hereford, and Hampshire, hurricanes hardly ever happen”. (Swear to cheesus, I haven’t hit IMDB yet… I really love this movie) Same words, same meaning, totally different accent.

Rather than a flower girl trying to sound like a Lady, I’m a T-SQL girl trying to sound like an MDX Lady. Or something like that. You know what I mean. 

   <– T-SQL Flower Girl

To get started, I picked up Microsoft SQL Server 2008 MDX Step by Step (by Brian C. Smith, C. Ryan Clay, and Hitachi Consulting). I’m starting with the basics, so right now I’m in “SELECT * FROM” territory. Or, “SELECT FROM ” territory, since we’re talking MDX.

Transitioning from T-SQL to MDX is not easy. The syntax is just familiar enough to me to trip me up. I keep catching myself trying to equate a query against a cube to a query against a relational data store. It’s not the same, and it has been tough for me to wrap my head around it. But, “I washed my face and ‘ands before I come, I did”, so I think I’m ready to get started.

So far, I’ve learned about one important concept: Tuples. The point of this blog post is to force myself to regurgitate what I’ve learned, because to paraphrase something Jen McCown (Blog | Twitter) said the other day, you don’t really know something until you’ve taught it. True that. Please keep reading, but also read a book by an expert. I’ve been happy with the Step by Step book so far.

Wait… one more silly analogy. Writing T-SQL is a bit like cutting out paper dolls. It can be complex, but it’s just two dimensional space. Writing MDX is like chiseling a hole into a big rock at a specific point. It’s n-dimensional space. While a bit goofy, this visualization has really helped me draw a line between T-SQL and MDX.

Tuples (as Translated by Me)

A tuple is basically the identifying characteristics of a cell inside a cube. Really, a data point inside a cube. Say I have three dimensions, Actor, Movie, and Year. Say I have a Measure Group that includes Budget Amount. Say I wanted to find the cell, or data point, identifying the budget for the movie My Fair Lady starring Audrey Hepburn that came out in 1964. I’d look at the attribute-hierarchies Audrey Hepburn, My Fair Lady, and 1964. (Hey, I didn’t say it was a well designed cube!) Those identifying characteristics of the cell are the tuple, which would be formatted something like this in MDX;

(
[Actor].[Audrey Hepburn]
,[Movie].[My Fair Lady]
,[Year].[1964]
,[Measures].[Budget Amount]
)

Another way to look at it is in terms of math. I always swore that geometry and algebra were pointless in high school. Well, Mr. Smith, you were right. I’m about to talk axes. (axises? axii?) Each of my attribue-hierarchies and my measure group make up an axis within my cube. Don’t even try to visualize a 4-dimensional cube. I did, and it made my head hurt when I ran out of 3-dimensional space. Let’s label each axis:

[Actor].[Audrey Hepburn] = a1
[Movie].[My Fair Lady] = a2
[Year].[1964] = a3
[Measures].[Budget Amount] = a4

Now, if I want to identify the point that is the intersection, my notation would look something like this: (a1, a2, a3, a4). I also imagine four lines (in 2-dimensional space, all intersecing one another at one point. That point is my tuple.

The MDX syntax for my query looks like this:

SELECT
FROM [Pretend Movie Cube]
WHERE
(
[Actor].[Audrey Hepburn]
,[Movie].[My Fair Lady]
,[Year].[1964]
,[Measures].[Budget Amount]
);

It would return one value: $17,000,000

Some Key Points:

1) Every attribute-hierarchy gets an axis, NOT every Dimension. So, if I had two attribute-hierarchies within my Actor dimension, Audrey Hepburn and Rex Harrison, they all have an axis. I could actually reference the same Dimension multiple times like so:

(
[Actor].[Audrey Hepburn]
,[Actor].[Rex Harrison] <–Henry Higgins!
,[Movie].[My Fair Lady]
,[Year].[1964]
,[Measures].[Budget Amount]
);

2) Measures each get an axis. They are treated differently at design time, but for the purposes of seeking out that one cell or set of cells, it’s treated just the same as an attribute hierarchy.

3) Analysis Services allows you to be lazy. You can define what’s called a Partial Tuple, leaving out some axis references. But… it’s going to try to figure out where on that missing axis you were headed. It’s going to go in this order:
        1 – Default member (defined at design time)
        2 – (All) member –remember that Measures don’t have an (All) member
        3 – First member

Am I getting this?  Have I missed the boat?  Close, but no cigar?  Any other cliche suggesting I don’t know what I’m talking about?



T-SQL Tuesday: Why are DBA Skills Necessary? – A Datachix Perspective

I have a few confessions to make: I encourage my son to watch Phineas and Ferb because I secretly enjoy it. Jersey Shore is strangely entertaining. I claim that I don’t sing, but if you ever see me alone in my car on I-85, I’m probably wailing away. I read all four Twilight books. I have a crush on Nathan Fillion.  And… lean closer… I’m going to whisper this one: I’m not a DBA.

My resume would say otherwise. I can point to multiple job titles throughout my career that have the words “Database Administrator” in there somewhere. Guess what? I’ve never been a DBA, and if pressed, I’d call myself a Developer. Now, I’ve made peace with this. I love what I do, and I’m pretty good at it, even if it is difficult to explain to non-technical people the difference between being a DBA and a Developer. I think the confusion comes from the fact that there are so many job titles, and they’re all kind of vague. People assume that if you work with data you’re a DBA. They assume that we all have the same skill set. The truth is so much more complicated.

See, there is a LOT of specialization out there. I’m currently a BI Consultant. Before that, I was a Data Architect, doing BI work. Before that, I was a “DBA”, developing relational databases. But, at the end of the day, I’ve got a specialization. I model and develop databases, and I move data around. Over the past few years, I’ve started traversing the BI stack, but at the end of the day, I’m really just a Database Developer. The simple truth is that there are so many angles and disciplines within the data universe, you have to be specialized. To borrow a silly cliché, anything else would be drinking from the fire hose. You’d get soaking wet, and you’d probably still be a little thirsty.

So, to the point. Paul Randal raised some great questions for this T-SQL Tuesday, “Why are DBA Skills Necessary?” I want to focus on a sub-topic he suggested, “Should there be cross-over between developer skills and DBA skills?” On the surface, the answer is an obvious, “Yes!” It was for me too, until I actually sat back and thought about it. My new answer is, “Yes, to a point.” See, asking me, a non-DBA, to attempt to be a DBA is like asking a psychiatrist to perform heart surgery. Yes, they both went to medical school, and yes, the psychiatrist could probably hold up his end of the conversation with a heart surgeon, but really, would you want him to cut on you? Probably not.

Here is my perspective: I need to be aware of what’s going on within the Database Administration universe. It’s why I attend both the Atlanta MDF, which is largely DBA-focused, and the BI User’s Group. I read blogs, books, and articles related to the entire data universe, not just my specialization. In fact, there are many, many things DBA’s do that I wish I knew better. I need to have a good vocabulary in the discipline, and I need to be able to carry on intelligent conversations with DBA’s. Does this mean that you should trust me to be your DBA? Probably not. By the same token, I also see application development in the same way. I should be able to discuss application development intelligently, but I don’t really know how to do it properly. Don’t get me wrong… I’m competitive enough that I want to be an expert at everything, but I’m also realistic to know that I’m never going to.

See, to lump us all into one “DBA” bucket is to diminish the amazing job that so many DBA’s do every day. To pretend that I could step into a server room and hold my own unassisted is not only arrogant and delusional, it is dangerous.

Which brings me to the next point…

“At what point does a SQL Server installation need a DBA to look after it?” Immediately! Friends, your DBA is not just the chick who does backups and restores. You know how developers complain about not being brought into the development cycle early enough? Like when requirements are being created? Well, how does the DBA feel? We pretend that we understand the DBA discipline, and muck around in our development environments, making wild assumptions based on test data sets. Then, just before going to production, we dump an entire environment on the DBA and ask her to make our hot mess of a product perform well. Even worse, we act like that annoying taxi passenger telling the driver to take the Connector instead of the Perimeter because its 5:17 on a Friday and we think we’ve got Atlanta traffic down to a science. Like I know more about how to get around the city than the guy who does it for a living? Right.

Good DBA’s, and I’ve been lucky enough to work with quite a few over the years, know as much or more about the business being served by the database as the rest of the development team. That’s a key point… The DBA is part of the development team. Business requirements translate into administrative requirements, so get that DBA in the room early and often! And if you’re lucky, by hanging out with the DBA more, you’ll learn a thing or two about her side of the world.