SQL Server Denali Dependencies & Lineage

I’ve been a long-time advocate and big proponent of having a mechanism in my end-to-end BI solution that can show me what has changed and what will changed BEFORE something breaks. By end-to-end BI solution I mean from the data source to the ETL to the data mart to semantic layer to the scorecards and dashboards. If something changes anywhere in my complex system of data integration and data analysis, I need the system to either self-heal (ultimate vision!) or at least flag this and let me know the impact. In fact, back when I was a BI consultant and PM for BI solutions, we use to include a custom-built Microsoft Console Snap-In that would collect your solution’s metadata from database to scorecard, store that data about your data and allow you to analyze changes flagged as red for breaking change, yellow for caution, etc.

SQL Server Codename Denali (SQL Server v-next) had the original public beta as CTP1 last year that included a “Dependency Service” which is meant to be a SQL Server based lineage and impact-analysis tool. You will notice that in CTP3, that service is gone from Object Explorer.

But have no fear! For those Microsoft BI solution architects out there, if you have not seen it yet, Microsoft is still working on this capability under the project name “Project Barcelona”. This is their blog here and I recommend you visit it if you would like to have a sneak peak at the tool and find out the latest from the project team’s efforts. One thing that is really cool on their blog is that they have a link to where you can try out and provide feedback on the UI. below is a screen capture sample of the latest UI that they have put up publicly for comment:

You should check it out and submit your feedback. Even if you don’t care for the monochrome approach, I can tell you this much: having worked on projects for impact analysis tools before, I personally find this approach very nice and easy to follow. Particularly from perspective of being able to browse the linkages and relationships of different metadata. It is also a unique way that they are using Windows Azure to share out a sample of the UI and the tool’s functionality for the community to comment on, see it here.

When you go to their blog, you can read about what seems to be a unique approach that is being taken to impact analysis and lineage by approaching this problem space from the aspect of the web crawler solution technique. I’ve pasted a copy of their system architecture below. You will see an index server that sits above a series of crawlers based on different metadata types and then an API that exposes the discovered connections to different end-users.

For me, the bottom-line is this: impact analysis, lineage tracing, and dependency data is a problem in SQL Server BI solutions that is very much needed. It has evolved throughout the Denali build timeframe and I’m happy to see it getting a lot of focus and attention from a very talented project team in Redmond. You can also watch a short video that the team has put up on Channel 9 here.

Best, Mark