#SQLSat41 <–That’s how you refer to it on TWITTER.

This past Saturday I attended SQL Saturday 41 in Atlanta, GA.  I began the day grumpy, tired and ashamed.  Ashamed because I was supposed to present and had to withdraw my presentation, tired because I’ve switched jobs 3 times in the last 8 months and that can be mentally draining as you might guess, and grumpy because of the former two adjectives.  But by the end of the day, I am happy to report I was inspired to go back out there into that great big bowl of stupid we all call life and FIX IT “Datachix Style” ( queue—“To Dream the Impossible Dream”). The day was just what I needed to recharge the battery on my metaphorical propeller hat.

I loved seeing familiar faces of those I’ve met in the last year and a half and further build on those relationships and also of course I got to meet some new folks.  Jen McCown of MidnightDBA was a hoot and she simply must come back to Atlanta so we can drink some 420’s together and annoy the general public with poorly executed, overly loud Monty Python quotes.  By the way Jen, I’m TOTALLY stealing your “I throw candy to people who pay attention” bit: GENIUS!  In all seriousness, I was very impressed with Jen’s presentation and floored that it was not her 100th.  She had the demeanor of an old pro at speaking.

I enjoyed David Rodriguez’ second presentation (he did three that day I believe, all on a laptop he had built the night before– HERCULES!).  David did an engaging presentation on what’s new in SQL Server 2008 R2.  This session focused on Reporting Services.  I really appreciated the content of this session, as I find that having a person show me with his computer what the new features are, what they look like, how you might use them, all the while fielding questions and ideas from a group of 30 people builds my trust of a product faster than 20 printed articles ever could.

Here is an EXTREMELY abbreviated list of what’s new with Reporting Services from my notes and recollections of that day.

  • Report Builder 3.0 – the next in the series, has more robust visual features.  Mapping from MS’s purchase of Dundas which beautifully materialize the new spatial datatype, and “spark lines”, which are adorable little “mini” bar charts which fit inside an individual cell in Excel.
  • Ability to publish Report Parts –You can now individually publish each piece of any report including, and this is the cool part—Datasets themselves. Report parts are published with their dataset as one entity.  So a map could be used by another report, with its data intact. 
  • A more modern look and feel to Report Manager.  Now it looks more like it came from the 21st century.

The session was lighthearted and one participant asked about the role (or lack thereof) of BIDS in Reporting Services R2, given all the attention that is being placed on Report Builder, Excel and SharePoint in the new release.  David explained that the directive Microsoft was working under was to make BI more accessible to end users, not just C levels, and this was the answer to that demand. (Then everyone in the room shared an “mmmmmkay” moment and we all moved on.)

And last, but most assuredly not least I want to speak to you all for just a minute about my co-blogger.  Audrey Hammonds presented The Art and Science of Data Modeling.  I greedily squirreled away a copy of this presentation and will keep it with me for the rest of my career.  Audrey took the crowd through a miniature data model from conversation with boss to conceptual model, logical model, physical model and briefly, even dimensional model.  She focused on practical, sound advice, imparting easy to remember rules and guidelines.  The room was packed and the folks attending were really engaged.  She did a great job. 

Ps—the most culturally relevant result of the event was that Audrey and I received quite a bit of flack for our lack of Twittering.  In response I am happy to report we are now both on Twitter (@Datachix1 Julie, @Datachix2 Audrey) and checking our Tweets/Tweeps/Twits regularly.

Julie’s Tiny Contribution to Audrey’s On Being a Datachick In a Developer’s World, or “This One Goes to 11”

part 1part 2

and now part 3:

11. Leave a “fidget” on your desk at all times.

If you are lucky (or unlucky depending on your need for companionship) enough to have your own office, there will often be a steady stream of visitors to this office.  Developers, managers, business analysts, project managers all find their way to your desk to talk about the data.  This is a good sign.  You must be doing something right, even if it’s just the cookies.

I placed an innocent looking “fidget” on my desk when I got my new office.  It was a little plastic disk with a fairly strong magnet inside.  Along with this disk came about 30 nuts (no I’m not talking about the people who came to visit me; I mean actual metal nuts that would go with a “bolt”).  The magnet made the nuts moldable and also hard to lose.  I thought it might help the occasional restless developer work through some process. 

Boy Howdy, was this thing popular.  I don’t think I ever saw anyone sit in the chair opposite me who didn’t absentmindedly reach for the fidget and start creating works of art.  One guy spent about a week trying to shape the nuts into a hand flipping me off (that’s just his thing; he doesn’t hate me).  When that novelty wore off, then he started trying for height records before the magnet failed.

Another developer became a big fan of the fidget as well and started a covert contest of “Fidget Art” with Magnetic Bird Man.  I can’t remember the title of his work, but I have officially declared his piece the winner:

I did not pack up this fidget in my box- o-belongings.  I felt the guys had grown too attached to it and to deprive them of their fidget would be cruel.  I’ll have to get a new one now, anyone have any suggestions?

Time To Rewrite Julie’s Bio Again…

I am no longer the first and only DBA at GA Communications. I’ve accepted a job where I will go back to my true data love, Business Intelligence. I start on Monday, and today was my last day at GA. When I crafted this exit timeline I envisioned three days of shopping, primping and lunches with friends so that I could hit the ground at the new job refreshed.

Oh contraire, mon Chickie. This weekend I annihilated my lower back playing soccer with a bunch of 8 year olds. I spent my last two days of work hobbling around like a little old lady. Instead of pedicures and haircuts the next three days will feature a lot of time spent laying on a heating pad.

When I wasn’t hobbling around or trying to stand upright, I was cross training my replacement. What a treat! Who gets to leave a job with a competent replacement anymore? Not someone who is already overworked and is just going to try and keep your stuff from exploding until you are actually replaced, but a fresh, qualified hire, whose existence is solely due to your impending absence and who is there with you for a while, no matter how briefly. I am so grateful to my former employer for doing this; I wish more companies were able to do this more often. All the best to my former colleagues–Thanks for everything.

Is It Us? or Is it WordPress? It’s WordPress, right?

What a drag.  I’ve just discovered that it’s not something we’re doing wrong.  Whenever Audrey or I copy our SQL into WordPress, the single quotes, or tics as I call them and the double dashes — for comments both get garbled. 

So be aware until we figure out how to outsmart WordPress.  I promise our sql works!

“It’s so funny to me that you think I wouldn’t know that.” 

Recipe for Random Rows

Audrey and I are planning on presenting again at SQL Saturday 41 in Atlanta. I’ve decided to present on the different ways to handle slowly changing dimensions. The term Slowly Changing Dimension is associated with datawarehousing, but the general idea can be applied to any dataset, not just a dimension in a datawarehouse. In a nutshell, you are comparing a data source to a data target based on a key, determining which rows already exist in the target, updating the target on those rows, and loading as new inserts the rows from the source which do not exist in the target.

Rather lengthy intro to a post about an only semi-related topic, so I’ll cut to the chase. I needed a large dataset for my presentation. I was shooting for at least a million rows. I decided to go with Sales Reps and Regions, so I now needed many names for my fictional employees. I raided all the baby name sites on the internet for lists of first names. Then I googled for lists of common surnames. I crammed those names into some quick and dirty (Audrey would call “rogue”) tables and mixed them all up using a cross join into a new table. But my end result displeased me. It did not look “random” like it was loaded from a transactional database. The rows were remaining stubbornly alphabetized.

Out to GOOGLE I go. Apparently many folks have encountered this exact same quandary. Here was the solution I found right away. Sort by NEWID(). NEWID creates a UniqueIdentifier datatype. You can use it explicity, or as I’m about to do, you can use it in an order by statement to sort randomly.

So as Audrey stated in her very first blog, this is one of those things that has been out there for a while, but darn it all I didn’t know how to do it until a need for it came up in my life. I used this recipe to create a table with over a million rows.

Again for the sake of the children, I include my sql here:

Use tempdb;
go

create table FirstNames( FirstName varchar(50));
go
create table LastNames( LastName varchar(50));
go

create table FullNames( FirstName varchar(50), LastName varchar(50), FullName varchar(101));
go

/*
–raid the internet for baby names and common surnames. There are plenty of choices. For my large set,
I used
about 1000 surnames and 1000 first names. Here I’ll do ten of each.
I literally copied the names off of pages and pasted them into text pad docs.
I then loaded them into staging tables with ssis, but you
could also use openrowset, or any other favorite method for cramming data into tables from text. */

insert into FirstNames(FirstName)
Values(‘Alan’),
(‘Betty’),
(‘Charles’),
(‘Deborah’),
(‘Edward’),
(‘Francine’),
(‘George’),
(‘Heather’),
(‘Ian’),
(‘John’)

insert into LastNames(LastName)
values(‘Addams’),
(‘Boddington’),
(‘Clarke’),
(‘Christopherson’),
(‘Daniels’),
(‘Ellington’),
(‘Jones’),
(‘Johnson’),
(‘Smith’),
(‘Tanner’)

–use a cross join (aka cartesian join) to produce one row for every first and last name combination
–possible between the two tables:

select fn.FirstName, ln.Lastname, fn.FirstName +’ ‘+ ln.Lastname as Fullname
from FirstNames fn
cross join LastNames ln

— boo very alphabetical! This does not look Random. They’re all gonna laugh at you Julie!

select fn.FirstName, ln.Lastname, fn.FirstName +’ ‘+ ln.Lastname, NEWID() as RandomnessMaker
from FirstNames fn
cross join LastNames ln
order by NEWID()
—showing you the value of the newid() for demo purposes, you don’t need to actually view or store it.

–The actual beautiful insert.
insert into FullNames (FirstName, LastName, FullName)
select fn.FirstName, ln.Lastname, fn.FirstName +’ ‘+ ln.Lastname
from FirstNames fn
cross join LastNames ln
order by NEWID()

—voila! Beautiful random names!
select * from FullNames

Why you wanna treat me this way? (Or Extra Special Super Secret Security in SS 2008 Reporting Services)

I agree with Audrey.  I too, heart Microsoft.  For me Microsoft’s SQL Server has been the cornerstone of a very rewarding career.  But in every relationship there are moments of great disappointment.  I will now share one of those moments in this thing me and SQL Server got going. 

I have a lot of SSIS experience, a bit of SSAS and practically no SSRS experience.    I have written many reports in “Access”, and have “heard” that of the three tiers in the BIDS stack SSRS is the easiest, but I wanted more than theory and hearsay.  Since no “real” projects came my way, I decided to do the genuine geek thing and just play with it myself.  The simple plan:  I would set up a reporting server on my laptop, a lovely ASUS Notebook U80V loaded with Windows Vista Home Edition.

I got started.  I did an install of SSRS. It’s just a series of clicks through the SQL Server Install wizard and I would be on my way to deploying reports on my very own server.  I took all the defaults.  Everything I looked up online said that would be ok to do.  Once I had my green success icon on the install I knew what to next (now that I had looked up what to do next).  I went to internet explorer, typed in http://myservername/reports to open up the Report Manager.  This is where I would be greeted with a GUI which would gracefully guide me through deploying some reports.  Everything in windows is intuitive, right?  Now that I knew how to open the report manager  , I would be able to figure out the rest easily.  (I’m pretty good with these computer thingies).  (Stop laughing at me). 

Here is what I was greeted with:

Awesome!  No errors! 

Now I just upload an RDL (report) file from the Contents Tab using the Upload File button and page.   …….   Um …… just , wait, it’s got to be here somewhere…… let me check the links —-Home.  (already home).  Um, My subscriptions ——(don’t have any).  Help——  (too much help). 

Well that’s weird.  I can’t “do” anything.  This doesn’t look like all those pictures of “functional” pages of Report Manager I’ve seen online:

Looking at it, it seems like a permissions problem but ….it can’t be a permission problem because this is my personal, stand alone laptop.  There is only me as the user and I’m an administrator.  The server is installed under my user (an administrator btw).  Every user, every service, every application I touch should have absolute God rights on this machine.  Can’t be permissions.

I took a deep breath.  I knew what I was in for now.  Dedicating a portion of my life to fishing online for the (probably annoying) solution.  From topics on MSDN/BOL  I started with “report manager” .   I was optimistic that the answer was close.  And it was about halfway down the page I saw this note:

 Note:
If you are using Windows Vista or Windows Server 2008, you must configure the report server for local administration before you can use Report Manager to manage a local report server instance. For instructions on how to configure the server, see How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008.

 

Hmm.  I was indeed using Windows Vista.  But wasn’t I already configured for local administration?   After all, this is my personal, stand alone laptop.  There is only me as the user and I’m an administrator.  The server is installed under my user (an administrator btw).  Every user, every service, every application I touch should have absolute God rights on this machine.  Can’t be permissions.

What the heck, I’m only ten minutes in at this point.  I followed the link.  

There was the answer.  Right at the top. 

——————————————————————————

Deploying Reporting Services on Windows Vista and Windows Server 2008 requires additional configuration steps if you want to administer a report server instance locally. Both Windows Vista and Windows Server 2008 limit the overuse of elevated permissions by removing administrator permissions when accessing applications. Because the operating system removes permissions, members of the local Administrators group run most applications as if they using the Standard User account.

While this practice improves the overall security of your system, it prevents you from using the predefined, built-in role assignments that Reporting Services creates for local administrators. However, with additional configuration on your part, you can effectively manage report server content and operations using standard user permissions if you do the following:

———————————————————————————–

Folks,  I have to say that the verbiage above is so unbelievable to me as to not need any embellishment for comic effect.  It’s like the SNL skit when Tina Fey played Sarah Palin in the Katie Couric interview and all she did was read from the transcript of the actual interview.

My personal favorite tidbits from the above:

  • “limit the overuse of elevated permissions”  you mean like permissions to execute all designed functionality?  Without a warning, flag, or tool tip in sight?
  • “requires additional configuration steps IF you want to administer report server instance locally”.  Maybe just assume that if a user installs it then that user may want to use it, “locally” being a popular choice for “how”.

Anyway I got my server working, using the below instructions.  I post them here now from MSDN/BOL for the sake of the children everywhere:

————————–

  To configure local report server administration on Windows Vista and Windows Server 2008

  1. Open a browser window with Run as administrator permissions. From the Start menu, click All Programs, right-click Internet Explorer, and select Run as administrator.
  2. Click Allow to continue.
  3. In the URL address, enter the Report Manager URL. For instructions, see How to: Start Report Manager.
  4. Click Tools.
  5. Click Internet Options.
  6. Click Security.
  7. Click Trusted Sites.
  8. Click Sites.
  9. Add http://<your-server-name>.
  10. Clear the check box Require server certification (https:) for all sites in this zone if you are not using HTTPS for the default site.
  11. Click Add.
  12.  Click OK.
  13.  In Report Manager, on the Home page, click Properties.
  14. Click New Role Assignment.
  15. Type your Windows user account in this format: <domain>\<user>.
  16. Select Content Manager.
  17. Click OK.
  18.  Click Site Settings in the upper corner of the Home page.
  19. Click Configure Site-wide security.
  20. Click New Role Assignment.
  21. Type your Windows user account in this format: <domain>\<user>
  22. Select System Administrator.
  23. Click OK.
  24. Close Report Manager.
  25. Re-open Report Manager in Internet Explorer, without using Run as administrator.

  ————————— 

So, even though the server was my personal, stand alone laptop, even though there was only me as the user and I was an administrator, even though the server was installed under my user (an administrator btw) and even though every user, every service, every application I touched should have had absolute God rights on the machine, it was the Permissions.

But I’m over it now.  Things are good again between me and SQL Server.  If anything, I think it’s made our relationship stronger. 😉

I’m Julie. I’m a Datachix.

The Reluctant Production DBA—or I’m a Big Girl Now.

I’m a Big Girl now.  Not in the Christina-Hendricks- from- Mad- Men sort of way (well maybe in that way too, but I digress), but in the Not –afraid- of- Servers- Networking- and –Maintenance-Tasks sort of way.

You see, I’ve been doing ETLS and programming for databases for ten years now, but I’ve always been much more comfortable inside the query tools rather than outside of them.  Outside is the scary realm of consoles, networking, users and permissions.  I didn’t completely avoid them.  I knew enough to do my job, but I must admit I was EXTREMELY grateful that the disks, logs, backups and maintenance tasks were not my responsibility.  Although this made me happy in the sense of “avoiding that which makes us uncomfortable” it did leave me with a bit of a Pinocchio complex.   I was convinced, and rightly so, that I would never flourish as a DBA until I sucked up my fears and did some learning.

So five months ago I took a leap into a job where I would be the first and only DBA.   Google, SQLServerCentral, and MSDN all became my lifelines as I forced myself through the transformation from  a “Development DBA”  to a “Development DBA Who Doesn’t Suck at Production Work Either”.

And I’ve learned some awesome stuff.  Installations?  Bring it.  Disk Configurations? I’m not scared.  Maintenance and Backups?  Sure.  Reindexing and Updating Statistics?  Whatever it takes.  Transaction Logs?  They’re my bitches.  SANs?  I’ll never be without one again.  Virtualizing a SQL Server?  I’ll get back to you.   Niedermeyer?  He’s a dead man.  (sorry that last one is not really relevant, but I was rolling).

So here we go.  I’ve got more to blog about now than I ever did.  I’m a big girl now.  Where’s my lollipop?