Wednesday, December 06, 2006
#
A question we often get here at our business intelligence courses is how to generate documentation for the OLAP cubes and dimensions built with Analysis Services. So we grabbed our Visual Studio and started developing a tool for this: ASDoc was born, and can be downloaded from http://www.u2u.be/res/SQL05_ASDoc.aspx. Check out this page from time to time, because we are adding new features to this product. Briefly, the tool queries the Analysis Services server, downloads the meta-data, and then builds an HTML page containing this meta-data. Have fun with it!
Thursday, June 01, 2006
#
Integration Services, the 2005 replacement of good old Data Transformation services, has so many control flow and data flow possibilities, that you often have more than one way to do a particular task. One of those things is inserting a bunch of data from a CSV file into sql server. I started some experiments on this, and the first results (please wait for more detailed results before we can draw reliable conclusions) show that Bulk Insert is not the fastest way to bulk insert data!
The setup: I have one file of 272 Mb (1 million records) of random data: 5 columns varchar(49), and 5 columns of integer data. I insert these in an empty table in SQL Server 2005 developer edition. There are no indexes nor constraints on this table. I try this insert in a number of ways:
- With the bulk insert control flow task
- With a data flow with a flat file source and
- an OleDb destination (default settings)
- an OleDb destination using the fast load option
- a SQL Server destination
All experiments where run 3 times, all on the same machine (the IS package is run at the same machine where SQL Server is running, which is a Pentium 4, 3 GHz, 2 Gb ram, 1 processor machine (heavy desktop)). Below, I show you the average execution time for each of these approaches:
- Bulk insert : 49 seconds
- OleDb destination: 460 seconds
- OleDb destination fast load: 39 seconds
- SQL server destination: 24 seconds
It's obvious that the OleDb destination is slower than the bulk insert, but I have to admit I was surprised to see both the fast load OleDb and the SQL Server destination to be faster (and for the SQL destination it is even about 2 times faster!) than the bulk insert. I'm going to run some more tests against this to see why this unexpected behaviour pops up.
Nico
Thursday, December 15, 2005
#
After enjoying SQL Server 2005 for about a month, this morning Analysis Services 2005 refused duty. It claimed that the SQL Browser (the tool which redirects requests to the right TCP port on the server, see http://msdn2.microsoft.com/en-us/library/ms165724.aspx for details) was not running, though this service was up and running (according to the services tool in the computer administrator). If I shut down the service and try to start it manually (with the ...\90\Shared\sqlbrowser.exe -c statement), it reports that it cannot start up the SSRP (redirect protocol) and it also fails in the OLAP redirector.
Weird... how come... A few moments of debugging (and a hint of my colleague Kris) later, it turned out that uninstalling the windows update nr 910437 solved the problem. So if you experience the same problem as I do, be careful with installing this windows update.
Nico
Friday, December 02, 2005
#
In SQL Server 2005, we can write stored procs, UDFs, aggregates and types in .NET. In some scenarios, it can be interesting to call web services directly from within the database. Since we can call web services easily from within .NET, and we can call .NET from within SQL Server 2005, it seems easy to create a stored proc or scalar UDF to call a web service. However, people who tried this (or attended one of my SQL 2005 courses) know that this was not the case with the CTPs: The proxy class generated by Visual Studio tries to build classes at runtime, and this type of code is not accepted by the SQL Server .NET host. However, there were some rumors on the internet that MS would solve this problem by generating 'well behaving' proxy classes from within the templates for building .NET stored procs, UDFs etc.
Since we have the final release now, it is time to check how things go. And it turns out that the proxy classes still have the same behaviour: building a proxy class by just adding a Web reference from within Visual Studio, gives you an assembly which can be loaded into Sql Server (deployed from within Visual Studio - but make sure you have permissions for creating external reference assemblies, and your database is trustworthy), but when you try to run them, you get this error:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'WSFunction':
System.InvalidOperationException: Cannot load dynamically generated serialization assembly. In some hosting environments assembly load functionality is restricted, consider using pre-generated serializer. Please see inner exception for more information.
So, in short, if you want to call a web service from within a .NET stored proc or UDF, you still have to use the approach as described in
this article.
Nico
Thursday, December 01, 2005
#
Hi,
In the past CTP releases of SQL Server 2005 and beta releases of Visual Studio 2005, it was always essential to install both products in the right order: First the client side tools of SQL Server, then the Visual Studio 2005 beta. If you did it wrong, you didn't get the templates to create and deploy .NET stored procedures, functions, user defined types and aggregates from within VS2005. Last week, I tested this on the final version of both products and... good news: the order doesn't matter anymore: first installing Visual Studio, then SQL Server 2005, and everything I needed was there... a small but soemtimes important detail, because reinstalling VS2005 is something you don't do for the sheer fun of it :-)
Nico