SQLBI - Marco Russo

SQL Methodology at PASS Summit 2009

Marco Russo (SQLBI) — Sat, 20 Jun 2009 14:27:00 GMT

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...(read more)

An Oslo DSL for NHibernate: NHModeller

Marco Russo (SQLBI) — Fri, 05 Jun 2009 15:59:00 GMT

A few days ago, I talked about Oslo and metadata issues with BI stack. I got several comments and the discussions it's interesting, but as I said it is too early now to use Oslo - but it could be a foundation for a future product.

As a side note, today I noted an Intellipad plugin to generate models for NHibernate starting from a textual DSL (Domain Specific Language) - it is called NHModeller and it shows just one of the powerful features of Oslo. It is not BI-related, but it could be useful to look at even if you just want to get an idea of what is the potential of a DSL with Oslo. However, the most interesting part of Oslo for the BI metadata issue is the repository, not something that you can actually see with a tool like NHModeller.

Possible bug with DirectSlice and MeasureExpression

Marco Russo (SQLBI) — Wed, 27 May 2009 07:41:00 GMT

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...

Metadata for BI Solutions in the Microsoft stack

Marco Russo (SQLBI) — Sun, 17 May 2009 16:11:00 GMT

You know, today Microsoft doesn't have a solution to handle metadata of a BI Solution. Really, they don't have nothing to handle a single layer of the architecture like the Data Warehouse.

In a (how much near?) future this gap could be filled by using Oslo. Yes, Oslo is not a solution by itself, it's a platform, but studying it I see that it is the more natural way to produce a very well integrated metadata solution.

What do you think about it? Is someone already working on this? If not, I would like to know the reasons...

I will be at European PASS Conference 2009

Marco Russo (SQLBI) — Wed, 15 Apr 2009 15:33:00 GMT

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!

Use of the WITH clause in SQL Command SSIS OLEDB Source

Marco Russo (SQLBI) — Fri, 10 Apr 2009 16:10:00 GMT

If you try to use the WITH clause in a SQL Command of an OLE DB Source in a Data Flow of Integration Services, you might receive the error 'Statement(s) could not be prepared'.

Solution: put a semicolon (;) just before the WITH keyword.

No more BI Conference... every BI news will be at PASS Summit 2009

Marco Russo (SQLBI) — Thu, 09 Apr 2009 22:14:00 GMT

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.

And sometime Connect works... slowly...

Marco Russo (SQLBI) — Mon, 16 Mar 2009 10:35:00 GMT

Almost two years ago I wrote a post about the abuse of the "it's by design" answer Microsoft gives to many bugs. You know, I think that a bug in design produces the same results (if not worst) than a bug in implementation. I also had a long discussion about the reason why that specific issue should have considered a bug or not. But that's history...

Last week I received a notification about the bug I originally posted on Connect. The last comment from MS notified me that the bug (or whatever you call it) has been fixed in SQL Server 2008. I tested it and it's true!

I'm happy that my feedback has been accepted by Microsoft, but as always there are still a lot of space for improvements:

Why I received the notification only months after the release of SQL 2008?
Probably the bug has been fixed more than one year ago. It would have been better receiving a notification earlier, describing tha resolution of the issue in a new release of SQL Server. In this way, in case of a critical issue, I would have been able to make a plan to introduce a new release earlier just to fix the issue (or maybe it was a blocking issue to upgrade SQL2000 to SQL2005 and this would have been an important news to plan the upgrade).
In general: why the Connect is so disconnected from the "real" bug tracking system that MS should have to track these and other issues? It seems that Connect is handled manually by some volunteers... when they have spare time...

However, a good news today.

Issues materializing reference dimension

Marco Russo (SQLBI) — Fri, 27 Feb 2009 07:30:00 GMT

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.

SQLBits 2009

Marco Russo (SQLBI) — Mon, 23 Feb 2009 14:51:00 GMT

This the second time in a row I will not able to attend to SQLBits - but don't lose this event if you can go to Manchester on March 28th, 2009. The conference is free, but registration is required. A training day (with a reasonable fee) is also scheduled the day before. I hope to attend the next one...

The reason I cannot go is mainly because a few days later there is the Microsoft SQL Server & Business Intelligence Conference 2009 in Milan and I'm involved in the organization of that conference - but be careful, all the sessions are in Italian, so it is good only if you already know that language! Otherwise, Manchester would be a better option for you!

Corrections for Programming Microsoft LINQ book

Marco Russo (SQLBI) — Fri, 20 Feb 2009 08:25:00 GMT

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.

Drillthrough improvements: call for feedback!

Marco Russo (SQLBI) — Wed, 21 Jan 2009 10:34:38 GMT

We all like Analysis Services, but when it comes to drillthrough, there are a number of issue that we like to be fixed. I added them in a feedback item on Connect – so please vote it and give to drillthrough a chance to improve in future releases!

Here is the list of the required improvements I would like to see:

Enable editing of drillthrough columns order. They can be arranged in any order you want inside the cube XML file, but editor in BIDS forces you to put all the attributes of a dimension near each other, without allowing to change the order of columns of the same dimension. Moreover, you cannot move dimensions up and down.
Enable use of properties in drillthrough and don’t limit the drillthrough only to those attributes with AttributeHierarchyEnabled set to true. I don’t know if there is a structural problem, to me it seems more like a semantic one.
Enable use of drillthrough on calculated members. In my opinion, who write calculated members has the knowledge to write a tuple describing the subset of the cube that you want to drillthrough. A dynamic MDX expression evaluated for each drillthrough request (just like the dynamic MDX caption) would be the best.
Fix behavior of drillthrough with semi-additive measures (or offer a way to customize it - see drillthrough on calculated members). Drill-through on semi-additive measure returns rows that are not considered in the value displayed. For example, a LastNonEmpty of a year returns all the year rows instead of the last day/month available. Best option, in my opinion, is to customize drillthrough for each measure if required, just as suggested for calculated members.
Optimize drillthrough on ROLAP dimension. If a drillthrough uses attributes from a ROLAP dimension, there is a SELECT DISTINCT over the whole dimension table if it has a regular relationship with the measure group, followed by a complex SQL statement which ends in filtering all the fact table. If the ROLAP dimension has a fact relationship (imagine a degenerate dimension with data as Order Number), the SELECT DISTINCT is not executed, but there is still a complex SQL query. When we have a surrogate key used as a primary key in the fact table (which is the case in most of data marts I’ve seen on SQL Server), it would be way faster getting the set of ID’s obtained by the MOLAP measure group and filtering the fact table with them.

Issues executing a DRILLTHROUGH with a ROLAP dimension

Marco Russo (SQLBI) — Sat, 17 Jan 2009 11:21:30 GMT

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.

SSAS 2008 calculated members still don’t love Excel 2007

Marco Russo (SQLBI) — Mon, 08 Dec 2008 15:28:00 GMT

I was tempted to start this post by this sentence “Analysis Services 2008 is not a new full release of SSAS” but as you can see, I resisted to it! :)

Ok, it’s a joke. However, how do you interpret the answer “In the next full release of SSAS” when I asked “Does it mean [the fix for the Excel 2007 calculated members selection issue will be solved] ‘in Office 14’, ‘in SSAS 2008’, ‘in Office 2007 SP1’ or ‘in SSAS 2005 SP3’?” (see this item on Connect for further details).

In 2007 (more than one year ago) I assumed that the answer corresponded to the ‘in SSAS 2008’ choice, but now I understand why the answer was slightly different…

Today I retried the calculated members issue I described in a previous post. Long story short: you cannot select individual calculated members on non-measure dimensions when the serve is SSAS 2005 SP2 or later build. Today I tested this behavior with SSAS 2008 and it still produce the same issue with Excel 2007. The reason I didn’t tested this issue before is that I prefer to use my DateTool solution, which is much more flexible and compatible than the one based on calculated members. But the issue is still there if you want to use calculated members for other reasons.

So, what will be the “next full release of SSAS”? I hope to read an answer from a blue-badge :-)

UPDATE: A member of the SSAS team answered in the comments - no good news, but at least I appreciate the transparency!

Updateable LINQ

Marco Russo (SQLBI) — Mon, 24 Nov 2008 10:21:39 GMT

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.