Posts

Personalized Cubes

Scenario: You need to personalize the captions of calculated members or/and KPIs of an SSAS cube. For example, a solution vendor may need to deploy a cube to multiple clients. Instead of maintaining separate cube definitions, the vendor may prefer to change the captions of certain calculated members and KPIs per client. There wasn’t a good story in SSAS 2005 about such requirements. In SSAS 2008, there are at least two techniques to tackle cube personalization.

First, SSAS 2008 introduces a new Caption property to calculated members and KPIs, which Chris Webb has already written about. Vidas Matelis has provided great examples of using the Caption property. Thanks to this enhancement, you can keep the names of calculated members and KPIs the same but change only their captions. This is the good news.

What if you need to make definition changes programmatically, such as to check the type of the client and change captions accordingly? Well, unfortunately the Caption property is not available in the AMO library as it has not been added to the KPI class. What’s worse is that the CREATE KPI statement doesn’t actually add the KPI definition to the KPIs Collection so you cannot programmatically change the caption by enumerate the AMO Kpis collection and update the KPI definition. To make the story short, the only way I’ve found to update the KPI caption is to update the cube MDX script (Cube.MDXScripts AMO collection). This forces you to resort to search and replace text inside the cube script. Sigh… The following code shows how to use AMO to connect to the cube and change the cube MDX script.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.AnalysisServices;

namespace AMO

{


class
Program

{


static
void Main(string[] args)

{


Server server = new
Server();

server.Connect(“<server>”);


Database database = server.Databases[“<database name”];


Cube cube = database.Cubes[“<cube name>”];

        // assume KPIs are defined in the cube default script


MdxScript mdxScript = cube.MdxScripts[0];


Command command = mdxScript.Commands[0];


// search and replace captions in the command.Text

        // command.Text = …

cube.Update(UpdateOptions.ExpandFull);

}

}

}

The second personalization option is the new Personalization Extension mechanism in the SSAS 2008, which I personally haven’t tried out. Microsoft has provided a sample here. A custom personalization extension is a .NET code that is deployed to the server. It lets you create user-specific session-level calculated members and KPIs without having to change the cube definition. The extension changes are transient and disappear when the user’s session ends.

Extending Report Builder 2.0

As I mentioned in my previous blog, Report Builder 2.0 RC1 has arrived. Although perhaps too early in the game, you may wonder how you can get the most out of Report Builder 2.0 and extend it when the built-in features are not enough. As Report Designer and Report Builder 2.0 share the same design surface, you may believe that they are equally extensible. Unfortunately, this is not quite the case. Specifically, due to time constraints, Report Builder 2.0 supports the following RS extensibility features only in server mode:

  • Custom Data Processing Extensions
  • Custom Security
  • Custom Report Items

Server mode is triggered when you open a report from the server or when the report references a shared data source whose definition must reside on the server. When you preview a report in server mode, the report is processed and rendered on the server. The above extensibility areas are not available in client mode. For example, the user can open a server report that has a custom report item, sets its properties (assuming that the right configuration settings have been made to the Report Builder 2.0 configuration files), and preview the report, as shown in the screenshot. However, the Report Builder 2.0 ribbon will not be extended with the custom report item. Nor the user will be able to author a report from scratch and add the custom report item to the report.

083108_1819_ExtendingRe1

The unfortunately side effect is that if the business user needs to use a CRI on a report, such as the Dundas Map CRI, a developer must author a “shim” report with Report Designer, add the CRI to the report, and deploy the report to the server so the business user can use the report as a starting point.

The only extensibility mechanism that seems to be working in client mode in RC1 is custom code (embedded and external). More than likely, Report Builder 2.0 will get the entire extensibility support of Reporting Services at some point of time. If you want to expedite the process, log your wish on connect.microsoft.com and vote for extensibility features you need Report Builder 2.0 to provide.

Report Builder 2.0 RC1 is Here

Thanks to Robert Bruckner’s blog, I’ve learned that the Report Builder 2.0 (previously known as Report Designer Preview) Release Candidate 1 is available for download. This should be a feature-complete build of Report Builder 2.0, which is scheduled for official release in October.

IMO, Report Builder 2.0 is one of most exciting features of Reporting Services. I expect it to blur the “great divide” between standard and ad-hoc reporting. Based on some preliminary feedback from our business customers, they are very excited about Report Builder 2.0 and they favor it instead of Report Builder 1.0. Despite the unfortunate name, Report Builder 2.0 has very little to do with its predecessor. Instead, it’s very close (almost identical) to the BIDS Report Designer as it supports the full RDL feature set.

You should definately evaluate Report Builder 2.0 for ad hoc business reporting.

Nasty SSAS 2008 Bug

There is a nasty known bug with SSAS 2008, where if a query that requests two measures with Sum and LastNonEmpty aggregate functions side by side and the query uses NON EMPTY (which all OLAP clients use by default), the server forces the measure with the SUM function to display empty.

Steps to repro:

Use the Analysis Services Tutorial cube (Lesson 10) which is included in the SSAS samples. Don’t use the Adventure Works cube because it has measure expressions for certain measures, so changing a measure aggregation function to LastNonEmpty and deploying the cube results gives the error “Errors in the OLAP storage engine: The metadata for the statically linked measure group, with the name of ‘Internet Sales’, cannot be verified against the source object.” That’s a different issue that has been around since SQL Server 2005 but apparently didn’t get fixed.

1.    Open the Analysis Services Tutorial project from Lesson 10

2.    Open the Analysis Services Tutorial.cube in the Cube Designer.

3.    In the Cube Structure tab, expand the Internet Sales measure group and select the Internet Sales-Order Quantity measure.

4.    In the Properties pane, change the AggregateFunction to LastNonEmpty.

5.    Deploy the cube.

6.    Browse the cube with the cube browser or Excel. Create a report that requests Internet Sales Amount and Internet Sales-Order Quantity measures sliced by the Subcategory attribute of the Product dimension.

Notice that the query forces Internet Sales Amount to empty. While waiting for an official hotfix from Microsoft, you can use one of the following workarounds meanwhile:

1. Don’t use the NON EMPTY clause in the query

or

2. Change the following property in the \Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini file.

<DisableCalcExpressNonEmpty>1</DisableCalcExpressNonEmpty>

Not sure what DisableCalcExpressNonEmpty does as it is not documented but appears to work.

UPDATE 8/18/2008

DisableCalcExpressNonEmpty is a new property in AS 2008. Severe performance degradation is likely to result when set to 1, but it should be comparable to AS 2005 performance if not a little better. A hotfix better come out quickly [:(]

Where is Report Builder 2.0?

With the official release of SQL Server 2008, Microsoft retired the RC0 Feature Pack and added the SQL Server 2008 Feature Pack, August 2008 page. Unfortunately, the Report Builder 2.0 link got dropped along the way. Microsoft is aware of this issue and is planning to add a release candidate page for Report Builder 2.0 feature pack available for download toward the end of August. The RTM version of Report Builder 2.0 is expected in Oct’2008 timeframe.

Microsoft SQL Server 2008 Feature Pack, August 2008

Microsoft released a Microsoft SQL Server 2008 Feature Pack, August 2008 page which includes a collection of stand-alone install packages that provide additional value for SQL Server 2008. Strangely, Report Builder 2.0 is excluded and nowhere to be found since the RC0 page has been taken offline.

Update 8/7/2008:

A RC for Report Builder 2.0 feature pack will be available for download toward the end of August

Microsoft Has Released SQL Server 2008

Today, Microsoft announced that official release of SQL Server 2008. Build #10.0.1600.22 has been declared as a SQL Server RTM.

SQL Server 2008 includes major enhancements to the Microsoft Business Intelligence Stack, some of which I have mentioned in my previous posts.

Note that if you have Visual Studio 2008 on your machine, you must wait for a few more days to get the official release of Visual Studio 2008 SP1, which is a required prerequisite to integrate the SQL Server 2008 client tools with Visual Studio 2008.

Enjoy!

Rules Have Changed

An interesting question has pop up on the Katmai RS 2008 discussion list today about how to implement a “green bar” group-level report that alternates background color for all rows in a group instance instead of doing this for each row. I attach the report sample I posted. What makes it interesting is that it demonstrates report variables – a new feature in RS 2008.

Before you start analyzing it, you should know that expression evaluation rules in RS 2008 have changed as a result of the new on-demand processing model. As a result, state in class-level instance variables is discarded as you page through the report which makes maintaining state trickier between page requests. However, RS 2008 introduces report variables that guarantee one-time evaluation semantics. I know this may sound to you like Greek, so let me jump into the implementation details:

1. The report has a EvenRow code-behind function that toggles each time it’s executed.

2. In the Category group (double-click it to access its properties), an EvenRow group level variable is defined that invokes the EvenRow function once per each group instance.

3. The rest is easy. I set the BackgroundColor property for each textbox to use this variable. BTW, the report uses the SSAS Adventure Works cube. [View:/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/greenbargroup.zip]

It’s Done

A quick update on my book Applied Microsoft SQL Server 2008 Reporting Services. I am happy to announce that the work on the manuscript is over and the manuscript RTM’d (released to manufacturing). The paper copy should be available on the reseller sites and brick and mortar stores around the publication date (August 15th). Expect the ebook version around that time as well. Of course, the good thing about the ebook version besides being searchable and portable is that it includes color images. As you would probably agree, color is a good thing when you are studying report design. Video demos are even better, of course, which brings me to the next topic.

While waiting, take a look at the book web page that just went live and check the available resources. Among other things, you’ll find two sample chapters (chapters 1 and 3), the book source code, and video demos, which I captured using the awesome TechSmith Camtasia. The videos are bonus material and are an experimental feature. Video demos are provided for a subset of the report authoring practices covered in the book. I am looking forward your feedback about this feature. Enjoy and I hope you don’t mind my thick as a brick accent J

Finally, the book web page includes a link to a discussion list, where you can make comments about the book and ask book-related questions. The new discussion list will replace my Reporting Services in Action discussion list, which will retire at the end of this month after four years of service. I hope you liked my participation and will consider “upgrading” to the new discussion list and book.

Automating Report Deployment

Here is an awesome tip courtesy to the Craig Guyer’s I Command Thee blog. You probably know that Visual Studio supports project configurations. In BIDS 2008, you can automate report deployment with configurations. For example, the following command deploys the solution using the settings of the QA configuration.

C:\>devenv “C:\Books\RS2008\Code\ch03\Reports\Reports.sln” /deploy QA

This is especially useful when automating deployment to SharePoint because you need to change the report definitions to use absolute paths to external resources, such as shared data sources. BIDS deployment can handle this for you and save you writing custom code to automate deployment.

While we are on the SharePoint deployment topic with BIDS, one nasty bug was introduced late in the SQL Server 2008 cycle that prevents you deploying folders that contain a space, such as Data Sources, to SharePoint from BIDS. When you attempt to do this, you get:

Error rsInvalidItemName : The name of the item ‘Data%20Sources’ is not valid. The name must be less than 128 characters long. The name must not start with slash; other restrictions apply.

If the folder already exists, deployment is successful. Unfortunately, due to time constraints, this bug won’t get fixed in the RTM timeframe. As a workaround, don’t use spaces in the target folders, e.g.; DataSources instead of Data Sources.