Monday, August 31, 2009

Report Builder 3.0

In the latest release of SQL Server 2008 R2, Microsoft has released an updated version of Report Builder called Report builder 3.0. One of the interesting aspects of this tool is the ability to create map based reports. These reports are built based on spatial data available through SQL Server Database or through the ESRI Shape file. The ESRI shape file can be found on Census sites or through the Dundas Maps site.






Developing MAP based reports and dashboards have been bought to the developers tool set, this should give them the ability to provide more visual reports to the business. This also greatly enhances the value and presentation of the reports. Here is a link to report builder 3.0 tutorial.
http://msdn.microsoft.com/en-us/library/ee240845(SQL.105).aspx

Friday, August 21, 2009

SQL Server Profiler-SSAS

Usually the sql profiler tool is used for analyzing queries written against sql server databases, typically to find performance bottlenecks. This tool is used to trap sql sent by SSRS/Applications communicating with a sql server database. One of things I wanted to try after having heard about it was to use the SQL Server profiler for Analysis Services cubes (SSAS). I connected to the analysis server and created a new profile, choose the standard option. Then I checked show all events, in the events selection tab there is a category called query processing which probably houses all the important events that happen when a cube is processed. There are events which show the MDX script being executed, Getting data from Aggregation,Cache , whether a subcube is being queried, these based on the results i saw are probably important events which can be analysed.






When looking at the results generated from these events, it gives a better idea of what happens beneath the cube in terms of which dimensions are being queried. Based on the findings this aspect of sql profiler adds an another useful aspect to the already existing important features.

Tuesday, August 18, 2009

SSIS-Package Property

I had a situation where in data had to be transformed on several tables (more than 15 tables). For the data transformation based on the business rules stored procedures were used, then i ran into the next challenge. I did not want to run the 15 stored procedures in a sequential manner within SSIS package. At this point these processes were going to run in parallel in the SSIS package. One of the issues which i was going to run into was there could be more tables added to the transformation and I would need more stored procedures to be run in parallel. Here in I stepped into the property set at the package level called MaxConCurrentExecutables. This is present at the package level as mentioned before can be see here in the image below. The property is highlighted in agreen color text.


The number was changed to 20 on the property and the package execution time was better than prior to changing the value for this property. There is a definition of the MaxConcurrentExecutables at MSDN.

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.maxconcurrentexecutables.aspx

Monday, August 10, 2009

Generating Reports Through WebService...

Usually once the reports are developed, they are deployed to the report server and the end users can view the report from the reporting portal. I ran into a situation where i had to perform data driven subscription kind of reports. Here the requirement at a set time of the day, the reports had to be generated in a .pdf format and stored in a network folder. In order to accomplish this, the reportexecution2005.asmx web service was used. The reference material at the MSDn site helped me guide to accomplish this effort. The .asmx file mentioned above is present in the report server. I added this file as a web reference to my project, once this was done there are methods which need to be used to generate the report. The methods are LoadReport,Render,GetExecutionInfo. The render method performs the actual rendering of the report. The output is then put out to the pdf file at the required location using the FileStream object. This whole thing was done in C# console application.

Sample Code:

ReportExecutionService rs = new ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.Url = "http://myserver/reportserver/reportexecution2005.asmx";

The link to the msdn article:
http://msdn.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx