mibuso.com

Microsoft Business Solutions online community
It is currently Sun Oct 26, 2014 11:30 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 15 posts ] 
Author Message
 Post subject: How to display ETL data flow?
PostPosted: Thu Dec 15, 2011 3:55 pm 
Offline

Joined: Thu Apr 05, 2007 12:15 pm
Posts: 969
Location: Bochum
Country: Germany (de)
Hi guys,

I need to create some kind of diagram that displays in an accurate but easy way where every single field in our DWH is extracted from. There are some transformations in between that should be also displayed. Data Source is not just an NAV database but also some other databases (MS SQL, Oracle), some Excel Sheets and some hard coded information.

So, in general I've got two questions regarding this:
1. What kind of diagram would you use for that purpose?
E.g. Data Flow Diagram in my opinion is a little bit limitated because it displays only a general data flow.
2. What kind of tool would you use to create such kind of diagram?


regards

_________________
"Money is likewise the greatest chance and the greatest scourge of mankind."


Top
 Profile  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Thu Dec 15, 2011 8:00 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
Just manually create a 3 or 4 page Visio document. Those first pages should look pretty important and throw lots of key words in there. Then attach another 200-300 pages of anything you can find.

then take 3 months fully paid vacation.

Then give this to who ever wanted it, telling them that it is the result of 3 months of 13 hour days of work.

They will only ever look at the first page or two, and no one will every actually use the document, so you will be fine.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Fri Dec 16, 2011 9:45 am 
Offline

Joined: Thu Apr 05, 2007 12:15 pm
Posts: 969
Location: Bochum
Country: Germany (de)
Yeah, that will probably work! :mrgreen:

To be serious, our standard dashboard isn't that much complicated. We are talking about approximately 50 figures.

_________________
"Money is likewise the greatest chance and the greatest scourge of mankind."


Top
 Profile  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Mon Dec 19, 2011 7:07 pm 
Offline

Joined: Thu Apr 05, 2007 12:15 pm
Posts: 969
Location: Bochum
Country: Germany (de)
Come on, I can't believe no one in here ever did such kind of diagram.

_________________
"Money is likewise the greatest chance and the greatest scourge of mankind."


Top
 Profile  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Mon Dec 19, 2011 7:26 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7993
Location: Flagstaff, AZ
Country: United States (us)
Visio comes to mind. And what else than a data flow diagram did you have in mind? You can make it as detailed as you want. All it takes is analysis time. I agree with David though, it'll probably never be used beyond the first time when you deliver said diagram.

_________________
Daniel Rimmelzwaan
KCP Dynamics
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Mon Dec 19, 2011 9:54 pm 
Offline

Joined: Sat May 12, 2007 9:19 am
Posts: 503
Location: Falkensee
Country: Germany (de)
Hi,

avoiding these tasks is an art of its own :mrgreen: A useful diagram would allow a drill-down on every stage, which is just not doable - there are always limitations. And there is the problem that this would be a (comprehensible) techie's view of the problem. It would never be used by someone who is making decisions. I know it's sort of the holy grail for salespeople - showing a management cockpit on BO and drilling down somewhere, and all numbers change accordingly in no time. This is the same sort of thing - only works on sales presentations, no practical use.

with best regards

Jens


Top
 Profile E-mail  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Tue Dec 20, 2011 12:12 pm 
Offline

Joined: Thu Apr 05, 2007 12:15 pm
Posts: 969
Location: Bochum
Country: Germany (de)
It won't be used by management. It's part of our technical documentation.

_________________
"Money is likewise the greatest chance and the greatest scourge of mankind."


Top
 Profile  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Tue Dec 20, 2011 12:29 pm 
Offline

Joined: Thu Apr 05, 2007 12:15 pm
Posts: 969
Location: Bochum
Country: Germany (de)
DenSter wrote:
And what else than a data flow diagram did you have in mind?

I was thinking about Sequence Diagram and Flowchart diagram, but I'm not sure about what's the best.
Data Flow Diagrams are good to display a general data flow. But e.g. there's no option for decision or loops. I'm not sure if this is sufficient to display the whole ETL process.

_________________
"Money is likewise the greatest chance and the greatest scourge of mankind."


Top
 Profile  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Tue Dec 20, 2011 12:33 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
einsTeIn.NET wrote:
It won't be used by management. It's part of our technical documentation.



In that case ignore my earlier advise where I said to create 3 or 4 pages at the beginning. Instead just create a binder with a pretty cover and filled with blank pages. I think we all know that techie people are never going top even open any documentation. :mrgreen:

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Tue Dec 20, 2011 12:56 pm 
Offline

Joined: Thu Apr 05, 2007 12:15 pm
Posts: 969
Location: Bochum
Country: Germany (de)
I always ignore your advises. :mrgreen:

You can't do any data analysis based on blank pages. And if there's no technical documentation you'll always have to analyse the source code of the different steps of the ETL process. That's maybe to difficult for someone who don't know about the different tools that are used. And that would take much more time than simply take a look into the documentation.

_________________
"Money is likewise the greatest chance and the greatest scourge of mankind."


Top
 Profile  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Tue Dec 20, 2011 4:00 pm 
Offline

Joined: Sat May 12, 2007 9:19 am
Posts: 503
Location: Falkensee
Country: Germany (de)
Interesting discussion... If you find a good solution, let me know. I'm afraid there is no readily available tool for this. I've been using Hyperion FDQM for a while, and had a go at Oracle EPMA. Those tools are so... un-usable (even worse than RDLC :wink: ) that the consultants selling this stuff had excel templates to handle the load of the definitions... EPMA is supposed to be a modeling and data flow tool, somehow. Not working.
I would try to go and build a SVN repository of the ETL scripts used. This way you know when someone has changed things. But it's not good (schematic) documentation.

with best regards

Jens


Top
 Profile E-mail  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Thu Dec 29, 2011 6:59 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Fri Jun 11, 2004 9:05 pm
Posts: 5013
Location: Los Angeles, CA
Country: United States (us)
einsTeIn.NET wrote:
Come on, I can't believe no one in here ever did such kind of diagram.


It's been done typically using Visio or Word or Excel. Nobody talks about it because the time and effort vs. the value you gained from it is marginal. In another words, a waste of time.

The better way to go about documenting is document it as you do development. This way, putting all those documents in a big binder won't seem like such a large task.

_________________
Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work

Getting Started with Dynamics NAV 2013 Application Development = my book


Top
 Profile E-mail WWW  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Thu Dec 29, 2011 7:47 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Aug 14, 2001 7:01 am
Posts: 5349
Location: Prague
Country: Czech Republic (cz)
My favorite two pieces of computer documentation.

Anyone that Learned FORTRAN IV will remember this

Quote:
The primary purpose of the DATA statement is to give names to constants; instead of referring to PI as 3.141592653589797, at every appearance, the variable PI can be given that value with a DATA statement, and used instead of the longer form of the constant. This also simplifies modifying the program, should the value of PI change.


And the IBM classic

Quote:















This page intentionally left blank.


I agree with Alex's comments.

There is no point in creating documentation that costs more than it will ever benefit.

_________________
David Singleton
Dynamics NAV Freelancer
Dynamics Book
Go-Live International


Top
 Profile E-mail WWW  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Tue Jan 03, 2012 10:08 am 
Offline

Joined: Thu Apr 05, 2007 12:15 pm
Posts: 969
Location: Bochum
Country: Germany (de)
I don't think it's a waste of time. What about if the developer of the ETL process leaves the company and nothing is documented? You can't do something like revers engineering everytime you want to change or approve a certain figure. Without such a documentation no one except a developer could ever check anything in your ETL process.

_________________
"Money is likewise the greatest chance and the greatest scourge of mankind."


Top
 Profile  
 
 Post subject: Re: How to display ETL data flow?
PostPosted: Tue Jan 03, 2012 10:11 am 
Offline

Joined: Thu Apr 05, 2007 12:15 pm
Posts: 969
Location: Bochum
Country: Germany (de)
:-$ David... here's a secret... PI will never change. :wink:

You are right, documentation is always an unloved job. Even in NAV there are some Fields that aren't explained enough in the help, e.g. Date Filter in Currency:
Quote:
This field is used internally by the program.

_________________
"Money is likewise the greatest chance and the greatest scourge of mankind."


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 15 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 0 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: