SQLBI - Marco Russo

Classification of BI solutions

Marco Russo (SQLBI) — Fri, 05 Sep 2008 07:54:20 GMT

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

One interesting question is “How do you measure the complexity of a BI solution?” If we need to decide whether a specific technique is suitable for a solution or not, it is very important to be able to classify that solution.

We want to define a classification of solutions based on their size and complexity. It is clear that size leads to complexity: even a simple solution made up of a few dimensions will become complex if it will handle several billions rows in the fact table.

Because the processing of data warehouses normally happens during the night, we propose a classification of BI solutions based on the time required for both the ETL execution and the cubes process to finish.

Small BI solution

We can rebuild a small BI solution each time because the entire ETL phase will consume a few hours of computation and does not need to keep complex history of changes in the dimensions.
If you are developing a small BI solution, you normally will recreate the whole relational database each time the ETL phase starts, producing a fresh database each night. This simple method of creating the data warehouse is very convenient, when applicable, because it highly reduces the complexity of handling the database. We can apply any change without worrying about the old data, just because there is no old data (where old data means data already processed and imported in the BI solution).
Even if this approach might seem strange, we find that many small or medium size companies have data warehouses whose elaboration will last no more than six, seven hours of computation. Using this pattern leads to simple solutions that are very easy to handle and with a very high return of investment.
We like to call a Small BI solution as a “one shot solution”, because it can be built with “one shot” of ETL computation.

Medium BI solutions

If we need to trace history of changes in the dimensions (i.e. we have some SCDs in the solution), the one shot solution seems to be no more a viable way. This is partially true.
If the complete solution can be rebuilt nighttime, we will always try to keep it as a one shot solution, computing it each night. If, for some reason, we need to maintain the history of changes of some specific dimensions, we think that it is easier to store the different versions of members of those dimensions in a persistent database, reloading all the facts and dimensions each night.
Doing this, we maintain the advantages of the one shot solution adding a slight complexity for the storage of different versions of the members of changing dimensions. Nevertheless, we still have full control on any database change, because we can rebuild the database during one pass of computation.

Large BI solutions

When the size of the fact tables becomes so big that the “one shot solution” is not a viable one, then the only choice is that of loading it incrementally each night, applying changes to the dimensions and adding new facts to it.
When a solution is very large, the overall complexity raises of some levels. We cannot rebuild the database easily, nor add attributes smoothly to the solution. Any change will require a high level of attention, because all operations will consume CPU and disk resources to a high degree.

Our experience is that the majority of solutions in the market are made of small or medium sized solutions. Whenever we start a new BI solution, we always need to understand in which scenario we are, because the architectural choices will highly depend on the size of the BI solution.

Methodologies to build BI Solutions

Marco Russo (SQLBI) — Fri, 05 Sep 2008 07:47:00 GMT

In the last years I spent most of my time working on BI solutions based on the SQL Server platform. I had experiences with other architectures but I mostly used Kimball's methodology to design relational databases feeding SSAS cubes. However, Kimball only describes the relational side of a BI solution and gives you a basic model (the star schema) that is the pillar of all the SSAS projects I made. The Kimball's model is product-agnostic and leveraging the modeling capabilities of SSAS requires you to make some decisions about how to model data, where to put some definitions and calculations and so on.

I and Alberto Ferrari worked together on several projects and we realized that today we use a methodology that is the result of years of experience and selection. We want to share our knowledge looking for flaws, comments and suggestions. I will start with some post with "Methodology" tag and very soon we will publish some whitepapers on SQLBI site. Please feel free to contact me for any feedback!

Microsoft Sharepoint Conference in Italy

Marco Russo (SQLBI) — Tue, 02 Sep 2008 07:11:09 GMT

Ok, this could be partially off topic, but if you work on BI it is not. I will be a speaker at the Microsoft Sharepoint Conference 2008 in Milan (Italy) on 2 and 3 October 2008. I have one speech about building digital dashboards using SharePoint, Reporting Services and Performance Point Server.

The conference will be in Italian, but there is a track in the second day with Patrick Tisseghem and Ted Pattison that will be in English. The web-site does not contain specific information about this, but conference organization told me that some people is coming from other countries just to attend to this track!

The agenda has session titles in English, but unfortunately the sessions abstracts are only in Italian. If you are interested in getting more information (in English, of course) you can contact me or directly the conference organization (write to info [at] sharepointconference.it). If you need a good excuse to come in Italy (October could be still a nice month to visit some cities), this is a very good one!

LINQ to SQL and the procedure cache of SQL Server

Marco Russo (SQLBI) — Thu, 28 Aug 2008 00:14:22 GMT

I just received a mail from Adam Machanic that pointed me to this bug (I would call it a performance issue) about the construction of SQL statements generated by the LINQ to SQL engine.

The issue: every string passed as a constant in the query will be auto-parameterized using the length of the passed string, even when you used a string variable into the LINQ query. If you write something like

string s = "Wine";
var query =
        from x in db.Products
        where x.ProductName == s
        select x;

you will see that a parameter of type NVARCHAR(4) will be passed to the generated SQL query. The next execution of query might contain a different value in the s parameter, and for this reason a different parameter type might be used: if the length of the string in the s variable changes, then the same query will be sent to SQL Server, but using a different type in the sp_executesql parameters .For example, a NVARCHAR(5) would be used whether s contains"Bread".

The consequence of this behavior is that you could have a non-optimal performance from SQL Server and, more important, the procedure cache could be filled up with several copies of the same query, differing each other only in the length of the parameter type.

I agree with Adam: this is something to be fixed. But my suspect is that we will get a "by design" answer another time...

When heap tables don't recover deleted pages

Marco Russo (SQLBI) — Wed, 27 Aug 2008 13:25:50 GMT

Today I solved an issue for a customer that was a little bit unexpected, at least for me.

The scenario: a table with a few records is periodically updated: a few records are inserted (with a bulk insert) and a few records are deleted (the older ones). It's a sort of circular buffer. That table hadn't any indexes or primary key. I know, this is not a best practice, but since the table was very small, I didn't realize the impact of the missing clustered index...

The problem: after a few months of this table has being in production, query performance joining this table degraded at an unacceptable level. I was astonished when I discovered that table had only a few rows... but something like 2 millions of pages was used by that table, resulting in a 16Gb of size for a bunch of records!. In this condition, a table scan needed a complete read of the table.

The solution: I created a non-unique clustered index on a field representing the date of the row.

The explanation: SQL Server doesn't release a page of a heap table, and doesn't use free space in previously used page (this was the very unexpected part) unless you use a table-level lock when you delete data. Details are explained in KB 913399.

Best practice: take care of any heap table you have in a production database if data are deleted and inserted. Standard maintenance plan doesn't work well in this case, because a simple reindex operation doesn't compact the heap table.

Get SQL Server allocation info inside SSMS

Marco Russo (SQLBI) — Mon, 18 Aug 2008 11:15:01 GMT

I just installed the Allocation Information add-in for SQL Server Management Studio. It's very cool having this tool integrated into SSMS. It is a free add-in published on CodePlex. Take a look, it does worth the download in my opinion.

IQueryable under the cover

Marco Russo (SQLBI) — Sun, 17 Aug 2008 09:02:59 GMT

In the Programming Microsoft LINQ book we dedicated two whole chapters (76 pages) about the writing of a IQueryable LINQ provider: one is about expression trees and the other covers the several ways to extend LINQ, including the writing of an IQueryable provider. I know that the subject is complex and probably is not necessary to every programmer. However, a good understanding of what happens under the cover of an IQueryable provider is good for everyone using any flavor of LINQ: when you debug your code, it might help you in finding issues faster.

I wrote this introduction just to explain why you should read this post of Bart De Smet, which is undoubtedly shorter than the corresponding chapter of our book and gives you a very good step-by-step introduction of the inner workings of an IQueryable LINQ provider. Then, if you really like this kind of things, you have another good reason to read the book :-)

The use of FOR XML PATH for string concatenation

Marco Russo (SQLBI) — Wed, 13 Aug 2008 09:09:34 GMT

I've just read this interesting article from Anith Sen that lists many different ways to concatenate several row values into a single column (i.e. one row for each category with a field containing a comma separated list of products for that category).

I admit I never thought to use the FOR XML PATH solution to get this kind of result. Not only this is a very elegant way to write the query (you formally are not using other recursive approaches based on CTE), but it seems to be also the better solution from a performance point of view. I didn't compared it with the CLR based one, but the performance seems to be so good with the FOR XML PATH solution that I think I will use that by default unless performances are very very critical - I would consider the CLR based solution only if I already have a CLR assembly deployed, but I wouldn't deploy an assembly just for this feature.

Important LINQ Changes in .NET 3.5 SP1

Marco Russo (SQLBI) — Tue, 12 Aug 2008 07:50:40 GMT

Dinesh Kulkarni wrote an important post about changes in LINQ introduced by .NET 3.5 SP1 that has been released yesterday.

One of the interesting changes is in the Cast<T> operator and its behavior is better described in this post by Ed Maurer. I think that the side effects of this change should be limited, because the use of explicit type for the range variable in a query expression (i.e. from int n in numbers select... instead of from n in numbers select...) is not very common. In fact, I don't remember examples of its usage in our Programming LINQ book. Take care of this change if you used (or will use) this syntax.

Mark Russinovich at Virtualization Congress (London)

Marco Russo (SQLBI) — Fri, 01 Aug 2008 14:28:00 GMT

A few months ago I talked about SQL Server Virtualization. For me, taking a look at virtualization improvements is very important, because I still think that not every database can be virtualized, but many of them probably yes! And the trend is that the number of databases that can be "virtualized" is growing for each new version of the virtualization environments. Hyper-V is an important milestone for Microsoft in that direction.

That said, I know that a lot of people (like me) have to make a decision about virtualization today, but they also want to get an overview of emerging trends and products in this market. A good news for us is that an international independent congress will be held in London on 15-16 October, 2008: the Virtualization Congress 2008.

Looking at the agenda, I discovered that Mark Russinovich will be the speaker from Microsoft and he also registered a promo video for the conference. Other speakers are from the most important players in this market. This conference is very different from the technical conference I'm used to attend from Microsoft (in fact, it is not from Microsoft!). However, the presence of people like Mark grants me that it shouldn't be a set of marketing sessions.

Another good excuse to visit London (if you, like me, are not from UK) after SQLBits.

It's not a bug, it's by (bad?) design

Marco Russo (SQLBI) — Mon, 28 Jul 2008 17:27:00 GMT

Most of the time I submitted an issue to Microsoft Connect, I got a "it's by design" answer. I've been always irritated by this sentence because they don't admit in this way that they have a bug. Today, I understood I'm wrong. It's a bureaucracy issue.

If you pretend that something is a bug, you are stating that a developer wrongly coded a program because it does not meet the requirements and the specifications. If the design is wrong, and the program is written "by design", then the program is wrong but you cannot say it has a bug.

Now, what if the design is wrong? Simple: we don't have a way to submit a design issue. In the Microsoft Connect site you have only two forms: the bug form and the suggestion form. Please, add a third form: I need the design issue form. May be nobody thought this before. It could be that simple? May be... Now the question is: what is the right form to ask for the add of a new form? I hope somebody can help me with this.

</rant>

For those of you who are asking what has been the issue that exceeded the "enough is enough" limit, take a look at this one, which has received an answer more than one year (one year!) after its submission. Technically speaking, the behavior of CAST is right. However, it requires that you convert a VARCHAR into a FLOAT and then into a NUMERIC if the initial string contains a number in exponential form. I know that in this case the "by design" answer has solid foundation and don't justify my reaction :-) but you know... I was right several other times and this time the "by design" answer started my post minutes before I realized that design reasons were good... too late, my rant was too funny to be deleted :-)

Microsoft client and server strategies about BI

Marco Russo (SQLBI) — Sat, 26 Jul 2008 09:14:47 GMT

The last news is the acquisition of DATAllegro by Microsoft. From a server point of view, Microsoft is doing well (even if not fast as I'd like). But is on the client side that I (and many other customers) are disappointed.

I totally agree with the post of Chris Webb about XLCubed, where he also explains the issues of Microsoft client tools that are on the market today. I know, we have to wait until Office 14 and/or PerformancePoint 2.0 before having a more advanced BI tool. We only have to think what to say to customers that would like to make strategical investment today and don't want to wait other 2 or more years just to get a client tool that is able to leverage on features that (for example) Analysis Services offered since 2005.

The reason for not investing too much on a third party tool is simple. Until three years ago, Panorama and ProClarity were the most important OLAP tools you could use with Analysis Services. Then, Microsoft acquisition of ProClarity stopped most of its development (because of the need to integrate it with PerformancePoint) and Panorama failed to update its client to leverage on new SSAS 2005 attribute-based paradigm. If only someone predicted the inability of Microsoft to deliver a full-featured client OLAP product, updated with fancy graphics, within 2006/2007, a very good move in 2004 would have been developing a ProClarity equivalent product made using .NET and WPF as development platform. Today, it would have been a killer-app, especially if you consider that Silverlight 2 allows you to deliver the fancy animated charts in a simple way. Today, I don't see any "wow" OLAP client on the market that I can plug to SSAS using all the SSAS features and power.

As Chris said, binding the release cycle of OLAP tools to the release cycle of Office hasn't been a good move. The sessions announced for the Microsoft Business Intelligence Conference 2008 don't anticipate much for the near future. Yes, we have to wait for Office 14...

The adoption of LINQ

Marco Russo (SQLBI) — Fri, 25 Jul 2008 07:57:39 GMT

Eric White has written an interesting post titled "Are developers using LINQ?" - there are interesting considerations about the adoption of functional programming too, but the most interesting part for me is the list of comment of the post. A lot of people described the adoption of LINQ into their team or company, and there is a spread variety of comments (good and bad).

An interesting comment is about the future adoption of F# when it will be shipped, because of the complete adoption of functional programming (C# 3 is not a complete functional programming like F# is). I suggest you to take a look at this post and its comments, because it gives you an idea of what is going on out there.

SQLBits in UK

Marco Russo (SQLBI) — Wed, 23 Jul 2008 22:12:00 GMT

I would like to attend to SQLBits this year, but my agenda is already overbooked. I hope you have the time to attend because there are a lot of interesting sessions, it's a good excuse to visit London (the conference is scheduled on September 13, 2008 - it's a Saturday) and there are very good speakers. The conference is free, but registration is required. A training day (with a reasonable fee) is also scheduled the day before. If you live in Europe, save the date!

MDX best practices analysis using MDX Studio

Marco Russo (SQLBI) — Sun, 20 Jul 2008 10:19:05 GMT

Mosha released a new version of MDX Studio (0.4), which offers an "MDX Analysis" function that suggest best practices for a given MDX query.

I tried the tool with some MDX query and it offers very good suggestion (when possible with link to further information about the issue) for both the MDX beginner and the MDX veteran. Sometime, the suggestion cannot be applied because you really want to make a particular "slow" calculation, but in general the approach is pretty good. A further step would the a "pluggable" architecture to extend best practices rules, just as FxCop does. However, chances are that not so many peopla have the time to implement additional rules, so it could be a useless effort and Mosha's time would be better spent on extending existing rules in the future.