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.