Dependency tracking application

[Database Administrator]

Hello,
I need advice on whether SSAS or OLAP in general is a good choice to
model dependency chains. I need to model and store application
depdendencies
e.g app A is dependent on database db1 which resides on server 1
Db1 is also dependent on ETL job Load1, which in turn is dependent on
a data file df1.

The goal is to be able to build a report so user picks an object
(file, db, app, or server) and can view it's dependency chain. e.g
user chooses to view dependencies of data file df1 then the report
should list all it's dependent objects-
File df1 affects, Load1, which in turn affects Db1, which impacts App
A.

Thank you for your input.

Ken

[Database Administrator]

On Nov 14, 4:15*am, Ken wrote:
> Hello,
> I need advice on whether SSAS or OLAP in general is a good choice to
> model dependency chains. I need to model and store application
> depdendencies
> e.g app A is dependent on database db1 which resides on server 1
> Db1 is also dependent on ETL job Load1, which in turn is dependent on
> a data file df1.
>
> The goal is to be able to build a report so user picks an object
> (file, db, app, or server) and can view it's dependency chain. e.g
> user chooses to view dependencies of data file df1 then the report
> should list all it's dependent objects-
> File df1 affects, Load1, which in turn affects Db1, which impacts App
> A.
>
> Thank you for your input.
>
> Ken

Ken,

this is the BI metadata handling we are all waiting for years!
Using existing technologies, we suggested a way to track this
dependencies adapting the development methodology to the existing
tools. Let's take a look at our SQLBI Methodology (http://
www.sqlbi.com/sqlbimethodology.aspx).

Marco Russo
http://www.sqlbi.com
http://sqlblog.com/blogs/marco_russo

[Database Administrator]

Marco, please point me to where on this site, you recommend
(www.sqlbi.com/sqlbimethodology.aspx), can I find information
relevant to my query?

Thank you

[Database Administrator]

Ken,

Maybe I'm not getting what you're asking in total, but this seems
one:many, but maybe with distinct hierarchies; you may be able to do
this with a regular dimension (with empties) or parent-child
dimension.
This is an pretty original use of OLAP though, I like it. We did
something like this with a troubleshooting app that built paths which
we did by building parent-child dimensions that mimicked the decision
trees.
Yes, form what I understand, OLAP would handle this, but I'm not sure
what the Facts would be, maybe the files them selves with the files
sizes or other details being the measures.

With that maybe I can get you to think of it in a differnet way,
though.

One of the paths seems like a AppUsageDependency, that is what it is
required to actively Use the app.
The other path you enumerate is a AppPrepDependency, that is, what is
required to ready the application prior to using it (loading the
data , etc.)

If you have one of the hierarchies like so, likley called
AppUsageDependency, I might turn it upside down.
An app cannot exist sans a server (or client). So would the server be
the leaf or root?
Baring that, I think the server would be at the root, and the apps
would be the leaves.

You may even want to include the individual files (DLLs, other
components, etc) that the app requires, but not sure if you need it
that detail.

(I might even suggest server instance as well as an additional level
in there)
AppUsageDependency:
Server -> DBServer -> DBServerInstance -> App

I might also suggest having attributes at the server level in there
like for the Virtual or Physical (booleans), and IPAddress.

AppPrepDependency:
Server -> DBServer -> DBServerInstance -> ETLJob -> SourceFile

Usage:
When they browse the dimension (in the report no matter what UI you
end up using), they could start at the bottom and work their way north
until they get the root, or the other way around, start on top and
work their way down.
Or you could use in your MDX query the Descendants and Ancestors
function would show you the complete path from root to leaf given any
root or leaf, or for the each of leaves.

-exologic