PASS Data Arch VC presents: Intro to Master Data Services by James Serra

Please join this Thursday Dec 20th 2012 at noon central for Business Intelligence expert/consultant James Serra talking Master Data Services. This new feature of the Microsoft BI stack is sure to help elevate your skills with data in SQL Server 2012.

Introduction to Microsoft’s Master Data Services (MDS) by James Serra

Online Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=4HQDRK&role=attend&pw=p%7E%29%7B%22X%2B4h
RSVPURL: http://dataarch.sqlpass.org/

 

When  iCal
UTC : Thu, Dec 20 2012 18:00 - 19:00
Event Time : Thu, Dec 20 2012 13:00 - 14:00 Eastern Standard Time
Your Local Time: Thursday, December 20, 2012 12:00 - 13:00

Where http://dataarch.sqlpass.org/

Featured Presentation

Introduction to Microsoft’s Master Data Services (MDS)

Speaker: James Serra, BI/Data Warehouse/MDM Architect and Developer Serra Consulting Services

Summary: Companies struggle with consolidating the same set of data from multiple systems to accurately report on critical business information. For example, having customer lists in multiple systems that often have the same customer in more than one list, sometimes with a different spelling. Master Data Services is bundled with SQL Server 2012 to help resolve many of the Master Data Management issues that companies are faced with when integrating data. In this session, James will show an overview of Master Data Services 2012, including the out of the box Web UI, the highly developed Excel Add-in, and how to get started with loading MDS with your data.

About James: James is currently a Data Warehouse/Business Intelligence/MDM architect and developer with over 25 years of IT experience. James started as a software developer, and then was a DBA for 12 years, and for the last six years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant and contractor. James is a noted speaker, blogger, and contributing editor for SQL Server Pro magazine.

PASS Summit 2012 - Done!!!

Thursday afternoon

Thursday afternoon concluded with How to run SSRS in Sharepoint integration mode, Enriching Tabular with DAX and Monitoring SSAS. The best was the last.  Tim Peterson (SolidQ) shared his 12+ years working with SSAS to give us 3 ways to monitor SSAS – SQL Profiler, PerfMon and a CodePlex module call ResMon. Most of the time was spent showing Profiler while running MDX after clearing the Cache, File Cache and Script Cache. These three demos paid for the whole conference. I love watching presentations from seasoned experts.

Below are pictures from Day One pre-conference session on the left – Rob Colledge, Day Two meeting with PASS Virtual Chapters leaders – Denise McInerney and Ryan Adams talking about promoting Virtual Chapters.

    

The DAX sessions was demoed in Excel 2013 and PowerPivot (which I am not using), so it was kind of disappointing and the SSRS integration was mostly in 2008 R2 (he did show 2012 at the end) and all on one machine. It would have been nice to show deployment on multi-server SharePoint farms because that is what most enterprises use. Even though I did not get what I wanted (gotta stop the selfishness), both were great sessions with experience presenters and 2-3 bits of information I had not known before.

One the left is Chuck Heinzelman and the right is Tim Mitchell preparing for his SSIS session.

    

Friday morning

The last morning started with conversations at breakfast with some Germany user group leaders where we talked a lot about last year’s summit and SQLSaturday. They are hosting I believe #170. The first session was Mike Davis(a consultant from the north) showing demos of starting and improving a SSAS Tabular Modular cube. Do you call it a cube or semantic model? His examples where great and very simple to understand. Maybe that has more to do with tabular than his demos.

The second part of the morning was Tim Mitchell showing SSIS and some patterns to use for handling errors and validation of data. I really like Tim’s presentation skills. You can tell he has been doing this for some time. I probably took 2 pages of notes to use back at work. He showed event handling and properties like propagate that I had never seen or use before.

Lunch was at the Birds of a Feather table on Database Development. The attendees were from South Africa, San Diego, Louisiana and others. We talked mostly about our backgrounds and some of the more comical situations at work where we learned some lessons. There was more laughter than seriousness, which helped with digesting the food (not that it was bad, the food is top notch). This is were I try to practice some listening and slow down on the talking. While most tables started to empty out, we were still going at it. That was a nice way to end the day.

Below is Peter Meyers and Matthew Roche, the middle is Carlos Bossy and the right is a view from the bloggers table during the WIT luncheon.

   

I am typing this at the airport with the sun in my eyes, but it does not bother me because of the great time I just had. You know, I never got out in the city at all and spent most of the time with SQL Server professionals. I do not even want to use the terms geeks or tweeps or SQLFamily, we are definitely a group of IT professionals that are employed to do a serious job of protecting, mining, speeding and handling data the runs business profitably and governments effectively yesterday, today and tomorrow. I love my job!!!

Mount Rainer from the plane.

     

It was also nice to have attendees come up to me later and say how much they enjoyed my session. I always reminded them to fill out the evaluations to help me(us) with future sessions.

God Bless!!!

Live Blog: PASS Summit–WIT Luncheon

WIT – Women In Technology 10 anniversary PASS Summit Luncheon

Please go to this link for more info on the speakers and content. I am blogging and tweeting from this luncheon to help with exposure of the message and to learn more myself.

Louisiana has a WIT community - http://www.la-wit.org/

11:55AM

Room is filling up, many conversations at tables. Bloggers table is full, actually overflowing and the session should start at 12noon pacific

12:05PM

PASS president Bill introduces Wendy as moderator. 10 years for WIT. Room for 700 people and Wendy reviews how they would recruiter people like the PASS Ambassadors of today. Wendy thanks SQLSentry and volunteers for support. Wendy also recognizes SQLSaturday organizers for running these events.

12:10PM

Stefanie is keynote speaker of WIT luncheon and says how overwhelmed with the attendance. Talks about have 60+ at the first luncheon and now 700+. Talks about her early years in technology and using COBOL.

12:15PM

Up next is Wendy, the VC leader on the WIT chapter. Talks about in 2002 search for a woman at PASS. But, now that has changed. A 24-HOP of women speakers have helped more women speaker at PASS, SQLSaturday and local user groups. Now, promotion of women in IT has grown. About 15% at PASS Summit are women in 2011 and 2012. Denise wants to she more woman create the technology not just support. she is concerned about the amount of women leaving tech jobs.

12:20PM

Kevin Kline is up next. Kevin has been involved with PASS since it started. Talks about the early days not having the money to help but we do have the attitude and heart to help the community. Leads the conversation of not being segregated and compares the different places that have welcome signs but a exclusive. PASS is goodness and we want to share with you

Mentions the need to have women in IT teams in order to bring the a different dimension to the task at hand. As a father of teenage girls, he is trying to find away to encourage them in IT. Always a Work In Progress.

12:30PM

Jen, the PASSion award winner up next. Very soft spoken woman, but experienced with the BI stack from Microsoft. Scottish accent is strong. Talks about the IT job helps her as a single mom to support her family. More diversity helps team dynamics. Mentions government studies in Scotland and US.

12:35PM

Kendra (MCM and MVP) talks about her search for the comfortable in the IT field and the different jobs she worked in. As she started speaking at SQLSaturday, She realized that when an attendee came up and asked more questions, being a consultant was a possibility. The encouragement she gives is start talking about what you know.

Rap Up

Question and answer period continues…

This luncheon was sponsored by

 

WIT Panelists
Stefanie Higgins
Stefanie Higgins has been a PASS member since 1999 and was very fortunate to have had the opportunity to serve as a PASS volunteer and eventually a PASS Board member from 2000 – 2005. She is one of the founders of the WIT luncheon event at PASS and as a mom of four she has a passion for encouraging working women especially in the area of work/life balance. Stefanie has worked as a Sr. DBA for IndyMac Bank, The Walt Disney Co. and as an IT Manager at DirecTV. She is an MCITP SQL Server 2008 R2, currently working as a Sr. DBA for Disney and enjoys telecommuting days from her home in beautiful San Diego CA.

Kevin Kline
Kevin Kline is a well-known database expert and software industry veteran. A long-time Microsoft SQL Server MVP and noted leader in the IT industry, he is a founder and former president of PASS and the author of popular IT books such as SQL in a Nutshell. Kevin is a top-rated speaker at industry conferences worldwide.

 

 

Kendra Little
Kendra Little is all about data, communication, and creativity. Kendra nerds out on SQL Server full time as a founder of Brent Ozar PLF, LLC, a boutique consulting company. She is a Microsoft Certified Master in SQL Server 2008 and a Microsoft MVP.

 

Denise McInerney

Denise McInerney began her career as a SQL Server DBA in 1998 and currently works as a Staff Technical Data Analyst at Intuit in Mountain View, CA. Denise, who serves as a Director-at-Large on the PASS Board of Directors, started volunteering for PASS in 2003. She founded the PASS Women in Technology Virtual Chapter (VC) and served as VC chairperson or co-chair for 5 years. Denise received the PASSion Award in 2007.

 

Jen Stirrup

Jen Stirrup is a SQL Server MVP, with a Bachelors degree in Psychology and two Masters in Artificial Intelligence and Cognitive Science. Jen holds a Europe-wide volunteer role in helping to co-ordinate 'Women in Technology' events as a SQLPass volunteer, which covers WIT events in disparate countries from Sweden to Poland. Jen is a veteran SQL Server Developer with 15 years SQL experience in delivering end-to-end Business Intelligence solutions. Jen blogs for SQLServerPedia as well as her own blog. She has presented in many SQL Server User Groups in the UK.

PASS Summit 2012–Thursday morning

Using SSIS 2012 for ETL in a Data Warehouse

Well, I feel great relief that my session has been completed, and completed successfully. The room was full with people on the floor and along the walls. That was great considering Peter Meyers and Devin Knight had done similar sessions 2 different times before mine. Visual Studio blew up once (at the beginning) and I made a mistake with one OLEDB connection, but was able to recover both times. I had 2 attendees keep track of all the new features of 2012 I demonstrated, and one said 22 and the other had 26. Not bad for 75 minutes. Doing live demos of SSIS 2012 is not the easiest task, but I think attendees rather see demos than slides.

Tuesday – community involvement

Tuesday morning began with with PASS Virtual Chapter leaders, then SQLSaturday leaders and volunteers. The amount of people in these meetings were more than double the amount last year. Folks, there is a lot going on with the PASS SQL Server community. Please get involved. The afternoon was with House of Bricks talking about the methodology, best practice and monitoring of SQL Server on VMWare. I was expecting more technical demos, but it was still worth my time.

After some exercise, I helped man the Virtual Chapter area of the Welcome Reception and signed up 10 or so new members. The time was also spent talking to many attendees I knew or just meet. Hearing other SQL Server professionals talk about their work is indeed interesting and great for knowledge sharing. The room was overflowing and time went by quickly. The evening concluded with the annual SQLServerCentral.com Casino night. Of course, I lost all my play money chips, but the conversations with DBAs was worth it.

Wednesday

This morning started in the PASS Community center at 7AM. Right next to the dining area help with grabbing a quick bite. As I finished at my table, Buck Woody walked up to continue the conversation with the First Timers. I spent some in the speaker room rehearsing my demos for a 3PM session. Carlos Bossy came in as well as Matt Mason. We talked SSIS and Data Modeling which was great. I missed the Keynote, but here is a good link to a BI perspective view – Marco Russo.

The first session was Peter Meyers and Matthew Roche presenting on 10 Extraordinary Things to Achieve in SSIS. The duo was a great combination with a good talker and a demo guru. Lunch was at the Baton Rouge SQL Server User Group table where I meet up with Patrick LeBlanc and Mark Verret. There was @SQLCajun and others from Mississippi at the table. Brian McDonald came by to talk with Patrick. Mark’s colleague was there from LSU and talked about us speaking about real world uses on Microsoft BI with their students.

The day concluded with SSAS Storage with Stacia Misner then my presentation at 3PM. The last session I attendee was SQL Server and SharePoint: Best Frienimies(sp?). A SQLCAT member did the presentation and she related it back to her DBA days. I am still not convinced about some of the Best Practices with maintenance but I understand more about why the want us to follow these suggestions 

Thursday

I am starting the morning in the PASS community lounge at 7AM helping new people get involved. Next is the massage chair in the Seattle Convention center. I am hoping to spend an hour there.

The first session is with Carlos Bossy who I meet last year with Steve Jones and he even came to Baton Rouge for our 3rd SQLSaturday. He is going to talk about planning and designing the underlying data base model that is needed for an effect Semantic Model. Next, I am live blogging and tweeting during the WIT Luncheon sponsored by SQLSentry. I rest of the day is up in the air, but the evening concludes with a Microsoft Appreciation event at Seattle’s Music Experience museum.

PASS Summit 2012–Day 1

Morning

Could not sleep past 4:30AM this morning, daylight savings time and 2 time zone changes did not help. So, exercise now in the morning and off to Summit for 7:30AM.

Meet 2 new Summit attendees in the elevator from Los Angles working for Stamps.com. They were both developers of web services for the shipping industry. They were heading to Louis Davidson’s (@DrSQL) Relations Database pre-con. Encouraged them to network and get as much from after hours networking as the sessions.

I am attending Rod Colledge pre-con on BI Dashboards. Rod did a session during 24-Hours of PASS that I was able to host and I am excited to learn for a pro. The room is starting to fill up at 7:25AM and an attendee from SQL Saturday in Baton Rouge and Houston is sitting next to me. He has some interesting things to say about SAP and Data Warehousing

PASS Summit 2012–My Schedule

Using SSIS 2012 for Data Warehouse ETL

I have been fortunate to be selected this year to speak at the PASS Summit 2012 in Seattle. This presentation will go mainly over 3 new features: The Change Data Capture and DQS controls as well as deploying SSIS projects/packages to the SSISDB catalog. Throughout the presentation, there will be usage of 10-20 more new features all depending on the demo and questions. 75% of this presentation is demos, so come along for the ride. I love presentations that show how to use SQL Server.

Pre-conference Session – Monday

After arriving on Sunday afternoon, I will go to Rod Colledge’s session on BI Dashboards with Dashboard Designer and PerformancePoint. This will satisfy the first goal of the conference; to take back what I learn and create 2 sets of dashboards for a project at work.

Monday night, I am going to the networking dinner Steve Jones and Andy Warren are hosting. I did this last year and it was a great way to sit with 4-6 SQL Server geeks and enjoy a dinner and conversation.

Community Involvement – Tuesday

Tuesday morning, I will be attending 2 round table discussions with Virtual Chapter leaders and SQLSaturday leaders/volunteers. I am the chair of the Data Architecture VC and have helped with the past 3 SQLSaturday’s in Baton Rouge and spoke at the first 3.

House of Bricks is hosting a SQL Server on VMware boot camp which I have decided to attend to help understand the configurations for the VMs we run at Turner Industries in Baton Rouge.

Tuesday night I am going to float between the Welcome Reception, SQLServerCentral.com/Redgate casino night and a speaker reception.

Main Conference – Wednesday

I am going to start out the morning at the Community Area booth which has been expanded this year in size. It will be great to say hi to Karla and other PASS workers and sign people up for Virtual Chapters and PASS.

The first session I am attending is Sean Boon’s Building Compelling Power View reports Lunch will be sitting at the Virtual Chapter tables talking Data Architecture.  The afternoon starts with Stacia Misner’s SSAS Storage followed by my SSIS 2012 presentation and ends with SQL Server and SharePoint: best Frienenies which should be a fun session.

Wednesday evening will be talking with vendors at their reception for attendees which was a hit last year.

Thursday Sessions

The morning starts again at the Community area early in the morning. The first session (10:15) is Carlos Bossy talking Data Modeling Best Practices for Enterprise Tabular Models which should help me with Data Mart designs for PowerView reports which is another goal for work. During Lunch, I will be Live Blogging and Tweeting from the Woman In Technology Luncheon which should be fun.

The afternoon starts with Running Reporting Services in SharePoint Integration Mode which Turner just had a consultant (SparkHound) install with SQL Server 2012 and SharePoint 2010. I really wanted to see Deeper Tabular Models with Wayne Snyder (who is a cool dude), but I think the SSRS is more valuable for work. I can always watch the recorded session when I get the DVDs.

The afternoon ends with Dive into the Query Optimizer and Monitoring Analysis Services which are both part of my daily checklist at work. Thursday evening, Microsoft is sponsoring a trip to Seattle’s Music Experience museum for 2-3 hours. The last couple of years it was at a GameWorks, which was pretty cool. I am expecting a good networking evening this night.

Friday Sessions and Homebound

I am leaving around 3:30PM Friday to spending time with family and friends on Saturday, so I will only be at 2 sessions plus lunch. I am getting married in January, so I need to get back to Baton Rouge, plus we (LSU) plays Miss St. at home that Saturday.

The first session (8AM) will be Power(view)ful Tabular semantic Model Development with Mark Davis, then Tim Mitchell learning me on ETL Gone Bad: Handling Errors and Data Anomalies in SSIS. These are 2 great speakers and consultants.

Lunch will have me sitting at a Birds of a Feather table on Data Warehouse Modeling and SSIS ETL.

WOW!!! That is a lot

That seems like a big schedule, but I am not evening doing everything that is available. The first year I attend PASS (07 in Denver), we spent time at the Microsoft First-Aid room working with replication developers on a problem in SQL Server 2005. The following year, I spent time in the Virtual Lab learning SQL Server 2008 new features. There is also visiting with vendors and collecting swag. The exhibition hall is sold out. Then, there are lightening talks which help speakers get a start with presenting as well as Microsoft Chalk Talks. There are pre-conference sessions on Monday and Tuesday.

The PASS Summit is full of stuff to do during the day and at night. I know a lot of people like #SQLKaraoke and Photo walks plus a #SQLRun on Tuesday morning. It is fun watching the SQL Kilt day and Thursday is wear your SQLSaturday shirt day.

Lastly, meeting and saying hi to friends like John Sterret (That is the back of his head above), Scott Shaw, Carlos Bossy, Patrick LeBlanc, Devin Knight, Erin Welker, Grant Fritchey, Thomas LaRock, Louis Davidson, Sean and Jen McCown, Wesley Brown, Kevin Boles and the list goes on and on. The SQL Community is the best group of technology geeks that love to share the #SQLLove.

Hope to see you there, and please come say hi and introduce yourself to me.

God Bless

Below is a picture from the first SQLSaturday in New York, and little did I know I was with Thoams LaRock, Adam Jorgenson, Grant Fritchey, Andy Leonard and Robert ?

When a Covering Index no longer covers

I received an email from my boss on the morning supervisors approve timesheets. He stated that they are complaining about an error: Deadlock. Searching the SQL Log, I was able to get the details of the deadlocking. We have Trace Flags 1204 and 1222 turned on which provides the details.

Once I got the details and having followed Bart Duncan’s Deadlock Troubleshooting, I found the victim and successful DML statements.

Next, I started a trace to get some parameter values for the victim T-SQL. By the way, the UPDATE statement was the higher priority statement that succeeded and a SELECT was the deadlock victim. Not the classic UPDATE/UPDATE example so many people use to illustrate deadlocking. When I got the Execution Plan for the victim, I noticed the Seek was on a Non-Clustered index with the INCLUDE option (Covering Index). After the Seek, there was a lookup on the Cluster Index.

Now, months ago I added the covering index to help this same SQL statement, but now it needed 2 extra columns from original suggested index.

What changed? Well, before I got the answer on what changed, I dropped the index so the users could proceed and not get the error, with some performance issues (it was taking 5-6 seconds instead of less than one)

What Changed? The previous week, the development team released a new version of the application and add 2 columns to the SELECT statement.

So, here is an example of what happened with the Adventure Works database.

USE [AdventureWorks2012]

GO

SELECT [SalesOrderID], [RevisionNumber], [OrderDate], [DueDate]

  FROM [Sales].[SalesOrderHeader]

  WHERE OrderDate Between '20070101' AND '20070101'

If you run the above query with Include Execution Plan, you will get a Missing Index like the following:

USE [AdventureWorks2012]

GO

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

ON [Sales].[SalesOrderHeader] ([OrderDate])

INCLUDE ([SalesOrderID],[RevisionNumber],[DueDate])

GO

The Execution Plan is using a Clustered Index scan to find the data with a cost of 0.54

Adding the index, the Execution Plan now does an Index Seek with a cost of 0.0033. Big improvement!!!

Now, when I add a column to the SELECT query that is not in the Covering Index:

The INCLUDE column index (covering index) is used in a SEEK, but then a Key Lookup is added to get the additional 2 columns from the Clustered Index.

So, how does this cause a deadlock. I have seen this problem many times over the last 5-6 years.

The SELECT statement starts to SEEK the Covering Index at the same time an UPDATE statement locks the clustered index to update, then tries to update the covering Index. But, the SELECT statement has a shared lock on the Covering Index (Non-clustered Index) that is now trying to place a shared lock on the Clustered Index, which is locked by the UPDATE statement. BOOM!!! Deadlock!!!

How do you fix this?

First, you can add the 2 columns to the covering index and the SELECT no longer needs a Key Lookup on the clustered index. This is fine, but I wish it could be found in development and not production.

Or, you could just remove the Covering Index (like I initially did) and find other ways with the development team to improve performance. I do not believe they even knew what I did when I added the Covering Index, nor did I know that had added some additional columns to the SELECT statement.

Part II - Louis Davidson (Dr SQL)–Designing for Common Problems in SQL Server…continued

The PASS Data Architecture Virtual Chapter will host part II of Designing for Common Problems in SQL Server by Dr. SQL Louis Davidson as he helps application and database developers design and implement SQL Server databases.

I saw Louis at the first PASS Summit I attended in Denver in 2006. He and Paul Nielsen help me see where data models and normalization can increase the efficiency with storing and retrieving data.

They also encouraged me by their presentations to start talking about database design at work and in the SQL community.

Please join us on Thursday October 11th at Noon Central for another informative presentation.

Subject: Designing for Common Problems in SQL Server Part II 

Start Time: Thursday October 11th, 2012 12:00 PM US Central Time

End Time: Thursday October 11th, 2012 1:00 PM US Central Time

Presenter: Louis Davidson

Live Meeting Link: Live Meeting

Designing for Common Problems in SQL Server II 

In this session, I will do a design and code review of several common patterns of solving problems that a typical programmer will come up against. Problems like coding for hierarchical data, data driven design, dealing with image data, structure generalization, user specified schemas, dimensional reporting, and dealing with uniqueness beyond what you might deal with using a simple uniqueness constraint might allow you to deal with.

Louis Davidson

Louis has been in the IT industry for 17 years as a corporate database developer and architect. He has been a Microsoft MVP for 8 years and has written 5 books on database design and has spoken on the topic of database design and implementation at SQL PASS, SQL Rally, many SQL Saturday events, as well as Devlink. Currently serves as the Data Architect for the Christian Broadcasting Network supporting offices in Virginia Beach, Virginia and Nashville, Tennessee. Louis has a bachelor's degree from the University of Tennessee at Chattanooga in computer science. For more information please visit his website at drsql.org.

24 Hours of PASS: Hosting Rod Colledge–BI in Sharepoint

The Virtual Chapters of PASS are asked to host sessions during this Fall’s 24 Hours of PASS preview for the PASS Summit in Seattle, WA Nov 5th-9th. The Data Architecture VC gets to host Rod presenting on BI Architecture With SQL 2012 & SharePoint 2010. This is a good preview of Rod presentation skills for the pre-conference session, Dashboards with Dashboard Designer & PerformancePoint Services 2010 [BID-398-P], he is doing on Monday during the Summit. I am actually attending the session to help with some PerformancePoint dashboards I am working on at Turner.

The Data Architecture virtual chapter has monthly webcast by SQL Server experts in the community. October will have Dr. SQL – Louis Davidson – presenting part 2 of his session on Designing for Common Problems with SQL Server.

The 24 Hours of PASS have being going on for the past 3-4 years with the fall session representing some of the pre-conference and spotlight sessions for the PASS Summit. I really enjoy going to at least one of these precons each year while at the Summit. It has always been a good one day training on a subject.

Please go register for 24-Hours of PASS and the Summit and get some training for SQL Server

BI Architecture With SQL 2012 & SharePoint 2010
This session will cover new BI features in SQL Server 2012 including Power View, PowerPivot, Report Alerts and SSRS Integration with SharePoint Server 2010. It will then focus on the importance of Self-Service BI and how it fits into an overall enterprise Business Intelligence architecture. This session will be rich with demos of all the new BI tools and the use of PerformancePoint Services to create Business Intelligence Dashboards. Attend and discover the awesome power of the latest release of the Microsoft BI Platform.

Rod Colledge
Principal Consultant, StrataDB
Rod Colledge is the Director and Co-Founder of StrataDB, a leading Australian-based provider of SQL Server solutions to clients in the Corporate, Government, and Education sectors. With over 15 years of SQL Server experience, Rod's ongoing commitment and depth of knowledge was recognized in 2010 with a Microsoft MVP award for SQL Server. Rod leads StrataDB's Business Intelligence practice and has authored or contributed to three books, including the "SQL Server MVP Deep Dives" series. He's a sought-after speaker at both local and international SQL Server conferences.

BI Dashboards with Dashboard Designer & PerformancePoint Services 2010 [BID-398-P]
Session Category: Pre-Conference Session (full day)
Session Track: BI Information Delivery
Speaker(s): Rod Colledge
During this demo-rich dashboard design deep dive, we'll begin with an introduction to Business Intelligence and PerformancePoint Services in SharePoint 2010. Then, we'll cover the basics of creating dashboards with the Dashboard Designer tool. We'll look at some of the more advanced areas, including SQL Server Reporting Services and Excel Services dashboard integration, before taking an in-depth look at the process of creating scorecards and KPIs. We'll finish up with a look at how we can get the different items on a dashboard page to interact with each other, and the power of Time Intelligence.

Lookup transformation in SSIS: Performance Improvements

There are 2 suggestions I hear about when performance tuning the Lookup transformation on a large dimension table when getting the surrogate key for a fact table. I hope to introduce here a third.

The first is the caching option.

 

The Full Cache option will cache the whole table (or query columns). If you have a large dimension table, this might use a lot of memory.

The Partial Cache will load the matching rows but will clear least used lookup values once the memory size of the cache is exceed.

No Cache will query the data each and every time a lookup value is needed. No good is running lots of lookups on a large table

 

I believe using Full Cache is good when you have no memory limitations and a small dimension table. The Partial Cache is good for large tables. Not sure where No Cache would be used unless the lookup is not used much in the Data Flow Task.

The next suggestion is instead of selecting the whole table, use a T-SQL query to select only the columns you need,

By using only the needed columns in the SELECT T-SQL statement, you limit the amount of information retrieve into SQL Server’s Buffer Pool and the SSIS cache.

One more option can help improve this query even more – a proper index.

SELECT ProductSKey, ProductID

  FROM DimProduct

WHERE Status = 1

This query used above will still do a Clustered Index scan or Non-clustered Index with a Key Lookup in the execution plan based on the indexes available like an index on the just the status column.

But, if you can create a covering index with the Status as a Non-Clustered Index including ProductSKey (surrogate key) and ProductID (natural key) in the index, you can get better performance on the SQL instance side.

The cost of the query went from 0.0178649 to 0.0065309. The statistics IO went from 21 logical reads to 7 and the execution went from a Clustered Index scan to a Non-clustered Index Seek.

 

Using the partial cache and proper index on dimension table helped reduce a look up on one of our dimension tables from 3 seconds to less than one second on a 200,000+ row dimension table at my current employer. The execution performance was viewed in the new SSISDB statistics report in the 2012 version of SSIS. More on that in another blog.

PASS Virtual Chapter August presentations

If you do not know by now, PASS has many virtual chapters that give free webcasts all month long. I have helped chair the Data Architecture VC for the past year or so, started by volunteering  with the Performance VC for about 6 months and have done presentations on the BI/DW VC. Please visit the Virtual Chapter site for more information - http://www.sqlpass.org/PASSChapters/VirtualChapters.aspx

Tuesday at noon Eastern, I will be presenting on SSIS 2012 – CDC, DQS and Catalog for the BI/DW VC. More info below.

Also, the recorded presentation from this past Thursday’s Data Architecture VC is now uploaded in the Meeting Archive. Louis Davidson will do part 2 later this fall. His example code can be found at DrSQL.org in the presentation section.

Tue August 21st 12pm-1pm EST

Using SSIS 2012 for ETL in a Data Warehouse

Speaker: Thomas LeBlanc

URL: https://www.livemeeting.com/cc/usergroups/join?id=GQ5DKK&role=attend&pw=J%3A%2BW4%3F%28hK

This session will go through some old and new features of Integration Service 2012 to help with loading data into a Data Warehouse/Mart. The GUI has been improved visually during design and execution. The storage of packages has been update with Integration Service Catalogs. The Change Data Capture task helps with incremental updates. Logging has been improved for debugging. A demonstration of creating your own Slowly Changing Dimension with current Tasks will be shown with some design hints.

Louis Davidson (Dr SQL)–Designing for Common Problems in SQL Server

The PASS Data Architecture Virtual Chapter hosts none other than Dr. SQL Louis Davidson as he helps application and database developers design and implement SQL Server databases.

I saw Louis at the first PASS Summit I attended in Denver in 2006. He and Paul Nielsen help me see where data models and normalization can increase the efficiency with storing and retrieving data.

They also encouraged me by their presentations to start talking about database design at work and in the SQL community.

Please join us on Thursday August 16th at Noon Central for another informative presentation.

 

Subject:  Designing for Common Problems in SQL Server

Start Time:  Tuesday, August 16th, 2012 12:00 PM US Central Time

End Time:  Tuesday, August 16th, 2012 1:00 PM US Central Time

Presenter:  Louis Davidson

Live Meeting Link:  Live Meeting

Designing for Common Problems in SQL Server

In this session, I will do a design and code review of several common patterns of solving problems that a typical programmer will come up against. Problems like coding for hierarchical data, data driven design, dealing with image data, structure generalization, user specified schemas, dimensional reporting, and dealing with uniqueness beyond what you might deal with using a simple uniqueness constraint might allow you to deal with.

Louis Davidson

Louis has been in the IT industry for 17 years as a corporate database developer and architect. He has been a Microsoft MVP for 8 years and has written 5 books on database design and has spoken on the topic of database design and implementation at SQL PASS, SQL Rally, many SQL Saturday events, as well as Devlink. Currently serves as the Data Architect for the Christian Broadcasting Network supporting offices in Virginia Beach, Virginia and Nashville, Tennessee. Louis has a bachelor's degree from the University of Tennessee at Chattanooga in computer science. For more information please visit his website at drsql.org.

Add to calendar

SQL Saturday #150 & TechDay in Baton Rouge–Sharepoint

This year’s SQLSaturday in Baton Rouge includes a Sharepoint track with 2 local speakers(Cody Gros & Eric Trivette), MCM Tom Resing and Bill Waters. Sharepoint sessions are great for those in the BI field using Microsoft BI stack managing PerformancePoint and PowerView/SSRS integration or SQL Server DBAs having a Sharepoint SQL instance.

 

8:20AM - Mapping Mashups with SharePoint Designer

Mapping Mashups with SharePoint Designer including an Overview of Current Map Service Providers, an Introduction to Customizing the Data View Web Part, and a Demonstration of Dynamic Mapping from List Data

Session Level: Intermediate

Tom Resing

Tom is a Microsoft Certified Master in SharePoint. He is passionate about online collaboration technologies and loves being active in the SharePoint Community. Tom has been blogging about SharePoint for the last 5 years after certifying on the 2007 platform. Tom has enjoyed 15 years in the internet industry developing products and web site functionality in Perl, C++, Java, VB.Net and C#. With SharePoint, Tom prefers an Out of the Box approach to deployment.

Contact URL: http://tomresing.com

 

9:35AM - SQL Server Best Practices for SharePoint 2010

Are you a SharePoint administrator and now have to maintain all your SharePoint databases? This session will provide you with the information you need to maintain your SharePoint databases including: - Backup/Recovery of databases - Determining your recovery model -Building maintenance plans - SQL Aliases

Session Level: Intermediate

Cody Gros

Cody Gros has been in the IT field for over 7 years working with SharePoint and Windows Server Technology. He is well versed in SharePoint 2010/2007 and is experienced in applying PowerShell scripting to SharePoint and Active Directory automation solutions.

Contact URL: http://blog.codygros.com

 

10:50AM - SharePoint Development for ASP.NET Developers

Getting started in the world of SharePoint development? Have ASP.NET development experience? In this session, you will learn how SharePoint 2010 is built off the ASP.NET platform. We will also bridge the gap between both platforms by covering the basics of the SharePoint platform and set you on the path to developing your own SharePoint solutions.

Session Level: Beginner

Eric Trivette

Eric Trivette, MCPD, has been working with Microsoft ASP.NET since December 2006. Eric began SharePoint development with SharePoint 2010 and works with Antares Technology Solutions as a consultant where he designs and implements custom solutions in SharePoint 2010 and all versions of ASP.NET.

 

1:10PM - Integrate with Business Connectivity Services

Business Connectivity Services with Microsoft SQL Server including an Overview of the Business Connectivity Services, an Introduction to Configuring BCS, and Demonstrations of using External Lists and External Data Columns.

Session Level: Advanced

Tom Resing

Tom is a Microsoft Certified Master in SharePoint. He is passionate about online collaboration technologies and loves being active in the SharePoint Community. Tom has been blogging about SharePoint for the last 5 years after certifying on the 2007 platform. Tom has enjoyed 15 years in the internet industry developing products and web site functionality in Perl, C++, Java, VB.Net and C#. With SharePoint, Tom prefers an Out of the Box approach to deployment.

Contact URL: http://tomresing.com

 

2:25PM - Adding Metro Style Navigation to a SharePoint Site

This is a walk through of how to create a Metro Sytle Navigation to a SharePoint Site, potentially including exposing high level Core Metrics in tiles to bubble that key information to users.

Session Level: Intermediate

Bill Watters 

I am a Nerd who loves soccer, hanging with my family and sharing what I learn. I have been in IT for over 15 years and focused on Portal Solutions for the last 10. I am currently highly focused on BI Integration with SharePoint and making data more accessable to users.

Contact URL: http://wattersbill72.wordpress.com

PASS Data Architecture presents Ami Levin: Where Are My (Primary) Keys?

I saw Ami Levin at the PASS Summit 2011 presenting on Loop Iterators in Execution Plans. It was on my top 3 sessions last year at the Summit. He has an excellent way of using visuals to explain things. The session for the PASS Data Architecture VC on Thursday (July 26th) is great session on Primary Keys and relational databases and the rules surrounding them. All Developers need to watch the presentation.

Here is an article written by Ami recently and review by Dr. SQL Louis Davidson. Excellent step by step on how to normalization a many-to-many relationship into the proper table structure using Data Modeling.

Subject: Where are My (Primary) Keys?

Start Time:  Thursday, July 26th, 2012 12:00 PM US Central Time

End Time:  Thursday, July 26th, 2012 1:00 PM US Central Time

Presenter:  Ami Levin

Live Meeting Link:  Attendees LiveMeeting

Where Are My (Primary) Keys?

In this session, we revisit some of the fundamental design principals of relational databases—normalization rules, key selection, and the controversies associated with these issues—from a very hands-on, practical perspective. We also consider the benefits and challenges of using different types of keys (natural, surrogates, artificial, others) from multiple aspects, including data consistency, application development, maintenance, portability, and performance.
Session takeaways:
• Understand the profound impact of key selection on the entire application lifecycle
• Learn to identify when it is best to use natural or artificial keys • Learn how to minimize performance, portability, and maintenance issues during the early database design phase

Ami Levin

  Ami Levin  CTO, DBSophic
Ami Levin is a Microsoft SQL Server MVP since 2006, with over 20 years of experience in development and IT. For the past 14 years, he has been consulting, teaching, writing, and speaking about SQL Server worldwide. Ami's areas of expertise are data modeling, database design, T-SQL, and performance tuning. He leads the Israeli SQL Server user group (ISUG), moderates the Hebrew MSDN SQL Server support forum, and is a regular speaker at Microsoft Tech-Ed Israel, Dev Academy, and other SQL Server conferences. Ami speaks often at user group meetings in Israel and Europe and writes articles about SQL Server.

Add to calendar

SQL Saturday #150 and TechDay Baton Rouge–BI 1 track

The SQL Saturday/TechDay in Baton Rouge will be the forth annual this year. The Business Intelligence sessions occupy 2 tracks this year. This blog will be about the speakers from first track.

The full schedule can be found here.

 

8:20AM - Introduction to SSIS with a Real-World Example

SSIS is a powerful and east to use data Extract, Transform, Load (ETL) tool SSIS providing a rich set of components to perform the most common ETL tasks with “drag-and-drop” ease. Packages can be constructed with rigorous error and exception handling as well as verbose logging allowing deployment of robust ETL processes.. This session will demonstrate a process for loading a comma-delimited data file into a staging table for further processing then validating the data and moving specific data into host tables using a a thorough example of a non-trivial ETL project with “real” data. The intended audience includes beginning SSIS developers, DBAs, and anyone with a desire to experience the magic of data management with this remarkable tool.

Session Level: Beginner

Ron Johnson

A 20 year veteran of the IT industry with over 10 years experience as a software engineer Ron is a seasoned SQL Server performance optimization professional.Ron is currently supporting various clients as a consulting DBA specializing in PTO,Replication,HA/DR,and Security with Xtivia, Inc. a full-service IT company with offices in Dallas,TX, Colorado Springs,CO,and Edison,NJ.Ron earned a M.Sc. in Computer Science and a B.Sc. in Physics.Ron has 10 years of experience as an adjunct faculty member.

Contact URL: http://rjssqlservernotes.wordpress.com/

 

9:35AM Using SSIS 2012 for ETL in a Data Warehouse

This session will go through some old and new features of Integration Service 2012 to help with loading data into a Data Warehouse/Mart. The GUI has been improved visually during design and execution. The storage of packages has been update with Integration Service Catalogs. The Change Data Capture task helps with incremental updates. Logging has been improved for debugging. A demostration of creating your own Slowly Changing Dimension with current Tasks will be shown with some design hints.

Session Level: Advanced

Thomas LeBlanc 

Thomas is a Senior DBA at Turner Industries in Baton Rouge, LA. Worked in the IT field for 21 years experience w/ COBOL dBase, FoxPro, Visual FoxPro, VB 3 thru 6 and .Net(C#). Designing and developing normalized database has become his passion. Full-time DBA work started about 9 years ago. He has been blessed with speaking at SQLRally at SQLPass in 2011. Thomas’ free time is spent helping other & improving his relationship with family & God. TheSmilingDBA.Blogspot.com

Contact URL: http://thesmilingdba.blogspot.com

 

10:50AM BI in the Cloud

In this session we will take a look at some strategies for building Business Intelligence solutions on the Windows Azure platform. We’ll see methods for transferring on-premises data to the cloud, report deployment to Windows Azure Reporting Services and options for performing ad-hoc analysis. We’ll look at some of the challenges faced when moving BI into the cloud, such as high-performance ETL, schema management and transient fault handling.

Session Level: Beginner

David Liebman

I’m a developer specializing in .Net, SQL, and SSRS development for over five years of the eighteen years I have spent in the IT Industry. I have experience working for some big companies in Financial, Healthcare, and Insurance. I have written some custom reporting solutions and web applications for Large Companies in the Tampa Bay area using .Net, SSRS, and SQL. I’m currently a Sr. Developer at AgileThought that is leader in software development and system integration services in Tampa FL.

Contact URL: http://www.bisqldave.com

 

1:10PM - Moving to a data warehouse

This presentation will walk through the key concepts and decisions necessary for moving from a transactional system to an analytical system based on a data warehouse. Concepts and demonstrations will be based on the process implemented at the LSU Highway Safety Research Group as the case study. The discussion will be based on a more business approach while also presenting technical details.

Session Level: Beginner

Cory Hutchinson 

Cory Hutchinson currently serves as the Associate Director for the Highway Safety Research Group (HSRG) at Louisiana State University and the Coordinator of the Louisiana Traffic Records Coordinator Committee (LA TRCC). Cory oversees all IT related projects within HSRG including business analytics, web site design, data quality analysis, electronic crash data collection, data reporting, disaster recovery, graphical information systems, business intelligence, and crash data integration.

Contact URL: http://hsrg.lsu.edu

 

2:25pm - SSIS for ETL in the Enterprise Environment

Design walkthrough of an ETL solution developed using the Microsoft SSIS system for an enterprise level Data Warehouse. Managing the ETL process migration from a single data warehousing SQL Server to a multi-server environment can often be a complex task. This session will walk you through the design of a ETL system that has been created for such a scenario. The design takes into consideration several of the shortcomings inherent in the SSIS system and provides a structure for coordinated loading and processing of data from extraction to presentation.

Session Level: Intermediate

Paul Kennedy

Paul Kennedy is the Data Warehouse Manager for Pool Corporation out of Covington Louisiana. Developing aspects of BI solution at Pool Corporation for the past seven years and with additional companies for an additional three years, Paul has developed and maintains the current ETL, Data Warehouse, and BI systems for the company. Experienced with working the intergration of Microsoft SQL products such as SSIS, SSDE, SSRS, and SSAS with various transactional systems and E-Commerce Websites

PASS BI/DW presents Dimensional Modeling 101–by me…

With a little help from the Kimball Group, I have become somewhat knowledgeable with the Data Mart dimension model approach to Data Warehousing. The PASS DW/BI Virtual Chapter has graciously allowed me to present on Dimensional Modeling. I am not an expert, so this is an introduction to what I have learned over the years.

At Crown Vantage (a paper mill), I was tasked to move a data warehouse from the old (bankrupt) home office to the lone paper that was left. After reading A Diary to Data Warehousing, I found out that our office (Sales) just needed a Data Mart, and did not need a Data Warehouse for the nightly reports. Years later I was a DBA at Amedisys and started to help the BI team performance tune the SQL. This led me to the BI department of Amedisys.

Please join me tomorrow if interest in a level 101 intro to Dimensional Modeling!!!

Tues July 17th 11:30am - 12:30pm Central

Dimensional Modeling 101

Speaker: Thomas LeBlanc

URL: https://www.livemeeting.com/cc/usergroups/join?id=J89H27&role=attend&pw=w%7B37%3A4SWd

This session is going to help create a dimensional Data Mart from the AdventureWorks database that includes dimension tables and 2 example fact tables. The Kimball spreadsheet for documenting a Data Mart will be used to re-enforce the need for Requirements and Functional specifications. A brief review of ETL for Slowly Changing Dimension and population of the Fact will be demonstrated as well as a simple cube for reporting.

http://bi.sqlpass.org/

I’m Speaking: PASS Summit 2012 and more…

Tuesday, July 10th started a string of speaking engagements I have been blessed (or cursed) with in the months of July and August 2012. My local brother from a different mother, Patrick LeBlanc, hosted a SQLLunch for me as I demonstrated the new CDC controls in SSIS 2012. Patrick’s slogan “No Fluff Just Stuff”, reflects the theme for SQLLunch.com as demo rich webcasts of SQL Server features. The recorded session is now available here.

 

The announcement a couple of weeks ago of the SQL PASS general session included me. A new talk I first did at SQLSaturday #132 in Pensacola about SSIS 2012 new features – concentrating on CDC controls, Data Quality feature and SSIS Catalogs. Part of the demonstration includes new display options and Undo/Redo plus some auto-correction when columns change in the data flows.

 

Next Tuesday, July 17th I will be presenting Dimensional Modeling 101 with the PASS BI/DW Virtual Chapter. I got great reviews from this presentation while at the SQL Saturday #104 in Colorado Springs, CO. This was a fun trip with my fiancé skiing on the Friday before the event at Monarch. Many great speaker were at this event, and you can read my review here.

SQLSaturday #150 and Tech Day in Baton Rouge in August 4th, and I will be presenting a preview to my PASS Summit session. You can read more about Baton Rouge’s largest tech event here and here and here.

The next session will be on the PASS BI/DW VC August 7th, but has not been posted on the site, yet.

God Bless,

Thomas

Baton Rouge SQLSaturday #150 & Tech Day .Net 1 track

August 4th of this year will be the forth annual Baton Rouge SQL Saturday and Tech Day. LSU has offered the new business building for out event.

This blog continues the series of blogs over the next few weeks to spotlight the tracks for this FREE event. Of course, nothing like this is possible without sponsors. This year we have Bronze sponsors CozyRoc and Turner Industries (my current employer).

The next track is the .Net 1 in the room 1100C. Returning speakers Latish Sehgal and Chris Eargle (MVP) are welcomed back. New local speakers, Steve Schaneville (Amedisys) and Forest Marie (Turner Industries) are my “enemies” because the use Entity Framework to generate SQL in .Net. They are still very respected individuals.Trey Cook is a former The Shaw Group employee that now works for Microsoft.

Oh, by the way, I heard today from the famous Patrick LeBlanc that he will be at SQLSaturday this year. It is recorded on SQLLunch.com today during a presentation I did for CDC control task in SSIS 2012.

8:20AM - The .Net Ninja's Toolbelt

As developers, we all have our favorite utilities, some used occasionally and then there are others that we refuse to work without. We might not use all of them everyday, but a good developer should know his options when the need arises. In this session, we'll go through the tools, utilities and hacks that can make you much more productive as a .Net developer.

Session Level: Beginner

Latish Sehgal

Latish is a .NET consultant with Improving Enterprises and very passionate about software development and solving customer problems. He also leads the Dallas C# SIG. He can be reached via his blog at http://www.dotnetsurfers.com/ or twitter (http://twitter.com/latish).

Contact URL: http://www.dotnetsurfers.com

 

9:35 Windows Azure and Smooth Streaming

Learn how to leverage Windows Azure and the Content Delivery Network to host HD quality video, and Visual Studio to build and deploy a Smooth Streaming Media player to play the video.

Session Level: Intermediate

Trey Cook

I am currently a Technology Strategist for Microsoft for the Enterprise Partner Group customers in South Louisiana. Prior to joining Microsoft, I spent eight years with The Shaw Group as an IT Manager / Senior Systems Engineer for messaging and collaboration as well as an IT Security Manager. Prior to joining The Shaw Group Inc., I spent nearly five years as a PC/Network Technician, Help Desk Manager, Systems Administrator and DBA for Turner Industries, Ltd.

 

10:50AM - The Legend of Lambda

Lambda expressions are a powerful feature of C#, one that can be wielded for good or evil. Attend this session for an adventure through the evolution of the C# language, from the depths of the delegate keyword to the expressiveness of the lambda expression. I will show you how anyone can easily learn to wield and empower code with lambda expressions. It's dangerous to go alone! Take this: =>

Session Level: Intermediate

Chris Eargle

Chris Eargle is a Telerik Developer Evangelist, INETA Director, and Microsoft Most Valuable Professional – C# from Columbia, SC, USA. He has over a decade of experience designing and developing enterprise applications, and he runs the local .NET User Group: the Columbia Enterprise Developers Guild. He is a frequent guest of conferences and community events promoting best practices and new technologies. His blog, kodefuguru.com, features content to guide you in becoming a .NET Ninja!

Contact URL: http://www.kodefuguru.com

 

1:10PM - Tools, Tips and Nuget for ASP.NET Developers

A high level discussion of the tools used in a large enterprise by a professional .NET team. On a budget? No worries, most of these tools are free courtesy of the open source .NET developer community that you need to know about. Discussion to include awesome debugging skills, catching performance issues before they go live (we learned the hard way), how to have a professional error logging system set up in minutes, working with other developers and productivity tools. We'll also spend some on some critical design tips to keep your applications running faster and avoiding the "clean slate syndrome."

Session Level: Beginner

Forest Marie

Forest Marie is a lead software developer working for Turner Industries in Baton Rouge, Louisiana and Microsoft Certified Professional. He's been developing a variety of software since graduating from Southeastern in 2003. As an advocate of the S.O.L.I.D. principles, Forest is passionate about writing applications that was easy to work on, easy to maintain and easy to extend.

 

2:25PM - Unit Testing - Solving some Tough Issues

Unit Tests are common place in our tools for creating quality code. However, we still run into several road blocks that often prevent us from writing a thorough suite of tests. This session will suggest ways of solving 2 specific problems that I have personally experienced when trying to write suites of unit tests. Specifically, I'll attempt to address: * how to write test for older code libraries that do not currently have unit tests so that the code can be changed with confidence (a look at the new Fakes framework in VS 2012). * how to write tests for logic/data access code that uses Entity Framework without hiding the flexiblilty of Entity Framework (note, I actually don't prefer the repository pattern).

Session Level: Advanced

Steve Schaneville

Maintenance Engineer at the Paper Mill a didn't make my (or any other) list of most satisifying jobs, so I left that life and moved to Germany, where I discovered programming. After 2 1/2 years writing code to control robots (well kinda), I moved back to Baton Rouge and, surrounded by some incredibly smart people, learned something or other about writing enterprise software. I now serve as the Senior Applications Architect at Amedisys, Inc., and learn new things daily from my genius coworkers