Thursday, November 29, 2007

Exporting from C#


/// <summary>
/// Web Utility Function For Exporting Data Set to Specified Format
/// </summary>
/// <param name="dsResults">Result Data Set</param>
/// <param name="enExport">Export Enum Values</param>
/// <param name="strColDelim">Column Delimiter value</param>
/// <param name="strRowDelim"></param>
/// <param name="strFileName"></param>
public static void ExportDataSet(DataSet dsResults , ExportFormat enExport,string strColDelim, string strRowDelim, string strFileName)
{
DataGrid dgExport = new DataGrid();
dgExport.AllowPaging =false;
dgExport.DataSource =dsResults;
dsResults.DataSetName ="WebERP";
dgExport.DataMember = dsResults.Tables[0].TableName;
dgExport.DataBind();
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.Buffer= true;
System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
System.Web.HttpContext.Current.Response.Charset = "";
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" +strFileName );
switch(enExport.ToString().ToLower())
{
case "xls":
{
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString());
break;
}
case "custom":
{
string strText;
System.Web.HttpContext.Current.Response.ContentType = "text/txt";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
strText = oStringWriter.ToString();
strText = ParseToDelim(strText ,strRowDelim,strColDelim);
System.Web.HttpContext.Current.Response.Write(strText);
break;
}
case "csv":
{
string strText;
strRowDelim = System.Environment.NewLine;
strColDelim = ",";
System.Web.HttpContext.Current.Response.ContentType = "text/txt";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
strText = oStringWriter.ToString();
strText = ParseToDelim(strText ,strRowDelim,strColDelim);
System.Web.HttpContext.Current.Response.Write(strText);
break;
}
case "tsv":
{
string strText;
strRowDelim = System.Environment.NewLine;
strColDelim = "\t";
System.Web.HttpContext.Current.Response.ContentType = "text/txt";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
strText = oStringWriter.ToString();
strText = ParseToDelim(strText ,strRowDelim,strColDelim);
System.Web.HttpContext.Current.Response.Write(strText);
break;
}
case "xml":
{
System.Web.HttpContext.Current.Response.ContentType = "text/xml";
System.Web.HttpContext.Current.Response.Write(dsResults.GetXml());
break;
}
case "htm":
{
System.Web.HttpContext.Current.Response.ContentType = "text/html";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
dgExport.RenderControl(oHtmlTextWriter);
System.Web.HttpContext.Current.Response.Write(oStringWriter.ToString());
break;
}
}
}

}

#region "Export To a Delim Format"
public static string ParseToDelim(string strText, string strRowDelim , string strColDelim)
{
Regex objReg = new Regex(@"(>\s+<)",RegexOptions.IgnoreCase);
strText = objReg.Replace(strText,"><");
strText = strText.Replace(System.Environment.NewLine,"");
strText = strText.Replace("</td></tr><tr><td>",strRowDelim);
strText = strText.Replace("</td><td>",strColDelim);
objReg = new Regex(@"<[^>]*>",RegexOptions.IgnoreCase);
strText = objReg.Replace(strText,"");
strText = System.Web.HttpUtility.HtmlDecode(strText);
return strText;
}
#endregion

public enum ExportFormat
{
XML,
XLS,
HTML,
CSV,
CUSTOM,
TSV
}

Wednesday, November 21, 2007

Importing Text-based data: Workbench

 

/* Robyn and Phil return with some fresh ideas about how to import text files into SQL Server, without resorting to DTS or SSIS scripting. They go on to show how much can be done in TSQL */

Importing Text-based data: Workbench

Monday, November 12, 2007

htmlSQL - live example

 

htmlSQL is a experimental PHP class which allows you to access HTML values by an SQL like syntax.
This means that you don't have to write complex functions (regular expressions) to extract specific values.

htmlSQL - live example

Sunday, November 11, 2007

BOCOG modifies Phase 2 domestic ticketing policy - The Official Website of the Beijing 2008 Olympic Games

Sounds like Beijing Ticketmaster ran out of log space in their database...  damn holes in the optimization processes.  I hate those things...  Should have put in some failover systems or something. though that probably wouldn't have kept up with the onslaught in demand. 

Within the first hour, from 9:00am to 10:00am, the page view (PV) volume of the official ticketing website of the Beijing 2008 Olympic Games (http://tickets.beijing2008.com) soared to eight million, while the number of calls to the ticketing hotline (+8610 952008) exceeded 3.8 million.

That's a lot of hits.

A ticketing official later said that after Phase 2 of the ticket sales began, an excessive volume of page views to the official ticketing website affected the database performance, causing in a "hole" in the optimization of the database processes and slowing the online search speed. In addition, new ticket buyers were unable to register with the system as a result.

After the second phase of the domestic ticket sales was launched on October 30, the public's purchase of tickets surpassed the system processing capability by more than eight times, leading to a "paralysis" of the official Olympic ticketing website. As a result, BOCOG decided to temporarily suspend the Phase 2 domestic ticket sales and issued an explanation for the situation on October 31.

On November 5, BOCOG released a new ticketing policy based on a comprehensive analysis of the situation and system capability.

The new ticketing policy for the Phase 2 domestic ticket sales institutes a unified ticket lottery system, whereby the order of ticket application submission will not matter. The ticket purchase limit will be adjusted accordingly, and the public may submit ticket applications from December 10 through December 30.

According to reports, the BOCOG Ticketing Center immediately began to work with the BOCOG Technology and Security departments and Beijing Gehua Ticketmaster Ticketing Co., Ltd to adopt measures to solve the technical problems as soon as they arose.

Source: BOCOG modifies Phase 2 domestic ticketing policy - The Official Website of the Beijing 2008 Olympic Games

More here. http://blogs.techrepublic.com.com/tech-news/index.php?cat=931

PC World - No Price Hike for SQL Server

Looks like the SQL 2008 price is staying the same as 2005.  Interesting to note that many customers have not even upgraded to SQL 2005 yet.  Launching 2008 should speed adoption of 2005. Customers always seem to want to stay 1-2 versions behind because of the appearance of stability & being a "proven" architecture.  In my opinion there is no reason not to ditch SQL 2000 for SQL 2005, if you have any need whatsoever for better reporting and performance capabililties.

Source: PC World - No Price Hike for SQL Server

Using 64-bit architecture and the increased memory capacities of SQL 2005 Enterprise should net some serious performance improvements for those who upgrade. Not to mention the management views that will make analyzing and boosting performance in systems much easier.

Hopefully they launch the first service pack a month after, so that the early-adopter crowd will go all in on an upgrade.

One feature being hyped is "Pervasive Insight".  This appears to be the external auditing capabilities that wrap around the database.  There also appears to be some integration of the BIDS add-in into the base platform, to troubleshoot issues with dimension relationships and warnings in Analysis Services.

More info below.

http://blogs.msdn.com/ajaiman/archive/2007/06/05/sql-server-2008-june-ctp.aspx

Friday, November 09, 2007

OLAP/Data Mining

Huge list of resources at this site. 

OLAP/Data Mining

Online Analytical Processing (OLAP)


http://altaplana.com/olap/

What Is OLAP


http://www.cis.upenn.edu/~cse330/lecture14/sld002.htm

OLAP Council


http://www.olapcouncil.org/

The OLAP Report


http://www.olapreport.com/

OLAP Journal


http://www.olapjournal.org/

Data Warehousing and OLAP Research Bibliography


http://www.cs.toronto.edu/~mendel/dwbib.html

OLAP Information Site


http://www.olapinfo.de/

Visual OLAP Resources


http://www.visualolap.com/

OLE DB For OLAP


http://www.microsoft.com/data/oledb/olap/default.htm

Microsoft OLAP Services User Community


http://groups.msn.com/MicrosoftOLAPServicesUsersCommunity/_homepage.msnw?pgmarket=en-us

Microsoft OLAP


http://www.mosha.com/msolap/

Microsoft SQL Server OLAP Services


http://msdn.microsoft.com/vfoxpro/technical/olap.asp

Microsoft OLAP Unleashed (Book) 1999


http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?srefer=&isbn=0672316714

Microsoft OLAP Solutions Updates


http://www.dsslab.com/MSOLAPSolutions.html

FIX: Processing OLAP Partitions in Parallel May Result in Errors (Q253496)


http://support.microsoft.com/default.aspx?scid=kb;EN-US;q253496

SAS Business Intelligence & OLAP


http://www.sas.com/technologies/olap/

SAS Analytic Intelligence


http://www.sas.com/technologies/data_mining/

OLAP and OLAP Server Definitions


http://www.moulton.com/olap/olap.glossary.html

The OLAP Survey


http://www.survey.com/reports/olap/

Active OLAP For Essbase


http://www.appliedolap.com/

EASY OLAP


http://www.peyo-home.sk/index.html?easyolap/index.htm~main

OLAP Technical Work Forum


http://www.tek-tips.com/gthreadminder.cfm/lev2/77/lev3/25/pid/450

German OLAP and Data Warehouse Forum


http://olap.winf.ruhr-uni-bochum.de/

OLAP Forum


http://www.ontargettek.com/wwwboard/wwwboard.html

OLAP Train


http://olaptrain.com/

OLAP Links


http://www.webopedia.com/TERM/O/OLAP.html

OLAP Resources


http://www.sgroves.demon.co.uk/olaplnks.htm

Open Directory: OLAP


http://dmoz.org/Computers/Software/Databases/OLAP/

Google Web Directory: OLAP


http://directory.google.com/Top/Computers/Software/Databases/OLAP/

Databases, Data Warehousing and OLAP


http://www.kdnuggets.com/websites/dbolap.html

IBM Welds OLAP, Data Mining


http://www.idg.net/ic_855480_8434_1-3921.html

IBM DB2 OLAP Server


http://www-3.ibm.com/software/data/db2/db2olap/

IBM Adds Hybrid OLAP In DB2


http://www.eweek.com/article2/0,3959,120907,00.asp

Oracle OLAP


http://otn.oracle.com/products/oracle9i/datasheets/olap.pdf

Oracle9i OLAP


http://www.oracle.com/ip/deploy/database/oracle9i/index.html?bi_olap.html

Oracle Express OLAP


http://www.charlesriver.com/titles/oracle.html

OLAP Information


http://www.andypryke.com/university/dm_docs/olap/

A New Face For OLAP


http://www.dbmsmag.com/9701i08.html

White Papers On Data Warehousing, Data Mining, OLAP


http://www.dkms.com/white_papers.htm

JSR 69 Java OLAP Interface (JOLAP)


http://www.jcp.org/jsr/detail/69.jsp

OLAP/Multidimensional Database Tools


http://www.dwinfocenter.org/olap.html

OLAP Performance


http://www.sqlmag.com/Articles/Index.cfm?ArticleID=7638

The Transition of OLAP


http://www.101com.com/solutions/dataintelligence/article.asp?articleid=4650

Intelligent Rollups In Multidimensional OLAP Data


http://www.it.iitb.ernet.in/~sunita/papers/vldb01.pdf

Maintenance and Control Aspects of OLAP Applications


http://www.xs4all.nl/~fab/olapkeep.html

OLAP and Statistical Databases: Similarities and Differences


http://citeseer.nj.nec.com/shoshani97olap.html

Mining/OLAP


http://www.cio.com/research/data/data_mining.html

Discovering New Opportunities With OLAP


http://webevents.broadcast.com/ibm/olap/home.asp

OLAP Tools


http://www.meridian-marketing.com/TECH/olap.html

OLAP Market Review


http://www.dmreview.com/master.cfm?NavID=198&EdID=4085

Evaluating OLAP Servers and Tools


http://www.corpinfodesigns.com/priv_docs/EVAL_OLAP.pdf

OLAP For Java Developers


http://www.devx.com/upload/free/features/javapro/1999/07jul99/kn0799/kn0799.asp

Mining For Golden Nuggets of Data


http://www.toptentechs.com/applications/Issue6/

A Survey On Logical Models For OLAP Databases


http://www.dbnet.ece.ntua.gr/~dwq/p31.pdf

Specifying OLAP Cubes On XML Data


http://www.cs.auc.dk/~tbp/articles/R015003.pdf

What Is Data Mining ?


http://www.anderson.ucla.edu/faculty/jason.frand/teacher/technologies/palace/datamining.htm

Data Mining Overview


http://www.megaputer.com/dm/index.php3

Defining Data Mining


http://www.dbmsmag.com/9608d53.html

Data Mining and Knowledge Discovery Journal


http://www.digimine.com/usama/datamine/

Information About Data Mining


http://www.the-data-mine.com/

IBM Data Mining Group Quest


http://www.almaden.ibm.com/cs/quest/

IBM DB2 Intelligent Miner For Data


http://www-3.ibm.com/software/data/iminer/fordata/

Data Mining, Web Mining and Knowledge Discovery Guide


http://www.kdnuggets.com/

National Center For Data Mining (NCDM)


http://www.ncdm.uic.edu/

Data Mining News


http://www.idagroup.com/

Data Mining Books, White Papers, and Tutorial


http://www3.shore.net/~kht/

Data Miners


http://www.data-miners.com/

Data Mining and Machine Learning


http://www.cs.helsinki.fi/research/fdk/datamining/

Effectively Guide Your Organization's Future With Data Mining


http://www.spss.com/spssbi/applications/datamining/

Data Mining Benchmarking Association


http://www.dmbenchmarking.com/

URLs For Data Mining


http://www.galaxy.gmu.edu/stats/syllabi/DMLIST.html

Data Mining In Molecular Biology


http://industry.ebi.ac.uk/~brazma/dm.html

Data Mining Glossary


http://www.twocrows.com/glossary.htm

Data Mining Technologies


http://www.data-mine.com/

DMSK: Data Miner Software Kit


http://www.data-miner.com/

ACM Special Interest Group On Knowledge Discovery in Data and Data Mining (SIGKDD)


http://www.acm.org/sigkdd/


http://www.kdd.org/

The Data Mining Institute (DMI)


http://www.cs.wisc.edu/dmi/

Data Mining and Knowledge Discovery in Databases


http://db.cs.sfu.ca/sections/publication/kdd/kdd.html

Untangling Text Data Mining


http://www.sims.berkeley.edu/~hearst/papers/acl99/acl99-tdm.html

Data Mining Forum


http://www.data-mining-forum.de/

Data Warehouse, Data Mining and Decision Support Resources


http://www.infogoal.com/dmc/dmcdwh.htm

Data Mining Lecture Notes


http://www-db.stanford.edu/~ullman/mining/mining.html

Data Mining: Concepts and Techniques (Book) 2000


http://www.cs.sfu.ca/~han/DM_Book.html

Analysis of Data Mining Algorithms


http://userpages.umbc.edu/~kjoshi1/data-mine/proj_rpt.htm

Tutorial On High Performance Data Mining


http://www-users.cs.umn.edu/~mjoshi/hpdmtut/

ITSC Data Mining Center


http://datamining.itsc.uah.edu/

Data Mining Techniques


http://www.statsoftinc.com/textbook/stdatmin.html

Advances In Knowledge Discovery and Data Mining (Book)


http://www.aaai.org/Press/Books/Fayyad/fayyad.html

Data Mining Tools


http://www.dwinfocenter.org/datamine.html


http://www.rulequest.com/

Data Mining: Practical Machine Learning Tools and Techniques with Java Implementations (Book) 1999


http://www.cs.waikato.ac.nz/~ml/weka/book.html

Data Mining With Predictive Models


http://www.spss.com/spssbi/clementine/

Open Directory: Data Mining


http://dmoz.org/Computers/Software/Databases/Data_Mining/

Data Mining Resources For Space Science


http://adc.gsfc.nasa.gov/adc/adc_datamining.html

Data Mining Group


http://cslab.anu.edu.au/ml/dm/

NAG Data Mining Components To Create Critical Competitive Advantage


http://www.nag.co.uk/numeric/DR/drdescription.asp

Data Mining Approaches for Intrusion Detection


http://www.cs.columbia.edu/~wenke/papers/usenix/usenix.html

Data Mining in Python


http://ai.fri.uni-lj.si/~aleks/orng/

Data Mining Product Features


http://www.exclusiveore.com/prodtable.html

Knowledge Discovery/Data Mining Center


http://open.cineca.it/datamining/index_ing.htm

UCLA Data Mining Lab


http://dml.cs.ucla.edu/

Data Mining Solutions and Establishment of A Data Warehouse


http://www.firstmonday.dk/issues/issue2_5/maxwell/

Data Mining - Case Study


http://www.math.mcmaster.ca/peter/sora/case_studies_00/data_mining.html

Workshop On Data Mining Lessons Learned


http://www.hpl.hp.com/personal/Tom_Fawcett/DMLL-workshop.html

Data Mining and Customer Relationships


http://www3.primushost.com/~kht/text/whexcerpt/whexcerpt.htm

OMG Common Warehouse Metamodel (CWM)

Data Warehousing, CWM and MOF Resources


http://www.omg.org/cwm/

OLAP/Data Mining