Data Structure Advice

Hello,
I am in the process of redesigning a database for client. The advice I need
is how to design a set of tables to reflect what they current use a single
table for.
They have a table that has records that reflect hierarchal data. For
example, they have fields called Level 1, Level 2, Level 3, and Level 4. The
data in Level 1 is repeated many many times. As is Level 2, but not as often,
and so on. Here are examples of types of data found in this table:
- record 1 will have "building" in Level 1, and nothing in the other fields.
- record 2 will have "building" in Level 1 and "Manufacturing in Level 2,
and nothing in the rest of the fields.
- record 3 will have the same in Level 1 and Level 2 but include "office" in
Level 3.
I would like to design a data structure that will not only normalize the
data, but also allow for a dynamic number of levels. Currently, because the
structure is flat, the maximum number of levels is 4.
Thanks in advance!
Jack
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:E9151293-4B0A-4370-844B-53FCF1752ED6@.microsoft.com...
> I would like to design a data structure that will not only normalize the
> data, but also allow for a dynamic number of levels. Currently, because
the
> structure is flat, the maximum number of levels is 4.
Jack,
Depending on your definition of "dynamic", that may not be possible.
A normalized version of what you described would have one table for
Buildings, then a table for Floors (or whatever the level 2 is) with a
foreign key referencing the PK of the Buildings table, then a table for
Offices, with a FK referencing the PK of the Floors table, etc.
More levels could be added to the hierarchy at any time by simply adding
more tables (and, possibly, an addition FK column to the top level table if,
for instance, you had clusters of buildings at different locations).
If, on the other hand, your definition of "dynamic" is that the number of
levels could change at any time by someone inserting or deleting a row,
that's not possible with a normalized structure. Any structure that does
attempt to implement that kind of dynamism for different types will violate
1NF, as you'll be forced to represent differing types (e.g. a Building key
and a Floor key) in the same column. In addition, there's no real win here
because different entities require different attributes. If someone needs
to add, for instance, a Locations level to the hierarchy, that level will
not share the same attributes as Buildings, Floors, or Offices, and
therefore you will still be required to add a new table or new columns.
|||There is a topic in BOL titled "Expanding Hierarchies" that may be of
interest. Searching the NGs will also yield many discussions of
hierarchical data (and the issues with representing and querying the data).
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:E9151293-4B0A-4370-844B-53FCF1752ED6@.microsoft.com...
> Hello,
> I am in the process of redesigning a database for client. The advice I
need
> is how to design a set of tables to reflect what they current use a single
> table for.
> They have a table that has records that reflect hierarchal data. For
> example, they have fields called Level 1, Level 2, Level 3, and Level 4.
The
> data in Level 1 is repeated many many times. As is Level 2, but not as
often,
> and so on. Here are examples of types of data found in this table:
> - record 1 will have "building" in Level 1, and nothing in the other
fields.
> - record 2 will have "building" in Level 1 and "Manufacturing in Level 2,
> and nothing in the rest of the fields.
> - record 3 will have the same in Level 1 and Level 2 but include "office"
in
> Level 3.
> I would like to design a data structure that will not only normalize the
> data, but also allow for a dynamic number of levels. Currently, because
the
> structure is flat, the maximum number of levels is 4.
> Thanks in advance!
> Jack

Data Structure Advice

Hello,
I am in the process of redesigning a database for client. The advice I need
is how to design a set of tables to reflect what they current use a single
table for.
They have a table that has records that reflect hierarchal data. For
example, they have fields called Level 1, Level 2, Level 3, and Level 4. The
data in Level 1 is repeated many many times. As is Level 2, but not as often
,
and so on. Here are examples of types of data found in this table:
- record 1 will have "building" in Level 1, and nothing in the other fields.
- record 2 will have "building" in Level 1 and "Manufacturing in Level 2,
and nothing in the rest of the fields.
- record 3 will have the same in Level 1 and Level 2 but include "office" in
Level 3.
I would like to design a data structure that will not only normalize the
data, but also allow for a dynamic number of levels. Currently, because the
structure is flat, the maximum number of levels is 4.
Thanks in advance!
Jack"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:E9151293-4B0A-4370-844B-53FCF1752ED6@.microsoft.com...
> I would like to design a data structure that will not only normalize the
> data, but also allow for a dynamic number of levels. Currently, because
the
> structure is flat, the maximum number of levels is 4.
Jack,
Depending on your definition of "dynamic", that may not be possible.
A normalized version of what you described would have one table for
Buildings, then a table for Floors (or whatever the level 2 is) with a
foreign key referencing the PK of the Buildings table, then a table for
Offices, with a FK referencing the PK of the Floors table, etc.
More levels could be added to the hierarchy at any time by simply adding
more tables (and, possibly, an addition FK column to the top level table if,
for instance, you had clusters of buildings at different locations).
If, on the other hand, your definition of "dynamic" is that the number of
levels could change at any time by someone inserting or deleting a row,
that's not possible with a normalized structure. Any structure that does
attempt to implement that kind of dynamism for different types will violate
1NF, as you'll be forced to represent differing types (e.g. a Building key
and a Floor key) in the same column. In addition, there's no real win here
because different entities require different attributes. If someone needs
to add, for instance, a Locations level to the hierarchy, that level will
not share the same attributes as Buildings, Floors, or Offices, and
therefore you will still be required to add a new table or new columns.|||There is a topic in BOL titled "Expanding Hierarchies" that may be of
interest. Searching the NGs will also yield many discussions of
hierarchical data (and the issues with representing and querying the data).
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:E9151293-4B0A-4370-844B-53FCF1752ED6@.microsoft.com...
> Hello,
> I am in the process of redesigning a database for client. The advice I
need
> is how to design a set of tables to reflect what they current use a single
> table for.
> They have a table that has records that reflect hierarchal data. For
> example, they have fields called Level 1, Level 2, Level 3, and Level 4.
The
> data in Level 1 is repeated many many times. As is Level 2, but not as
often,
> and so on. Here are examples of types of data found in this table:
> - record 1 will have "building" in Level 1, and nothing in the other
fields.
> - record 2 will have "building" in Level 1 and "Manufacturing in Level 2,
> and nothing in the rest of the fields.
> - record 3 will have the same in Level 1 and Level 2 but include "office"
in
> Level 3.
> I would like to design a data structure that will not only normalize the
> data, but also allow for a dynamic number of levels. Currently, because
the
> structure is flat, the maximum number of levels is 4.
> Thanks in advance!
> Jacksql

Data Migration

Can I get some advice guys?
We have a de-normalized database that is currently fed from Siebel
(CRM). We want to pump in data from Pivotal but there is a slight
problem.
We used the Siebel Table=E2=80=99s identity column for PK in each
corresponding table (contact, account, etc). The record is then pushed
to our Data Warehouse and used for the corresponding dimension=E2=80=99s PK.
My original thought was to simply multiply Pivotal=E2=80=99s identity column
by something like 100,000,000 in the ETL and just pump the data in.
After taking my first look at Pivotal, I noticed that Pivotal does not
use identity columns. Pivotal uses a binary field to uniquely identify
each record.
This is a real mess and I get to be the one to fix it! =EF=81=8C.
We have several cubes and reports in production so I want to try and
minimize the overall impact.
Can you guys help me out with damage control and point me in the right
direction?I don't see a problem. You should be able to generate a new surrogate
key on insert, either in the ETL process or when you load the
dimension. In fact it's conventional practice always to allocate new
surrogate keys in the data warehouse. Of course the surrogate should
never be the only key in a dimension so there should be no problem
uniquely identifying the data row. But maybe I misunderstood what you
are asking.
David Portas
SQL Server MVP
--|||You are going to have to change how your keys are generated, and stop using
the Siebel key in your own DB.
Suggest generating new surrogate keys in your DB for Both Siebel and
Pivotal, and keep the key "mapping" in a separate table,
Create Table KeyMap (SiebelPK Integer Null, PivotalPK Binary(xx) Null,
NewPK Primary Key Identity Integer Not Null)
And then Populate it with all the current existing Siebel records to start
off with:
Set Identity_Insert KeyMap On
Insert KeyMap(SiebelKey, NewPK)
Select Distinct SiebelKey, SiebelKey
From ExistingTable
Change your import process to insert into the key map first, then use
created Identity for the insert into the rest of your data tables...
"daveg.01@.gmail.com" wrote:

> Can I get some advice guys?
> We have a de-normalized database that is currently fed from Siebel
> (CRM). We want to pump in data from Pivotal but there is a slight
> problem.
> We used the Siebel Table’s identity column for PK in each
> corresponding table (contact, account, etc). The record is then pushed
> to our Data Warehouse and used for the corresponding dimension’s PK.
>
> My original thought was to simply multiply Pivotal’s identity column
> by something like 100,000,000 in the ETL and just pump the data in.
> After taking my first look at Pivotal, I noticed that Pivotal does not
> use identity columns. Pivotal uses a binary field to uniquely identify
> each record.
> This is a real mess and I get to be the one to fix it! ?.
> We have several cubes and reports in production so I want to try and
> minimize the overall impact.
> Can you guys help me out with damage control and point me in the right
> direction?
>|||Oh, DOn;t forget to turn off Identity_Insert after you insert all the
existing Siebel Records...
Set Identity_Insert KeyMap Off
"daveg.01@.gmail.com" wrote:

> Can I get some advice guys?
> We have a de-normalized database that is currently fed from Siebel
> (CRM). We want to pump in data from Pivotal but there is a slight
> problem.
> We used the Siebel Table’s identity column for PK in each
> corresponding table (contact, account, etc). The record is then pushed
> to our Data Warehouse and used for the corresponding dimension’s PK.
>
> My original thought was to simply multiply Pivotal’s identity column
> by something like 100,000,000 in the ETL and just pump the data in.
> After taking my first look at Pivotal, I noticed that Pivotal does not
> use identity columns. Pivotal uses a binary field to uniquely identify
> each record.
> This is a real mess and I get to be the one to fix it! ?.
> We have several cubes and reports in production so I want to try and
> minimize the overall impact.
> Can you guys help me out with damage control and point me in the right
> direction?
>|||Oh, Don't Forget to tuen off the Identity_Insert after you insert all the
existing Siebel Records..
Set Identity_Insert KeyMap Off
"daveg.01@.gmail.com" wrote:

> Can I get some advice guys?
> We have a de-normalized database that is currently fed from Siebel
> (CRM). We want to pump in data from Pivotal but there is a slight
> problem.
> We used the Siebel Table’s identity column for PK in each
> corresponding table (contact, account, etc). The record is then pushed
> to our Data Warehouse and used for the corresponding dimension’s PK.
>
> My original thought was to simply multiply Pivotal’s identity column
> by something like 100,000,000 in the ETL and just pump the data in.
> After taking my first look at Pivotal, I noticed that Pivotal does not
> use identity columns. Pivotal uses a binary field to uniquely identify
> each record.
> This is a real mess and I get to be the one to fix it! ?.
> We have several cubes and reports in production so I want to try and
> minimize the overall impact.
> Can you guys help me out with damage control and point me in the right
> direction?
>|||Thanks for the suggestion!
It sounds like that is exacally what I need to do.

Data from Excel into SQL

Please excuse the cross posting I was not sure which newsgroup would produce
the required advice.
I have a requirement to update a SQL table with data from an Excel
worksheet.
Can anyone suggest the best way to do this...ADO??
PWS
There are numerous variables here. Is this a one-time requirement, or an on-going need? Do you
want to write a program, or just use a utility? If programming, what languages are you open to
using?
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would produce the required advice.
> I have a requirement to update a SQL table with data from an Excel worksheet.
> Can anyone suggest the best way to do this...ADO??
> PWS
>
|||ADO is a great way - IMHO. There are many others too. If you fancy
taking the ADO route here's a few useful links:
'Exhaustive list of objects, properties, methos etc.
http://www.devguru.com/Technologies/...cts_index.html
'getting started
http://msdn.microsoft.com/library/de...tml/sqlado.asp
'a good FAQ for commonly experienced problems - beats waiting for
replies on an NG!
http://www.oblique.ch/ms/ADO_Faq.html
And here's some sample code to get you started. Note, there's many ways
to work with ADO and Access - I use RecordSet to retrieve data and
Command to insert or update. Seems to work ok...
HTH
Gareth
'--
Private Const myDBConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0"
Private Const myDBLocation As String = "C:\myDB.mdb"
Private rs As ADODB.Recordset
Private cn As ADODB.Connection
'Once we've opened a connection we leave it open!
'This is closed upon closing the workbook.
Private Function fcnConnectToDB() As Boolean
'Check whether the connection is already open.
If Not cn Is Nothing Then
If cn.State <> adStateClosed Then
fcnConnectToDB = True
Exit Function
End If
End If
'Open connection the database
Set cn = New ADODB.Connection
With cn
.Errors.Clear
On Error Resume Next
.CursorLocation = adUseClient
.connectionString = myDBConnectionString
.Open myDBLocation
On Error GoTo 0
If .Errors.Count = 0 Then fcnConnectToDB = True
End With
End Function
'
'Returns False if there was a error
'Else returns array containing of returned records
Public Function ADO_RunQuery(mySQL As String) As Variant
'Check we're connected to the database
fcnConnectToDB
'Open the recordset.
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockPessimistic 'adLockBatchOptimistic
.Open mySQL 'cmd
End With
'Dump the record set into an array
If rs.RecordCount = 0 Then
ADO_RunQuery = False
Else
ADO_RunQuery = rs.GetRows
End If
rs.Close
'tidy up
Set rs = Nothing
'(We leave the connection permanently open - you may like to
' close it afterwards)
End Function
Public Function ADO_UpdateDB(mySQL As String) As Boolean
'Check we're connected to the database
fcnConnectToDB
'Set the command text and execute
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = mySQL
.CommandType = adCmdText
On Error Resume Next
.Execute
If Err.Number = 0 Then ADO_UpdateDB = True
On Error GoTo 0
End With
'tidy up
Set cmd = Nothing
'(We leave the connection permanently open)
End Function
Paul Smith wrote:
> Please excuse the cross posting I was not sure which newsgroup would produce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO??
> PWS
>
|||This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html
Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm
It demonstrates how to use SQL in Excel's VBA to:
* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.
DAO and ADO files available.
You can also download the demonstration file called "excelsql.zip".
The code is open and commented.
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would
produce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO??
> PWS
>
|||I found the chicken vindaloo recipe particularly useful too - made it,
ate it, loved it. Thanks
Andy Wiggins wrote:
> This might be a help for getting data to and from Excel and Access: It
> includes examples of using variables in SQL queries.
> http://www.bygsoftware.com/examples/sql.html
> Or you can get there from the "Excel with Access Databases" section on page:
> http://www.bygsoftware.com/examples/examples.htm
> It demonstrates how to use SQL in Excel's VBA to:
> * create a database,
> * create a table
> * insert records
> * select records,
> * update records,
> * delete records,
> * delete a table,
> * delete a database.
> DAO and ADO files available.
> You can also download the demonstration file called "excelsql.zip".
> The code is open and commented.
>

Data from Excel into SQL

Please excuse the cross posting I was not sure which newsgroup would produce
the required advice.
I have a requirement to update a SQL table with data from an Excel
worksheet.
Can anyone suggest the best way to do this...ADO'?
PWSThere are numerous variables here. Is this a one-time requirement, or an on
-going need? Do you
want to write a program, or just use a utility? If programming, what langua
ges are you open to
using?
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would produ
ce the required advice.
> I have a requirement to update a SQL table with data from an Excel workshe
et.
> Can anyone suggest the best way to do this...ADO'?
> PWS
>|||ADO is a great way - IMHO. There are many others too. If you fancy
taking the ADO route here's a few useful links:
'Exhaustive list of objects, properties, methos etc.
http://www.devguru.com/Technologies...ects_index.html
'getting started
http://msdn.microsoft.com/library/d...
ado.asp
'a good FAQ for commonly experienced problems - beats waiting for
replies on an NG!
http://www.oblique.ch/ms/ADO_Faq.html
And here's some sample code to get you started. Note, there's many ways
to work with ADO and Access - I use RecordSet to retrieve data and
Command to insert or update. Seems to work ok...
HTH
Gareth
'--
Private Const myDBConnectionString As String =
"Provider=Microsoft.Jet.OLEDB.4.0"
Private Const myDBLocation As String = "C:\myDB.mdb"
Private rs As ADODB.Recordset
Private cn As ADODB.Connection
'Once we've opened a connection we leave it open!
'This is closed upon closing the workbook.
Private Function fcnConnectToDB() As Boolean
'Check whether the connection is already open.
If Not cn Is Nothing Then
If cn.State <> adStateClosed Then
fcnConnectToDB = True
Exit Function
End If
End If
'Open connection the database
Set cn = New ADODB.Connection
With cn
.Errors.Clear
On Error Resume Next
.CursorLocation = adUseClient
.connectionString = myDBConnectionString
.Open myDBLocation
On Error GoTo 0
If .Errors.Count = 0 Then fcnConnectToDB = True
End With
End Function
'
'Returns False if there was a error
'Else returns array containing of returned records
Public Function ADO_RunQuery(mySQL As String) As Variant
'Check we're connected to the database
fcnConnectToDB
'Open the recordset.
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockPessimistic 'adLockBatchOptimistic
.Open mysql 'cmd
End With
'Dump the record set into an array
If rs.RecordCount = 0 Then
ADO_RunQuery = False
Else
ADO_RunQuery = rs.GetRows
End If
rs.Close
'tidy up
Set rs = Nothing
'(We leave the connection permanently open - you may like to
' close it afterwards)
End Function
Public Function ADO_UpdateDB(mySQL As String) As Boolean
'Check we're connected to the database
fcnConnectToDB
'Set the command text and execute
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = mySQL
.CommandType = adCmdText
On Error Resume Next
.Execute
If Err.Number = 0 Then ADO_UpdateDB = True
On Error GoTo 0
End With
'tidy up
Set cmd = Nothing
'(We leave the connection permanently open)
End Function
Paul Smith wrote:
> Please excuse the cross posting I was not sure which newsgroup would produ
ce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO'?
> PWS
>|||This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html
Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm
It demonstrates how to use SQL in Excel's VBA to:
* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.
DAO and ADO files available.
You can also download the demonstration file called "excelsql.zip".
The code is open and commented.
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
"Paul Smith" <pws@.twelve.me.uk> wrote in message
news:42ea3c7c$0$3509$ed2619ec@.ptn-nntp-reader03.plus.net...
> Please excuse the cross posting I was not sure which newsgroup would
produce
> the required advice.
> I have a requirement to update a SQL table with data from an Excel
> worksheet.
> Can anyone suggest the best way to do this...ADO'?
> PWS
>|||I found the chicken vindaloo recipe particularly useful too - made it,
ate it, loved it. Thanks
Andy Wiggins wrote:
> This might be a help for getting data to and from Excel and Access: It
> includes examples of using variables in SQL queries.
> http://www.bygsoftware.com/examples/sql.html
> Or you can get there from the "Excel with Access Databases" section on pag
e:
> http://www.bygsoftware.com/examples/examples.htm
> It demonstrates how to use SQL in Excel's VBA to:
> * create a database,
> * create a table
> * insert records
> * select records,
> * update records,
> * delete records,
> * delete a table,
> * delete a database.
> DAO and ADO files available.
> You can also download the demonstration file called "excelsql.zip".
> The code is open and commented.
>