I haven’t worked with MSCRM for a few months, but I still follow the news and today there was something worth acknowledging. Jim Daly released a tool which creates a Word 2007 Document detailing the configuration of all customisable Microsoft CRM 4.0 forms, including tabs, sections and details about each field. This document can be used for planning of form customisations or documenting the current state of form customisations. Ah how I wish I had this for some projects!

You can download it for free MSDN Code Gallery!

Thanks Jim Glass for heads-up.

What the documentation looks like for an appointment form.

If you are using IE 8 beta 2 and your zoom is NOT at 100% you could be having all kinds of issues from performance to skipping around the screen and being unable to select text. This doesn’t seem to be a big issue, I mean who runs at anything other than 100% but it is bigger than that. See in IE 8 your DPI settings will effect the zoom, so if you are running 1280x1024 or higher by default you will be at 120DPI and not the standard 96DPI which means you will run at 125% zoom in IE! It also effect people using the zoom for accessibility reasons.

There are two items available for voting on for these issues on Connect, so if you are an IE 8 beta user please take the 5 min to first test yourself and if you are able to repro the issue please vote on these items:

• https://connect.microsoft.com/IE/feedback/ViewFeedback.aspx?FeedbackID=371251
• https://connect.microsoft.com/IE/feedback/ViewFeedback.aspx?FeedbackID=382566

Ah, day 2 has arrived so I got up at 6 to get ready and watch the email trickle down (Emerald Casino appears to only have GPRS for Vodacom) which didn’t finish in the end. Unfortunately this post will not be as informative as the previous two, because what I was doing was taking notes on the machine I was working on and about an hour before the end of the day there was a power failure and I was not able to retrieve my notes :( So not only did we leave early, it was meant to be a short day to begin with… nice for a Friday to avoid traffic and get home early.

What stood out for me for that day was working with PerformancePoint which is a business user tool to build dashboards, KPI’s and scorecards. It was impressive at how easy it was to work with it, especially considering that in the background it’s creating cubes, writing MDX etc… This is the way this is meant to be. However it is a BUSINESS USER tool, so much so that Kevin (the presenter from IS Partners) was telling how they hired four CA’s in the last month and trained them to use PerformancePoint rather than training “BI people” to understand business. From speaking to some of the Microsoft people who have used it, the installation of PerformancePoint though is exceptionally tough and I was disappointed that it was not even touched on :(

Anyway without notes it’s hard to write long posts without possibly making huge mistakes, so I will leave it there for what was covered.

As an event it delivered on the initial point of being a level 200 course and helping understand the concepts and helping business users. The problems with labs and manual are understandable (these things happen) but considering you are paying for this and this is a MICROSOFT event it definitely should’ve been more professional than it felt. I would recommend this course to analysts and business users but not to development or serious BI users.

If you haven’t checked out GMail for a while, you need to today! They have launched approx 12 themes for it. That in itself isn’t impressive but the fact the themes seem MUCH faster than before and work perfectly in IE 8 Beta 2 in standards mode means that is one less site that I have to keep IE in compat mode for!

My inbox with the new Shiny theme – after Firefly anything names Shiny gets bonus points with me.

I recently had a question about layout in reporting services. Basically if you use a table you get a vertical layout, like this:

But what if you want to go horizontally? Sure there could be UI issues because the report will get wider and wider with more data getting put in and it could influence the quality of export or printing but in a special scenario it would be very useful. Unfortunately the tablix control (Table + Matrix + List in 2008. See my post on what’s new for more info) does offer this natively :( But using a trick you can get around it. First you need a tablix in matrix format (i.e. drag a matrix onto the form), and then add a new column header for each column, but rather than putting a header text in there you put the data in there. You ignore the rows and data cells (in fact you can hide them by setting the hidden property to true).

And the output is a horizontal layout of data:

Reporting Services

After lunch we dived into the next topic I know (don’t worry I learn stuff later in the day), namely Reporting Services :) So we started with the PowerPoint again :| I am not repeating everything he said, since a lot is covered in my earlier post on reporting services.

First where are reports used:

• Internal reporting – operational & management
• External – to customers
• Embedded – Portals, Windows and Web

The lifecycle of report development is:

Reporting lifecycle – Once again no clever names (I should work on that).

Some notes on RS which I never knew: Data driven subscriptions are an enterprise edition only feature :(  and drill through only enabled for reports rendered into html (wonder why PDF doesn’t support it).

Reporting Services Labs

Right after the slides were put away we dived into the first lab… actually we didn’t because the report builder 2.0 software was NOT installed on the machines :( Skip over lab 1 and onto lab 2! Lab 2 involved creating a simple report with a chart control on it. It enjoyed this lab even though it was fairly simple.

The report from exercise 1. Note two charts but it is a single chart control. Thanks Dundas :)

For exercise 2 we created a report with gauges:

The report from exercise 2.

The third exercise involved publishing to a RS server from Visual Studio and then exporting to Word.

The report about to be exported in exercise 3.

All in all I found the RS labs very enjoyable if not even at the advertised level 200 for the course.

Introduction to UDM

With that out of the way we looked at UDM, which is the SQL 2005/2008 name for a cube! UDM stands for Unified Dimensional Model.

Uses of OLAP

• Sales analysis
• Forecasting and budgeting
• Financial reporting
• Web Statistics
• Survey Result
• ETL Process Analysis

SQL  Server Analysis Services (SSAS) is made up of an OLAP component and a data mining component.

Basic data mining is also available for free in Excel (separate download).

Building a cube provides more functionality to users, enables high performance queries, abstracts multiple sources and encapsulates business rules.

Now that the introduction is done it was on to the labs. The first lab was to build a cube! There seem to be WAY too many steps involved in doing this (maybe I am just a lazy developer) but it quickly became an exercise on following a manual and not really any understanding of what and why each step was being done. One of the comments I over heard was that it was a lot of work to build a pivot table, because that’s all we ended up with. I think that actually highlighted the lack of information about why we did things :S

Using the data browser in the cube we built.

We then fired up Visual Basic :shock: to build an installer for for the cube so it could be installed on multiple servers easily. This was very interesting, mostly because I spent a lot of time in the past with MSI and never really got to grips with the advanced features (like embedding VB code).

My VB code! Actually it’s copy and paste from the snippets file.

Deploying my cube using the installer.

To do that we were given 75min and the manual said it expects us to complete lab 1 in 75 minutes (I did it in 71), but no extra time was allocated for lab 2 which was estimated 45 min, lab 3 or lab 4 which were estimated at 15min each. That meant unless you were super fast with this (i.e. you knew it) you couldn’t get to 75% of the labs :(

Introduction to MDX

After the cubes we moved into MDX which is a language similar to SQL but optimized for handling analysis of data in a cube. It’s specifications are either from OLEDB for OLAP (ODBO) or XML/A.

Since it is a language it is not a end user, or even power user, tool. It is a development tool and the skills needed to help learn it are:

• Good to have: SQL Skills, Excel Skills (Formulas)
• Not helpful: C# or ASP.NET

So what does it look like: SELECT <Dicers> FROM <Cube or sub query> WHERE <Slicer>

SQL vs. MDX

• In SQL the result is defined by columns which populate rows while in MDX it is defined by rows and columns and populating cells.
• In SQL the row headings are data while in MDX they are a schema.
• SQL aggregates dynamically into groups while in MDX data is referenced by cell sets.

What is a MDX Set: Grouping of items together. Uses standard set notation: { … }

You can add formatting (like currency information or thousand separators) to the result in the cells using the CELL FORMATING statement.

Tuple – A list of members from different hierarchies separated by commas. Pronounced: Too-ple like quadruple. These are what goes in the <Dicers> and <Slicers> section of the example above.

Imagine the following 3 tables

Towns:

• CT
• JHB

Values:

• SALES
• COS

Sales People:

• JIM
• JACK

You could built the following tuples:

• All sales for CT: (ct,sales) or (sales,ct) <- Order does not matter.
• All sales for JHB and CT: (ct,sales)+(jhb,sales)
• All JIM’s sales in CT and all JACK’s sales in JHB: (ct,sales,jim)+(jhb,sales,jack)
• Note that would exclude all JIM’s sales in JHB and all JACK’s sales in CT.

Types of tuples:

• Complete tuple: references every dimension
• Partial tuple: omits some dimension. Will always use current member if dimension is omitted.

In MDX tuples have functions like: Percentage of parent, prevmember, parallelperiod and children.

Now that we have looked at MDX we hit the lab which was very refreshing as they provided about 10 broken MDX queries and you had to apply your knowledge (and skills with the help file) to fix each one. This was a really good lab since it actually enabled you to learn and understand what you were doing. It was a little frustrating at first because it’s different but more of the labs should’ve been this way. In the end I got some right and some I just got stuck on and had to peek at the answers to see how to fix them.

One of the MDX queries I fixed.

By now it was after 6pm and time to check-in and head for dinner and drinks.

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!