To make a long story short: the ADO.NET team is now responsible of ADO.NET Entity Framework (including LINQ to Entities) and of LINQ to SQL (the last one was originally in charge of the a separated team, tied to the C# compiler).

There is an evident overlapping between LINQ to SQL and LINQ to Entities and since the first day, Microsoft said that in the long run, these two solutions would have been merged into a single one.
Now, the roadmap that is arising is: Entity Framework will be improved adding features that will be necessary to cover scenarios where LINQ to SQL today is preferred over LINQ to Entities and Entity Framework.

There are a lot of comments - I suggest you starting here to get a good recap and pointers to many others.

My personal opinion is that LINQ to SQL is very good in some scenarios and should not be dropped until a good alternative (in EF?) is available. For example, I use LINQ to SQL to implement nightly processes that are part of ETL solutions. In these cases, I use LINQ to SQL to read data (expecially configuration data, but sometimes also source data) and use the SqlBulkCopy API to write data into destination tables. Having all the necessary into a single executable file, without external dependencies, is a big advantage for deployment (a single file to copy). Today LINQ to Entities would be slower, would have more files and would require .NET 3.5 SP1 on production servers (the last one would not be a real issue in my case). There are of course other scenarios when there is something that makes LINQ to SQL a better choice against the current version of Entity Framework.

My hope is that a convergence of two partially overlapped frameworks is good, but at the same time this shouldn't be a penalization for the current users of the "losing" part. This will require several releases of .NET to be done, and I hope that in the meantime the LINQ to SQL engine will have a decent evolution to keep its current position of "light LINQ oriented DAL replacement to SQL Server".

If you are at PDC 2008, I'll be at book signing for Programming Microsoft LINQ at bookstore on Tuesday 28, during the coffee break between 3:00 and 3:30 PM.
I and Paolo will be happy to meet you and receive your direct feedback about our LINQ book.

I looked at some of the Gemini comments wrote in the week-end. I'm sincerely worried about the possible abuse of this tool, just like everyone over there. However, I think that Microsoft shown Gemini so early (read my previous post about what Microsoft has not shown) just to make a position in the market. There are other tools out there that are similar to Gemini but that are proposed as an economical alternative to classical DW/OLAP solutions. The fact that also Microsoft will be part of this market should clarify to several customers that these two technologies are not necessarily concurrent but could be complementary.

Of course, the problem is that this technology is particularly subject to misuse and abuse. I liked the analogy made by Chris between Gemini and illegal drugs. What is the best way to control them? Legalization or prohibitionism? It's a really hard question. Considering we are talking about software and you can't have rules and police to enforce prohibitionism, it seems to me that legalization is the only way to control it.

However, my worst nightmare is another one: looking at some MS marketing presentation selling Gemini as the "final solution" to every BI needs. Will Microsoft be able to correctly instruct their sales rep and their partners about how to correctly position its product? In my opinion, this will be the most critical part of the Gemini debut.

I just found this useful Add-In for Excel 2007 that checks the compatibility of a spreadsheet for Excel Services publication. I'd like to get feedback if you know other similar tools and/or if you have experience with this one.

We’ve seen it on stage. It’s Excel on the steroids. I’m talking about “big announcement” here at Microsoft Business Intelligence Conference. The several announcements are:

·         Madison: the announcement of integration between SQL Server and DATAllegro, release expected within 1H 2010. Look at it if you have a datawarehouse of hudreds of Tb or more.

·         Kilimanjaro: a sort of “R2” version of SQL Server expected to be released within 1H 2010. It includes self-service analysis (project code Gemini) and self-service reporting

The most interesting part is of course the Gemini project. It’s a column-based in-memory storage that seems to be part of Analysis Services (not so much details by now) and that can be used locally with Excel (just like you can use local cubes, I believe). At the end, you have an add-in for Excel that allows you to build relationship between data coming from different sources and simply “pasted” into the model. No model design required. Relationships between data are inferred by the engine and the navigation tool provided in Excel is an evolution of the PivotTable that you can then publish in SharePoint. In a few words, Excel is the reporting tool of the future. Just like today, but much more powerful.

I would like to use this user interface with existing SSAS cubes – the user interface to filter data is so much better than today… It’s better to not show this thing to end user until it will be released – in the meantime, we have to look at what really does and what are the real limits of this approach. I suspect that data cleansing and more “traditional” BI solutions are not going to disappear, but this approach can integrate very well and satisfy customer needs that cannot afford a complete project lifecycle before it can be delivered. More details in the next few days…

The first cumulative update for SQL Server 2008 has been shipped. It can be downloaded here.

This cumulative update contains several hotfix released for AS2005 (CU8, CU9, CU10) but not included in SQL Server 2008 RTM. There is also some bug fixed.

This post is part of a Methodology discussion - other posts will follows. I will be happy to get your feedback!

I am proud to announce a public draft of the first paper about the SQLBI Methodology. I and Alberto Ferrari tried to define a consistent methodology which covers the construction of the back-end of a BI Solution using Microsoft SQL Server and its complementary services. Now, we are looking for comments and feedback about it.

But, wait. Is this “yet another data warehouse methodology”? It depends from your point of view, because it strongly inherits concepts from both Kimball’s and Inmon’s methodologies, but it also includes concepts that are very specific to serve a multidimensional database like Analysis Services. However, before you start to read the whole paper, we start to make a fast comparison in this post between different methodologies we are talking about.

On the web, there are plenty of discussions regarding the Inmon vs Kimball debate. Both authors have fans that seem to believe that the choice between the two approaches to a data warehouse project is much more a religious war than a technical decision.

We (I’m writing these posts with Alberto Ferrari) do not want to start another war with this post, mainly because we do not believe that such a war has any reason of being, as is the case with any religious war.

Both Inmon and Kimball developed methodologies that work well in different kind of data warehouses. However, there are situations where the Kimball approach brings a fast and effective data warehouse and there are others where the Inmon approach leads to a cleaner solution. Both methodologies have drawbacks too and this post is not the right place to list all of them.

What is really important, in our opinion, is something else. If you choose the wrong approach, you will discover (usually too late) that you cannot complete your data warehouse project on time and on budget. Moreover, since the requirements in the BI world change frequently, it is often the case that you have to choose the methodology when the real requirements are still not clear.

Having worked with both methodologies for different projects, we believe that a careful analyst should take a different approach other then the “religious war”. For example, it would be a good idea to use the best of both methodologies, starting with the easier Kimball method but being ready to introduce Inmon structure if and when needed.

Our experience is that a clean analysis of the entire BI solution (including the data warehouse) can produce a very effective database and a set of ETL procedures that will let you get the best from both methodologies. Most important, this can change the general approach. The ability to change idea very late in the data warehouse building is very important, because it will let you follow the user requirements even when they deeply change.

If all the ETL process is designed with a strong and clean architecture, then it is possible to switch from Kimball to Inmon, in a smooth and easy way. Clearly, this is feasible if and only if you start – from the beginning – with a mixed approach in mind. This mixed approach is the one that is described in the first paper about the SqlBI methodology, downloadable here.

As said at the beginning, since this is the first public draft of the paper, we will be glad to receive feedback about it. You can contact us directly and you can also participate in the dedicate forum SQLBI Methodology forum.

This post is part of a Methodology discussion - other posts will follows. I will be happy to get your feedback!

Building a BI solution (like any kind of software project), it is normal to look for existing methodologies and best practices, just to avoid pitfalls and get better results. Usually, a methodology is not related to a particular technology and/or software product. However, considering some details and/or features that will be available in your solution might affect important decisions that will be made. If we apply these concepts to a BI solution, we might discover that features of a particular product might have a wide impact in the overall architecture.

In this post, we want to consider the impact that the adoption of the SQL Server BI stack (and in particular of Analysis Services) can have on a methodology.

The first part of a modern BI solution is typically a data warehouse. Even if you adopt Analysis Services, usually you will have a relational star schema as a data source. This star schema will be part of a Data Mart extracted from the Data Warehouse, or sometime will be part of the Data Warehouse itself. If we talk about relational star schemas, and more in general if we talk about Data Warehouse modeling, there are well-known best practices to follow. Nevertheless, these papers and books are generally not tied to a particular platform; the only requirement is the use of a relational database. The model you will create will be not affected by the product of a particular vendor you will use as a DBMS. If the user is going to write its own queries against the Data Warehouse, everything is ok.

Now, when Analysis Services comes into the game, our previous choices might affect our final result. Analysis Services has several modeling capabilities that might change the relational Data Mart design we would have been thought otherwise. Just to name a few of these features:

• Many-to-Many Dimension Relationships
• Reference Dimensions
• Parent-Child Dimensions
• Join to dimension at different level of granularity
• Data Source View (which allows the definition of views outside from the database)
• MDX Scripts

At this point, several issues arise. Are the reference dimension a signal that snowflake schema might be better than star schema? Are the parent-child dimensions a way to avoid the construction of a fixed number of hierarchy levels corresponding to the maximum deep of the existing hierarchies? Should we prefer named queries in Data Source View instead of creating regular (and centralized) views into the relational database? Can the power of MDX Scripts substitute some part of the ETL processing?

Answering yes or not to the each of the previous questions might affect our architecture, and in particular the relational design (but also ETL). We didn’t mention the many-to-many relationships. When used in a simple way, they reflect the existing many-to-many dimension relationships existing in a regular Kimball design. However, as shown in the “The Many-to-Many Revolution” paper, we might create very atypical relational schema, just to satisfy our multidimensional modeling needs.

Thus, we need to consider how these changes affect our methodology of choice. If we want to leverage on features of a particular product, we will need to make several exceptions to a standard methodology. Not having a guide for these exceptions often brings to inconsistent results, where several people of the team (or, sometime, the same people over time) use different techniques to implement something that is not well described in the original methodology.

Moreover, if you extend these considerations to the client used to navigate OLAP cubes, there are more substantial differences. These differences impact both the user experience in terms of speed and ease of use and the format of the query sent to the server, forcing you to adapt the cube structure to the specific client.

For these reasons, in the last years we defined a set of rules, patterns and best practices that forms a specific methodology to implement a BI solution with the Microsoft SQL Server BI stack of services. We named it “SQLBI Methodology” and we will publish within September 2008 on the SQLBI web site.