I and Alberto will present the SQLBI Methodology session at PASS Summit 2009. After the very good feedback we got at the European PASS Conference 2009, we will have the big opportunity to discuss this methodology in front of a broader audience.

In the last months we've been busy writing an upcoming book with Chris, but we're confident we'll release some update (and of couse a working demo!) of some of the SQLBI papers before the PASS Summit. For example, we should update The Many-to-Many Revolution paper with some new very useful pattern we still haven't disclosed.

I know that the buzz will be for the new release of Analysis Services and Gemini, but I still think that new tools will not replace the need for design of a large and long-lifecycle BI solution.

We (I found the issue, Chris created the repro) detected a possible bug using DirectSlice and MeasureExpression.
We just posted it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=455732
 
The issue could be very dangerous resulting in wrong query results on cells affected by DirectSlice property and MeasureExpression, depending on the order of the MDX query sent to Analysis Services. We included a complete repro to the bug posted. This behaviour has been tested on both SSAS 2008 and SSAS 2008 SP1.
What I would like to understand is how to use DirectSlice without worrying about this issue...

Please vote the bug if you agree it is dangerouse. I would like to get some feedback from MS too...

Even this year I will be a speaker at the European PASS Conference 2009 (April 22-24, 2009 - Neuss, Germany).

Last year I had a session discussing the Many-to-Many Revolution paper.
This year I'm excited to introduce the SQLBI Methodology joining Alberto Ferrari on the stage. We got a lot of feedback until now, but this is the first time we will have the opportunity to discuss it in front of a broader audience (we made the same presentation some weeks ago at the Italian Microsoft SQL Server & Business Intelligence Conference 2009).

If you will attend the conference, stop us and say hello! It's always nice giving a face to a name. See you in Germany!

It has been confirmed that the Microsoft BI Conference 2009 has been delayed to October 2010 (one year later).

The immediate consequence is that the PASS Summit 2009 will be the main event where something new about Kilimanjaro could be disclosed by Microsoft. The PASS Summit will have more tracks to better support the BI coverage.

However, my point of view is that a BI conference should be necessary. Today we have too many products involved, each one with its own vertical conference:

• Microsoft SQL Server (+ SSIS/SSAS/SSRS)
• Microsoft Office (Excel)
• SharePoint (which is part of Office platform and that recently incorporated Performance Point Server)

What is the right conference to discuss about all these products together? May be TechEd, but it's too big. The Microsoft BI Conference was a good thing, if the decision is to move it each other year, than it's ok, but I think it would be a mistake to cancel a dedicated conference that could mix all these session at the right level.

Alberto Ferrari wrote about an interesting issue he discovered working on optimization of dimension process using materialized reference dimensions.

I can understand SSAS behavior from a technical point of view, but it is not so obvious that an update of a dimension could result in returning wrong results from a cube. For this reason, I suggest you reading about the issue - maybe one day it will save several our hours of work.

I just published an updated version of the corrections for the Programming Microsoft LINQ book that I wrote with Paolo Pialorsi.

If you don't have the book, remember that you can download some sample chapters from the book web site.

I recently encountered an issue using DRILLTHROUGH with ROLAP dimension and I wasn’t able to find any good information making searches about it, so I think it’s a good idea writing a few notes. Probably I will write a more complete on the DRILLTHROUGH issue later, but there is a specific issue (a bug feature by design, probably…) that can be hard to resolve.

Let’s start talking about DRILLTHROUGH. If you have some degenerate dimension (attributes in the fact table that are not measures) that you want to be part of a drillthrough action, you can add a dimension with these attributes, making it a fact dimension (or a regular one – a discussion of what is best in a future post). Now, when you process the database, this dimension is copied into the MOLAP storage, just because (by default) a dimension has the StorageMode property set to MOLAP. It works, but it requires some heavy SELECT DISTINCT on your fact table. Not a good idea with large volume of data.

At this point, changing the StorageMode setting to ROLAP might seem a good idea. In SSAS 2005/8 you can define (with the Enterprise version) that a dimension can be ROLAP even if the measure groups are MOLAP. You should pay the cost of SELECTs to the fact table only when a drillthrough action is required.

After changing the StorageMode setting, you have to reprocess the dimension and the cube. Making a drillthrough query, you might encounter an error. With Excel 2007, this error sounds like this message:

Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘dbo_FactInternetSales’, Column: ‘SalesOrderNumber’, Value: ‘SO43697’. The attribute is ‘Sales Order Number’.

The specific name of table, column value and attribute may very depending on the cube you are using. I’m getting this error with Adventure Works DW 2008 sample (with a modified setting, as you will read later).

If you execute an equivalent drillthrough query in SQL Server Management Studio, like this:

DRILLTHROUGH 
    Select  ([Measures].[Internet Order Quantity],[Date].[Calendar].[Calendar Quarter].&[2001]&[3])  on 0
From [Adventure Works]

you end up in the same error, even if the SSMS console only says in the messages panel:

Executing the query ...

Server: The operation has been cancelled.

However, you can see the same error displayed by Excel by tracing the analysis services activity using SQL Server Profiler.

Well, what is this issue? The exactly same query, with the same data, works fine if the dimension storage is MOLAP. In reality, if you try the original Adventure Works DW 2008 sample, it works fine. The setting I had in a dimension of another project that produced the issue is the ErrorConfiguration/KeyDuplicate property of the dimension. If it is set to “Ignore”, like the original sample, the drillthrough works well in both ROLAP and MOLAP dimension storage setting. But if you set the ErrorConfiguration/KeyDuplicate property of the dimension to “ReportAndStop”, the drillthrough works well when it is set to MOLAP, but it fails with the error I described before when it is set to ROLAP.

I posted this behavior on the Connect site with FeedbackID 400702. But after several hours spent on this issue, I hope to save time to other developers who might encounter the same issue again.

The obvious workaround is to set the ErrorConfiguration/KeyDuplicate dimension property to Ignore. However, I hope this will be explained in a better way by MS.

Bart de Smet wrote a very interesting post about the possible creation of an Updateable LINQ provider, especially if it can be used for a SQL dbms (of course it would work with LINQ to SQL but it should be applicable to LINQ to Entities too). I like this approach very much.

However, a Microsoft word on this, especially defining the "official" LINQ extensions and interfaces to implement an updatable query provider, would be fundamental to write code that will be still valid when a different LINQ provider implementation will be used.