SQL Saturday #104: Colorado Springs

January 8th, 2012 will be the first SQL Saturday for 2012. 104 SQL Saturdays is an amazing number for our community. I will be presenting on Dimensional Modeling based on the Kimball books. Database design and the planning for them has been a necessary start for successful projects.

I selected Colorado Springs because I have not been skiing in 3 years. My girlfriend and I will be skiing on Friday before the event. There is a planned skiing trip for Sunday with the event, but we have to get back work on Monday. She will be visiting a friend while I am attending/speaking at this great event.

There are 3 Pre Cons on tap for Friday, which is well worth the $100 for a day of training from MVPs and SQL Server experts. I would be at one if not for skiing.

1. "Scaling SQL Server" (Glenn Berry)
2. "Data Warehouse Dimensional Design and Architecture Planning" (Erik Veerman)
3. "What's In Your Utility Belt?" (Chris Shaw and TJay Belt)

The Saturday schedule is pack full of great sessions. I will probably will go over my presentation first thing in the morning in the Speaker room, then I am up at the second BI session of the day at 9:30AM. Next, I will stay in the BI room and watch Doug Lane with some report service info.

The other tracks have sessions from MVPs Tim Ford, Grant Fritchey and Jason Strate, all great and knowledgable speakers. There is also Thomas LaRock, Karen Lopez and William Pearson. It is amazing to be around these people in the SQL community willing to speak at these Free Saturdays. Joe Sack will be there from SQLSkills and Colorado local Carlos Bossy and Austin man Jim Murphy have some SQL Server 2012 sessions.

I will probably hang out in the BI track, wondering over to DBA sessions and enjoying the lunch. It sounds like there will a great Friday night speaker/sponsor event that is great for networking and thanking the sponsor.

If you live around Colorado Springs, come hang out with some SQL Server geeks getting some SQL learning on!!!

God Bless, Thomas

PASS Data Architecture VC: Jeremy Huppatz - Row Versioned Data Warehouses

Subject: Row Versioned Data Warehouses

Level: 200-300 (Intermediate)

Start Time: Thursday, December 15th, 2011 8:00 PM US Central Time

Presenter: Jeremy Huppatz (Blog|@OzzieMedes)

Live Meeting Link:

https://www.livemeeting.com/cc/UserGroups/join?id=B77TSH&role=attend&pw=dGC-%3B88%275

Row Versioned Data Warehouses
Jeremy will discuss the strengths and disadvantages of a row-versioned data warehouse design in the context of a real world case study, sharing lessons learnt and demonstrating some of the technologies and techniques used to build a row-versioned data warehouse. He will discuss row-versioning in the context of Kimball slowly-changing dimensions, and will also provide some details on the relative strengths of row-versioning as applied to measure information in situations where such data cannot be considered finalized at the time it is loaded into the warehouse.

Jeremy Huppatz
Jeremy has been a SQL Server data guy going back as far as 1997. During his 14 years working with Microsoft’s flagship database, he has filled the roles of DBA, data modeller, database designer, data analyst, BI developer and data architect in a multitude of projects, which have included departmental database apps, enterprise data warehouses and a bit of just about everything in between. Jeremy now runs his own IT consulting firm called Solitaire Systems and lives in the scenic Adelaide Hills with his partner Alison and her cat. He also plays, writes and records music and writes in his spare time, and has been described optimistically as an avid (as opposed to obsessive) computer gamer.

DataArch.SQLPass.org

The PASS Data Architecture Virtual Chapter will focus on data architecture concerns as they impact users, developers and DBAs on the Microsoft SQL Server platform. We want to make data architecture accessible to all data practitioners, and drive the point home that Data Architecture is a set of practices and a body of knowledge that overlaps almost all database professionals to some degree.

PASS Data Arch. Virtual Chapter: On beyond Zebra AdventureWorks OR where did I go wrong?

 

Subject:  On beyond Zebra AdventureWorks or where did I go wrong?

Level: 200 (Intermediate)

Start Time: Thursday, November 17, 2011 8:00 PM US Eastern Time (November 18, 2011 1:00 AM GMT)

End Time:  Thursday, November 17, 2011 9:00 PM US Eastern Time (November 18, 2011 2:00 AM GMT)

Presenter: Steve Simon (LinkedIn)

Live Meeting Link: https://www.livemeeting.com/cc/UserGroups/join?id=K32S7T&role=attend&pw=q%3Fp4cW%24Mg

On beyond Zebra AdventureWorks OR where did I go wrong?
No matter how well planned and executed, the data structures of any data warehouse oft times eventually land up resembling something out of a Dr. Seuss book. In this presentation we shall be looking at and discussing a few of the “perfect” data structures (that I have inherited) and discussing the flaws that have only recently surfaced. We shall be looking at the ‘before’, discussing a few alternatives to make these structure more efficient and effective, then we shall then look at final “production structure” and the resulting improvement metrics; all of which help us to help our clients’ make better decisions in a timely manner.

Steve Simon
Steve Simon is a Senior Business Intelligence Engineer with MyWebGrocer in Burlington Vermont. He has been involved with database design and analysis for over 26 years. Steve has presented papers at eight North American PASS Summits (in Orlando, Seattle WA (4), Denver CO (2) and San Francisco CA), two at PASS Europe 2009 and one at PASS Europe 2010. He has just recently presented at the Johannesburg and Cape Town SQL Server Saturdays.

Steve has also had two papers published in Information Builders’ Systems Journal, is the chairman of the Boston User Forum and is a regular User Forum and Webinar presenter for Information Builders.

Add to calendar

PASS Summit 2001 In Review…WOW!!!

It has been a couple of weeks and I have had time to get back in the groove at a new job as Senior SQL Server DBA, after trying Informatica Administrator via Enterprise Architect for about 3 months. Long story, but I am glad to be back as a DBA.

My week started on a Sunday travel day where I got to Seattle about 8pm Pacific and close to 10pm getting to bed at the 6th Avenue Inn after taking the Light Rail. The rail was cheap ($2.75), but took at little more work and time. The hotel suggestion was from a blog by Brent Ozar and cost ~$600 for Sunday thru Saturday.

Monday was off to Rooooobbbb Farley’s Query Tuning Pre-con. I learned a lot about Residual Predicates and GROUP BY versus HAVING versus WHERE clauses. He was ad-hoc writing queries to show performance metric and I hope to get a copy of the sql before long to review.

The second day was about networking. I started in the Virtual Chapter leaders meeting with Geoff and Karla, They did a good job informing us about options and sharing from different chapter leaders. Our Data Architecture VC can give away a $25 gift that I did not know about. If we get a sponsor, up to $100 a session. It was nice to talk with the DBA VC which had 3 people on attendance, and I got a nice shirt, that I wore while manning the VC tables during the Welcome Reception.

  

Tuesday was not over. Next, SQLSaturday leader meeting. This was led by Andy Warren who is a well-seasoned leader and moderator. Various suggestions were given for the website, which I think is invaluable, no matter what does not work perfectly. Anything free is nice in my opinion. Lots of information in starting a Pre-con for a SQL Saturday and talk about speaker cancelations. This seems to be a growing concern from leaders and ways were discussed to help grow more speakers and get speakers to notify about needs to cancel.

 

Above are pictures of Andy, Karla and the PASS IT dude. And right is Greg Larson and Sri.

The second half of Tuesday was spent at Adam Machanic’s Pre-con and performance tuning. Man, this dude knows his stuff. He and Alan White helped me understand the difference between CXPACKET problems, versus request Waits through twitter and blogs. sp_WhoIsActive is a tool you have to learn, plus read his 30 days of explanation on what it does. Adam is well organized and well rehearsed. His training is a must.

Tuesday evening was the Welcome Reception and SQLServerCentral.Com and RedGate’s Casino night. The exceptional DBA ward is given at the later event.

Wednesday morning was time to tackle the regular sessions. I made it a BI day. Started with Craig Utley from SolidQ doing SSAS Aggregates. Then, Devin Knight from PragmaticWorks presenting common SSAS mistakes. Both sessions were great and well prepared. Lunch was Chapter day and wear your SQL Saturday shirts. Jen Stirrup presented on PDW and reporting which got me wanting to investigate more into the monitoring of instances. Last was Many to many relationships in DAX by Alberto Ferrari. The last session kind of got me thinking about how to model the data to not have to write expressions in Excel. Wednesday night was Vendor appreciation dinner at the Summit. I visited with SQLSentry, Confio and Idera, ending with Quest while I had questions about Spotlight used at my current employer.

Thanks to DELL for my favorite place at PASS!!! And thanks to Robert from BCBS helping me find this treat.

Thursday morning I got to blog from the bloggers table at the morning Keynote. See previous blog for my take - http://thesmilingdba.blogspot.com/2011/10/sql-pass-2011-live-blog-keynote-day.html. It was really cool to sit next to Kevin Kline and Stacia Misner among others.

Thursday morning was Reporting Service Tips and Tricks from Bob Meyers. I really enjoyed this session. Though my favorite session on the whole week was Ami Levin talking about Physical Join operators. After I go through his examples again, and apply the lessons to some real world queries, I will have to blog about it. Do not miss this guy, he was well organized and very knowledgeable. I might be wrong about the best session, because next was Kalen Delaney talking Plan Cache. 500-600 people attending can tell you she knows her stuff. Check out her internals book for more info - http://www.sqlserverinternals.com/books.html. Last was a chance to see a Kimball employee Joy Mundy talked about Multivalued Dimension relationships – no demos and all talk left me wanting more. Maybe my brain was already toast.

I took Thursday might off to head to my room and grab a bite. There was a need to rehearse my 3rd Normal Talk before Friday. Well rested, Friday morning started. First up, Adam Machanic again with a 500 level Query Memory session. Another great, well prepared session with demos. After lunch, I briefly went to Peter Meyers talk about KPIs in the MS BI Stack. Peter came to SQLSaturday in Baton Rouge, so I want to say hi and thank him again. All the SolidQ presenters are great in the information they share and well prepared. My talk happened fast. Only 2-3 people left out of ~150 and 5-6 questions at the end. Hope to learn from comments. I think next time, I will not go to 400 or 500 level sessions before my 200 level. I felt I did not present enough advanced stuff, but I know the database normalization talk is needed in the development community. Last, I took it easy in a T-SQL session with Aubrey Hammonds. She showed some CTEs and other cool SQL. It was a good break from the other advanced sessions. The data Architecture VC will have her presenting in the future.

The PASS Summit is a not miss event. I would rather go to it than any TechEd event. No other conference have I been able to see networking, fellowshipping and free sharing of knowledge ever. I am so glad I have been able to meet people like Grant Fritchey, Sean and Jen McCown, Andy Warren and Steve Jones just to name a few. And thank the companies that sent or let me go.

God Bless and GEAUX Tigers!!!

Thomas

Jeremy Huppatz presents Row Versioned Data Warehouse

Subject: Row Versioned Data Warehouses

Level: 200-300 (Intermediate)

Start Time: Thursday, December 15th, 2011 8:00 PM US Central Time

Presenter: Jeremy Huppatz (Blog|@OzzieMedes)

Live Meeting Link:

https://www.livemeeting.com/cc/UserGroups/join?id=B77TSH&role=attend&pw=dGC-%3B88%275

Row Versioned Data Warehouses
Jeremy will discuss the strengths and disadvantages of a row-versioned data warehouse design in the context of a real world case study, sharing lessons learnt and demonstrating some of the technologies and techniques used to build a row-versioned data warehouse. He will discuss row-versioning in the context of Kimball slowly-changing dimensions, and will also provide some details on the relative strengths of row-versioning as applied to measure information in situations where such data cannot be considered finalized at the time it is loaded into the warehouse.

Jeremy Huppatz
Jeremy has been a SQL Server data guy going back as far as 1997. During his 14 years working with Microsoft’s flagship database, he has filled the roles of DBA, data modeller, database designer, data analyst, BI developer and data architect in a multitude of projects, which have included departmental database apps, enterprise data warehouses and a bit of just about everything in between. Jeremy now runs his own IT consulting firm called Solitaire Systems and lives in the scenic Adelaide Hills with his partner Alison and her cat. He also plays, writes and records music and writes in his spare time, and has been described optimistically as an avid (as opposed to obsessive) computer gamer.

DataArch.SQLPass.org

The PASS Data Architecture Virtual Chapter will focus on data architecture concerns as they impact users, developers and DBAs on the Microsoft SQL Server platform. We want to make data architecture accessible to all data practitioners, and drive the point home that Data Architecture is a set of practices and a body of knowledge that overlaps almost all database professionals to some degree.

SQL PASS 2011: Live Blog Keynote Day 2–Quentin Clark

7:50AM – arrived at bloggers table and positioned between Rooob Farley and Kevin Kline. Just meet SQLBalls, in front is Ryan Adams and Mike Walsh.

Hopefully, this will be a demo Keynote and not just talk, talk, talk…

8:10AM – Room is filling up, cannot believe so many people here at #SQLPASS, they are saying a 22% increase from last year. All the rooms are pack, some have people on the floor. Andy Warren and Stacia just sat down and are typing and talking. John Sterret just came and said hi.

8:25AM – Laptop crashes OOOHHH!!!!

8:30-8:40am – Bill talks about the wonderful volunteers around the country for SQL Server. Lori Edwards receives PASSion award. Tim and Jeff mentioned about their work

8:45AM – Quentin Clark invited Bob Erickson from Interlink Transport – talks about operations and uses of SQL Server (Mission critical is Their Business)

8:50AM – demonstrating Always ON for Interlink – Wizard setup and ASync versus Sync. Listening is a DNS name and connect to name and does the redirecting

8:55AM – on to Blazing fast speeds – VertiPak built into Analysis Services, takes advantage of the technology and in the RDBMS. Some customers are reporting 10, 20 & 30 times processing speed.

9:00AM – Power View + PowerPivot – SSAS on the back-end but control by IT (SharePoint). Alerts on reporting, configurable. A BI Semantic Model – ‘who do you know…’ Semantic understanding. As the model is used, the engine learns from queries. Now, to get it in a data model. Master Data Services – managing the location of where everything is located.

9:05AM – @SQLGal is on stage. Using Contoso database. Columnstore index demonstration on query in report going from 45 seconds to .13 seconds after ColumnStore index and linking data to Cloud location for objects in map not appearing where there are actually located. Also,

9:10Am – Parallel Data Warehouse – Appliances. Deep Dive to workloads, and understand characteristics and find the architecture to solve problems. Appliance developer kit to fit customer. Demos DELL parallel warehouse, more than one appliance.The stage has the DELL racks for PDW. And th HP rack that has been out over a year. Also, have your own Private Cloud.

9:20AM – ODBC drivers to Linux – more and better drivers.

9:25AM – integrating CDC with Oracle, SQL Server 2012 has it – Now Beyond relational. file Stream 2D Spatial, Semantic search

Beyond full-text search – better to be seen – Demo time

The demos need some Zooming

Semantic search extracts the keywords from the documents. More than Full-text search.

9:30pm – ‘Juneau’ – now SQL Server data Tools -

Now embed SQL Server Express DB in an Application

Scaling to the Cloud – spin up many many instances in the cloud to distribute queries – DEMO time

Nicholas Dritsas SQLCAT team – SQL Azure demo

9:35AM – Who is @CajunSQL – did not know of any other CoonAxxes here at PASS

9:40AM  - Real customer demo with SQLAzure – moved to cloud form

9:45am – Using Federation in the Cloud – sizing and scaling in the cloud

Example is a blog site that does not know what blog goes viral – Blogs r’ Us

No developer rework of code

Neat interface/management tools – Schema walker

Overview of tasks going on – Powerful reporting

Ability to create large database – up to 150 GBs in size

9:50am – Azure and SQL in same code base.

NOW FOR SOME TRAINING!!!

God Bless,

Thomas

PASS Summit 2011: Day One

After getting a little lost from the rail to the hotel, I finally got to sleep Sunday night after a 10+ hour airplane/airport travel time. Well worth it especially after seeing LSU whoop up on Florida in Tiger Stadium on Saturday afternoon.

Started by meeting Robert from BCBS to have an early registration and breakfast and the networking started at 7:30am with a gentleman from New York and another from California. We chatted about performance tuning on SQL Server as well as Sybase. Rob started to see what I meant about the value of PASS right away. He went to Brent Ozar’s SANs and Virtualization Pre-con and I hit Rob Farley’s 1/2 comedy/music show and Advanced TSQL for query tuning.

Lunch break had Robert and I at a table with Ryan and David from Dallas talking about SQL Saturday’s. We also had some new friends for lunch to chat about different companies we worked for.

The second half of the day was more Rob Farley and TSQL. He was able to explain some property window information about query plans. Never realized sometimes you have to read the plan from left to right to really understand them. WHERE and HAVING clauses were explained in more detail. The highlights was a new term for me: Residual Predicates. You really need to read up on this to understand advanced query tuning.

Rob even helped promote my 3rd Normal Talk on Friday.

The evening was a well organized Networking dinner that Andy Warren and Steve Jones put together at Lowell’s. The food line moved at a good pace for outside conversations, but the food was served fast and it was good. Saw @SqlDiva from New York and Kathy K from Microsoft. Idera was there with Dave and Heather.

Robert and I called it a night at 10PM CST (we are on Pacific time, so it was 8pm), but we were both tired.

Tomorrow is all about networking with Virtual Chapter leaders, local user group leaders and SQL Saturday organizers. Should be interesting.

Here are some links:

Rod Farley Residual Predicate - http://sqlblog.com/blogs/rob_farley/archive/2011/03/22/probe-residual-when-you-have-a-hash-match-a-hidden-cost-in-execution-plans.aspx

Lowell’s in Seattle - http://www.eatatlowells.com/

24-Hours of PASS DB SOP - Standard Operating Procedure for Normalized Database Design

God Bless,

Thomas LeBlanc

PASS Summit 2011: My Itinerary

Here goes with what I hope to achieve the following week in Seattle, WA Oct 9-15 for my 4th summit.

I have spent about 1 hour a day reviewing what I have selected for the week with the really cool Build Your Schedule for Summit 2011.

Sunday Oct 9th is travel day, which starts about 1PM central in Baton Rouge and end at 9pm Pacific in Seattle. I will use this time to review my presentation and read parts of Louis Davidson’s book about relational databases.

Monday morning I hope to have breakfast will some SQL friends (Rob) and head to Rob Farley’s Pre-con on Fixing Queries with Advanced T-SQL constructs. I saw a session on 24 Hours of Pass by Rob, and realized I need to learn from this guy. He seems to have some real good customer/employer experience with queries, plus a dynamic personality. I like the fact his abstract says there will be not much PowerPoint

Monday night Andy Warren and Steve Jones have a meet and greet social that I hope to attend with a new Summit person I encouraged to attend. Hopefully, I get to see John from West Virginia that night. Cool dude!!!

Tuesday is full of social activities with SQL people that volunteer on different levels. First, 8am meeting with Virtual Chapter leaders. I am the Co-Chair with Lorra Newton on the PASS Data Architecture VC. After that, there is a meeting of SQLSaturday people up until lunch. The chair of local PASS chapters meet in the afternoon, and I am going to try and represent the Baton Rouge chapter for William Assaf  and Patrick Leblanc. I really hope to meet some new faces in the SQL Server world during this day. There is always someone new that is so fired up about helping the community. I love being around these people of just listen and encourage.

Tuesday night is the Quiz bowl and Welcome reception which is always entertaining.

In addition to Quiz Bowl, Tuesday night is the best evening at Pass with SQLServerCentral.com and RedGate Casino Night. Steve Jones is a great guy to be around and SQL Server Central is where I got my first look at how giving the SQL Server community is with information. Thanks Steve, Andy and Brian!!!

If I do not get to blog the summit keynotes, I will skip the first keynote and go to the Lab and play around with Denali. These hands on labs are a great way to introduce new stuff. I will go to the Keynote on Friday with David DeWitt - http://www.sqlpass.org/summit/2011/SummitContent/Keynotes.aspx. Maybe Thursday morning I will go chat with the SQL First Aid station and just listen in on some debugging. If you have a problem, this is the place to be to get Microsoft Support to assist. I have used these people with replication problems in the past.

Wednesday starts the 3-day summit with 75, 90 and 1/2 day sessions. It is really hard to pick, but here goes.

I have multiple options for Wednesday thru Friday, the only sure thing is my 3rd Normal Form: That’s Crazy Talk!!! on Friday at 2:30pm.
Wednesday Oct 12th

10:15 AM - 11:30 AM -
[BIA-404] Add It Up: Analysis Services Aggregations
[RM:4C3]

1:30 PM - 2:45 PM
[BIA-305] Common Analysis Services Design Mistakes and How to Avoid Them
[RM:602-604]
1:30 PM - 2:45 PM
[DBA-304] SQL2008 Query Statistics
[RM:2AB]
1:30 PM - 4:30 PM
[DBA-500-HD] Inside Tempdb
[RM:6E]

3:00 PM - 4:15 PM
[BID-306] Intelligent Laziness with the Management Data Warehouse – why work harder, when you can work smarter?
[RM:608]
4:45 PM - 6:00 PM
[AD-318] Characteristics of a Great Relational Database
[RM:602-604]
4:45 PM - 6:00 PM
[DBA-320] Why are we Waiting..
[RM:606-607]

Wednesday is the Exhibitor Reception and I need to get with Quest people because my new employer uses SpotLight. Still need to say hi to Confio who have Ignite, the best Wait State performance monitor around.
Thursday, October 13, 2011

10:15 AM - 11:30 AM
[DBA-403] Advanced SQL Server 2008 Troubleshooting
[RM:6E]

1:30 PM - 2:45 PM
[BID-301] Reporting Services Techniques and Tricks
[RM:613-614]
1:30 PM - 2:45 PM
[AD-400] Physical join operators
[RM:615-617]
1:30 PM - 4:30 PM
[BIA-303-HD] So How Does the BI Workload Impact the Database Engine?
[RM:606-607]

3:00 PM - 4:30 PM
[DBA-314-S] What Happened? Exploring the Plan Cache
[RM:6E]

5:00 PM - 6:15 PM
[BID-303] Creating Business Intelligence Dashboards with PerformancePoint Services 2010
[RM:4C3]
5:00 PM - 6:30 PM – I have to see at least one presentation by Peter Myer
[BID-305-S] End-to-End SQL Server PowerPivot
[RM:2AB]

Thursday night I am taking the night off, have some dinner and rest for my presentation on Friday afternoon. I might go by the Community Appreciation Party sponsored by Microsoft for a quick bite.
Friday, October 14, 2011

10:15 AM - 11:45 AM
[AD-500-S] Query Tuning Mastery: Zen and the Art of Workspace Memory
[RM:6E]
10:15 AM - 11:30 AM
[DBA-402] Windows Operating System Internals for Database Pros
[RM:4C1-2]
10:15 AM - 11:30 AM
[BIA-307] Vertipaq vs OLAP: Change Your Data Modeling Approach
[RM:608]

1:00 PM - 2:15 PM
[BIA-308] Delivering KPIs with Analysis Services
[RM:602-604]

2:30 PM - 3:45 PM
[AD-206] 3rd Normal Form: That's crazy talk!!!
[RM:609-610]

4:15 PM - 5:30 PM
[AD-303] Parameter Sniffing: the Query Optimizer vs. the Plan Cache
[RM:608]
4:15 PM - 5:30 PM
[BID-302] Multidimensional Reporting: MDX Essentials for Report Design
[RM:602-604]

Friday night find some people to have dinner and wind down, maybe blog some about the Summit. This is definitely the best week of training and networking I have ever experienced. Please go if you ever get a chance.

Saturday I travel home and hopefully I get some sleep on the plane and get some idea how LSU is doing against Tennessee. GEAUX TIGERS!!!

24 Hours of PASS: Answer questions…

I will try my best to answer some of the questions after the Standard Operating Procedure webcast for 24 Hours of PASS.

First one is what design tools are out there. Here is a list I found from Louis Davidson’s book Pro SQL Server 2008 Relational Database Design and Implementation:

• All Fusion ERwin Data Modeler
• Toad data Modeler
• ER/Studio
• Visible Analyst DB Engineer
• Visio Enterprise Edition

I would not suggest using the one built into SQL Server, because if you change the design then and save it, the tables will be updated automatically with the changes.

The second one had to do with having views in a data modeler tool and showing the relationship between other tables or views: I do not have a good answer to this, and have asked others with no available tool.

3. What ER diagram tool are you using for the presentation? Which do you recommend?

I used Visio for my presentation because it is what I have a license for, but the companies I have worked for recently use ERwin.

4. Where can we download the example document I used?

I cannot let you have it because it is from a company I worked for, but I did relink the Parts 1 thru 6 of a generalization of the SOP.

5. One comment was for me to read up more on normalization –

This is true, I gave a brief (very brief) explanation of 1st, 2nd and 3rd normal forms. I should not have done that and just referenced a website.  Or suggested to go to the PASS Summit and watch my session 3rd Normal Form: That’s crazy talk!!!

6. Singular or Plural on table name?

Singular, like Louis says, pick a standard and be consistent.

7. The CHAR(1) for IsActive is much better for using the column directly in Reports (for example - If the value is Y or N it is much easier to simply write the sql to display than to logically determine the meaning of the Boolean.

That is a good point, reporting is very important. Some report writers might even convert Boolean to a text equivalent.

8. Please repeat the contact/blog/web site information. Thank you!  --> Here it is

9. Will the recording be available - answered yes.

Keep checking back on 24HOP site, or you can watch it from the PASS Data Architect Virtual Chapter

10.  will there be an example of the SOP available? –> see Number 4 above

11. Have you found an ERI application which allows joins to be defined on a view. Visio and Erwin do not allow this. –> see Number 2 above

12. I recall sometime ago while trying to use Visio 2010 to import SQL 2008 database for database diagrams it wasn't supported. Any ideas on future support for Visio and database diagrams?

Look on the database menu choice to reverse engineer a database, then it connects thru ODBC (DSN)

13. Preferred data type for ID?

It depends on the amount of rows in the table, but you can go with tinyint, smallint, int or biint

God Bless,

Thomas

Database Standards: Links to past blogs

After a great session on 24 Hours of PASS, lots of people wanted the SOP document. While I cannot give the one from the company to you, here is a post of the links to the blogs about them

Parts 1 thru 6 and Lookup tables. Please review and place comments on the blog!!!

Database Standards Part I: Defs, Abbreviation & Data Types

Database Standards Part II: Schemas, Tables & View and Columns

Database Standards Part III: Indexes, Constraints & Primary/Foreign Keys

Database Standards Part IV: Stored Procedures

Database Standards Part V: Triggers and User-defined Functions

 Database Standards Part VI: code and design

Lookup tables

I will answer the other questions in a blog next week as soon as I can get come reliable answers. Thanks again for attending and all the great questions and comments. Thanks to PASS for this opportunity

God Bless,

Thomas

24 Hours of PASS: More free training from experts (and me)

I try not to say I am an expert, I just have some years of experience with some failures that were turned into successes. Experience is a good teacher.

I was an alternate for 24 Hours of PASS (#24HOP twitter hash tag) until Karen Lopez had to cancel because of a flight she was going to be on.

I will be giving a Database Standard Operating Procedure talk as a preview to the 3rd Normal Form: That’s Crazy Talk!!! that I was selected to do at the PASS Summit 2011.

If you have never been to a PASS Summit, I would bet you would leave there more excited about SQL Server than when you arrived. The Summit has some of the most enthusiastic SQL Server professionals I have ever meet.

Most talks are by IT people in the field, not salesman trying to sell you software. You get real world experience. The 24 Hours of PASS will have some of these speakers giving previews of their Summit talks. Some Summit talks are regular sessions like mine(75 minutes), but most are doing either 90 minute Spotlight sessions, 1/2 day sessions or Pre-conference sessions which are Monday and Tuesday of the Summit.

I have been to the PASS Summit 3 of the last 4 years, have gone to pre-conference sessions every year and plan of doing the same this year. I have also watched 24 Hours of PASS the last 2 years when I can at work.

Please join us this Wednesday and Thursday for 24 hours of free training by SQL Server experts and ME!!!

RECAP: Baton Rouge SQLSaturday #64 and TechDay

     

This year I decide to help with volunteering and not speak. Since being selected for SQL PASS Summit

 I decided not to speak and give others, especially local people an opportunity to start their speaking careers. The first time I spoke was at a local user group then SQLSaturday #28. I did my best to pick a topic (RML Utilities) that I thought would impress people. What I realized over time, that the majority of attendees at SQLSaturdays were new to the technology and it would be better to appeal to the newbies.

Execution Plan Basics and 3rd Normal Form: That’s Crazy Talk!!! are the types of talks I concentrate more on lately. The more advanced attendees (including myself) get to criticize the session about the lack of advanced/intermediate topic, but I have to come to accept these comments to only improve the next talk.

I did get to introduce the event at the keynote SQL side which was a basic description of the event and how to read the schedule, visit sponsors and participate in raffles. Serving Jambalaya and tallying speaker evaluations were some of the volunteering I did. Keeping up with speakers and cancelations/replacements were not fun as 1/2 of the speaker coordinator, but it had to be done.

      

There was a wonderfully giving group of volunteers that meet periodically to plan this event. Laurie, Sara, Eric & Beth from Antares and William, Mike & Justin from Sparkhound and Mr. Mike Verret (LSU).

  Proficient (Mike, Ryan & Val) sponsored as a Platinum level with Microsoft (THANKS!!! Zain and Patrick LeBlanc) and HP. There were many Gold and Silver sponsors

We had 35+ attendees pick volunteer when the registered which gave us enough people to monitor each speaker room to help collect close to 1000 speaker evaluations and help with a book giveaway at each session.

I cannot say enough of the speakers who came from all around the country, Steve Jones and Carlos from Colorado, Peter Myers from SolidQ, Sean & Jen from Dallas, Stuart from Atlanta, Rob Vetter (C# MVP), Jose from Tampa, AvePoint and K2 as Sharepoint/Sponsors and filling in for speaker cancellations. Even the late arriving William Pearson from JawJa.

More volunteers (There were probably 20-30 extras helping us).

     

Here is the link to more pictures: https://picasaweb.google.com/110051938285440518693/SQLSaturday64?authuser=0&authkey=Gv1sRgCOSS5LS5l_TkXg&feat=directlink#

Until next year, God Bless!!!

Future Reserved Dates

Here is a quick view of dates and locations that have been reserved for future SQLSaturday events. Click here to reserve a date!

Aug 04, 2012
Taylor Hall, Louisiana State University Campus
Baton Rouge
LA

August 18th Data Arch VC Meeting: John Racer on Data Warehouse Architecture

August 18 Data Arch VC Meeting: John Racer on Data Warehouse Architecture

Subject:
Data Warehouse Architecture

Level:
200 (Intermediate)

Start Time:
Thursday, August 18, 2011 1:00 PM US Central Time (August 18, 2011 6:00 PM GMT)

End Time:
Thursday, August 18, 2011 2:00 PM US Central Time (August 18, 2011 7:00 PM GMT)

Presenter:
John Racer (blog|@SpeedRacer)

Live Meeting Link:
https://www.livemeeting.com/cc/UserGroups/join?id=TQ6GQT&role=attend&pw=5p_%28%3BB3df

Data Warehouse Architecture
Data Warehousing is a large and often misunderstood concept. Marketers, Authors, and IT Departments all have differing spins and opinions on what is involved and just how to go about building one. In this session we will discuss some of the more common architectures and practices. We will cover various aspects of what goes in to Data Warehouse including the systems, modeling, data storage, data integration and data access.

John Racer
Database Administrator, Business Intelligence Developer and Data Warehouse Designer with strong knowledge of telecommunications, care center, compensation and business operations systems. Experienced with implementation and maintenance of SQL Server and Oracle Multi-Terabyte High-Availability environments. Broad experience with project management through SDLC, RAD and Scrum. Driven by the challenge to understand business and processes and apply this knowledge create innovative applications of technology to improve organization and operational efficiency.

John's blog:
http://speeddba.com

Add to your calendar

Business Intelligence Track–SQLSaturday #64 Baton Rouge

Just in case you did not know, the annual Baton Rouge SQLSaturday #64 and Tech Day will be August 6th in Baton Rouge, LA at the LSU campus. Patrick LeBlanc started it all in 2009 and there is wonder group of Technology Geeks keeping the trend going.

If I were not volunteering for #SqlSat64, this is where my but would be all day, except during lunch eating jambalaya and watching with Steve Jones represent RedGate

http://www.sqlsaturday.com/64/schedule.aspx

Business Intelligence for Managers/Decision Makers (1111) - Beginner

BI Track – 8:30-9:30AM

Business Intelligence is an essential component for any business today and a successful BI implementation has the power to change the way your organization functions. In this non-technical session Carlos Bossy will show you what a BI project looks like and how it is different from other IT projects, how to determine the ROI of a BI project, the makeup of a good BI team, how to define the success of a BI implementation, and how to fix a BI project gone bad. You will also learn how to evaluate your BI & Analytical Strategy and you should leave this session with better knowledge of how to plan, build and deploy effective Business Intelligence solutions.

Carlos Bossy (MCTS SQL Server 2008 BI, CBIP) is a Consultant with 25 years of experience in software and database development. Carlos is an independent consultant and focuses on developing Business Intelligence solutions including data warehouses, predictive analytics, data integration and reporting. He has worked with SQL Server for 10 years and is very enthusiastic about its powerful features. Carlos has developed warehouses and BI solutions for a variety of industries and state agencies.

Handling Advanced Data Warehouse Scenarios in SSIS (1111) - Intermediate

BI Track – 9:45-10:45AM

So you’ve used SSIS to populate a simple star schema data mart, and everybody’s happy. But now you have new requirements that require more advanced data warehouse approaches, like late arriving dimensions, bridge tables, parent child dimensions, and Type 3 or Type 6 slowly changing dimensions (SCD). How do you handle those in a scalable, efficient way in SSIS? This session will present some common patterns for handling these scenarios. You’ll learn when to use each advanced approach and the pros and cons associated with each pattern. You will learn how to implement these patterns in SSIS, and how to tune them for high performance

John Welch is BI Architect with Varigence. Varigence builds tools and frameworks that enable the creation and management of end-to-end business intelligence solutions with unprecedented ease and speed. John has been working with business intelligence and data warehousing technologies since 2001, with a focus on Microsoft products in heterogeneous environments. He is a Microsoft Most Valued Professional (MVP), and a frequent presenter on SQL Server BI topics.

Introduction to Analysis Services 2008 R2 Cubes (1111) - Intermediate

BI Track – 11:00-12:00PM

Analysis Service’s OLAP component allows organizations to implement a cube that is designed for rapid ad hoc information retrieval of their data. The cube, as a single version of the truth, can be enriched to encapsulate business rules and calculations, and advanced Business Intelligence features including KPIs and actions. For organizations that have a Standard or Enterprise SQL Server license, Analysis Services is a possible zero-cost opportunity for your organization today. In the session learn how to exploit the capabilities and features, and the basics of best practice design. Be prepared for numerous compelling demonstrations and to leave the session energized by the potential

Peter Myers has 14 years of solid experience working in OLTP database design and Business Intelligence with SQL Server and SharePoint. In April 2010, Peter was re-awarded Microsoft MVP (Most Valuable Professional) for the fourth time. Peter enjoys sharing his enthusiasm for Microsoft technologies by presenting at SQL Server user group meetings and technical events, including TechEd in North America, Europe and Australia

Designing and Optimizing SSAS Hierarchies (1111) - Advanced

BI Track – 1:30-2:30PM

In this session Jose will do an overview of the different types of hierarchies and how to design user hierarchies in SSAS by defining attribute relationships and key columns the right way. Attendees will learn how to implement natural & unnatural hierarchies, balanced and ragged hierarchies, and Parent-Child hierarchies. Jose will show how to optimize SSAS hierarchies by specifying attribute and dimension types, member uniqueness, and aggregations. Jose will also do quick intro on querying hierarchies with MDX

Jose Chinchilla is a Microsoft Certified Database Administrator and Business Intelligence Developer working as a Sr. BI Consultant and trainer for Pragmatic Works. Jose has12+ years of experience in IT and has focused his career in OLTP and OLAP database design, development and administration and specializes in ETL/ELT using SSIS, Data Warehousing and Multidimensional Analysis using SQL Server 2008 BI tools. He is also the current president of the Tampa Bay Business Intelligence User Group

Visualizing SQL Saturday (1111) - Intermediate

BI Track – 2:45-3:45PM

This session will cover the best practices of dashboard design and data analytics as we explore and create data visualizations from the history of SQL Saturday. This session will focus on analyzing trends over time, categorical trending and dashboard design

Tim Costello is an MCITP, MCTS specializing in etl and data analytics for Interworks Inc. Tim is an international speaker with 25 presentations in the US and Canada since January of 2010. Tim is active in the SQL community and leads a data visualization focused user group in the Dallas area. Tim Started working with data in Access 95 and has worked in every version of SQL from SQL 7 forward

An Overview of PowerPivot (1111) - Beginner

BI Track – 4:00-5:00PM

In this presentation, Microsoft BI Architect and SQL Server MVP Bill Pearson explores “self-service BI” as a concept. We then provide a walkthrough of the primary features of PowerPivot, focusing on how we can exploit its capabilities to offer the benefits of BI to decision makers and analysts throughout our organizations. “There’s no service like self-service …”

Bill Pearson created Island Technologies Inc. in 1997, and has developed a large and diverse customer base since. Bill's background as a CPA, Internal Auditor, Management Accountant and SQL Server MVP (BI) enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, and focuses his practice upon the integrated Microsoft business intelligence solution

SQLSaturday #64 Baton Rouge Edition

AAAAYYYEEE!!! as they say down on the Bayou.

Time to get some free SQL Server, .Net and Sharepoint training in Baton Rouge. This is annual Tech Day in Baton Rouge for the third year. MVPs from all over the south and beyond are gathering to train people through 8 Tracks with 6 sessions each, that is 48 hours of free training plus a Window 7 Phone garage (not sure about this one).

Please register her: http://sqlsaturday.com/64/register.aspx

See the session lineup here: http://sqlsaturday.com/64/schedule.aspx

There are too many presenters to mention, so will not name them because I do not want to miss anyone, but this year the will be 46 different speakers and I will not be one of them. I am concentrating on helping the volunteers put on the show.

Also, I have been accepted as a speaker (and alternate) at the PASS Summit in Seattle October 10-15 2011. Still have to get approval from my new job to attend.

Oh yeah, I forgot to mention that I have a new Job. I now work at Blue Cross-Blue Shield of Louisiana in Baton Rouge in the Enterprise Architecture team, probably to change to Enterprise Architect Data Performance title. I am in New York right now training on Informatica which is an enterprise ETL tool that sorry to say, blows SSIS out of the water.

Maybe Denali will catch up with Informatica, but right now it does not look close.

Well, good night and back to work

God Bless,

Thomas

July 21 Data Arch VC Meeting: Gaurav Aggarwal on Microsoft – Readying on “Hadoop”

July 21 Data Arch VC Meeting: Gaurav Aggarwal on Microsoft – Readying on “Hadoop”

Subject:
Microsoft – Readying on “Hadoop”

Start Time:
Thursday, July 21, 2011 9:00 PM US Eastern Time (July 22, 2011 2:00 AM GMT)

End Time:
Thursday, July 21, 2011 10:00 PM US Eastern Time ( July 22, 2011 3:00 AM GMT)

Presenter:
Gaurav Aggarwal (blog)

Live Meeting Link:
https://www.livemeeting.com/cc/UserGroups/join?id=R9J5WW&role=attend&pw=Zf%2B-Q%6098k

Microsoft – Readying on “Hadoop”
This session will provide overview of different options on Microsoft platform for Hadoop. Session will cover pro and cons of different options and architecture level coverage of HPC, PDW and Dyrad.

Gaurav Aggarwal
Gaurav Aggarwal is a Senior Architect with 16+ years of experience. He has been working with Microsoft for the past 7+ years. He helps senior management in aligning their IT capabilities to innovate and achieve momentum for their business goals. He has been architecting and delivering solutions using various technologies for nearly 17 years. He has delivered solutions in the areas of Investment Banking, Insurance, HR, Manufacturing, Retail Banking and ITES. These solutions were based on a wide range of technologies including Microsoft and Java platform based solutions.

He is a Microsoft Certified Master (SQL Server), Microsoft Certified Architect (SQL Server), and Microsoft Certified Architect (Solutions), IASA Certified Architect Professional and a practicing Six Sigma and PMP (Project Management Professional) professional.

Gaurav’s blog:
http://blogs.msdn.com/b/gauravagg/

Add to your calendar

PASS Data Architect presents Karen Lopez–You’ve just inherited a Data Model: Now What?

 

Please come join the PASS Virtual Chapter for Data Architect on Thursday June 16th at 2PM Eastern for InfoAdvisor's principal consultant  Karen Lopez sharing her experience with inheriting a Data Model.

This is the third month in a row the rebirth of this virtual chapter has hosted. The schedule is booked thru December, Robert, Lorra and I are excited about the presentations and volunteers that have helped this chapter teach SQL People about data architect.

The foundation of data is very important and some loss site of the basics while enjoying all the new stuff in SQL Server.

Subject:
You've Just Inherited a Data Model: Now What?

Start Time:
Thursday, June 16, 2011 2:00 PM US Eastern Time (7:00 PM GMT)

Presenter:
Karen López (blog|@datachick)

Live Meeting Link:
https://www.livemeeting.com/cc/UserGroups/join?id=T4ZKMM&role=attend&pw=Q%3A%26%24h%28-9m

You've Just Inherited a Data Model: Now What?
The good news is that someone else has done the hard work of architecting a data model and you just have to take on minor maintenance…or is that the bad news? Or have you been tasked with implementing a pattern or industry standard data model? Perhaps a team member has sent the world's best resignation letter and won't be helping you with the model. Learn the 5 steps you MUST take before working with a new data model.

Attendees will also receive a detailed checklist for the 5 steps.

Karen López
Karen López is a principal consultant at InfoAdvisors, Inc., a Toronto-based consulting firm. Karen is a frequent speaker at DAMA, SQLSaturdays and PASS conferences, including 24 Hours of PASS, the PASS Summit and PASS SQLRally. She has 20+ years of experience in project and data management on large, multi-project programs. Karen specializes in the practical application of data management principles.

Karen is also the ListMistress and moderator of the InfoAdvisors Discussion Groups at www.infoadvisors.com.

Karen's blog: http://www.infoadvisors.com/Home/tabid/36/BlogID/1/Default.aspx

Twitter:@datachick

Add to your calendar

More #SqlRally pictures and observations

Wow, it is a picture of me taken by Tim Mitchell, a very cool dude from Dallas and SSIS MVP.

 

This was at the raffle outside and my friend from Wheeling, WV John Sterrett.

Geaux Tigers!!!

   SQLLunch and ex-MVP Patrick Leblanc

Picture taker Tim

Kevin Kline in the Pre-con for Personal Development I attended working on leadership and manager skills

Me at a distance, at least 30-40 people in the picture, so there was probably close to 100. That is crazy, but really cool.

Up next #SqlSat77 in Pensacola.

God Bless,

Thomas

SQLRally, what a Ride

The week was a wonderful learning, networking and speaking experience.

It all started with Kevin Kline { Blog | Twitter } giving a Personal Development session on Leadership and Management. Got some great statistics and experience from the 30+ attendees at this Pre-Con.

   

Wednesday and  Tuesday evenings were spent visiting with SQL Peeps at various places. I meet lots of new SQL People that are joining our community. 2 from Ohio – Tim and Dustin – were great to hang around and share about  what we do sharing SQL information and tools. Both were amazed by the end of Friday of everything that is available to them from SQLRally, SQLPass and SQLSaturdays plus blogs and webcasts. I am sure they were just like me when I first experienced all the sharing.

http://www.flickr.com/photos/62704669@N07/

Thursday, I spent the day in sessions and visiting vendors. Brian Mitchell from MS showed Parallel Data Warehouse with the single SKU for everything to some internals and client tools. A no show from a speaker (black list) cleared some time to go by Confio, Idera and Melissa Data booths.  During the week , I spent sometime with Heather from Idera about their new ACE program. Vicki and another developer from Idera chatted about the SQLdm product. I was also able to demo Ignite for David at the Confio booth. Brian McDonald did an awesome job with SSRS Boot camp, good presentation and flow even though he was late.

   

Lunch each day was a great box lunch with a big cookie which over road the apple for me each day. I made sure to sit with different people each day to chat about real world stuff. The last day I sat with SQL People from Utah, California, Indiana, Florida, North Carolina and of course me from Louisiana. That was great. SQLRally hit it good with this one.

Thursday afternoon I saw Scott Shaw talk about The Enterprise SQL DBA, Stacia Misner demo SSRS visualizations and Jen Underwood explain the workings of PerformancePoint in SharePoint. All in one afternoon!!! I went back to my room for room service and a review of my presentation on 3rd Normal Form: That’s Crazy Talk!!! One last review and some practice on the Visio diagrams to demo and then off to bed.

Friday at 8:30 I presented to a 2/3s full room (probably 60-70) people about database normalization. Louis Davidson (DrSQL) { Blog | Twitter } was the ‘moderator’ for my presentation. What a blessing to have a guru in the industry watching me, I hope he has some constructive criticism for me!  Lots of conversation after the session and plenty of comments the rest of the day from others. It was the first time I felt I did not leave anything out or felt I tried to explain too much. I believe it helped to run through the session with developers at work before the Rally, and take some things out to reduce the session to under an hour. I noticed a lot of sessions either did not have enough time (60 minutes) or the presenters needed to reduce the content to finish on time and not leave the attendees missing something.

The rest of the day was spent in Eric’s SSIS Data Flow Logging, Devin Knight performance tuning SSAS, Julie Smith’s Cool Tricks for SSIS, Adam Jorgenson guiding Julie in a cube creation and Excel reporting and finally watching Jeremiah flying through SQL Server internals. Man, that is a lot. But, I have learned from past SQLPass Summits that I should just try to bring 2-3 new ideas back to the office. The first week back, implement one to show the value to the bosses. Play with the second one for a month and then implement. And summarize the last to the some group or department.

God Bless,

Thomas

My PASS Summit 2011 Submissions

Before I list what presentations I have submitted this year to PASS, I want to briefly talk about SQLRally.

SQLRally gives me a wonderful opportunity to present on Database Normalization. This passion of mine is expressed with others in the SQL Server community and I see a really need for others to teach the basics of normalization. It is not an academic knowledge of 1, 2, 3… but more importantly to help performance, reporting and now business intelligence. Another session I have started working on is Database Standard Operating Procedure which came from questions I have received from attendees at SQLSaturday and in-house at Amedisys.

The next opportunity is SQLSaturday #77 in Pensacola. Karla and gang looks like the have many sponsors and the schedule is set. I am going to attend 4 others sessions while presenting at 2.

So here are my PASS Summit submissions:

3rd Normal Form: That's crazy talk!!!

How did the development world conclude that an integer is the best primary key? What has been added to SQL Server in 2005 and 2008 to help change the way database design has evolved over the years? Can we still use a VarChar(xx) for a primary key? What is the difference between a lookup and Parent/Child relationship? What is an example of a Many-to-Many relationship? What is 4th and 5th normal form? This session will go through the history of 22 years of experience with various database designs – normalized and denormalized. The discussion will include the benefits and forward looking that should be required for using various design techniques. The flow will be a discussion with attendee participation to share success and pains in database development, leading to standards for all of us to take advantage of while designing databases.

Execution Plan Basics

This will be a Beginners session highlighting the starting point for using the execution plans from SQL Server to assist in query tuning. Briefly, we will look at the history to get an idea of how Microsoft has improved the display through Graphical Plans and Missing Index suggestion. Examples from the AdventureWorks database will be shown so anyone can take the queries after the session to try on the own, which will be encouraged. Questions will be answered like: What is the difference between a Table and Clustered Index Scan? What is a Lookup? How do you improve performance of Lookups? What are the different types of Loops? How to get more information from the Plan with the properties window? What other options are available in Management Studio to assist with query tuning?

Transition from DBA to BI Architect

Database Normalization and Dimension Modeling are the same but different. Development in today ‘s larger industries require the design and analysis of Online Transaction Processing (OLTP) to take into account Data Warehousing/OLAP (Online Analytical Processing). My transition from Senior DBA to BI Architect at Amedisys has been a process of sanding the rough edges of my passion from fully normalized databases. The Dimensional Modeling started a re-tooling of my mind to look at end result analytics and statistics from smaller and smaller transactions. From previous experience, I know I am not going to get it right the first time. Lessons learned will mold me into a great BI Guy.

RML Utilities\SQL Nexus

Microsoft Support (CSS, formerly PSS) has made available a utility to do an analysis of a trace file. This session will go through an explanation of how to use this utility and interpret the results. RML Utilities can answer questions about where the most resources are being consumed, queries that are responsible for heavy usage, changes in plans during trace and if queries are running slow in comparison to other traces

Good to all submitters and look forward to seeing y’all all in Orlando and Seattle.

God Bless