Russell Christopher's Semi-Useful BI Musings

Proof positive that SSRS 2008 is superior to SSRS 2005

Cross posting again!

 http://performancepointblog.com/2008/07/proof-positive-that-ssrs-2008-is-superior-to-ssrs-2005/

SQL Reporting Services: What does that “Thread pool pressure” warning mean?

Cross-posting:

http://performancepointblog.com/2008/06/sql-reporting-services-what-does-that-thread-pool-pressure-warning-mean/

Packing up and moving to performancepointblog.com

I had $140 burning a hole in my pocket and saw a great domain name was available, so I've purchased 2 years of hosting for the next incarnation of this blog - performancepointblog.com. The content will be similar to what you've seen here, but with a heavier emphasis on PPS (I have no choice with the name of the blog, right?)

So, this'll be my last post here. If you'd like, feel free to read my inaugural post out there 

Language Packs and the PerformancePoint add-in for Excel

Here’s an interesting behavior I saw being discussed that you may run into when launching the PPS add-in for Excel when you have (or not) language packs installed:

The PerformancePoint Add-in for Excel could not be loaded. The language pack for the configured language could not be found. Please install the multilingual user interface pack for your version of Office or set the operating system locale to the same locale as that set for Office

The add-in always wants to see that the regional settings of your operating system and Office are the same. OK, good so far. The add-in also expects (I’m not sure why) those settings to use the core language, too. In other words, German (Germany) or Spanish (Spain) are good, but German (Austria) and Spanish (Peru) are not – stinks if you’re in Peru or Austria, eh?

Never fear, because there’s a workaround can you use to deal with the behavior above -  Just make sure that you have the language packs for both locales (German (Germany) and German (Austria), for example) installed on the machine. You can use http://www.microsoft.com/globaldev/reference/win2k/setup/lcid.mspx for locale-specific information, by the way.

…But what happens if the locale you want to use, like German (Austria) doesn’t even have a language pack? Well, there’s a solution for that, too:

1.       Install the language pack for your “core” language on the machine in question – German (Germany) will lay down C:\Program Files\Microsoft Office\Office12\1031

2.       Create a copy of the folder above (including all the files inside it), and then rename it to the correct LCID value (use the link above to determine what the value is). For German (Austria) we’d create C:\Program Files\Microsoft Office\Office12\3079.

At this point, you should be in pretty good shape.

Problems making web reference to ReportService2006: Unable to import operation "SetReportHistoryOptions"

Today I needed to write a bit of code to create a model on a SQL 2005 Report server which was running in Sharepoint integrated mode. Boy, what fun I had!

 After creating my web reference, I got this error:

Custom tool error: Unable to import WebService/Schema. Unable to import binding 'ReportingService2006Soap' from namespace 'http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices'. Unable to import operation 'SetReportHistoryOptions'. The XML element named 'ScheduleDefinition' from namespace 'http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices' is already present in the current scope. C:\Documents and Settings\Administrator\My Documents\Visual Studio 2005\Projects\MakeAModel\Properties\Settings.settings 1 1 MakeAModel

After much scratching of the head, grumbling, and beating of the breast, I checked our internal bug database and found that this is a known issue (there's no KB article however).

So, I held by breath, cracked open the WSDL and started deleting every element which referred to the SetReportHistoryOption operation since I didn't need this method anyway. After I saved my changes, the error message didn't go away. I shut down VS, reloaded my project, built it, and all was well in my world. I'm attaching my hacked WSDL for your viewing pleasure.

SQL Analysis Services error processing ROLAP partitions against Teradata

Today I was playing around with stacking SSAS on top of Teradata. Essentially I have AdventureWorks DW moved over into Teradata and I’m trying to build a dumbed-down version of the AdventureWorks cube against it.

Things went fairly well while my partitions were MOLAP. But when I started changing some of them to ROLAP, SSAS threw the following exception when I tried  to process:

Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used: The isolation level is not supported by this version of Teradata Database.

After a bit of playing, I found that the issue had to do with including aggregations on these partitions – you can’t. When I set the aggs to 0%, everything worked perfectly. Shortly afterwards,  a colleague forwarded me additional information on the subject, which will be included in a soon-to-be released Teradata/SSAS whitepaper:

ROLAP aggregations: An Analysis Services cube partition that uses ROLAP partition storage with the Teradata Database, must be defined with zero aggregations. This issue is discussed in Designing Partition Storage and Aggregations section. Recommendation to use AJI for performance

PerformancePoint: Why can’t I see values for my KPI’s Actual and Target metrics in a scorecard?

While working on a KPI in PPS, I found that after binding an Actual and Target to my cube I couldn’t see values for these metrics after adding the KPI to Scorecard and updating.  The indicator image these metrics showed  was “No Data”.

I broke out ProClarity to make sure that data existed, and it did.

Turns out the culprit was another Target that lived in the same KPI – I had written an MDX tuple formula for it, and fat fingered the MDX itself. Unfortunately, it looks like we’re not verifying MDX syntax, so  the UI happily accepted my “bad” MDX, which subsequently caused all data values in the KPI to die. After correcting my MDX, all was well.

Save your SSAS browser queries for re-use: new add-in

File this under "reclaim a few second of your life". If you build semi-complex pivots in the SSAS browser, you probably get annoyed when you have to rebuild them time and time again after you close the cube designer. Well, Yossi Elkayam has written the  Analysis Services Browser Views add-in which allows you to save the underlying queries so you can re-use them later. Great idea!

Moving On…

Just a quick update on my status – as of 1-May, I’ll be changing my focus a bit. For the last 7-8 years, I’ve been an Application Development Consultant in the Services organization here at Microsoft. Well, on Thursday I’ll be moving over to the Communications Sector where I’ll act as a Technical Specialist for Business Intelligence.

I’ll be working with all the familiar stuff in the SQL BI stack but will spending quite a bit more time with PerformancePoint than I used to.

…and don’t worry, I still love my Reporting Services and plan to stay busy with semi-useful posts around same.

Cheers!

Fun displaying SSRS (integrated) reports using PerformancePoint SQL Server Report viewer

Disclaimer: the post below was written after 24+ hours traveling to India - I was pretty much (very much) out of my mind at the time...

4. Click the link which represents your report library (“ReportsLibrary” for me).

Sheesh – could we have made this any more difficult?

What I like about SQL Server 2008 Reporting Services: The Configuration Manager

I’ve been slacking a little bit in terms of blogging, but now that CTP6 is out with a bunch of new stuff I can “officially” talk about, I suspect I’ll be a bit more active.

Today while building a VPC image of CTP6, it occurred to me how small improvements really add up. For example, take the Configuration Manager for SSRS 2008. Don’t get me wrong, version 2005 was just fine. But this new one – nice – you can tell someone took some time to figure out how to make life easier for admins.

The whole tool is more wizard-driven and less likely to confused first-time users. For example, the configure database task is substantially more intuitive than it used to be. When teaching classes, I saw users regularly create the ReportServer database, but then forget to actually “join” it. The wizard doesn’t allow you to make this mistake (at least not easily).

The text associated with each option in the tool is thoughtful and concise. Before, you actually had to understand the product already or RTFM to really know what you were doing in Config Manager. Now, everything you need is already right there: easy!

So anyway, I like the Configuration Manager – attention to detail on this fairly minor piece of functionality is a good sign in terms of what the rest of the product does!

SSRS File Share provider in a Workgroup

Today I was a lurker in a "news to me" conversation about the SSRS file share delivery provider – thought I’d pass this interesting (to me) tidbit along.

When using the file share extension on a box which was NOT part of a domain, the user kept on getting the following error when trying to process a subscription:

Failure writing file: A logon error occurred when attempting to access the file share. The user account or password is not valid.

The user did standard testing and found that the file share was accessible from the console using the same username/password, etc.

Turns out that file share delivery provider doesn’t currently support challenge-response authentication. Instead, It impersonates the user before making the connection to the underlying machine. Because of this, in a workgroup environment, the file share deliveries will not work.

Thanks to Neeraja Divakaruni!

SQL Server 2-D Matrix Builder available on CodePlex

If you need to build a very complex query that returns a matrix with dynamic columns and rows from T-SQL,  you might want to take a look at this new project posted on CodePlex :

SQL Server 2-D Matrix Builder

http://www.codeplex.com/SQL2DMatrixBuilder

This project is basically a SQLCLR stored procedure called TheMatrix, as well as a SQLCLR UDT called QueryBuilder that allow you to describe all the queries you want to call to build the axis and the content of the matrix you need to retrieve back.

Check the CodePlex site for more information : http://www.codeplex.com/SQL2DMatrixBuilder

RSPreviewPolicy.config: Change in behavior for BIDS 2008?

For those who play around with custom assemblies in SQL Reporting Services, it’s pretty common knowledge that it is not necessary to add a <CodeGroup> element for your custom assembly in RSPreviewPolicy.config in order to use it while previewing your report in BIDS. Essentially, in 2005 if you drop a custom assembly into \PrivateAssemblies (and maybe elsewhere, I haven’t checked), DevEnv.exe gives it FullTrust in BIDS preview even if you don’t add a <CodeGroup> for it like BOL tells you to. I have to admit that I tell all my customers to make sure to add this entry as a best practice, but I rarely do it myself :)

Well, it looks like that habit is about to change. I was trying to reproduce a problem in 2008 for a customer today and they use custom assemblies. I did the standard rssrvpolicy.config mods on my server, dropped the assemblies in question into ReportServer\Bin and \PrivateAssemblies and tried to preview the report in BIDS. I got this error message:

Failed to load expression host assembly. Details: The type initializer for “Some.Assembly.Name.Is.Here” threw an exception.

I spent a good 45 minutes double-checking my work and then for the hell of it went ahead and added a CodeGroup for each of the 3 assemblies I was working with. My report immediately started previewing correctly in BIDS.

So, unless I’m just working with an odd build of SSRS 2008, it looks like we’re all going to have to do a little bit more work in the future when working with custom assemblies.

What does an exported PerformancePoint scorecard look like in Reporting Services report designer?

I was curious what would happen when I opened an RDL file created by PerformancePoint in Report Designer, so I tried it.

Here is what I discovered:

1.       For whatever reason, PPS exports RDL using the SSRS 2000 RDL spec (xmlns=http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition). When you attempt to open the report up with BIDS, you’ll be prompted to upgrade it.

2.       If you click the Data tab, you’ll hit the error: “The designer extension ScorecardDPE could not be loaded. Check the configuration file RSReportDesigner.config”

I found there were two ways to (sort of) solve the second problem.

The first workaround is rather old-fashioned. Just keep clicking OK 3-4 times and the error message eventually gives up. Then, switch the query designer into generic mode and dismiss the error message you get there 3-4 times. After that you can run the query with the “!” button, and things work.

If you want to be a little bit more creative, go ahead and edit (carefully, of course) RSReportDesigner.Config. You’ll find the file in C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies, and you’ll want to place the following element in the <Designer> block of the file:

Extension Name="ScorecardDPE" Type="Microsoft.ReportingServices.QueryDesigners.VDTQueryDesigner,Microsoft.ReportingServices.QueryDesigners" />

If you go this route, you’ll STILL get an error (“Format of the initialization string does not confirm to the specification starting at index 0”), but you only have to dismiss it once at which point you can go into the generic query designer mode (without additional generic query designer errors to get rid of).

Both solutions aren’t perfect, but then we don’t live in a perfect world, eh?

In terms of modifying the report layout itself, you can change the header colors from “Office 2007 Blue” to whatever you’d like, remove any of the Value, Goal/Status, Trend columns, etc.