• Transmissions from TechEd USA 2009 (Day 3)

    May 14, 2009 / No Comments »

    Today was a long day. I started by attending the Richard Tkachuk's A First Look at Large-Scale Data Warehousing in Microsoft SQL Server Code Name "Madison". Those of you familiar with Analysis Services would probably recognize the presenter's name since Richard came from the Analysis Services team and maintains the www.sqlservernanalysisservices.com website. He recently moved to the Madison team. Madison is a new product and it's based on a product by DATAllegro which Microsoft acquired sometime ago. As the session name suggests, it's all about large scale databases, such as those exceeding 1 terabyte of data. Now, this is enormous amount of data that not many companies will ever amass. I've been fortunate (or unfortunate) that I never had to deal with such data volumes. If you do, then you may want to take a look at Madison. It's designed to maximize sequential querying of data by employing a shared-nothing...

  • Transmissions from TechEd USA 2009 (Day 2)

    May 13, 2009 / 3 Comments »

    I started the day by attending the Donald Farmer and Daniel Yu's session "Creating the Right Cubes for Microsoft Excel and Excel Services" hoping that I'll get a sneak preview of Excel 2010. Alas, it was all about refining the cube definition with display folders, perspectives, hierarchies, etc. so it appears more user-friendly in Excel. Later, I learned that Office 2010 (or whatever it will be called) is under strict NDA which explains the lack of demos. The most interesting thing about that session was that I finally understood why the SSAS team decided to scale down the cube wizard in SSAS 2008 to generate basic dimensions only. The reason was performance. You see, the BIDS 2005 cube wizard would oftentimes suggest non-optimal dimension hierarchies and the modeler wouldn't revise the design leading to bad performance. Next, I attended the Thierry D'Hers Top Ten Reasons for Using Microsoft SQL Server...

  • Transmissions from TechEd USA 2009 (Day 1)

    May 12, 2009 / No Comments »

    Day 1 of TechEd 2009 is almost over with the exception of the Community Influencers Party tonight. I heard that this year they expect 7,000 attendees. This is a huge scale-down from previous years. For instance, we had 16,000 attendees at TechEd USA 2007. Economy is hitting everything hard. I thought the keynote was kind of lame. Judging by it, Microsoft has only three products: Windows 7 (officially announced to ship around holidays although Microsoft didn't say which holidays), Windows Server 2008 (the buzz is now the forthcoming R2 release), and Exchange Server 2010. Unlike previous TechEds, there wasn't a single announcement about other products. SQL Server KJ, Office 2010, Azure, dev tools? Nope, apparently not worth mentioning. Sure Mark Russinovich, whom I respect very much, did some cool Windows 7 demos but there were not enough to pique my interest. I understand that OS and Exchange Server are bedrock...

  • SQL Server 2008 Business Intelligence Development and Maintenance Toolkit Available

    April 16, 2009 / No Comments »

    Today was supposed to be the birthday of the SQL Server 2008 Business Intelligence Development and Maintenance Toolkit by Microsoft Press but it's not available with retailers, such as Amazon, yet. I guess a couple or so more days as the book is finding its way to resellers. As with the 2005 version, I was privileged to work together with Erik Veerman and Dejan Sarka (all SQL Server MVPs) on the new revision. My part was the four Analysis Services chapters. Besides updating the book for SQL Server 2008, we re-worked the entire material to flow more logically and make this resource even more useful to help you prepare for the corresponding 70-448 exam. I hope you'll find the toolkit useful and pass the exam to certify!

  • Overwriting Parameter Defaults

    April 14, 2009 / No Comments »

    Issue: An interesting question popped today on the discussion list about overwriting the default of a cascading parameter. In this particular case, the report has two parameters: Frequency and HireDate. The HireDate parameter is dependent on the Frequency parameter. Specifically, when the user changes the Frequency parameter, custom code changes the default of the HireDate parameter. Sounds easy, right? Well, not so fast. This requirement calls for smashing the HireDate default. However, smashing values that the user selected (either explicitly or implicitly by not changing the default) is usually considered to be a bad thing. There exist uncommon cases such as this one where the author of the report can reasonably know the user would expect their previously selected value will be overridden. Unfortunately, Reporting Services doesn't not currently have a feature which would allow the report author to distinguish the common case from the uncommon case. As a result,...

  • Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies, April 2009

    April 13, 2009 / 1 Comment »

    As I mentioned in a previous blog, one very important SSRS enhancement in the recently released SQL Server 2008 SP1 is Report Builder 2.0 ClickOnce. This lets you reconfigure the native and SharePoint integration modes to let the user use Report Builder 2.0 instead of Report Builder 1.0. Note that in SharePoint integration mode, installing SP1 only is not enough. That's because there are actually two instances of RB2.0 ClickOnce. If you install SQL Server 2008 SP1, this will provide an update for native mode only. For SharePoint integration mode, you must refresh the Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies. This is necessary because the ClickOnce bits must reside on the SharePoint server. The April 2009 version of the add-in can be downloaded from here.

  • SSAS Import Wizard to the Rescue

    April 2, 2009 / No Comments »

    Sometimes, it's better just to reset. I came across an issue concerning processing a partition whose containing measure group joins a dimension via another reference dimension. Specifically, when I attempt to process the partition, the server would error out with: The binding for the 'dimension key' column is not a ColumnBinding type What made this issue interesting is that I was able to repro the issue each time I would process the partition but the Microsoft support engineer, who was assigned to this issue, hasn't been able to. After some digging we realized that the issue only happens in project mode, that is, when working with the project source in BIDS and deploying to the server. How do we fix this horrible issue? We reverse-engineer the deployed cube using the SSAS Import Wizard to regenerate the source code. In BIDS, click File->New->Project. In the New Project dialog, select Import Analysis...

  • Excel PivotTable Tabular Reports

    March 31, 2009 / No Comments »

    Here is a great tip thanks to Greg Galloway who pointed me to the Steve Novoselac's excellent blog. By default, Excel 2007 PivotTable stacks (hierachizes) the attributes on the report even if they are from the same dimension. For example, let's say the user wants to slice by the product hierarchy but want also some additional product attributes, such as the product color, size, etc. By default, Excel will produce the following layout: Needless to say, this is hardly what the users want to see as they would prefer the product attributes to be displayed in a tabular format. Luckily, this is easy albeit not very intuitive. Before you drop the first field on PivotTable, click the Design main menu on the ribbon (the one next to Options menu). Expand the Report Layout drop-down menu and select Show in Tabular Form. Again, make sure that that PivotTable is empty before...

  • Report Builder – Past, Now and Future

    March 31, 2009 / No Comments »

    The Report Builder technology made its debut in SQL Server 2005. It targeted business users who wanted to author their own (ad-hoc) reports but they were not necessarily technically savvy, meaning that no technical knowledge about database schemas or query syntax was assumed. The Report Builder technology includes a Report Builder Model and Report Builder Client. Similar to other ad-hoc reporting tools, such as Impromptu, the solution developer was required to implement a Report Builder model that is layered on top of the database and deploy it to the server. This model would receive the reports authored by the end users, which were described in an abstract language (SMDL), and automatically translate them to native queries for the supported data sources (SQL Server, Oracle, and Analysis Services).The initial vision, which didn't materialize, was to make the query generator open, so developers can plug in custom query generators for other data...

  • Troubleshooting the SharePoint Add-in for Reporting Services Part 2

    March 30, 2009 / No Comments »

    Today, a co-worker declared a victory after struggling a few days to get Reporting Services 2008 SharePoint integration mode going on Windows Server 2008 (aka Longhorn). I helped of course J It all started with access denied error when attempting to deploy reports to SharePoint. Navigating to the WSS Central Admin Operations page would reveal however that the Reporting Services section is missing. After n-installs of the RS add-in we realized that something is not right. Take-home note: If the Reporting Services section is missing, the Reporting Services add-in failed to install irrespective of the fact that it didn't report any errors. Bummer No 1. Wish No 1: The RS add-in should report errors to users ideally with recommendations. I asked him to troubleshoot the RS add-in installation following the steps in this blog. The most important line from the add-in log file was: **********  User does not have permissions...

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