Post reply

Data Driven Architecture

  • February 14, 2005 at 10:41 pm

    #163685

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

     

  • February 17, 2005 at 12:18 am

    #542710

    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.

  • February 17, 2005 at 2:07 am

    #542732

    The .VB files seem to be removed from the zip file!

  • February 17, 2005 at 6:10 am

    #542779

    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

  • February 17, 2005 at 7:55 am

    #542809

    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

     

  • February 17, 2005 at 8:54 am

    #542832

    Here ya go:

    DOWNLOAD

  • February 17, 2005 at 11:20 am

    #542898

    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

  • February 17, 2005 at 12:07 pm

    #542910

    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.

  • February 17, 2005 at 12:38 pm

    #542915

    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).

  • February 19, 2005 at 11:54 am

    #543222

    list.aspx.vb  is still missing.

  • February 22, 2005 at 9:29 am

    #543517

    Bah.  Sorry about that.  I've tried 4 times now to get all the files.  Visual studio needs some nice way of packaging this up.

     

    Anyway, I have replaced the file again.

  • February 22, 2005 at 10:04 am

    #543525

    Actually the first .ZIP file you posted was created correctly.  However, either your virus scanning software or a firewall is stripping out the .VB files

     

    (from 6_WARNING.HTM)

    -----------------------------------------------------------------------------

    BLOCKED FILE ALERT

    A file has been blocked due to the 'Dangerous Attachment Block' rule. See your system administrator for further information.

    Context: 'ASSEMBLYINFO.VB'

    Disallowed due to filename

    OUHSC Postmaster

    Copyright © 1993-2003, Networks Associates Technology, Inc

    -----------------------------------------------------------------------------

  • February 23, 2005 at 2:47 pm

    #543791

    Well, the one I posted on my own site should be working.  Since it was never emailed via our email system.

    If not, I can try try again.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply