• How to Test SSRS MDX Queries in SQL Server Management Studio

    July 6, 2009 / 2 Comments »

    I often need to capture an MDX query that a report sends to Analysis Services and execute it in SQL Server Management Studio (SSMS). I usually do this to understand what parameters the report passes to the query and to troubleshoot the query itself. Unfortunately, SSMS doesn't support parameterized MDX queries so you have to resort to the following technique: Capturing MDX Queries The easiest way to capture an MDX query is to use the SQL Server Profiler. Start SQL Server Profiler from the Microsoft SQL Server <version>-> Performance Tools program group. Click File -> New Trace or press Ctrl+N. In the Connect To Server dialog box, choose Analysis Services, enter your server name, and click Connect. In the Trace Properties dialog box, click the Event Selection tab. Check the Show All Events and Show All Columns checkboxes. Optionally, if you want to see only your events, that is events...

  • Maintaining State in Reporting Services 2008

    June 30, 2009 / 5 Comments »

    Sometimes, more advanced reporting needs may require maintaining state using custom code. Recently I had to implement a rather involved report consisting of two sections: a detail section that used a recursive sum, Sum(Fields!SomeField.Value, , True), and a summary section with additional calculations which would reference some of the aggregated values in the detail section. Since Reporting Services is not Excel, you cannot reference arbitrary fields on a report. So, I had to cache the values from the detail section in a hashtable using custom code so the summary section could obtain these values when needed. The following embedded code gets the job done: Friend Shared _hashtable As New System.Collections.Hashtable() Function GetKey(ByVal id as Long, ByVal column as String) as String return Report.User!UserID & id & column End Function Function AddValue(ByVal id as Long, ByVal column as String, ByVal value As Object) As Object Dim key as String = GetKey(id,...

  • Suppressing Auto-generation of MDX Parameter Datasets

    June 8, 2009 / No Comments »

    There is an unfortunate issue with the MDX Query Designer in SSRS 2008 Report Designer and Report Builder 2.0 where changing the main dataset overwrites the parameter datasets. This is a rather annoying issue because often you need to make manual changes to the parameter datasets. However, when you make a change to the main dataset, the MDX Query Designer wipes out the manual changes and auto-generates the parameter databases from scratch. According to the feedback I got from Microsoft, the issue will be fixed in SQL Server 2008 R2. Meanwhile, there is a simple workaround which requires manually changing the dataset definition in RDL. Basically, you need to add a SuppressAutoUpdate designer switch to the parameter dataset, as follows: <Query> <DataSourceName>Viking</DataSourceName> <CommandText> … <rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate> <rd:Hidden>false</rd:Hidden> </Query>

  • TechEd 2009 North America BIN304 Slides and Code Uploaded

    May 22, 2009 / No Comments »

    I've uploaded the slides and code from my Reporting Services 2008 Tips, Tricks, Now and Beyond breakout presentation delivered on May 13th 2009 at TechEd 2009 North America.

  • TechEd 2009 BI Power Hour Demos

    May 21, 2009 / No Comments »

    The TechEd 2009 BI Power Hour demos, which I blogged about before, are posted on the Microsoft BI Blog. Robert Bruckner has also posted the SSRS demo from the TechED USA 2008 Power Hour, which he renamed to Sea Battle.

  • Transmissions from TechEd USA 2009 (Day 4)

    May 15, 2009 / No Comments »

    I got the final evaluation results from my session. Out of some 15 Business Intelligence sessions, mine was the fourth most attended session with 169 people attending. Based on 35 evaluations submitted, it was ranked as the third most useful session with an average satisfaction score of 3.46 on the scale from 1 to 4. For someone who does presentations occasionally, I'm personally happy with the results. Thanks for all who attended and liked the session! I took it easy today. I attended the Scott Ruble's Microsoft Office Excel 2007 Charting and Advanced Visualizations session in the morning. This was purely Excel-based session with no BI included. It demonstrated different ways to present information effectively in Excel, such as with conditional formatting, bar charts, sparklines, etc. Next, I was in the Learning Center until lunch. In the afternoon, I decided to do some sightseeing and take a tour since I've...

  • 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!

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