Wednesday, April 21, 2010

Report Builder 3.0 Part 2...

In my earlier post on Report Builder 3.0 which is part of sql server 2008 R2, i had written about the basic features available. As i get to work on a geo coding project and playing around with the Maps feature available, there are basic elements of spatial terminology which needs to be understood so that a better map based reporting soluton can be developed. The technet microsoft site mentioned here
http://technet.microsoft.com/en-us/library/ee240845(SQL.105).aspx goes over basic terminologies associated with Map based reports like Viewport,Shape files, Spatial dataset,Polygon,point,line and tile layers, all of these in totality makeup the map report. A better understanding of the concepts would enable us to build out better map based reports. Here is a picture of map based report generated with report builder 3.0, one of the neat things that can be done is add Points(here it is in the shape of a push pin) to highlight locations.

Thursday, April 15, 2010

EXCEPT,INTERSECT

I have been working a project where in data needs to archived from the main reporting database at ed of each month. Once the data has been archived, I run a validation stored procedure to check the counts in the archive and the main source table. Once the validation is complete, a message is sent out to the developers indicating how many matches and/or mis matches are found in the data. I noticed the validation process was taking too long to report the results since the number of checks that were to made were on a small set of data. Here is where i tried the use of INTERSECT and EXCEPT clause available in SQL Server 2008. The results were generated very quickly since these were set based operations, earlier i was using the cursor approach which was taking too long to report the comparison results. Here is an example of the intersect and except clauses. For the matches I used the INTERSECT clause:

SELECT SummaryTable,cnt FROM #tmp_Arch

INTERSECT
SELECT TableName,cnt FROM #tmp_sumry

For the rows which were not macthing I used the EXCEPT clause.

SELECT SummaryTable,cnt FROM #tmp_Arch

EXCEPT
SELECT TableName,cnt FROM #tmp_sumry

Tuesday, April 6, 2010

Virtual DB...

In today's IT world, one of the concepts which is taking prominence in the server and infrastructure management, is the concept of virtualisation. This allows the companies to consolidate servers, this in turn gives the company to save on expenses related to server management. Virtualisation also is one of the skills sought after in network/database administrators. Along the same lines of thought is the product SQL Virtual DB bought out by Idera (http://www.idera.com/), the link for the product is http://www.idera.com/Products/SQL-Toolbox/SQL-virtual-database/. Pinal Dave (SQL Server MVP) has outlined a pretty detailed demo of this product in his blog (http://blog.sqlauthority.com/). The features of this product are interesting and useful especially when a Virtual Database is created from existing database backup, the Virtual DB does not ocuupy any additional disk space. The regular sql queries can be run on the Virtaul Database, these kind of databases can be used to create effective Test databases which can mimic production databases.