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=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91″/> –>
<!– <Extension Name=”SAP” Type=”Microsoft.Adapter.SAP.SAPConnection,Microsoft.Adapter.SAP.SAPProvider, Version=1.0.0.0, 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.

MDX Solutions Book on the Horizon

Chris Webb tells us that the MDX Solutions book, which we co-wrote with other MDX gurus, is soon to be released. Based on the resources available on the publisher’s website, the book looks like a very promising read and I am looking forward to get my hands on it. Unfortunately, during my Applied Microsoft Analysis Services 2005 book project, I didn’t have the time to fully research the vast MDX changes in SSAS 2005 and devote the space they deserve. This is where MDX Solutions comes in. I enjoyed the previous edition by George Spofford. I am sure that I will enjoy the sequel as well.

Join me in the "hub"

I’ve just learned that I’ve been selected to present at TechEd 2006 US in Boston! I’ve been honored to present Reporting Services for three consecutive years at TechEd. This time, I will change things a bit and instead of focusing on the development area, I will talk about integrating Reporting Services and Analysis Services. As of now, the main topics I am planning to include are:


1. SSAS report actions
2. MDX Query Designer
3. Server aggregates
4. Building data mining reports
5. Ad-hoc reporting
6. CLR integration


See you in the “hub”!

Building Report-enabled Applications with the New ReportViewer Controls (Part 2 of 2)

In the first part of this article, you saw how to use the Windows Forms version of the ReportViewer control. The second installment walks you through the differences and discusses the process for using the Web version of the control.

Nick Barclay’s Review

Nick Barclay from Australia has good things to say about my book “Applied Microsoft Analysis Services in Action”. No, I didn’t sweat blood, but I have to admit that writing this one wasn’t easy! It took me almost twice as long to write it as my previous “Microsoft Reporting Services in Action” book. For the most part, that was because Analysis Services is a focal point of many technologies, including OLAP, data mining, data integration, reporting, performance management, etc. Not to mention that, as Nick said it, it was like trying to hit a moving target because the product was constantly evolving on the way to RTM.


The other main factor for taking so long is that I just can’t write short. I am a firm believer that knowing a product is knowing its limitations. I usually put myself in my reader’s shoes and try to address all possible questions the reader may ask while reading the book. I am committed to point out not only the tool strengths but also its weaknesses (nothing is perfect, right). Of course, this takes time, lots of, lots of time …

SQL Server Express Advanced Services Beta Announced

Euan Garden (Product Unit Manager for SQL Server) tells us that SQL Server Express Advanced Services (which includes Reporting Services Express) has reached a beta stage. I emphasized the importance of choosing the right RS edition in one of my previous blogs.

When (null) is not NULL

Here is an interesting SSRS-SSAS integration gotcha that got me recently. Suppose that you have the following MDX query.



Needless to say, this query is most likely wrong to start with. It brings the members of three dimensions even if they don’t exist with each other. The correct version should probably be to cross-join the attribute hierarchies which is what the MDX Query Designer does behind the scenes when you place attribute hierarchies side by side. As you could see, as it stands, this MDX query brings 12 rows. However, much to your surprise, when you bind this dataset to a table region, you will see only three rows (the ones that don’t have (null)). So, what’s going on?


It turns out that the rows with (null) are server aggregates. The (null) in this case in this case indicates that this is a subtotal row. Since such a row is not a detail row, the SSRS Analysis Services data extension drops the row when you bind the dataset. If you add groupings on the table (group1: AgeGroup, group2: Year, group3: Month), and use the Aggregate() function accordingly in the group headers/footers, the table will show the appropriate aggregated measure values from the AS flattened rowset in the correct scopes.


What if you want to show all 12 dataset rows as detail rows on your report? The only way to do this is to switch the data source from “Analysis Services” to “OleDB” and select the MSOLAP.3 (OleDb for OLAP 9.0) or MSOLAP.2 (OleDB for OLAP 8.0) provider. However, once you switch to the OleDB provider, new features such as the graphical MDX query designer, query parameters for MDX queries, etc. are no longer available. You have to use the text-based generic query designer. You could design the query with the graphical designer as usual and then copy & paste the non-parameterized MDX query string to use it with the old OleDB provider.


One cautionary note: As I noted, most likely the original MDX query is wrong. While changing the data extension will give you all the rows, make sure that this is what you want because the report now will show duplicate rows that don’t exists within the cube space. This will probably skew your report results and confuse the end users.


The SSRS team is considering extending the RDL schema to so that server aggregate rows (currently determined by if a data extension implements the IDataReaderExtension interface) can be explicitly interpreted as real server aggregates (behavior of the AS 2005 AdoMd data extension) or as detail rows (behavior of the AS 2000 OleDB provider).

Building Report-enabled Applications with the New ReportViewer Controls (Part 1 of 2)

DevX has just published Part 1 of my article Building Report-enabled Applications with the Windows Forms ReportViewer. Part 2 (ASP.NET ReportViewer) should be out by the end of February.

Reporting is an integral part of every complete application. The Report Viewer controls greatly reduce the development effort required to report-enable .NET applications. Part one of this two-part article shows you how you can leverage the Windows Forms ReportViewer to integrate your Windows Forms .NET applications with Reporting Services 2005.  

Logging Report Builder Queries

As you probably know, the RS 2005 Report Builder lets business users create ad-hoc reports. Behind the scenes, the Query Translator converts the report metadata to the native queries supported by the relational data source (SQL Server and UDM with RS 2005 only). If you want to see the underlying queries, you have two choices:


1. You can use the SQL Server Profiler and examing its trace.


2. You can log the queries to the RS log files or watch them realtime by attaching a trace listener (e.g. the SysInternals DbgView). To enable this option, open the Report Server web.config file and change the Components element as follows:


<add name=”Components” value=”all,RunningJobs:3,SemanticQueryEngine:2,SemanticModelGenerator:2″ />


<add name=”Components” value=”all,RunningJobs:3,SemanticQueryEngine:3,SemanticModelGenerator:2″ />


Alternatively, you can omit the SemanticQueryEngine element whatsoever. The net effect of doing so will be escalating the trace switch to 4.


You may find this article useful in case you need to learn more about the Report Builder architecture.