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 Source Provider Error: The event log file is full

I'm trying to process one of my cubes and am getting this error, see subject
line. Mt configuration is this: Development Web server Dul09 holds the cube
s. The data lives on a SQL server DryHou10. I have had my sysadmin check the
event logs on both serv
ers, neither of which is full. This setup has been working just fine for the
last few years. Other cubes on Dul09, using different SQL servers for data,
process just fine.
All servers are running SQL 2000TJSully wrote:
> *I'm trying to process one of my cubes and am getting this error, see
> subject line. Mt configuration is this: Development Web server Dul09
> holds the cubes. The data lives on a SQL server DryHou10. I have had
> my sysadmin check the event logs on both serv
> ers, neither of which is full. This setup has been working just fine
> for the last few years. Other cubes on Dul09, using different SQL
> servers for data, process just fine.
> All servers are running SQL 2000 *
annieckl
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message441896.html|||Are you sure that they are not full...I got the same error once and emptied
all my logs (you can put them into files if
you want to keep them) - it worked again after that.
--Michael
"annieckl" <annieckl.1dtcei@.mail.mcse.ms> skrev i en meddelelse
news:annieckl.1dtcei@.mail.mcse.ms...
> TJSully wrote:
>
> --
> annieckl
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message441896.html
>

Data source from Oracle 10g problem

Dear Sir,

We met a problem when we move on Oracle 10g from Oracle 9i in the loading process. The scenario is that SQL Server as Data Warehouse, SSIS as ETL tool. But the length of string column as data source table in SSIS grows up 4 times when using Oracle 10g. For example, ABC table x varchar2(8) --> x varchar(32) -->x varchar(8) --> x varchar(8) {Oracle 10g SSIS(source) SSIS(target) SQL Server}

This doesn’t influence the loaded result (I did not find any problem until now), but that influence loading performance and we worry about potential problems.

Do you have any idea or have you met the same problem before?

Thanks,

Jacob

Change your source column in SSIS to be 8 bytes instead of 32.|||

Phil Brammer ,

Thanks for your quick response.

I can't change the length of the column from 8 to 32 by maner because the length of target column is 8. The origal length of the column in Oracle is 8. I guess I should setup some parameters in Oracle side like 'code page'

Thanks,

Jacob

|||No, inside SSIS, set the length of the column in the OLE DB Source to 8 instead of 32.|||

Phil Brammer ,

Thank you very much. This is migration problem. There are more than 100 tables and 1000 columns that ran in Oracle9i very well. But now we have the problem when we want to move on Oracle10g.

Thanks,

Jacob

|||I don't understand the problem then.

Are you asking an SSIS question or an Oracle question?

Please provide more details.

Data Source for Process Cube

Is the source for the data used to process the cube actually embedded in the cube itself? I.e. I have source data which could move around. How can I programatically modify the source connection string before processing the cube using SSIS?

Why do you want to change the datasource?
You can have several partitions in a cube and each partition with different datasource.
If you want to update data in a datasource you have to proccess only this partition.
That's all|||Because I need to create approximately 2300 unique cubes for different customers that are all sourced off 2300 different SQL databases residing on different servers throughout the data center.

All the source databases use the same schema. I am using the same ETL process to create 2300 warehouses. Now I need to create the unique cubes off those warehouses. Seperate "database"/cubes are required for compliance reasons.

Thanks,

Chris|||Then what you need is a different cube for each warehouse. You can have 1 cube with 1 partition. Because when you proccess full the partition you overwrite data.
If I where you I try to make a client dimension or something like this and then filter data with dimension.
All the data in a unique database/table, and the cube reads this table.
Are you thinking to give access with Excel? If you are using Excel use instead multiple partitions.|||You still don't get it. I need to figure out how to change the Data Source information that is embedded in the cube at the time I do the processing. i.e. I need to figure out how to run an XMLA query against the Analysis Services database that will allow me to change the connection string for the data source so that when I subsequently process the cube I am guaranteed it will find the source database.

The source database could move in the data center for load balancing reasons, etc.

Thanks,

Chris|||Hi Chris,

You can do this with an XMLA query (as you suggested) that looks like the one I posted below. This runs an alter command on the data source for the "Adventure Works DW" Analysis Services database. So, by changing the "Data Source" and "Initial Catalog" settings in the connection string, you update the data source in the deployed database.

Hope that is what you are looking for. BTW, I created this XMLA script by going into SQL Server Management Studio, connecting to Analysis Services, then drilling down within a deployed version of the "Adventure Works DW" database, selecting the data source, then right-clicking on it and selecting "Script Object As", then "ALTER to", then "New Query Editor Window". Great way to capture various XMLA scripts for automating changes to a deployed AS..

Dave Fackler

<Alter ObjectExpansion="ExpandFull" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<Object>

<DatabaseID>Adventure Works DW</DatabaseID>

<DataSourceID>Adventure Works DW</DataSourceID>

</Object>

<ObjectDefinition>

<DataSource xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="RelationalDataSource">

<ID>Adventure Works DW</ID>

<Name>Adventure Works DW</Name>

<ConnectionString>Provider=SQLNCLI.1;Data Source=newserver;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=newdatabase</ConnectionString>

<ImpersonationInfo>

<ImpersonationMode>ImpersonateServiceAccount</ImpersonationMode>

</ImpersonationInfo>

<Timeout>PT0S</Timeout>

</DataSource>

</ObjectDefinition>

</Alter>

|||You are brilliant. I knew about the scripting feature however I wasn't thinking about the data source being surfaced in the Management Console for analysis services. Thanks!

Data quality tools

Guys,

I'm trying to implement some of the data quality techniques. I use staging tables in my ETL process.

What I'd like to do is:

- check for required fields - make sure all required fields have values in all records

- check for duplicates

- check for data formats (line phone#, zip code etc)

Obviously all of this can be in SQL.

My question is what would be the best approach in SSIS to do it?

Dima.

Dima S wrote:

- check for required fields - make sure all required fields have values in all records

The Derived Column component has an ISNULL() function.

Dima S wrote:

- check for duplicates

Aggregate transform will give you all records that contains duplicates. COUNT over all the columns in which you want to check for duplicates.

Dima S wrote:

- check for data formats (line phone#, zip code etc)

Regular expressions would be a good fit here: http://blogs.conchango.com/jamiethomson/archive/2005/07/04/1754.aspx

-Jamie

|||

Thanks!

Data Pump Failing

I did not see a forum for the SQL Server 2000 DTS.

I have a flat file feeding a table via a data pump. The table is only used by this process. It will run for about 30minutes and then fail. The message in the history does not give any detail on why it is failing. Below is the message I get and if I rerun the job it works fine. Anyone help me please.

Date 07/23/2007 6:00:02 AM
Log Job History (Daily: Load EOL from MVS1 (First Run))

Step ID 1
Server PIT-CS-M608
Job Name Daily: Load EOL from MVS1 (First Run)
Step Name Daily: Load tblCaseMasterSched
Duration 00:28:05
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: PIT-CS-M608\SYSTEM. ...rt: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 4000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 4000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 5000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 5000 DTSRun OnProgress: DTSStep_DTSDataP... Process Exit Code 1. The step failed.

DTS questions can be asked in the DTS newsgroup:

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg

data migration - new server uses different collation

Latin1_General_CI_AS on new server
All database created on old server use SQL_Latin1_General_CP1_CI_AS
I understand its can be a complete process to change the default collation
on my new server to SQL_Latin1_General_CP1_CI_AS
Can someone please help me understand what i need to do ?
Thanks for your time
Scott
SQL 2005 standard.i can use this on new DBs to make sure they use correct COLLATION (i.e same
as all user dbs.)
COLLATE SQL_Latin1_General_CP1_CI_AS
BUT my system dbs now use this > Latin1_General_CI_AS on new server.
What problems can i expect in future if these COLLATIONS differ on user /
system dbs ?

Data Migration

Hi
We are in the process of migrating Data from SQL SERVER 2000 to
Oracle 9i.

Can anyone let me know what are the steps that needs to be followed
in achieving the same.

If there's any specific documentation also available , please let me know
Any pointers would be of great help.

Thanks
Shankar GokuleI think you'd get a more sympathetic ear over here:

http://www.dbforums.com/f4/

Besides...migration is migrattion...

Copy structure (in ERWin is nice), forward engineer (change the code to match the platform)...dump data, load to stage, audit data, load new structure...

Data Mart Maintenance

The database maintenance on our data mart SQL server is causing problems.The re-index process causes too much file growth and the processes are unable to complete due to disk space constraints.I want to write a process to re-index the database, but handle this a single file group at a time and then shrink the file group before going to the next one. I'm looking to write a maintenance routine (stored procedure) that will get all of the tables from a file group and re-index them. Any general ideas would be greatly appreciated.

I think I've found a reindexing solution to this problem I posted. I'm posting an answer here and if anyone wants to comment thats fine but I am doing it to help out other developers out there. If you scroll down farther you can see that I only reindexed clustered indexes in this Database and that is because when you reindex clustered indexes, tables with non-clustered indexes are automatically reindexed as well I believe.

-- cursor to loop through File Groups

DECLARE FG CURSOR FOR

SELECT DISTINCT groupid

FROM sysfilegroups

OPEN FG

FETCH NEXT FROM FG INTO @.fgroupid

WHILE @.@.Fetch_status=0

BEGIN

-- Cursor to loop through filenames

DECLARE FILENAME CURSOR FOR

SELECT DISTINCT f.name

FROM sysfiles f

INNER JOIN sysfilegroups fg ON f.groupid = @.fgroupid

OPEN FILENAME

FETCH NEXT FROM FILENAME INTO @.fname

WHILE @.@.Fetch_status=0

BEGIN

PRINT 'Current Filename = ' + @.fname

--Table Name Cursor

DECLARE TBL CURSOR FOR

--This query selects tables with clustered indexes only

SELECT DISTINCT 'DBCC DBREINDEX (' + i.TABLE_NAME + ')'

FROM INFORMATION_SCHEMA.TABLES i

INNER JOIN sysindexes si ON i.TABLE_NAME = object_name(si.id)

INNER JOIN sysfiles sf ON sf.groupid = si.groupid

WHERE objectProperty(object_id(i.TABLE_NAME), 'IsUserTable') = 1

AND objectProperty(object_id(i.TABLE_NAME), 'TableHasClustIndex')=1

AND sf.filename=@.fname

OPEN TBL

FETCH NEXT FROM TBL INTO @.tname

WHILE @.@.Fetch_status=0

BEGIN

PRINT @.SQLA

EXEC (@.SQLA)

FETCH NEXT FROM TBL INTO @.SQLA

END

CLOSE TBL

DEALLOCATE TBL

FETCH NEXT FROM FILENAME INTO @.fname

END

SELECT @.SQLB = 'DBCC SHRINKFILE ('+ @.fname+ ')'

EXEC (@.SQLB)

CLOSE FILENAME

DEALLOCATE FILENAME

FETCH NEXT FROM FG INTO @.fgroupid

Data Mart Maintenance

The database maintenance on our data mart SQL server is causing problems.The re-index process causes too much file growth and the processes are unable to complete due to disk space constraints.I want to write a process to re-index the database, but handle this a single file group at a time and then shrink the file group before going to the next one. I'm looking to write a maintenance routine (stored procedure) that will get all of the tables from a file group and re-index them. Any general ideas would be greatly appreciated.

I think I've found a reindexing solution to this problem I posted. I'm posting an answer here and if anyone wants to comment thats fine but I am doing it to help out other developers out there. If you scroll down farther you can see that I only reindexed clustered indexes in this Database and that is because when you reindex clustered indexes, tables with non-clustered indexes are automatically reindexed as well I believe.

-- cursor to loop through File Groups

DECLARE FG CURSOR FOR

SELECT DISTINCT groupid

FROM sysfilegroups

OPEN FG

FETCH NEXT FROM FG INTO @.fgroupid

WHILE @.@.Fetch_status=0

BEGIN

-- Cursor to loop through filenames

DECLARE FILENAME CURSOR FOR

SELECT DISTINCT f.name

FROM sysfiles f

INNER JOIN sysfilegroups fg ON f.groupid = @.fgroupid

OPEN FILENAME

FETCH NEXT FROM FILENAME INTO @.fname

WHILE @.@.Fetch_status=0

BEGIN

PRINT 'Current Filename = ' + @.fname

--Table Name Cursor

DECLARE TBL CURSOR FOR

--This query selects tables with clustered indexes only

SELECT DISTINCT 'DBCC DBREINDEX (' + i.TABLE_NAME + ')'

FROM INFORMATION_SCHEMA.TABLES i

INNER JOIN sysindexes si ON i.TABLE_NAME = object_name(si.id)

INNER JOIN sysfiles sf ON sf.groupid = si.groupid

WHERE objectProperty(object_id(i.TABLE_NAME), 'IsUserTable') = 1

AND objectProperty(object_id(i.TABLE_NAME), 'TableHasClustIndex')=1

AND sf.filename=@.fname

OPEN TBL

FETCH NEXT FROM TBL INTO @.tname

WHILE @.@.Fetch_status=0

BEGIN

PRINT @.SQLA

EXEC (@.SQLA)

FETCH NEXT FROM TBL INTO @.SQLA

END

CLOSE TBL

DEALLOCATE TBL

FETCH NEXT FROM FILENAME INTO @.fname

END

SELECT @.SQLB = 'DBCC SHRINKFILE ('+ @.fname+ ')'

EXEC (@.SQLB)

CLOSE FILENAME

DEALLOCATE FILENAME

FETCH NEXT FROM FG INTO @.fgroupid

Data Mart

What is Data Mart?
How does it help the data retrival process in terms of speed ?

Quote:

Originally Posted by yogeshbhandare

What is Data Mart?
How does it help the data retrival process in terms of speed ?

In some data warehouse implementations, a data mart is a miniature data warehouse; in others, it is just one segment of the data warehouse. Data marts are often used to provide information to functional segments of the organization.

for more information visit'
http://msdn2.microsoft.com/en-us/library/aa905978(SQL.80).aspx