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 selection not happening for a date because of time format

hello all,

i am making a query which select the data again a particuler date.

I insert values in the table for with current date(Today's date) and the records is inserted with the date format(2006-07-14 16:12:09),now when i run the query after 2 or 3 minutes to select the records inserted today, my query returns no results.

I think it is because of the the time (14:16 in this case) that after 2 minutes, the query looks for the records inserted at (2006-07-14 18:12 or 2006-07-14 19:12) and does not get the result.

Is there a method to not consider the time(14:16) when running the query but the query fetches the records including the records inserted at this time(14:16) no matter at what time I run the query today?

Please anyone help me!

Thanks in advance!

to get all of today's rows use this

SELECT *
FROM YourTable
WHERE YourDateColumn >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Thanks you SQL_Menace for writing to me!

your query did exactly that I needed.

Thanks indeed!

|||

SQL_Menace, I have another question that is closely related to the previous one.

I am searching for the records between two dates.

Here is my query(modified after your reply):

SELECT * FROM Mytable WHERE(DateEntered >=DATEADD(dd, DATEDIFF(dd, 0, '" + Convert.ToDateTime(srchdate) + "')+0, 0) AND DateEntered <= DATEADD(dd, DATEDIFF(dd, 0, '" + Convert.ToDateTime(todate) + "')+0, 0))

DateEntered is my date column, and Convert.ToDateTime(srchdate), Convert.ToDateTime(todate) are the values given by the user at runtime.

Problem here is the same. I insert 10 records today(2006-07-14 18:16:33) but when I provide yesterday(2006-07-13 19:16:33) as FROM DATE and today(2006-07-14 18:16:33) as TO DATE as parameter to search beteen these two dates, query returns nothing i.e. count is zero(0).

How can I achieve this?

Thank you!

Data schema for storing arbitrary order

Currently this is purely hypothetical, though I'm semi-considering something
like this for part of a current project. I'm wondering how to apply a
pseudo-order to a set of data.
To give an example, if you were storing Service Ranks, which were
referenced by the service personnel you stored in the database :-
(... indicates other ranks in-between)
Rear Admiral
...
Admiral
...
Captain
...
Ensign
You store the data as a 2 column table, RankID (unique system-generated eg
Identity(1,1)) and RankName (varchar(50) perhaps).
So you now want to produce a report, that lists the personal in order from
highest rank to lowest rank. As far as the software user is concerned, if a
rank was missing, they would just enter it, tell the system what rank it was
below/above, and it would be stored. But how best to store the list in the
database, given that a new item could be inserted anywhere in the list? You
can't sort alphabetically, because the ranks aren't alphabetically ordered.
You can't trust an auto-generated ID, because that would put most recent
additions at the bottom, earliest at the top.
My current thought is to add a new column to the items - RankPos (integer) -
with an arbitrary (x) between the items (so first rank would be 10, fourth
rank down would be 40, the rank below it would be 50 etc)... if a rank is
inserted between the two, I'd give the new rank 45, and then run an update
once in a while (or if the numerical gap was used up) that would re-number
this column so there was an gap (x) of 10 again. But, is there a better way?
As I said, just wondering at the moment, but not the first time. I seem to
be stuck in the record groove, coming back to the above way each time I
think about it.
Yours,
Ann-Mariethat is one way....to add a "SortOrder" or "SortSequence" column
the other way is to implement this as a linked list or dual linked list.
Where each individual record keeps track of who is above and who is below
them.
just food for thought.
Greg Jackson
PDX, Oregon|||"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:e7VFu98LFHA.2796@.tk2msftngp13.phx.gbl...
> that is one way....to add a "SortOrder" or "SortSequence" column
> the other way is to implement this as a linked list or dual linked list.
> Where each individual record keeps track of who is above and who is below
> them.
> just food for thought.
> Greg Jackson
> PDX, Oregon
>
From the software development background, yes, I would agree. But when it
comes to SQL, but wouldn't I then have a problem doing a simple query to get
the personnel returned 'ORDER BY {rank}'. Echoes of the adjacency tree
storage vs. nested-set tree storage problems and discussion come to mind.
That said, thanks for your reply,
Ann-Marie

Data migration overview help

Hello,

This is my first project with SSIS and I'm having some problems migrating our current data import to it. Generally: right now we're pulling hourly data (which has a timestamp on it) in from a customer's db(which can't be modified, and is big and growing quickly) into our db. To do this we have been using a field to keep track of the last data download time (in our db) and then using that then when querying the customer's db to reduce the number of rows returned.

Will a similar system work in SSIS? I'm having trouble pulling the last download date out of the "target" database and using it in the query on the customer's db. Any advice on what I should be looking into to do this and am I headed in the right direction or just not thinking about this in a SSIS way?

How powerful/common are variables in SSIS and should I be trying to use them here?

Thanks,
Jeff

Variables are the way to go with this. Use an ExecuteSQL task to grab the timestamp and store it in a variable. Then either use the variable as a parameter on your data source SELECT statements in the data flow, or build the SQL dynamically, using expression based variables. Generally, if your data source is not SQL Server, it seems like you are better off using the expression based variables.

Data flow task multiple destinations

Hi,

The further i get with doing my current SSIS package the more i am starting to wonder about best practices and performance.

My current package loops through CSV files in a specified location and extracts events from these files. Each file contains multiple events which are a mixture of different types. Depending on the event there are a different number of comma seperated values. In the package i firstly set each event to one column seperated by a comma delimeter. I then create an array for the event which is split by the delimeter. In a script i weed out all elements of the array that are common to all events and set the remaining events to another array. After some processing i come to my conditional split transformation which splits the processing of each event based on the EventID. This is where i'm having doubts on whether i have approched the package correctly. There are approximately 60 different events so each one of these has a seperate pipeline to process the remaining parameters in the array and output them to the destination table. The destination table is differnet for each ID. Is it viable to have this amount conditions and paths when creating the pacakge and is this likely to have any detrimental effect on performance. Is there possibly another way that i could approach this problem?

Many thanks, i hope that made sense.

Grant

Grant Swan wrote:

Hi,

The further i get with doing my current SSIS package the more i am starting to wonder about best practices and performance.

My current package loops through CSV files in a specified location and extracts events from these files. Each file contains multiple events which are a mixture of different types. Depending on the event there are a different number of comma seperated values. In the package i firstly set each event to one column seperated by a comma delimeter. I then create an array for the event which is split by the delimeter. In a script i weed out all elements of the array that are common to all events and set the remaining events to another array. After some processing i come to my conditional split transformation which splits the processing of each event based on the EventID. This is where i'm having doubts on whether i have approched the package correctly. There are approximately 60 different events so each one of these has a seperate pipeline to process the remaining parameters in the array and output them to the destination table. The destination table is differnet for each ID. Is it viable to have this amount conditions and paths when creating the pacakge and is this likely to have any detrimental effect on performance. Is there possibly another way that i could approach this problem?

Many thanks, i hope that made sense.

Grant

Grant,

I have to be honest, it sounds like a very good approach. Importing as a single column and then splitting within the pipeline is definately a good idea.

Granted, 60 data-paths is alot but that does not mean the SSIS pipeline cannot handle them so yes, I do think it is viable. In terms of performance, well yes, of course as the number of paths increases then SSIS has more work to do and this will be detrimental to the time taken to execute - but that's not the same as being detrimental to perrformance. You're not going to get away from an increase in execution time whatever you solution may be, regardless of whether you use SSIS or not. Whether the performance is acceptable or not is entirely down to your discretion and the determining factors are:

Amount of data|||Hi Jamie,

Many thanks for this. It has put things in perspective. I still believe this solution at its current state to be quicker than the existing stored procedure which consists of a hell of a lot of nested if statements. The goal is to speed up this process as we are constantly getting these CSV files on a daily basis (approx 2000 a day) and the current methods are showing limitations.

I'll hopefully get some throughput and performance stats once i have the package completed and would be happy to let you cast your eye over them.

On a lightly differnet note. I am aware that when i process the remaining parameters there may be times where the event row will cause errors. Instead of using a row redirect and setting up the same destination multiple times would i be correct in saying that the redirects should point to a Union all transformation which then can input them all to one error destination table? Thinking about this would it be possible to have multiple inputs into a script which formalises all the events so that they can be directed to and error table?

Thanks,

Grant|||

Grant Swan wrote:

Hi Jamie,

Many thanks for this. It has put things in perspective. I still believe this solution at its current state to be quicker than the existing stored procedure which consists of a hell of a lot of nested if statements. The goal is to speed up this process as we are constantly getting these CSV files on a daily basis (approx 2000 a day) and the current methods are showing limitations.

I'll hopefully get some throughput and performance stats once i have the package completed and would be happy to let you cast your eye over them.

If resources start to get squeezed on performance then perhaps your conditional split could push EventIDs 1-30 into a raw file and EventIDs 31-60 into a second raw file. You can then process each "group" in isolation in seperate data-flows - thereby splitting out the processing. Variations on this these (i.e. more groups) are of course an option :)

As always the way to find the optimum solution is test and measure test and measure test and measure!!!

Grant Swan wrote:

On a lightly differnet note. I am aware that when i process the remaining parameters there may be times where the event row will cause errors. Instead of using a row redirect and setting up the same destination multiple times would i be correct in saying that the redirects should point to a Union all transformation which then can input them all to one error destination table? Thinking about this would it be possible to have multiple inputs into a script which formalises all the events so that they can be directed to and error table?

Yeah I think they should point to a UNION ALL. Maybe you could push that data into a raw file (which is stupendously quick compared to a flat file or DB table) and then push the contents of the raw file to wherever you want it in a seperate data-flow. Again, it would be an interesting test to find out. I dread to think what your data-flow will look like with 120 data paths in it though

-Jamie

|||Jamie,

Yeah I

think they should point to a UNION ALL. Maybe you could push that data

into a raw file (which is stupendously quick compared to a flat file or

DB table) and then push the contents of the raw file to wherever you

want it in a seperate data-flow. Again, it would be an interesting test

to find out. I dread to think what your data-flow will look like with

120 data paths in it though

It looks bad enough with nearly 60 :)
Need to look at the layout and set it out a bit better i think.

Thanks again,

Grant|||Jamie,

I've been thinking and believe i have come up with possibly a better approach to this problem. I've found that error re-direction from a script component isn't just a simple matter of dragging the red path to a seperate output although if you could help on how to do (as a seperate question) this i'd be interested in finding out.

Anyway, i believe i can get rid of all script tasks following the conditioanl split and just having one before the split. In this i would then split up my comma delimited list into generic parameter variables. Each event in the conditional split would then use only specific parameters from the array. This will work as long as i use a fixed length array with empty strings where no parameters exist. The problem i think i will encounter with this method is that i still need to convert the parameter data from strings into the destination table data type. I assume for this i should be able to use the data conversion task in place of the script? will the data conversion task allow me to do complex conversions that might involve changing a hex value in the source data to a integer value for the destination.

Any help or opinions on this would be appreciated.

Thanks,

Grant|||

Complicated conversions like that are more readily achieved in a Derived Column component or a script component.

-Jamie

|||Thanks Jamie,

Thats what i thought. At least with the Derived columns and the data conversion transformations i can use the error redirect as soon as a problem arises. As i mentioned the script task seems to be sadly missing this functionality for what ever reason.

Grant