• Cumulative Update Package 1 for SQL Server 2008

    September 23, 2008 / No Comments »

    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

    September 11, 2008 / No Comments »

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

  • Personalized Cubes

    September 2, 2008 / No Comments »

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

  • Extending Report Builder 2.0

    August 31, 2008 / 1 Comment »

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

  • Report Builder 2.0 RC1 is Here

    August 25, 2008 / No Comments »

    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?

    August 22, 2008 / No Comments »

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

  • Nasty SSAS 2008 Bug

    August 15, 2008 / No Comments »

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

  • Where is Report Builder 2.0?

    August 14, 2008 / 2 Comments »

    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

    August 11, 2008 / No Comments »

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

  • Microsoft SQL Server 2008 Feature Pack, August 2008

    August 7, 2008 / No Comments »

    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

Training

We offer onsite and online Business Intelligence classes! Contact us about in-person training for groups of five or more students.
For more information or to register click here!

Books

Learn Power BI at your own pace with our latest book Applied Microsoft Power BI. Targeting information workers, data analysts, pros, and developers, It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Syndication