Report Snapshots and rsReportNotReady

/0 Comments/in /by

Two very useful features of Reporting Services (version 2000 and above) are snapshot execution and report history. Snapshot execution allows you to cache the report execution. Optionally, you can keep the cached reports in history, e.g. if the user wants to keep a copy of the report for historical analysis.

Once a report is enabled for snapshot execution, you can generate the history snapshot by invoking the CreateReportHistorySnapshot API assuming you need to do so programmatically, e.g.:

historyID = managementProxy.CreateReportHistorySnapshot(reportPath, out warnings);

However, if your code just calls CreateReportHistorySnapshot and the end user requests the report by clicking on the report link in the Report Manager or SharePoint (if the server is configured for SPS integration with SQL Server 2005 SP2), the user will get:

The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (rsReportNotReady)

This error is generated despite the fact that the report has history runs generated already. What’s going on? Why doesn’t the report server returns the last history run automatically? It turns out that the report snapshot and history runs are kept in two different places in the report catalog and are treated differently. To avoid this error change your code as follows:

managementProxy.UpdateReportExecutionSnapshot(reportPath);

ReportHistorySnapshot[] history = managementProxy.ListReportHistory(reportPath);

historyID = history[history.Length – 1].HistoryID;

The first line of code generates the report execution snapshot so clicking on the report link in the Report Manager would show the last snapshot. Assuming the report history for the report is enabled and “Store all report execution snapshots in history” option is enabled, this will also copy the report snapshot to the report history. The second line retrieves the definitions of all report history runs. Finally, the third line gets the last history identifier to return to the client if this is what the client is expecting.

Visual Studio 2005 Team Edition for Database Professionals

/0 Comments/in /by

The newly released CTP7 of Visual Studio 2005 Team Edition for Database Professionals (aka DataDude) now supports bi-directional schema updates. Once you generate the scripts from your database schema, you can choose either the database or the scripts as a source of changes and commit the changes to the target. Visual Studio 2005 Team Edition for Database Professionals requires Visual Studio 2005 Team Edition.

SQL Server 2005 SP2 CTP 2 Build Released Today

/0 Comments/in /by

The second CTP (Community Technology Preview) build of the forthcoming SQL Server 2005 Service Pack 2 was released today. Besides bug fixes, SP2 includes major BI features and enhancements including:

Reporting Services

  • SharePoint 2007 integration – this one is a true enzilla (enhancement Godzilla) and deserves a separate post. Suffice to say that unlike the previous Explorer and Viewer webparts, SP2 will allow you to move the report catalog to SharePoint to enable publishing, viewing, and managing SSRS 2005 reports within SharePoint 2007.
  • Report Builder support for Oracle 9.2.0.3 or above (previously only SQL Server and Analysis Services 2005 were supported for Report Builder models)
  • New .NET Data Provider for Hyperion 9.3 BI+ Essbase Enterprise Analytics
  • Select All option has been restored with multi-valued parameters (it was disabled in SP1 for performance reasons).

Analysis Services

  • Better Excel 2007 integration – advanced filtering, cube formulas, offline cubes.
  • Data mining add-ins for Excel 2007 — Table Analysis Tools for Excel, Data Mining Client for Excel, Data Mining Templates for Visio
  • Performance improvements with subselects, arbitrary shapes, running sum calculations, visual totals, ROLAP dimensions, cell writeback, many-to-many dimensions, 64-bit NUMA hardware, semi-additive measures and unary operators.
  • Supportability enhancements – enhanced memory dumps, filtered dumps, best practices analyzer

More information about what's new in SQL Server 2005 SP2 CTP2 is available in the readme file.

Office 2007 RTM’ed

/0 Comments/in /by

Microsoft Corp. today announced the completion of the 2007 Microsoft® Office system code and confirmed its release to manufacturing (RTM).

Linked Attribute Hierarchies

/0 Comments/in /by

In UDM, the cube space is defined by attribute hierarchies. Dimensions are just logical containers of attribute hierarchies. It will be great if the next release of SQL Server (Katmai) could expand further on the attribute nature of UDM and solve some nagging issues that modelers currently face.

Let’s consider an example. Say, you have Geography and Customer dimension tables (see the AdventureWorksDW database). It is logical to expect that end users may be willing to browse data by the Geography-Customer hierarchy. As common this requirement is, modelers today need to make some tradeoffs to meet this requirement.

To start with, you could embrace the star schema and build two UDM dimensions (Geography and Customer) on top of the corresponding dimension tables. Then, the end users can drop these two dimensions side-by-side in their favorite OLAP browser to slice the data by Geography and Customer. Behind the scenes, the server will cross-join these two dimensions . Although cross-joing dimensions may meet the business need, there are a couple of well-known issues with this approach. First, you won’t be able to define easily useful hierarchy-related calculations, such as ratios of children to parent because there are no user-defined hierarchies. Second, cross-joining large dimensions may impact performance.

Alternatively, you could decide to create a user-defined hierarchy (aka multi-level hierarchy) that spans Geography and Customer attributes. This is where the things get trickier. Assuming a star schema, both dimension tables would join the fact table. To build a Customer dimension spanning both tables, you need to bridge them via the fact table – a definite no-no situation. The other solution is to duplicate the Geography columns to the Customer dimension table but this leads to duplication of database and UDM storage and will complicate the ETL processes.

At this point, ditching the star schema and showflaking the Geography table off the Customer table starts to look appealing. Now, you could build the Customer dimension on top of the Geography and Customer dimension tables – the approach that the sample AdventureWorks UDM takes. The first tradeoff is that you may need to fight an uphill battle with fans of the “classic” dimensional modeling that swear by star schemas. Second, this approach still results in duplication of UDM storage. That’s because, when the Geography and Customer dimensions are processed, the server will still build all dimension attribute hierarchies irrespective of the fact that some of them of identical. For example, if you have a City attribute (from the Geography table) in the Customer dimension, the server will build two City attribute hierarchies – one when building the Geography dimension and another one when building the Customer dimension.

It will be nice if a future SSAS version supports linking attributes from one dimension to another. With this feature, the database schema (star or snowflake) could become irrelevant. The City attribute from the Geography dimension could be linked to the Customer dimension. Of course, the cube schema will still be constrained by the underlying database schema and table relationships. However, cross-dimension attribute relationships will certainly enable more flexible scenarios.

Drill Through Your Way

/0 Comments/in /by

One of the most useful features of the SSRS 2005 Report Builder is the ability to generate reports on the fly. As long as there is a navigational path in the model, the end user can click on a drillthrough-enabled attribute to see more details. This feature is officially called infinite drillthrough.


Sometimes, the automatic drillthrough reports may get in the way. For example, there are performance implications when this feature is used with UDM as a data source. Or, you may need the drillthrough report to bring data from a different data source than the one the Report Builder model is built upon. To meet this requirement, you can wire up a custom drillthrough report. For example, let’s assume that your model has Customers and Accounts entities where a customer can have one or more accounts. You need to define your own drillthrough report that shows the customer’s account details when the end user clicks on the customer entity.


As a first step, you need to create the drillthrough report. If the report will fetch data from the model data source, use the Report Builder to author the report. Once the report is ready, go to the Report->Report Properties menu and select the “Allow the users to drill to this report from other reports” checkbox. This causes the Report Builder to add two input parameters (DrillthroughSourceQuery and DrilthroughContext) to the report. The Report Server will pass the semantic query and execution context to these parameters at runtime (more on this in a moment). Next, save the report to the Report Server. Start SQL Server Management Studio and open up the model properties. Select the Drillthrogh reports page (see attached screenshot) and set the Single Instance or Multiple Instance properties to point to the custom drillthrough report(s) you’ve created.


But what f you want your custom drillthrough report to fetch data from a different data source? Perhaps, your model is based on UDM but you need to display data from the relational database? Fear not, you have a complete control over the drillthrough report as long as it has DrillthroughSourceQuery (you don’t really need this one but it must be present) and DrillthroughContext string parameters. You will most likely need the identifier of the clicked entity in the Report Builder so you can filter data in the drillthrough report. The identifier is passed in XML format under the DrillthroughContext parameter, e.g.:


<DrillthroughContext xmlns=”http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling”>


…


<GroupingValues>


<GroupingValue Name=”Customer”>ABtbQ3VzdG9tZXJdLltDdXN0b21lcl0uJlsyNV0=</GroupingValue>


</GroupingValues>


</DrillthroughContext>


As you can see, the customer identifier is Base64-encoded, so it will take some extra effort to extract and decode it. You can write a simple custom function to load the DrillthroughContext XML fragment in XML DOM and navigate to //GroupingValue[@Name=’Customer’] (watch out for the default namespace!). Then, you can use System.Text.UTF8.GetString(System.Convert.FromBase64String()) to decode the value. For example, in our case, the identifier may look like [Customer].[Customer].[Customer].&[134] (recall that we target UDM as a data source). Finally, you need to parse this string to get to the 134 nugget of truth. Once it is yours, the rest is easy.

ProgressTracker Bug

/0 Comments/in /by

Some late code changes to the ProgressTracker CRI sample featured in my MSDN Magazine article "Jazz Up Your Data Using Custom Report Items In SQL Server Reporting Services" have introduced a bug that causes ProgressTracker to ignore changes to the CRI color and border style whent the reoprt is rendered. To fix:

  1. Open the ProgressTrackerRenderer.cs (or ProgressTrackerRenderer.vb if you use the VB.NET version)
  2. Locate the Initialize() function and make the following code changes:

//_progress.ProgressColor = Shared.GetColorFromString(LookupCustomProperty(_cri.CustomProperties, Shared.PROP_PROGRESS_COLOR, Drawing.Color.Salmon.Name));

//_progress.BorderColor = Shared.GetColorFromString(LookupCustomProperty(_cri.CustomProperties, Shared.PROP_PROGRESS_COLOR, Drawing.Color.Gray.Name));

_progress.ProgressColor = Shared.GetColorFromString(_cri.Style["Color"].ToString());

_progress.BorderColor = Shared.GetColorFromString(_cri.Style["BorderColor"].ToString());

   3. Close the Reports project (if it is open) and rebuild the ProgressTrackerCRI project to deploy the code changes.

The net result of making these changes will be that the CRI will reuse the Color and BorderColor standard RS properties instead of trying to find corresponding custom properties in RDL. Thanks to Sorin Sandu for reporting this.

And the MVP Award Goes To

/0 Comments/in /by

I learned today that my MVP award has been renewed. This will be my third year as an MVP for Windows Systems – SQL Server.

Transcend T-SQL Limitations with SQL Server 2005 CLR Objects

/0 Comments/in /by

One of the coolest SQL Server 2005 feature is .NET CLR objects. When use wisely, CLR integration can solve many nagging problems with T-SQL. For example, you cannot pass columns from an outer SQL statement to a TBF even though it returns one row. Or, for some obscure reason, you cannot use dynamic execution (EXEC statement) inside a scalar –valued function. Yet, you may need to use the same scalar-valued function with an arbitrary column.

In comparison, the sky is the limit about what a CLR stored procedure or a CLR UDF can do. Here is an extract from a real-life CLR UDF written in C# that returns the YTD aggregated value for a given measure:

[Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read)]

public static SqlDecimal YTD(int companyID, DateTime financialPeriod, string measure) {

  using (SqlConnection conn = new SqlConnection("context connection=true")) {

     conn.Open();

     SqlCommand cmd = new SqlCommand(String.Format("SELECT SUM({0}) " +

     "FROM    <some table here> (NOLOCK) " +

     "INNER JOIN <some other table> (NOLOCK) " + "… " +

     "WHERE FinancialPeriod = @FinancialPeriod" ", measure), conn);

     cmd.Parameters.Add(new SqlParameter("@CompanyID", companyID));

     cmd.Parameters.Add(new SqlParameter("@FinancialPeriod", financialPeriod));

     return ToSqlDecimal(cmd.ExecuteScalar()); }

}

private static SqlDecimal ToSqlDecimal(object value) {

   return value is System.DBNull ? SqlDecimal.Null : SqlDecimal.Parse(value.ToString());

}

Here, the function takes a company identifier, financial period, and the name of the measure to be aggregated as input arguments. The ADO.NET SqlCommand object takes care of executing the query (a boiler-plate ADO.NET code). Note the SystemDataAccess = SystemDataAccessKind.Read attribute that decorates the function. If you omit it, you will be greated with the following exception at runtime:

This statement has attempted to access data whose access is restricted by the assembly.

Once deployed, the function can be called as a regular T-SQL UDF, e.g.

SELECT <FULLY QUALIFIED CLASS NAME>.YTD(1, '7/1/2006', "Sales"),

assuming you have a Sales decimal column in your table or view.

Who knows, perhaps one day, we will be able to ditch T-SQL whatsoever in favor of .NET languages. I know I will be the first one to jump.

Office 2007 Business Intelligence Videos

/0 Comments/in /by

Microsoft has released several cool Business Intelligence-related webcasts that target the forthcoming Office 2007. The webcasts are taken from the corresponding breakout session delivered at the Microsoft Office System Developers Conference 2006.