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