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

SQLSaturday #77 Pensacola, sp_WhoIsActive & EFM

2 sessions have been selected for SQLSaturday #77 in Pensacola, FL – Execution Plan Basics and Transition From DBA to BI Architect. I really do not believe Karla has a real job because of everything she has been doing in the SQL Community, just kidding Karla.

   

The first session goes through some history of query tuning tools from Microsoft and then into a No Fluff, Just Stuff (I am boring this expression from Patrick LeBlanc’s SQL Lunch) examples in SSMS. I am really liking this session, and hope to expand for intermediate level. Need to get a SQLSaturday to accept the next session somewhere.

The second session will be the third time I have talked on a transition at my current employer over the last year. From Database Normalization to Dimensional Modeling has been enlightening. I use the Kimball books as references and some query tuning experience that has helped me gain respect from others in the BI department.

Adam Machanic has been doing a series on his ever expanding and brilliant monitoring stored procedure SP_WhoIsActive.  Part 15 of 30 helps explain CXPACKETS which is a mysterious wait that has taken me a year or 2 to full understand. Every time I read a post about CXPACKETS I learn a little more.

In between SQLRally and SQLSaturday, I am taking a mentoring class for EFM (Education for Ministry) in Robert, LA at the Solomon Center. EFM is a 4 year course of learning about the Old Testament, New Testament, Church History and Theologians in the Anglican (Episcopal) Church. I hope to be an assistant then mentor in EFM in the near future. I completed the course from 2003 – 2007.

There is nothing better than working on the Spirit and Mind from the Bible. Yes, even better than SQL Server and work.

God Bless

Data Architecture Chapter restarted

 

Here is a link to @SqlSoldier blog on the restarting of the PASS Data Architecture Virtual Chapter.

I was involved with the Performance Chapter for about 6 months, until I changed positions at Amedisys.

http://www.sqlsoldier.com/wp/sqlserver/darcvcreborn

If you would like to speak, please email us from the Virtual Chapter site

PASS Data Architecture Virtual Chapter

This weekend is time for some spiritual food listening to speakers talk about the walk with God. It will be nice to take a break from life, and work on the spirit.

God Bless,

Thomas

Visit the site: http://dataarch.sqlpass.org/
Follow us on Twitter: @DArchVC
Like us on Facebook: Data Architecture VC

Next Meeting Announced

Subject:
Database Normalization

Start Time:
Thursday, April 21, 2011 12:00 PM US Central Time (6:00 PM GMT)

End Time:
Thursday, April 21, 2011 1:00 PM US Central Time (7:00 PM GMT)

Presenter:
Thomas LeBlanc (blog|@TheSmilingDBA)

Live Meeting Link:
https://www.livemeeting.com/cc/usergroups/join?id=28NR92&role=attend&pw=PTSs%26%5E%282Z

Database Standards SOP
Join Normalization nut Thomas LeBlanc for a review of a standard operating procedure used among DBAs at an employer. See the changes he made after joining the BI group at this employer. The session will go through naming conventions, check list for creating a table, formatting in stored procedures, and more. A brief preview of the SQLRally talk 3rd Normal Form: That’s Crazy Talk!!! Will be given about Lookup tables. This discussion comes from 21+ years of developing databases for application developers. The use of identity columns for primary keys, and the need for a unique constraint on transaction tables that do resort to ID columns will be covered.

Add to your calendar

SQLRally

Well, I have my flight booked and registered as a speaker. Looking over the speaker provided schedule, there are many sessions to choose from in the Business Intelligence track. My first goal is to get as much SSIS and Data Mart design training as I can over the 2 days of sessions.

I still have not decided about what Pre-Conference session I want to attend. Grant Fritchey’s Query Tuning is number one on the list, but learning BI from the Pragmatic Motley Crew would probably benefit my career as a better choice. Then, there is Kevin Kline teaching Personal Development. Since I will be attending regular BI sessions, I am leaning towards Kevin Kline’s PD, with Grant a close second (you can never get too much performance tuning advice).

This past week at work, I was tasked with adding columns to a Fact table that a consultant started to work on before the end of the contract with their company. I noticed performance problems with the additions to the Inserts and Updates. After investigating, I found out using NOT Null and Default on the Alter Table Add column was best since some of the existing rows were not going to have values. The ETL had to be changed for Daily and Monthly updates.

The Monthly Updates were taking between 4 and 8 hours. After digging a little more, the Monthly Updates where not updating just the current period, but all data. By Adding a WHERE CURRENT_PERIOD = ‘Y"’, the processed reduced to 10 minutes.

Also, a fact table had 6 Non-clustered indexes, no primary key and no clustered index. This is taking a little more work because of the structure of the fact table, but an additional clustered index after removing all non-clustered. Then, adding Non-cluster indexes back after updating their structure to exclude the new non-clustered columns were appropriate, help take the 9-10 hour process down to 3 hours 10 minutes. Also, a big thanks to the Network/Infrastructure group that has enabled us to have a dev/test/prod environment that are almost all equal. That is a blessing not many shop s have.

Unbelievable!!! Whenever I am able to work on something like this, I have a tendency to try and tune more and more and more… never finishing. But it also reinforces all the training and real world experience in action and validate I am on the right track.

There is so much to learn in the Database world, but stopping and concentrating on a few items to become an expert gets harder and harder.

God Bless,

Thomas