Prologika Forums

Where is RDLOM in R2?

Teo Lachev — Tue, 09 Mar 2010 02:17:00 GMT

After covering the cool new stuff in R2, it's time to talk about the uncool. One R2 change that really vexes me is RDLOM. RDLOM, short for Report Definition Language Object Model, lets developers program RDL in object-oriented way as opposed to using XML. RDLOM could save you a lot of effort when you have to meet more advanced requirements that involve pre-processing RDL. For example, in one of my projects we had to implement a wizard that would walk business users through steps to generate an ad-hoc report. Behind the scenes, the wizard would generate the report definition by using a custom RDLOM which we developed since back then Microsoft didn't have their own. I included a scaled-down version of our implementation in my TechEd 2007 code sample. Although it took us a while to get our RDLOM implemented, it turned out to be invaluable down the road. Since we didn't have to use XML API to manipulate RDL, it simplified programming RDL. For another project, we used RDLOM to let the customer specify which sections they want to see on the report. Once the selection was made, we would remove the unwanted sections from a report template. The list of real-life scenarios that could take advantage of RDLOM goes on …

Apparently, I wasn't the only one bugging Microsoft about RDLOM that ships with SSRS and they promised that this will happen at TechED 2007 USA. Sure enough, SSRS 2008 brought us an unsupported version which I blogged about and covered more extensively in my Applied SSRS 2008 book. Although unsupported, the SSRS 2008 RDLOM was very useful. It was implemented in a single assembly (Microsoft.ReportingServices.RdlObjectModel.dll), exposed all RDL elements as objects, and supported serializing and deserializing RDL.

Alas, this will come to pass with R2. Upgrading to R2, you'll find that the classes in Microsoft.ReportingServices.RdlObjectModel are marked as internal so you cannot use this assembly. Instead, RDLOM got moved into the Microsoft.ReportingServices.Designer.Controls.dll assembly. The good news is that there are rumors that RDLOM may get documented. Now the bad news:

Clearly, R2 RDLOM was not meant to be distributed. While you need to reference explicitly only Microsoft.ReportingServices.Designer.Controls and Microsoft.ReportingServices.RichText assemblies you'll find that they are dependent on other assemblies and you need to distribute a total of 25 assemblies resulting in a whopping 25 MB of referenced code. One way to avoid shipping so many assemblies to deploy your application in the BIDS or Report Builder 3.0 folders.

R2 RDLOM will be 32-bit only because BIDS and Report Builder are 32-bit only.
R2 RDLOM doesn't support serializing the object model. I found a hack that uses .NET reflection to invoke the internal Serialize method of the old RDLOM 2008.

The RDLOM R2 C# console application bundled in this code sample demonstrates how you can use the "new" RDLOM. Besides referencing the new assemblies, you need to use the Load method to deserialize RDL to the object model:

report = Report.Load(fs);

And the above-mentioned hack to serialize it back to disk or stream.

using (FileStream os = new FileStream(ReportPath, FileMode.Create)) {

Assembly assembly = Assembly.GetAssembly(typeof(Report));

object mc = assembly.CreateInstance("Microsoft.ReportingServices.RdlObjectModel.Serialization.RdlSerializer");

Type t = mc.GetType();

MethodInfo mi = t.GetMethod("Serialize", new Type[] { typeof(Stream), typeof(Report) });

report = (Report)mi.Invoke(mc, new object[] { os, report });

}

Other than that, RDLOM remains virtually unchanged from 2008 excluding that it now supports the R2 enhancements to RDL. What if you don't need this overhead but you need to make small changes to RDL that don't necessarily require an object-oriented layer. I've found that the easiest way to do this and avoid XML programming is to use LINQ to XML. The LINQ to XML project included in the same sample demonstrates this approach. For example, the following code changes the data source reference of the first data source.

static void Main(string[] args) {

XElement report = XElement.Load("report.rdl");

string dns = "{" + report.GetDefaultNamespace() + "}";

XElement ds = report.Element(dns + "DataSources").Element(dns + "DataSource").Element(dns + "DataSourceReference");

ds.Value = "/Data Sources/AdventureWorksAS2008";

report.Save("report1.rdl"); }

Not happy about the RDLOM story? Join the movement and vote for my suggestion on connect.microsoft.com to have a supported RDLOM.

How Windows UAC Got Me Again

Teo Lachev — Tue, 02 Mar 2010 21:17:00 GMT

User Access Control (UAC) is a feature of Windows Vista, Windows 7, and Windows Server 2008 that prevents you from running applications with administrator rights even if you are a local admin on the box. Although I have to admit I hate UAC, I am convinced that it's better to have it around especially after my home desktop got infected a few months ago with a rootkit virus so badly that I had to restore the disk from a backup. So, UAC is a necessary evil. But since it's relatively new, not many application developers test for UAC and this can get you as it happened in the following story.

A coworker complained that Reporting Services SharePoint integration doesn't work on a distributed environment where SharePoint is installed on the front end Windows 2008 server and the SharePoint configuration database is installed on a separate database server. As usual, my first stop for troubleshooting SharePoint integration with Reporting Services was to examine the log file which gets generated after installing the Reporting Services Add-in for SharePoint, as I explained in this blog. On Windows Server 2008, the log file is located in the Temp folder of the user running the add-in setup, such as C:\Users\<user>\AppData\Local\Temp. To make the quest even more interesting, the AppData folder is actually hidden by default on Windows Server 2008 and Windows Vista/7 (another security gotcha), so you have to use Folder and Search Options to enable hidden files so you can see it. Are you having fun yet?

In this case, the log file showed an error:

User: SYSTEM

Installing Report Server feature.

Calling copyappbincontents command.

copyappbincontents command completed successfully.

Adding Report Server feature to farm.

Install error: The EXECUTE permission was denied on the object 'proc_putObject', database 'IWSharePointCluster_Config', schema 'dbo'.

So, SharePoint can't connect to the database server. What's interesting is that the log file reported that the user is SYSTEM and not the logon of the interactive user. Luckily, Prash Shirolkar from the SSRS team blogged about this and provided a workaround for this issue which will get fixed in R2 RTM. Gotta love UAC…

New Reporting Services Data Providers

Teo Lachev — Sun, 28 Feb 2010 15:39:00 GMT

Two new data providers will be included in the SQL Server 2008 R2 release of Reporting Services, Microsoft SQL Azure and Microsoft SQL Server Parallel Data Warehouse, that will let you report from SQL Azure and SQL Server 2008 R2 Parallel Data Warehouse (code named Madison) databases respectively. I decided to take the Microsoft SQL Azure data provider for a spin. As you probably know by now, SQL Azure is a part of the Microsoft Windows Azure SaaS cloud offering. SQL Azure lets you host your SQL Server relational databases in the Microsoft data centers that provide scalability and fault tolerance services. This is great for companies of all sizes which don't have resources or expertise to purchase and configure expensive hardware and software in order bo build scalable and highly available solutions. IMO, SQL Azure will be increasingly important, especially after Reporting Services and Analysis Services become available on SQL Azure although Microsoft doesn't say yet when this will happen. At this point, you can only use Reporting Services as a client to author reports from SQL Azure-hosted databases.

Strictly, speaking if your database is hosted in SQL Azure, you can use the SQL Server or OLE DB providers (already included in Reporting Services) to send queries to SQL Azure. Just like on-premise solutions, SQL Server Azure clients retrieve data using the native SQL Server Tabular Data Stream (TDS). However, if you decide to use the native SQL Server providers, you will run into some issues with retrieving SQL Azure metadata. Hence, Microsoft added the SQL Azure data provider. To test it out, I followed these steps:

I signed up for SQL Azure. If you MSDN Universal subscriber, you can sign up to test SQL Azure for eight months for free.
I deployed the AdventureWorksDW2008R2 and AdventureWorksLT2008R2 databases to my SQL Azure server. The Adventure Works database deployment scripts for SQL Azure are available on codeplex. They will create AdventureWorksDWAZ2008R2 and AdventureWorksLTAZ2008R2 database on your SQL Azure server.
Installed SQL Server 2008 R2 Release Candidate which is not publicly available.
Created a new report in BIDS. As usual, I added a data source and selected the Microsoft SQL Azure provider.
In the Connection Properties dialog box, I entered the name of my SQL Azure server and my standard security credentials (Windows security is not supported on SQL Azure). For some reason, the database dropdown is not automatically populated so I had to enter the name of the database AdventureWorksDWAZ2008R2 manually.
Once connectivity has been taken care of, life is as usual. The Graphical Query Designer (toggle the Edit as Text button if the generic query designer starts first) shows the database schema.

One thing that surprised me was the performance of data retrieval. Even when I opted to get all data from FactInternetSales, which has thousands of rows, the query executed pretty fast. One caveat though with this approach is that you have open port 1433 on your corporate network because this is the port SQL Server uses for client connections. This is not required if your application is installed on Windows Azure and connects to the SQL Azure database on the server.

Beware Hidden Calculated Members

Teo Lachev — Mon, 18 Jan 2010 23:06:07 GMT

Here is another one of those "by design" issues (see the Not All Calculated Members Are Born Equal blog for another recent gotcha) that are not documented but can sure waste you precious time figuring out. The issue was first submitted on connect.microsoft.com by Greg Galloway almost a year ago where it was left in an investigation phase (what can we do to get the SSAS team actually review the connect cases and post findings?). Then, I submitted a similar issue and official support case since I was 100% sure it was a bug and I needed a closure. Alas, Microsoft came back to declare it "by design":

"…per feedback from several AS 2005 Beta customers, for compatibility with pre-existing AS 2000 behavior, AS 2005 RTM (and later releases) was changed to not include hidden calculated members in cell calculations and scopes (except when a hidden calculated member is explicitly referenced in the calculation scope)."

The issue affects hidden calculated members and scope assignments where a scope assignment simply ignores hidden calculated members if they are not explicitly referenced by the script. You can easily repro the issue by making the following changes to the Adventure Works script:

/*-- Allocate equally to quarters in H2 FY 2005 ------------------------*/

CREATE MEMBER CURRENTCUBE.[Measures].[Visible Quota] AS null,

FORMAT_STRING = "Currency",

VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Sales Targets' ;

CREATE MEMBER CURRENTCUBE.[Measures].[Invisible Quota] AS null,

FORMAT_STRING = "Currency",

VISIBLE = 0 , ASSOCIATED_MEASURE_GROUP = 'Sales Targets' ;

Scope

(

[Date].[Fiscal Year].&[2005],

[Date].[Fiscal].[Fiscal Quarter].Members

);

this = (ParallelPeriod

(

[Date].[Fiscal].[Fiscal Year], 1,

[Date].[Fiscal].CurrentMember

), [Measures].[Sales Amount Quota]) * 1.35;

Here, I created two identical calculated members with the only difference that the first one is visible and second one hidden. Then, execute the following query:

SELECT {[Measures].[Sales Amount Quota], [Measures].[Visible Quota], [Measures].[Invisible Quota]} on 0

FROM [Adventure Works]

WHERE [Date].[Fiscal].[Fiscal Quarter].&[2005]&[1]

And here are the results (surprise!):

The Visible Quota member is populated as it should be but Invisible Quota refuses to budge as the script simply ignores it. If the script scopes on the Invisible Quota member explicitly or you make it visible, then we get the expected results. Since the issue is by design and a fix won't be available anytime soon, you should be very careful when you use hidden calculated members and expect them to be affected by scope assignments. I use often helper calculated members which I hide since they shouldn't be visible to the end users. This issue hit me really hard after QA reported wrong results and your humble correspondent had to debug a cube with some 1000+ lines of code.

Report Builder 3.0 Edit Sessions

Teo Lachev — Mon, 18 Jan 2010 14:25:00 GMT

When Report Builder 2.0 shipped , it introduced local and server preview modes. If the report uses local references only, such an embedded data source(s), Report Builder 2.0 executes the query and renders the report locally just like the BIDS Report Designer. However, when the report uses a server reference, such as shared data source(s) or subreports that were saved on the server, Report Builder 2.0 would transparently upload and process the report on the server just like the Report Builder 1.0.

Report Builder 3.0 improves the performance of server preview mode by introducing edit sessions. An edit session is implicitly created when the user runs the report for the first time. Behind the scenes, Report Builder 3.0 calls the new CreateReportEditSession API to instruct the server to create an edit session. The server caches the report dataset(s) in the ReportServerTempDB database (not in memory) and uses the cached data for subsequent previews. In this respect, you can think of edit sessions as the *.data files that the BIDS Report Designer generates to cache the report data in order to speed up report preview. As long as you don't make changes to the report that affect the data, such as changes to the dataset itself, data source, or parameters, the cached copy of the data is used by the report. This means that the user may not see changes that might have taken place in the database for the duration of the edit session. To make sure that the latest data is used, click the Refresh button on the Report Builder 3.0 toolbar. Refreshing the report discards the edit session and executes the database queries.

The administrator can configure two new server properties to manage edit sessions:

EditSessonCacheLimit - By default, the data cache can hold up to five datasets per report and user. Note that if the dataset is parameterized, each parameter combination counts as one dataset. If you use many different combinations of parameter values, the report might need more cached dataset copies. The administrator can allow this by increasing the EditSessonCacheLimit setting.
EditSessionTimeout - By default, an edit session lasts 7200 seconds (2 hours). The servers resets the session every two hours when the user previews the report.

Report Builder 3.0 edit sessions are a performance-related enhancement that makes server-side report preview more efficient.

Report Builder 3.0 Enhancements in R2 and SharePoint 2010 Mode

Teo Lachev — Wed, 13 Jan 2010 03:06:00 GMT

A couple of Report Builder enhancements in SharePoint 2010 integration mode caught my attention. First, Report Builder 3.0 now launches by default when the Open in Report Builder action is initiated inside SharePoint 2010. Previously, Report Builder 1.0 would be launched.

Second, if the report includes a drillthrough action to another report, the action now works when the action is initiated from Report Builder 3.0 preview mode. As it turns out, a drillthrough action is forwarded directly to SharePoint. Previously, a report drillthrough action will result in an error because the Report Builder would attempt to resolve the action locally.

ReportService2010 Endpoint

Teo Lachev — Wed, 13 Jan 2010 02:29:57 GMT

Here is a little gem for developers programming Reporting Services. SQL Server 2008 R2 brings a new web service endpoint, ReportService2010, which merges the functionalities of the ReportService2005 and ReportService2006 endpoints. The new endpoint can manage objects on a report server that that are configured for either native or SharePoint integrated mode. Previously, the ReportService2005 endpoint was used for managing objects on a report server that is configured for native mode and the ReportService2006 endpoint was used for managing objects on a report server that is configured for SharePoint integrated mode. This required code changes and retesting if the report server was re-configured in a different mode. These endpoints are now deprecated but still supported. The ReportService2010 endpoint includes the functionalities of both endpoints. When unsupported APIs in ReportService2010 are invoked, such as invoking the CreateLinkedReport API in SharePoint mode which doesn't support linked reports, the endpoint will return an error that the operation is not supported.

Virtual Adventures

Teo Lachev — Thu, 07 Jan 2010 23:48:58 GMT

Scenario: Mount base and differencing VHD disks using Windows Virtual PC on Windows 7 x64.

Dude, did you run out of BI stuff to talk about? I didn't but I think other folks may benefit from my experience especially MCTs who have discovered that Windows 7 hasn't happened yet to the Microsoft Official Curriculum and resorted to all sorts of hacks to get Virtual Server 2005 or Lab Launcher working with Windows 7. In the process, I've learned a lot about the virtualization technology that goes beyond just mounting disks. OK, I have a hidden agenda to write this blog as well – I want to document these steps.

So, I am preparing to teach 6326A - Implementing and Maintaining Microsoft SQL Server 2008 Reporting Services in an instructor-led environment where I have to install the required software on the student's machine. After downloading the trainer resources, I have discovered that Microsoft provides 6236A-NY-SQL-01.vhd disk which requires a base disk Base08A.vhd. Reading the setup guide included in the course revealed that Microsoft wants me to install Virtual Server 2005 on both the instructor and students machines.

Gotcha 1: Virtual Server 2005 doesn't run on Windows 7. Browsing the MCT private groups I came across posts discussing a hack around this limitation but I didn't want go that way. Not to mention that Microsoft Lab Launcher, which apparently is intended to simplify the VHD deployment to student machines, doesn't run on Win 7 as well. A double-gotcha which picked up my interest and stirred my hacking instincts to get the whole thing working with Virtual PC. After all, I can't control what OS the students run or the training center has installed.

OK, but this is just VHD disk, isn't it? So, it should run on Windows Virtual PC, right? So, I created a new virtual machine and attempted to mount 6236A-NY-SQL-01.vhd but I was greeted with the following informative error:

Cannot attach the virtual hard disk to the virtual machine. Check the values provided and try again.

Then I mounted Base08A.vhd and I was able to boot the VM only to find out that it includes the Windows Server 2008 OS only. Where is the cool SQL Server BI stuff? This is the part where I realized that my virtual knowledge needs brushing up. Enter differencing disks. This of course is not explained in the setup document, so don't try to find it there. I figured that Base08A.vhd is the base disk that has the OS only while the 6236A-NY-SQL-01.vhd is the differencing disk that has the rest (SQL Server 2008 + Reporting Services samples and Adventure Works database) installed. To prove my hypothesis, I used the Sun's VirtualBox. I use VirtualBox for my personal VM work because it supports x64 guess OS, which Virtual PC doesn't support (shame). I used the Disk Manager to add both the base and difference images and … lo and behold, the VM booted up and I was able to verify that SQL Server 2008 and rest of the goodies are installed.

But I didn't want to force the students to install VirtualBox so I continued hacking my way to Windows Virtual PC. In a Eureka moment, I examined the Windows Event log and saw the following message:

The parent virtual hard disk 'C:\Program Files\Microsoft Learning\Base\Base08A.vhd' for the differencing virtual hard disk 'C:\VPC\3263\6236A-NY-SQL-01.vhd' does not exist. Please reconnect the differencing virtual hard disk to the correct parent virtual hard disk.

So, the base disk path is hardcoded in the differencing image. The following commands confirmed this.

Went to command prompt and entered (the actual commands are in bold):
C:\>Diskpart
DISKPART> select vdisk file="c:\vpc\6236\6236A-NY-SQL-01.vhd"
DiskPart successfully selected the virtual disk file.
DISKPART> detail vdisk
Device type ID: 2 (VHD)
Vendor ID: {EC984AEC-A0F9-47E9-901F-71415A66345B} (Microsoft Corporation)
State: Added
Virtual size: 64 GB
Physical size: 9 GB
Filename: c:\vpc\6236\6236A-NY-SQL-01.vhd
Is Child: Yes
Parent Filename: C:\Program Files\Microsoft Learning\Base\Base08A.vhd
Associated disk#: Not found.
I moved the based image (Base08A.vhd) to C:\Program Files\Microsoft Learning\Base. With a baited breath, I repeated the process of creating a new virtual machine in Windows Virtual PC pointing to the existing 6236A-NY-SQL-01.vhd but I got the first error:
Cannot attach the virtual hard disk to the virtual machine. Check the values provided and try again.
No more details in the Windows event log. At this point, I posted a question to the Windows 7 Virtualization discussion forum and I was clued in the disk virtual size (not physical size) could be the issue since Virtual PC doesn't support disks larger than 127 GB. Although after attaching the 6236A-NY-SQL-01.vhd using the Windows 7 Disk Manager I could see that the virtual size is 64 GB, I compacted the base disk using these steps. That didn't help and the hour was getting late. So I threw the white towel on the differencing disk approach (for now) and decided to merge the differencing disk into the base disk.
Assuming the commands in step 1), I executed the following commands:
DISKPART>detach vdisk
DiskPart successfully detached the virtual disk file.
DISKPART>merge vdisk depth=1
100 percent complete
(after a long wait …)
DiskPart successfully merged the differencing chain.

Now that I had the disks merged I was able to create a new virtual machine successfully using the base disk image (Base08A.vhd) which combined both the original and differencing disks. Since the differencing disk was merged, I could delete 6236A-NY-SQL-01.vhd.

Gotcha 2: After booting the virtual machine, I was able to log in using the pre-defined Student account. However, attempting to run the SQL Server Management Studio, I got an error that the SQL Server 2008 evaluation period has expired. I attempted to reset the Windows Server 2008 (guest OS) system date, but after a few seconds the date would revert to the present date. More binging the Internet solved the issue.

Shut down the VM.
Open the vmc file in Notepad.
Chang the last six digits to a year ago (070109)
<time_bytes type="bytes">57002400060004070109</time_bytes>
Further down the file, disable guest-to-host OS time synchronization:
<host_time_sync>
<enabled type="boolean">false</enabled>
<frequency type="integer">15</frequency>
<threshold type="integer">10</threshold>
</host_time_sync>
Boot VM. Now change the system date to 07-01-09.

Success!

Most Requested Features

Teo Lachev — Mon, 21 Dec 2009 21:06:51 GMT

You can use the Microsoft Connect website to find most requested features. Unfortunately, the search doesn't let you specify a product so the search results may be related to other products. For example, searching on reporting services may bring in results from Analysis Services and reporting. Nevertheless, it was quite interesting to find the top voted suggestions. For example, the following query shows the top suggestions for Reporting Services (flip to the Suggestions tab):

https://connect.microsoft.com/SQLServer/SearchResults.aspx?KeywordSearchIn=2&SearchQuery=%22reporting%22+AND+%22services%22&FeedbackType=1&Scope=0&SortOrder=10&TabView=0&wa=wsignin1.0

Reporting Services-Recognize multiple result sets returned from a stored procedure – (50 votes)
Merging / Linking datasets on report level (50 votes) – No 6 on my SSRS Top 10 wish list.
SQL Reports should support stylesheets (43 votes) – No 9 on my list.
Support for XML Paper Specification (XPS) Output Format (29 votes) – I am personally surprised about this one.
Reporting Services Security Using Membership and Roles (29 votes), and so on

Running ReportViewer Under Trusted Account

Teo Lachev — Wed, 16 Dec 2009 00:33:00 GMT

Scenario: You have an Internet-facing web application that uses the Visual Studio ReportViewer control to render reports. The application takes care of authenticating the users. You want all report requests to the report server to go under a single (trusted) Windows account. There are at least two approaches to implement this scenario:

Basic Authentication

You can configure ReportViewer for basic authentication by implementing the Microsoft.Reporting.WebForms.IReportServerCredentials interface, as follows:

[Serializable]

public class MyReportServerCredentials : IReportServerCredentials

{

public MyReportServerCredentials()

{

}

public WindowsIdentity ImpersonationUser

{

get

{

return null;

}

public ICredentials NetworkCredentials

{

get

{

return new NetworkCredential(

ConfigurationManager.AppSettings["reportUser"],

ConfigurationManager.AppSettings["reportPassword"]

, "<your domain here>" – comment out this line if a domain account is used as a trusted account

);

}

public bool GetFormsCredentials(out Cookie authCookie, out string user, out string password, out string authority)

{

authCookie = null;

user = null;

password = null;

authority = null;

return false;

}

Specifically, you need to implement the NetworkCredentials property of IReportServerCredentials. In my case, I read the trusted account credentials from web.config. Once IReportServerCredentials is implemented, you need to configure ReportViewer to use it. This takes one line of code:

reportViewer.ServerReport.ReportServerCredentials = new MyReportServerCredentials();

The advantage of using IReportServerCredentals if flexibility. You can retrieve the account credentials from any place, such as security service, configuration file, database, etc. In addition, the impersonation is scoped for ReportViewer only. Other network calls made by the applications are unaffected.

Process Identity

The issue with IReportServerCredentals is the application must deal with the credentials of the trusted account, including the password. For added level of security, I try my best to avoid handling passwords and use Windows authentication whenever I can. Therefore, my preferred approach is to change the process identify of the web application. One way to do this is to change the identity of the IIS application pool the web application is running under, as follows:

If you don't have a domain account to use as a trusted account, create a new domain account.
On the web server, add the account to the IIS_WPG local Windows group to give it the necessary rights to be used as an application pool account.
In the IIS Manager, expand the server name, right-click the Application Pools folder, and click New Application Pool.
Give the new pool a name. In the pool Properties page, click the Identity tab, select the Configurable option, and enter the credentials of the trusted account.
Open the web application properties and change its application pool to the pool you just created.
If present, remove <identity impersonate="true"/> element from the application web.config file so the network calls are made under the process identify instead of the anonymous user account, such as IUSR_<MACHINE_NAME>.
At this point, if you run the web application, you may get a permission error because the domain account doesn't have the necessary rights to specific folders, such as the web application folder and the temp folder. Using Windows Explorer, grant the domain account (or IIS_WPG group) rights to these folders. The error message should include the path of the folder you need to grant access to.
Finally, use the Report Manager to grant the necessary rights to the trusted account, such as Browser, to the required folders.

Infragistics Silverlight for Analysis Services

Teo Lachev — Wed, 09 Dec 2009 22:11:39 GMT

Don Demsak pointed out another Silverlight-based control from Infragistics, which is currently in a CTP phase. There are actually two controls: XamWebDataChart and XamWebPivotGrid. I played a bit with it and it looks great. I liked the Excel-like paradigm with the metadata pane on the right and the PivotChart and PivotTable synchronized. As I've been saying – give me a Silverlight-based Excel Pivot and I am happy.

See my previous blog about the Intelligencia for Silverlight which is another Siliverlight-based control for Analysis Services.

Analysis Services Top 10 Wish List

Teo Lachev — Mon, 07 Dec 2009 18:27:00 GMT

Analysis Services leads the OLAP server market and it should be your platform of choice for historical and trend reporting. In the spirit of the season, here is my Top 10 Wish List for Analysis Services. All items are related to regular cubes and none to PowerPivot which I am yet to try.

Improved client support – No 1 on my list has nothing to do with the server itself but with how well the Microsoft-provided clients support Analysis Services. To its credit, Microsoft continues improving the Excel OLAP support but still there are major functionality gaps from one tool to another. This is especially evident in the area of Reporting Services (see my No 1 SSRS wish list item). I am not sure if that's possible from a technical standpoint but I wish the SSAS team re-factors the query generation logic into a MDX Query Generator binary of some kind which all tools can share to produce the same results consistently.
Focus on UDM (aka Corporate BI) – Although I see some useful scenarios for self-serviced OLAP with PowerPivot (previously known as Gemini), for various reasons I believe the focus should stay on UDM which now Microsoft refers to as Corporate BI. First, the business problems that I solve with UDM exceed by far Gemini's capabilities. Second, if not a single source for reporting and a single version of the truth, I believe UDM should handle 80-90% of the reporting needs. Finally, based on my experience, data acquisition is the most difficult thing. This is why we have a dimensional model which IT would put together. But once data is cleaned and transformed, building UDM on top of it shouldn't take that long
Silverlight OLAP Viewer control - A Silverlight-based control that ships with Visual Studio to let developers embed an Excel-like Analysis Services browser into their ASP.NET applications.
Improved performance – Version 2008 has brought significant performance enhancement and SSAS performs great when aggregating regular measures. Usually, it is the calculations that will get you in trouble. So, anything that can be done to improve the server performance further will be welcome. For instance, the calculation engine executes the query on a single thread. It will be nice if a more complicated query can be parsed and executed in parallel.
MDX Query Analyzer – Related to performance, it will be great if we finally get a graphical query analyzer (a-la the T-SQL query analyzer) with hints about how to improve the query execution.
Memory-bound partitions – Again related to performance, it will be great if you could configure a partition to be memory-bound for super-fast access, as I mentioned in this blog.
Detail reporting – I wish Analysis Services improves the ability to report on detail level. For example, it will nice to support text measures that the server returns for the lowest grain. I also want detail queries, such as a query that returns accounts for given customer to perform faster and don't give up with "The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples." error.
Resource governor – Analysis Services should expand on letting the administrator manage resources, such as memory, connections, query timeouts, etc.
Custom security – Support custom authentication that is not Windows-based to facilitate Internet connectivity perhaps similar to custom security in Reporting Services.
Modeling enhancements – OK, this is a catch-all bucket for support issues that I logged and which were declared as "by design" and "future enhancements" , such as aggregatable Many-to-Many relationships, fixing subselect issues, fixing exclusion filter with custom operator issue.

Happy holidays!

Reporting Services Top 10 Wish List

Teo Lachev — Mon, 07 Dec 2009 13:55:00 GMT

Reporting Services has gone a long way for the past five years and it's getting better. In the spirit of the season, here is my Reporting Services Top 10 Wish List revised from three years ago. This list is based on my work, so your priorities may differ.

Improved Analysis Services integration – Since its debut in SSRS 2005, the MDX query designer hasn't changed. R2 continues the trend, so no hope until at least 2011, that's six years! Yet, the MDX query designer has plenty of deficiencies and bugs to be addressed, such query schema limitations, poor support for parent-child hierarchies, parameter limitations, and so on. I use SSAS heavily in my real-life projects and have to battle these deficiencies on a daily basis. I think it's time for Microsoft to poll customers and MVPs for feedback and improve the integration with SSAS.
Eventing model – Although SSRS 2008 introduced a Report Definition Customization Extension (RDCE) that lets you change the report definition when the report requested, this extensibility mechanism was added as an afterthought and it's somewhat kludgy. What's really needed is server-side events, such as OnReportRender, OnPrint, OnParameterLoad, etc., similar to the ASP.NET programming model. Imagine the flexibility you will have as a report author if the server raises events for various stages of report processing and passes the report definition to let you evaluate conditions and change collections and RDL on the fly.
Supported RDL Object Model – After promising an RDL object model at TechEd 2007, SSRS 2008 brought an unsupported version but R2 is on its way to "undo" it by making it publicly inaccessible. At the same time, there are many requirements that call for pre-processing report definitions. A supported RDL object model will definitely simplify this.
Silverlight-based Report Viewer – Silverlight established itself as a platform of choice for web-based development. A Sliverlight Report Viewer will be a welcome addition.
More interactive reporting – Currently, SSRS requires you to make design changes and preview them. It will be great if a future release blurs design and preview to support more interactive reporting similar to Excel PivotTable.
Dataset enhancements, such as ability to navigate dataset rows and joining datasets at report level.
More user-friendly ad-hoc reporting – Report Builder 2.0/3.0 is one my favorite features that SSRS 2008/R2 brought in. However, some end-user oriented features from Report Builder 1.0 got "lost" along the way. This item is about these features, such as making it easier for the end user to filter report data. When connected to SSAS, for example, SSRS should be as user-friendly as Excel.
ADO.NET dataset binding for server reports – I keep on asking for this feature and I still think it could be very useful to be able to pass ADO.NET datasets to published reports without having to build a custom dataset extension.
Report styling – Ability to style and skin reports.
Decoupling the Report Builder Designer and exposing it as a reusable .NET control which can be embedded in applications.

Happy holidays!

Microsoft Live Labs Pivot

Teo Lachev — Wed, 02 Dec 2009 20:39:45 GMT

In case you've missed this, the Pivot era has begun. After Excel PivotTable and PivotChart, we'll have PowerPivot in SQL Server 2008 R2. But Pivot evolves… A co-worker showed me today a glimpse of the Pivot future which I guess is the Microsoft Live Labs Pivot. Since grids and charts are not cool anymore we now have pictures and animation. It's hard for me to understand at this point how this would apply to Business Intelligence but the Silverlight app with all these pictures sure looks catchy.

Long live Pivot!

Expert Cube Development with Analysis Services 2008

Teo Lachev — Wed, 02 Dec 2009 14:00:00 GMT

Now that I've got my copy and read it, I can say a few things about the book Expert Cube Development with Microsoft SQL Server 2008 Analysis Services by Chris Webb, Alberto Ferrari, and Marco Russo. As mentioned in another post, the authors (all MVPs and prominent SSAS experts) don't disappoint and you won't go wrong picking up this book. The book is somewhat small in size (330 pages) but big in wisdom and best practices. It also includes plenty of references to external resources and blogs that the authors and other SSAS experts have written when you need further understanding of the topics discussed. Make no mistake though, this is not a step-by-step book and it doesn't target novice users. As its title suggests, the book is geared toward more advanced users who have a few years of SSAS experience under their belt and are thirsty for more insightful knowledge.

Chapter 1 Designing the Data Warehouse for Analysis Services

The first and most important step for implementing a successful Analysis Services solution has nothing to do with Analysis Services at all. Its success depends on a solid schema design on which you'll build the cube so the book is right on target to hit this topic first. Chapter 1 discusses common schema modeling challenges and provides plenty of tips to tackle them. One grudge I have here is about the following sentence "Beware of the temptation to avoid building a data warehouse and data marts" which may leave the reader with the impression that a data mart is a must. In fact, call me lazy but the first thing that I'd explore when starting a new SSAS project is if it's possible to avoid a separate database. While a classic OLAP solution in many cases will require a new database (whatever you call it a data warehouse or a data mart) and the whole ETL enchilada that this entails, you may need to build a cube on top of simpler schemas, such on top of a DSS (decision support system) schema. In this case, I would explore the option to "dimensionalize" the source schema with a layer of SQL views. As with everything else, the keep-it-simple principle works for me.

Chapter 2 Basic Dimensions and Cubes

This chapter discusses the groundwork required to set up the raw dimensional model. This includes working with data sources, data source views, cubes, and dimensions. My favorite part of this chapter was the attribute design and setting up attribute relations. This often overlooked aspect of SSAS development is crucial for achieving good performance and the book explains why.

Chapter 3 Designing More Complex Dimensions

Next, the book explains how to handle more complex dimension schemas and hierarchies. This includes parent-child, ragged, slowly-changing, referenced, fact, and junk dimensions. The authors' real-life experience shows through. About junk dimensions, my preference is to avoid them wherever possible for performance reasons. Instead, I'd explore the option to keep the low-cardinality attributes in the fact table and use report or URL actions to view them. In my opinion, SSAS is not there yet as far as reporting on details.

Chapter 4 Measures and Measure Groups

The whole purpose of building a cube is to aggregate measures. Analysis Services supports various aggregation functions and the book provides essential coverage. You will learn how to implement semi-additive measures if you cannot afford the Enterprise Edition of SQL Server. Custom aggregation by using unary operators and custom member formulas are presented too. Then the book discusses more complex relationship types, including different grains, linked dimensions and measure groups, role-playing dimensions and fact relationships.

Chapter 5 Adding Transactional Data

Although most users would be interested in aggregated data, sometimes users may need to see the level of details behind a cell. The book explains how you can do this by using drillthrough and actions. Drillthrough options are covered in great details. Excellent coverage is provided for many-to-many relationships as well.

Chapter 6 Adding Calculations to the Cube

Cubes usually include business calculations in the form of MDX expressions. Chapter 6 gives lays the foundation for using MDX. It provides practical examples for calculated members, including YTD, ratios, growths, same period last previous year, moving averages, and ranks. Handling relative dates is a common business requirement. For example, I personally had to implement two custom types of relative dates as explained in this blog. The book provides a great example for implementing a custom calculation dimension for implementing more flexible relative dates that the BI Wizard provides. Finally, the chapter discusses static and dynamic named sets.

Chapter 7 Adding Currency Conversion

I consider myself lucky for never having to support international users because of the complexities surrounding the issue. If you have to, SSAS gives you the necessary features to tackle various localization requirements. This chapter walks you through the details of implementing currency conversion using the BI wizard, measure expressions, and writeback. My advice is to avoid writeback due to its limitations. Instead, consider an URL action that navigates the user to a custom web page for handing data changes and proactive caching for automatically updating the cube.

Chapter 8 Query Performance Tuning

SSAS bends backwards to provide great performance. You can help by following best practices for cube design. This chapter teaches you how to implement partitions and aggregations. I like the sections for monitoring partition and aggregation usage and building aggregations manually. The chapter presents useful tips to diagnose and resolve performance issues, such as by using the MDX Studio, named sets, calculated members, and caching.

Chapter 9 Securing the Cube

Chances are you will need to provide restricted access to the cube for groups of users. This chapter shows you how to implement and test roles. It covers cell and dimension security. It presents solutions for implementing custom dimension data security which are similar to the ones I discussed in this article.

Chapter 9 Productionization

Your SSAS solution is ready and it's time to see daylight. This chapter covers the tasks associated with deploying and managing your SSAS solution in production environment. These include managing and generating partitions and processing. I liked the coverage of different processing options.

Chapter 10 Monitoring Cube Performance and Usage

As an administrator, your job doesn't end once the solution is deployed. You need to monitor it on a daily basis to ensure that it performs optimally. The last chapter explains different server monitoring techniques, such as performance counters, tracing, Resource Monitor, SQL Profiler, and using Dynamic Management Views (DMVs). I personally liked the memory management tips.

In summary, I highly recommend this book to anyone who has several years of experience in SSAS development and need a compass check to make sure that he follows the right track. Kudos to the authors for taking time to write it!

SharePoint List Data Extension

Teo Lachev — Tue, 24 Nov 2009 14:18:51 GMT

R2 brings several SharePoint enhancements, including:

Support for multiple SharePoint zones – Previously, SSRS supported access from SharePoint URLs in the default zone, as I explained before.
Support for the SharePoint Universal Logging service - Reporting Services introduced a new server interface called IRSSetUserToken. When this interface is implemented, the report server calls the UserToken Set property on this interface and passes the SharePoint user token to the data extension.
Scripting with the rs utility - The rs utility now supports servers configured in SharePoint integrated mode. This lets you create scripts that automate management tasks that target report servers in SharePoint mode.
New SharePoint List Data Extension – Lets you report from SharePoint lists

The last on the list, the new SharePoint List data extension, probably picked up your interest and deserves more explanation. Previously, if you had to use the XML Data Provider to call the SharePoint web service if you wanted to report off SharePoint lists. The new extension simplifies this by supporting SharePoint lists natively. But before you get too excited about it, I have to dampen your enthusiasm by mentioning two limitations:

The extension supports querying a single list only.
It doesn't support folders if the list organizes items in folders.

The SharePoint List data extension is supported with both modes – native and SharePoint integrated. It supports Windows SharePoint Services 3.0 and 2010, as well as MOSS. Next, I'll demonstrate the SharePoint List extension by setting up a dataset that retrieves items from the Issues list of a SharePoint site. The SharePoint list extension is available both in the BIDS Report Designer and Report Builder 3.0. For the purposes of this demo, I'll use the BIDS Report Designer.

Configuring the data source

Start by setting up the data source as you would normally do when you author a report.

Set up a data source that uses the Microsoft SharePoint List provider.
In the Connection String field, enter the URL of the SharePoint site that has the list you want to query.
Switch to the Credentials tab and select the Use Windows Authentication option. Click OK to close the Data Source Properties dialog.

Configuring the dataset

Once the data source is in place, the next steps will be to set up a dataset. The SharePoint List data extension supports a graphical query designer that lets you select and filter a list.

Add a new dataset that uses the data source you've just created. On the Dataset Properties, click the Query Designer button.

Report Designer loads the graphical query designer. The SharePoint Lists pane shows all lists in the library. You can check a list to get all fields of the list or select individual fields. You can hover on a field to see additional properties as tooltips, such as Name, Identifier, Field Type and Hidden properties.

You can also filter the list. In this case, the Issues list is associated with a My Issues view. Since I don't have any issues assigned to me, I had to overwrite the list filter by supplying a non-empty query filter. For demo purposes, I decided to set up a filter where the Title column contains the word "parameter" and the Modified date is after 10/1/2009.

Click the Run Query button to execute the query and see the data and the OK button to close the Query Designer and generate the dataset.

In case you are curious what the actual query looks like, click the Edit As Text query button. If you want to get all fields, the query can omit the ViewFields element.

<ListName>Issues</ListName>

. . .

</ViewFields>

<Query>

<Where>

<And>

<Value Type="Text">parameter</Value>

</Contains>

<Gt>

</Gt>

</And>

</Where>

</Query>

</RSSharePointList>

The SharePoint List data extension is a new R2 feature that simplifies the process of authoring reports from SharePoint lists. Although it doesn't support all features of third-party data extensions for SharePoint, it's a welcome enhancement that lets you retrieve and filter items from a single SharePoint list.

Report Parts

Teo Lachev — Tue, 24 Nov 2009 01:41:00 GMT

The major new R2 feature that debuts in CTP 3 (November CTP) of SQL Server 2008 R2 Reporting Services is report parts. A report part is a fragment of a report definition that you can publish to the report server to facilitate best practices and reuse. Just like a developer can refactor and share some piece of code, the report author can re-factor a portion of a report and publish it to the server so that end users can use it as it-is. The following report items can be published as report parts:

Charts
Gauges
Images
Maps
Parameters
Rectangles
Tables
Matrices
Lists

A notable exception to this list is page headers and footers which are probably the most likely candidates for reuse but at this point they didn't make the list. It's important to note that if a data region uses a report-specific dataset (not a shared dataset), publishing the region as a report part will publish its dependent dataset. Report parts can best understood by example, so let's walk through the workflow of publishing, using, and changing a report part.

Publishing report parts

You can use the BIDS Report Designer or Report Builder 3.0 to publish a report part. The following steps assume you use Report Designer. Let's say I decided that the tablix with sparklines, which I discussed in a previous blog, is a darn good and it should be promoted as a published report part that other users could enjoy for educational or other purposes.

Start by setting the server folder where report parts will be published on deploy. In the project properties, set the TargetReportPartFolder (a new deployment setting) to the name of the server folder, such as Report Parts, or a SharePoint library URL for SharePoint integrated mode.
With the report open in design mode, expand the Report main menu and click Publish Report Parts. If you use Report Builder 3.0, click the Report Builder 3.0 button (the round button in the topmost left area) and select Publish Report Parts.
The Publish Report Parts dialog box shows the items on the report that are supported as report parts. Check the Tablix report item.
You can rename a report part by double-clicking its name and typing the new name in place. Click the arrow next to it to expand the Tablix section. Enter a description, such as "A table with sparklines" and click OK to close the dialog box.

5. So far, you have configured which items will be promoted to report parts but you haven't actually published them. To do so, you need to publish the containing report. In Solution Explorer, right-click the report and click Publish.

At this point, you should see messages in the BIDS Output Window informing that the report and the report parts you defined are being published.

Deploying report part 'Tablix' to '/Report Parts'...

Deploying report parts complete -- 1 succeeded, 0 failed, 0 skipped

6. Open Report Manager (or SharePoint) and navigate to the Report Parts folder. You should see the Tablix report part.

If you go to its properties and download its definition, you will see that the file name has an *.rsc extension and that the report part is represented by a ComponentItem element in RDL. Notice also that the report part definition includes all datasets that report part uses. Once the report part is published, you can manage it just like you can manage a published report. For example, you can change its name, description, or security settings.

Using report parts

End users can consume published report parts. Notice that I said "end users" because more than likely this will be the most common scenario. In fact, the BIDS Report Designer doesn't support referencing report parts, only publishing them. The Report Builder 3.0 has been extended to support this feature.

Open Report Builder 3.0 and create a blank report.
Click the Report Builder 3.0 button, click Options and verify that the report server URL is set.
Click the Report Part Gallery tab of the Properties window. Click the Search button to show all report parts that you have access to.
Select the Tablix report part and notice that Report Builder 3.0 shows its properties below.
Drag the Tablix report part and drop it on the report canvas.

Report Builder 3.0 adds the tablix region, the dataset is bound to and report parameters. From here, the report part takes a life of its own. You can make changes to the report as needed including changes to the report part elements. The layout changes you make are not reflected to the published part. If you want to re-publish the changed report part, you can click the Report Builder button and click Publish Report Parts. This will prompt you if you want to re-publish with default settings or review and make changes which will bring you to the Publish Report Parts dialog box.

Changing report parts

The original report part author or another user which appropriate permissions can make changes and re-publish a report part. End users can check if a report part has changed and choose to accept or not the changes. To do this, they have to poll the server for updates. Currently, there is no way for the server to push the changes to all reports who consume the report part in order to force the changes.

In Report Designer or Report Builder 3.0, open the report that has the Tablix report part. Make changes to the tablix region and deploy the report to re-publish the report part.
In Report Builder 3.0, open a report that uses that report part.
Click the Report Builder 3.0 button and click Check for Updates. Notice that Report Builder 3.0 detects the changes and displays a message that it has found one changed part.
Click the View Updates button to see what report parts have changed. In the Update Report Parts dialog box, select the Tablix report part and click the Update button to refresh it.

Report Builder 3.0 replaces the tablix region the published version. However, Report Builder 3.0 keeps the existing dataset and parameters, even if they are not changed, and appends new datasets and parameters each time the part is refreshed. So, if you have a dataset called Main, refreshing a part will add a new dataset called Main2 and bind the refreshed part to Main2.

Report parts are a new feature of SSRS R2. They promote consistency and reuse by letting you re-factor sections of a report and make them available for public consumption.

Cumulative Update Package 5 for SQL Server 2008 Service Pack 1

Teo Lachev — Fri, 20 Nov 2009 17:58:00 GMT

Microsoft released Cumulative Update Package 5 for SQL Server 2008 Service Pack 1 (build 10.00.2746). Among other things, if fixes an SSRS issue with printing and conditional visibility of recursive groups which I reported.

Sparklines and Data Bars

Teo Lachev — Fri, 20 Nov 2009 13:24:00 GMT

Besides indicators, R2 adds sparklines and data bars to the arsenal of data visualization features. Sparklines are small graphics embedded in a context of words, numbers, and images. They help end users visualize simple trends. You could implement sparklines and simple data bars in the previous versions of Reporting Services but you had to go through many steps to strip down the chart or gauge regions. R2 makes the process much simpler. Let's see what it takes to author a sparkline report with R2 that is similar to the Sparklines report included in my book source code. (Please visit the site to view this media)

This report helps the user visualize the performance of the Adventure Works sales persons. For each person, the report shows the employee's trend based on the reseller sales made over four quarters and his performance ratio which is defined as the actual sales made by the employee divided by his sales quota. The report gets the data from the Adventure Works 2008 Analysis Services cube.

Configuring Sparklines

In Reporting Services, a sparkline is a scaled-down chart region. Configuring a sparkline takes a few simple steps.

1. Right-click on a tablix cell and click Insert ð Sparkline.

2. In the Select Sparkline Type dialog box that follows, select the Line sparkline type.

3. Double-click the sparkline region to open the Chart Data panel which is another new design feature in R2.

4. Click the plus sign in the Values pane and select the Reseller_Sales_Amount to configure the sparkline series.

5. Click the plus sign in the Category Groups pane to add the Category_Quarter_of_Year field to configure the category groups.

That's it! I've made a few more minor adjustments to finalize the sparkline, such as removing the chart border, setting the chart background for the "green bar" effect and increasing the line width.

Configuring Data Bars

To demonstrate data bars, I implemented the employee's performance ratio using a data bar region. Similar to a sparkline, a data bar is a simplified chart.

Right-click on a cell and click Insert ð Data Bar. I actually, configured the data bar outside tablix first by embedding it inside the rectangle and copying and pasting it inside the tablix cell. I did that to make the bar narrower so it doesn't occupy the entire cell height.
On the Select Data Bar Type, leave the default Bar type selected.

3. In the Chart Data panel, click the plus sign in the Value pane to add the PerformanceRatio field and set its aggregation function to Avg.

That's pretty much it to set up the initial data bar although I had to go through a few more steps to configure the bar labels and appearance.

Although not revolutionary features, Indicators, sparklines and data bars will save you time when you need to enhance your report with simple graphics. Sean Boon from the Reporting Services team has written an interesting blog post that demonstrates more sparkline features to implement a win-loss sparkline.

Indicators

Teo Lachev — Thu, 19 Nov 2009 02:47:00 GMT

Often, reports need to show images that represent discrete values, such as an image that shows a KPI status. Previously, you didn't have another option but to use images that are dynamically changed based on the field value. Indicators, a new R2 feature, make this much simpler. In Reporting Services, an indicator is a simplified gauge region that lets you associate images with states. Although you can use indicators as stand-alone regions, you would typically nest them in a tablix, as the following report (Please visit the site to view this media) demonstrates. The right-most column of this report shows the Gross Margin KPI status as an indicator.

Authoring this report takes a few simple steps:

Add a dataset that requests the Status property of the Financial Gross Profit Margin KPI from the Adventure Works 2008 Analysis Services cube grouped by Year and Quarter.
Use a tablix region to show the KPI status with Year and Quarter row groups.
Right-click the cell in an empty column and click Insert ð Indicator.
In the Select Indicator Type dialog that follows, select the indicator type. In this case, I chose the default directional type.

Once the indicator region is added to tablix, right-click on it and click Indicator Properties. In the Indicator Properties dialog box, select the Value and States tab.
Since in this case I display numeric values and not percentages, change the States Measurement Unit drop-down list to Numeric.

By default, the default directional indicator lets you defined three states, as shown below. However, you can add or delete states and use custom images if needed. In my case, I defined a range from -1 to 0.-99 for underperformance, 0 - 0.99 as acceptable performance, and 1 as good performance.

Indicators are simple but very useful enhancement to Reporting Services. They lets you visualize discrete values on reports.

Pagination and Rendering Enhancements

Teo Lachev — Tue, 17 Nov 2009 13:52:16 GMT

R2 brings more control over pagination and rendering. A question that pops every now and then on the discussion list is how to name Excel worksheets for each instance of a row group. This scenario wasn't supported before R2. In R2, there are addition page break options, as shown in the screenshot below. One of them is PageName.

In this case, I set the PageName property of the ProductCategory row group to the Category field. Then, I set up the BreakLocation of the ProductCategory row group to break when the product category changes. The screenshot below shows what the exported to Excel report looks like. As you can see, each worksheet is now named. If the row group spills on the next page, a new worksheet is added with the same name.

Another welcome enhancement is the ability to reset page numbering. Suppose you have a master report that includes a subreport and you want to reset the page numbering before the subreport is rendered. This is another commonly requested scenario which wasn't supported before R2. In the screenshot below, I have a report package that includes two subreports. I want to reset the page number after the first subreport renders. Since the subreport region doesn't have page break properties, I enclosed the first subreport in a rectangle and set the rectangle PageBreak ð BreakLocation to End and PageBreak ð ResetPageNumber to True. In the page footer, I added Globals!PageNumber of Globals!OverallTotalPages. Since you can reset the page number, the RS team has added also OverallPageNumber and OverallTotalPages built-in fields to the Globals collection. Given this setup, the report will increase the page number for each page in the Company Sales 2008 report. Once Company Sales 2008 has rendered, the server will reset the page number but it will show the overall number of pages.

Notice that there is also a Disabled page break property. Suppose that when exported to other formats you want your report to break on each row group instance but when exported to Excel you want the entire report to export to a single worksheet. R2 has added two additional built-in fields to the Globals collection, RenderFormat.Name and RenderFormat.IsInteractive, that lets you support such scenarios. In this case, I had to define the following expression for the PageBreak ð Disabled property:

=Iif(Globals!RenderFormat.Name="EXCEL", True, False)

If the user exports the report to Excel, the expression turns on the Disabled property and no page breaks are generated. However, if the user exports the report to another format, the report breaks on each instance of the row group.

Finally, to round up the rendering enhancements, you have now the option to rotate text 270 degrees as shown in the report below. This can be easily achieved by setting the textbox WritingMode property to Rotate270. You probably would want to set also TextAlign to Center and VerticalAlign to Middle.

Shared Datasets

Teo Lachev — Mon, 16 Nov 2009 14:15:24 GMT

When the Reporting Service team asked my opinion about shared datasets, a new feature in the forthcoming SQL Server Reporting Services R2, I was somewhat skeptical. I preferred them to focus on more important in my mind features, such as the ability to join datasets at report level. But the more I look at the way shared datasets got implemented, the more real-life scenarios I think may benefit from this enhancement.

Think of a shared dataset a hybrid between a shared data source and report execution. Similar to a shared data source, a shared dataset is a report dataset that can be managed independently and shared among reports. A shared dataset must use a shared data source. The shared datasets can be parameterized and reports that use it can pass parameters to it. Similar to report executions, a shared dataset can also be cached and refreshed on a schedule. In my opinion, there are two main scenarios where shared datasets can be useful:

Easier maintenance – Suppose you have a dataset that you can re-use across reports, such as a dataset that populates the parameter available values. You want all reports to reuse the dataset and pick up the dataset query changes automatically.
Improved report performance – You may have query that takes very long to execute. You want to execute the query in an unattended mode, such as outside working hours, perhaps for different set of parameters, and cache the dataset for a specific duration.

Creating Shared Datasets

Creating a shared dataset is easy. Both BIDS Report Designer and Report Builder 3.0 are capable of creating shared datasets. In Report Designer, you right-click on the new Shared Datasets folder in Solution Explorer, and click Add New Dataset. In Report Builder 3.0, you click the Create Dataset option on the main Create Report or Dataset popup screen.

You can easily convert a report-specific dataset to a shared dataset. Just right-click any dataset in Report Data Window and click Convert to Shared Dataset. Consequently, Report Designer will re-factor the dataset as a shared and it will add its definition to as an *.rsd file to the Shared Datasets folder in Solution Explorer. In the screenshot below, I have converted the EmployeeSalesDetails dataset in the Employee Sales Summary 2008 report to a shared dataset. Notice that the EmployeeSalesDetails dataset reference inside the report has a special icon.

As I mentioned in a previous post, the project properties dialog get enhanced to support new deployment settings that are specific to shared datasets. Specifically, the OverwriteDatasets setting specifies if the shared dataset definition will overwritten on deployment if it exists and the TargetDatasetFolder specified in which folder the dataset definitions will be deployed to.

Once the dataset is configured as shared, you can set up its properties, which include the dataset query, fields, and filters. Then, you can configure the dataset reference inside the report to pass parameters to the shared dataset or to have its own filters. This is similar to how to you configure a subreport. What if you want to use row-level security and you need to pass the user identify to the dataset query (User!UserID)? Just add a query parameter to the shared dataset, such as LoginID, and configure the dataset reference inside the report to pass User!UserID as a parameter to the shared dataset.

Managing Shared Datasets

Once the shared dataset is deployed, it can be managed just like any other published item using Report Manager or SharePoint. Common management tasks include setting the data source reference, caching, and security. The most interesting of these is caching. Just like report executions, you can cache a shared dataset. When a shared dataset is configured for caching, the report server will cache a dataset copy for each parameter combination. The same restrictions apply as with caching report executions. Specifically, the data source cannot use Windows Security or Prompt for Credentials authentication options. You can configure cache expiration options. For example, if data latency of 30 minutes is acceptable, you can configure the dataset cache to expire in 30 minutes.

Let's say you a dataset query that takes very long to execute. You can set up a cache refresh plan to warm up the dataset cache on a set schedule. Cache refresh plans are also a new R2 feature. Previously, you pre-execute reports by creating a subscription using the NULL delivery provider. Moving to R2, you don't need to use the NULL provider anymore and you can refresh shared datasets independently from reports. In the process of setting up a cache refresh plan, you need to specify an item-specific or shared schedule and default values for each parameter, just like you would do with snapshot caching or subscriptions.

As you can see, shared datasets can help you implement some interesting scenarios when you need to reduce the management effort or improve the report performance.

Not All Calculated Members Are Born Equal

Teo Lachev — Fri, 13 Nov 2009 14:30:56 GMT

An interesting issue popped up yesterday regarding the calculated member syntax. I had to multiply negative amounts in a financial cube for certain account categories that had negative amounts. The cube also had a Many:Many relationship between financial accounts and account groups which may be related to the issue. So, I had the following scope assignment:

CREATE MEMBER CURRENTCUBE.[Multiplier] /* Old Style*/

AS [Financial Account].[Financial Account Hierarchy].CurrentMember.Properties("Multiplier", TYPED),

VISIBLE = 0;

Scope

(

[Financial Account].[Financial Account Hierarchy].Members

);

this = [Financial Account].[Financial Account Hierarchy].CurrentMember * [Measures].[Multiplier];

End Scope;

The Multiplier calculated member returns the Multiplier property of the current account member, which could be 1 or -1 (if the account has to be negated). Then, the scope assignment overwrites all measures for the current member. As simple as it is, the assignment didn't work and the account amounts didn't get converted although the Multiplier calculated member would return the correct number. I stripped out the entire cube script to only these two script definitions but I couldn't make it to work. After some research, I came to this interesting discussion between Chris Webb and Read Jacobsen, which made me think that the issue probably has something to do with the solve order of the calculated member. As it turned out, the new style has a higher pass so the following fixed the issue:

CREATE HIDDEN [Multiplier] = [Financial Account].[Financial Account Hierarchy].CurrentMember.Properties("Multiplier", TYPED); /* New Style*/

As Chris suggested, I tend to believe that it's a bug especially given the fact that I've recently logged another nasty calculated member-related issue, where scope assignments would produce wrong results if the calculated members with the old style definitions are hidden.

Aggregates of Aggregates

Teo Lachev — Thu, 12 Nov 2009 01:54:00 GMT

Aggregates of aggregates is a new feature in SQL Server R2 Reporting Services. It can be better understood by the sample Aggregates.rdl report ((Please visit the site to view this media)). It shows sales grouped by year and quarter. The Year group subtotals use the Sum aggregation function to sum the quarter sales. The report footer total averages the year subtotals.

Prior to R2, authoring such a report would require dividing the total sales by the number of quarters, which you can obtain by using the CountRows function. You cannot just use the Avg function in the footer as the Bad Sales measure shows:

=Avg(Fields!Internet_Sales_Amount.Value)

If you do so, the Avg function would operate on the detail rows (all quarter rows) and it would produce 2,258,360. R2 lets you nest aggregate functions. The Sales footer total uses the following expression:

=Avg(Sum(Fields!Internet_Sales_Amount.Value, "Year"))

In this case, the Average function operates over the aggregated year subtotals. Note that not all aggregate functions can be nested. For example, you cannot define an aggregate over the Aggregate, RunningValue and Previous functions.

Where is the SSAS 2008 Book?

Teo Lachev — Tue, 10 Nov 2009 22:50:00 GMT

Readers have been asking about a revised edition of my Applied Microsoft Analysis Services book for version 2008. First of all, I'd like to thank all my readers who bought and liked the book. This book has been a great success and it really exceeded my expectations in any way! It has great reviews on Amazon and it has sold over 10,000 copies to date, which is not a bad sales record for a technical book. I am still supporting my readers on the book discussion list and more than likely will continue doing so for foreseeable future. At the same time, I decided not to write a revision for SSAS 2008 or R2 for two main reasons:

I just need a break from book writing. It took me several months to write the Applied Microsoft SQL Server 2008 Reporting Services and BI toolkit. I am not ready to start a new book and … face a divorce. Seriously, writing books is not easy and it takes a lot of time away from the family. And I just can't write small.
Version 2008 of Analysis Services has been an incremental and stabilization release. The theme was "don't rock the boat" since the boat was rocked big time in SSAS 2005, which was essentially a re-write of Analysis Services from ground up. The major 2008 enhancements were in the area of performance and manageability. So, hardly enough material to write a revision from a feature standpoint although I definitely have plenty of real-life experience to contribute. So, Applied Microsoft Analysis Services 2005 is still not outdated and I hope you won't go wrong reading it. Speaking of this book, since it looks like the demand for it waned in the past months (everyone jumped on the 2008 bandwagon, and this is what you should do) I'm not planning a new print. Instead, I lowered the price of the e-book version to $29.95, so the book is still available in e-book format if you decide to buy it.

Although I am not planning a new SSAS book anytime soon, I am committed to Analysis Services, which I like very much and use heavily in real-life projects. Looking forward, I am planning a new SSAS revision for SQL Server 11, which is next major release of SQL Server after R2. Meanwhile, I offer an Analysis Services 2008 training class if you are looking for instructor-led online training.

What Analysis Services 2008 books would I recommend?

Novice User

Professional Microsoft SQL Server Analysis Services 2008 with MDX – written by members of the SSAS team
Microsoft SQL Server 2008 Analysis Services Step by Step – a good introduction to Analysis Services
Microsoft SQL Server 2008 MDX Step by Step – I've heard good things about this book from my co-workers who used it to learn MDX basics
MCTS Self-Paced Training Kit (Exam 70-448): Microsoft SQL Server 2008 Business Intelligence Development and Maintenance – if you plan to take the 70-448 BI exam, we wrote this book for you. My contribution was the SSAS chapters.

Experienced User

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services – You won't go wrong with the fellow MVPs: Marco Russo, Alberto Ferrari, and Chris Webb. I reviewed this book here.
Microsoft SQL Server 2008 Analysis Services – if you really want to get under the hood, you won't get disappointed by the folks who implemented Analysis Services
MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase – Bring your MDX knowledge to the next level.

Speaking of books and writing, I have an exciting announcement which I'll make soon in another blog. Stay tuned…