Davide Mauri : Agile

Agile Data Warehousing with SQL Server 2012 Q&A

Davide Mauri — Thu, 30 Aug 2012 13:38:33 GMT

Yesterday has been published my Q&A interview on my Pre-Conference Workshop at SQL Pass 2012. It’s a good way to understand what will be discussed in the workshop, so if you’re interested or you’re into the Data Warehouse / Business Intelligence field and want to understand how the Agile approach can help you, you can read it here:

http://bit.ly/PASS2012DMInterview

“Adaptive BI: Engineering a BI Solution” slides available

Davide Mauri — Thu, 23 Sep 2010 06:09:45 GMT

Many thanks to all who attented to my webcast on Monday. I really appreciated your feedbacks and I already got some very interesting suggestion that I’ll surely implement.

If you have more feedbacks you can leave them here:

http://speakerrate.com/talks/4577-adaptive-bi-engineering-a-bi-solution

I must say that I would have liked to have more time to do the demo, but via Livemeeting seems to me that time goes MUCH faster and so I arrived near the end of the session with few time left for a proper demonstration of my “Dimension Loader Package Generator” tool. For this reason I’m preparing a set of small screencast that will show it in action, since also for this little tool I’d like to have your feedbacks.

Ah yes, before I forget :), slides are available here:

http://www.slideshare.net/yorek/adaptive-bi-engineering-a-bi-solution

Finally, for all those who asked: the Workshop I’ll be delivering at PASS will show how one can create a BI solution putting in place all the rules I’ve shown, so that the solution will be almost self-documenting, solid, clean, formal but still adaptive.

A public version of “Dimension Loader Package Generator” will be make available at PASS Summit.

BI Virtual Chapter: “Adaptive BI: Engineering a BI Solution”

Davide Mauri — Thu, 09 Sep 2010 09:54:27 GMT

On the next 20th Semptember I’ll deliver a session on a topic that is IMHO really important but is not yet covered enough:

Adaptive BI: Engineering a BI Solution

Are you starting to create a BI solution....but where do you begin with? How to setup everything correctly so that you'll be able to handle new features required by the customers easily and without too much effort? Which standard do you put in place so that everyone who will join the project will be immediately operative? How do you define your BI architecture so that it can be sound and flexible and the same time? All these questions come out every time someone has to start a BI project. In this session I'll present a set of standard rules we use to create our BI project, consolidated in more than five years of development (since SQL 2005 was released) and that allow to put some technical rules that helps you to start in the correct way right from the beginning: naming convention, architecture decisions, database unit testing, layering and everything you need to know to build an "Adaptive" Business Intelligence solution. The idea is to be flexible in terms of architectural decision but have some well-known rules in the solution to make it “engineerable”: in one word..."Adaptive"!

If you’re into BI this session can give you some interesting ideas on how to structure your next project. See you in cyberspace here

https://www.livemeeting.com/cc/usergroups/join?id=C97PWB&role=attend&pw=Z%40%2B%5Ew%2B%40h4

on Sept 20th at 12pm EST.

At the end of the session I’ll show also how the rules can be applied automatically with the right tools, creating an entire package to load a dimension in less then 1 minute, with all best pratices and standard in place!

Engineering BI (Or “On my PASS seminar”, prelude)

Davide Mauri — Sat, 28 Aug 2010 13:27:33 GMT

I’ve always stated that creating a software solution is half and art and half an engineering process. Just like building an house, or a car, or any mechanical or electrical device, the art is that part of the work where someone has to create something new to satisfy the customer need.

Architects are the figures that plays the artistic role. Sure, they don’t re-invent everything from scratch every time: Architectural Design Pattern are here to demonstrate that ideas can be reused successfully. But, still, architects put patterns and new ideas together in order create something new.

Engineers are the more practical side of this role-playing game and they are more concerned with processes and implementation of the solution so that it can feasible in first place, but also be maintainable, predictable, replicable and cost-effective.

In the software industry we have Architects but my feeling is that we’re missing the Engineers. This means that a lot of what is being produced from the software industry is more an art than a science and this drives to the fact that the quality, and the maintainability of the final product is completely dependent on the people working on it. In addition to that, since there are no standards to follow, each one will do as it wish. If you put several people working together you’ll obtain a cauldron of different styles that leads inevitably to higher maintenance cost and low stability and predictability. (I don’t know if you have the same feeling, but I would like to have a feedback on this, if you care to share.)

Of course I’m generalizing a lot here – I complete discussion on this matter would easily fill a book – and there are several exception to this, but I think I can say without any doubt that if I had to make a picture of my perception of the software industry I’d draw a conglomerate of craftsman workshops and not a factory district.

In the last years I struggled a bit with this lack of the “engineering” discipline: particularly I found an evidence of this situation in BI projects. While there’s at least two well-know architectural approach (Kimball and Inmon), with other options rising (like the Data-Vault), but still there are no rules and tools that can make the creation of a BI solution more standard.

In order to be able to add people to an existing project quickly, being sure that they would create something that anyone of the existing team can easily understand, that the infrastructure of all of our BI solution would be similar in order to make its development faster and easier to maintain, I’ve defined a sets of rules and tools that helps to make the work easier and higher in quality.

The idea is that the rules will define the standards, and once we have the rules, we can also create the tools to apply them quickly. This will shorten a lot the time need to build the solution (which is the part with the lowest added-value) and leave much more time to design it (where there is the “core” value of it).

I’ve got this idea watching “How it’s made” on Science Discovery channel, watching people building the awesome Airbus 380. If such complex machine can be assembled by several people everyone doing a small part of the work, we should be able to do it also with BI solution! We “only” need rules that will drive organization of work and tools to make work easier and faster for anyone. Of course this also leads to a solution that is flexible enough to accomodote quickly new customers requests or changes of direction.

Apply the idea to Business Intelligence and you’ll have and “Engineered BI Solution” which will be both sound and agile. I like to call it “Adaptive”. That’s what I’ve already presented on PASS European Summit 2010 in an specific session that had very good feedbacks and that’s what I’m going to show in my “Creating a BI Solution from A to Z” post-conference seminar (8 hours of deep dive, blending theory and practice, architecture and engineering, making it a unique session of its kind), in my next BI Virtual Chapter meeting (more on this in another post) and also in the next posts of this series.

If you’re new to BI or you’ll starting a new BI project in the next months, or you simply want to learn more on this approach, don’t miss the opportunity, and share your feedback!

QueryUnit 0.0.0.8 – Trust No One

Davide Mauri — Tue, 13 Apr 2010 06:22:01 GMT

Yesterday I’ve release an updated version of QueryUnit, the version 0.0.0.8.

QueryUnit now supports AreNotEqual, Greater, and Less assertions and is more capable of managing strings results.

I must say that I cannot live anymore without a proper Unit Testing of a BI solution. Just yesterday happened that one of the unit tests at a customer site failed showing a subtle situation where the release of a new version of custom application would have corrupted the source of BI data with a very low chance that someone would have noticed it before several days. It may happen when you have more the 15 systems that handles the data needed by your BI solution.

The key message of this situation is “Trust No One”: if your data hasn’t passed quality testing it’s not trustable. Period.

So QueryUnit is now officialy an hero (at least for me) :) No superpowers still, but useful above all.

http://queryunit.codeplex.com/

The importance of Unit Testing in BI

Davide Mauri — Thu, 01 Apr 2010 16:50:06 GMT

One of the main steps in the process we internally use to develop a BI solution is the implementation of Unit Test of you BI Data.

As you may already know, I’ve create a simple (for now) tool that leverages NUnit to allow us to quickly create Unit Testing without having to resort to use Visual Studio Database Professional:

http://queryunit.codeplex.com/

Once you have a tool like this one, you can start also to make sure that your BI solution (DWH and CUBE) is not only structurally sound (I mean, the cube or the report gets processed correctly), but you can also check that the logical integrity of your business rules is enforced.

For example let’s say that the customer tell you that they will never create an invoice for a specific product-line in 2010 since that product-line is dismissed and will never be sold again. Ok we know that this in theory is true, but a lot of this business rule effectiveness depends on the fact the people does not do a mistake while inserting new orders/invoices and the ERP used implements a check for this business logic. Unfortunately these last two hypotesis are not always true, so you may find yourself really having some invoices for a product line that doesn’t exists anymore.

Maybe this kind of situation in future will be solved using Master Data Management but, meanwhile, how you can give and idea of the data quality to your customers? How can you check that logical integrity of the analytical data you produce is exactly what you expect?

Well, Unit Testing of a DWH or a CUBE can be a solution.

Once you have defined your test suite, by writing SQL and MDX queries that checks that your data is what you expect to be, if you use NUnit (and QueryUnit does), you can then use a tool like NUnit2Report to create a nice HTML report that can be shipped via email to give information of data quality:

In addition to that, since NUnit produces an XML file as a result, you can also import it into a SQL Server Database and then monitor the quality of data over time.

I’ll be speaking about this approach (and more in general about how to “engineer” a BI solution) at the next European SQL PASS

Adaptive BI Best Practices
http://www.sqlpass.org/summit/eu2010/Agenda/ProgramSessions/AdaptiveBIBestPratices.aspx

I’ll enjoy discussing with you all about this, so see you there!

And remember:

“if ain't tested it's broken!”

(Sorry I don’t remember how said that in first place :-))

QueryUnit POC v.0.0.0.6

Davide Mauri — Sun, 11 Oct 2009 09:02:00 GMT

Yesterday I released a new version of QueryUnit, a tool I've created to easily perform unit testing on SQL Server and Analysis Services databases.

The main feature of this new version is the ability to automatically launch the NUnit GUI so that it’s even simpler to do unit testing.

You can download it here:

http://queryunit.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=34250

I’ve also updated the little help page:

http://queryunit.codeplex.com/Wiki/View.aspx?title=How%20to%20use%20this%20Proof-Of-Concept

SET IDENTITY_INSERT little bug(?)

Davide Mauri — Wed, 30 Sep 2009 05:52:22 GMT

While I was working on a package to incrementally load a dimension in the DWH I’m developing for a customer, I stumbled upon a tricky little bug (I think, I still have to have a response from MS) that had – and will have – a little impact on how I create my packages.

I’ve found that, when working on an empty table that has an identity column, if you insert a value forcing the identity value, the next time SQL Server will generate the identity value, it will generate it missing one number. So, for example, you’ll have

0	(Forced Identity Value)
2
3

instead of having the correct sequence

0	(Forced Identity Value)
1
2

Not a huge problem as you can see, but I’d like to see it fixed, since can cause little troubles if your forgot about it and you expect to have predictable identity values. I know that identity values are usually not 100% predictable, but I find them useful to speed-up SSIS package development for loading dimensions tables while keeping package complexity low. Since I apply Agile Principles to BI, simplicity is fundamental, since it allows me to change package quickly, easily embracing changes that comes from refactoring or from customer new needs.

You can find the connect item to vote here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=492452

QueryUnit Binaries Released

Davide Mauri — Fri, 31 Jul 2009 15:16:40 GMT

Due to several requests I’ve also released the binaries of QueryUnit:

http://queryunit.codeplex.com/

Enjoy!

Agile BI Development: QueryUnit is born!

Davide Mauri — Mon, 27 Jul 2009 16:29:39 GMT

Having a developer background I’m used to work trying to apply an Agile approach to projects. In these last years I focused my work primary on BI projects, and so I tried to apply an Agile approach also to them, since I really believe that Agile is the path to follow.

While Agile methods in the classic Object-Oriented field are quite widespread nowadays, in the BI field they are basically totally unknown to masses (at least this is my experience). Of course this means also that there is a total lack of tools and instruments to properly adopt Agile in your approach.

Unit-Testing is surely something that is absolutely necessary if you want or need to refactor a solution. Since now, to unit-test staging databases, datawarehouses and cubes was a pain. I needed a tool to automate everything. I searched on the web but I found nothing I really liked. So I started to wrote something by myself.

The first release is more a proof of concept that anything else, but I’ve started to use it in current BI project I’m working one with a lot of satisfaction. Now each time I change something in DWH or Cube I can automatically tests hundreds of queries. If all lights are green I can ask the customer to test the new implementation being sure that all existing reports, cubes, queries and so no will still be working perfectly. Otherwise I have some more work to do [:)].

Here’s a sample screenshot:

As you can see I’m using NUnit as Unit-Testing framework. It will allow to test SQL and MDX queries stored into an XML file, so that no .NET coding is required.

I’m going to release this first release ASAP, hope you’ll enjoy it as much as I am doing, and that you start to embrace Agile also in the DB field, if you’re not already with it. :)