David E. Myers' Blog ( www.DavidEMyers.com )

OttawaSQL.net Business Intelligence Evening Event July 23rd 6:00 PM to 8:30 PM

Introduction to SQL Server Integration Services 2008

SQL Server Integration Services (SSIS) is an ideal tool to extract, transform and load / distribute data. In many BI projects up to 70% of the effort is spent on developing efficient ETL processes. SSIS is designed to reduce the development time and maintenance involved with this critical task.

In his session, Christian will cover the following topics:

• Overview of SSIS control flow tasks
• Overview of SSIS data flow components
• Useful new features in SSIS 2008
• Demonstration: Integration of various data sources including XML, flat files and Excel into a data mart.

Christian Coté, IA-Tech Consulting

Christian Coté is an IT professional with more than 10 years of experience working on business intelligence projects. He is currently working on his 6th major SSIS implementation since it was released in 2006. Before SSIS was released, Christian developed ETL processes using a range of tools on multiple platforms. Christian has been a presenter at a number of conferences and code camps and most recently presented a special session to our OttawaSQL.net BI study group. He currently co-leads the SQL Server PASS chapter in Montreal.

Register at:  http://www.clicktoattend.com/?id=139419
Arrival:  5:30 PM to 5:55 PM
Session:  6:00 PM to 8:30 PM
Refreshments:  Pizza and Pop

Note: No one will be admitted by building security after 5:55 PM, and the event will start promptly at 6:00 PM.

OttawaSQL.net June 17th Event: Introduction to Dimensional Modeling - A Case Study, Lynda Rab

Topic:  Introduction to Dimensional Modeling - A Case Study

What do they want to count?  And what do they want to count by?  This session will be presented in the form of a case study that will take the answers to those questions, the data from multiple OLTP databases and take you step by step through the path of creating a basic but well formed dimensional model.  What data should go in the Fact tables?  How many Dimensions tables are needed?   What do you mean by ‘Determine the Grain’?  What is a Bus Matrix?  This session will answer those questions using examples and also provide you with a step by step process that can be used for any new Data Warehousing project.  During the session, Lynda will share what she feels are the most important things to remember and the most important things to avoid based on her 8 years of experience creating and working with Dimensional models.

Speaker:  Lynda Rab, Canada Mortgage and Housing Corporation

Lynda Rab is an IT professional with over 15 years of technical experience. She has been working with SQL Server since version 4.2 and is now focusing her efforts on Data Warehousing working for Canada Mortgage and Housing Corporation.  Lynda is the Program Director for the Professional Association for SQL Server (PASS) and is a member of the OttawaSQL.net executive team.

RSVP:  http://www.clicktoattend.com/?id=138407
Arrival:  5:45 PM to 6:00 PM
Session:  6:00 PM to 8:30 PM
Refreshments:  Pizza and Pop

OttawaSQL.net May 25th Evening Event: Data Warehouse Architecture, Dejan Sunderic

Session: Data Warehouse Architecture

Dejan Sunderic presented this session on Data Warehouse Architecture to the Toronto group last December and attendees were very impressed. We are lucky to have him visit the Ottawa group to share his knowledge.

The main topics of his session include:

• Objectives of a Data Warehouse System
• Components of a Data Warehouse / Business Intelligence System
  • DW, ETL, ODS, OLAP, Data Marts, Grain vs. Presentation Marts, Reports, Analytic Tools
• Data Warehouse Design Methods and Artifacts
  • Normalizing vs. Denormalizing, Dimensional Modeling, Star vs. Snowflake Schema, Surrogate Keys, Conformed Dimensions and Facts, Bus Matrix, Slowly Changing Dimensions, Fact Table Types

Speaker: Dejan Sunderic

Dejan Sunderic is an independent consultant specializing in SQL Server development and architecture. He is an author of couple of books (such as "SQL Server 2005 Stored Procedure Programming in TSQL and .NET") and maintains couple of other web sites (such as tsql.trigonblue.com, ssis.trigonblue.com...)

RSVP: http://www.clicktoattend.com/?id=138406
Arrival: 5:45 PM to 6:00 PM
Session: 6:00 PM to 8:30 PM
Refreshments: Pizza and Pop

Upcoming OttawaSQL.net Events

Full session details for each of these events will be sent out to the OttawaSQL.net mailing list, but in the meantime we wanted to let you know the roadmap so you can save the dates (or register early)!  We will be continuing with a focus on BI for the next 2 events, and we'll be capping it off with a full day event this summer for people interested in the MCITP BI certification on SQL Server 2008.

Monday, May 25th 6:00 PM to 8:30 PM - Microsoft Ottawa, World Exchange Plaza

Session: Data Warehouse Architecture, Dejan Sunderic

Dejan Sunderic presented this session on Data Warehouse Architecture to the Toronto group last December and attendees were very impressed.  We are lucky to have him visit the Ottawa group to share his knowledge. 

The main topics of this session include:

· Objectives of a DW System

· Components of a DW / BI System

· DW Design Methods and Artifacts

Speaker: Dejan Sunderic

Dejan Sunderic is an independent consultant specializing in SQL Server development and architecture.  He is an author of couple of books (such as "SQL Server 2005 Stored Procedure Programming in TSQL and .NET") and maintains couple of other web sites (such as tsql.trigonblue.com, ssis.trigonblue.com...).

Register at:  http://www.clicktoattend.com/?id=138406

Wednesday, June 17th 6:00 PM to 8:30 PM - Microsoft Ottawa, World Exchange Plaza

Session: Introduction to Dimensional Modeling - A Case Study

Lynda will explore introductory dimensional modeling techniques and will demonstrate how to build an OLAP database using these techniques.  Her case study will examine source data from an operational database (OLTP) and step by step create a basic dimensional model and OLAP database.

Speaker: Lynda Rab

Lynda Rab is a Senior Data Warehouse Analyst and has been working with OLAP databases since 2000.  Lynda is the Program Director for the Professional Association of SQL Server (PASS) and is a member of the OttawaSQL.net executive team.

Register at:  http://www.clicktoattend.com/?id=138407

Late July / Early August

Certification Exam Review, MCITP: Business Intelligence Developer 2008

In keeping with the BI theme, we are working on having a full day event (6 to 8 hours) on a Saturday in late July or early August to review all of the exam objectives for the MCITP BI certification (on SQL Server 2008).  We will have a team of 3 or more presenters that will tightly base their presentations on the objectives for the 70-452 exam ( http://www.microsoft.com/learning/en/us/Exams/70-452.mspx ).  Since we have not yet confirmed the date we are simply asking for people that are interested in attending to let us know via email (at Events@OttawaSQL.net ).  Once all of the logistics have been arranged we will setup a registration for the event.

Content Posted: Building and SSIS Management Framework

Earlier this month Rushabh presented a fantastic session on SQL Server Integration Services at our PASS chapter in Ottawa.

I was lucky enough to attend the SQLTeach BI Boot Camp in Ottawa that Rushabh was teaching and I was amazed to see him go straight from delivering 2 days of intensive boot camp training to deliver the user group session.  I’m telling you, this guy is a trooper! :)

Rushabh has been gracious enough to share some of the T-SQL code for his framework solution with our community.  ( Thanks Rushabh !!! )

I’ve posted the material on the OttawaSQL.net site (on the Events page) for everyone to check out:

http://www.OttawaSQL.net/Events/tabid/510/Default.aspx

Remember, if you are serious about implementing an SSIS Management Framework then consider contacting Solid Quality Mentors to help your organization with this process.  You will definitely find that the investment is worth it!

Cheers,

David

David E. Myers

OttawaSQL.net Executive Director

OttawaSQL.net March 10th Special Event: Building an SSIS Management Framework (Level 400), Rushabh Mehta

Feature Session: Building an SSIS Management Framework (Level 400), Rushabh Mehta

As more and more organizations and teams within organizations have started adopting SQL Server Integration Services for data processing and other management task automation, there is increasing need for standardization early in the process to avoid common management problems that arise from lack of standardization and good management techniques. In this session, we will help you identify areas of standardization and pitfalls that arise from lack of standards, reviews some common standardization approaches and best practices and finally walk you through a standardized template example that can be customized, deployed and used as a starting point for an SSIS project.

Speaker: Rushabh Mehta, Solid Quality Mentors

Rushabh is a Mentor for Solid Quality Mentors' global Business Intelligence Division and the Managing Director for Solid Quality India. He has worked with Microsoft SQL Server since 1998 and has designed and developed BI systems on the Microsoft platform since 2001, including predictive analysis system, health-care analysis solution as well as a multi-terabyte financial decision support system. He has been the lead architect for many large scale and complex business intelligence solutions for clients such as Publix, Raymond James Financials, Jackson Hewitt, and Checkers. Through his work experience, Rushabh has been instrumental in creating multiple best-practices in the implementation of the BI technologies.

Rushabh serves on the Board of The Professional Association for SQL Server (PASS), is a SQL Server MVP and a frequent speaker at large conferences and PASS Chapters around the world.

Registration:

http://www.clicktoattend.com/?id=136527

Arrival:  5:45 PM to 6:00 PM
Introductory Session:  6:00 PM to 6:30 PM
Feature Session:  6:30 PM to 8:30 PM
Refreshments:  Pizza and Pop

Event Sponsors:

Last Chance: SQLTeach Special Event: BI Boot Camp, March 9^th to 10^th in Ottawa!!!

Use our group’s rebate code OT00SQLNET to register and save $100 off the $999 registration!

Business Intelligence is one of the topmost IT priorities and is increasingly pervasive within organizations large and small. Business Intelligence touches many aspects of our daily lives, from enhancing our online and retail shopping experience to the healthcare we receive. BI also impacts the quality of decisions that business leaders make.

The 2 day (March 9-10, 2009) boot camp has been designed to give you a head start in Business Intelligence practice. You will acquire fundamental knowledge of Business Intelligence and dimensional modeling along with understanding on how to use the various Microsoft technologies to implement a BI solution.

OttawaSQL.net is a community group of Ottawa area developers and IT professionals.  We share an interest in Microsoft’s data technologies especially:  SQL Server, SharePoint, PerformancePoint, Workflow Foundations, LINQ, ADO.NET and Entity Framework.

BI Certification Study Group: Resources for setting up your learning lab….

A few people in the OttawaSQL.net certification study group were asking about setting up their own lab environments to use while studying for the 70-445 & 70-448 SQL Server Business Intelligence exams.

Microsoft makes a number of trial versions of their software available for download.  This provides a perfect way to get setup to practice your skills in a safe environment.  The fact that they are time limited is only 1 more motivating factor to stay on task and write the exam :)

Here’s the list of links to resources that I suggested to the study group.  You can take advantage of these resources to set up your very own learning lab!

Resource Links for Lab Setup

You can download a trial of a pre-configured OS and then install your SQL Server trial.  All you need to do is install Virtual PC or Virtual Server 2005 (both free downloads) and boot up the VHD.

 

Virtual PC 2007 with SP1:

http://www.microsoft.com/downloads/details.aspx?FamilyID=28c97d22-6eb8-4a09-a7f7-f6c7a1f000b5&displaylang=en

Virtual Server 2005 R2 with SP1:

http://technet.microsoft.com/en-ca/bb738033.aspx

Windows Server 2003 R2 with SP2 VHD (30 Day Trial):

http://www.microsoft.com/downloads/details.aspx?FamilyID=77f24c9d-b4b8-4f73-99e3-c66f80e415b6&displaylang=en

Windows Server 2008 VHD (60 Day Trial, can be extended to 240 days):

http://www.microsoft.com/downloads/details.aspx?FamilyID=961db8ce-8473-47c2-92eb-b822ec5af0c9&displaylang=en

*** Note, you cannot use the CORE edition of Windows Server 2008 with SQL Server, use the FULL edition. ***

 

Alternatively you can download the OS trial software ISO image and create your own VHD....

 

Windows Server 2003 R2 with SP2 ISO (180 Day Trial):

http://technet.microsoft.com/en-ca/windowsserver/bb430831.aspx

*** Note, when you follow the link on this page called Download Trial Software you will have to sign in with a Live ID.  You will then proceed through a store checkout which is kind of confusing.  Complete the process and you will be given a install key.  You will be emailed with a link to download the software.

Windows Server 2008 Enterprise ISO (60 Day Trial, can be extended to 240 days):

http://www.microsoft.com/downloads/details.aspx?FamilyId=13C7300E-935C-415A-A79C-538E933D5424&displaylang=en

I highly recommend installing the SQL Server Enterprise Edition yourself a few times since installation is a tested skill on the exams! (vs. using the pre-configured SQL Server VHDs)

 

SQL Server 2005 Enterprise (180 Day Trial):

http://technet.microsoft.com/en-ca/bb738031.aspx

SQL Server 2008 Enterprise (180 Day Trial):

http://www.microsoft.com/sqlserver/2008/en/us/trial-software.aspx

Schedule Finalized for Certification Study Group - MCTS: SQL Server Business Intelligence 2005 & 2008

The next OttawaSQL.net study group is quickly approaching!  We’ll be covering the 70-445 and 70-448 BI exams and using the MS Press 70-445 Training Kit as our main resource.  Since there is a lot of similarity in the material we’ll cover the SQL Server 2005 material first and then review the differences for the SQL Server 2008 exam at the end.  Jean-Rene Roy (SQL Server MVP) and I have finalized the schedule and will be meeting with the members soon to get the ball rolling.  Unfortunately we didn’t have enough spots for everyone that wanted to participate.  For those who didn’t get in, all is not lost :)  I’ll be sharing resources and blogging about some of the best questions that come out of the group.  So if you are interested in getting the certification and want to follow along with us, check out the schedule and stay tuned for more information via my blog!  If you are really interested in getting a kick start to your certification, check out the SQLTeach Business Intelligence Boot Camp in Ottawa on March 9th and 10th.  If you are interested in following along with us, send me an email and we’ll chat about options.

Study Group Schedule:

Week	Date	Content	Pages
1	17 Mar 2009	Introductions & Study Group Overview Chapter 1: Installing SQL Server 2005 Business Intelligence Tools Chapter 2: Configuring SQL Server 2005 Business Intelligence Components Chapter 3: Migrating to SQL Server 2005 Business Intelligence Components	70
2	24 Mar 2009	Chapter 4: Developing SSIS Packages Chapter 5: Debugging and Error Handling with SSIS Packages	58
3	31 Mar 2009	Chapter 6: Developing SSAS Cubes Chapter 7: Extending SSAS Cubes	74
4	07 Apr 2009	Chapter 8: Defining SSAS Storage, Partitions and Aggregations Chapter 9: Developing SSAS Data Mining Models	63
5	14 Apr 2009	Chapter 10: Developing SSRS Reports Chapter 11: Formatting and Extending SSRS Reports	66
6	21 Apr 2009	Chapter 12: Deploying and Configuring SSIS Packages Chapter 13: Deploying and Processing SSAS Objects	62
7	28 Apr 2009	Chapter 14: Deploying and Scheduling SSRS Reports Chapter 15: Securing and Executing SSIS Packages	56
8	05 May 2009	Chapter 16: Administering and Securing SSAS Cubes Chapter 17: Securing and Querying SSAS Data Mining Models	68
9	12 May 2009	Chapter 18: Managing and Securing SSRS Reports Exam Review: SQL Server 2005 (70-445) (Part 1)	26
10	19 May 2009	Exam Review: SQL Server 2005 (70-445) (Part 2)	TBD
11	26 May 2009	New Features in SQL Server 2008 Business Intelligence	TBD
12	02 Jun 2009	Exam Review: SQL Server 2008 (70-448)	TBD

This study group would not be possible without the support from our volunteers and sponsors!!!