Prologika Forums to be Upgraded Tonight

Telligent has done it again! Community Server 2.0 is out and it’s too sleek to ignore. I am planning to upgrade Prologika Forums to CS 2.0 tonight. If all is well, tomorrow Prologika Forums will have a facelift and more features. I hope the upgrade process won’t last too long and won’t cause service interruptions.

Phantom Data Extensions

There are two “shy” data extensions that come with RS 2005 but you won’t see when you create a data source.

The first one (SSIS data extension) enables interesting integration scenarios with SQL Server 2005 Integration Services. It allows you to author reports that use data generated by running an Integration Services (SSIS) package. The second one is the SAP data extension for integrating SSRS with mySAP Business Suite.

By default, these extensions are disabled and commented in the rsreportserver.config file:
<!– <Extension Name=”SSIS” Type=”Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91″/> –>
<!– <Extension Name=”SAP” Type=”Microsoft.Adapter.SAP.SAPConnection,Microsoft.Adapter.SAP.SAPProvider, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35″/> –>

Why are these extensions doomed to obscurity, you may ask? The reason is that they have setup dependencies. It could have been nice if the SQL Server 2005 setup program could verify these dependencies (e.g. if SSIS is selected for install) and enables the extensions accordingly so the users know about their existence but this feature was cut out due to time constrains. Assuming that you install the required components, you can just comment out the extension to enable it.

Where Is My Cartridge?

A little known fact about the SSAS data architecture is that it uses “cartridges” to communicate with the data source. In brief, a cartridge is a XSL stylesheet that defines capabilities of a data source, as well as the rules for optimizing the SQL statements for relational querying and writing. SSAS 2005 ships with set of cartridges for Jet, SQL 70, SQL 2000, Oracle, Teradata, and DB2, which can be found in the \Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\Cartridges folder. Vendors can plug in (server restart required) cartridges for other data sources if needed.

One gotcha is when the UDM uses multiple data sources in a single data source view. This scenario requires that the primary data source must be SQL Server because behind the scenes the server uses the SQL Server-specific OPENROWSET statement to extract data from the secondary data source(s). The problem with this approach is that it effectively bypasses the installed cartridge for the non-SQL Server data source. As a result, processing queries that normally execute just fine when the DSV uses that data source only, fail to execute in a multi-data source DSV.

There are at least three workarounds for this predicament. First, you can replace each table in the DSV with a named query which uses the right native syntax. Second, you can link the data server to your SQL Server and wrap the linked server tables with SQL views. A third solution is to split UDM per a data source – a SQL Server UDM and another UDM for the second data source. Then, you can link the dimensions and measure groups from one UDM to another. As you have probably guessed it, all of the above approaches may present maintenance and operational challenges. It will be great if a service pack of a future release solve this issue and honors the cartridges with heterogeneous queries.