Prologika Forums

Reporting Services 2008 Dashboard Goes Live

tlachev — Mon, 05 Jan 2009 17:44:19 GMT

Thanks to the Robert Bruckner's blog, I've learned that Microsoft has published the Reporting Services 2008 Information Aggregator. It is a cool dashboard page that aggregates a variety of resources related to Reporting Services, such as training content, white papers, blogs and more. I'm proud to be featured as an MVP J The page also includes to my most recent books and training videos I've authored.

Kudos go to the Reporting Services user education team who worked hard to implement this page. Check out the SSRS aggregator and add it to your favorites.

SQL Server 2005 Service Pack 3 is Out

tlachev — Thu, 18 Dec 2008 13:03:02 GMT

Yesterday, Microsoft released SQL Server 2005 Service Pack 3. Reporting Services Report Builder added support with Teradata. Note that some manual configuration is required to turn things 'on' as well as you need to install the .Net provider from Teradata.

Dimensionalizing Pivoted Measures

tlachev — Tue, 16 Dec 2008 00:43:39 GMT

Issue

You have a table with pivoted measures, such as:

Use the following statement to create and populate the table:

CREATE TABLE pvt (VendorID int, DateID int, MaturityAmount1 int, MaturityAmount2 int,

MaturityAmount3 int, RepricingAmount1 int, RepricingAmount2 int, RepricingAmount3 int);

INSERT INTO pvt VALUES (1,1,4,3,5,4,4, 100);

INSERT INTO pvt VALUES (2,1,4,1,5,5,5, 100);

INSERT INTO pvt VALUES (1,2,4,3,5,4,4, 200);

INSERT INTO pvt VALUES (2,2,4,1,5,5,5, 200);

INSERT INTO pvt VALUES (3,1,4,3,5,4,4, 300);

INSERT INTO pvt VALUES (3,2,4,3,5,4,4, 400);

INSERT INTO pvt VALUES (3,3,4,3,5,4,4, 100);

INSERT INTO pvt VALUES (4,1,4,2,5,5,4, 100);

INSERT INTO pvt VALUES (5,1,5,1,5,5,5, 200);

Let's say you need a way to unpivot the results in the following format which is more suitable for OLAP:

In this way, you eliminate the repeating measure columns with a single column. Here, the BucketID column is introduced so you could join the results to a dimension to let the user cross-tab the results on columns.

Solution

Usually, ETL processes would take care of transforming data in format suitable for analysis especially with large data volumes. However, with smaller fact tables and an OLAP layer directly on top of the operational database, such as having SQL views directly on top of the source database, you may need an ad-hoc approach to unpivot the results. Assuming you are fortunate to have SQL Server 2005 or above, you can use the SQL Server UNPIVOT function to unpivot on multiple columns. The following query does the trick:

SELECT VendorID, DateID, CAST(REPLACE(Maturity, 'MaturityAmount', '') as int) as BucketID,

MaturityAmount, RepricingAmount

FROM

(SELECT VendorID, DateID, MaturityAmount1, MaturityAmount2, MaturityAmount3,

RepricingAmount1, RepricingAmount2, RepricingAmount3 FROM pvt) p

UNPIVOT

(MaturityAmount FOR Maturity IN (MaturityAmount1, MaturityAmount2, MaturityAmount3)) AS UnPivotedMaturity

UNPIVOT

(RepricingAmount FOR Repricing IN (RepricingAmount1, RepricingAmount2, RepricingAmount3)) AS UnPivotedRepricing

WHERE REPLACE(Maturity, 'Maturity', '') = REPLACE(Repricing, 'Repricing', '')

Notice that a WHERE clause is needed to join the two unpivoted sets so you don't end up with a Cartesian set. The net result is that the number of rows in the unpivoted dataset is:

Number of Rows in Pivoted (original) Table * Number of Repeated Columns

So, in our case, the dataset will have 9 * 3 = 27 rows.

Troubleshooting the SharePoint Add-in for Reporting Services

tlachev — Wed, 19 Nov 2008 22:06:00 GMT

A common issue when setting up Reporting Services integration with SharePoint is that the SharePoint add-in is not installed properly although the setup program doesn't indicate any issues. More than likely, the culprit is that the person who installed the add-in is not SharePoint Web farm administrator and Site Collection administrator.

As a first stop for troubleshooting SharePoint integration issues, you should examine the add-in log after the add-in setup completes to verify if it was installed successfully. You'll find the log file (RS_SP_<N>.log) in your temp folder, such as \Documents and Settings\<login>\Local Settings\Temp. Specifically, check that all activation tasks have completed successfully and that there are no errors. If all is well, the log files should look as follows:

User: <userid>

Installing Report Server feature.

Calling copyappbincontents command.

copyappbincontents command completed successfully.

Adding Report Server feature to farm.

Installed Report Server feature.

Activating Report Server feature to root level site collections.

Activating feature in web app 'SharePoint - 80'

Activating feature to root site collection: http://<server>

Activated feature for root site collection: http://<server>

Activating Report Server feature in all admin site collections.

Activating feature in web app ''

Activating feature to root site collection: http://<server>:35000

Feature already activated in site collection http://<server>:35000

Cumulative Update 2 for SQL Server 2008

tlachev — Wed, 19 Nov 2008 17:58:45 GMT

Microsoft has released the Cumulative Update 2 build (build number 1779) for SQL Server 2008. The update is not publicly available as of now. It has to be released individually based on whether you are running into a bug fixed in the build and it is password-protected. Among other things, it fixes the freeze issue associated with opening SSAS projects in BIDS.

Referencing Parameters in SSRS Calculated Members

tlachev — Mon, 10 Nov 2008 00:09:00 GMT

Issue: You author an SSRS report that connects to a cube. You want to create a calculated member that uses a report parameter. For example, the Selector calculated member in the following query returns 1 if a given employee has exceeded 1 million in sales for the dates specified by the user in the report parameter.

WITH MEMBER [Measures].[Selector] AS SUM (

StrToSet(@DateCalendar), iif([Measures].[Reseller Sales Amount]>1000000, 1, NULL)

)

select non empty {[Measures].[Reseller Sales Amount],[Measures].[Reseller Order Quantity], selector} on 0,

non empty (Filter([Employee].[Employee].[Employee].Members, [Measures].[Selector]>0) * [Date].[Calendar].[Calendar Year]) on 1

from (select StrToSet(@DateCalendar) on 0 from [Adventure Works])

However, when you run the query you get the following error if you target Analysis Services 2005:

The DateCalendar parameter cannot be resolved because it was referenced in an inner subexpression.

Obviously, the built-in SSAS data provider has an issue parsing parameters in a calculated member. This issue is apparently fixed in SQL Server 2008 where the above query executes just fine.

Workaround: As a workaround with SQL Server 2005, try defining a set that references the parameter and use the set in the calculated members, as follows:

WITH SET [SchoolYears] AS STRTOSET(@DateCalendar)

MEMBER [Measures].[Selector] AS SUM (

[SchoolYears], iif([Measures].[Reseller Sales Amount]>1000000, 1, NULL)

)

select non empty {[Measures].[Reseller Sales Amount],[Measures].[Reseller Order Quantity], selector} on 0,

non empty (Filter([Employee].[Employee].[Employee].Members, [Measures].[Selector]>0) * [Date].[Calendar].[Calendar Year]) on 1

from (select StrToSet(@DateCalendar) on 0 from [Adventure Works])

Dundas Chart Controls for .NET Available

tlachev — Mon, 27 Oct 2008 16:09:54 GMT

On Friday, Microsoft released chart controls for Microsoft .NET Framework 3.5. These are re-tooled Dundas .NET chart controls which are now baked in the .NET framework. This is important because you can add the same stunning Dundas chart features to your Windows Forms and ASP.NET applications without external dependencies or licensing fees. It also minimizes your learning curve, as the Dundas controls share the same object model across technologies, e.g. Windows Forms to ASP.NET to Reporting Services.

Currently, only the Dundas chart control has made the cut to .NET. There is a sticky post at the top of the forums that has all of the links. Keep an eye on the PDC announcements today for more BI goodies.

SSAS WHERE vs. Subselect Puzzle

tlachev — Thu, 23 Oct 2008 12:53:00 GMT

I've been involved recently in a quest to optimize an SSAS 2008 long-running query that would return the 12-month history of a KPI. Since the KPI was displayed on a dashboard page alongside other KPIs and charts, it was important to optimize the query as much possible. Rephrased to Adventure Works, the query went something like this:

WITH MEMBER increaser AS Iif([Measures].[Internet Sales Amount]>([Measures].[Internet Sales Amount], [Date].[Calendar].PrevMember),1,NULL)

MEMBER CustomersWithIncrease as Sum([Customer].[Customer].[Customer], [Measures].[increaser])

SELECT [Measures].[CustomersWithIncrease] on 0,

ParallelPeriod ([Date].[Calendar].[Month], 11, [Date].[Calendar].[Month].&[2003]&[12]) :[Date].[Calendar].[Month].&[2003]&[12] on 1

from (select -{[Customer].[Customer].&[20075]} on 0 from [Adventure Works])

where {[Sales Territory].[Sales Territory Country].&[United States], [Sales Territory].[Sales Territory Country].&[United Kingdom]}

This query compares the sales for each customer with the customer's sales for the previous month and returns the number of customers who have increased sales for the past 12 months. Also, the query uses a subselect to exclude a customer and a WHERE clause that restricts the results to USA and United Kingdom only. The real-world query would get the members to filter in the WHERE clause from the UI.

The first optimization technique to point out is that instead of filtering the customer set using the Filter function, the query benefits from Mosha's summator trick, which alone improved the query performance about ten times. However, the query was still taking long to execute (some 30 seconds with set of 150,000 customers). Much to my surprise though, flipping the WHERE and the subselect clauses cut the query time in half.

WITH MEMBER increaser AS Iif([Measures].[Internet Sales Amount]>([Measures].[Internet Sales Amount], [Date].[Calendar].PrevMember),1,NULL)

MEMBER CustomersWithIncrease as Sum([Customer].[Customer].[Customer], [Measures].[increaser])

SELECT [Measures].[CustomersWithIncrease] on 0,

ParallelPeriod ([Date].[Calendar].[Month], 11, [Date].[Calendar].[Month].&[2003]&[12]) :[Date].[Calendar].[Month].&[2003]&[12] on 1

from (select {[Sales Territory].[Sales Territory Country].&[United States],

[Sales Territory].[Sales Territory Country].&[United Kingdom]}

on 0 from [Adventure Works])

where -{[Customer].[Customer].&[20075]}

Here, the member that needs to be excluded was moved to the query WHERE clause, while the rest of the filter went to the subselect. While I don't know what the server does exactly, my hypothesis is that the WHERE clause is more efficient in restricting the cube space before the rest of the query is executed. I wasn't able to reproduce the performance gain with the Adventure Works cube probably because is too small or the issue was design specific. Larger cubes may benefit from the filter flip.

IMPORTANT OOPS

As Mosha was quick to point out, in particular case where SUM is used, the attempt to exclude a given customer in both cases (subselect and WHERE) will not work. Specifically, filtering out the customer is a no-op, since the Customer.Customer.Customer inside Sum overwrites it. Consequently, the queries don’t exclude this customer from the computations at all and therefore are wrong. A great optimization without QA involvement, indeed :-). However, I still can't explain why subselect is slower if both options are no-ops. BTW, if the exclude filter was done on another attribute (not Customer), then the subselect would produce the correct results, while the WHERE clause will still be a no-op.

In this particular case, the easiest way to exclude specific customers is to remove them from the set inside the SUM function, as the following query shows. This, of course, will impact the query performance. Based on my test, the member exclusion adds about 25% overhead to the query.

WITH MEMBER increaser AS Iif([Measures].[Internet Sales Amount]>([Measures].[Internet Sales Amount], [Date].[Calendar].PrevMember),1,NULL)

MEMBER CustomersWithIncrease as Sum({[Customer].[Customer].[Customer] - [Customer].[Customer].&[20075]} , [Measures].[increaser])

SELECT [Measures].[CustomersWithIncrease] on 0,

ParallelPeriod ([Date].[Calendar].[Month], 11, [Date].[Calendar].[Month].&[2003]&[12]) :[Date].[Calendar].[Month].&[2003]&[12] on 1

from (select {[Sales Territory].[Sales Territory Country].&[United States],

[Sales Territory].[Sales Territory Country].&[United Kingdom]}

on 0 from [Adventure Works])

Report Builder 2.0 RTM'ed

tlachev — Sat, 18 Oct 2008 02:59:00 GMT

Today, Microsoft released Report Builder 2.0 (build 10.00.1600.60) , as Robert Bruckner announced in his blog. This is a major milestone in the Reporting Services roadmap. In my opinion, Report Builder 2.0 is one of the most important and exciting features of Reporting Services 2008 as it bridges the gap between standard and end-user (ad hoc) reporting. You should definitely consider Report Builder and Analysis Services for your ad-hoc reporting projects.

People Ready BI

tlachev — Wed, 08 Oct 2008 01:20:36 GMT

At the BI Conference in Seattle, Microsoft announced the future roadmap for next BI-oriented SQL Server release, nee Kilimanjaro, that will focus on three major teams:

• Self Service Analytics

• Self Service Reporting

• Sharing, Collaboration & Management

The Self Service Analytics will encompass projects Madison (a share-nothing MPP scale-out version of SQL Server, powered by DATAllegro technology) and Gemini, which I mentioned before.

The Self Service Reporting is where Reporting Services.NEXT will come in, of course. Microsoft announced the acquisition of 90 Degree Software. In case you are not familiar with this company, 90 Degree Software has built a very comprehensive report designer that is very similar to Report Builder 2.0. What I expect from this acquisition is more end-user enhancement in Report Builder 2.0 and improved Analysis Services integration. Expect also a shared report library that lets the end user save and share report elements and templates with other users.

The SoftArtisans acquisition will finally materialize in Kilimindjaro to empower business users to author reports inside Microsoft Office. Expect the preview bits of Kilimanjaro in 1H2009 with release in 1H2010. Watch the keynote presentation online.

Memory-based Dimensional Model (MDM) on the Horizon

tlachev — Tue, 07 Oct 2008 01:27:00 GMT

As the news arrived today (see Mosha Pasumansky blog, Chris Webb blog, and Marco Russo blog) , the BI Conference revealed what has been brewing for a while in the Microsoft BI nest. Project Gemini, which one may call Memory-based Dimensional Model (MDM) to draw a comparison with the Unified Dimension Model (UDM), will let business users create ad-hoc cubes that are stored in memory. This will let Microsoft compete with other BI players, such as the Cognos TM1 offering which Cognos acquired from Applix. Accidentally, I dare to predict that MDM will be very similar to TM 1 (watch a demo here) but of course better J.

While I need to get my hands on this Gemini thing to say something worthwhile, I really hope that existing UDM cubes could benefit from it as well, especially in terms of performance. Today, folks are pushing SSAS to its limits. A dashboard page, for example, may need to execute many queries and crunch huge volumes of data to present trend graphs within seconds. It will be cool if Gemini lets you cache subcubes in memory to speed up these scenarios. For example, if you need to implement a bunch of customer-related KPIs, it will be nice if you could tell Gemini to cache in memory or materialize to disk the pre-aggregated at the customer level data and which dimensions can be used to slice these KPIs.

What about giving the business users the option to create ad-hoc cubes? I have to admit I have mixed fillings about this. The term "OLAP" alone is known to cause severe brain crunch to many users. I am a bit skeptical that "off you go, start building your own cubes in Excel" philosophy will really fly. If you package a cool wizard that hides some of the dimensional model complexity, how would verify that the results are indeed correct so you don't end up with as many versions of the truth as the number of users? How would teach end users MDX to create their own calculations? Not sure how much of your time Gemini will really save if this is its major selling point. But again I may change my point of view as details unfold as life often proves me wrong. Meanwhile, long live MDM and Kilimanjaro, which is the code name for SQL Server.NEXT, scheduled for H1 2010!

Cumulative Update Package 1 for SQL Server 2008

tlachev — Tue, 23 Sep 2008 17:20:10 GMT

Microsoft has released the first for SQL Server 2008 cumulative update package. Among other issues, it fixes the SSAS bug with requesting measures with mixed aggregation functions which I previously reported.

Sorting Dates in a PivotTable Report

tlachev — Thu, 11 Sep 2008 01:06:00 GMT

Issue: You use Excel as an OLAP browser connected to an SSAS cube. You've defined the name of the Month attribute in the Date hierarchy in the format MMM-YYYY. While months are sorted in an ascending order in Excel, descending sorting doesn't work; or rather Excel sorts them as strings. That's because Excel interprets these captions as strings instead of dates even if you set the Value property of the Month attribute to a field of DateTime type.

Workaround: You can use the Excel custom sorting feature as a workaround while waiting for Excel to become a better OLAP citizen:

Connect to the cube and select the Month attribute to get all months displayed in Excel. Select all month cells.
Click the Excel Office Ribbon button.
In the Popular tab, click the Edit Custom Lists button.

In the Custom Lists dialog box that follows, click Import button to create a new custom list for the dates in the format MMM-YYYY. Click OK

As you can see, there is a custom sorting list for months in the format MMM, so descending sort will work if the month captions are in MMM format. However, the end user won't be able to tell the months apart if the user requests only the Month attribute on the report. So, you kind of have to pick up your own poison.

Personalized Cubes

tlachev — Tue, 02 Sep 2008 01:33:05 GMT

Scenario: You need to personalize the captions of calculated members or/and KPIs of an SSAS cube. For example, a solution vendor may need to deploy a cube to multiple clients. Instead of maintaining separate cube definitions, the vendor may prefer to change the captions of certain calculated members and KPIs per client. There wasn't a good story in SSAS 2005 about such requirements. In SSAS 2008, there are at least two techniques to tackle cube personalization.

First, SSAS 2008 introduces a new Caption property to calculated members and KPIs, which Chris Webb has already written about. Vidas Matelis has provided great examples of using the Caption property. Thanks to this enhancement, you can keep the names of calculated members and KPIs the same but change only their captions. This is the good news.

What if you need to make definition changes programmatically, such as to check the type of the client and change captions accordingly? Well, unfortunately the Caption property is not available in the AMO library as it has not been added to the KPI class. What's worse is that the CREATE KPI statement doesn't actually add the KPI definition to the KPIs Collection so you cannot programmatically change the caption by enumerate the AMO Kpis collection and update the KPI definition. To make the story short, the only way I've found to update the KPI caption is to update the cube MDX script (Cube.MDXScripts AMO collection). This forces you to resort to search and replace text inside the cube script. Sigh… The following code shows how to use AMO to connect to the cube and change the cube MDX script.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.AnalysisServices;

namespace AMO

{

class Program

{

static void Main(string[] args)

{

Server server = new Server();

server.Connect("<server>");

Database database = server.Databases["<database name"];

Cube cube = database.Cubes["<cube name>"];

// assume KPIs are defined in the cube default script

MdxScript mdxScript = cube.MdxScripts[0];

Command command = mdxScript.Commands[0];

// search and replace captions in the command.Text

// command.Text = ...

cube.Update(UpdateOptions.ExpandFull);

}

The second personalization option is the new Personalization Extension mechanism in the SSAS 2008, which I personally haven't tried out. Microsoft has provided a sample here. A custom personalization extension is a .NET code that is deployed to the server. It lets you create user-specific session-level calculated members and KPIs without having to change the cube definition. The extension changes are transient and disappear when the user's session ends.

Extending Report Builder 2.0

tlachev — Sun, 31 Aug 2008 18:20:11 GMT

As I mentioned in my previous blog, Report Builder 2.0 RC1 has arrived. Although perhaps too early in the game, you may wonder how you can get the most out of Report Builder 2.0 and extend it when the built-in features are not enough. As Report Designer and Report Builder 2.0 share the same design surface, you may believe that they are equally extensible. Unfortunately, this is not quite the case. Specifically, due to time constraints, Report Builder 2.0 supports the following RS extensibility features only in server mode:

Custom Data Processing Extensions
Custom Security
Custom Report Items

Server mode is triggered when you open a report from the server or when the report references a shared data source whose definition must reside on the server. When you preview a report in server mode, the report is processed and rendered on the server. The above extensibility areas are not available in client mode. For example, the user can open a server report that has a custom report item, sets its properties (assuming that the right configuration settings have been made to the Report Builder 2.0 configuration files), and preview the report, as shown in the screenshot. However, the Report Builder 2.0 ribbon will not be extended with the custom report item. Nor the user will be able to author a report from scratch and add the custom report item to the report.

The unfortunately side effect is that if the business user needs to use a CRI on a report, such as the Dundas Map CRI, a developer must author a "shim" report with Report Designer, add the CRI to the report, and deploy the report to the server so the business user can use the report as a starting point.

The only extensibility mechanism that seems to be working in client mode in RC1 is custom code (embedded and external). More than likely, Report Builder 2.0 will get the entire extensibility support of Reporting Services at some point of time. If you want to expedite the process, log your wish on connect.microsoft.com and vote for extensibility features you need Report Builder 2.0 to provide.

Report Builder 2.0 RC1 is Here

tlachev — Mon, 25 Aug 2008 23:12:00 GMT

Thanks to Robert Bruckner's blog, I've learned that the Report Builder 2.0 (previously known as Report Designer Preview) Release Candidate 1 is available for download. This should be a feature-complete build of Report Builder 2.0, which is scheduled for official release in October.

IMO, Report Builder 2.0 is one of most exciting features of Reporting Services. I expect it to blur the "great divide" between standard and ad-hoc reporting. Based on some preliminary feedback from our business customers, they are very excited about Report Builder 2.0 and they favor it instead of Report Builder 1.0. Despite the unfortunate name, Report Builder 2.0 has very little to do with its predecessor. Instead, it's very close (almost identical) to the BIDS Report Designer as it supports the full RDL feature set.

You should definately evaluate Report Builder 2.0 for ad hoc business reporting.

Where is THE Book?

tlachev — Fri, 22 Aug 2008 19:40:55 GMT

A few readers are asking about the whereabouts of my book Microsoft SQL Server 2008 Reporting Services, so I felt I need to write a short status report. The book is printed and it's on stock with the distributor. It's making its way slowly-y-y to the retail network. It turns out there are lots of things that need to happen before the book is finally listed in-stock with retailers, such as Amazon. I am frustrated by this fact but there is nothing I can do to speed up the process. Hopefully, the book will pop within a few days so you can get it at a discounted price.

Anyhow, if you need the paper copy as soon as possible, you can order it from the distributor at the full price and you will get it within 2-3 days. Alternatively, you can order the e-book version, which is already available retail, such as on ebooks.com, diesel-ebooks.com, etc.

Finally, don't forget that two chapters are freely available on the book page, plus video demos, to get you started with RS 2008.

Nasty SSAS 2008 Bug

tlachev — Fri, 15 Aug 2008 12:11:00 GMT

There is a nasty known bug with SSAS 2008, where if a query that requests two measures with Sum and LastNonEmpty aggregate functions side by side and the query uses NON EMPTY (which all OLAP clients use by default), the server forces the measure with the SUM function to display empty.

Steps to repro:

Use the Analysis Services Tutorial cube (Lesson 10) which is included in the SSAS samples. Don't use the Adventure Works cube because it has measure expressions for certain measures, so changing a measure aggregation function to LastNonEmpty and deploying the cube results gives the error "Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of 'Internet Sales', cannot be verified against the source object." That's a different issue that has been around since SQL Server 2005 but apparently didn't get fixed.

1. Open the Analysis Services Tutorial project from Lesson 10

2. Open the Analysis Services Tutorial.cube in the Cube Designer.

3. In the Cube Structure tab, expand the Internet Sales measure group and select the Internet Sales-Order Quantity measure.

4. In the Properties pane, change the AggregateFunction to LastNonEmpty.

5. Deploy the cube.

6. Browse the cube with the cube browser or Excel. Create a report that requests Internet Sales Amount and Internet Sales-Order Quantity measures sliced by the Subcategory attribute of the Product dimension.

Notice that the query forces Internet Sales Amount to empty. While waiting for an official hotfix from Microsoft, you can use one of the following workarounds meanwhile:

1. Don't use the NON EMPTY clause in the query

2. Change the following property in the \Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini file.

Not sure what DisableCalcExpressNonEmpty does as it is not documented but appears to work.

UPDATE 8/18/2008

DisableCalcExpressNonEmpty is a new property in AS 2008. Severe performance degradation is likely to result when set to 1, but it should be comparable to AS 2005 performance if not a little better. A hotfix better come out quickly

Where is Report Builder 2.0?

tlachev — Thu, 14 Aug 2008 01:32:36 GMT

With the official release of SQL Server 2008, Microsoft retired the RC0 Feature Pack and added the SQL Server 2008 Feature Pack, August 2008 page. Unfortunately, the Report Builder 2.0 link got dropped along the way. Microsoft is aware of this issue and is planning to add a release candidate page for Report Builder 2.0 feature pack available for download toward the end of August. The RTM version of Report Builder 2.0 is expected in Oct'2008 timeframe.

Static Credentials

tlachev — Mon, 11 Aug 2008 14:08:56 GMT

A new week comes with new issues... I ran into an interesting issue today with Windows integrated security and SharePoint. I was troubleshooting an issue on a behalf of a Windows user that connects to an SSAS server. To repro the issue, I used the SharePoint "Sign in as a different user" feature. To facilitate testing, I decided to save the password in the standard Windows authentication dialog that follows ("Remember my password" checkbox).

After this "convenient" setup, to my surprise all calls to that server went the credentials of that user, including connections to the cube from SQL Server Management Studio and Excel! For example, when I connected to the SSAS database with SSMS and attempted to manage the server, I was greeted with the following message although I have admin rights to the server:

The connected user is not an Analysis Services server administrator. Only an administrator can make changes to server properties. (Microsoft.AnalysisServices.ManagementDialogs)

The SQL Server Profiler revealed that indeed the server impersonates any call under that user. Not sure what exactly happens when you save the password but be careful of this issue with "static" credentials. To correct the issue, use the Sign in as a different user feature again but don't check the Remember my password option.

Microsoft SQL Server 2008 Feature Pack, August 2008

tlachev — Thu, 07 Aug 2008 14:24:00 GMT

Microsoft released a Microsoft SQL Server 2008 Feature Pack, August 2008 page which includes a collection of stand-alone install packages that provide additional value for SQL Server 2008. Strangely, Report Builder 2.0 is excluded and nowhere to be found since the RC0 page has been taken offline.

Update 8/7/2008:

A RC for Report Builder 2.0 feature pack will be available for download toward the end of August

Microsoft Has Released SQL Server 2008

tlachev — Wed, 06 Aug 2008 17:47:00 GMT

Today, Microsoft announced that official release of SQL Server 2008. Build #10.0.1600.22 has been declared as a SQL Server RTM.

SQL Server 2008 includes major enhancements to the Microsoft Business Intelligence Stack, some of which I have mentioned in my previous posts.

Note that if you have Visual Studio 2008 on your machine, you must wait for a few more days to get the official release of Visual Studio 2008 SP1, which is a required prerequisite to integrate the SQL Server 2008 client tools with Visual Studio 2008.

Enjoy!

Rules Have Changed

tlachev — Wed, 06 Aug 2008 02:26:00 GMT

(Please visit the site to view this media)(Please visit the site to view this media)(Please visit the site to view this media)An interesting question has pop up on the Katmai RS 2008 discussion list today about how to implement a "green bar" group-level report that alternates background color for all rows in a group instance instead of doing this for each row. I attach the report sample I posted. What makes it interesting is that it demonstrates report variables – a new feature in RS 2008.

Before you start analyzing it, you should know that expression evaluation rules in RS 2008 have changed as a result of the new on-demand processing model. As a result, state in class-level instance variables is discarded as you page through the report which makes maintaining state trickier between page requests. However, RS 2008 introduces report variables that guarantee one-time evaluation semantics. I know this may sound to you like Greek, so let me jump into the implementation details:

1. The report has a EvenRow code-behind function that toggles each time it's executed.

2. In the Category group (double-click it to access its properties), an EvenRow group level variable is defined that invokes the EvenRow function once per each group instance.

3. The rest is easy. I set the BackgroundColor property for each textbox to use this variable. BTW, the report uses the SSAS Adventure Works cube. (Please visit the site to view this media)

Yet Another Post About Analysis Services HTTP Connectivity

tlachev — Sat, 02 Aug 2008 02:30:55 GMT

With risk to iterate the obvious, here is a lesson learned from the trenches:

Scenario: You want to let external users browse an Analysis Services cube over the Internet.

Setup: You have followed the steps in the Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003 article by Edward Melomed to set up Analysis Services for HTTP connectivity.

Issue: Windows integrated security doesn't work.

After a long battle where countless options have been tried, I have to admit a defeat. In our case, we've set up identical Windows local accounts on the SSAS server. This worked fine when connecting Report Builder 2.0 to the cube from our home machines. However, it appeared that the firewalls that our clients use make a minced meat of Windows integrated security. Strangely, Excel connects without a problem to the cube using Windows integrated security. However, Report Builder 2.0 chokes big time. I guess this has to do with differences between OLE DB (which Excel uses) and ADOMD.NET (used by Report Builder 2.0).

So, we had to give up on Windows integrated security over Internet. Instead, we went for Basic security with SSL, as follows:

Configure the msmdpump virtual folder for Basic security (this should be the only available security option).
Install the SSL certificate on the IIS server.
In Report Builder 2.0, set up a new data source that uses the Microsoft SQL Server Analysis Services. Click the Edit button on the Data Source Properties dialog box.

In the Connection Properties dialog, enter the URL to the msmdpump.dll, such as https://adventure-works/olap/msmdpump.dll.
Enter the user credentials. Click the Save My Password to avoid being asked to retype the password.
Here is an important step if you want to populate the Connect to a Database drop-down list. Click the Advanced button and locate the Integrated Security setting. Select and clear the SSPI default setting. Click OK.
Back to the Connection Properties dialog, expand the Connect to a Database drop-down list. You should see the list of the SSAS databases the user has rights to access. Select a database.
Click the Test Connection dropdown. The connection should succeed.

The data source is set up now. You can proceed with setting up a dataset with the MDX Query Designer.

A Must-read Reporting Services Blog

tlachev — Mon, 21 Jul 2008 19:53:48 GMT

Robert Bruckner on the Reporting Services team has started a blog. Robert oversees several key areas of Reporting Services, including Report Definition Language (RDL), data processing, report processing, data visualization, and performance/scalability. Many of you have gotten help from Robert on the Reporting Services discussion lists to which he frequently contributes. So, this sure it's going to be a must-read blog as his first posts (report variables and scalability) show.

RS Blog Roll

Bob Meyers' Blog

(http://blogs.msdn.com/bobmeyers)—Bob Meyer is a Program Manager on the Reporting Services team who is responsible for Report Builder 1.0.

Brian Welcker's Blog

(http://blogs.msdn.com/bwelcker)—Brian Welcker is a former Group Program Manager on the Reporting Services team.

Chris Baldwin's Blog

(http://blogs.msdn.com/chrisbal)—Chris Baldwin is a Program Manager on the Reporting Services team who oversees the report rendering area.

Chris Hays's

(http://blogs.msdn.com/chrishays)—Chris Hays is an architect on the Reporting Services team and oversees the Report Definition Language.

John Gallardo's Blog

(http://blogs.msdn.com/jgalla)—John Gallardo is a Software Development Engineer on the Reporting Services team who is responsible for the report server.

Lukasz Pawlowski's Blog

(http://blogs.msdn.com/lukaszp)—Lukasz Pawlowski is a Program Manager on the Reporting Services team who is primarily responsible for the management feature of the product.

Reporting Services Team Blog

(http://blogs.msdn.com/sqlrsteamblog/)—A collective blog of the Reporting Services team.

Reporting Services User Education Blog

(http://blogs.msdn.com/rosettaue)—From the user documentation team which is responsible for creating all the documentation that ships with the product.

Robert Bruckner's Blog

(http://blogs.msdn.com/robertbruckner)— Robert Bruckner is a Software Development Engineer on the Reporting Services team who oversees Report Definition Language (RDL), data processing, report processing, data visualization, and performance/scalability.

Russell Christopher's Blog

(http://blogs.msdn.com/bimusings)—Russell Christopher is a Business Intelligence consultant with Microsoft who posts great insights about working with Reporting Services.