Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Driven Architecture


Data Driven Architecture

Author
Message
jcraddock
jcraddock
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 99

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jcraddock/datadrivenarchitecture.asp





iloya
iloya
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 5
I am impressed by the simplicity with which this article has been authored. Thanks for the knowledge and making a complex topic simple for me.



Dienst ICT
Dienst ICT
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 2
The .VB files seem to be removed from the zip file!

Jamie Thomson
Jamie Thomson
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 188

Dynamic code such as this is something my company try to practise and preach. In my current data warehouse project we have built a metadata layer that lists the warehouse tables that we process data for. By employing a standard naming convention for the SPs that do the ETL we are able to generate a list of tables for which data needs to be processed and then dynamically call the requisite SPs. This has the added advantage that we don't have to call ETL SPs that we don't need to (perhaps because not all source data is supplied every day of the week - as is the case on the project in question).

This takes alot of work initially to set up the metadata and the code to take advantage of it but thereafter adding new bits of functionality is very easy. Not having to explicitly call each piece of new ETL code is fantastically powerful and gives us a quick turnaround of chalkboarded design to a full implementation.

Definately the way forward.

Thanks for the article...I enjoyed reading it.



Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson
thormj
thormj
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 1

I agree that this is pretty nifty; I use a similar getup for generating reports using SQLMail. That said, I've found some problems using the idea:

  1. No dependencies -- The SQL statements are not "in" SQL's knowledge, so determining if something breaks if you change the name of a table/column is not as easy as a sproc.
    This one has been a biggie. We have a report that transfers data from one database to another with some calculations. There is no way to do "select lhs, measure, scale, offset" when all the calculation code is in the query in the table.
  2. Query isn't cached -- Dynamic queries are a bad thing (as was told to me... my db isn't big enough to care)

That said, I've mitigated some of the problems by adding Yet-Another-Layer:

  1. There is a table that has queries, descriptions, and I/O parameters
  2. The queries *mostly* point to stored procedures or views. They sometimes have "formatting" peices (eg, casting everything to nvarchar & concatenating it so it can be handed off to SQLMail), but they don't reference anything but the sprocs.

The upside is that DB dependencies are tracked in SQL server for you, and you have a "List of queries to do things."

The downside is that you really should make a new stored procedure for the joining operations -- I don't know how effective joining a bunch of views together would be, so its not as "reusable/configurable" as the scheme in the article.

Although all of this is beginning to sound very similar to the master database tables... I've been thinking that I could create a few simple "filter" tables (eg, Object X, Column Y is visible as Name) that I would join with the sysobjects/syscolumns/whatnot. I guess this wouldn't be at all portable among databases





jcraddock
jcraddock
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 99

Here ya go:

DOWNLOAD





Vendoran
Vendoran
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 339

I've been researching and working the last couple of weeks on Extended Properties and metadata, good timing. I'm looking forward to working with the samples, however while the new file provided ClassFile.vb it also gives a bad zip file offset error when unzipping and does not unzip DataDrivenArchitecture.ERI or DataDriveArchitecture.sql. The original file didn't have them included either.

Thanks





jcraddock
jcraddock
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 99
Fixed. Download from my link above and the files should be there. The ER1 file is an ERwin file. It is only helpful if you have ERwin.



jgoodwin
jgoodwin
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 10

We have been using this idea internally for years in a winforms app. Our base product is very soft and intelligent, allowing you to basically design the database and not have to worry about the UI. Once the data structure is in place, you just add items to the menu that point to a view/table/sp and the interface is done.

The interface does things like automatically give a lookup window for FK columns, a date picker for datetime colums, a checkbox for bit, provide all neccsesary navagation buttons, etc. You can then change column/form lables, hide columns, add hyperlinks, add buttons, etc. Parent/child layouts can be specifically defined, or can be told to generate automatically based on partial PKs containing an FK to another tables complete PK.

There is a lot more functionality we've built in over the years, allowing us to very quickly develop and deploy applications. A side effect of this has become the ability to throw our tool at existing databases to provide extended functionality for existing applications instantly.

We are in the process of polishing it and are going to offer it for sale toward the middle of this year. If anyone is interested you can get some minimal further info at http://www.datasofttools.com (excuse the unfinished site, as I said we are looking at mid year to release to public).





Steve Walcher
Steve Walcher
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 29
list.aspx.vb is still missing.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search