ChoirulAmri.org

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:

• Create a chart of your choice. Lines chart is a popular form. Bind it to the data of interest such as sales amount, total cost, etc.
• Squeeze the chart as small as possible but still maintain its readability. Remove all unnecessary  details like X and Y axis, legend, series label, etc.
• Drag and drop the chart in to data area of your report and tidy up for better presentation.

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.

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:

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:

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:

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:

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.

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.

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.

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.

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.

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.

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:

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.

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

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.

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

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.

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.

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:

• What is data warehouse
• Data warehouse design
• How to load dimension and fact table
• Performance trade of
• Best practices in DW design and ETL
• Demos on SSIS

Grab my PPT and demo from my skydrive here:

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.

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

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.

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.

I spoke on SQL Server User Group Indonesia monthly meeting last month, discussed about a developer perspectives of SQL Server Security. I covered most of the session with some SQL Injection techniques, completed with real sample code in web application.

I started with the awareness that network administrator is not the only person who’s responsible for computer security. Networking guys are only responsible at the network (firewall) and host (OS) level. At the application level, is a developer responsiblity for the software security. Ensure that no single line of code contains a hole, something that can be utilized by an attacker to compromise the system.

These are the summarry for SQL Injection countermeasures:

• Never use user input as a string concatenation element
• Avoid string concatenation in SQL statement
• Use Parameter collection of ADO.NET Command object for parameter parsing
• Use stored procedure as possible
• Validate user input, never trust them
• Client side validation is not enough (java script can be removed)
• Utilize built in database constraint
• Change the sa password
• Remove built in administrator

Some configurations for locking down SQL Server from any vulnerabilities:

• Disable xp_cmdshell (default ON in SQL2000)
• Disable OPENROWSET and OPENDATASOURCE (default ON in SQL2000)
• Don’t enable SQL CLR if not needed
• Minimize protocol used
• Disable remote access if not needed
• All setting can be modified using sp_configure
• SQL authentication: create user mapping, don’t use the real SQL user
• Use Application Role if possible

Download the presentation here.
Download the sample code here.