Generic staging design of data warehouse

I have a question about staging design using SSIS. Has anyone come up with an ETL design that would read table names from a generic table and dynamically create the ETL to stage the table.

1. Have a generic table which would have table name and description and whatever else that was required.

2. Have a master ETL that would enumerate through the table and stage all the table names found in the generic table.

This way I wouldn't have to create an ETL which would hardcode the names of 300-500 tables and have the appropriate 300-500 data sources and targets listed.

Not sure if I am making sense but I hope someone understands the attempt.

thanks

Assuming you know the metadata of each table this should be possible. It won't be easy - but possible.

You'll need to generate a package using code. There are examples in BOL and this post should help out - http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

-Jamie

|||

Thanks for the the lead.

In my case, I'm trying to read from Oracle and move the table(s) to SQL Server. I assume this would not work similarly as the task that is mentioned is only from SQL Serer to SQL Server.

|||

In Oracle, you can hit some of the system tables to generate them like all_tables and all_tab_columns to retrieve the metadata you would need to do this. Like Jamie said, it will be a lot of work to do and you'll learn a lot about the object model by doing it :).

-- Brian Knight

|||

I apologize for not being more explicit with my requirements. I already know all the Oracle tables I need which with Oracle Applications can run anywhere up to 10000 tables depending on which applications are installed.

In our staging strategy where we move our Oracle tables to SQL Server we'd like to get away from creating N number of packages or one large package with N number of tables we require which could be at least 500. The idea of having to hardcode 500 OLE DB source objects and 500 OLE DB target objects seems a little archaic at best to me. I'm just wondering if a better strategy would be to be able to access a SQL Server table where we would maintain a list of those tables required (which would include more metadata information for our own operations staff which we can't using the Oracle FND_TABLES or ALL_TABLES/ALL_OBJECTS).

We would then have one ETL job that would essentially read from this table and dynamically create the 500 OLE DB source and target objects required to complete our daily staging routines.

The prior example which Jamie eluded to seems to only work with SQL Server source and target situations. Is there some way we could accomplish this using Oracle source to SQL server target and how would I best get started on this?

thanks

|||

Yes, the example only uses SQL Server sources and targets but the theory and the approach are roughly the same. It is still just an OLE DB source so really, there isn't any difference. just a different connection string.

-Jamie

|||You could use the Import Data context menu to not only build your Staging Schema but also to build your extract package.

On the first Import Data process, link to your Oracle box and select

the tables you want to import. You can optionally set destination

column types. If you have clob/blob columns, you will have to

process them manually. Once your done setting your options, just

run the package.

On the second Import Data process select the same tables. Here is

the annoying part, for each table you need to set the option to

append. When your finished, save the package. You'll then

have an extract package for all 500 tables.

Larry Pope|||

If you know T-SQL and PLSQL well enough I'd not bother with SSIS...

Just create set of scripts based on meta-data that:

- create target table

- dumps text file with data

- load tables in SQL Server field by field (may be by using Bulk Insert)

- map data types when needed for blobs and so on

The scripts would accept a table name as parameter. Then loop through system table in Oracle Db and run that script.

I don't think SSIS transformation toolset is mature enough to do things like that...but you can use SSIS or out it all together in a workflow.

|||

I guess I don't completely understand, sorry!!! Where is the Import Data context menu?

It still sounds like much manual work. I was hoping for something more dynamic.

Sorry, for the questions but I'm new to SSIS. I have been using Business Objects Data Integrator product which is quite different in many aspects.

thanks

|||The Import Data context menu can be accessed in SQL Management

Studio. Right-Click on the destination database and select

Tasks. The option is near the bottom.

If you don't have the schema setup in your staging database, it is alot

easier to use the wizard to generate your tables. If you don't

make any changes, it should only take a few minutes to build the

schema. But the package will also copy data over as well, so if

you have data in the source it might take awhile.

As far as the amount of manual work, that's a matter of opinion.

Would you rather develop a package that can read tables/schema from

Oracle, checks tables/schemas on MSSQL and dynamically populated them

OR run through a wizard and manually set the append option on each

individual table.

There are advantages and disadvantages to both. One big advantage

to the dynamic approach is that the destination table(s) will

automatically be updated with schema changes. The big advantage

of the Import Data method, is that it will take significantly less time

implement.

Larry Pope|||

Thank you very much for the additional information and guidance. I'll take a look at your recommendations.

John

Generic Database Design

Hello,

Can someone please guide me to online resources or books on how to design generic database? I mean, I can have a "Record" that can have a set of fixed fields like: ID, Title, CreatedOn, etc ... and I can add as many properties I want to that record in a vertical way.

Then based on the generic tables present, I can fit in the same table, a Record for Customer, a Record for House, a Record for Order. All share the same set of Fields, but each has its own set of proeprties.

This way, I design my database once, and customize it to fit any type of applications I need.

Is there something like that or just dreaming?

thanks

One way you can go about doing this is build a tool to generate the necessary sql to create the database tables. You can even have it create the stored procedures and code behind to access it. Im not sure if there are tools out there for this, I just wrote my own.|||

Thanks for the reply!

Maybe I was misunderstood. I am asking about having a database design that fits all kinds of applications. I mean design the database once and use it for all my applications. I talked about notion of Record that can hold any object!!

Any more ideas?

Thanks

|||

Perhaps you are talking about the model database in SQL Server. The model database in SQL Server is a template database which can be generalized so that every database created afterwards includes some pre-generated objects. Below are some of the links that you may find useful.

http://articles.techrepublic.com.com/5100-9592_11-6126240.html

http://www.sswug.org/see/30280

Explain your problem in details so that we can help you better.

Hope this will help.

|||

Hello,

Thanks for the reply.

My point is that I want to design a database schema that can fit any application. I mentioned about Record table. This table can hold information about any object regardless of its nature. All objects share a set of predefined fields. I can also assign to each object a number of properties that might be defined somewhere else also in another data table.

My idea is to have a single Database Design that I can use to make it fit for any application. for instance, if I have an application to handle customers and orders, I would define a Record for each object.

Record for Customer

Record for Order

In the database I don't need to do anything, but define two record instances. one for Customer and one for Order. Add any proeprties to each. On the other side, I would develop an API that deals with Records only, regardless if this record is Customer or Order. Each record would have a set of predefined fields and a collection of properties.

Am I making any sense?

Thanks

|||

Its possible I still dont understand you, but how many columns (properties) could every record possible share? Like theres an ID but other than that you are not going to need the address and phone number for Order, but you will for Customer. Im not sure what you hope to accomplish by doing this. Do you want to be able to programmitcally add new columns to a table?

Or are you talking about assuming that you will need an Order table and a Customer table in every project you work on, and you want to create almost a template that you can modify to fit the needs of some specific project?

|||

Hello,

What I am thinking of is to have a notion of Record. A customer is stored in one record and an order is stored in one record, a student is stored in one record, etc ... This includes only basic fields.

Also, I want another table call it Property, that holds additional proeprties of each record. By using properties I can customize my database to configure any new website's objects with the propeties required. So you can think of having one framework for the database design that can fit any project.

Does it make any sense?

Thanks

sql

Generic data types in SQL Server

Hi, I have a best question practice about database design. My idea is to
store business rules information for my customers. Basically, I define a set
of rules. For each rule, I know the type. Then I want to describe the
customers' sets of rules.
I want a table of CustomerID, RuleID, Value
Example:
Rule 1 = tax rate (Currency)
Rule 2 = Fiscal Year Start (Date)
Rule 3 = Have website (Boolean)
Then, my table might look like this: (CustomerID, RuleID, Value)
123,1,7.5
123,2,#1/1/5#
123,3,True
Obviously, I can't have a single column in SQL Server 2000 that holds
variant types. One solution is to have a column for each type and keep Null
values in all of them except the correct type column. That doesn't seem
right.
One suggestion was to cast the value to Binary, store it as such and then
cast the Binary value back to the expected data type when we need to use it.
Would that work in all cases?
Any thoughts are appreciated.
John,
these are not 'business rules'...these are attributes of customer and
would most effectively be stored as separate attributes....overtyping
fields based on other fields is something we did in languages like
cobol to save space and coding.....not really something we should do
in an environment where the end user is supposed to query the data.
Best Regards
Peter Nolan
www.peternolan.com
|||To add to Peter's reply, what you're doing is creating what is known as an
OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
very common mistake that developers new to databases make, and it can cause
severe data integrity problems. Please refer to:
http://groups.google.com/groups?hl=e...lt&qt_s=Search
http://groups.google.com/groups?hl=e...av&qt_s=Search
http://groups.google.com/groups?hl=e...rm&qt_s=Search
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
> Hi, I have a best question practice about database design. My idea is to
> store business rules information for my customers. Basically, I define a
> set
> of rules. For each rule, I know the type. Then I want to describe the
> customers' sets of rules.
> I want a table of CustomerID, RuleID, Value
> Example:
> Rule 1 = tax rate (Currency)
> Rule 2 = Fiscal Year Start (Date)
> Rule 3 = Have website (Boolean)
> Then, my table might look like this: (CustomerID, RuleID, Value)
> 123,1,7.5
> 123,2,#1/1/5#
> 123,3,True
> Obviously, I can't have a single column in SQL Server 2000 that holds
> variant types. One solution is to have a column for each type and keep
> Null
> values in all of them except the correct type column. That doesn't seem
> right.
> One suggestion was to cast the value to Binary, store it as such and then
> cast the Binary value back to the expected data type when we need to use
> it.
> Would that work in all cases?
> Any thoughts are appreciated.
>
|||I looked at the first search and at the first thread that came up. OTLT was
advocated (amongst others) by a guy from HP Openview. I have worked with HP
Openview and I though it was a pile of junk*. I now understand why.
* clearest memory of that symptom: having a dialog box with 3 buttons, 2 of
which had the same keyboard shortcut
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eftQlTVzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> To add to Peter's reply, what you're doing is creating what is known as an
> OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
> very common mistake that developers new to databases make, and it can
> cause severe data integrity problems. Please refer to:
> http://groups.google.com/groups?hl=e...lt&qt_s=Search
> http://groups.google.com/groups?hl=e...av&qt_s=Search
> http://groups.google.com/groups?hl=e...rm&qt_s=Search
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
> news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
>
|||Hi Adam,
wow, it even has a name? And I've not heard of it? That's something.
We have done things like encode large numbers of miscellaneous codes
into one lookup table to save ourselves the paid of having large
numbers of lookup tables...and one operational system I used to use at
IBM was the Common Table Management System where very large numbers of
code tables were stored....but these were not attributes of a defined
entity...or they were used to decode code stored on the original
entity when we wanted to save space....not an issue any more..
Peter

Generic data types in SQL Server

Hi, I have a best question practice about database design. My idea is to
store business rules information for my customers. Basically, I define a se
t
of rules. For each rule, I know the type. Then I want to describe the
customers' sets of rules.
I want a table of CustomerID, RuleID, Value
Example:
Rule 1 = tax rate (Currency)
Rule 2 = Fiscal Year Start (Date)
Rule 3 = Have website (Boolean)
Then, my table might look like this: (CustomerID, RuleID, Value)
123,1,7.5
123,2,#1/1/5#
123,3,True
Obviously, I can't have a single column in SQL Server 2000 that holds
variant types. One solution is to have a column for each type and keep Null
values in all of them except the correct type column. That doesn't seem
right.
One suggestion was to cast the value to Binary, store it as such and then
cast the Binary value back to the expected data type when we need to use it.
Would that work in all cases?
Any thoughts are appreciated.John,
these are not 'business rules'...these are attributes of customer and
would most effectively be stored as separate attributes....overtyping
fields based on other fields is something we did in languages like
cobol to save space and coding.....not really something we should do
in an environment where the end user is supposed to query the data.
Best Regards
Peter Nolan
www.peternolan.com|||To add to Peter's reply, what you're doing is creating what is known as an
OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
very common mistake that developers new to databases make, and it can cause
severe data integrity problems. Please refer to:
http://groups.google.com/groups?hl=...tlt&qt_s=Search
http://groups.google.com/groups?hl=...eav&qt_s=Search
http://groups.google.com/groups?hl=...orm&qt_s=Search
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
> Hi, I have a best question practice about database design. My idea is to
> store business rules information for my customers. Basically, I define a
> set
> of rules. For each rule, I know the type. Then I want to describe the
> customers' sets of rules.
> I want a table of CustomerID, RuleID, Value
> Example:
> Rule 1 = tax rate (Currency)
> Rule 2 = Fiscal Year Start (Date)
> Rule 3 = Have website (Boolean)
> Then, my table might look like this: (CustomerID, RuleID, Value)
> 123,1,7.5
> 123,2,#1/1/5#
> 123,3,True
> Obviously, I can't have a single column in SQL Server 2000 that holds
> variant types. One solution is to have a column for each type and keep
> Null
> values in all of them except the correct type column. That doesn't seem
> right.
> One suggestion was to cast the value to Binary, store it as such and then
> cast the Binary value back to the expected data type when we need to use
> it.
> Would that work in all cases?
> Any thoughts are appreciated.
>|||I looked at the first search and at the first thread that came up. OTLT was
advocated (amongst others) by a guy from HP Openview. I have worked with HP
Openview and I though it was a pile of junk*. I now understand why.
* clearest memory of that symptom: having a dialog box with 3 buttons, 2 of
which had the same keyboard shortcut
Jacco Schalkwijk
SQL Server MVP
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eftQlTVzFHA.2076@.TK2MSFTNGP14.phx.gbl...
> To add to Peter's reply, what you're doing is creating what is known as an
> OTLT (One True Lookup Table) or EAV (Entity Attribute-Value). This is a
> very common mistake that developers new to databases make, and it can
> cause severe data integrity problems. Please refer to:
> http://groups.google.com/groups?hl=...tlt&qt_s=Search
> http://groups.google.com/groups?hl=...eav&qt_s=Search
> http://groups.google.com/groups?hl=...orm&qt_s=Search
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "JohnMSyrasoft" <JohnMSyrasoft@.discussions.microsoft.com> wrote in message
> news:6EFAB117-BD1F-48FF-B58F-C7DD61B13A86@.microsoft.com...
>|||Hi Adam,
wow, it even has a name? And I've not heard of it? That's something.
We have done things like encode large numbers of miscellaneous codes
into one lookup table to save ourselves the paid of having large
numbers of lookup tables...and one operational system I used to use at
IBM was the Common Table Management System where very large numbers of
code tables were stored....but these were not attributes of a defined
entity...or they were used to decode code stored on the original
entity when we wanted to save space....not an issue any more..
Peter

generating a database design from xml

Hi, I'm looking for a way to take an XML feed and basically do what this article describes: http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/(In short, take in XML, generate a database generation script, creating that database, then load the XML data into said database.)But, the above article involves user interaction and I would like this all automated, ie: in .NET 2.0/C#Does anyone know how to do this?Cheers,Matt.

Himatthewwebster, I read your previously post and give some advise there, please check whehter it works for your issue:)

http://forums.asp.net/1266225/ShowThread.aspx

generate sp using wizards - have problem

When I change some tables dbowner (design table > table and index properties > owner) I can’t generate sp using wizards (tools > wizards > database > create stored procedures wizards). What is the problem? (User dbo haven’t any problem)

Which application are you using for that ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Tharindu Dhaneenja wrote:

When I change some tables dbowner (design table > table and index properties > owner) I can’t generate sp using wizards (tools > wizards > database > create stored procedures wizards). What is the problem? (User dbo haven’t any problem)

does the user have the right to script? i mean permission wise?

|||

I am using sql server 2000. Manually I can write sp using my table

Eg : select EmpNo, EmpName from tharindu.emp (0 errors)

Sp name: tharindu.insertEmp_sp (after exec 0 errors (normal insert update sp))

My problem is I can’t create sp (insert record sp) using wizards.

|||

hi,

\which wizard are you referring to?

if it is the dot.net wizard, you need a primary key on the table

or you should be using the required data provider in the connection string

thanks

joey

|||

Run sql server enterprise manager > tools > wizards > database > create stored procedures wizards.

|||

could it be permission problem.

check if you have rights to create proc in the Db

|||

Yes I have rights to create proc in the Db, for example after login SQL query analyzer using my user (“Tharindu”) I can create, delete all sp and tables. But only problem is I can’t create sp using wizards.

This error prompt,

http://tharindu.50webs.com/err/error.htm

|||

Finally I see my error jpg doesn’t open.

Still can not find good solution I think sql server wizards support only user “dbo”

|||

More information about this matter read this, Generate sp using wizards display flowing Error…(Error 21776)

generate sp using wizards - have problem

When I change some tables dbowner (design table > table and index properties > owner) I can’t generate sp using wizards (tools > wizards > database > create stored procedures wizards). What is the problem? (User dbo haven’t any problem)

Which application are you using for that ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Tharindu Dhaneenja wrote:

When I change some tables dbowner (design table > table and index properties > owner) I can’t generate sp using wizards (tools > wizards > database > create stored procedures wizards). What is the problem? (User dbo haven’t any problem)

does the user have the right to script? i mean permission wise?

|||

I am using sql server 2000. Manually I can write sp using my table

Eg : select EmpNo, EmpName from tharindu.emp (0 errors)

Sp name: tharindu.insertEmp_sp (after exec 0 errors (normal insert update sp))

My problem is I can’t create sp (insert record sp) using wizards.

|||

hi,

\which wizard are you referring to?

if it is the dot.net wizard, you need a primary key on the table

or you should be using the required data provider in the connection string

thanks

joey

|||

Run sql server enterprise manager > tools > wizards > database > create stored procedures wizards.

|||

could it be permission problem.

check if you have rights to create proc in the Db

|||

Yes I have rights to create proc in the Db, for example after login SQL query analyzer using my user (“Tharindu”) I can create, delete all sp and tables. But only problem is I can’t create sp using wizards.

This error prompt,

http://tharindu.50webs.com/err/error.htm

|||

Finally I see my error jpg doesn’t open.

Still can not find good solution I think sql server wizards support only user “dbo”

|||

More information about this matter read this, Generate sp using wizards display flowing Error…(Error 21776)

generalization/specialization

I have a db design which includes generalization/specialization. How can I implement this in sql server?

thanks so much!

I think that you are just referring to a Parent-Child relationship, or a hierachical data model.

You 'should' be ok with using SQL Server for data storage.

|||

OO Gen/Specs can often be modeled as SuperTypes/SubTypes in relational databases. The key in the SuperType is carried down to the subtype, the subtype has the "specialized" attributes, supertype has the common attributes. In some cases, especially with busy OLTP systems, you may want to consider a single table with nullable columns for the subtype attributes that may not apply to all rows in the table. But database design isn't address very well in forums, newsgroups. You'd probably want to read up on supertypes/subtypes in relational database artcles and books and figure the best approach for you particular business needs.

I think Louis Davidson handles the subject in a simple, straight forward manner in his book from Apress:

Pro SQL Server 2005 Database Design and Optimization.

-Sue