born to train, forced to code::SQL Server, SharePoint, .Net, Business Intelligence
It was a busy week preparing Windows 7 Launch on October 22, 2009, and I managed to squeeze my time to do Windows 7 presentation for MaxIT event. I will post more detail about Windows 7 next time. In the mean time, you can download the PPT from the event below:
Thanks to Andrew for the base material.
posted on: October 19th, 2009 |
Add comment
I posted KPI report in previous entry, something that really cool in SSRS 2008. There is another method to display a trend without much attention on number and achievements. It’s called sparklines. There are many occasions when decision maker only need to know the trend regardless of actual values. A simple visualizations can help them to grab the fact quickly.
Below is an examples of sparklines:
How to create a sparklines report? It is actually simple. In general, there are 3 steps:
The good thing about sparklines is its dynamic behaviour. Since it is located inside data area, it follows whatever grouping setup in rows and column. The sparklines adopts to drill down report setup along with its grouping, like the following example:
 |
 |
The most important thing of creating sparklines are data binding and removing unnecessary chart properties. In the following picture, the chart is bounded to Sum(SalesAmount). I removed X and Y axis and also its title. This is a straight forward task by right clicking every property and select disable or delete.
The last step is to add additional column in report table for sparklines container. I use “Total Trend” as presented in the first picture, then drag and drop the chart in to column and tidy up for better visualization. What about source code? Download sample .rdl file from here:
As usual, it needs AdventureWorksDW2008 sample database which freely available from codeplex.
posted on: August 23rd, 2009 |
Add comment
I did the second presentation for MaxIT event on July 17, 2009. There are 2 topics presented: Reporting Services 2008 for morning session and Upgrading to SQL Server 2008 in the afternoon. I found several new materials so I decided to update the slide deck and additional demos. If you already downloaded the previous PPT, this is the latest update with many enhanced information specially for afternoon session.
Reporting Services 2008 PPT:
Upgrading to SQL Server 2008 PPT:
Demo source code for KPI and Reporting Services:
posted on: July 19th, 2009 |
Add comment
Reporting Services 2008 brought many enhancements for data visualization. The queen is Gauge, which is ideal tool to create scorecard and KPI. Imagine the report popping up in front of manager with colourful and fancy indicators like this:
Gauge is only one among many other things. Charting is more fun than ever, with many 3D effects and customizable areas. I also like new feature such as calculated series that deliver moving average report in minutes. No more complex expression to calculate median or moving average data.
Gauge also comes with many type such as radial type with speedometer style. I can display sales indicator and turn the pointer to red, yellow, or green like this:
I did demo and discussion about this topic in Singapore SQL UG meeting on June 25, 2009. As I promised to upload my slide deck, you can grab it here:
Of course I release the source code too for deeper exploration. Apologize that I don’t have enough passion to tidy up the reports for best appearance. It is available in my skydrive:
All reports need AdventureWorks2008 and AdventureWorks2008DW sample, available to download from CodePlex.
Below are some pictures from the meeting:
posted on: June 26th, 2009 |
2 comments
I blogged about backup compression here, and I mentioned that the main advantages are saving space, reduced backup and restore time. After discussion with some fellow MVPs, I have to revise my statement in previous post.
Yes, backup compression saves backup and restore time only if there are enough CPU and IO resources available. This is obvious because IO is the biggest bottleneck when reading/writing backup file. Thanks to Aaron and Andrew for remind me about this fact.
However, IO bottleneck is solvable if we use fast removable storage (eSATA for example) instead of internal hard disk. With this way, we can take full advantage of compression without affecting current machine performance.
The SQL Customer Advisory team did a very comprehensive benchmark on this. Below is some screen shoot I cut and paste from their blog post:
The complete post for benchmark by SQLCAT is here
posted on: June 11th, 2009 |
Add comment
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:
posted on: June 2nd, 2009 |
Add comment
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:
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.
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.
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 …
posted on: June 1st, 2009 |
1 comment
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:
What do we need to configure this:
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:
Assuming that SharePoint already installed, below are the explanations to do integration.
Continue Reading: Sharepoint – Reporting Services Integration
posted on: May 30th, 2009 |
3 comments
This is a quick post of my presentation slide and demo codes for Singapore SQL Server User Group last week. I presented the following topics:
Grab my PPT and demo from my skydrive here:
posted on: March 1st, 2009 |
Add comment
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?
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.
So, are you convinced? Here are the scripts to do compressed backup.
Continue Reading: New in SQL Server 2008: Backup Compression
posted on: February 6th, 2009 |
2 comments
