Working on a Data Warehouse project

I have been busy for the last month as we, at Code71, rolled out the first release of the Business Intelligence project for Vuneu Media. I will give you a brief on my project here so that you can follow my coming posts.

The used vehicle dealers buy their vehicles from different Auctions across the country. When making a buy decision, they try to find answer to these questions-

1. What vehicles are customers buying?
2. How much it costs to get those from Auctions?
3. How many of these are receiving cash advances and by which lenders?
4. What is my potential gross profit if I deal a vehicle?
5. Where can I find the vehicles in Auctions?

So, we are getting the information from data vendors. Since, the amount of data is really large (nearly 10 million rows per month in total) for human manipulation, we are adding real value to the business by providing software intelligence. We developed the technology to put the pieces together and make it handy for the dealers.

We are using the following software/technologies:-

1. SQL Server 2008, 64 Bit, Standard Edition.
2. SQL Server Integration Service.
3. SQL Server Reporting Service.
4. ASP.Net, C#, WCF.
5. SQL Server Analysis Service (is in the pipeline, not used yet!)
6. Windows Server 2008, 64 Bit.

I came across a number of innovations as well as issues at each step of the project. The database schema design was itself a challenge since data come from different vendors in various formats and granularities.

Here, I will start with an issue resulting from the “64 bit” SQL Server limitations-

SQL Server 64 bit versions include Integration Services, but it lacks support for some components. One such is, Microsoft Jet Driver. We use this driver to load data from Excel files.

To load Excel data using SSIS, you have the following options:-

1. Locate Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe. Then use this with your options. You can take a look at the command arguments by invoking DTExec –? This way you can run the SSIS packages using command line.

2. Using the SQL Server Agent Job is rather simple. Just Create/edit your job step having the Excel, then select Use 32 Bit in the Options/Execution Options tab. It will automatically use the 32 bit version of DTExec.

3. If you are using the IDE, you can turn of 64 bit by un-checking the Use 64 Bit Runtime at the Project Properties > Debugging.

More information on 64 bit SQL Server related issues are here at MSDN.

Also, I got help from Blog N-Technologies.