|
|
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.
|
|
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.
-
December 2, 2010
-
SQL Server Day Belgium
:
Scripting in Integration Services Data Flow
Integration services is a handy ETL tool, with many build-in sources, transformations and destinations. With the Script component, we can add custom .Net code to our integration services data flows. In this session, we investigate all the possible scripting scenarios: sources, destinations and transformations, synchronous and asynchronous transformations, with and without additional connections, with single or multiple output streams. So, if you have some .Net coding skills, and you are an SSIS developer, come and have a look at how to combine both.
-
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.
|