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.