Prologika Forums

Textbox on Steroids

tlachev — Thu, 19 Jun 2008 18:13:18 GMT

One of the new features in Reporting Services 2008 that debuted in the SQL Server 2008 RC0 build is the enhanced textbox report item. In previous releases, if you wanted to mix static and dynamic expression-based text, you either needed multiple textboxes or a Visual Basic expression to concatenate strings together. The first approach led to textbox "explosion". The disadvantage of the second approach was that you couldn't format string fragments inside the same textbox independently.

In Reporting Services 2008, the textbox report item has been redesigned to support multiple bands of text. The screenshot shows a report title of a sales order report. In the past, you would need two textboxes (or three if you wanted different formatting for the sales order number). You may be surprised to find that the entire title is implemented as a single textbox with two paragraphs. The second paragraph combines static text (Order #:) with dynamic text ([SalesOrderNumber]), which defines a placeholder for a dataset field value. Each fragment can have its own format settings. Thanks to these enhancements, you'll find that by moving to Reporting Services 2008, you need fewer textboxes and you need to write less often expressions that concatenate text. Moreover, the new textbox lets you implement report solutions, such as mail merge, that were difficult or impossible to implement with previous releases.

Many report authors will rejoice learning that the textbox report item now supports a subset of HTML tags for formatting the text content. This is also known as rich formatting (not be confused with RTF which is not supported). You can import static HTML text or bind the textbox to a dataset field. For instance, if the dataset field includes HTML tags, such as <b>SO50750</b>, you can configure the textbox to interpret these tags and display the sales order number in bold.

SQL Server 2008 RC0 Product Samples Refreshed

tlachev — Wed, 11 Jun 2008 12:25:28 GMT

Microsoft has refreshed the product samples and AdventureWorks databases. I still have to figure out what's the AdventureWorksAS2008 database that is not released yet.

Microsoft SQL Server 2008 Feature Pack RC0, June 2008

tlachev — Tue, 10 Jun 2008 11:51:00 GMT

Today, Microsoft released an updated feature pack for SQL Server 2008 RC0. Among other things, it includes:

A web downloadable installer for Report Builder 2.0 which is not included anymore with the SQL Server 2008 setup
An updated version of the RS add-in for SharePoint
Analysis Services 10.0 OLE DB Provider

SQL Server 2008 Release Candidate 0 (RC0)

tlachev — Sat, 07 Jun 2008 12:30:00 GMT

Microsoft declared build #1442.32 to be the official SQL 2008 RC0 build and made it publicly available.

Key SSRS improvements in this build:

Rich text formatting. You can now format the text inside a textbox with different styles.
Report Wizard to let the user auto-generate the report definition.
Fully functional MDX Query Designer.
An updated build of the Report Builder 2.0 (aka Report Designer Preview) will be available via a web download. The URL link is not known at this point.

BI Salary Survey

tlachev — Tue, 20 May 2008 18:46:00 GMT

Not sure how much your BI expertise is worth? The 2008 TDWI Salary, Roles, and Responsibilities Report TDWI survey may help.

Intelligencia Query

tlachev — Thu, 15 May 2008 02:37:48 GMT

Chris Webb, a fellow MVP and MDX guru, who's blog is a must-read for all SSAS junkies, was kind enough to let me take a look at the Intelligencia Query product he's been working on for a while with Andrew Wiles. Chris announced the product back in April which is now selling as a commercial offering.

Now, lots of things have been said about the current state of the SSRS-SSAS integration, which is to say the least wanting. In a previous blog, I wrote "In my opinion, the biggest challenge the Microsoft BI initiative faces today is the inadequate support for Analysis Services." Alas, we won't witness an improved SSRS-SSAS integration in SQL Server 2008 as Microsoft decided to focus on other areas and priorities.

Rest assured though that Microsoft understands the importance of this integration scenario. I personally have voiced my concerns on a few occasions and have put this on the top of my wish list which I shared with the Reporting Services team. There are good things happening already which make me believe that SQL Server.NEXT (post-SQL Server 2008) will materialize this wish. What's really needed is Excel-like support of SSAS in Reporting Services, plus calculated members which are already supported.

Then, the question is what to do meanwhile if you target Analysis Services (and you should). One approach is to take the "I'll stick with Microsoft" approach. This may require you find workarounds for the static schema and other limitations, such as using the Microsoft OLE DB Provider for Analysis Services instead of the built-in SSAS provider. This is not as bad as it seems as little out-of-box thinking usually gets the job done. By the way, I wrote some 50 pages in my next book to cover integrating SSRS with SSAS in as much detail as possible.

The second approach is to look at third-party offerings, such as Radius Producer and Intelligenca Query. What I liked about Intelligenca Query is that it doesn't require throwing the baby with the water. You can still keep the Microsoft Report Designer but use Intelligenca Query to replace the built-in SSAS provider. This works because just like the built-in provider, Intelligenca Query is implemented as a data extension. I tested Intelligenca Query with SQL Server 2008 and it worked without any issues with the BIDS Report Designer and Report Builder 2.0 (aka Report Designer Preview).

The good thing about Intelligenca Query is that it lets you plug in an arbitrary MDX query. Behind the scenes, it pulls out a little trick where it rewrites the report dataset by transposing columns to rows. Consequently, you have to use the matrix region to rotate the rows back to columns to recreate the original query results. Now, my ideal provider would support a dual mode where I can tell it not to rewrite anything but just to give me the results. Of course, if another dimension member is added, it won't show up on the report columns (assuming a table with fixed columns) but I can see this being useful for notice users that create ad hoc reports or when the columns are fairly static.

Another thing I liked, of course, about Intelligenca Query is that it eliminates the nagging issue of server aggregates which are the aggregates for the dimension All members. With the built-in provider, you have to explicitly request them by using the Aggregate function but they won't be retuned if you hand-code your query. Intelligenca Query simply brings them as additional rows. What if you don't want them, such as when you need a group footer? No problem, just exclude them from the query and group on the rest of the rows.

Another cool thing is working with parent-child hierarchies. The MS built-in provider takes this strange design pattern where it dumps all members in a single column, thereby making it virtually impossible to create subtotals per level. With Intelligence Query this issue disappears:

I also liked the graphical query designer which is pretty much in par with the Microsoft graphical MDX Query Designer. On the downside, some outstanding work is required to handle parameters and synchronize the text query with the graphical designer.

If the SSAS built-in provider is driving you nuts, I encourage you to take a look and evaluate Intelligenca Query.

SSAS HTTP Connectivity and SharePoint

tlachev — Mon, 12 May 2008 11:58:26 GMT

Scenario

You won't enable SSAS HTTP connectivity via a SharePoint site.

Issue

You followed the instructions in the Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003 whitepaper but you get " Failed to start monitoring directory changes" error when you try to access the SSAS server by its pump URL, such as http://www.adventure-works/olap/msmdpump.dll.

Solution

Ignore the step where you had to create an olap pool and assign the OLAP vroot to it. Instead, assign the vroot to the same IIS application pool as the one the SharePoint site belongs to, such as SharePoint -80. In addition, make sure you enable the web extension to the pump dll, as explained in the above article.

If you have created non-domain Windows accounts on the server for testing, make sure that the server and local Windows accounts (the one the user will use to log in to the user machine) are synchronized, that is, have the same name and password. For example, if I create a non-domain local account Bob on the server where SSAS is installed, Bob must log in to his machine as Bob and both accounts (user and server) must have the same password.

Sharing Lost Reports with Web Users

tlachev — Sat, 10 May 2008 00:55:52 GMT

Scenario

You need to configure a SharePoint site integrated with Reporting Services for Internet access. In our case, the end users would authenticate with Windows security by using local Windows account created on the web server.

Issues

Report URLs reference the machine NetBIOS name and fail to execute with "The path of the item '<path' is not valid. The full path must be less than 260 characters long; other restrictions apply". Same happens when Report Builder tries to load models.

Solution

This one took a few hours to solve:

Since Reporting Services 2008 only supports access from SharePoint URLs in the default zone, change the public URL of the default zone (SharePoint 3.0 Central Administration -> Operations -> Alternate Access Mappings) to the web server Internet URL, such as http://www.adventure-works.com/.

This will take care of the report URLs which now should include the server Internet address. However, requesting reports would result in rsItemNotFound errors although the report URLs look perfectly normal.
Open the report project in BIDS. In the project properties, change the TargetDataSources and TargetReportFolder settings to include the server Internet URL instead of NetBIOS names, such as http://www.adventure-works.com/Report Library/Shared Documents/Data Sources for the TargetDataSource setting.
Redeploy the report definition.

At this point, standards reports deployed to SharePoint should work. If you use local server accounts for your users, however, previewing reports in Report Builder may fail with mysterious errors about invalid semantic queries. You can solve this issue by making sure that the user logon and server accounts are identical, that is, have the same name and password.

Tricksy Parameter Prompts

tlachev — Mon, 21 Apr 2008 16:47:00 GMT

Q: How to make the darn parameter drop-down list wider in HTMLViewer? HTMLViewer is the Report Manager control that displays the report when you view the report.

A: HTMLViewer supports limited customization via a CSS stylesheet.

Copy HTMLViewer.css to MyHTMLViewer.css in the same folder (Reporting Services\ReportServer\Styles). Note that the actual folder path varies between SQL Server versions.
Add the following CSS style to MyHTMLViewer.css

SELECT {

font-size: 8pt;

font-family: Verdana;

width:200px

}

As a result, all SELECT elements on the page (all dropdowns, such as parameter prompts, zoom, export) will pick up these settings.

Configure the server to use the custom stylesheet by one of these options:
1. If you want to apply the custom styles to all reports, add a new HTMLViewerStyleSheet element to the rsreportserver.config file, such as:

...

<HTMLViewerStyleSheet>MyStyleSheet</HTMLViewerStyleSheet>

...

</Configuration>

Note that due to an unfortunate bug, the HTMLViewerStyleSheet setting doesn't work with February CTP of SQL Server 2008 but it will be fixed in the final release.

Use the rs:Stylesheet device setting to apply the custom style to given report only, such as:

http://localhost/ReportServer?/AdventureWorks Sample Reports/Product Line Sales&rs:Command=Render&rc:Stylesheet=MyStylesheet

Applied Microsoft SQL Server 2008 Reporting Services

tlachev — Wed, 02 Apr 2008 02:48:00 GMT

This is not an April's fool joke. I am excited to make an early announcement about my next book, Applied Microsoft SQL Server 2008 Reporting Services, which has been keeping me busy lately. There are many exciting features coming up the next (third) release of Reporting Services which deserve coverage by your humble correspondent. I'll disclose more details about the book in the coming months as the project evolves.

Got spinny?

Applied Microsoft Analysis Services 2005 Goes E-Book

tlachev — Thu, 27 Mar 2008 12:16:50 GMT

I happy to announce my book "Applied Microsoft Analysis Services 2005" is now available as an Adobe PDF e-book. The first retailer that Google Alerts reported to sell it is Diesel Ebooks. It should soon pop up on all popular ebook retailer sites, such as ebooks.com. The suggested retail price for the ebook version is set to $39.95, which is ten bucks cheaper than the suggested retail price of the paper copy. The ebook is DRM-protected but fully functional with unlimited printing and copying capabilities. The DRM protection is handled by the retailer. Since the distributor is not set up to handle different pricing models, such as buy the paper copy and get the ebook free, there is no discount pricing model for the e-book version at this time.

Also, the same book is now available on Amazon Kindle. Since I don't have an Amazon Kindle, I don't know what the Kindle version looks like. Unfortunately, Amazon doesn't support PDF. They use proprietary software that converts the book to the Kindle native format (AZW). If you buy the Kindle copy, please drop me a note to share your feedback.

The Rational Guide To Planning with Microsoft Office PerformancePoint Server 2007

tlachev — Sat, 15 Mar 2008 22:46:21 GMT

I have to admit that my preoccupation with Reporting Services and Analysis Services don't leave me much bandwidth nowadays to tackle other Microsoft BI (far less third-party) offerings. One of the products I know I need to catch up is the planning component of PerformanceServer, formerly known as Biz#. Fortunately, Nick and Adrian followed up on their The Rational Guide To Planning with Microsoft Office PerformancePoint Server 2007, which I wrote about, with a new book The Rational Guide To Planning with Microsoft Office PerformancePoint Server 2007. Initially, they were planning a single PerformancePoint book but they decided to split it because of book size limitations that the publisher has.

The planning piece of PerformancePoint requires more than solid knowledge of Analysis Services because of its strong financial focus. You need to know quite a bit about budgeting, planning, chart of accounts, and other financial concepts. Fortunately, The Rational Guide To Planning with Microsoft Office PerformancePoint Server 2007 fills in the gap and bridges the implementation and business audiences by providing an essential coverage of the product accompanied with practical examples, all in a portable light-size format.

SQL Server 2008 February CTP Is Out

tlachev — Fri, 22 Feb 2008 12:41:51 GMT

February CTP is out. It brings the much anticipated integration with Visual Studio 2008 and the new BIDS Report Designer.

Download the February CTP here:
http://www.microsoft.com/sql/2008/prodinfo/download.mspx

Updated Books Online for February CTP are available here:
http://www.microsoft.com/downloads/details.aspx?familyid=19DB0B42-A5B2-456F-9C5C-F295CDD58D7A&displaylang=en

What's new in Reporting Services February CTP (compared to the previous November 2007 CTP):

Last Non Empty Affairs

tlachev — Wed, 06 Feb 2008 23:51:00 GMT

Enterprise Edition of Analysis Services 2005 and 2008 supports semi-additive functions, such as LastNonEmpty, LastChild, and so on, to facilitate working with semi-additive measures, such as account balances and inventory snapshots. For example, the screenshot below shows LastNonEmtpy in action. To demonstrate this, I added a Last Sales measure to the Internet Sales measure group in the Adventure Works cube and set its AggregationFunction property to LastNonEmpty. Of course, this doesn't make much sense because sales amounts are fully additive but it's OK for demo purposes.

As expected, the quarter and year totals show the last non empty sales. The customer totals are correct too because semi-additive measures sum across any dimension except Time. But what about the grand total of $14.98? Shouldn't we get the sum of the quarter (or year) subtotals?

To answer this question, we need to understand how LastNonEmpty operates. LastNonEmtpy works across time and not across other dimensions. Because the last customer recorded sales are in June 2008, LastNonEmpty gives you Q2 total of $14.98 which becomes the YTD total as well. So, does it make sense to sum the totals vertically? The philosophical answer is "it depends". If this was inventory balances, the question is why we have missing balances in the last day. In other words, to get the vertical sum right we need to record balances for each product for each period. One may say that in this case LastNonEmpty is correct because it makes no assumptions about what happened to the "missing" values for products, customers, etc. It could be that we didn't record them or it could be that there we discontinued that their ending balance is indeed not applicable.

But, there could be cases when we need LastNonEmpty to sum vertically instead of getting the last values. For example, the cube may have test scores and you may need to get the last score, such as to calculate the average student or school score. To address this requirement, our first impulse could be to use a scope assignment that overwrites the (Root(Customer), Root(Date)) tuple to server SUM aggregate function (the one you specify in the AggregateFunction property). But, as it stands, SSAS doesn't support plugging in server aggregate functions in scope assignments.

So, we will try the reverse approach:

Add a new fully-additive measure Sales with SUM aggregate function.
Overwrite the Sum with LastNonEmpty at the customer level.
Hide Last Sales and use Sales instead.

Scope

(

[Date].[Date].[All],

[Customer].[Customer].[Customer].Members, /*or Leaves(Customer)*/

{

[Measures].[Sales]

}

);

this = [Measures].[Last Sales]; /*assign Last Sales to Sales to make it semi-additive at customer level*/

End Scope;

Note that [Customer].[Customer].[Customer]. Members exclude the Customer All member. Since the server uses the All member to aggregate up, aggregating at a higher level, such by product will work fine because All member will contain the SUM of the last sales.

It will be great if a future SSAS release supports:

Using server aggregate functions in scope assignments
Give the modeler an option to control the behavior of the semi-additive measures.

SQL Server 2008 Expected Q3 2008

tlachev — Fri, 25 Jan 2008 20:59:00 GMT

Francois Ajenstat, Director of Product Management for Microsoft SQL Server, tells us that SQL Server 2008 will be released in Q3 2008 while Release Candidate (RC) is expected in Q2. The February 27^th marketing launch date remains unchanged.

Where Are Report Server Content Types Gone?

tlachev — Sun, 13 Jan 2008 02:55:00 GMT

SharePoint always has a trick up its sleeve to surprise the innocent. I have to admit that I continue to be baffled by this technology. Not that I know too much about it, of course. But this thing is like the Pacific Ocean – web applications, site collections, sites, oh my. The further you go, the deeper it gets. Things that still escape my mind include how to get the default Office Server web site (with the Report Center, etc.) after upgrading from WSS 3.0, why I cannot implement custom SharePoint filter provider web parts in WSS given that the interfaces are in WSS, and the mother of all questions, why WSS supports Windows Server OS only.

One thing, however, I managed to figure out (of course, after hours of experimenting, installing, and head-scratching since documentation is, shall we say, wanting) is how to get the Report Server content types to show in Microsoft Office SharePoint Server 2007. If you installing a new MOSS installation, after installing the Reporting Services Add-in, the Report Server content types should show up in the site settings. However, if you are upgrading from WSS, they may not show up. The issue could be that the Report Server Integration Feature is not activated. To check:

Open the site, expand the Site Actions and go to Site Settings, Modify All Site Settings.
Under Site Collection Administration, choose Site Collection Features. If this is not a top-level site, you need to select the link for the Top Level Site Collection Features.
Check the Report Server Integration Feature. If it is deactivated, click Activate. Isn't this tricksy?

That should do it. You should be able to add the Report Server content types to the site.

Trying to Communicate

tlachev — Sun, 23 Dec 2007 03:07:00 GMT

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.

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.
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?

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>

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

tlachev — Sat, 22 Dec 2007 00:54:00 GMT

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

tlachev — Mon, 17 Dec 2007 19:06:00 GMT

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

tlachev — Sun, 16 Dec 2007 03:59:00 GMT

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

Getting Rid of the Vista Credentials Prompt

tlachev — Thu, 06 Dec 2007 23:47:00 GMT

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.

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

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>

<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

tlachev — Mon, 26 Nov 2007 04:01:12 GMT

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

tlachev — Wed, 21 Nov 2007 03:04:00 GMT

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.

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

tlachev — Mon, 19 Nov 2007 18:14:00 GMT

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.

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

tlachev — Thu, 18 Oct 2007 21:15:05 GMT

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

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.