mca

PPT from CTU 2009: Reporting with Data Mining

Well, traditional reporting is quite common. It connects to relational database, perform calculation and agregation, then display it in a nice and neat report. What is data mining reporting anyway?

It is also a report, but connects to mining model rather than traditional RDBMS. Example: a model to forecast the customer behavior created in SSAS. It predicts whether a customer will favor buy or not buy toward our product. Then we have another dataset of our potensial customers. We want to predict their behavior, will they favor BUY over not buying?

We can use SQL Reporting Services, connect to decision trees mining model inside SSAS, and use potensial customers data as input. The result is predicted behaviour and probability whether Mr.Bob will BUY (90%) and Mr. John not buy (60%). Another cool tool is Excel Data Mining Add-In. It uses Excel 2007 as data mining client tool, very nice and intuitive for end user. There is a very nice overview how does it work, read it here.

I also presented this tool, together with SSRS data mining reporting at Singapore CTU on May 23, 2009. The crowd were very enthusiast about this guy. Nice tool very end user power user, event for IT Pro.
My PPT can be downloaded here:

Installing SQL 2005 (X64) on Windows 7 RC

I was forced to install SQL Server 2005 X64 in my system, because my development DB was X64. Since my machine runs Win 7 X64, I was tempted to install on top of it. Anyway, I don’t have a spare machine, and VPC also does not support X64 OS :).

I was thinking that everything should be fine because I did it on my Vista X64 before. But apparently not, after tweak the setting here and there, I was ended up with the following error screen:

Capture

I was sure that all pre-requisites has been installed properly, but it was still failed. My first attempt was checking IIS 7 configuration, but everything was fine. The machine passed IIS requirement checking at the beginning of the steps.

I tried the last resort, just checking my luck. Since I saw that SQL Native Client was failed to install, I tried to download the latest SQLNCLI and voila! It was installed successfully after I updated the native client. Moral of the story: Windows 7 X64 RC does not come with the latest SQLNCLI, and the one bundled in SQL 2005 CD is not compatible with Mr. 7.

Capture3

To sum up, this is the steps to install SQL 05 X64 on Win 7 x64 RC:

Download and install the latest SQL Native client, it’s bundled under SQL 2005 Feature Pack family. I use the one from February 2007 and worked like a magic.

Capture2

Second, make sure all pre-requisites are installed such as IIS 7 components (ASP.NET, CGI, IIS6 compatibility, etc). This is the case if we want to configure Reporting Services as well.

Once installation finished, update to SQL Server 2005 SP2. Without update, it won’t work because the security policy kicked out Mr. Administrator from SQL sysadmin role :).

That’s all, happy testing …

Sharepoint – Reporting Services Integration

I did presentation for MaxIT event on May 29, 2009 and one of the interesting discussion was SharePoint integration with Reporting Services. I know that SharePoint is hot here in Singapore. I also did demo on some new features of SSRS 2008 such as tablix and charting. My presentation slide can be downloaded here:

There are several advantages of integrating SSRS with SharePoint:

  • Seamless UI for end user. They do not need to jump from one screen to another to open report and SharePoint dashboard at the same time.
  • All reports are deployed as document library, so it follows all SharePoint library setting. It saves lots of time instead of managing report in separate report manager.
  • All reports are under SharePoint security setting, giving better consolidated administrative from SharePoint perspective
  • Render reports inside web part. It makes building SharePoint dashboard faster and easier.

What do we need to configure this:

  • SharePoint Services 3.0 or MOSS 2007, It’s better if upgraded to SP1 or SP2.
  • SQL Server 2005 Reporting Services with SP2, or Reporting Services 2008.
  • Reporting Services add-in for SharePoint. There are 2 versions of add-in: SSRS 2005 and SSRS 2008.

Configuration can be simple (all in one box) or scale up to separate boxes. Below is the logical architecture if SSRS and SharePoint are installed on separate boxes:

ssrs-sharepoint

Assuming that SharePoint already installed, below are the explanations to do integration.

Install SSRS 2005 + SP2 in the SharePoint box, or separate machine. Just make sure to select “Install Report Server but do not configure” when doing this. The default configuration is native mode instead of SharePoint integrated, so please take note. Once SSRS installed, upgrade to SP2 (minimum) or directly to SP3.

In the case of SSRS 2008, SharePoint integration feature is already built in. Upgrading to SQL 2008 SP1 is recommended but not compulsory. Another improvement of SSRS 2008 is guided installation wizard. Once the installation CD is plugged in, there will be a menu to install SSRS in SharePoint mode instead of native stand alone.

Configure SSRS to integrate with SharePoint. Open SSRS configuration manager and connect to SSRS instance then create a new virtual directory for ReportServer. This should be the address of existing IIS website where the report server to be configured.

reportserversharepoint1

Configuring Windows Service and Web Service identity is straight forward. Use Network Service as identity account if it is a single box installation. In the case of scale up scenario, it needs to provide a dedicated SSRS service account from AD and use it as SSRS identity.

reportserversharepoint2

The next step is to create a new database for ReportServerDB and make sure to specify database as SharePoint integrated mode. It has to connect to existing SQL Server database instance.

reportserversharepoint3

It is not necessary to configure IIS website for Report Manager because SharePoint is our report manager now. Email and execution account configuration can be ignored in the meantime, specially if we do not use automated report delivery by email.

Another part of configuration is inside SharePoint central admin. Install SharePointRS.msi (SharePointRS_X64.msi for 64 bit) in SharePoint box. This package needs to be installed at every front end web server if SharePoint is configured as web farm. By the way, this file is Reporting Services add-in for SharePoint I mentioned earlier. Once installation finished, do IISRESET and open SharePoint Central Admin. A new Reporting Services configuration menu is available.

cassrs0

SharePoint has to know the address of Report Server which is under IIS virtual directory. Security configuration should be in windows integrated mode. This can be done under “Manage integration setting” link.

cassrs1

SharePoint also has to connect to ReportServerDB that already created earlier. Specify the name of SQL Server instance and click OK and specify windows account to connect to SQL Server.

cassrs2

There is another note for multiple boxes scenario: Reporting Services need to have local access to SharePoint object model in order to communicate with SharePoint. It means SharePoint binary has to be installed in SSRS machine, but left it empty without web application. It just needs to join existing SharePoint farm during installation, it is not necessary to create a site collection.

That’s all configuration needed. Now time to test it. Create a new library in SharePoint with the type of Report Library, for example “Sales Report”. Open existing SSRS source code (with Visual Studio of course) and deploy them to SharePoint. The tricky part is now the deployment location has changed. Remember that SharePoint is our report manager now. Below is the deployment configuration in Visual Studio:

vsdeployrs2sharepoint

What is the difference? Now it connects to SharePoint site as report server instead of http://myserver/reportserver. Data source and report folder location should point to address of “Sales Report” which is created earlier. The Sales Report library should display all reports after the deployment completed successfully.

reportdeployed

Clicking “Product Line Sales” bring up SSRS report under SharePoint, nice :).

ssrsinsharepoint

I don’t like full screen report, can we render them as web part?

Yes, report can be rendered as web part, and with all SharePoint flexibility, just drag and drop to any zones to display them. This web part is actually part of SharePointRS.msi installed previously. The name is Reporting Services viewer web part.

addssrswebpart

One page may render multiple web part and display them in a nice dashboard as follow:

reportwebpart

Managing the report is easy. Everything can be done under SharePoint. Click one of the report and java script popup tells us what all related reporting services setting can be done.

sharepointreportadmin

Nice feature eh? Now we have another more reason to implement BI under SharePoint :).

One more thing, Microsoft does provide a comprehensive guide to achieve this integration here.

New in SQL Server 2008: Backup Compression

Backing up database is a routine DBA job. This easy task become a headache when the machine runs out of disk space. We need to cleanup the disk or attach a new one to do the backup. Fortunately, SQL Server 2008 comes with built in compression feature of backup set. We can do backup and compress at the same time. What does it mean?

  • Save disk space, the compressed size is only around 10% of normal backup.
  • Faster backup time.
  • Faster restore time (surprise)!.

This is very convenience approach instead of doing normal backup and compress it with Winzip. According to benchmark I did, Winzip compression works slower than backup with compress on the fly. Not mention that we also have to unzip the file to restore from backup.

I am also surprise because restoring compressed backup is faster than non compressed. Logically, it needs to decompress and restore so it should be slower than restoring normal one. But my benchmark showed the opposite. I modified the AdventureWorks2008 sample database and insert additional data to make it bigger.

Below is my benchmark results with 900 MB size.

  • Backup with no compress: 115 seconds
  • Backup with compress: 49 seconds
  • Restore from non compressed backup : 123 seconds
  • Restore from compressed backup : 113 seconds

So, are you convinced? Here are the scripts to do compressed backup.

Specify with compression when doing a backup:

BACKUP DATABASE AdventureWorks2008
TO DISK = 'C:backupdbadvworksbackupcompress.bak'
WITH COMPRESSION

 

The result is a compressed backup file, it’s smaller size then non compressed:

backup2

It’s reduced from 900+ MB to only 118 MB, not bad right?

Restoring from compressed backup is straight forward. No additional option required. Just normal restore syntax from the name of backup set.

RESTORE DATABASE AdventureWorks2008
FROM DISK = 'C:backupdbadvworksbackupcompress.bak'
WITH REPLACE

I use with REPLACE option to override existing AdventureWorks2008 database.

Running SharePoint on Windows 7

I know that running SharePoint on Vista or Windows 7 is not for production, but as a developer I need a handy tool for coding and testing instead of VPC or VMWare. Well, installing SharePoint on Windows 7 is not officially supported, but if you are a geek enthusiast, here are the steps:

First, prepare IIS 7 for ASP.NET component. I configured the following components in my Win 7: 

prepareIIS71 prepareIIS7 

 

Download SharePoint with SP1, in my case I use WSS 3.0 SP1 for testing purpose. Get it from here: http://www.microsoft.com/downloads/details.aspx?FamilyID=4191a531-a2e9-45e4-b71e-5b0b17108bd2&displaylang=en

Install SQL Server, I use SQL 2008 Express for testing purpose. Download from here:

http://www.microsoft.com/express/sql/default.aspx

The next step is the most important thing. Thanks to Bamboo solution who created a small install launcher to make WSS work on Vista. Get WssOnVista helper and install it on Win 7 box: http://community.bamboosolutions.com/media/p/193.aspx

Run the WssVista helper and point to WSS 3 SP1 binary, then follow standard SharePoint installation procedure. One important thing is to choose Advanced mode instead of Basic, and proceed with Web front end configuration. This is because the wizard cannot detect SQL 2008 Express automatically in my machine. Well, I never try it with SQL05 Express which is usually managed to detect SQL Express instance under Basic instalation mode.

wssvista1

wssvista2

wssvista7

 

After several clicks and Next, here is my WSS on Windows 7, peace :)

wssvista8

Shrink and manage huge SQL log file

Updated on January 10, 2011:  This post is outdated

You should not shrink database file too often because It creates more fragmentation. Quote from MSDN documentation:

Best Practices

Consider the following information when you plan to shrink a database or file:

  • A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
  • Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
  • A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.
  • Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.

http://msdn.microsoft.com/en-us/library/ms189080(v=SQL.105).aspx

========================================================================================================

I had a bad experience with transaction log file, it was 8 years ago. But thanks to Mr.Bad, I learnt very important lesson as newbie DBA at that time. This is the story: my ERP application which sitting on top of SQL Server 2000 was fine at Friday afternoon. I went home for a weekend, only to find everybody was starred at me at Monday morning. What had happened? The ERP was crashed, actually it couldn’t start at all.

I went to my SQL Server box, and the guy was stopped. It’s not started. I checked everything from security permission to possibility of virus, nil. Then I found that the disk was out of space because of transaction log explosion. The accounting guy was doing quarterly stock journal in the system, and it caused transaction log growth exponentially. There was an audit table which recording all journal operation, and obviously the INSERT operation was the source of problem.

Moral of the story: make sure you have automated way to manage transaction log growth.

There are 2 way to shrink the log:

  • Detach database, delete the big log, and re-attach without log file. SQL Server will create a new, fresh log file with minimum initial size. It is 2MB by default. Disadvantage: the database will not be available during the process. Offline duration depends on how fast is the detach – attach operation takes time.
  • Better approach: use SHRINKFILE of DBCC command. This is better because I do not need to bring the system OFF.

Regardless of the approach, make sure to do full database backup. Shrinking the log deletes historical transaction that may be needed to restore database to specific point of time. Just in case, make sure to do full backup. You have been warned right :).

These are the steps to shrink a huge log file:

First, obtain the log filename, simply use sp_helpdb as follow:

sp_helpdb 'AdventureWorks'

It returns all files in AdventureWorks:

log

Then shrink AdventureWorks log file to 2 MB size:

USE AdventureWorks
GO
DBCC SHRINKFILE (AdventureWorks_Log, 2)
GO

The outcome is 20 GB log file has been shrunken to 2 MB:

 

shrinked

I usually run DBCC SHRINKFILE on scheduled basis, by attaching SQL script to SQL Agent job. This is easier and painless :).

Slide Deck and Demo on SharePoint + Silverlight

Technorati Tags: ,

This is a quick post for my presentation slide and demos at SharePoint Pro Singapore User Group Meeting last week. I discussed about configuration on SharePoint box to make it Silverlight enabled, and also giving some demos to write Silverlight video player then deploy it to SharePoint.

image

The PPT and demos are available for download from my Skydrive. Enjoy!

Presentation Slide at Singapore CTU 2008

I presented at Community Technology Update 2008, organized by Microsoft Singapore DPE on November 22, 2008. It was a fantastic community event, a place when community members gathered together to share their passion on latest Microsoft Technologies.

Heroes_Banner

My first session at the morning was Web 2.0 Programming with Visual Studio 2008. I presented Web 2.0 term, and what kind of tools available in Visual Studio 2008 to achieve Web 2.0 effects. I was much inspired by DropThings project  by fellow ASP.NET MVP Omar Al Zabir.

The second session was about SQL Server Performance Tools. No, this is not yet another Microsoft product come out. It is a collection of free tools, scripts, and tips to identify server bottleneck and resolve performance problems. My idea was to share hidden tools and script to reveal performance bottleneck. I was presenting about PAL, SQLDiag, server side script and database tuning advisor. I have to give a big credit to Itzik Ben Gan book for collection of server side trace script instead of using graphical Profiler. Both PPTs are available for download from my Skydrive:

Web 2.0 with VS 2008

SQL Server Performance Tools

I am not able to publish my demo code at the moment, because it was running on my real machine at that time, so I need to fine tune and modify some configuration to avoid any problem. Once I finished with the changes, I will update share my demo code here.

Easy Server Monitoring with PAL

The first step in SQL Server performance tuning usually starts with finding bottleneck at the server side. It includes monitoring to some suspicious symptoms on CPU, Memory, Disk, and various SQL Server counter. Yes, Performance Monitor tool is already available on Windows machine. It is a very valuable tool for detecting the server bottleneck, and it is free. Just type perfmon and we are ready to start to do monitoring.

Usually I create counter log and let the monitoring runs at the background based on specific schedule and sampling interval. With this way, I can minimize the performance impact by running Perfmon inside the box. Once I got the log data, then I am ready to start the analysis. There are thousands or even millions rows of logs inside. How do I analyze and finding the system behavior, detect the pattern and plan a possible actions? Obviously I can import the CSV log file to SQL table or Excel and then use my T-SQL knowledge to summarize the data. But actually there is easier way to do it, Clint Huffman of Microsoft already created PAL.

The PAL does query to the counter log data using Log Parser, and display it in a nice HTML format. It is a graphical chart data analysis, so that’s why PAL requires Office Web Component installed to view the report. We can download PAL from CodePlex, and don’t forget to install Log Parser and OWC11 as pre-requisites. So the steps to do analysis with PAL are:

  • Collect counter log data with Perfmon
  • Use counter log as input to PAL
  • Analyze the report with PAL

The first screen of PAL will ask a location of counter log file that was created by Perfmon.

image

At the next screen, there are several options of system monitoring template. It depends of what kind of servers installed in the system.

image

The good things is PAL already comes with predefined template and threshold, so we do not need to think about the threshold for various counter. We con modify this template as well to suit our needs.

Here are several reports generated by PAL, very nice analysis with graphical trends.

image

image

Free E-Learning with MSDN Ramp Up

Microsoft released the Ramp Up initiative, the place where we can learn several emerging technologies such as VS 2008, SharePoint, and Silverlight. This is a free service, all we need is a Live account to start. It has collection of article, Webcast, demo code, and virtual lab running on Virtual Server.

I signed up to SharePoint 2007 developer track and tried several PPT and virtual lab. The lab speed was acceptable, of course we cannot expect the same speed as the real machine. But given a condition with my 1 MBps connection, It was running fast.

rampupvlab

All lab pre-requisites, VS 2008, SharePoint, code snippet, including a downloadable lab manual is available. I should admit this is an amazing job, well done ramp-up team!

rampupvlab2 

I completed SilverLight lab in 1 hour. Actually I just want to try it out, because I already know the stuff. The lab started from level 200, and then progressing to the higher level at the end. An experienced developer can skip lab 01 or 03, and jump to the more challenging code.