Tuesday, March 3, 2015

Simple-Talk Article

In December, I was asked to write an article(s) on Database Refactoring. It started as 2 topics, but then consolidated into one. I really wanted to talk about the database design issues I see with a development department. Please give some time to reading the article and comment. First, I would like to thank for Simple-Talk team for the editing process which was first class and very, very useful.

https://www.simple-talk.com/sql/t-sql-programming/defusing-database-time-bombs-avoiding-the-need-to-refactor-databases/

The comments are really for the new developer who needs some guidance on designing databases. I wish everybody had the MIS class I took at LSU for Entity-Relationship diagrams. Back then(86-91), the ER Diagram and Data Diagram were 2 separate pieces to the database. You looked at the conceptual design before the physical implementation.

I am started to wonder if Agile/Scrum methods are starting to water down the design sessions for application development. Or maybe not :)

Tuesday, January 20, 2015

SQLSaturday #362 – Austin, TX – Let’s Get Weird

I used to spend the 4th of July weekend (or week) in Austin with some buddy’s when we were in our 20s. There was so much to do and enjoy while there it is exciting to go back to this great city. Not only that, but there are some really good SQL Server experts and community organizers in that city – John Sterrett, Jim Murphy, SQLServerIO (Wes Brown – hope he is there), AJ Mendo and more. Catching up with these people will be refreshing.

The event is on Jan31st at Wingate by Wyndham Round Rock Hotel and Conference Center 1209 N IH 35, Round Rock, TX 78664

 

THIS YEAR THERE ARE 2 OFFICIAL SQL SATURDAY PRECON'S ON FRIDAY JANUARY 30th, 2015. TICKETS WILL SELL OUT QUICKLY SO MAKE SURE YOU REGISTER TO SPEND THE DAY WITH SOME OF SQL SERVER'S GREATS!

BECOME AN ENTERPRISE DBA WITH SEAN AND JEN MCCOWN     Eventbrite - Become an Enterprise DBA with Sean and Jen McCown

MURDER THEY WROTE WITH JASON BRIMHALL AND WAYNE SHEFFIELD   Eventbrite - Murder They Wrote

Register

 

Here is the emailed schedule:

Track

Starts

Session Title

Speaker

Business Intelligence

08:30 AM

Registrations

SQLSaturday 362

Business Intelligence

09:00 AM

Capture Change and Apply it with CDC & SSIS

Steve Wake

Business Intelligence

10:15 AM

Getting started with Tabular Analysis Services

Thomas LeBlanc

Business Intelligence

11:30 AM

15 Quick Tips for SSIS Performance

Tim Mitchell

Business Intelligence

12:30 PM

Learn How To Build A Golden Record for Any Subject Over Lunch!

Gene Webb

Business Intelligence

02:15 PM

Writing Your First BimlScript

David Stein

Business Intelligence

03:30 PM

Change Data Capture & Change Tracking Deep Dive

Kevin Hazzard

Business Intelligence

04:00 PM

XML & SQL: Best Frenemies Forever

Richard Heim

Business Intelligence

05:15 PM

Raffle

SQLSaturday 362

DBA, Automation

09:00 AM

DBA Toolbox: Surviving A Disaster

AJ Mendo

DBA, Automation

10:15 AM

SQL Server: Monitoring disk usage

Daniel Janik

DBA, Automation

11:30 AM

Beginning Automation with Powershell

Amy Herold

DBA, Automation

12:30 PM

Embarcadero - Lunch Sponsorship Session

John Sterrett

DBA, Automation

02:15 PM

Database Release Management: Tips to help organize it

TJay Belt

DBA, Automation

03:30 PM

SQL Server Internals

Naomi Williams

DBA, Automation

04:00 PM

SQL Server Bingo – Install, Migration & Config

Mindy Curnutt

DBA, Automation

05:15 PM

Raffle

SQLSaturday 362

DBA, Cloud

09:00 AM

SQL Server Foreign Keys – De-mystifying the Rest of the Story

Mike Byrd

DBA, Cloud

10:15 AM

Getting Started with SQL Server in the Cloud with Microsoft Azure

Anil Mahadev

DBA, Cloud

11:30 AM

SQL 2012 Extended Events

Jason Brimhall

DBA, Cloud

12:30 PM

Amazon Web Service - Lunch Sponsorship Session

John Sterrett

DBA, Cloud

02:15 PM

No DR Site?! No problem!! Enhance your SQL Server HA/DR capabilities using Windows Azure

Kal Yella

DBA, Cloud

03:30 PM

SQL Server Statistics – What Are The Chances?

Lori Edwards

DBA, Cloud

04:00 PM

Introduction to Wait Types and Response Time Analysis

Janis Griffin

DBA, Cloud

05:15 PM

Raffle

SQLSaturday 362

DBA, Security

09:00 AM

Turbocharge Your Career With a Learning Plan

Andy W

DBA, Security

10:15 AM

CIS Benchmarks – A Guide to SQL Server Security

Nancy Hidy Wilson

DBA, Security

11:30 AM

SQL Watchdog - find out instantly when SQL change

Michael Bourgon

DBA, Security

12:30 PM

Cisco - Lunch Sponsorship Session

John Sterrett

DBA, Security

02:15 PM

SQL Security Best Practices & Shrinking Your Attack Surface

Matthew Brimer

DBA, Security

03:30 PM

SQL Injection

Kevin Boles

DBA, Security

04:00 PM

Securing Your SQL Environment

Tim Radney

DBA, Security

05:15 PM

Raffle

SQLSaturday 362

Development

08:30 AM

Registrations

SQLSaturday 362

Development

09:00 AM

Parameter Sniffing the Good and the Bad

Lance Tidwell

Development

10:15 AM

5 TSQL Commands I've Been Missing

Jason Carter

Development

11:30 AM

The Real Value of Name-Value Pairs: Using PIVOT and UNPIVOT

Kevin Wilkie

Development

12:30 PM

Women in Technology Round Table Discussion

Rie Irish

Development

02:15 PM

Windowing Functions

Timothy Costello

Development

03:30 PM

Turbo Boost Performance: In Memory Tables index optimizations

Konstantin Melamud

Development

04:00 PM

T-SQL Throwdown

Hakim Ali

Development

05:15 PM

Raffle

SQLSaturday 362

Thunderdome

09:00 AM

Introduction to Powershell cmdlets for DBAs

Jennifer McCown

Thunderdome

10:15 AM

Query Store - A New SQL Query Tuning Feature

Conor Cunningham

Thunderdome

11:30 AM

Increase your SSIS productivity with Biml

Reeves Smith

Thunderdome

12:30 PM

Microsoft - Sponsor Lunch Session

John Sterrett

Thunderdome

02:15 PM

SQL Server 2014 In Memory technologies - In Memory OLTP (aka Hekaton) & ColumnStore

Reinaldo Kibel

Thunderdome

03:30 PM

Table Vars & Temp Tables - What you NEED to Know!

Wayne Sheffield

Thunderdome

04:00 PM

AlwaysOn Live Deployment

Ryan Adams

Thunderdome

05:15 PM

Raffle

SQLSaturday 362

Thursday, December 18, 2014

PASS – Connect, Share and Learn

The Professional Association of SQL Server (PASS) has a slogan for what they do – CONNECT, SHARE and LEARN. So, what does this mean to me and the SQL Server professionals I have meet in this community (#SQLFamily)?

 

image

First, I connected. Where did that start? SQLServerCentral.Com (SSC). I started reading articles on this site over a decade ago, and made it the main SQL Server resource for me beginning a DBA career. Back then a DBA was someone who either did one of 3 task types (or any combination).

  • DBA Administrator – worked to keep a system up and running including Windows Admin(Clustering), SAN usage, Security and Permissions (ActiveDirectory) and Performance Tuning (Monitor System resources).
  • DBA Developer – wrote T-SQL for stored procedures, triggers and jobs as well as create databases, tables and indexes usually because you were also a developer of software
  • DBA Business Intelligence – ETL with DTS (now SSIS), cubes in SSAS (multidimensional) and reports in SSRS (it was Crystal Report writer for me because SSRS was not released and packaged with Visual Basic) in addition to creating databases, tables and indexes.

Reading articles helped me develop a talent in the administrator position I was in along with getting a MCDBA in SQL Server 2000 (now I have MCITP in 2005 & 2008). The CONNECT part came when I ask my boss if I could go to the PASS Summit in Denver (2007). He had the funds in our department’s budget and included himself in the trip. There, I watched sessions based on the titles and abstracts. I did not know many speaker names except those I saw write articles on SSC. Those main ones I recognized were the ones who answered lots of questions posted on SSC forums as well as Microsoft message boards.

The next year, I was able to attend the Summit in Seattle and this is where I really started to CONNECT. Before that year’s Summit, I watched Patrick LeBlanc start the Baton Rouge SQL Server User Group. Patrick was able to use the resources from PASS to get it started and the networking with local IT professionals started this CONNECT for me. The group was created after the .Net User Group was started in Baton Rouge.

The second Summit I attended got me started wondering about the SHARE aspect. 2 or 3 sessions I attended got me thinking “I can do that”. Now, it was not magic and I was presenting. The first presentations I start doing were every other week Lunch and Learns at work regarding upgrading SQL Server from 2000 to 2005. The first presentation outside of work was for the local SQL Server User Group about SQL Server monitoring with Performance Dashboards. It went well, and I received very candid feedback from employees I worked with. I have learned today that feedback (good and bad) helps me improve the presentations over time.

Getting involved with PASS, I have helped the local user group with presentations, volunteering for 4 SQLSaturday events and promoting SQLPass.org locally. The Data Architecture Virtual Chapter needed help and I became the Chairperson for 2 years and have been able to encourage another DBA to assume the role after I retired which led into a VC mentor role.

The last item is LEARN. Well, if you have read this far, you should already know that all of the things above have helped me LEARN SQL Server - connecting to get the right people to go to with questions and sharing which enhances my skills. They are all learning opportunities which help advance my career and skill set.

Friday, November 7, 2014

PASS Summit: Blogger Q&A with Board of Directors Executive Committee

On Thursday, I was invited as a blogger to attend an intimate conversation (Q&A) with the PASS Board of Directors: Adam Jorgensen (EVP – Executive Vice President), Denise McInerney (VP Marketing) and Thomas LaRock (President). I started to try to understand why I was asked, but gave up due to mental exhaustion from attending the PASS Summit 2014. It was a great event with just way too much to do.

I decided to fish the internet and other community people for questions to ask. They boiled down to info about the BA conference, BOD structure/commitment, location of Summit and inquire about growth of DBAs in our community.  There were other questions, but the hour ran out. But, all three assured us that if you (Us and Ya’ll all) have a question or concern about PASS, each is individually willing to talk one on one through emails or phone calls. They even gave examples of individuals in the community that talked one on one to resolve an issue or understanding without have to use twitter or a blog post be load. We talked some about being mature members (growing up).

BA Conference – Why was it started, who is pushing it and why will it continue?

Thomas LaRock used my career  - from developer to DBA to BI Architect to a want to progress to Business Analytics. I never even thought about that until he mentioned it. Microsoft re-branded its SQL Server area for us as Microsoft Data Technologies – adding what is happening to data in this time and age. These are new opportunities for us data people.

There was no direct answer to the “pushing”, but the word Microsoft came up here and there. They are booked for the BA conference for the next 2 years in Silicon Valley (Santa Clara, CA) where there is a pool of speakers and talent in this area. Microsoft knows we (PASS) do community well, so they want us to help develop the community around BA. Adam mentioned the boards intent is not to split the community, but bring us together.

Denise mentioned how the first PASS Summit was an initiation only conference and they are going in that direction this year to help us get some understanding of what BA is really all about and help start to grow the speaker pool. It made sense. (I just read Mickey’s blog and she mentions there are vendors/exhibitors available as well in this area).

Summit Location – It will be in Seattle for the next 3-4 years (2015-2018 I believe)

There was no answer if it will be in another location. The deal is booking an event like this is starting to get more difficult with convention centers booking 4-5 years out. The BA conference is booked for 2 years (I believe they said that was the original plan/commitment).

Mark’s Question – Talk about the Election problems and why it took awhile for a response

The board and HQ had to abide by PASS By-Laws and laws of the state of IL. After HQ talk will Legal and quorums (12 people needed) were available, then decisions could be made (By the way, I think the decision and action were on the mark). The quorum problem got me to follow up my question about the board structure and time needed to be active in the decisions).

There were many conversations before quorum about what was the right thing to do. Denise ave credit to HQ for the movement of the process.

Board Structure and Time Required

I was amazed when Denise said she spends sometimes 2 hours a day on average (most made up on weekends because of having a ‘job’) and sometimes up to 30 hours in a week a month before a conference. Director At-Large maybe 5 hours a week. It could be as low a 2 hours a week if not much is going on.

The By-laws have the details if you want to read them (that is what they told me), but they did an excellent job making it simple for me.

Each Executive Member (elected – EVP, President and VP-Marketing, and Past-President stands on committee) of Board servers 2 year terms (max 8 years). The other 2 are Microsoft appointed (CA vacated their appointees last year). I asked if they will be replaced and that is being looked at (for help on the Executive Board). The total Board of Directors votes on the 3 Executive Positions.

The opening for president has a requirement of serving at least one year on executive committee. This past election we voted on was for At-Large members of the board. There are something like 6-8 at-large members. Tim Ford was one of them that sat in on the Q&A. I am sure there is more, but I will stop there.

Bill G (Past President) is pushing the Exec Board to get more HQ staff to take load off.

Growth of DBA members of PASS

No growth, but about 1/3 are DBAs. I do not know what to think about this, so I might follow up after it sinks in a little more.

 

Please visit Mark’s site. I am sure he will have a blog on this session. - http://markvsql.com/

 

Side Notes:

All agreed that HQ is doing a fantastic job!!!

The board believes the best way to communicate items to the masses is up to HQs to make that call. The Board is trying to let HQ do the business work of PASS, while the board works on strategy.

The BA stuff is becoming a portfolio with a director (from current At-large board members).

LaRock was all about growth, which is still happening.

Wednesday, October 29, 2014

PASS Summit 2014

Well, it is that time of year again. The Professional Associations for SQL Server (PASS) annual summit. This year we go back to Seattle where Microsoft mostly lives and hope we can get lots of their employees to visit and talk shop.

image

One of my main goals is to Network and get contacts from around the SQL Server world, but I always find 3 new things to learn at the Summit to bring back to my job. There, I can show the value of going to this event to my boss(es). This year the first goal is to find new information about maintaining a robust Data Warehouse since this is my main job at eQHealth. The second is to learn about PowerBI and what can be done in the Cloud (yes, you heard that right I said Cloud). Finally, I need to learn about Column Store Indexes and other improvements in performance of queries. There is always something to learn in this area and many sessions to be a part of.

image

I fly in on Monday night, so I will miss the annual Networking event put on by Steve Jones and Andy Warren. If you are there Monday night, it is worth the time. Networking is a big deal at these events, so get out and start talking.

image

Tuesday, I will start the day with a visit with other PASS volunteers, then attend one of the many great Pre-Conference All-Day sessions with Alberto Ferrari – From 0 to DAX. Tuesday evening is the Welcome Reception and a Volunteer Party.

 

 

image

Wednesday morning I will start the morning at the Community Zone from 7:30AM to 8:30AM. Then, listen to the Keynote. The 10:15AM session will be with Reza Rad on Data Mining. I meet Reza last year and it was a pleasure talking and learning from him. Smart guy. At noon, I will be in the Virtual Chapter lunch as I am a VC Mentor and need to meet and greet our leaders. At 1:30PM, Jen Stirrup is talking PowerBI & R. Peter Meyers (one of the best presenters I know) will be presenting on Master Data Services is at 2:45PM, which might be helpful at my job. At the end of the day it is a toss up between Marco Russo – DAX Patterns and Jon Welch – Continuous Delivery of DW & DMs. Wednesday night is the Exhibitor Reception.

image

Thursday morning has another Keynote on The Cloud, followed by a 10:15 session by Reza Rad (again) on Query M Functions. I will probably have to leave early to man the Community Zone from 11:30-12:30 during lunch. Hosting a Lightening Round session is next at 1:30 which I am really excited about. I will probably have to chill out after the Lightening Round and take a leisure break at the community zone and just visit with people or end the day at Alberto Ferrari’s Adv Model w/ SSAS Tabular. Thursday night is the Community Appreciation Part – EMP Museum which is always fun.

image

Friday morning I present at 10:15AM – Excel 2013 Tips and Tricks for SSAS Multidimensional. Unless I am totally comfortable with the presentation, I will probably take it easy getting up in the morning, rehearse the presentation at the hotel and then end up in the Speaker Ready room and try to sneak in the presentation room to test everything. After that all bets are off, except to fly home late on a red-eye to get back to Baton Rouge.

Wednesday, October 15, 2014

Can’t Create A Timeline in Excel on Multidimensional Cube

Excel 2013 - We can’t create a Timeline for this report because it doesn’t have a field formatted as Date.

After getting excited about the Timeline object in Excel 2013, I tried to add this cool feature of Excel 2013 and got an error – “We can’t create a Timeline for this report because it doesn’t have a field formatted as Date.”

 

image

After some Bing searches :), I found the solution to be adding the ValueColumn property of the Date Key attribute to be a date value. In this example, my Date key attribute in the Date dimension is an integer like 20140101 not the actual date itself. The NameColumn property is a text description of the date.

image

 

After adding the actual date (FullDate), the first error went away.

image

 

The following error was new – We couldn’t create a Timeline because the date field contains values that are not supported as dates in Excel.

 

image

 

In the initial stages of loading data, we had source dates that were unknown and we replaced those dates with 01/01/1800 (12/31/2099 for End Dates) or the date would come in as 01/01/1000 from the source. These values do not work in the Timeline. Once I verified that the unknown dates were cleared up, I changed the Date source in the Data Source View of the cube from including these date values:

BEFORE

SELECT DateKey, FullDate, DateName, DayOfWeek, DayNameOfWeek, DayOfMonth, DayOfYear, WeekdayWeekend,
    WeekOfYear, MonthName, MonthOfYear, IsLastDayOfMonth, CalendarQuarter, CalendarYear,
    CalendarYearMonth, CalendarYearQtr, 'Q' + CONVERT(varchar(1), CalendarQuarter) AS CalendarQuarterName,
    DateRange, CONVERT(int, REPLACE(CalendarYearMonth, '-', '')) AS MonthKey,
    RTRIM(LTRIM(MonthName)) + ' ' + CONVERT(varchar(4), CalendarYear) AS MonthYear
FROM dbo.DimDate
WHERE (DateKey BETWEEN 20090101 AND 20141231)
  OR (DateKey = - 1)
  OR (DateKey = - 2)

The OR in the WHERE includes a lower (1800-01-01) and upper (2099-12-31) in the Date dimension. These are no longer needed because the data in scrubbed first to exclude or correct the date problems.

AFTER

SELECT DateKey, FullDate, DateName, DayOfWeek, DayNameOfWeek, DayOfMonth, DayOfYear, WeekdayWeekend,
    WeekOfYear, MonthName, MonthOfYear, IsLastDayOfMonth, CalendarQuarter, CalendarYear,
    CalendarYearMonth, CalendarYearQtr, 'Q' + CONVERT(varchar(1), CalendarQuarter) AS CalendarQuarterName,
    DateRange, CONVERT(int, REPLACE(CalendarYearMonth, '-', '')) AS MonthKey,
    RTRIM(LTRIM(MonthName)) + ' ' + CONVERT(varchar(4), CalendarYear) AS MonthYear
FROM dbo.DimDate
WHERE (DateKey BETWEEN 20090101 AND 20141231)

Now, no more error:

image

 

image

Nice!!!

Saturday, September 27, 2014

PASS got it right

This morning I received an email (really 3 emails) about an extension for voting for the Board of Directors giving anyone registered with PASS before June of 2014. I think this is fair and gives those that have been active, but did not update their profile a chance to vote.

I read some posts about not being able to vote, passwords exposed, etc. with concern but patience. One thing I have learned over the years is I have to listen to all sides of the story before making a statement. Usually, I do not do this because of my inpatient personality. This happened with work this past Friday while I should have been enjoying PTO because of the birth of my son. :)

PASS (Professional Association of SQL Server) has enabled me and many others I know, advance in our careers. There is money involved with this organization, and I think that causes some tension in our minds. I really believe some have voiced concern to get more attention to their blog (which is their consultant company).

I received 3 emails because I have at some point in the past registered with PASS under 3 different accounts. I do not remember why and when I did this, but I need to inactivate the 2 I do not use. This is my part in helping PASS maintain the list of valid members.

I read Andy Warren’s post about who he is voting for and why, and see the problems with PASS opening the association to more than just SQL Server professionals. I actually agree with him even though my career is migrating from a DBA role to Business Analytics. I also agree with his suggestions for the following years.

Here is why I was on PTO last week.

DSCN1425    DSCN1421