BI/BPM - The SeeQueL

Rob Farley : The SSIS tuning tip that everyone misses

2012-01-25T07:34:00.001-08:00

Great tips for optimizing SSIS source queries.

Luckily, T-SQL gives us a brilliant query hint to help avoid this.

OPTION (FAST 10000)

This hint means that it will choose a query which will optimise for the first 10,000 rows – the default SSIS buffer size. And the effect can be quite significant.

Rob Farley : The SSIS tuning tip that everyone misses

Debugging SSIS Script Component « Code Reference

2012-01-23T10:06:00.001-08:00

If you’re having issues with SSIS 2008 R2 logging in script tasks, it’s most likely because syntax has changed.

See this link on how to use ComponentMetadata.FireProgress instead of Dts.FireProgress.

Debugging SSIS Script Component « Code Reference

reportsync - sync SSRS report between two report servers - Google Project Hosting

2012-01-20T17:36:00.000-08:00

reportsync - sync SSRS report between two report servers - Google Project Hosting: "reportsync
sync SSRS report between two report servers

Sync reports between two SSRS servers.
Download rdls from SSRS to local PC.
Upload files to a SSRS server
Attach datasources automatically on upload(Data source name given in the report must exist on server)"

there is an error in xml document (5, 2) rptproj - Google Search

2012-01-19T08:27:00.001-08:00

---------------------------
Microsoft Visual Studio
---------------------------
The report definition failed to load: There is an error in XML document (5, 2).. Verify the report definition conforms to the correct schema.
---------------------------
OK Cancel
---------------------------

If you get this, and are using Subversion for your Reporting Services development, you may have a corrupt data source file. The xml involved has nothing to do with rptproj, it is actually an item within, in this case the data source.

XML and Visual Studio are my two least favourite things when dealing with bugs.

there is an error in xml document (5, 2) rptproj - Google Search

Microsoft to enable Linux on its Windows Azure cloud in 2012 | ZDNet

2012-01-12T03:42:00.000-08:00

Actually, it should be any O/S, if VMs are available… an interesting twist on cannibalizing their existing Windows Azure platform.

To date, Microsoft has been balking at customer requests to add persistent VMs to Azure, hoping to get customers to develop Azure apps from scratch instead. But the lack of the ability to host apps like SharePoint and other third-party business applications with persistence was a deal breaker for a number of business users who were unwilling to consider Azure until Microsoft added this support, one of my contacts said.

Microsoft to enable Linux on its Windows Azure cloud in 2012 | ZDNet

Multi-Value (Select All) parameter in Reporting Services | Microsoft Connect

2011-12-30T07:20:00.000-08:00

One area Reporting Services falls down a bit is in the parameter selection process for the built-in Report Manager and integrated modes. Usually this involves building custom parameter web parts or asp.net portals to get around. There are some workarounds for issues related to multi-value parameters and the infamous ‘Select All’ checkbox that may help if your database is getting clobbered.

Multi-Value (Select All) parameter in Reporting Services | Microsoft Connect

SQL Server Reporting Services Team Blog - Site Home - MSDN Blogs

2011-12-26T05:08:00.000-08:00

Is Reporting Services going the way of the dodo, to be replaced by Power View (Project Crescent?)

No.

Comparing Power View, Report Builder, and Report Designer

Power View won’t replace the existing Reporting Services reporting products.

Report Designer is a sophisticated design environment that developers and IT pros can use for embedded reporting in their applications. In Report Designer they can create operational reports, shared data sources, and shared datasets, and author report viewer controls.

In Report Builder, IT pros and power users can create powerful operational reports, and reusable report parts and shared datasets.

Report Builder and Report Designer create RDL reports; Power View creates RDLX reports. Power View cannot open RDL reports, and vice versa.

Both Report Designer and Report Builder are shipping in Microsoft SQL Server 2012 Release Candidate 0 (RC 0) Reporting Services, along with Power View. For more information, see Tools (SSRS).

SQL Server Reporting Services Team Blog - Site Home - MSDN Blogs

It is interesting that Microsoft is launching tools like PowerPivot and Power View, knowing that they will cannibalize their existing Analysis Services and Reporting Services products and roadmaps for future installations.

My question is, why don’t they just go all-in and build out these tools as replacements, instead of diverging paths? Why not incorporate all the best features of Power View into Report Builder, and leverage the RDL language that is designed for reporting?

Same goes for Analysis Services and PowerPivot. Why have two separate servers for each product? What is the value (other than licensing) of splitting up these technologies into 2 siloed environments? The sum of the parts is much better than the individual.

Still looking for some cohesion and sense of direction for the future of SQL Server reporting and BI tools… and hoping for a better 2012.

A deployment checklist for the new Denali / SQL 2012 tools

http://msdn.microsoft.com/en-us/library/hh231687(v=sql.110).aspx

SQL Reports Scheduler|SQL Server Reporting Services|SSRS|Report Distribution|Business Intelligence|SQL-RD|ChristianSteven Software

2011-12-24T03:05:00.000-08:00

The Swiss Army knife of Reporting Services tools. From two men who both aren’t from Switzerland. I’m not really sure what it doesn’t do.

SQL-RD is a Business Intelligence application for windows that saves time and money by making it easy to define single or packages of Microsoft® SQL Reporting Services reports, schedule and run them automatically, and send the reports to print, fax, disk, ftp, sms or email in a number of standard formats.

technoblab

2011-12-23T03:03:00.000-08:00

Day 23: Tips from Technoblab for Report Header/Footer, format codes, customizing the toolbar, working with images, etc

Lots of nice code examples here

A database field can neither be placed in a report header nor in the footer. This will throw an error "The Value expression for the textbox <fieldname> refers to a field. Fields cannot be used in page headers or footers".
There is however a workaround. Place a hidden textbox in the report body call it tb_hidden and assign to this the database field that you need in the header. Place a textbox in the report header and assign the hidden textbox's value to it
ex: =reportitems!tb_hidden.Value
This however as a drawback, the value will appear only in the first page. If your report runs into many pages then you need to take a different approach.
Pass the value to appear in the header as a parameter from your .aspx page call it database_field. Assign this to the textbox in the header.
ex: =Parameters!database_field.Value

technoblab

SQL Server Reporting Services Parameter Logging & Caching

2011-12-22T09:00:00.000-08:00

Day 22: Auto-caching and logging of SQL RS parameters

The solution tracks the selected parameters from a report execution and uses them later for the next execution to take them as default parameters. This enables users to log parameters and inspect the execution statistics of reports as well as caching former execution parameters.

SQL Server Reporting Services Parameter Logging & Caching

Using SharePoint List data for Reporting Services (SSRS) parameter source « Dan English's BI Blog

2011-12-21T07:15:00.000-08:00

Excellent article with how to filter distinct values from a list using SSRS and Sharepoint.

Using SharePoint List data for Reporting Services (SSRS) parameter source « Dan English's BI Blog

SSIS ReportGeneratorTask

2011-12-21T04:23:00.000-08:00

Day 21 was probably part of Day 20 too. Still worth a mention. Code Generator for SQL RS reports

SSIS Task for SQL Server 2008 to create server reports or local reports from a recordset data source.

SSIS ReportGeneratorTask

SSIS Community Tasks and Components

2011-12-20T06:35:00.000-08:00

Day 20: An aggregator of all the SSIS components on Codeplex and elsewhere

Anything and everything you ever need to do with SSIS is somewhere on this page. Includes some Reporting Services functionality too.

This is a community project for developing additional tasks and components for SSIS, and identifying other CodePlex projects that do the same. 51 Integration Services extensibility projects exist on CodePlex - find them and over a hundred more from around the 'net right here.

SSIS Community Tasks and Components

Microsoft SQL Server Community & Samples

2011-12-19T04:42:00.000-08:00

Day 17, 18, and 19: Whatever you want to do with SQL is somewhere in these samples…

Microsoft SQL Server Community Projects & Samples

Microsoft SQL Server Community & Samples

SQL Server 2008 & R2 Reporter

2011-12-16T06:42:00.000-08:00

Day 16: One of the more useful tools out there – integration of Reporting Services with SSMS for exporting table data to a report!

Because the import/export wizard sucks.

SQLReporter enable you exporting table or view to Reporting Services Report , Then , you can export it to Excel & PDF. Quick & nice.

SQL Server 2008 & R2 Reporter

GUI Tool Reveals SQL Injection Vulnerabilities

2011-12-16T04:43:00.001-08:00

SQL Injection attacks are probably the most common attacks on internet-facing SQL Servers due to their ease of use. This tool helps expose vulnerabilities to end-users and non-IT executives, visually.

The reasoning is that most existing SQL Injection testing tools are executed from a command line and “lack an intuitive user interface”. In other words, if you can’t show the problem in a pretty web page, people won’t really believe it exists.

GUI Tool Reveals SQL Injection Vulnerabilities

SSRS Report Downloader

2011-12-15T04:32:00.000-08:00

Day 15: Leech for Reporting Services

SSRS downloader is a tool to download all report files from a folder from Reporting Services at once. It uses reportservice2005.asmx endpoint web service to do this.

SSRS Report Downloader

SimpleDBExtension - SSRS extension for SimpleDB

2011-12-15T03:55:00.000-08:00

Day 15: Amazon SimpleDB query extension for your cloud computing needs

If you’d like to tell your manager you’re running reports “off the cloud”, here’s one way to do it.

http://aws.amazon.com/simpledb/

This project is a SQL Server Reporting Services 2008 data processing extension that facilitates querying of Amazon's SimpleDB.

SimpleDBExtension - SSRS extension for SimpleDB

SSRS PowerShell Provider

2011-12-14T08:45:00.000-08:00

Day 14: A Powershell provider for SSRS

Doesn’t look too current but probably still works…

A PowerShell provider for SSRS (SQL Server Reporting Services). The provider allows for navigation of SSRS installations, management of reports, etc, all through a scriptable interface.

SSRS PowerShell Provider

Database Documentation Reports

2011-12-13T08:44:00.000-08:00

Day 13: Self-service documentation

These are reports for MS SQL Management Studio and MS SQL Reporting Services for creation of database documentation with the use of descriptions from Extended Properties.
Reports contain the detailed information on tables, views and stored procedures etc. Use of reports engine allows exporting generated reports into Excel, Word, PDF etc. formats easily.

Database Documentation Reports

Sperm Report

2011-12-12T04:25:00.000-08:00

Skipping the weekend again.

Day 12: The unfortunately-named sperm report project, blowing out data from OLAP cubes with “Spermline visualization”

Essentially with these four reports you can create 400 user reports (actually as many as you like) Change what is displayed on rows, columns, filter or time by simply creating a new "linked report" and setting a new set of paramaters.

Sperm Report

RSBuild SQL Server Reporting Service Deployment Tool

2011-12-09T08:37:00.000-08:00

Day 9: An automated build tool for SQL RS

RSBuild is a deployment tool for SQL Server Reporting Service. It currently supports two types of tasks: executing SQL Server scripts and publishing SQL Server Reporting Service reports and shared data sources. From version 1.1.0 onwards SQL Server 2008 Reporting Services is supported as well as all previously supported versions of SQL Server Reporting Services.

RSBuild SQL Server Reporting Service Deployment Tool

SQLCAT Community Projects and Code Samples

2011-12-09T04:36:00.000-08:00

Day 9: Auditing in SQL from the SQLCat team (and Denny Lee)

This project is a place for the SQL CAT team to share their expertise with the community and collaborate with other community members outside Microsoft who wish to contribute code samples to enable more productive use of the SQL Server platform for high-scale,enterprise customers

SQLCAT Community Projects and Code Samples

RSToolKit

2011-12-08T05:18:00.000-08:00

Day 8: RSToolKit, a tool for creating, moving, documenting, and exporting reports.

This could be very useful in a distributed, farm or DRP environment.

RSToolKit allows users to administrate different SQL Server Reporting Services with one application and has different methods to deploy reports. It supports all SQL Server Reporting Services versions which are using the ReportService2005 and the ReportExecution2005 SOAP API

RSToolKit

Drilltrough and filtering on SSAS-cubes in SSRS

2011-12-07T06:34:00.000-08:00

Day 7: A framework for navigating an Analysis Services cube from SSRS

Basically the user-friendliness of the interface is achieved by allowing users to drill up/down on the different hierarchies by simply clicking columns or rows, and applying filters by clicking on icons in rows or columns.
The performance is achieved by only retrieving the necessary data from the cube.

Drilltrough and filtering on SSAS-cubes in SSRS