Prologika Forums

People Ready BI

tlachev — Wed, 08 Oct 2008 01:20:36 GMT

At the BI Conference in Seattle, Microsoft announced the future roadmap for next BI-oriented SQL Server release, nee Kilimanjaro, that will focus on three major teams:

• Self Service Analytics

• Self Service Reporting

• Sharing, Collaboration & Management

The Self Service Analytics will encompass projects Madison (a share-nothing MPP scale-out version of SQL Server, powered by DATAllegro technology) and Gemini, which I mentioned before.

The Self Service Reporting is where Reporting Services.NEXT will come in, of course. Microsoft announced the acquisition of 90 Degree Software. In case you are not familiar with this company, 90 Degree Software has built a very comprehensive report designer that is very similar to Report Builder 2.0. What I expect from this acquisition is more end-user enhancement in Report Builder 2.0 and improved Analysis Services integration. Expect also a shared report library that lets the end user save and share report elements and templates with other users.

The SoftArtisans acquisition will finally materialize in Kilimindjaro to empower business users to author reports inside Microsoft Office. Expect the preview bits of Kilimanjaro in 1H2009 with release in 1H2010. Watch the keynote presentation online.

Memory-based Dimensional Model (MDM) on the Horizon

tlachev — Tue, 07 Oct 2008 01:27:00 GMT

As the news arrived today (see Mosha Pasumansky blog, Chris Webb blog, and Marco Russo blog) , the BI Conference revealed what has been brewing for a while in the Microsoft BI nest. Project Gemini, which one may call Memory-based Dimensional Model (MDM) to draw a comparison with the Unified Dimension Model (UDM), will let business users create ad-hoc cubes that are stored in memory. This will let Microsoft compete with other BI players, such as the Cognos TM1 offering which Cognos acquired from Applix. Accidentally, I dare to predict that MDM will be very similar to TM 1 (watch a demo here) but of course better J.

While I need to get my hands on this Gemini thing to say something worthwhile, I really hope that existing UDM cubes could benefit from it as well, especially in terms of performance. Today, folks are pushing SSAS to its limits. A dashboard page, for example, may need to execute many queries and crunch huge volumes of data to present trend graphs within seconds. It will be cool if Gemini lets you cache subcubes in memory to speed up these scenarios. For example, if you need to implement a bunch of customer-related KPIs, it will be nice if you could tell Gemini to cache in memory or materialize to disk the pre-aggregated at the customer level data and which dimensions can be used to slice these KPIs.

What about giving the business users the option to create ad-hoc cubes? I have to admit I have mixed fillings about this. The term "OLAP" alone is known to cause severe brain crunch to many users. I am a bit skeptical that "off you go, start building your own cubes in Excel" philosophy will really fly. If you package a cool wizard that hides some of the dimensional model complexity, how would verify that the results are indeed correct so you don't end up with as many versions of the truth as the number of users? How would teach end users MDX to create their own calculations? Not sure how much of your time Gemini will really save if this is its major selling point. But again I may change my point of view as details unfold as life often proves me wrong. Meanwhile, long live MDM and Kilimanjaro, which is the code name for SQL Server.NEXT, scheduled for H1 2010!

Cumulative Update Package 1 for SQL Server 2008

tlachev — Tue, 23 Sep 2008 17:20:10 GMT

Microsoft has released the first for SQL Server 2008 cumulative update package. Among other issues, it fixes the SSAS bug with requesting measures with mixed aggregation functions which I previously reported.

Sorting Dates in a PivotTable Report

tlachev — Thu, 11 Sep 2008 01:06:00 GMT

Issue: You use Excel as an OLAP browser connected to an SSAS cube. You've defined the name of the Month attribute in the Date hierarchy in the format MMM-YYYY. While months are sorted in an ascending order in Excel, descending sorting doesn't work; or rather Excel sorts them as strings. That's because Excel interprets these captions as strings instead of dates even if you set the Value property of the Month attribute to a field of DateTime type.

Workaround: You can use the Excel custom sorting feature as a workaround while waiting for Excel to become a better OLAP citizen:

Connect to the cube and select the Month attribute to get all months displayed in Excel. Select all month cells.
Click the Excel Office Ribbon button.
In the Popular tab, click the Edit Custom Lists button.

In the Custom Lists dialog box that follows, click Import button to create a new custom list for the dates in the format MMM-YYYY. Click OK

As you can see, there is a custom sorting list for months in the format MMM, so descending sort will work if the month captions are in MMM format. However, the end user won't be able to tell the months apart if the user requests only the Month attribute on the report. So, you kind of have to pick up your own poison.

Personalized Cubes

tlachev — Tue, 02 Sep 2008 01:33:05 GMT

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

tlachev — Sun, 31 Aug 2008 18:20:11 GMT

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.

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

tlachev — Mon, 25 Aug 2008 23:12:00 GMT

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.

Where is THE Book?

tlachev — Fri, 22 Aug 2008 19:40:55 GMT

A few readers are asking about the whereabouts of my book Microsoft SQL Server 2008 Reporting Services, so I felt I need to write a short status report. The book is printed and it's on stock with the distributor. It's making its way slowly-y-y to the retail network. It turns out there are lots of things that need to happen before the book is finally listed in-stock with retailers, such as Amazon. I am frustrated by this fact but there is nothing I can do to speed up the process. Hopefully, the book will pop within a few days so you can get it at a discounted price.

Anyhow, if you need the paper copy as soon as possible, you can order it from the distributor at the full price and you will get it within 2-3 days. Alternatively, you can order the e-book version, which is already available retail, such as on ebooks.com, diesel-ebooks.com, etc.

Finally, don't forget that two chapters are freely available on the book page, plus video demos, to get you started with RS 2008.

Nasty SSAS 2008 Bug

tlachev — Fri, 15 Aug 2008 12:11:00 GMT

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

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

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?

tlachev — Thu, 14 Aug 2008 01:32:36 GMT

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.

Static Credentials

tlachev — Mon, 11 Aug 2008 14:08:56 GMT

A new week comes with new issues... I ran into an interesting issue today with Windows integrated security and SharePoint. I was troubleshooting an issue on a behalf of a Windows user that connects to an SSAS server. To repro the issue, I used the SharePoint "Sign in as a different user" feature. To facilitate testing, I decided to save the password in the standard Windows authentication dialog that follows ("Remember my password" checkbox).

After this "convenient" setup, to my surprise all calls to that server went the credentials of that user, including connections to the cube from SQL Server Management Studio and Excel! For example, when I connected to the SSAS database with SSMS and attempted to manage the server, I was greeted with the following message although I have admin rights to the server:

The connected user is not an Analysis Services server administrator. Only an administrator can make changes to server properties. (Microsoft.AnalysisServices.ManagementDialogs)

The SQL Server Profiler revealed that indeed the server impersonates any call under that user. Not sure what exactly happens when you save the password but be careful of this issue with "static" credentials. To correct the issue, use the Sign in as a different user feature again but don't check the Remember my password option.

Microsoft SQL Server 2008 Feature Pack, August 2008

tlachev — Thu, 07 Aug 2008 14:24:00 GMT

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

tlachev — Wed, 06 Aug 2008 17:47:00 GMT

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

tlachev — Wed, 06 Aug 2008 02:26:00 GMT

(Please visit the site to view this media)(Please visit the site to view this media)(Please visit the site to view this media)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. (Please visit the site to view this media)

Yet Another Post About Analysis Services HTTP Connectivity

tlachev — Sat, 02 Aug 2008 02:30:55 GMT

With risk to iterate the obvious, here is a lesson learned from the trenches:

Scenario: You want to let external users browse an Analysis Services cube over the Internet.

Setup: You have followed the steps in the Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003 article by Edward Melomed to set up Analysis Services for HTTP connectivity.

Issue: Windows integrated security doesn't work.

After a long battle where countless options have been tried, I have to admit a defeat. In our case, we've set up identical Windows local accounts on the SSAS server. This worked fine when connecting Report Builder 2.0 to the cube from our home machines. However, it appeared that the firewalls that our clients use make a minced meat of Windows integrated security. Strangely, Excel connects without a problem to the cube using Windows integrated security. However, Report Builder 2.0 chokes big time. I guess this has to do with differences between OLE DB (which Excel uses) and ADOMD.NET (used by Report Builder 2.0).

So, we had to give up on Windows integrated security over Internet. Instead, we went for Basic security with SSL, as follows:

Configure the msmdpump virtual folder for Basic security (this should be the only available security option).
Install the SSL certificate on the IIS server.
In Report Builder 2.0, set up a new data source that uses the Microsoft SQL Server Analysis Services. Click the Edit button on the Data Source Properties dialog box.

In the Connection Properties dialog, enter the URL to the msmdpump.dll, such as https://adventure-works/olap/msmdpump.dll.
Enter the user credentials. Click the Save My Password to avoid being asked to retype the password.
Here is an important step if you want to populate the Connect to a Database drop-down list. Click the Advanced button and locate the Integrated Security setting. Select and clear the SSPI default setting. Click OK.
Back to the Connection Properties dialog, expand the Connect to a Database drop-down list. You should see the list of the SSAS databases the user has rights to access. Select a database.
Click the Test Connection dropdown. The connection should succeed.

The data source is set up now. You can proceed with setting up a dataset with the MDX Query Designer.

A Must-read Reporting Services Blog

tlachev — Mon, 21 Jul 2008 19:53:48 GMT

Robert Bruckner on the Reporting Services team has started a blog. Robert oversees several key areas of Reporting Services, including Report Definition Language (RDL), data processing, report processing, data visualization, and performance/scalability. Many of you have gotten help from Robert on the Reporting Services discussion lists to which he frequently contributes. So, this sure it's going to be a must-read blog as his first posts (report variables and scalability) show.

RS Blog Roll

Bob Meyers' Blog

(http://blogs.msdn.com/bobmeyers)—Bob Meyer is a Program Manager on the Reporting Services team who is responsible for Report Builder 1.0.

Brian Welcker's Blog

(http://blogs.msdn.com/bwelcker)—Brian Welcker is a former Group Program Manager on the Reporting Services team.

Chris Baldwin's Blog

(http://blogs.msdn.com/chrisbal)—Chris Baldwin is a Program Manager on the Reporting Services team who oversees the report rendering area.

Chris Hays's

(http://blogs.msdn.com/chrishays)—Chris Hays is an architect on the Reporting Services team and oversees the Report Definition Language.

John Gallardo's Blog

(http://blogs.msdn.com/jgalla)—John Gallardo is a Software Development Engineer on the Reporting Services team who is responsible for the report server.

Lukasz Pawlowski's Blog

(http://blogs.msdn.com/lukaszp)—Lukasz Pawlowski is a Program Manager on the Reporting Services team who is primarily responsible for the management feature of the product.

Reporting Services Team Blog

(http://blogs.msdn.com/sqlrsteamblog/)—A collective blog of the Reporting Services team.

Reporting Services User Education Blog

(http://blogs.msdn.com/rosettaue)—From the user documentation team which is responsible for creating all the documentation that ships with the product.

Robert Bruckner's Blog

(http://blogs.msdn.com/robertbruckner)— Robert Bruckner is a Software Development Engineer on the Reporting Services team who oversees Report Definition Language (RDL), data processing, report processing, data visualization, and performance/scalability.

Russell Christopher's Blog

(http://blogs.msdn.com/bimusings)—Russell Christopher is a Business Intelligence consultant with Microsoft who posts great insights about working with Reporting Services.

It's Done

tlachev — Mon, 21 Jul 2008 03:11:00 GMT

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 15^th). 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

tlachev — Sat, 12 Jul 2008 14:31:02 GMT

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.

SQL Server 2008 Available on August Price List

tlachev — Wed, 09 Jul 2008 16:54:00 GMT

Courtesy to Francois Ajenstat, Director of Product Management for Microsoft SQL Server, we know that SQL Server 2008 is on the August price list. What this probably means according to my interpretation is that the SQL Server 2008 RTM date is close. More than likely, SQL Server 2008 will be released in August.

Textbox on Steroids

tlachev — Thu, 19 Jun 2008 18:13:18 GMT

One of the new features in Reporting Services 2008 that debuted in the SQL Server 2008 RC0 build is the enhanced textbox report item. In previous releases, if you wanted to mix static and dynamic expression-based text, you either needed multiple textboxes or a Visual Basic expression to concatenate strings together. The first approach led to textbox "explosion". The disadvantage of the second approach was that you couldn't format string fragments inside the same textbox independently.

In Reporting Services 2008, the textbox report item has been redesigned to support multiple bands of text. The screenshot shows a report title of a sales order report. In the past, you would need two textboxes (or three if you wanted different formatting for the sales order number). You may be surprised to find that the entire title is implemented as a single textbox with two paragraphs. The second paragraph combines static text (Order #:) with dynamic text ([SalesOrderNumber]), which defines a placeholder for a dataset field value. Each fragment can have its own format settings. Thanks to these enhancements, you'll find that by moving to Reporting Services 2008, you need fewer textboxes and you need to write less often expressions that concatenate text. Moreover, the new textbox lets you implement report solutions, such as mail merge, that were difficult or impossible to implement with previous releases.

Many report authors will rejoice learning that the textbox report item now supports a subset of HTML tags for formatting the text content. This is also known as rich formatting (not be confused with RTF which is not supported). You can import static HTML text or bind the textbox to a dataset field. For instance, if the dataset field includes HTML tags, such as <b>SO50750</b>, you can configure the textbox to interpret these tags and display the sales order number in bold.

SQL Server 2008 RC0 Product Samples Refreshed

tlachev — Wed, 11 Jun 2008 12:25:28 GMT

Microsoft has refreshed the product samples and AdventureWorks databases. I still have to figure out what's the AdventureWorksAS2008 database that is not released yet.

Microsoft SQL Server 2008 Feature Pack RC0, June 2008

tlachev — Tue, 10 Jun 2008 11:51:00 GMT

Today, Microsoft released an updated feature pack for SQL Server 2008 RC0. Among other things, it includes:

A web downloadable installer for Report Builder 2.0 which is not included anymore with the SQL Server 2008 setup
An updated version of the RS add-in for SharePoint
Analysis Services 10.0 OLE DB Provider

SQL Server 2008 Release Candidate 0 (RC0)

tlachev — Sat, 07 Jun 2008 12:30:00 GMT

Microsoft declared build #1442.32 to be the official SQL 2008 RC0 build and made it publicly available.

Key SSRS improvements in this build:

Rich text formatting. You can now format the text inside a textbox with different styles.
Report Wizard to let the user auto-generate the report definition.
Fully functional MDX Query Designer.
An updated build of the Report Builder 2.0 (aka Report Designer Preview) will be available via a web download. The URL link is not known at this point.

BI Salary Survey

tlachev — Tue, 20 May 2008 18:46:00 GMT

Not sure how much your BI expertise is worth? The 2008 TDWI Salary, Roles, and Responsibilities Report TDWI survey may help.

Intelligencia Query

tlachev — Thu, 15 May 2008 02:37:48 GMT

Chris Webb, a fellow MVP and MDX guru, who's blog is a must-read for all SSAS junkies, was kind enough to let me take a look at the Intelligencia Query product he's been working on for a while with Andrew Wiles. Chris announced the product back in April which is now selling as a commercial offering.

Now, lots of things have been said about the current state of the SSRS-SSAS integration, which is to say the least wanting. In a previous blog, I wrote "In my opinion, the biggest challenge the Microsoft BI initiative faces today is the inadequate support for Analysis Services." Alas, we won't witness an improved SSRS-SSAS integration in SQL Server 2008 as Microsoft decided to focus on other areas and priorities.

Rest assured though that Microsoft understands the importance of this integration scenario. I personally have voiced my concerns on a few occasions and have put this on the top of my wish list which I shared with the Reporting Services team. There are good things happening already which make me believe that SQL Server.NEXT (post-SQL Server 2008) will materialize this wish. What's really needed is Excel-like support of SSAS in Reporting Services, plus calculated members which are already supported.

Then, the question is what to do meanwhile if you target Analysis Services (and you should). One approach is to take the "I'll stick with Microsoft" approach. This may require you find workarounds for the static schema and other limitations, such as using the Microsoft OLE DB Provider for Analysis Services instead of the built-in SSAS provider. This is not as bad as it seems as little out-of-box thinking usually gets the job done. By the way, I wrote some 50 pages in my next book to cover integrating SSRS with SSAS in as much detail as possible.

The second approach is to look at third-party offerings, such as Radius Producer and Intelligenca Query. What I liked about Intelligenca Query is that it doesn't require throwing the baby with the water. You can still keep the Microsoft Report Designer but use Intelligenca Query to replace the built-in SSAS provider. This works because just like the built-in provider, Intelligenca Query is implemented as a data extension. I tested Intelligenca Query with SQL Server 2008 and it worked without any issues with the BIDS Report Designer and Report Builder 2.0 (aka Report Designer Preview).

The good thing about Intelligenca Query is that it lets you plug in an arbitrary MDX query. Behind the scenes, it pulls out a little trick where it rewrites the report dataset by transposing columns to rows. Consequently, you have to use the matrix region to rotate the rows back to columns to recreate the original query results. Now, my ideal provider would support a dual mode where I can tell it not to rewrite anything but just to give me the results. Of course, if another dimension member is added, it won't show up on the report columns (assuming a table with fixed columns) but I can see this being useful for notice users that create ad hoc reports or when the columns are fairly static.

Another thing I liked, of course, about Intelligenca Query is that it eliminates the nagging issue of server aggregates which are the aggregates for the dimension All members. With the built-in provider, you have to explicitly request them by using the Aggregate function but they won't be retuned if you hand-code your query. Intelligenca Query simply brings them as additional rows. What if you don't want them, such as when you need a group footer? No problem, just exclude them from the query and group on the rest of the rows.

Another cool thing is working with parent-child hierarchies. The MS built-in provider takes this strange design pattern where it dumps all members in a single column, thereby making it virtually impossible to create subtotals per level. With Intelligence Query this issue disappears:

I also liked the graphical query designer which is pretty much in par with the Microsoft graphical MDX Query Designer. On the downside, some outstanding work is required to handle parameters and synchronize the text query with the graphical designer.

If the SSAS built-in provider is driving you nuts, I encourage you to take a look and evaluate Intelligenca Query.