24 Nov 2008

SQL BI Boot Camp - Day 1: Afternoon Session

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:

image

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.

image

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:

image

The report from exercise 2.

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

image

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.

For UDM you use OLAP which stands for OnLine Analytical Processing and is really just retrieving data from 1 or many databases and presenting it in a way which is easy for retrieving/analysis of the data.

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

image

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).

image

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

 image

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.

clip_image002

One of the MDX queries I fixed.

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

Tags: 
24 Nov 2008

SQL BI Boot Camp - Day 1: Morning Session

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).

Photo042 

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:

2

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

3

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.

image

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. 

image

One of the data flow views from the second lab.

Tags: 
17 Nov 2008

SQL Server BI Boot Camp

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!

Tags: 
07 Nov 2008

DTS to SSIS

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

Tags: 
07 Nov 2008

SQL 2008 - Saving Changes Is Not Permitted

I was working on a database for some sample data a few weeks back using SQL Server 2008 for the first time. I initially configured the table in one way and decided to change it later, because I wanted to do something else with the data. However when I tried to save it I got the following message:

a1

As there was no data in yet, I wasn’t worried about tables being dropped and re-created so I needed to enable the option mentioned in the message. The option can be found in the options menu item (duh!) under Designers –> Table and Database Designers. You need to uncheck the option Prevent saving changes that require table re-creation and click OK!

a2

Tags: 
27 Oct 2008

SQL Reporting Services TR - Think you've seen it?

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…

Clipboard01

Hope to see you there!

Tags: 
20 Oct 2008

What's new in SQL Reporting Services 2008

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:

srs1

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).

srs2

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 Smile 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.

srs3

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):

srs4 

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:

srs5

  • 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!

Tags: 
15 Oct 2008

Dev4Devs - This Saturday

A few weeks ago I posted about the Dev4Devs event coming up, well it is this Saturday! I will not only be attending but I will also be doing a presentation on what’s new in SQL reporting services (SRS) in 2008! It will be a nice quick level 100 to 200 demo of the new stuff.

I also saw on Eben’s post that two of my absolutely favorite presenters are speaking, namely Rudi Grobler (he is speaking on what’s new in 3.5 SP 1 for client development) and Brent Samodien (he is speaking on consuming ASP.NET data services using AJAX… which I hope means he will show ADO.NET Data Services, will have to wait until Saturday to find out).

Tags: 

Pages