Krishna Gupta

[kcgupta-blog]

Using MVC 4 Model objects inside Javascript for Google maps

Using a server-side object as a Model in an MVC 4 View is quite straightforward. Just define the @model at the top of the .cshtml file, and then use it in the rest of the View as needed, taking advantage of the excellent model binding system in MVC 4. However, using the same Model from within a piece of javascript code is a whole different story. The problem arises because while the rest of the .cshtml file is evaluated on the server side and converted to HTML before being served, the javascript code is sent to the browser and runs on the client side as-is, and does not have access to the Model and its property values. The solution lies in writing our View such that the Model properties in the .cshtml file are evaluated at the server level itself, and the javascript code simply receives the resulting values and not the property names themselves. Here is a sample code that demonstrates this approach, using the “address” information from a Model object to display a Google Map in the webpage. Using this approach, no changes at all are required in the Controller - the View coding takes care of everything (while not taking over any business logic). Assuming that a Layout file is being used to support the View, the steps required to display a Google Map on the webpage are: 1. In the “head” section of the Layout, create an @RenderSection, so that our main View can insert the javascript into the “head” of the resulting HTML: @RenderSection("pagespecificcode", required: false) 2. In the “body” section of the Layout, call the initialize() method of our javascript when the onload event fires (be careful with this, as it’ll fire off a method of that name in any child page, not just the one you want!):

3. In the .cshtml of our specific View itself: @section pagespecificcode{ @*BEGIN - Google Maps calculation and rendering*@ var geocoder; var map; var address = @Html.Raw(Json.Encode(Model.AddrLine1 + ' ' + Model.AddrLine2 + ' ' + Model.AddrCity + ' ' + Model.AddrState + ' ' + Model.AddrPostalCode)) @*This function is called from the 'onload' event of the 'body' section of the Layout*@ function initialize() { geocoder = new google.maps.Geocoder(); var latlng = new google.maps.LatLng(0.0, 0.0); var mapOptions = { zoom: 13, center: latlng, mapTypeId: google.maps.MapTypeId.ROADMAP } map = new google.maps.Map(document.getElementById("map_canvas"), mapOptions); codeAddress(); } function codeAddress() { geocoder.geocode({ 'address': address, 'region': 'US' }, function (results, status) { if (status == google.maps.GeocoderStatus.OK) { map.setCenter(results[0].geometry.location); var marker = new google.maps.Marker({ map: map, position: results[0].geometry.location }); } }); } @*END - Google Maps calculation and rendering*@ } The crucial line in the code above is the following one; it is taking the Model properties and converting them to a string, which is then set as the value of the javascript variable. One little drawback here - there’s no Intellisense available while we’re programming the line. var address = @Html.Raw(Json.Encode(Model.AddrLine1 + ' ' + Model.AddrLine2 + ' ' + Model.AddrCity + ' ' + Model.AddrState + ' ' + Model.AddrPostalCode)) 4. Later on in the .cshtml file, at the point you want to display the map, the only section that’s needed to render the map is this - there’s no code, just define the div:

5. Make sure the CSS file defines the correct size and any other properties you want for the rendered map: .gmap { width: 340px; height: 180px; border: 1px solid lightgray; } Done. Now once the Model object is populated by the Controller, the View will take care of the rest and render the Google Map nicely. **Caution here:** We should not convert the entire object as it creates a large Json string, most of which are likely not needed for our purpose above. They will simply add to your server outbound traffic. Convert only the fields needed, as illustrated above. Checking the Page Source shows exactly what’s being converted. Please note: The above example does not use an API Key from Google. It will work fine without that key; however, Google places limits on how many maps can be rendered, and sites with lots of traffic will need a key. Please see details of the API, the Key, traffic limits and various other aspects of the system on the [Google Maps support page](https://developers.google.com/maps/documentation/javascript/tutorial). Happy coding!

[kcgupta-blog]

Optimizing image loading in MVC VS-2012 websites

Images often take up a large proportion of the load time of a webpage, second only to data retrieval from a database. Here are 3 things we can do in our Visual Studio MVC website projects, to *significantly* reduce that load time. 1. **Reduce file size, by controlling the image dimensions and its jpeg quality.** Ideally, images should be of exactly the dimensions (width and height in pixels) that are to be finally displayed on the webpage (typically controlled by the css img width and height properties). Also, reducing the jpeg quality (increasing compression) a little may result in a drastic drop in file size while still having very good screen-display quality. If you are using the EncoderParameter class to process images for uploading, consider using a compression level of 90. 2. **Enable image caching at both server and browser levels.** Use the OutputCache attribute on your controller action method that retrieves the images, and set its location property to “ServerAndClient”. The duration property can be set appropriately depending upon your website, and will determine how long your web server and the user’s browser are going to cache the image. *Added bonus:* The server caching will reduce your storage data-out costs, and the browser caching will reduce your web server data-out costs. 3. **Eliminate duplicate pulls of the same image, by always using an identical method call pattern.** Parameters in an action method are not case-sensitive, nor do they need all the parameters to be included in a call (missing parameters will receive a null). However, when retrieving the same image from different pages or different parts of the same page, inconsistency in the calls from your .cshtml or .vbhtml files will cause your browser to consider these as separate elements, and it will send multiple requests to your server. If the calls are identical, most browsers will send only a single request, and will then cache and re-use the same image. *Recommendation:* In your @Url.Action() calls, make sure to specify all of the action method’s parameters, and treat them as though they were case-sensitive. Using the above 3 suggestions, you should see significant load time improvements. A good way to do a quick (but pretty detailed) check of the page’s performance in the Google Chrome browser: Right-click on the page | click on Inspect Element | click on the Network tab | refresh your page. You’ll see exactly what was loaded, and how long each element took. You can click on the Images tab at the bottom, to examine just the images. Then tweak your website using the above suggestions - and look out for improved performance and happier users! Happy coding!

[kcgupta-blog]

How to deploy a .Net 4.5 website to Azure using Visual Studio 2012

Last week, Windows Azure started supporting .Net 4.5. Long awaited, most welcome feature! Now we can create and deploy our .Net 4.5 websites straight to Azure, all from within Visual Studio 2012. No add-ons needed. In your Azure account: --- 1. Sign-in into your Azure account. If you don’t have one, you can always sign-up for a free 3-month trial. 2. Create a new Azure web host on the Azure server: Click on **Web Sites** in the left bar, then click on the large **+ New** button at the bottom. 3. Select **Compute / Website / Quick Create** (Note: you may have to sign-up for the Preview program at this point, if not already done so - it’s simple, painless and quick). 4. Give your new Azure web host a name that you like. The URL for your new site will be of the form: *http://yournamechoice.azurewebsites.net*. 5. In a minute or 2, the host will be set up, and good to go (to receive your actual web content). 6. At this point, if you navigate to the URL, it will show an auto-created default page. 7. In the main Azure window, click on the name of your new web host (not its URL), to get to its **Dashboard** page. Click on **Download Publish Profile**, and note where the Publish Profile was downloaded into your local machine. This will be used by Visual Studio 2012 in the next step. In Visual Studio 2012: --- 1. Open up your .Net 4.5 website project, and press Ctrl-F5 to make sure it runs well locally. 2. Right-click on the website project, and select the **Publish…** option. 3. You’ll get a new Publish Web window. Click the **Import…** button, to import the Publish Profile (.PublishSettings file) you obtained earlier from your Azure website host. 4. You can check everything by using the Next> button, and click Publish when all looks good. 5. VS 2012 will take just 2 or 3 minutes to upload everything to your Azure host. 6. At the end of the upload, Visual Studio will automatically bring up your new website, in your default browser. That’s it… done! The whole process has been made very direct and clean - even eliminating the need to add the extra “Windows Azure Cloud Service” project (as had to be done with VS 2010 or with .Net 4.0 websites earlier). Happy coding!

[kcgupta-blog]

Adding custom fields to a VS2012 MVC4 SimpleMembership system

Add custom columns/fields to your MVC4 SimpleMembership “UserProfile” table MVC 4 in Visual Studio 2012 introduces the new SimpleMembership Provider as its default web-based authentication system. While this system is much simpler than the earlier ASPNETDB system that it replaces, there definitely are a few new and neat tricks to be learnt. One of them is: How do I add custom fields to my MVC 4 registration system? It can be done in just 4 simple steps: 1. Start up VS 2012, and create a new MVC 4 web project. Set up your SimpleMembership registration system - for some help, please see my blog post on [setting up a SimpleMembership Provider in a VS2012 MVC4 project.](http://www.dwdmbi.com/2012/09/visual-studio-2012-uses.html) 2. Next, run the project and navigate to the login page, so that the tables get created. It is not necessary to create a user at this point. 3. Then, in your database, alter the table UserProfile, and add a column: FullName (nvarchar(50)). 4. Next, add/reference the new FullName field in the following programs in your project: In class UserProfile, in *AccountModels.cs* (to ensure the class matches the table columns): [Table(“UserProfile”)] public class UserProfile { [Key] [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)] public int UserId { get; set; } public string UserName { get; set; } // Customization: Field(s) added public string FullName { get; set; } } In class RegisterModel, in *AccountModels.cs* (to enable handling on the registration form): public class RegisterModel { // Existing code // … // Customization: Field(s) added [Required] [Display(Name = “Your full name”)] [DataType(DataType.Text)] public string FullName { get; set; } } In the *Register.cshtml* view (so it’ll appear on the user registration form), add a new li element with the following content: @Html.LabelFor(m => m.FullName) @Html.TextBoxFor(m => m.FullName) In the [HttpPost] *Register* ActionResult, in *AccountController.cs*, modify the call to the CreateUserAndAccount method (to write the new column to your database): // Customization: Field(s) added when creating a new account WebSecurity.CreateUserAndAccount( model.UserName, model.Password, new { FullName = model.FullName } ); Now run your program and register a new user, supplying a value for the FullName field… success! The new row created in your database table contains the value supplied for the new column! **Note:** This post covers how to create a custom column. A near-future post will explain how to *access the value* of that column from within your VS 2012 MVC 4 projects. Please re-visit my blog occasionally :) *Happy coding!*

[kcgupta-blog]

Visual Studio 2012 uses SimpleMembership as its new Membership Provider

Simplified approach to implementing Forms Authentication in MVC 4 Web-apps in VS 2012

Feature highlights

Schema gets built directly - no more aspnet_regsql.exe (or  aspnet_regsqlazure) needed;

Much simplified as compared to the earlier membership provider;

Usable both for SQL Azure as well as on-premises databases;

Supports OAuth/OpenID, to authenticate users through Facebook, Google etc.

Easy customization: Adding custom fields to your MVC 4 Registration System

The most interesting details of SimpleMembership

Visual Studio 2012, launched August 15th, uses the new (and very appropriately named) SimpleMembership as its default forms-authentication membership provider for MVC 4 Web Applications, replacing the ASP.NET Membership Provider that all of us have been using for many years. This is a very welcome change. Some of the most interesting changes that we see immediately in this are:

Highly simplified database schema - has just 5 tables, no Views, and no stored procedures.

Does not use the aspnet_regsql.exe command line tool to generate the schema. Rather, the tables are generated automatically the first time you add a user into the system (through your application). The database used is whatever you set in the connection string in your web.config file.

The same approach can be used to create the Membership system in Windows Azure, simply by giving the correct connection string in web.config. Since there are no stored procedures and Views to worry about, the system is just a set of tables that is easily synchronizable between your SQL Azure database and your on-premises database.

Another plus for Azure users: now you don't have to worry about the aspnet_regsqlazure tool any more, because the tables are generated by the new system itself.

The UserId is now an int column, not a Guid that was used in the previous ASP.NET Membership system. This simplifies the system even more, and reduces space requirement a bit. Please note, however, that you may have to pay attention to this if you need to merge data from 2 separate implementations of the SimpleMembership provider, else there may be key duplication conflicts.

Supports user authentication through their Facebook, Google, Microsoft Live, Twitter etc accounts, by simple changes in the AuthConfig.cs file in your project (for details, please visit: OAuth/OpenID Support for WebForms, MVC and WebPages).

A quick VS 2012 web project to check out SimpleMembership

1. Create a new ASP.NET MVC 4 Web Application, and call it, say, SimpleMembershipTest.

2. On the next screen, select Internet Application, and create your new project.

3. Once the new project is created, it's a good idea to update all your NuGet packages - do this by clicking on Tools \ Library Package Manager \ Manage NuGet Packages for Solution, and then selecting Updates > All. Update each of them one by one by clicking on the Update button. (Note - all the DotNetOpenAuth packages will get updated when you simply update the first one.)

4. In the new project's web.config file, change the connection string for DefaultConnection reflect your server, database and relevant db user info. If you specify the "sa" user, even the database itself can be created by your app, however this obviously does not represent best practices.

5. Now press F5 or Ctrl-F5 to run the app, and click the Register button - specify a new user id and password and voila! Your new database schema gets created, along with a data row for the new user! And do notice that it's very lightweight - no SPs and no Views. 

6. Select data rows from the tables, and you'll see the new user has been created there.

Happy coding!

[kcgupta-blog]

Using VSS 2005 with Visual Studio 2012

It's easy to integrate Visual SourceSafe into Visual Studio 2012

Visual Studio 2012, launched on 15th Aug 2012, is a terrific IDE. While most of its documentation includes references to using Team Foundation Server (TFS) integration as the source control, it remains very easy to use VSS 2005 instead, by using the VSS plug-in to get a seamless experience. This is extremely useful to those of us who have, for years, been using Visual SourceSafe 2005 with Visual Studio 2010, are happy to be in the comfort zone, and find it simpler to stick to VSS until absolutely forced to move.

Steps to follow:

A. Select VSS as the plug-in:

Make sure the VSS 2005 client is installed on your machine. If you have been using it with your VS2010 installation, then it's already there.

In VS2012, go to TOOLS > Options

The Options window will come up. In this window, expand "Source Control" on the left pane, and click on "Plug-in Selection". You should see the following choices in the dropdown (which may vary depending on what other source control plugins you have available on your machine): None, Visual Studio Team Foundation Server, Microsoft Visual SourceSafe, and Microsoft Visual SourceSafe (Internet)

Select Microsoft Visual SourceSafe from the dropdown

(Do not click the OK button at this stage)

B. Next, adjust the VSS environment settings, if necessary:

Under Source Control in the left pane, click on "Environment"

Make sure that "Visual SourceSafe" shows in the dropdown selection in the right pane, then look through all the settings to make sure they are as desired. For most cases, the defaults should be fine.

(Do not click OK even now!)

C. Finally, make sure the VSS-specific settings are correct:

Under Source Control in the left pane, click on "Plug-in Settings"

Make sure the VSS login id is correct

Click on the "Advanced..." button, and examine each tab of the resulting "SourceSafe Options" window to make sure everything is as expected. Click OK in that window once you're done.

Click OK in the "Options" window, to save your selection.

Specifying VSS 2005 as your source control for VS 2012 is now complete. You should now be able to right-click on any solution in the Solution Explorer, and select "Add Solution to Source Control". This would display the VSS log-on window, and enable you to take it from there.

Once your solution is in VSS, with the correct settings, you'll find most activities to be highly simplified: Starting to edit a file automatically checks it out, right-clicking gives options such as save changes, compare versions, or undo a checkout, and so on.

Check it out!

[kcgupta-blog]

Visual Studio 11 Beta - installing and getting started

Microsoft announced the release of the much-anticipated Visual Studio 11 Beta (VS 11) last week. After downloading and installing it today, here are my first impressions. Downloading The download from MSDN is an ISO (DVD image) file. It's almost 1.8 GB in size, and took a few hours to get (especially as I was simultaneously downloading SQL Server 2012 RC as well -- that one itself being 4.4 GB). Next, I unpacked the ISO using 7-Zip, which is available (for free) from download.cnet.com. This way I could run the install directly from the hard disk; the alternative is to burn a DVD and then install Visual Studio 11 from that DVD. Or, if we want to neither unpack nor burn a DVD, 7-Zip also enables us to view the contents of the ISO, and permits running the installer directly from there without unpacking. Installing Installation was quite straightforward. It took about 2 hours on my machine, and requires about 10 GB of space. The first thing to notice was the new splash screen - stylish in blue and gray. While waiting, there's a cool set of 5 dots that repeatedly move across the screen, replacing the familiar green moving bar. With 2 hours to ponder over them, I concluded that each was pulling the one behind it using a rubber-band. This physics is especially noticeable when the 5th one is pulled away.

Once the install is complete, and after a mandatory restart, VS 11 is good to run. Running Visual Studio 11 The first time it's run, it asks for your development environment preferences - C# or VB development, Web development and so on. Along with that, it also offers to copy preferences from the prior VS version - not sure how this overlap works or gets resolved.

Once I got past that, the new Visual Studio 11 home screen came up. For Visual Studio 2010 users, the first difference is the switch in placements of the Open/Create Project and the Getting Started materials. The latter is now on the left, much more prominent. In some ways this highlights the available learning material, and that is a good approach. Graphics on the screens are monochrome presently; I imagine the designers at Microsoft are hard at work creating beautiful full-color icons that'll come with the final release.

Visual Studio 11 comes with the latest .NET 4.5 framework, as well as with MVC 4. Together, the whole set forms a powerful team, and there's a ton of new material to learn -- as well as to enjoy! They contain great features both on the IDE side as well the .NET library side. It is installable side-by-side with Visual Studio 2010, and in fact one of the most interesting features (first time for any VS version, I think) is that a Visual Studio 2010 .NET 4 project can be opened in VS 11 as well -- without breaking it for VS 2010. This sounds like a really good feature for teams that may have some members using VS 2010 and others VS 11, allowing the team to upgrade gradually. Shown below is a screenshot of the default MVC 4 web app, out of the box and without any additional coding at all. It definitely is a very far cry from the VS 2010 MVC 2 or MVC 3 starting points.

Downloading and installing Visual Studio 11 is easy, so don't wait any longer! Happy coding!

[kcgupta-blog]

Deadlock recovery using TRY-CATCH in SQL Server stored procedures

Deadlocks occur in SQL Server when two separate transactions require exclusive access to the same resources, but each transaction blocks a portion of what is required by the other. In this situation, SQL Server chooses one of them as the 'deadlock victim', and rolls back the victim's transaction while letting the other one continue through. The 'victim' will receive error ID 1205, along with a message saying that the transaction was deadlocked on resources with another process and has been chosen as the deadlock victim, and recommending that you rerun the transaction. Deadlocking recovery can be handled gracefully within the stored proc, to retry a few times using a TRY-CATCH block. This will first try the transaction, and in case it fails, then loop around to retry it again. Here's a sample code:

-- Specify how many times to retry this task

-- in case it is selected as the deadlock victim

DECLARE @retry INT;

SET @retry = 5;

WHILE (@retry > 0)

BEGIN

    BEGIN TRY

        BEGIN TRANSACTION;

        -- The body of the stored procedure, containing

        -- various INSERT/UPDATE/DELETE/SELECT statements

                    -- Statements

        -- More statements

        -- If we reach here without problems, we are good to go

        -- Set the loop variable to 0 so we can exit out, and COMMIT

        SET @retry = 0;

        COMMIT TRANSACTION;

    END TRY

    BEGIN CATCH

        IF XACT_STATE() <> 0

            ROLLBACK TRANSACTION;

             -- Retry again if this is the deadlock victim

        IF (ERROR_NUMBER() = 1205)

            SET @retry = @retry - 1;

        ELSE

            SET @retry = -1;

                -- If not retrying again, log the error         -- (Removing the IF statement below will cause each retry to be logged as well)         IF @retry <= 0

        BEGIN

            INSERT INTO ErrorLog

                (SourceProgram, SourceLine, ErrorNumber, ErrorMessage)

            VALUES

                (ERROR_PROCEDURE(), 'Line: '+ CONVERT(varchar, ERROR_LINE()),

                 ERROR_NUMBER(), ERROR_MESSAGE());

        END

    END CATCH

END -- End WHILE loop.

Happy coding!

[kcgupta-blog]

Let your database applications happily traverse timezones

Sometimes developers come across the situation where a datetime value is stored on each row in a table (for example, the created or last-modified datetime), and then subsequently this datetime value needs to be accessed and processed in some way by a client program. If the client program is such that it may be running in any arbitrary timezone (e.g. web users), it becomes difficult or impossible to be sure about when exactly the underlying row was created or modified. A standardized approach, using the UTC (Coordinated Universal Time), is required. On the SQL Server side:

When storing a datetime in SQL Server, always generate the datetime in the SQL stored proc itself (in the INSERT or UPDATE statement); never pass it from the calling client program.

Always store the datetime as UTC and not local -- use the GETUTCDATE() function in SQL Server, not GETDATE()

For comparisons of current datetime against the datetime on a given row, again ONLY use the current UTC time generated by the SQL Server and not by the client app. To get the current server UTC time, you can create a simple stored proc that just returns the GETUTCDATE() value, perhaps as an OUTPUT parameter.

On the .NET client side:

Use DateTime.Compare method to compare the datetime values received from SQL Server

If you need to convert a UTC time returned by the server into the local time, for display purposes, use the DateTime.ToLocalTime method in your client application. This also automatically factors-in any Daylight Saving time rule in effect.

Observing these simple rules will ensure that the date/time comparisons remain valid and accurate in your database applications, whether they are deployed over local networks or over the Internet across the globe. A positive side-effect is also that if you move your server to a different timezone, the logic to handle UTC datetimes needs no change at all - just make sure the server system clock is set to its local timezone. Happy coding! PS: Why is "Coordinated Universal Time" abbreviated to UTC and not to CUT? Check out these links: http://www.nist.gov/pml/div688/utcnist.cfm http://en.wikipedia.org/wiki/Utc#Symbol.

[kcgupta-blog]

Use (UPDLOCK, READPAST) to read a SQL Server data queue

The problem A useful business scenario involves reading data from a "queue", which would be a table consisting of rows generated by one process, but read by a separate process that runs independently.  The second process (queue reader) may be running with multiple threads, or having multiple instances (such as a multi-user desktop or web application).  This can often lead to unexpected results, such as duplicate data retrieval, deadlocks, or other concurrency issues.  The SQL Server stored procedure called by such an application may involve a set of steps similar to:

SELECT the ID of a specific row (meeting some criteria) into a temporary variable

UPDATE a flag on that row to mark it as "processed"

SELECT the data from that row along with rows from any related tables

Enclosing the above logic inside a BEGIN TRANSACTION - COMMIT TRANSACTION segment does not prevent any parallel processes from reading the same ID in step-1 above. This is because while the transaction prevents both processes from trying to update the same row, but the SELECT in step-1 of one transaction does not block the other transaction from also doing the same SELECT. The solution An efficient approach to solving this is to use the WITH (UPDLOCK, READPAST) table hint in the SELECT statement. UPDLOCK specifies that update locks are to be taken and held until the transaction completes, and READPAST specifies that SQL Server not read rows (i.e. skip them) that are locked by other transactions. The combined effect of these table hints is that the first transaction to get to the SELECT statement establishes a lock on the row (or page), and the second transaction simply skips the locked rows and reads the next available row.  Without the READPAST, the second transation would get blocked by the first one until the locks are released. Here's a sample code segment to use these table hints in a stored procedure:  

BEGIN TRANSACTION SELECT TOP 1 @rowid = a.RowID FROM TestTable a WITH (UPDLOCK, READPAST) ORDER BY a.ProcessedOnUtc, a.RowID UPDATE TestTable SET ProcessedOnUtc = GETUTCDATE() WHERE RowID = @rowid -- (rest of the data retrieval and processing statements)   COMMIT TRANSACTION

A very important Gotcha! To enable this to work correctly, there must be an index on the column(s) being used to identify the selected row(s), in this case the RowID column.  Without that index, SQL Server will need to look through the table to find the correct row for the second transaction, which will then get blocked since it won't be able to get past the row that's locked by the first transaction.  The result in such a case will be that duplicates will be avoided but deadlocks will still occur - the same as using UPDLOCK without READPAST.

[kcgupta-blog]

Setting up a software RAID-5 on a Dell T310 server

Got the opportunity to wet my feet on setting up a software-based RAID-5 on a Dell PowerEdge T310 server, within Windows Server 2008. Quite an experience!

The hardware The system was originally equipped with a 250GB drive. It contains the OS and we left it alone. Installed 3 additional, identical, drives: 1TB Western Digital Caviar Black. These are 7200 RPM drives with 32 MB buffers. The question was whether to go for the RE4 drives (also from WD), but those are about twice as costly, and this is a medium load dev & test server, so it was decided to stick with the Blacks and save some Green. Drive installation This was somewhat unexpectedly tricky. The physical installation was a breeze of course - just pull out the drive trays, set the drives into them, push them back in and snap in the connectors. But, the system refused to boot after this - LED indicators 1 and 4 lit up and that was it; the system did not even get to the BIOS setup screen. After some initial irritation ("a drive must be DOA!"), we tried out the drives in a different machine (desktop, not server); all worked perfectly. Good. Then, a bit of research into the owner's manual for the T310 showed that there's a jumper on the motherboard that clears the stored configuration when the system is next restarted. Set the jumper accordingly, plugged-in and restarted the system, and... no luck! What! Restored the jumper, tried again, same results. Again toyed with the jumper; nope. I think the fourth time around I saw what initially looked like an unbelievable dream - various LED lights flashing back and forth, the disk light flashing, and generally speaking a sight very different from the steady 1 and 4 indicators! Scrambled to press F2 to get into the setup screen. The drives showed up fine. There must be something that I missed here. Drive installation would not be such a hit-or-miss approach. Besides, this would tend to disprove the theory that trying the same thing repeatedly and expecting success is madness. Setting up the RAID-5 in Windows Server 2008 Once Windows booted up, went into Start > Computer Management, and clicked on Disk Management (under the Storage subsection). The 3 additional drives showed up fine, although unformatted and unavailable for use at this point. In Windows Explorer, they are not showing up yet. Right-clicked on one of the new drives in the Disk Management UI screen, and was immediately presented with an option to set up a RAID-5. Selected that option led to further screens where the remaining 2 drives were added into the RAID, and that was about it. Once the new drive name and letter were specified, the system started to sync the 3 drives together, to form a 2TB volume using the 3x1TB drives. Note that the system can set up the drives as RAID-0, RAID-1 or RAID-5; available options will depend on how many drives are available (with just 2 drives, the RAID-5 option will be grayed-out). Another important note when setting up a RAID-5: you cannot have data on a drive, and then add 2 more drives and try to get them all into a RAID-5 configuration (though you can mirror the original drive). All the 3 drives must be empty when you start. Once we gave the go-ahead to start the sync, the system takes a long, long time! This is the progress after about 7 hours:

Interestingly, while the sync is going on, I can work with the drives - they show up as a single 2TB volume in Windows Explorer (E: in this case). I tried creating a simple Notepad document and it seems to be fine. However, I'm going to wait for the entire sync to complete before loading any real data on the volume. Once done, this will give me 2TB of space, and a little peace of mind against disk crashes.

[kcgupta-blog]

Data Mining rated as a "hot" career choice by UCSD Extension

The University of California San Diego (UCSD) Extension, in its study called "Hot Careers for College Graduates 2010" gives top marks to Data Mining as a career choice for recent and mid-career college graduates.  Its hottest 10 career options are listed below.  The top 4 careers continue to remain in computers (inspite of the significant outsourcing of software development jobs overseas). 1. Healthcare Information Technology 2. Mobile Media 3. Data Mining 4. Embedded Engineering 5. Geriatric Healthcare 6. Occupational Health & Safety 7. Spanish/English Translation & Interpretation 8. Sustainable Business Practices & Greening of all Jobs 9. Feature Writing for the Web 10. Teaching English as a Foreign Language URLs for the study Brief summary: http://extension.ucsd.edu/about/display/dsp_release.cfm?vAction=view&vEventID=566 Detailed paper: http://extension.ucsd.edu/about/images/careerReport.pdf A quote from the summary

"Looking for a needle in a haystack is a good analogy for data mining jobs. Data mining is the technique of extracting specific types of information or patterns from large databases, such as data warehouses. Advanced statistical methods sift through large volumes of data, providing answers to questions that were once too time-consuming."

A quote from the detailed PDF document

"More importantly, data mining is a rapidly growing industry due to the explosion of available data. A study by students and faculty at the University of California Berkeley found that the amount of data in the world doubles every three years. For this reason, more employees are needed in the data mining industry to drill down, analyze and interpret the data. "Career prospects exist in several areas, such as advertising technology, fraud detection, surveillance, web mining, probabilistic trading, risk management, business intelligence, scientific research and law enforcement. Data mining requires comprehension of algorithms and advanced statistics, and the ability to program and use advanced software. Job hunters with computer science and statistics training, along with good business sense, would be well-suited to this career. Individuals with an understanding of the appropriate math and computer science would also be well qualified. "Data Mining Analysts, Data Mining Researchers, Data Mining Scientists and other Data Mining professionals can expect to earn high wages."

[kcgupta-blog]

SSIS 2008 Transforms: Term Extraction and Term Lookup

SSIS 2008 includes a very good lineup of transformation task components.  In this article, we take a brief look at a pair of very interesting, related transforms that help us analyze unstructured data: Term Extraction and Term Lookup. The Term Extraction transform Have you ever had the need to extract the nouns and noun phrases from a text column (or a text file)?  And when writing that long program to do it, did you wish there was a simpler way?  If the answers are "yes" and "yes", then the Term Extraction transform is your friend!  This transform analyzes free-flowing text in your data source, and enables you to pull out nouns and noun phrases that are found in it, along with the frequencies with which they occur. Advanced settings for the Term Extraction transform enable you to select just 1-word nouns ("SQL"), noun phrases ("SQL Server 2008"), or both nouns and noun phrases.  The count itself can be just a simple count of the frequency of occurrence, or computed using a formula called TFIDF, which stands for Term Frequency and Inverse Document Frequency.  The TFIDF calculation delivers a weighted result, based upon the frequency of the word or phrase, number of rows in which it occurs, and the total number of rows in the data.  Try out both approaches, to see which one best meets your needs. The Term Lookup transform Term Lookup also breaks up incoming text into nouns and noun phrases; however, it then compares these to a set of user-defined terms and delivers the frequency counts of only the matching ones, on a row by row basis.  So, once you have the list of terms from the earlier (Term Extraction) transform, you can review and edit that list and retain only the ones of interest, and then run the Term Lookup transform against that list to get counts of the terms matched on each row. Since the results of this transform are displayed at row-level detail, a given row may occur multiple times in the results if it contains multiple flagged terms.  If you need to group the results by the term being matched, you'll need to use the Aggregate Transform in conjunction with the Term Lookup transform. Using these powerful transforms to analyze unstructured data For an actual project, it could be of great value to run the Term Extract transform initially and then once in a while, and have a domain expert or Business Analyst review the results and edit the list.  That list then is used by the Term Lookup transform, which would run regularly, perhaps weekly.  As a practical example, take the case of the Customer Support call-in system of a packaged software manufacturer.  When logging a support call, the technician will typically select the product line and application module, perhaps select a generic pre-programmed problem code from a dropdown, and then enter a few words of free-format text into a field, noting the problem as described by the customer as well as some comments etc.  Over time, the database may consist of millions of rows.  Using this pair of transforms on that free-format field, management will be able to narrow down the data to the most frequently encountered problems within each product line, thereby getting a clearer view of the areas to focus on.  Powerful results - from unstructured data!

[kcgupta-blog]

The Dimensional Model Star Schema - a quick overview

Data organization - DW vs. OLTP The tables in a DW are organized precisely opposite to how they are done in a regular OLTP database - highly de-normalized in a DW vs. highly normalized in an OLTP system. The reason is that their objectives are different. A DW system is read-only and built for fast queries on aggregated data, while an OLTP system is read-write and built for fast Create-Retrieve-Update-Delete (CRUD) operations on individual transactions. Database developers are used to living and breathing normalized systems.  Therefore for them, when transitioning to BI design, it becomes most important to think in terms of de-normalizing everything! Dimensional modelling - Fact and Dimension tables Data in a DW is organized into tables that are called Fact and Dimension tables.  The fact tables contain the actual data (often called measures), which is typically numeric and can be aggregated, such as the quantity and unit price of line-items from a sale invoice.  Dimension tables, on the other hand, contain the attributes of that data - the date of sale, item sold, customer name and address, and so on.  Foreign keys on the fact tables link to the primary keys on the dimension tables.  A Data Warehouse can have as few as 1 fact table and 1 dimension table. Fact tables run deep - millions or sometimes even billions of rows.  Dimension tables are very wide (remember de-normalization!) but not deep - lots of columns but generally maxing out at 1 or 2 million rows. The arrangement of the fact and dimension tables is called the Star Schema.  To visualize the star, think of the fact table sitting at the center, and lines radiating from it that connect to each dimension.  The instant advantage of the star schema is that each fact table row is just 1 join away from any of its dimensions (though a snowflake dimension is an occasionally-used exception), resulting in super-fast query results.  A query asking for data on sales of Mountain Bikes in California during the last calendar year will likely look at only 3 dimension tables, with each being linked directly to the fact table.  But the same query in the OLTP system, because of normalization, is going to hit a much greater number of joins in relational tables (state - customer address - customer id - invoice header - invoice detail, to determine just the California sales alone). Data in the DW is not real-time.  It is loaded on a regular schedule from the OLTP system (and even other non-relational sources such as flat files and spreadsheets etc), often nightly, and often by using an ETL tool such as SSIS which can be scheduled to run automatically.

[kcgupta-blog]

The DW-DM-BI acronym and terminology guide

The BI field is full of acronyms - many are common to the industry itself, while others are specific to the Microsoft solution (incidentally, Microsoft used to be famous for their TLAs - Three Letter Acronyms - but lately we are seeing some increased usage of four letter acronyms).

TerminologyDescriptionAMOAnalysis Management ObjectsBIBusiness IntelligenceBIDSBusiness Intelligence Development StudioCubeA logical object that stores dimensions and facts and provides a multidimensional view of the data (often used interchangeably with UDM)DMData Mining; also used for Data MartDMXData Mining Extensions (language)DTSData Transformation Services (generally refers to legacy SQL Server 2000 packages)DWData Warehousing, or a Data WarehouseETLExtract, Transform, LoadHOLAPHybrid OLAPKPIKey Performance IndicatorMDXMultidimensional Expressions (language)MOLAPMultidimensional OLAPOLAPOnline Analytical Processing (also see HOLAP, MOLAP, ROLAP)OLTPOnline Transaction ProcessingROLAPRelational OLAPSSASSQL Server Analysis ServicesSSISSQL Server Integration ServicesSSRSSQL Server Reporting ServicesUDMUnified Dimensional Model (often used interchangeably with cube)XMLAXML for Analysis

[kcgupta-blog]

Use a Data Warehouse for Reporting

Businesses of all types and sizes use database systems, called Online Transaction Processing (OLTP) systems, to store and manage their transactions. These OLTP systems, when well designed, store data in relational tables in a highly normalized format, that is, tables are linked to each other so as to:

Avoid duplication/redundancy of data

Minimize space requirements

Enable fast retrieval and updates of these transactions

OLTP systems are very efficient at storing millions of transactions. However, when building reports that need to aggregate this data (for example, calculating sum, average, percentage etc), things start to slow down. The problem becomes more pronounced as we start to filter on various criteria, such as "Total $ amount of sales for last year", or "Total $ amount of sales of Mountain Bikes in California during Q3 of last year". This is because aggregation is being done on the fly for each query, and combined with the additional filters (WHERE clauses), quickly starts eating up our SQL Server resources. Not only are the reports slow, but other business users experience significant system slowdowns as well. A Data Warehouse (DW) on the other hand, which is part of an Online Analytical Processing (OLAP) system, is built for one purpose alone - fast, efficient reporting. In a DW system, data from the OLTP system will be read, de-normalized, and (typically) stored in a Star Schema, as a set of Dimension and Fact tables. This is often done using SQL Server Integration Services (SSIS). From these tables, tools within SQL Server Analysis Services (SSAS) can be used to build cubes, which contain not only our data but also its pre-calculated aggregates based upon definitions that we specify within SSAS. This results in many advantages:

Super-fast reporting

No impact on the OLTP system

Data Mining (DM) and Business Intelligence (BI) solutions can be readily built

The DW is not synchronized in real-time with the OLTP system. It is loaded at regular intervals, which may be as varied as once a month or several times a day, depending on the business requirements. SSIS and SSAS enable us to create packages that can be scheduled to run automatically (say, nightly), simplifying the time-consuming activities of loading of the Data Warehouse and building the Cubes.