Shoshone Falls running full

This is not a technology post, but if you happen to be running by southern Idaho right now (around Twin Falls), now is the time to see Shoshone Falls (the Niagra of the West).

Sorry about the size of this image, but it had to be done.

Thoughts for the day

Here are my thoughts for today.

*Transitions are transparent like a fart is transparent -- but still noticeable.

*Hemorrhoids and jalapeños do not mix well. (Note, this one is not mine, but I have that one on good authority)

*I had another thought, but it got lost in the shuffle.

SQL Server 2005 SP1 is out

For those of you who wait until the first service pack for any Microsoft technology is out, wait no longer for SQL Server 2005!. SP1 has been released.

http://www.microsoft.com/downloads/details.aspx?FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc&DisplayLang=en

As with many service packs, Microsoft is sneaking in new features, you can see what is new here.
One of the promised features for SQL Server 2005 Express is in SP1: Text Search.


And something I had not noticed before: Microsoft SQL Server 2005 Express Edition Toolkit

ADOMD: GetSchemaDataSet musing and aggravation

I made a rediscovery today in ADOMD.NET: GetSchemaDataSet. This is a function that is callable from the AdomdConnection object and returns a DataSet, and a very useful function it is.

First off, what does it look like? Go here. If function complexity were judged by number of overloadable options, this guy should be awarded a medal at minimum.

You want a list of Catalogs/Databases on the server? Call GetSchemaDataSet. Like this:

AdomdConnection oConn = new AdomdConnection("Provider=MSOLAP;data source=local;"); DataSet ds = oConn.GetSchemaDataSet(AdomdSchemaGuid.Catalogs, new object[0]);
OK, so where does that get us? What GetSchemaDataSet returns is a standard DataSet with one Table. The columns in the table are as follows: CATALOG_NAME, DESCRIPTION, ROLES, and DATE_MODIFIED. The values we are most interest in are in the first column: CATALOG_NAME.

Now, retrieving the list of Catalogs is just one trick that can be performed by this function, you can get a taste of that by looking at the static AdomdSchemaGuid class. Browse to this page to see all of the possibilities. Using GetSchemaDataSet with the AdomdSchemaGuid you can get all sorts of data out of your cubes that is not available anywhere else in the API.

unfortunately, this isn't all of the story. Retrieving the list of catalogs is an easy task, same with retrieving a list of cubes, dimensions, kpis, measures, and members. But what if you only want a select group of members? Well, time to introduce yourself to the second parameter: restrictions. A entirely different beast there, and there is a reason it takes an array of objects.

The nice thing for the API designer about an array of objects is that it can take anything. The bad thing about an array of objects for the developer using the API is that it can take anything. And without documentation as to what can go there...oh yes, that is right were we are. Plus, there are some interesting compromises that were made as part of this.

When calling GetSchemaDataSet you can pass in a string and a AdomdRestrictionsCollection OR a GUID and a object array (there are some other options, but this is the basics of the dilemma). Also, the AdomdSchemaGuid thing I was talking about earlier, it is a static class with a bunch of properties that all return Guids. It is not, I repeat, NOT an enumerated type. Why is it not an enumerated type when you clearly treat it like an enumerated type? I have no idea.

Now the other part that sucks. If you want to use the AdomdRestrictionsCollection you have to pass in a string based schema -- but for the life of me I cannot figure out what format of string the thing wants! So as of right now I can't use the AdomdRestrictsionsCollection. Oh well I guess.

Furthermore, setting up the object array is not exactly trivial either. OK, technically creating an object array is easy. But getting it to return the data you want, not as easy. Here are the basics: for every value you want to pass in as a restriction you create two spots in the array. The first value is the column name, the second value is the value to filter by.
So technically, if you want to filter Dimensions by CATALOG_NAME, it should look something like this:

object[] oValues = new object[2]; oValues[0] = "CATALOG_NAME"; oValues[1] = "Adventure Works"; DataSet ds = oConn.GetSchemaDataSet(AdomdSchemaGuid.Dimensions, oValues);
At least that is what it would look like if it worked. But it looks like there is actually a bug report out on that feature (see here) and a fix, but not until SP1.

Oracle SQL Developer Review

So the Oracle SQL Developer has been out for at least a week now, and I have given the week to try to convert from TOAD to this new shine collection of bits.

Where to get it: http://www.oracle.com/technology/products/database/sql_developer/index.html

What is the Oracle SQL Developer? It is a TOAD replacement. Think of Query Analyser that comes with Microsoft SQL Server.

So, the good parts:

• Free (http://www.oracle.com/technology/products/database/sql_developer/index.html)
• Easy install.
  
• Syntax Highlighting
• Snippets containing commonly used functions
• Nice Connections tree that expands to tables/views/etc
  
• Drag and drop functionality
• Tabbed interface. So you can run multiple queries at once.
• Very nicely organized right-click menu (compared to TOAD anyway)
  
• Pretty to look at (interface is light blue)
• Works with Oracle 9.2 and above
  

The not so good parts:

• Written in Java. Not a condemnation in itself, but does make the app a bit slow. Takes a good couple of minutes to load up, and the refresh rate is terrible.
  
• 27 MB download (without Java 1.5 bundled in)
  
• The Connection tree: I like it, but it lists all Oracle connections in one tree, and all of the Aliases are part of the connection. So if you are working with multiple databases, and you have the connections expanded for both connections, you can end up doing quite a bit of scrolling. But, I still like it better than how toad does things.
  
• Memory usage -- it wants a lot of it. Just booting up, with no queries run, it is using 60 MB.
• Did I mention the refresh was slow -- oh ya, I did. Just checking.
• Had some problems with my TNSNames file (I have multiple homes)
  

One item to note about the performance: the performance issue has to do with initially loading the application and refresh (a lot of drawing). The query performance, or how quickly it displays data once it is loaded, is fine. I would rank the performance as noticeably slow, but not unworkably so.

I guess the final point: if you cant get TOAD, Oracle SQL Developer is a fine product. It beats the heck out of SQL+.

Microsoft buys ProClarity

Microsoft has agreed to buy ProClarity (very big news here in Boise).

As a former ProClarity employee, and a current user/customer, I do have some hope that this will be a good thing for both ProClarity and Microsoft, but we will have to wait and see how Microsoft adds ProClarity to their portfolio.

My hope is that ProClarity get sold along side the SQL Server product and gets encorporated into the MSDN Subscriptions. This would be very helpfull because it might finally open up some corporate lisencing of ProClarity.

Speaking selfishly, this would help me out quite a bit as well, as I do a lot of development with the ProClarity SDK...and I am very open to getting more customers.

Speaking REALLY selfishly, maybe now I can start working on becoming a Microsoft ProClarity MVP. :)