Boost Performance for Large Data Warehouse

When we deal with large data warehouse, it needs specific trick for best performance. I did presentation about this on last Oct 17 for Mini TechReady in Microsoft Singapore. Basically here are some guidances:

  • Take advantage from table partitioning for large fact table.
  • Put clustered index on fact table key, specially for datetime column and do partitioning based on this column.
  • Put non clustered index on non datetime column of fact table, when the query usually using exact criteria.
  • Do the query based on interval criteria, put BETWEEN on WHERE clause when dealing with datetime key, ofcourse after put clustered index on it.

There are another tips related to Analysis Services, hardware, and deal with Integration Services. Download my presentation here: SQL2005LargeDW
Due some users that encountered problem when download entracting the file, I have uploaded again. I have double tested and it works fine.

Choosing The Right Data Mining Algorithm

Imagine we have a business problem, and already have the historical data. We can analyze the data using several mining algorithm inside SQL Server 2005. This is what I’ve talked in my second session at Teched SEA 06 Kuala Lumpur. This was not so technical session, but explain any considerations to decide which mining model should be used for specific business problem. Then do the future prediction using the mining model.

Download sample code here
Download the powerpoint slide here

Download “Smart” ASP.NET Demo is Available

I just come back from Yogyakarta, giving a 2 hours chat on Data Mining at Atma Jaya University. Last weekend was very busy, I presented about SQL Server 2005 BI for Foxpro developer in MS Office on Friday (April 28), and early in the Saturday morning on April 29 I went to Yogya with the first flight schedule.

The presentation material and demo code is rather same for both events, but I added some Foxpro snipet code for Friday material. I talked more on practical implementation of OLAP and data mining for Atma Jaya student, and my fellow Zeddy gave more attention on academic approach of mining model. I made some coding enhanchments for Saturday chat, so It looks more fancy and real. The demo code was about “smart” ASP.NET application using Association Rule mining algorithm of SQL2005.

This demo is like Amazon’s features, which able to give some movies recommendation based on visitor’s shopping basket. It drives them to other movie titles that maybe they interested, and encourage them to make “impulse buying”.
I reused some codes that made by Raman Iyer and Jesper Lind in his article at ASPNETPro Magazine. Then adding some user interface improvements so the application looks more nature and fancy as online shopping mall. I also added a data layer with C# 2.0 generics to demonstrate data access best practices for Atma Jaya’s student.

Don’t forget that you need at least Visual Studio 2005 Professional edition to open and run the demo file.
Download PPT file and code here: