0

I can't find anything that hints towards native support for charting graph data structures (otherwise known as "network maps" by some), and in my case, a directed graph. I'm wanting to create a visualisation of our ETL dependency chain at work to show the steps that each different 'job' is reliant on before being able to proceed.

Questions:

  • Has anybody been able to 'simulate\hack\workaround' this lack of out-of-the-box functionality in SSRS?
  • Any ideas on how to possibly achieve this if no-one has thought of doing this before?



EDIT - 2014-10-30
Two years and no answer so I've accepted the most promising advice on a workaround to get what is needed, as no direct functionality has been found.

Community
  • 1
  • 1
Adrian Torrie
  • 2,795
  • 3
  • 40
  • 61
  • What parts of your graph need to be dynamic for the report? Sounds like some tricks with images will get you what you want. – Jamie F Oct 23 '12 at 14:06
  • @JamieF The directed graph will be dependent on the number of vertices and edges returned. [Here is a picture](http://confluence.highsource.org/download/attachments/3244145/dependencyGraph.png) (Google image search used to find) that shows what the output could possibly look like. Thinking of perhaps even using a matrix to show a textual representation somehow? – Adrian Torrie Oct 23 '12 at 23:26

1 Answers1

1

From left field: You could wrap an SSIS package around your "ETL jobs". The SSIS Control Flow surface has a GUI for expressing task dependancies. It's functional if not not visually outstanding. Your "ETL jobs" could be Execute SQL Task or Execute Process Task objects. You can connect the precedence constraints to show dependancies.

This could either be for real use or just for documentation purposes. If you use it for real you'll find its a great way to control ETL dependancies and parallelism.

Mike Honey
  • 14,523
  • 1
  • 24
  • 40
  • Useful idea for a documentation tool, doesn't sound too dynamic though. The graph is held in a table in the ETL database, along with other metadata (in other tables) that control the process. There is a combination of SSIS packages, PROCS, CMD scripts (it could all be in SSIS, but its not, this is what I have to work with atm). Scripting up a SSIS package on the fly (for documentation purposes) could be an idea? Not really sure how to go about this though? – Adrian Torrie Nov 02 '12 at 02:27
  • No it's not dynamic in those terms. Personally I'd scrap the whole "dynamic control table" design and implement all of that in SSIS. It can happily iterate through control tables, be driven by metadata etc, but usually you find that once the requirements settle down the ETL requirements are not that dynamic after all... – Mike Honey Nov 06 '12 at 02:32
  • agree 100%, not my choice, so I'm stuck with what I have. – Adrian Torrie Nov 08 '12 at 04:42