The Microsoft BI 3rd Party Ecosystem

One of the great strengths of the Microsoft BI stack is that due to its relatively open architecture and sets of API’s mean that it is easy for 3rd party developers to develop tools that work with it. This means that where there are gaps in the stack, often a 3rd party has stepped in to fill it. This 3rd party ecosystem is much richer for Microsoft that other providers. The upshot is that while other vendors may be able to say “Microsoft does not do this” – often, there is someone independent of Microsoft who does, and cheaply. So as a customer you aren’t tied to vendors release cycles to address gaps in the functionality of their offering (though obviously you are tied to when these get developed by the 3rd parties…). Another benefit is that because these products tend to be pretty focused on a specific need they tend to be better at meeting that need than a core vendor provided solution. The reason for this is because a subject matter expert will be driving the product to meet that need, and that need only – so there are no distractions from other priorities within the overall suite.

So where do some of these gaps get filled? Let’s start¬†from my¬†space of expertise, SSIS. Cozyroc develop some very useful additional components such as Amazon S3 data connectors. Pragmatic Works also provide additional components such as the Upsert. There’s also a heap of other components available on Codeplex thanks to Todd McDermid’s efforts to catalogue all of them.

Pragmatic Works also provide tools to auto-document MS BI solutions, perform DTS to SSIS conversion and provide SSIS auditing and control. Of course there is my own SSIS ETL Control Framework on Codeplex. To enhance the development environment there is the excellent BIDS Helper addin on Codeplex.

It tends to get a bit quiet around SSAS extensions, though i’ve worked with ex-TM1 man Howard Taylor a long time and watched his SSAS / MS Office based OlapOffice budgeting and forecasting suite grow from strength to strength. There may be more out there but it’s not really my field of expertise.

When we get into Reporting and Data Visualisation there’s mounds of tools, some MS specific and some aimed at multiple source systems. Panorama have long been key in this area (they developed SSAS in the first place) and provide strong data browsing and analytic capabilities. There are also more niche providers such as SiSense who focus more on rapid self-service reporting and dashboarding.

Even Master Data Services, barely out the gate in SQL2008R2, gets a look in, with Profisee providing a improved user experience and extra management features.

The above list is far from comprehensive, and I’d love to hear from my fellow BI Monkeys about others you are aware of and like.

A note of warning to the Vendors who will inevitably post comments plugging their product – i’m happy for you to do so, but only if you state what the product actually does. Posts that are pure hyperbole (i.e. NewBIProduct will change the face of BI forever!) will be summarily dispatched.

Read More

BI Documenter from Pragmatic Works

I recently had to demo SSIS to an enterprise as part of an ETL tool evaluation. One of the Microsoft BI stacks weaknesses is the lack of Data Lineage tracking. What this means is there is nothing embedded in the toolset that allows you to identify clearly the source of a data item in a package / report / cube without digging into the development environment. Rumours are this will be fixed in the next release, however nothing has yet been confirmed.

However, where the Microsoft BI stack has a competitor beating edge is the 3rd Party ecosystem – so where there is a gap in the toolset, often another company will step in and fill it. In this case, Data Lineage issues are addressed by a tool created by Pragmatic Works (which is run by Brian Knight, an SSIS heavyweight) called BI Documenter.

BI Documenter Review

So what does the tool do? It has 3 key functions:

Documentation Generation: The tool auto-generates documentation for Databases, SSIS packages, SSAS Cubes and SSRS reports. It’s quick, and the output is pretty – and it’s really a bit useless. Its benefit is if you have to say you’ve produced some documentation and need to do so with minimal effort. The reason I say this is because it provides no context for why things have been done, what the purpose of the component is, where it fits in to the framework etc. My usual gripe with documentation I come across is that it only tells me the what, not the why, and the why helps me solve a problem. This tool can’t do anything to address this.

Data Lineage: Now this is where the main value lies. Through simple navigation you can select any object (table / view / package etc) in the solution and see what objects depend on it and what objects it depends on in turn. This is great in a complex system where if you need to make a change and need to find out what that impacts.

Now its not perfect – it seems to skip documenting some sources, such as flat files, so they get missed in the impact analysis. And the level of granularity is at the object level – for example you can’t see the impact of an individual column change, just at the table level, but its still a great start and a useful tool.

Snapshot comparison: A final piece of value which can be useful in troubleshooting. BI Documenter takes snapshots of your solution to document – and you can compare these to identify changes in the solution. The detail level is pretty good and will be a great place to start tracing changes in your system when your source control systems fail.

Conclusions

Is the tool worth it? At a maximum cost of US$500 a seat, it’s definitely got a place somewhere in your organisation. The documentation tool is of limited use but the Data Lineage and Snapshot comparisons are worth the cost of the product. Full details here: Pragmatic Works – BI Documenter

Read More