Friday, June 4, 2010

Using spatial data types in SQL Server 2008 R2 is not hard

SQL Server 2008 was a major upgrade and one of its new features was support for new data types such as spatial data types. Spatial data types will be increasingly adopted in dealing with globalization and handling the distributed nature of resources and events. Presently it supports 2D data with a possible 3D addition in the future.

Spatial data is basically of two types, the geometry type for flat-earth situations that can be handled by Euclidean geometry principles and the other the Geography type for situations dealing with locations on the ellipsoid or round-earth handled by spherical geometry. 

In the hyper-linked article  a clear guide to understanding spatial coordinates is presented in five easy to follow steps.

Step 1: Gather the resources
Step 2: Create a database
Step 3: Create a table to hold geography data type
Step 4: Populate the table with sample data
Step 5: Populating the geography data type the table's column

With this you can do some geocalculations.


P.s: You can read it for free by becoming a guest.

Thursday, June 3, 2010

MS ACCESS finds a home on SQL Azure

My wish has been finally granted and MS Access can now have a footing on SQL Azure. 3499 views and 37 replies back and forth has finally provided a solution. Blog entries and forum participation work after all.

How this got started may be found here.

Wednesday, June 2, 2010

Does PowerPivot compress smaller files efficiently?

The Analysis Services engine shipped with SQL Server 2008 R2compresses and processes the data which is loaded by the Analysis Services into the worksheet. All this happens in the background using the Power Pivot [Microsoft.AnalysisServices.Modeler.FieldList.Addin.Integration] menu option. The storage mode of this service called the VertiPaq  does the magic of manipulating columnar data in memory.

Results from a simple test of compression:

I downloaded the Power Pivot sample file in Excel format from the CodePlex site. Review my recent post here for details:

  • The file is called ContosoStoreData with columns from A to S with 307 Rows as shown here. The file size is 88KB on disc.

  • I copied the data and pasted into an excel spread sheet (not using the PowerPivot)and saved it as ContosoNormal. The file size of was 48KB on disc

  • Next I imported ContosoNormal into an Excel Spreadsheet under PowerPivot control and named the new file, ContosoNormalReverted. It had the same number of columns as ContosoNormal and the same number of rows(307). The file size was 52KB on disc.
My computer was an Acer Notebook with the following specifications: Windows 7 Ultimate 32bit OS with 3.00 GB RAM. U2700@1.3 GHZ. Free space of 33 GB available on the 288 GB hard disc.