Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data Driven Architecture Expand / Collapse
Author
Message
Posted Monday, February 14, 2005 10:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 10:36 AM
Points: 104, Visits: 98

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

 




Post #161614
Posted Thursday, February 17, 2005 12:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2008 6:07 AM
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.


Post #162259
Posted Thursday, February 17, 2005 2:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 8, 2008 6:51 AM
Points: 1, Visits: 2
The .VB files seem to be removed from the zip file!
Post #162289
Posted Thursday, February 17, 2005 6:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, November 10, 2013 11:52 AM
Points: 877, 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
Post #162344
Posted Thursday, February 17, 2005 7:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 6, 2005 10:53 AM
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

 




Post #162382
Posted Thursday, February 17, 2005 8:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 10:36 AM
Points: 104, Visits: 98

Here ya go:

DOWNLOAD




Post #162410
Posted Thursday, February 17, 2005 11:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 12:43 PM
Points: 39, Visits: 312

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




Post #162487
Posted Thursday, February 17, 2005 12:07 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 6, 2014 10:36 AM
Points: 104, Visits: 98
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.


Post #162505
Posted Thursday, February 17, 2005 12:38 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 21, 2009 10:33 AM
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).




Post #162514
Posted Saturday, February 19, 2005 11:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 17, 2014 5:36 PM
Points: 37, Visits: 27
list.aspx.vb  is still missing.


Post #162890
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse