Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CDBanks
Starting Member
23 Posts |
 Posted - 2005-12-06 : 15:34:23
|
| I am working on developing a relational dB from my POS (point of sale) system for a restaurant business. The situation I have is a follows:1) There is no schema to work from so this truly represents a blank slate.2) We are fairly new to using DTS and SQL 2000.3) I want to get information into a relational structure so that I may begin using reporting & analysis services.The question we have is: would we be better off normalizing the data using the import process or should we bulk import the information and then use views to normalize the data for reporting and analysis?Any advice received would by greatly appreciated.The dbf file that I am working with contains:1) StoreID2) Date of Business3) Period (Part of the Day Open-2, 2-5, 5-close)4) Type (5 = Comp, 31 = Net Sales)5) Type ID (Comp ID, Order Mode)I have a listing that tells me what number relates to which type. For example, 5 = Comps by CompID, 19 = Comps by Sales Category, 31 = Net Sales by Order Mode, 10 = Total Guests, 49 = Guests by Order Mode, etc. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-12-06 : 16:35:37
|
| Usually, there is a two-step process:1) DTS the data directly into staging tables that mirror the format of the data your are importing (i.e, denormalized, all varchar columns, etc)2) Then, once the staging tables are filled up, use standard SQL to validate the data and move it into the properly normalized tables in your db.Just because the import is in a certain format does not require you to store it in that same format. All imports from sources that cannot be controlled should usually go into staging tables first. This also allows you to do things like cancel imports half-way through if necessary, and also you can leave the data there for review (and approval) before moving it to your actual tables. |
 |
|
|
CDBanks
Starting Member
23 Posts |
Posted - 2005-12-07 : 11:34:55
|
| Thanks for the help. That was the avenue we had decided to explore while we were waiting for confirmation from the experts @ SQLTeam.com. |
|
|
|
|
|
|
|
|
Import flat files to SQL server