|
|
Nico Jacobs
|
|
Hi, I'm Nico Jacobs, trainer @ U2U, with a PhD in data mining.
Let's talk about databases. BIG databases. I always loved the I in IT. Although
we have lots of interesting areas in the ICT landscape, I fell in love with the
basics: data. Storing and retrieving data, and all sorts of derived data. Although
it may seem trivial and far from sexy, databases are the basis of nearly every business
application. So at U2U, I'll mainly bring the story about how to store and query
that precious data in relational and OLAP databases, how to transform data, and how
to build reports on them. But I admit: data all by itself isn't that great either,
so from time to time, you'll catch me writing .Net applications... on top of a database,
of course!
In 2004, I started specializing in the Business Intelligence tools that Microsoft
ships with SQL Server 2005: Integration Services, Analysis Services and Reporting Services.
And the story continues, with SQL Server 2008 bringing lots of new features. |
|
|
|
|
Courses
These are some of the courses that I give for U2U.
-
This five day course focuses on using the SQL Server database to develop applications. It discusses basic as well as some of the advanced features of SQL Server: creating tables, views, functions and stored procedures, asynchronous communication between servers with service broker, the integration of the .Net framework … We also focus on solving the common questions that developers have when working with SQL Server: how do I design indexes, when should I denormalize data, how about dealing with transactions and locking… We also discuss the new data types and development techniques which are added to SQL Server 2008. Topics: Creating Databases and Database Files. Creating Data Types and Tables. Using XML. Creating and Tuning Indexes. Implementing Data Integrity. Implementing Views. Implementing Stored Procedures and Functions. Implementing Managed Code in the Database. Using Service Broker. Measuring database performance. Optimising queries for performance. Optimizing an Index Strategy. Managing concurrency. Working with spatial data.Streaming data access.
-
This five day course focuses on installing, configuring and maintaining a SQL server 2005/2008 database. It starts with the typical administrative topics: installation, backup and restore, setting up security, monitoring, automating tasks, data transfer and high availability. We also focus on topics which are borderline between administration and development, such as index design and maintenance and setting up statistics. We also discuss new 2008 features, such as the resource governor, the new auditing, and the policy based management. Topics: Installing and Configuring SQL Server 2005/2008. Managing Databases and Files. Disaster Recovery. Managing Security. Monitoring SQL Server. Transferring Data. Automating Administrative Tasks. Maintaining High Availability. Introduction to Replication. Managing and Automating Databases and Servers. Managing SQL Server Supporting Services. Building a Monitoring Solution for SQL Server Performance Issues. Troubleshooting Database and Database Server Performance Issues. Policy based management (2008). Advanced administrative features (2008).
-
Overview of the features of the server side Business Intelligence tools that Microsoft ships with SQL Server: Extracting, transforming and loading data with Integration Services; Creating, managing and browsing Analysis Services cubes and data mining models; Building reports on Reporting Services using the Report Builder as well as the Report Designer against relational data as well as OLAP cubes. The client side possibilities for browsing these Business Intelligence Servers using Microsoft Office, Sharepoint, and the Scorecard Manager.
-
Reporting and analysis applications are become more and more important in today's business applications. Queries that aggregate over a large portion of the data in a relational database slow down this database. Therefore, OLAP (OnLine Analytical Processing) cubes have been invented to provide performant aggregation querying over large amounts of data. In this course, you learn how to build and query OLAP cubes with Microsoft Analysis Services, as well as build and query Analysis Services data mining models, which are aimed at discovering complex, unknown and useful patterns in large amounts of historical data. Topics: Creating and managing Analysis Services cubes, using features such as translations, Key Performance Indicators and calculated members. Processing cubes, designing aggregations, deciding upon which type of storage is optimal. Setting up data mining models, processing and analysing these models, and creating PREDICTION JOIN queries for client side querying of these models.
-
Using multidimensional terminology. Adding calculated members to a cube. Using MDX member functions to navigate within a hierarchy. Using MDX time functions to compare values over time. Using MDX set functions in a query statement. Using functions to manipulate sets of members. Using functions to aggregate values from a set of members. Applying MDX to make a cube change dynamically. Calculating formulas for specific blocks of cells. Controlling the order of calculations within a cube. Applying MDX in common customer and sales applications. Applying MDX in common financial applications.
-
Creating and managing Microsoft SQL Server Integration Services packages. Creating Control flows and Data Flows. Debugging these packages. Setup logging and event handling for packages. Configuring package values with configuration files. Working with variables. Program ad hoc control and data flow task.
-
This course provides students with the knowledge and skills to author, deploy, and manage reports using Microsoft Reporting Services 2005 and 2008. Students should leave the course with a good understanding of how to apply reporting services to problems within a business context. Topics: The architecture of Microsoft Reporting Services and Office integration. Authoring reports using the Report Designer. Deploying and publishing reports. Managing the lifecycle of a report including report scheduling and backup and restore. Applying Reporting Services to a business problem.
|
|
Books
I've already written following books and Articles:
-
“Relational sequence learning and user modelling”, the title of this thesis,
introduces four topics which play a central role in this thesis. Although ‘user
modelling’ is the last term in the title, it is the first thing we need to explain,
because it forms the motivation for the rest of this work. ‘Learning’ refers to
data mining and machine learning.
| |
| |
Articles
Here is the list of articles I've written for U2U and Microsoft:
-
Automatically updating Reporting Services caches: Getting best of both worlds
July 2006
Reporting services allows you to create advanced reports, based on data which comes from a diversity of data sources. Reports are usually build based on data which accesses many records in the underlying database: often you visualise in the report information that spans multiple days, products, departments,… and you aggregate all the data (sum, average, minimum,…) to get an easy to interpret report. As you know, business data is more about finding the haystack than it is about finding the needle. Because we are addressing a large amount of data when building a report, it is common to use a caching technique to make sure users are served cached reports, in that way avoiding unnecessary access to the database.
-
SQL Server 2005 Service Broker
July 2006
SQL Server 2005 makes it easy to set up asynchronous distribute applications by integrating a queueing mechanism into the database. It combines the ideas of MSMQ with the stability, reliability and functionality of a relational database server. In this code walkthrough we investigate how to set up a simple application on this service broker. Gradualy we introduce additional aspects of the service broker, complicating the initial simple setup.
-
Using SQL Server 2005 to store and query serialized objects
May 2006
.NET offers the possibility to serialize and deserialize objects into a binary or XML representation. Serializing into XML is a requirement in order to create and consume webservices, binary serialization can be used for remoting. But besides moving objects between different applications, can serialization also be used to ‘materialize’ objects, and give them a persistent storage. This can be done to file, but databases offer a more robust storage (transactional consistency, backup,…). But there is more to it. Since Sql Server 2005 offers XML querying functionality (XPath as well as XQuery), this allows us to query the materialized objects, and even alter them. In this article, we demonstrate this idea by building a small application in which we store persons in a listbox. We then store the state of our listbox items (including the Person objects) serialized as XML in the SQL Server 2005 database, and deserialize this. Since we can store multiple serialized listboxes, we extend our load method so that we can query our database. In our case, we allow the user to enter the name of a person, and when we then click load, the first materialized listbox in which a person with this name occurs is loaded.
-
Microsoft SQL Server 2005 Integration Services smaakt naar meer
October 2005
Microsoft vaart al enige tijd onder de vlag ‘innovatie door integratie’. De nieuwste editie van
hun ETL-tool (Extract, Transform, Load) bewijst dit nog eens door de naam Integration Services
te dragen. In dit artikel nemen we deze opvolger van SQL Server Data Transformation Services
(DTS) onder de loupe.
-
Microsoft SQL Server 2005 Analysis Services: van gegevens- naar informatiebeheer
October 2005
Databases zijn een vast onderdeel in zowat elk IT-project. Naar mate de hoeveelheid opgeslagen data
toeneemt, worden taken als rapportage en analyse alsmaar belangrijker. Vandaar dat Microsoft in de
nieuwe versie van Microsoft SQL Server ook veel aandacht heeft besteed aan de OLAP-component
Analysis Services. In dit artikel beschrijven de auteurs de belangrijkste kenmerken van Microsoft
SQL Server 2005 Analysis Services, met de nadruk op de nieuwe aspecten.
-
Consuming Web Services in SQL Server 2005
September 2005
Sql Server 2005 offers the functionality of writing functions, stored procedures, aggregates, triggers and types in .NET. In this way, we can ‘wrap’ .NET functionality in a database function, procedure or type. In an earlier article, we demonstrated this principle by wrapping regular expression functionality in a CLR function. In this article, we illustrate the same principle, but this time by consuming a web service.
-
Regular Expressions in SQL Server 2005
May 2005
SQL Server 2005 offers the possibility to write functions, stored procedures, aggregate functions, triggers and user defined types in a .NET language such as Visual Basic .NET or C#. One of the great advantages of this functionality, is that we can ‘pull’ functionality that is available in the .NET class libraries into the database. In this article, we illustrate this idea by ‘pulling’ the .NET support for regular expressions into SQL Server 2005.
|
|
|
|
|
|
Presentations
Here are some of the presentations I've done. You'll be able to download my slides
and demos here.
-
March 31, 2010
-
TechDays 2010 Belgium
:
5 Things SQL Server does different from what many developers expect
This talk is intended for the developer who knows about inner and outer joins, and knows how to write a T-SQL stored procedure. But sometimes developers bump into ‘weird’ behavior of SQL Server. This sessions dives into 5 common misconceptions developers have on SQL Server. We touch upon these topics: Transactions (nested transactions, the five and a half isolation levels), stored procedures (do they really speed up my query, what is the effect of the pre-compilation?), error handling (errors in a try block will jump to the catch block, don’t they?), statistics (SQL Server builds them automatically, so I shouldn’t worry about them, right?) and index design (we have a wizard for this, why would we need something else?).
-
December 3, 2009
-
SQL Server Day Belgium
:
Using XML in SQL Server
Web services, REST, Office 2007 documents, RSS feeds,... , the world is filled with XML data. How do we deal with this when it comes to storing such data in our databases? SQL Server offers a lot of functionality to store, retrieve, index and manipulate XML data. In this session, we bring an overview of all the XML related technologies in SQL Server. We start with FOR XML and OPENXML, functionality available since SQL Server 2000. We move on to the XML type, new in SQL Server 2005. We dive into the five functions to query and manipulate XML data on SQL Server, and build primary and secondary xml indexes to improve performance. We conclude with column sets, new functionality in SQL Server 2008 to deal with sparse columns in an xml-alike style.
-
May 28, 2009
-
DevDays Netherlands
:
Administration Revised: Policy Based Management
In SQL Server 2005 a lot of administrative things can be automated, but the administrator always need to decide how certain administrative policies are enforced. In SQL Server 2008, policies are added, which allow an administrator to specify only which policies they would like to enforce, so that they no longer need to worry on how to enforce it: administration becomes declarative instead of procedural. In this session, we introduce facets, conditions and policies, investigate the different ways policies can be applied, and see how best practices can be enforced by policies. Finally, we dive under the hood of policy based management to see how this is implemented.
-
May 28, 2009
-
DevDays Netherlands
:
Building reports the easy way: Reporting Services 2008
Reporting Services has changed a lot. The tablix component replaces the table, matrix and list components, and provides interesting new reporting options. But there is more: we got a new gauge component as well, word rendering, expression placeholders, improved charting capabilities, a new rendering approach, we lost the ties with IIS, we gained a new report builder environment, and much more. This session shows the highlights of what is new in Reporting Services 2008.
-
November 2007
-
MSDN Evening Session
:
What is new in Visual Studio 2008?
(Together with Peter Himschoot)
The next version of Visual Studio, Microsoft Visual Studio 2008, will provide an industry-leading developer experience for Windows
Vista, the 2007 Microsoft Office system, and the Web.
If you want to know how Visual Studio 2008 differs from every single previous version of Visual Studio, if you are having a hard
time figuring out what exactly is new in .net 3.5 or if you are interested in exploring the new language features of C# 3.0 or
VB 9.0, then this session will answer all of your questions... and more!
After this session, you’ll hurry back home and start upgrading your projects to Visual Studio 2008. That’s a promise!
-
February 2006
-
ISV Summer School
:
Microsoft SQL Server 2005 for Developers
In 2005 I provided several 5 day workshops on MS SQL Server 2005 at different locations in Europe.
-
June 2005
-
TechNet Session
:
Microsoft SQL Server 2005 for Developers
From daily data to Business Intelligence with Microsoft SQL Server 2005 Analysis Services.
|
|
This section provides a list of tools I developed.
-
ASDoc is a tool which creates documentation for your Analysis Services 2005
OLAP cubes. It connects to your live server, and queries the metadata
for the name and description which the developers attached to every object (database,
cube, dimension, hierarchy, attribute, translation, data source, data source view,...).
It then produces an HTML file which contains the name and description
of all these objects. But there is more: also other properties, such as last processing
time, creation time, sizes of fact tables etc. are written to the HTML file.
-
In an OLAP cube, it is important that the cube can quickly figure out if a certain cell (tuple) is empty or not.
For measures, Analysis Services is keeping track of this. But for calculated members, the system has to evaluate
the full expression, which slows down the query. Unless... unless we can tell the system that the calculated member
will be empty (null) when another, real member is null. This is done via the non-empty behavior property of the
calculated member. With this tool, you can quickly see all the calculated members in your live cubes which do not
have a non-empty behavior set on them. The tool also shows you the definition of the relevant calculated members.
|