Events

Introduction

So after the drive to Emerald Casino (which took half the time I expected) and registration we started. Frikkie Bosch, marketing manager for app products (BizTalk, SQL, VS), from Microsoft is acting as facilitator for the boot camp! The actual presenter for the days is Kevin Coestzee from IS Partners. The room (pictured below) is big with space for approx. 80 people and Frikkie introduced all the companies attending at the start (lots of big names, including Microsoft staff!) and then Kevin took over and stated that the aim of the course and a brief overview of the SQL Server 2008 BI stack. It would be a level 200 course and some sections catering for business users (about 40% of the attendees) and some sections for technical (about 60% of the attendees).

The training room. This would fill up when everyone eventually arrived.

The PowerPoint section

Now that the stage is set lets look at what was covered in the mornings PowerPoint session:

First up what is BI: Taking knowledge, aggregating data and providing it to business.

BI’s use can be to understand the health of the organisation, collaborate the shared view of business drivers and reduce decision time and these are facilitated through four scenarios:

• Operational reporting
• Activity management
• Data Mart – analytics & decision making
• Data warehouse

What is the BI evolution:

BI Evolution – I couldn’t come up with names for each stage.

What is a star schema design:

• It is a fact table with a number of linked dimensional tables. Fact tables do not link to other fact tables

A star schema.

• A dimensional table is list of unique dimensions (people, time periods, stores) with a primary key and meta data on the items. They link and support numeric data.
• A fact table combines all dimensional table primary keys with the collection of measurements associated with a business final result of process. For instance the final amounts of a sale, or salary payments. There are two ways to design it:

  1) Columns layout – An individual column for each type (salary, sale etc…). Very wide layout with many columns which could be empty.

  2) Single value column with a foreign key to a dimension table to designate type (salary, sale, etc…). Very narrow layout but very long and no empties.

  The fact table will be the biggest table in the DB.

• This design is good for BI because
  • It allows for high performance queries.
  • It is designed for models.
  • It is low maintenance

To me it seems similar to normalized database model.

What is a snowflake schema design:

It is a normalised version of the star schema where the dimensional table may be related to one or more other dimensional tables. Useful for hierarchy designs, like company organisation charts.

Date Dimensional Table

• Most common dimensional table created as it is used in almost every system.
• Date information is consistent across fact tables.
• Useful common attributes for the dimensional table include: year, quarter, month, day

Parent Child Dimensional Tables

There are tables which are self referring. So one column contains the primary key, while another a foreign key to the first column. Good for organizational charts, where all employees in a single table and the foreign key points to the employees manager.

Slowly Changing Dimensions

The version control system of BI ;) So they support data warehouses by providing information on the past accurately. It is a concept introduced by Ralph Kimball and is used to track changes over changes time (employee sales when they move from store 1 to store 2 etc…).

There are three types:

• Type 1: No tracking.
• Type 2: Full versioning
• Type 3: Partial versioning (current + 1 or 2 back) – Exceptionally seldom used

Type 2 is done by having a start and end date columns for the record which define when it was created and when it was superseded by a new version. If the end date is null then it is current. When a new row is inserted the old data is copied across from the old row, the changes applied and the end date set on the old row.

Type 3 is done by adding a column for the fields to be tracking to the same row and coping the old data to that column before updating the column.

What is Data Integration?

• Transform corporate data into information.
• Enterprises spend 60-80% of their BI resources in the data integration stage.

The Labs

And that ended the marathon PowerPoint slide deck! :) Now on to the first two labs focusing on SSIS, which I already know well, but I am here so I dived into them because you never know what you will find. Each lab is broken into a number of exercises and the first lab (Introduction to ETL techniques using SSIS) is made up of four exercises. Exercises 1 through 3 focused on creating a SSIS package which retrieves csv files from an FTP server and stores them locally. Those are filtered by date using a variable and then unpivot the CSV data and lookup data from dimensions and load all of it into a fact table. I really enjoyed this part, even though it is the “simple” lab is is very cool, especially since you get to read from FTP and do some parsing. The section on unpivots and derived columns (including the horrid expression that is needed) could’ve had more information and the lab should have had a way to see the data before and after.

The control flow view of the first lab.

Exercise 4 was about investigating the slowly changing dimension transform while the entire lab 2 was to manage changing data using change data capture or the MERGE statement. Unfortunately both exercise 4  and lab 2 were to me useless. The involved opening a prebuilt solution and going through each of the parts, opening the components and reviewing the settings. Normally no information on why each setting was set the way it was. There were sporadic helpings in the manual but they seemed to wane the closer to the end of the lab you got. This was especially bad for MERGE (which is new to SQL 2008, and I only know that because I read it this week in prep for a session on what’s new in SQL 2008 I am giving) which really needs more information on the use because it is so powerful. These parts really were the “nice to feature x is there” but they were more of a demo without a presenter than a lab. 

One of the data flow views from the second lab.

If you, like almost everyone else (almost = everyone less Eben + Ahmed), did not attend PDC2008 well then why not swing by my office and grab the 60Gb worth of movies from it! Martin Woodward sent us a drive (arrived today) with them on, saving us from a fight with IT over bandwidth usage. There are 202 videos available! So why not host your own mini-PDC!

Call it a Christmas present, call it what ever you like but the guys at S.A. Developer have a session on the 8th of December with none other than Scott Hanselman! Make a plan, bribe, call in sick, do what it takes but you owe it to yourself to be there! For all the details see Craig’s Post.

Thanks Rudi for the heads up.

This week Thursday and Friday you will be able to find me at Emerald Casino! No, I haven’t given up my cycling addiction for a gambling one (although in yesterdays 94.7 race at the 70km mark I did think about it). Reality is more boring than that I am attending a two day boot camp on SQL BI! The camp covers areas I know well like Integration and Reporting services and areas I don’t know well like analysis services and performance point. I am looking forward to it and presenting a condensed, focused version on my return to the rest of BB&D (and possible S.A. Architect).

If you are attending also let me know via the comments so we can hook up for drinks on the Thursday night! Otherwise keep an eye on the blog for regular postings during the event!

I presented a high level session on SQL Server Integration Services (SSIS) this week with the focus on what’s new in 2008, and when and where to use SSIS. Less focus was given to the how because how is easy with SSIS but I did do 30min+ of live demo’s so that showed the high level how as well. SSIS is in it’s second version (2005 being v1) because before that in SQL 2000 and before DTS existed and I was asked during the session on how to migrate from DTS to SSIS. I stupidly mentioned that there was a third party tool but no Microsoft one, because in 2005 there wasn’t and the what’s new in 2008 doesn’t mention anything (1 + 1 = assumption… and you know where that gets you).

Skip forward two days and guess what Zayd the “Server Dude” finds by accident… a TechNet article on how to migrate. Not only showing the third part option but the NEW built in option in SQL Server 2008 which I said didn’t exist! Anyway if you are trying to move from DTS to SSIS then you have to check it out at http://technet.microsoft.com/en-us/library/cc768544.aspx

Yesterday was the day of my first full day training session, where I took a class of smart people through LINQ! It was MUCH more tiring than I thought it would be, both physically and mentally but  felt it was a great session. I have yet to see my scores but I am hoping that it will be in the 90% from the verbal feedback I got. One of the smart people to attend was Willy who has gone and written a review on what was covered including source code! One nice thing is that the discussions brought up some more topics and that I have fine tuned the content last night so those coming to the next one (which still has a FREE community seat open, I think) will get a even more refined session!

I did a 20min whirl wind tour of SRS 2008 at Dev4Devs (see here for the event info and here for the content), and I mentioned I will be running this again in November (see here). For those who did attend my session at Dev4Devs you may want to come and see this one, because I have 60min to fill. Even if I spoke like Kirk I couldn’t stretch 23 min (which is what I used) to 60 so I’ve added some really cool new content. The new content covers the designer and the WinForm and ASP.NET chart controls (yeah I know it’s not really SRS but it’s borderline and seriously how cool are they?!) and also actually get into where that web server went!

Here is the new introduction slide…

Hope to see you there!

Willy posted a short entry clarifying the S.A. Architect member attendance for events BB&D runs basically for TR we do not cap the limit on how many community members can attend but for a DRP we are capped to 1! He also explained what the difference is between a DR P and TR.

Anyway for November we kick off with three sessions from me! First is a TR on SSIS, then I have a repeat (due to demand and scheduling issues) of the LINQ session (this is a DRP but it means a community member can attend the first and someone else the second!) and then a TR on SQL Reporting Services. This last one will be a repeat of the content I presented at Dev4Devs (which you can read here). I will be going slower this time and also showing off the new config tool and the report builder tool (which I didn’t show before)! After that Herman takes over with his TR on the Expression Suite followed by Henk (who did a great DRP on Mono - .Net on LINUX! – today) doing a TR session XEN virtualisation. The last two for November include introduction TR on Regular Expressions and a TR on DataSets.

On Saturday I did a 20min (which is basically nothing) presentation on what is new in SRS 2008 at Dev4Devs! The feedback I have gotten has been very positive and I personally learnt more about what it takes for me to present well. That said some people asked me afterwards about the slides and content I used and the reality is that I didn’t have a single slide! The truth was Eben indicated when I volunteered that developers don’t like slides, so I took it as a challenge and did all my “slides” in SRS! For those who couldn’t attend, here is the run down on what I covered!

Introduction

The first “slide” was really about what I was covering and also some of the user groups out there! I must say sorry to Craig for leaving out www.sadeveloper.net. For those wanting the links to the groups they are:

• SA Architect: www.saarchitect.net
• Information Worker: www.informationworker.co.za
• BizTalk: www.biztalkug.co.za
• SA SQL Server: http://sqlinthewild.co.za/index.php/2008/10/01/south-african-sql-server-user-group-revived/

For the Designers

Building the slide also gave me the platform for the first section, what  is new for designers! So switching to edit mode I was able to demo the fact that textboxes can now contain rich text, so the entire of the title and agenda was a single textbox with different font styles and positioning. Below that you can see the communities worth supporting with two different colours! In SQL 2005 you were limited to a single font configuration per textbox so to do the above in 2005 would have take 7 textboxes!

Next I showed off the new design surface improvements which make it no longer feel like a annoying grid but a real smooth surface. There is also enhancements like the guide lines which snap you to other design elements and the distance tooltips which show your distance from other elements (see below).

I then showed off my favorite feature, UNDO and REDO. I know it seems small but the UNDO in 2005 reloaded entire reports and took forever to do, now there is a real instance UNDO!

We’ve been Robbed

Next I went into what had been taken out… sort of First was the data tab (missing highlighted in red below) which has moved to it’s own window. It feels so much slicker there! And then I spoken about the fact Table, Matrix and List were gone and what the table, matrix and list tools are now! I won’t retell the story on that because Teo Lachev has a better description at his blog.

Pretty Pictures

From there I moved into showing off the new Dundas based charting and gauge controls which make your reports look super slick and demo'd why gauges are great for showing multiple pieces of data at the same time (in my demo the distance and time of my exercising):

The Boring Slide

Lastly I ended with a “slide” on things I couldn’t demo but are noteworthy and I included a smiley rating scale on how noteworthy they are:

• No More IIS: SRS 2008 no longer requires IIS to run! It actually has it’s own web server and this means that not only does it scale better, it also is a true middle tier application.
• Memory Management: Because SRS 2008 is in charge of everything now and not needing IIS, you can limit how much RAM is used!
• Data Driven to SharePoint: You can now use a data driven subscription (i.e. one which is based on data in the DB) to publish to SharePoint.
• Support for Teradata: A lot has been said on this, so I assume it is important (I saw some heads nod when I spoke about it in my talk), but as I have never used Teradata it got the confused smiley.
• Per page rendering: This is big, it no longer renders the entire report at once. Now it just renders a page at time! Great for those massive reports.
• Custom and forms based authentication: A really great feature for hybrid environments! Also combined with no more IIS those Kerberos issues between CRM and SRS should be a thing of the past!
• Export to Excel: In my series on complex report building, the last part mentioned the horror that was exporting to excel and how sub reports generated ugly grey blocks! Well that is no longer the case. YEAH!
• Export to CSV: Has been improved to export just data. I did point out that there is limitations (like values from gauges will not be included if you use CSV) so be careful.

I lastly mentioned a new tool, called Report Builder which is an 18Mb download and gives an Office (ribbon bar) experience to building 2008 reports. It is really great in that it has low overheads (no Visual Studio requirements), it has all the design surface features I mentioned at the start and it is very easy to get up and running. It does require a full SRS server to be available if you want to run the report, so no preview mode like in SQL BI Studio. That said it is great for power users and I see the real value coming in the future when you need to work with old reports and don’t want to install old versions of Visual Studio, like we have to with 2003 and 2005! I mentioned on Saturday it was RC1 status, we’ll it seems that was a lie because at 3:30am Saturday they released the RTM version!!!! For more and to download it (it’s free) see: http://blogs.msdn.com/robertbruckner/archive/2008/10/17/report-builder-20-release.aspx

Finally thanks for Eben and Ahmed for arranging the event and EVERYONE who attended!

One of the perks of being a registered member of S.A. Architect is that when the ATC team at Barone Budge and Dominick run a training session we keep a seat open for a community member. Coming up at the end of this month is my first full day session *gulp* which aims to cover LINQ. Being the shameless self promoting type, I thought I would do a little blurb on what you can expect if you, said S.A. Architect member, decide to join us.

Remember attendance for this one member is FREE! First you come to our excellent collab centre where you will have your own PC to work on for the day (sorry, can’t take it home with you) and I will be taking you through 28 HANDS ON labs during the day! Most are small labs (sub < 15min) so don’t worry about leaving late. Mindful that you are here for the full day, you get a free hot lunch (always cool) and I promise to personally guide you to the vending machines (snacks and coke) which will be free too! You will also take home a printed copy of the manual created for this session. I am waiting for the final proof to be developed but I suspect it will be about 200 pages in length in the end! Next I will be asking questions during the day (to see who is sleeping) and for prizes there the stress balls. I am hoping we will have a few t-shirts left after Rhodes to give away to people answering questions too. Lastly you get to meet some very smart people and do some networking!

Before it sounds like this is super special for my session, most of this (lunch, snacks, prizes, networking) is available at most of the full day sessions we run!

This is a session for someone who knows nothing of LINQ and we will start at the basics and go through to some level 300 stuff in the end. You do need to know basic C# (if you know what I mean by saying: add the using System.Data.Linq, you will be fine). The day will cover:

• The Problem which LINQ solves
• LINQ Architecture
• Implicitly Typed Variables
• Use LINQ to Objects to get and sort data and understand what is happening using the basic LINQ program.
• Anonymous Types
• LINQ Query Execution
• Anonymous Methods and Lambda Expressions
• LINQ Query Operators
• LINQ to XML
• LINQ to SQL

So if you are interested, first register at S.A Architect. Then let Willy know you wish to attend! Dates/times etc… can be found here!