Trying to Communicate

/0 Comments/in /by

Visual Studio 2008 embraces the exciting new world of Windows Communication Foundation (WCF) for communicating with services. However, pitfalls await the unwary. I’ve recently tackled invoking the Reporting Services Web service with WCF and I want to share my findings.

  1. The Visual Studio Add Web Reference menu has been renamed to Add Service Reference to denote that WCF can communicate with much more than Web services, including probably my Zune device. Although the dialog has somewhat changed, you will be find your way to generate the proxy.
  2. What’s more surprising is that the auto-generated proxy methods now have somewhat different signatures.

For example, the SQL Server Books Online has the following signature of the Reporting Services GetExecution Options API.

public ExecutionSettingEnum GetExecutionOptions (string Report,out ScheduleDefinitionOrReference Item);

Yet, WCF generates the following signature:

public ServerInfoHeader GetExecutionOptions(string Report, out ExecutionSettingEnum executionOption, out ScheduleDefinitionOrReference Item);

So, the returned value becomes an out parameter while ServerInfoHeader becomes a returned value. I am not sure how WCF figures this out. Does it mean that now the documentation should show both the 2.0 and WCF signatures?

  1. The second surprise wave hit me when I was trying to figure out a way to pass my credentials to the Web service. This, of course, will probably be one of the first things you need to do to invoke an Intranet service.

In the good ol’ 2.0 days, impersonating the user takes a single line of code.

rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

How do we this in the shiny new WCF world? Strangely, the Visual Studio help says little about this. I came across some bizarre examples of declaring HTTP transports that made my head spin. In a sheer stroke of luck, I managed to figure out the right changes in the application config file (yes, now we have declarative settings).

<security mode=”TransportCredentialOnly“>

<transport clientCredentialType=”Ntlm” proxyCredentialType=”None” realm=”” />

<message clientCredentialType=”UserName” algorithmSuite=”Default” />

</security>

Wait! We need to tell WCF also that is OK to impersonate the user.

ReportingService2005SoapClient rs = new ReportingService2005SoapClient();

rs.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

At this point, I felt like upgrading my house only to find that I have to enter through the chimney. Upgrading to a new technology shouldn’t complicate things unnecessarily. I promptly switched back to the 2.0 style of programming. Luckily, they kept the old Add Web Reference button from the advanced settings of the Add Service Reference dialog.

I guess they were right. You can’t teach an old dog new tricks…

Happy holidays!

Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques Whitepaper Available

/0 Comments/in /by

The Microsoft CAT team has released a new whitepaper Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques.

“Many-to-many dimension relationships in SQL Server 2005 Analysis Services (SSAS) enable you to easily model complex source schemas and provide great analytical capabilities. This capability frequently comes with a substantial cost in query performance due to the runtime join required by Analysis Services to resolve many-to-many queries. This best practices white paper discusses three many-to-many query performance optimization techniques, including how to implement them, and the performance testing results for each technique. It demonstrates that optimizing many-to-many relationships by compressing the common relationships between the many-to-many dimension and the data measure group, and then defining aggregations on both the data measure group and the intermediate measure group yields the best query performance. The results show dramatic improvement in the performance of many-to-many queries as the reduction in size of the intermediate measure group increases. Test results indicate that the greater the amount of compression, the greater the performance benefits—and that these benefits persist as additional fact data is added to the main fact table (and into the data measure group).”

Brian Welcker Leaving SSRS

/0 Comments/in /by

As he posted here, Brian Welcker (Group Program Manager of Reporting Services) is leaving the Reporting Services team and moving to the Microsoft Healthcare Solutions Group. Brian did so much to build and promote Reporting Services. SSRS wouldn’t have been the same if it wasn’t for Brian. The technical community (myself included) will surely miss him.

Let’s wish Brian good luck with his new career!

Relational Guide to Monitoring and Analyzing with Microsoft Office PerformancePoint Server 2007

/0 Comments/in /by

As you know, business scorecards are the latest BI craze. Nick Barclay was kind enough to send me a copy of his new book Monitoring and Analyzing with Microsoft Office PerformancePoint Server 2007 by Rational Press which he co-authored with a co-worker and friend Adrian Downes. This is one of these relatively-small and very practical books which helps you hit the ground running quickly. As its name suggests, the book focuses only on the monitoring and analyzing piece of PerformancePoint which was previously known as Business Scorecard Manager. Nick and Adrian wrote another book, the Rational Guide to Planning with Microsoft Office PerformancePoint Server 2007, which covers the Biz# portion of the product.

I liked the author style and the practical examples included in the book. The authors show you how to build scorecards from a variety of data sources and deploy them to SharePoint or Reporting Services reports. I didn’t know that you can create a Visio strategy map and hook it to the scorecard. Too bad that the ProClarity stuff didn’t get integrated into BSM. I guess we have to wait for another release to get the cool decomposition tree when you click on a KPI. Until then, Visio strategy maps J

121607_0358_RelationalG1

Getting Rid of the Vista Credentials Prompt

/0 Comments/in /by

There could be 1001 reasons why IE asks you for credentials when you access a website configured for Windows Authentication and none of them has to do with Reporting Services itself. Windows Vista and IE 7 add yet another one. IE 7 will prompt you for credentials if your computer is not added to a domain and you access a local website configured for Windows Authentication, such as http://localhost/reports. Thanks to the help from James Wu on the SSRS team and Bryan Noyes’s blog, the solution is simple.

  1. Open IE and go to Tools, Internet Options, Security tab.
  2. Select the Local Intranet zone. Press the Sites button.
  3. Unselect the Automatically detect intranet network checkbox and check the three checkboxes below it.
  4. Restart IE. Now http://localhost/Reports or http://localhost/ReportServer shouldn’t prompt for credentials.

120607_2346_GettingRidO1

If it still prompts and you have SQL Server 2008 installed, it could be an issue with Kerberos. To fix this, open the ReportServer configuration file (rsreportserver.config). Locate the following section:

<Authentication>

<AuthenticationTypes>

<!–RSWindowsNegotiate/–>

<RSWindowsNTLM/>

</AuthenticationTypes>

<EnableAuthPersistence>true</EnableAuthPersistence>

</Authentication>

Comment the <RSWindowsNegotiate/> element or remove it, so only <RSWindowsNTLM/> is enabled.

Performance Improvements for MDX in AS 2008

/0 Comments/in /by

Here is a useful page from the SQL Server 2008 BOL that summarizes the SSAS performance improvements in Katmai pretty well. It also shows which functions are optimized. For example, the Lag function wasn’t optimized in 2005 but now is.

Radius Producer "Produces" No-nonsense SSAS Support

/0 Comments/in /by

In my opinion, the biggest challenge the Microsoft BI initiative faces today is the inadequate support for Analysis Services. The premimum Microsoft reporting tools claim to support SSAS but they all take an idependent view about what features they should support or not. To make things worse, even supported features turn out to be not “supported” enough and subsequenly declared as “by design” or known limitations. This cripples UDM and forces customers to look outside the Microsoft BI stack, hoping to find third-party tools to fill in the gap.

112107_0304_RadiusProdu1

Recently, I had the pleasure to take a look at the pre-release bits of the Radius Producer by 90 Degree Software. Those of you who installed the November CTP of SQL Server 2008 and played with the standalone Report Designer, will undoubtedly find many similarities between both tools, in terms of artchitecture, designer experience, even look and feel. One feature that Radius Producer excells in is end-user reporting from SSAS. For example, the snapshot shows the Radius Producer filtering support which I particularly liked. The user can filter on multiple dimension and measures and the tool provides common filter conditions that business users would appreciate, such as top, bottom, greater than, less than, etc. When the user selects the Ask Me Later option, the filter is promoted to a report parameter. Based on what I’ve seen, Radius Producer generates very clean and efficient MDX although I am yet to test with larger cubes. I was able to produce reports succesfully from both SSAS 2005 and 2008.

Radius Producer is more ambitious than being a reporting tool only. It promotes colleboration among information workers. Users can save queries and report snippets, and subsequently share them on the Radius network. Think Zune but instead of music you share report gadgets. If you are on a lookout for a third-party reporting tool that generates RDL and provides a good support for SSAS, take a moment to evaluate the forthcoming release of Radius Producer.

SQL Server 2008 November CTP Is Out

/0 Comments/in /by

The November CTP (CTP5) of SQL Server 2008 is out. Here are the most significant BI-related highlights that caught my attention.

Reporting Services

The standalone Report Designer has undergone a major facelift. It now sponsors a shiny Office 2007 ribbon interface. It will surely charm end users who are planning to use the standalone Report Designer for ad hoc reporting. The bad news is that there is still much integration work ahead. For example, the Analysis Services and custom data processing extensions are not integrated yet. Further, only the Dundas chart component has made the CTP5 cut. Nevertheless, I am very excited about the standalone Report Designer and its long-term potential to unite professional and end-user reporting needs.

111907_1814_SQLServer201

Analysis Services

The block computation enhancements debut in CTP5. Based on my experiments, they result in 50-60 percent and more improvement in query response times. For example, a highly inefficient Report Builder query that filters on a measure would take hours with SSSAS 2005. With CTP5 of SSAS 2008, the same query finishes under a minute! Put in highly technical terms, the nastier the query is, the faster it will with SSAS 2008. There are also optimizations in MOLAP write-back that remove the need to query ROLAP partitions although I haven’t given them a try.

Integration Services

CTP5 brings lookup performance enhancements. Are the SSIS guys lazy or what? J

Relational Engine

One BI-related enhancement with a great potential is Change Data Capture (CDC). When enabled, this features tracks changes to data, including inserts, deletes, and updates. This means that ETL process can quickly detect what changes have been made to a given table by just querying the CDC internal tables. For example, you query CDC to return only the new records that have been added to a table after a given data. As you can imagine, this baby will be a welcome enhancement with large data warehouses and can help you optimize the ETL processes.

Parameterized Parameter Prompts

/0 Comments/in /by

One new SSRS 2008 feature that international users will undoubtedly appreciate is parameterized parameter prompts.

101807_2114_Parameteriz1

Previously, the parameter prompts were static. In SSRS 2008, you will notice the familiar function symbol (fx) next to the parameter prompt which means that the prompts can be expression-based. This allows the report author to change the parameter prompt caption at runtime. For example, you can retrieve the localized version of the parameter prompt from an external resource assembly based on the user language.

UPDATE 03/27/2009 This feature didn’t make it to the release version of SSRS 2008. SSRS 2008 doesn’t support expression-based parameter prompts. The feature has been slated to a future release.

NEB Revenge in Katmai

/0 Comments/in /by

An advanced SSAS performance-related feature is Non Empty Behavior (NEB) which has been discussed in many places including this Mosha’s blog. The issue with NEB in SSSAS 2005 is that you can very easily get it wrong. The server will trust you (this could be an optimistic assumption) and accept a NEB even if it’s not correct. Example:

CREATE MEMBER CURRENTCUBE.[Measures].[Profit Gap To Prior Period]

AS [Measures].[Profit]-([Date].[Calendar].PrevMember, [Measures].[Profit]),

FORMAT_STRING = “#,#;(#,#)”,

NON_EMPTY_BEHAVIOR = { [Measures].[Profit]},

VISIBLE = 1;

Here, the intention is to default the expression for the Profit Gap To PriorPeriod calculated member to NULL if Profit is NULL. But the expression may be not be NULL even if Profit is NULL. This can yield incorrect results in 2005. In 2008, optimizations were made that don’t use the NEB expression. The net effect is that some calculated members that use wrong NEBs can produce different, albeit correct, results in 2008. If I am not wrong, in this webcast, Donald Farmer mentioned in this webcast that NEBs will be de-emphasized in SQL Server 2008. Meanwhile, if you are to use NEBs be sure that they are right to save you many hours of testing and head-scratching.