• Parameterized Parameter Prompts

    October 18, 2007 / No Comments »

    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. UPDATE 03/27/2009 This feature didn't make it to the release version of SSRS 2008. SSRS 2008 doesn't support expression-based parameter prompts. The feature has been slated to a future release.

  • NEB Revenge in Katmai

    October 9, 2007 / No Comments »

    An advanced SSAS performance-related feature is Non Empty Behavior (NEB) which has been discussed in many places including this Mosha's blog. The issue with NEB in SSSAS 2005 is that you can very easily get it wrong. The server will trust you (this could be an optimistic assumption) and accept a NEB even if it's not correct. Example: CREATE MEMBER CURRENTCUBE.[Measures].[Profit Gap To Prior Period] AS [Measures].[Profit]-([Date].[Calendar].PrevMember, [Measures].[Profit]), FORMAT_STRING = "#,#;(#,#)", NON_EMPTY_BEHAVIOR = { [Measures].[Profit]}, VISIBLE = 1; Here, the intention is to default the expression for the Profit Gap To PriorPeriod calculated member to NULL if Profit is NULL. But the expression may be not be NULL even if Profit is NULL. This can yield incorrect results in 2005. In 2008, optimizations were made that don't use the NEB expression. The net effect is that some calculated members that use wrong NEBs can produce different, albeit correct, results in 2008....

  • Protect UDM with Dimension Data Security Reloaded

    September 21, 2007 / No Comments »

    SQL Server Magazine published the second part of my Protect UDM with Dimension Data Security article in the October issue of the magazine. In this article, I explain how to use MDX expressions to implement dynamic dimension security. I discuss two practical approaches for meeting advanced requirements for securing UDM data. First, I present a solution in which the security policies are stored in a factless fact table. Next, I explain how to leverage SSAS stored procedures to integrate UDM with an external security service. The article is available for public access. Enjoy!

  • Scale-Out Querying with Analysis Services Using SAN Snapshots

    September 12, 2007 / No Comments »

    If your organization can afford SAN, here is another best practices article by Carl Rabeler from the SSAS team. The Scale-Out Querying with Analysis Services Using SAN Snapshots article "describes the use of virtual copy SAN snapshots in a load-balanced scalable querying environment for Microsoft SQL Server 2005 Analysis Services to increase the number of concurrent queries to Analysis Services servers with a rapid response time per query."

  • MCTS Self-Paced Training Kit (Exam 70-445): Microsoft SQL Server 2005 Business Intelligence—Implementation and Maintenance

    September 12, 2007 / 2 Comments »

    Those of you preparing for Microsoft exam 70-445 Microsoft SQL Server 2005 Business Intelligence – Implementation and Maintenance may be interested to know that Microsoft Press has just published a training kit with the same title which I co-authored with Erik Veerman, Dejan Sarka, and Javier Loria from Solid Quality Learning. I wrote four of the SSAS chapters. I also took the actual exam prior to writing the content so I hope the book will help you to prepare and pass 70-445.

  • SSRS 2008 Variables

    September 9, 2007 / No Comments »

    As I mentioned in my Under the Hood post, the SSRS 2008 engine has been redesigned to perform on-demand report processing for better scalability. As a result, textbox values are calculated on-demand every time the containing page is rendered. Irrespective of the on-demand behavior, values of dataset-bound textboxes won't change because the underlying dataset is cached in the report database. Therefore, the end result for dataset-bound textboxes is the same as with SSRS 2005. What about expression-based values? For example, you may have a textbox that references some custom method. Unlike SSRS 2005, the new engine will execute that method each time the page is rendered. This may be or may not be what you want. If you need up-to-date information, the new behavior will be welcome. However, there will be cases, when you may need the old behavior where the custom code is executed once. For example, imagine that...

  • Under the Hood

    August 30, 2007 / No Comments »

    One of the major themes in SSRS 2008 is scalability. A significant effort will be made to ensure that SSRS scales well with large reports. Why is this needed? Recall that in SSRS 2005 (and 2000), report processing is instance-based. This means that the report engine would process the entire report as a snapshot, including textbox values, styles, etc. As a result, SSRS 2005 reports are memory-bound. In general, the memory consumed by SSRS 2005 is proportional to the size of the report. The bigger the report, the more memory it would take. Therefore, large reports won't scale well. In contrast, the SSRS 2008 processing engine processes the reports on-demand. It pre-computes and saves only certain invariants, such as grouping, sorting, filtering, and aggregates. However, textbox values are calculated on-demand every time the containing page is rendered. To test this, drop a textbox on the report and set its value...

  • Identifying and Resolving MDX Query Performance Bottlenecks Whitepaper

    August 29, 2007 / 1 Comment »

    Performance is the most common incentive for using Analysis Services. Yet, despite the advances in the SSAS management tools, optimizing the MDX query performance is still considered as "black magic" by many. Graphical optimization tools similar to the SQL query optimizer and query showplan would definitely help but they are not expected anytime soon. Meanwhile, Microsoft has just published a 60-page whitepaper Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services by Carl Rabeler and Eric Jacobsen. At first sight this resource looks like a must-read for anyone looking for tips to make that query execute a few seconds faster. From the article summary: "To improve the performance of an individual MDX query that is performing poorly, you must first identify the source(s) of the performance bottlenecks in the execution of the query. This requires that you understand how to use Windows and SQL Server 2005...

  • Microsoft Launches Tafiti

    August 24, 2007 / No Comments »

    Microsoft launches a preview of a cool search portal dubbed Tafiti to bring the web search to a new level and give Google a run for its money. From FAQ "Tafiti, which means 'do research' in Swahili, is an experimental search front-end from Microsoft, designed to help people use the Web for research projects that span multiple search queries and sessions by helping visualize, store, and share research results. Tafiti uses both Microsoft Silverlight and Live Search to explore the intersection of richer experiences on the Web and the increasing specialization of search." The Silverlight-based UI looks pretty sleek.

  • Charting the Future

    August 19, 2007 / No Comments »

    It is old news by now that Microsoft has acquired the intellectual rights to the Dundas Reporting Services controls, including the Dundas chart, map, gauge, and calendar. At this point, it is not known if all controls will make it to Katmai but the chart will definitely will. The July CTP (CTP4) includes a preliminary version of the Dundas chart that has been converted from a custom report item to a native region. Many of the properties are not hooked yet but you can witness some of the new enhancements. Although somewhat unpolished, the following report demonstrates some of these features. The report features two charts in one – a column sales chart and a scattered profit chart. They are projected on two value axes. Having a secondary axis wasn't supported by SSRS 2005. Also, the first value axis has axis breaks. Since the Bikes sales exceed by far the...

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