• RDL Object Model on the Horizon

    August 14, 2007 / 3 Comments »

    As I mentioned in a previous blog, SQL Server 2008 will probably include an RDL Object Model. This is great news for developers who generate report definitions programmatically. No more custom RDL object models as the one I talked about during my TechEd presentation. The early incarnation of the promised object model is included in the July CTP (CTP4) and resides in the \Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin\Microsoft.ReportingServices.RdlObjectModel.dll assembly. DISCLAIMER Before rejoicing too much, recall the usual disclaimer that everything is in a flux and a subject to change. Although here, the RDL Object Model may very well disappear in the final bits. Note that I disclaimed myself let me introduce you to the RDL Object Model (don't try to find it in BOL; long live .NET Reflector!). using System; using System.IO; using Microsoft.ReportingServices.RdlObjectModel; using Microsoft.ReportingServices.RdlObjectModel.Serialization; namespace RDL { class Program { static void Main(string[] args) { string idef = @"C:\Reports\Sales...

  • How to Get Extended Properties with SSAS OLE DB Provider

    August 14, 2007 / No Comments »

    Many report developers find the SSAS data provider too restrictive for authoring reports from UDM. Instead, they replace it with the native Microsoft OLE DB Provider for Analysis Services 9.0 which gives them maximum flexibility at the expense of convenience (the query has to be hand-generated, parameters are not supported, extended properties are not available, etc). By default, the OLE DB provider doesn't return the extended cell properties, such as format and color settings. Thanks to Robert Bruckner from the SSRS team, here is a precious tip to get the extended properties: Append Extended Properties="ReturnCellProperties=true" to the OLE DB connection string of your data source, e.g.: Provider=MSOLAP.3;Data Source=localhost;Initial Catalog="Adventure Works DW";Extended Properties="ReturnCellProperties=true" Request the cell properties in your MDX query as the SSAS provider does, e.g.: SELECT NON EMPTY { [Measures].[Internet Sales Amount], … } ON COLUMNS, NON EMPTY { [Sales Territory].[Sales Territory Group].[Sales Territory Group].ALLMEMBERS , …. } FROM...

  • SQL Server Katmai Reporting Services Forum

    August 14, 2007 / No Comments »

    In case you've missed it, the SSRS team has started a SQL Server Katmai Reporting Services forum and eagerly awaits your feedback.

  • Tablix – The Crown Jewel

    August 13, 2007 / No Comments »

    One of most important enhancements coming up in SQL Server 2008 Reporting Services is the new tablix region. In fact, I dare to predict that many folks will upgrade to SSRS 2008 just to get Tablix. What's tablix anyway? Tablix = Table + Matrix So, tablix combines the flexibility of the table region and the crosstab reporting features of the matrix region. Actually, a tablix is table, matrix, and list all in one but I guess the SSRS team decided to keep the name short. Don't be fooled by the old toolbar buttons because they just provide entry points to the tablix region. If you click the Matrix button, you will get a tablix region preconfigured for crosstab reporting, when you click the Table button you get a table report, and List button will give you a free-form Tablix. So, this is simple. In SSRS 2008, tablix powers all reports....

  • The New Kid on the Block (aka Stand-alone Report Designer)

    August 7, 2007 / No Comments »

    The need for a stand-alone Report Designer is real. True, developers, including myself, enjoyed the VS.NET Report Designer. But novice users were overwhelmed with the complexity of the VS.NET IDE environment. In addition, not all IT shops rejoiced over the idea to install VS.NET or BIDS just to author reports. Enters the SSRS 2008 stand-alone report designer. As its name suggests, you run this designer completely outside VS.NET. In fact, in CTP4, VS.NET has not been yet integrated with the new designer (it still uses the RS 2005 designer). Therefore, to get the new RDL enhancements in CTP 4, such as tablix, you need to use the stand-alone report designer. Note that stand-alone doesn't mean embeddable. While a future release make this possible, it is unlikely that you will be able to embed the SSRS 2008 designer in custom .NET application. You launch the stand-alone report designer from the Report...

  • The Perfect Host

    August 4, 2007 / No Comments »

    This is my first blog about SSRS 2008. The July CTP (CTP4) of SQL Server 2008 (aka Katmai) includes the new hosting model of Reporting Services. Gone is the dependency to IIS and all the management headaches associated with it. Instead, the SSRS Windows service pulls a nice trick by hosting the http.sys kernel-mode device driver which listens for HTTP requests. Behind the scenes, the SSRS Windows service hosts application domains for the Report Manager and Report Server and forwards the incoming requests to them. You reserve URL addresses for Report Manager and Report Server by using the Reporting Services Configuration utility. If you have ever set up a web site in IIS, you know everything you need to reserve an URL address. The URL address consists of IP address, TCP port, and vroot. You can specify any unreserved port (in Vista and Windows Server 2003+ you can have SSRS...

  • Scale-Out Querying with Analysis Services Article

    August 1, 2007 / No Comments »

    I've come across a good best practices article about scaling out SSAS by Denny Lee and Nicholas Dritsas. It describes how to set up a load-balanced scalable querying environment for Microsoft SQL Server 2005 Analysis Services so that you can handle a large number of concurrent queries to your Analysis Services servers.

  • July CTP of SQL Server 2008 Is Out

    August 1, 2007 / No Comments »

    July CTP of SQL Server 2008 (Katmai) is out. On the BI side of things: New SSRS Report Engine "Improvements represent the two major infrastructure changes for Reporting Services. Reporting Services enhances the processing engine and rendering extensions to enable new functionality, such as Tablix support, and scalability as well as remove the dependency on IIS. Additionally, new report designer and configuration tool are provided that improve usability and workflow for RS customers." Analysis Services Time Series This improvement adds a new time series forecasting algorithm (ARIMA: Auto Regressive Integrated Moving Average) to the data mining algorithm suite that provides more stable long term predictions.

  • Empty Affairs

    July 13, 2007 / No Comments »

    If you have a cube with large dimensions you may have come across the following error: The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples. The culprit is the NON EMPY (or NonEmpty), as mentioned in the Chris Webb's blog and Mosha Passumansky's blog. It turns out that NonEmpty simply gives up when the number of tupples in the cross-join members exceeds 4GB. The problem is all Microsoft SSAS clients (Excel, Reporting Services, Report Builder, etc.) are blissfully unaware of the trap to come and would happily cross-join dimension members as you add more dimensions to the report. Take for example the following (simplified) query produced by the Report Designer when the report shows the customer name and its accounts: SELECT NON EMPTY { [Measures].[Sales] } ON COLUMNS, NONEMPTY { ([Customer].[Customer].[Customer].ALLMEMBERS * [Account].[Customer Account Number].[Customer Account Number].ALLMEMBERS)} ON ROWS FROM <some cube> WHERE...

  • SQL Server 2008 (Katmai) to Launch February 28th 2008

    July 12, 2007 / No Comments »

    From the latest MS press release "In anticipation for the most significant Microsoft enterprise event in the next year, Turner announced that Windows Server 2008, Visual Studio 2008 and Microsoft SQL Server 2008 will launch together at an event in Los Angeles on Feb. 27, 2008, kicking off hundreds of launch events around the world." Whoa, I didn't expect Katmai so soon. Well, Euan Garden is telling us that a marketing launch doesn't mean all that much after all [;)]. It will probably be a few more months before Katmai RTMs.

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