Data transformation services: structure

Showing posts with label structure. Show all posts

Thursday, March 29, 2012

data synchronization

Hello! anybody know some tool to synchronizate data between databases (two
databases has the same structure) . I can not use replicate becouse I have
not a permanet connection between servers. Now, somebody can send me a
backup peridocally using email. And I can restore in a second database in
local and work in local with principal and second database, then send back
the backup sinchronized.
Thank you in advance and sorry for my english.
Guillermo
You might be able to use tools like RedGate software's data compare -
however they too will require a network connection.
You can replicate using merge replication or transactional replication to a
local database and then hack the msmerge_contents or msmerge_tombstone
tables for merge replication, or use sp_browsereplcmds for transactional
replication to get a list of the changes and then apply these on either
side.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Guillermo Villanueva" <guillermovil@.nospam.com> wrote in message
news:OTu6RLM4GHA.5000@.TK2MSFTNGP02.phx.gbl...
> Hello! anybody know some tool to synchronizate data between databases (two
> databases has the same structure) . I can not use replicate becouse I have
> not a permanet connection between servers. Now, somebody can send me a
> backup peridocally using email. And I can restore in a second database in
> local and work in local with principal and second database, then send back
> the backup sinchronized.
> Thank you in advance and sorry for my english.
> Guillermo
>
|||"Hilary Cotter" wrote:

> You might be able to use tools like RedGate software's data compare -
> however they too will require a network connection.
> You can replicate using merge replication or transactional replication to a
> local database and then hack the msmerge_contents or msmerge_tombstone
> tables for merge replication, or use sp_browsereplcmds for transactional
> replication to get a list of the changes and then apply these on either
> side.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Guillermo Villanueva" <guillermovil@.nospam.com> wrote in message
> news:OTu6RLM4GHA.5000@.TK2MSFTNGP02.phx.gbl...
>
>

data synchronization

Data structure Internals Question

Hi all,
I have found that adding a column to an existing table (tested on a table
with >1M rows >200 bytes long) behaves as follows:
1. If the added column does not allow NULLs and has a DEFAULT value, the
operation actually changes the row structure.
I can see that from the time it takes to complete and the number of reads
and writes I trace with profiler.
SELECTing from the table shows the default value was correctly entered to
the new column.
So far, so good.
2. If the added column allows NULLs, the operation takes ~0 time and just a
few reads and writes, implying a metadata operation only.
SELECTing from the table shows that NULLs were entered instead of the
DEFAULT value.
This is a documented behavior, I know I can use the "WITH VALUES" option to
force the default values into the columns instead of NULLs which will result
in the same behavior as above (1).
This happens regardless of whether the column has a DEFAULT value and also
regardless of whether the column is fixed length or variable length.
What surprises me about it, is the fact that now I actually have a table
with different row structures.
The old rows (before the column was added) were not changed and the new rows
I INSERT or UPDATE will have a different structure reflecting the newly
added column.
From what I know about the data structures, fixed length columns take up
their size regardless of their actual value and variable length columns
require an additional 2 bytes for the column offset in the row. Both fixed
and variable length columns that allow NULLs require an additional NULL bit
in the NULL bitmap for each row as well.
If I SELECT from the table, I get the NULLs as expected from the old rows as
well but where the **** are they actually stored?
I suspected that since the NULL bitmap always uses 8 bit bytes, perhaps
there was a bit left to spare for the new column (although it doesn't seem
logical to have a NULL bit for a column that does not exist in the row
itslef) so I created a table with exactly 8 nullable columns and added a 9th
one to see if it would take the time to allocate another byte for the NULL
bitmap but it seems not to... it completed almost instantaneously (<100ms)
taking only about 20 reads and 3 writes to complete for a >2500 pages table
(I have cleared the cache before running the ALTER TABLE).
Any insights about what's happening under the hood here will be appreciated.
TIA,
AmiAmi,
It would help a lot if for each of the situations you don't
understand you provide the following:
1. A CREATE TABLE statement that creates the table with
the structure it has before the new column is added.
2. An ALTER TABLE statement that adds the new column.
3. What you see from the ALTER TABLE in terms of reads
and writes from Profiler.
4. What you expected from #3.
5. The statements to create any indexes or constraints that
exist on the table beyond what is in the CREATE TABLE
statement.
You seem to have a good understanding of the way rows are
constructed, and I'm not sure exactly what specific situation
it is that you don't understand. As far as whether there is
a bit in the null bitmap to represent NULLs in a non-existent
column, in some sense, this does exist, but a better way of
saying it is that there are at times unused bits in the NULL
bitmap which are just meaningless filler to pad the data
to byte boundaries; when a new nullable column is added
without VALUES in this case, the change can be represented
without any data changes. One of these spare null-bitmap
bits becomes the null bit for the new column through a
change in metadata and at most a flipping of the bit (I don't
know offhand if spare bits are always zeroed out - the
bits may have to be set with write operations also. The null
values don't take up space in the table beyond the null bit.
Some of this is a little different depending on whether the
column in question is fixed or variable length, or whether
the table has any variable-length columns or not. Whether
the table is a heap (no clustered index) or is its clustered
index (if there is one) may also be an issue, and when data
is actually added, the number of writes may change dramatically
depending on whether adding a new column with VALUES
changes the number of rows that fit on each 8K data page.
I hope you can see why it helps to know the particulars of
the examples that concern you. For more information, you
can look at the topics "Estimating the Size of a Table with a
Clustered Index" and "Estimating the Size of a Table Without
a Clustered Index" in Books Online.
Steve Kass
Drew University
Ami Levin wrote:

>Hi all,
>I have found that adding a column to an existing table (tested on a table
>with >1M rows >200 bytes long) behaves as follows:
>1. If the added column does not allow NULLs and has a DEFAULT value, the
>operation actually changes the row structure.
>I can see that from the time it takes to complete and the number of reads
>and writes I trace with profiler.
>SELECTing from the table shows the default value was correctly entered to
>the new column.
>So far, so good.
>2. If the added column allows NULLs, the operation takes ~0 time and just a
>few reads and writes, implying a metadata operation only.
>SELECTing from the table shows that NULLs were entered instead of the
>DEFAULT value.
>This is a documented behavior, I know I can use the "WITH VALUES" option to
>force the default values into the columns instead of NULLs which will resul
t
>in the same behavior as above (1).
>This happens regardless of whether the column has a DEFAULT value and also
>regardless of whether the column is fixed length or variable length.
>What surprises me about it, is the fact that now I actually have a table
>with different row structures.
>The old rows (before the column was added) were not changed and the new row
s
>I INSERT or UPDATE will have a different structure reflecting the newly
>added column.
>From what I know about the data structures, fixed length columns take up
>their size regardless of their actual value and variable length columns
>require an additional 2 bytes for the column offset in the row. Both fixed
>and variable length columns that allow NULLs require an additional NULL bit
>in the NULL bitmap for each row as well.
>If I SELECT from the table, I get the NULLs as expected from the old rows a
s
>well but where the **** are they actually stored?
>I suspected that since the NULL bitmap always uses 8 bit bytes, perhaps
>there was a bit left to spare for the new column (although it doesn't seem
>logical to have a NULL bit for a column that does not exist in the row
>itslef) so I created a table with exactly 8 nullable columns and added a 9t
h
>one to see if it would take the time to allocate another byte for the NULL
>bitmap but it seems not to... it completed almost instantaneously (<100ms)
>taking only about 20 reads and 3 writes to complete for a >2500 pages table
>(I have cleared the cache before running the ALTER TABLE).
>Any insights about what's happening under the hood here will be appreciated
.
>TIA,
>Ami
>
>|||Hi Steve,
Thanks for your prompt reply.
here is a similar script to the one i have used for the last test to try and
focus the question:
CREATE TABLE T1
(
PK_col INT NOT NULL IDENTITY PRIMARY KEY,
Col1 INT NULL,
col2 INT NULL,
col3 INT NULL,
col4 INT NULL,
col5 INT NULL,
col6 INT NULL,
col7 INT NULL,
col8 INT NULL
)
GO
SET NOCOUNT ON
DECLARE @.c INT
SET @.c = 0
WHILE @.c <= 250000
BEGIN
INSERT T1
SELECT @.c,@.c,@.c,@.c,@.c,@.c,@.c,@.c
SET @.c = @.c + 1
END
GO
SELECT dpages
FROM sysindexes
WHERE id = OBJECT_ID('T1')
-- 1420 pages
GO
DBCC DROPCLEANBUFFERS
GO
-- start profiler trace
ALTER TABLE T1 ADD col9 INT NULL
GO
-- 46ms duration, 27 Reads, 3 Writes
Since the ALTER TABLE took only 27 reads, it obviously couldn't have changed
all 1420 pages.
Since the original table had exactly 8 nullable columns, it should have
filled the default 1 byte allocated for the null bitmap.
My questions are:
Where are the null values stored for the existing 250,000 rows?
If I execute the following statements:
INSERT T1
SELECT 1,1,1,1,1,1,1,1,1
GO
Now I have a new row that is physically about 9X4 bytes long (ignoring row
overhead) and 2 bytes of null bitmap.
The other 250K rows are 8X4 bytes long with 1 byte null bitmap.
How does SQL Server know how to distinguish between the 2 different
structures?
The only way it can do that IMHO is to "assume" that if the physical row
strcuture is missing a column compared to the table metadata, that column
must be NULL.
When updating, does it compare the table metadata with the actual row
structure it encounters while reading the pages and change the row structure
"on the fly" if necessary?
Things will get much more complicated now if for example I will do:
1. INSERT a few rows with values for all 9 columns
2. DROP col3 (or any other column) - I know that drop column is also a
metadata only operation, the column will not be physically removed.
3. ADD col10
4. UPDATE some rows adding values to the new columns
4. INSERT more rows
5. ... etc. etc.
I've read quite a few books and articles on SQL Server internals but I can't
remember ever encountering any articles relating to this issue.
I can start digging into it by examining DBCC PAGE internal structures but I
was hoping someone here already knows how it works under the hood to save me
the time and effort :-)
TIA
Ami
"Steve Kass" <skass@.drew.edu> wrote in message
news:OkQfZDuAFHA.2640@.TK2MSFTNGP14.phx.gbl...
> Ami,
> It would help a lot if for each of the situations you don't
> understand you provide the following:
> 1. A CREATE TABLE statement that creates the table with
> the structure it has before the new column is added.
> 2. An ALTER TABLE statement that adds the new column.
> 3. What you see from the ALTER TABLE in terms of reads
> and writes from Profiler.
> 4. What you expected from #3.
> 5. The statements to create any indexes or constraints that
> exist on the table beyond what is in the CREATE TABLE
> statement.
> You seem to have a good understanding of the way rows are
> constructed, and I'm not sure exactly what specific situation
> it is that you don't understand. As far as whether there is
> a bit in the null bitmap to represent NULLs in a non-existent
> column, in some sense, this does exist, but a better way of
> saying it is that there are at times unused bits in the NULL
> bitmap which are just meaningless filler to pad the data
> to byte boundaries; when a new nullable column is added
> without VALUES in this case, the change can be represented
> without any data changes. One of these spare null-bitmap
> bits becomes the null bit for the new column through a
> change in metadata and at most a flipping of the bit (I don't
> know offhand if spare bits are always zeroed out - the
> bits may have to be set with write operations also. The null
> values don't take up space in the table beyond the null bit.
> Some of this is a little different depending on whether the
> column in question is fixed or variable length, or whether
> the table has any variable-length columns or not. Whether
> the table is a heap (no clustered index) or is its clustered
> index (if there is one) may also be an issue, and when data
> is actually added, the number of writes may change dramatically
> depending on whether adding a new column with VALUES
> changes the number of rows that fit on each 8K data page.
> I hope you can see why it helps to know the particulars of
> the examples that concern you. For more information, you
> can look at the topics "Estimating the Size of a Table with a
> Clustered Index" and "Estimating the Size of a Table Without
> a Clustered Index" in Books Online.
> Steve Kass
> Drew University
>
> Ami Levin wrote:
>
a
to
result
also
rows
fixed
bit
as
seem
9th
NULL
(<100ms)
table
appreciated.|||Hi Ami,
My guess is that NULLs for the new columns aren't stored anywhere, and that
the algorithm for reading data is something like this:
- read metadata
- read row
- if you find less columns than expected, assume NULL for missing columns
It's easy to figure out whether there are missing columns since the row
internally holds the actual number of fixed/variable length columns.
When the row is actually modified for any purpose, then all postponed
physical changes take place.
If you want, I can post this in the private MVP forum to get the developers
feedback, but I suspect it won't be much different than this.
Let me know...
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ami Levin" <XXX_NOSPAM___XXX__amlevin@.mercury.com> wrote in message
news:%23GjYwBvAFHA.3988@.TK2MSFTNGP11.phx.gbl...
> Hi Steve,
> Thanks for your prompt reply.
> here is a similar script to the one i have used for the last test to try
> and
> focus the question:
> CREATE TABLE T1
> (
> PK_col INT NOT NULL IDENTITY PRIMARY KEY,
> Col1 INT NULL,
> col2 INT NULL,
> col3 INT NULL,
> col4 INT NULL,
> col5 INT NULL,
> col6 INT NULL,
> col7 INT NULL,
> col8 INT NULL
> )
> GO
> SET NOCOUNT ON
> DECLARE @.c INT
> SET @.c = 0
> WHILE @.c <= 250000
> BEGIN
> INSERT T1
> SELECT @.c,@.c,@.c,@.c,@.c,@.c,@.c,@.c
> SET @.c = @.c + 1
> END
> GO
> SELECT dpages
> FROM sysindexes
> WHERE id = OBJECT_ID('T1')
> -- 1420 pages
> GO
> DBCC DROPCLEANBUFFERS
> GO
> -- start profiler trace
> ALTER TABLE T1 ADD col9 INT NULL
> GO
> -- 46ms duration, 27 Reads, 3 Writes
> Since the ALTER TABLE took only 27 reads, it obviously couldn't have
> changed
> all 1420 pages.
> Since the original table had exactly 8 nullable columns, it should have
> filled the default 1 byte allocated for the null bitmap.
> My questions are:
> Where are the null values stored for the existing 250,000 rows?
> If I execute the following statements:
> INSERT T1
> SELECT 1,1,1,1,1,1,1,1,1
> GO
> Now I have a new row that is physically about 9X4 bytes long (ignoring row
> overhead) and 2 bytes of null bitmap.
> The other 250K rows are 8X4 bytes long with 1 byte null bitmap.
> How does SQL Server know how to distinguish between the 2 different
> structures?
> The only way it can do that IMHO is to "assume" that if the physical row
> strcuture is missing a column compared to the table metadata, that column
> must be NULL.
> When updating, does it compare the table metadata with the actual row
> structure it encounters while reading the pages and change the row
> structure
> "on the fly" if necessary?
> Things will get much more complicated now if for example I will do:
> 1. INSERT a few rows with values for all 9 columns
> 2. DROP col3 (or any other column) - I know that drop column is also a
> metadata only operation, the column will not be physically removed.
> 3. ADD col10
> 4. UPDATE some rows adding values to the new columns
> 4. INSERT more rows
> 5. ... etc. etc.
> I've read quite a few books and articles on SQL Server internals but I
> can't
> remember ever encountering any articles relating to this issue.
> I can start digging into it by examining DBCC PAGE internal structures but
> I
> was hoping someone here already knows how it works under the hood to save
> me
> the time and effort :-)
> TIA
> Ami
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:OkQfZDuAFHA.2640@.TK2MSFTNGP14.phx.gbl...
> a
> to
> result
> also
> rows
> fixed
> bit
> as
> seem
> 9th
> NULL
> (<100ms)
> table
> appreciated.
>|||Hi Itzik :-)
How are you?
I have guessed the same (I wrote it in my reply to steve) but i would like
to know how it really works.
Our "guess" seems good enough for the simple script I have used but what
about the more complex situation if after adding col9 we will
1. INSERT a few rows with values for all 9 columns
2. DROP col3 - physical data will not be removed...
3. ADD col10
4. UPDATE some rows with values to the new column
5. DROP col5
5. Add col11
6. INSERT more rows
7. ... etc. etc.
can you see a way to do it without keeping extra metadata?
Let us know what the developers say.
I find this very interesting.
See you soon,
Ami
--
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:ecderKvAFHA.824@.TK2MSFTNGP11.phx.gbl...
> Hi Ami,
> My guess is that NULLs for the new columns aren't stored anywhere, and
that
> the algorithm for reading data is something like this:
> - read metadata
> - read row
> - if you find less columns than expected, assume NULL for missing columns
> It's easy to figure out whether there are missing columns since the row
> internally holds the actual number of fixed/variable length columns.
> When the row is actually modified for any purpose, then all postponed
> physical changes take place.
> If you want, I can post this in the private MVP forum to get the
developers
> feedback, but I suspect it won't be much different than this.
> Let me know...
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com|||Ami, here's what I got back:
"* Add nullable column -- This does not require any data update because the
way the row format is setup, the newly added nullable column assumes NULL
value by default. Similarly, when a
not-nullable column is altered to be nullable, no update is required
because the row format is capable of storing a nullbit for all columns.
* Drop column -- The dropped column(s) continue to consume space (even if
rows are updated or inserted(fixed-len cols)) until the clustered index is
rebuilt. This includes the LOB allocations, and they are only deallocated
on rebuild. For heaps, dbcc cleantable will reclaim space used by dropped
LOB and variable-length columns only.
In Shiloh, we did not have a case where we postponed a data-update due to a
schema-change until the time the row is touched. In Yukon, we do postpone
pushing/pulling data out-of-row/inrow when the setting is changed for the
new max LOB types.
For dropped columns we did not store any information in Shiloh except for a
flag in sysindexes indicating that LOB deallocation/cleanup is required.
Evidence of dropped columns could be deduced from the offsets allocated to
existing columns. As far as compacting the rows is concerned, this occurs
automatically as part of index rebuild. In Yukon, we now store a bit more
information about dropped columns.
Bottom-line, we don't really have a general concept of "postponed or lazy
schema changes", except for the couple of cases mentioned above which only
Sort-of fall into this category."
BG, SQL Server MVP
www.SolidQualityLearning.com
"Ami Levin" <XXX_NOSPAM___XXX__amlevin@.mercury.com> wrote in message
news:%23fB$chvAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> Hi Itzik :-)
> How are you?
> I have guessed the same (I wrote it in my reply to steve) but i would like
> to know how it really works.
> Our "guess" seems good enough for the simple script I have used but what
> about the more complex situation if after adding col9 we will
> 1. INSERT a few rows with values for all 9 columns
> 2. DROP col3 - physical data will not be removed...
> 3. ADD col10
> 4. UPDATE some rows with values to the new column
> 5. DROP col5
> 5. Add col11
> 6. INSERT more rows
> 7. ... etc. etc.
> can you see a way to do it without keeping extra metadata?
> Let us know what the developers say.
> I find this very interesting.
> See you soon,
> Ami
> --
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message
> news:ecderKvAFHA.824@.TK2MSFTNGP11.phx.gbl...
> that
> developers
>|||Thanks Itzik.
That does not explain the "how" but we'll have to settle for it i guess :-)
Thanks again,
Ami
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uN7eb6VBFHA.3596@.TK2MSFTNGP12.phx.gbl...
> Ami, here's what I got back:
> "* Add nullable column -- This does not require any data update because
> the
> way the row format is setup, the newly added nullable column assumes NULL
> value by default. Similarly, when a
> not-nullable column is altered to be nullable, no update is required
> because the row format is capable of storing a nullbit for all columns.
> * Drop column -- The dropped column(s) continue to consume space (even if
> rows are updated or inserted(fixed-len cols)) until the clustered index is
> rebuilt. This includes the LOB allocations, and they are only deallocated
> on rebuild. For heaps, dbcc cleantable will reclaim space used by dropped
> LOB and variable-length columns only.
> In Shiloh, we did not have a case where we postponed a data-update due to
> a
> schema-change until the time the row is touched. In Yukon, we do postpone
> pushing/pulling data out-of-row/inrow when the setting is changed for the
> new max LOB types.
> For dropped columns we did not store any information in Shiloh except for
> a
> flag in sysindexes indicating that LOB deallocation/cleanup is required.
> Evidence of dropped columns could be deduced from the offsets allocated to
> existing columns. As far as compacting the rows is concerned, this occurs
> automatically as part of index rebuild. In Yukon, we now store a bit more
> information about dropped columns.
> Bottom-line, we don't really have a general concept of "postponed or lazy
> schema changes", except for the couple of cases mentioned above which only
> Sort-of fall into this category."
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ami Levin" <XXX_NOSPAM___XXX__amlevin@.mercury.com> wrote in message
> news:%23fB$chvAFHA.2016@.TK2MSFTNGP15.phx.gbl...
>

Data Structure for Date Time Selection

Hi All,
We have a table which looks like the following:
ID (int)
Status (int)
Elgiblity (int)
EligibleTime (datetime)
Counter (int)
Flag (bit)
Typically our queries look like
SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
= 0 AND Counter < 2
Since we're doing a lot of selects based on time + status codes... are
there any database structures suited for this?
Also, the values in this table are updated quite often... how do ensure
the indexes are update to date?
We load and use about 200K rows of data per day. We keep about 45 days
worth of data in the database. We would lilke to return the data as fast
as possible... does anyone have suggestions on how to store the data?
Thanks.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.comLucas
Are you concerned about performance of the query? Do the users complain that
they have to wait for a request?
What are the inedexes defined on the table?
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D131BA935A9nntprogerscom@.127.0.0.1...
> Hi All,
> We have a table which looks like the following:
> ID (int)
> Status (int)
> Elgiblity (int)
> EligibleTime (datetime)
> Counter (int)
> Flag (bit)
> Typically our queries look like
> SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
> Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
> = 0 AND Counter < 2
> Since we're doing a lot of selects based on time + status codes... are
> there any database structures suited for this?
> Also, the values in this table are updated quite often... how do ensure
> the indexes are update to date?
> We load and use about 200K rows of data per day. We keep about 45 days
> worth of data in the database. We would lilke to return the data as fast
> as possible... does anyone have suggestions on how to store the data?
> Thanks.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Hi
to auto update statistics, use database option
AUTO_UPDATE_STATISTICS
>from bol
When set to ON, existing statistics are automatically updated when the
statistics become out-of-date because the data in the tables has changed.
When set to OFF, existing statistics are not automatically updated; instead,
statistics can be manually updated. For more information, see Statistical
Information.
By default, AUTO_UPDATE_STATISTICS is set to ON.
The status of this option can be determined by examining the
IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
To know when exactly statistics are updated
DBCC SHOW_STATISTICS
To updat statistics
UPDATE STATISTICS table | view
[
index
| ( statistics_name [ ,...n ] )
]
[ WITH
[
[ FULLSCAN ]
| SAMPLE number { PERCENT | ROWS } ]
| RESAMPLE
]
[ [ , ] [ ALL | COLUMNS | INDEX ]
[ [ , ] NORECOMPUTE ]
]
Regards
R.D
"Uri Dimant" wrote:

> Lucas
> Are you concerned about performance of the query? Do the users complain th
at
> they have to wait for a request?
> What are the inedexes defined on the table?
>
> "Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
> news:Xns96D131BA935A9nntprogerscom@.127.0.0.1...
>
>|||SORRY AT WRONG PLACE
"R.D" wrote:
> Hi
> to auto update statistics, use database option
> AUTO_UPDATE_STATISTICS
> When set to ON, existing statistics are automatically updated when the
> statistics become out-of-date because the data in the tables has changed.
> When set to OFF, existing statistics are not automatically updated; instea
d,
> statistics can be manually updated. For more information, see Statistical
> Information.
> By default, AUTO_UPDATE_STATISTICS is set to ON.
> The status of this option can be determined by examining the
> IsAutoUpdateStatistics property of the DATABASEPROPERTYEX function.
> To know when exactly statistics are updated
> DBCC SHOW_STATISTICS
> To updat statistics
> UPDATE STATISTICS table | view
> [
> index
> | ( statistics_name [ ,...n ] )
> ]
> [ WITH
> [
> [ FULLSCAN ]
> | SAMPLE number { PERCENT | ROWS } ]
> | RESAMPLE
> ]
> [ [ , ] [ ALL | COLUMNS | INDEX ]
> [ [ , ] NORECOMPUTE ]
> ]
> Regards
> R.D
>
> "Uri Dimant" wrote:
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:eUaMZwQuFHA.360@.TK2MSFTNGP12.phx.gbl:

> Lucas
> Are you concerned about performance of the query? Do the users
> complain that they have to wait for a request?
> What are the inedexes defined on the table?
Hi,
It's an application that runs against the table... the application
places phone calls on behalf of records in the database - we need to put
out 100s or 1000s of calls per minute(i.e. Emergency phone calls).
Right now there are a combination of factors:
1. Table Structure Could be Optimized
2. hardware could be better
As you see in my original post, the table is pretty much a flat file
(there are actually more columns - like 30+ but they're parameter
columns)... I'm just wondering if you guys have any pointers on how to
store datetime data in a way that is easily selectable.
Thanks!
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Lucas
can be bit specific
is it the problem of insertion or select statement to retrive it.
pl.Post script
Regards
R.D
"Lucas Tam" wrote:

> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:eUaMZwQuFHA.360@.TK2MSFTNGP12.phx.gbl:
>
> Hi,
> It's an application that runs against the table... the application
> places phone calls on behalf of records in the database - we need to put
> out 100s or 1000s of calls per minute(i.e. Emergency phone calls).
> Right now there are a combination of factors:
> 1. Table Structure Could be Optimized
> 2. hardware could be better
> As you see in my original post, the table is pretty much a flat file
> (there are actually more columns - like 30+ but they're parameter
> columns)... I'm just wondering if you guys have any pointers on how to
> store datetime data in a way that is easily selectable.
> Thanks!
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
>|||examnotes <RD@.discussions.microsoft.com> wrote in
news:B0959AEA-AB2D-48A9-922D-5E0528AB5CE3@.microsoft.com:

> Lucas
> can be bit specific
> is it the problem of insertion or select statement to retrive it.
> pl.Post script
> Regards
> R.D
Problem with select statement:
SELECT TOP 1 * FROM TABLE WHERE (Status IN 1000, 2000, 3000) AND
Eligibilty = 2000 AND EligiblityTime < '09/12/2005 1:31:33 PM' AND Flag
= 0 AND Counter < 2
Something like that can take a while... and use a lot of resources.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com

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 Structure -- Its been a while

Ok, I haven't been doing too much database work lately and my brain has gone
soft.

I need some help with proper structure.

My database is being used to track television shows.

Any given show will be associated with at least one production company (call
it ProdCo) but possibly up to 3 production companies (never more than that).

I have:

tblNetwork
NetID (numeric, Identity)
NetName (char, 50)

tblShowData
BookingNum (numeric, Identity)
BookingNumExt (numeric, Identity)

Now, if I was just dealing with one ProdCo I would add it tblShowData as a
foreign key from tblNetwork; no problem.

How do I structure it so that I can combine 1 to 3 ProdCo's as one reference
in tblShowData.

I tried:

tblNetworkCombo
ID (numeric, Identity)
Net1
Net2
Net3

The problem is, obviously I can only join the FK from tblNetworks to one of
the NetX fields in tblNetworkCombo.

I don't quite know where to go from here. Any help would be appreciated."Jake Jessup" <watcherdude@.hotmail.com> wrote in message news:xyplc.46660$Qy.28399@.fed1read04...
> Ok, I haven't been doing too much database work lately and my brain has gone
> soft.
> I need some help with proper structure.
> My database is being used to track television shows.
> Any given show will be associated with at least one production company (call
> it ProdCo) but possibly up to 3 production companies (never more than that).
> I have:
> tblNetwork
> NetID (numeric, Identity)
> NetName (char, 50)
> tblShowData
> BookingNum (numeric, Identity)
> BookingNumExt (numeric, Identity)
> Now, if I was just dealing with one ProdCo I would add it tblShowData as a
> foreign key from tblNetwork; no problem.
> How do I structure it so that I can combine 1 to 3 ProdCo's as one reference
> in tblShowData.
> I tried:
> tblNetworkCombo
> ID (numeric, Identity)
> Net1
> Net2
> Net3
> The problem is, obviously I can only join the FK from tblNetworks to one of
> the NetX fields in tblNetworkCombo.
> I don't quite know where to go from here. Any help would be appreciated.

Create an associative entity that resolves the M:M relationship between shows and nets.
CREATE TABLE tblShowNetwork(
BookingNum numeric not null
REFERENCES tblShowData (BookingNum)
ON UPDATE RESTRICT
ON DELETE CASCADE,
NetID numeric not null,
REFERENCES tblNetwork (NetID)
ON UPDATE RESTRICT
ON DELETE CASCADE,
primary key( BookingNum, NetID )
;

Paul Horan
VCI Springfield, MA

We're in the TV business as well - we offer an industry-leading Sales, Traffic, and Billing system for TV/Cable
networks.|||I think what you really want is three tables.

Two of the tables should be entity tables, and the last one should be
a relationship table:

(I use the convention "dbe_" for database entity and "dbr_" for
database relation).

For the sake of simplicity, I have not checked the code to ensure it
works:

------------------
CREATE TABLE dbe_productionCompanies(
pcId int,
pcName varchar(64)
)

CREATE TABLE dbe_shows(
showId int,
showName varchar(64)
)

CREATE TABLE dbr_showProductionCompanies(
showId int,
pcId int,
CONSTRAINT FK_showProductionCompanies_REF_productionCompanies
FOREIGN KEY (pcId)
REFERENCES dbe_productionCompanies(pcId)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT FK_showProductionCompanies_REF_shows
FOREIGN KEY (showId)
REFERENCES dbe_shows(showId)
ON UPDATE NO ACTION
ON DELETE CASCADE,
CONSTRAINT PK_showProductionCompanies
PRIMARY KEY (showId, pcId)
)
------------------

To select the names of the production companies for a given show, you
would use the following query:

SELECT pcs.pcName
FROM dbr_showProductionCompanies AS spc
JOIN dbe_productionCompanies AS pcs ON spc.pcId = pcs.pcId
WHERE spc.showId = <<<ANY_ID>>
This should return a resultset with up to 3 matches and no more. Each
of the matches should be unique since there is a primary key
constraint on the two fields so that no duplicate fields are entered.

What you are really specifying is a one-to-many many relationship
between show and production companies. Being that it is one to many,
the most efficient way to manage the relationship (unless the join is
large) is to break it up into the entities and a relationship table
(at least this is what I have been taught).

Play around with some of your sample data to see how well those
foreign key dependencies work for you; you may not want the cascading
behavior.

Good luck.|||I got it figured out. You helped out a lot. Thanks!

"Charles Chen" <c.chen@.charliedigital.com> wrote in message
news:56f97b20.0405030930.41f521c0@.posting.google.c om...
> I think what you really want is three tables.
> Two of the tables should be entity tables, and the last one should be
> a relationship table:
> (I use the convention "dbe_" for database entity and "dbr_" for
> database relation).
> For the sake of simplicity, I have not checked the code to ensure it
> works:
> ------------------
> CREATE TABLE dbe_productionCompanies(
> pcId int,
> pcName varchar(64)
> )
> CREATE TABLE dbe_shows(
> showId int,
> showName varchar(64)
> )
> CREATE TABLE dbr_showProductionCompanies(
> showId int,
> pcId int,
> CONSTRAINT FK_showProductionCompanies_REF_productionCompanies
> FOREIGN KEY (pcId)
> REFERENCES dbe_productionCompanies(pcId)
> ON UPDATE NO ACTION
> ON DELETE CASCADE,
> CONSTRAINT FK_showProductionCompanies_REF_shows
> FOREIGN KEY (showId)
> REFERENCES dbe_shows(showId)
> ON UPDATE NO ACTION
> ON DELETE CASCADE,
> CONSTRAINT PK_showProductionCompanies
> PRIMARY KEY (showId, pcId)
> )
> ------------------
> To select the names of the production companies for a given show, you
> would use the following query:
> SELECT pcs.pcName
> FROM dbr_showProductionCompanies AS spc
> JOIN dbe_productionCompanies AS pcs ON spc.pcId = pcs.pcId
> WHERE spc.showId = <<<ANY_ID>>>
> This should return a resultset with up to 3 matches and no more. Each
> of the matches should be unique since there is a primary key
> constraint on the two fields so that no duplicate fields are entered.
> What you are really specifying is a one-to-many many relationship
> between show and production companies. Being that it is one to many,
> the most efficient way to manage the relationship (unless the join is
> large) is to break it up into the entities and a relationship table
> (at least this is what I have been taught).
> Play around with some of your sample data to see how well those
> foreign key dependencies work for you; you may not want the cascading
> behavior.
> Good luck.

Tuesday, March 27, 2012

Data Source View of Mining Structure cant be edited?

Hi, all here,

Thank you very much for your kind attention.

I got a quite a strange problem with Mining structure for OLAP data source though. The problem is: I am not able to edit the mining structure in the mining structure editor. The whole data source view within the mining structure editor is greyed. Could please anyone here give me any advices for that?

Thank you very much in advance for any help for that.

With best regards,

Yours sincerely,

You can't edit the data source view in the data mniing designer. The DM designer has a "read-only" view of the DSV. You need to switch to the DSV designer to make edits.|||

Hi, Jamie, thank you very much for your guidance.

Sorry for the bad description of the problem. What I originally meant is that: the mining structure cant be edited within the mining structure editor when its data source is OALP. Why is that?

Is it the limitation for OALP data source?

Thank you very much for your further advices.

With best regards,

Yours sincerely,

|||This is a limitation of the OLAP data source. This is not intended for user modification.|||

Hi, Jamie, thank you very much.

Regards,

Monday, March 19, 2012

data page structure

Hi all,
I found documentation once on the structure of a data
page. However, I am unable to find this documentation
now. Just to make sure I am referring to data types like
nvarchar, varchar, nchar, char and interger.There is a brief discussion in the BOL, but I know there
is a pretty good discussion on this in Kalen Delaney's
Inside SQL Server book.
Matthew Bando
BandoM@.CSCTechnologies.com
>--Original Message--
>Hi all,
>I found documentation once on the structure of a data
>page. However, I am unable to find this documentation
>now. Just to make sure I am referring to data types
like
>nvarchar, varchar, nchar, char and interger.
>.
>

Thursday, March 8, 2012

Data Mining Wizard (Not Responding)

I am trying to create a new mining structure, but when launching the wizard the "Data Mining Wizard" stops responding where the dropdownlist with the algorithms should appear.

The bizarre thing is that I can access the cube and the datawarehouse just fine using the same setup, it's just the mining wizard that doesn't work.

I am using SQL 2005 standard & Visual Studio 2005 Team Suite (and yes I have reinstalled SQL 2005) - no change.

Are there any logs that might shed some light on this strange behavior, or is this a known issue?

The main difference with the DM wizard is that, unlike other wizards, it actually connects to the server to determine the list of available algorithms. However, it is supposed to provide a default list of algorithms if it cannot connect.

You might try looking at the project settings to make sure that the deployment server is set up correctly.

Have you succesfully deployed any AS projects to an AS server?

|||

Hi Chris

The behavior of the algorithm selection wizard page is to retrieve the list of algorithm from the deployment server. If the query times out, it reverts to a list of cached algorithms. The value of the timeout depends on whether:

1. The server is a nonexistent machine (in this case, the timeout is equal to the time it takes to determine from DNS that the machine name is nonexistent and is usually fast)

2. The server is offline. In this case, the timeout is longer (usually 30 seconds, but can be configured under network settings).

3. The server is online, but Analysis Service is not started. This timeout is the TCP timeout, usually 30 seconds.

The first step is to determine your deployment server. For that, right click on the project select properties and look under Configuration Properties -> Deployment. The server name is available in the property grid. Next, please ensure that the server is reachable and is running Analysis Services. If it's an offline server, that you should get a list of default algorithms after 30 seconds or the timeout configured for your machine

Let me know if you have any further questions

Thanks

Shuvro

|||

I have the exact same issue, I have deleted and removed every thing and reinstalled SQl 2005 standard and Visual Studio 2005 and every time I get an unresposive screen when trying to pick a mining model.

I had created a mining model before and worked fine but know just stops responding.

|||

If possible, please work with tech support - we haven't seen these issues outside of what we've already commented on, and it's difficult to debug through the forum.

Thanks

Data Mining Wizard (Not Responding)

I am trying to create a new mining structure, but when launching the wizard the "Data Mining Wizard" stops responding where the dropdownlist with the algorithms should appear.

The bizarre thing is that I can access the cube and the datawarehouse just fine using the same setup, it's just the mining wizard that doesn't work.

I am using SQL 2005 standard & Visual Studio 2005 Team Suite (and yes I have reinstalled SQL 2005) - no change.

Are there any logs that might shed some light on this strange behavior, or is this a known issue?

You might try looking at the project settings to make sure that the deployment server is set up correctly.

Have you succesfully deployed any AS projects to an AS server?

|||

Hi Chris

1. The server is a nonexistent machine (in this case, the timeout is equal to the time it takes to determine from DNS that the machine name is nonexistent and is usually fast)

2. The server is offline. In this case, the timeout is longer (usually 30 seconds, but can be configured under network settings).

3. The server is online, but Analysis Service is not started. This timeout is the TCP timeout, usually 30 seconds.

Let me know if you have any further questions

Thanks

Shuvro

|||

I had created a mining model before and worked fine but know just stops responding.

|||

If possible, please work with tech support - we haven't seen these issues outside of what we've already commented on, and it's difficult to debug through the forum.

Thanks

Data Mining slicing / filtering

Is there a way to dynamically slice / filter data when running a data mining query?

(Dynamically means - not deployed as part of the structure)

For example - I’d like find associations to products sold in different geographic locations.

Does the answer differ if the structure is based on a cube or on a rational DB?

The answer is no - the counts of product associations are the counts for the products with which the model was changed. You would have to create specific structure for each subset of the data you would want to model.

You may be able to use many-many dimensions in OLAP and then provide the appropriate slice to see association counts by region, but you won't have a mining model that can perform predictions, and you will only see pairwise counts.

Data Mining slicing / filtering

Is there a way to dynamically slice / filter data when running a data mining query?

(Dynamically means - not deployed as part of the structure)

For example - I’d like find associations to products sold in different geographic locations.

Does the answer differ if the structure is based on a cube or on a rational DB?

Wednesday, March 7, 2012

Data mining cube with 0-value facts

I'm defining a mining structure against an OLAP dimension. The continuous value that I'm using both as input and for forecasting represents the time to complete a certain process.

There's something that strikes me as if it could be a problem, but I'm not sure. Our fact table has multiple columns (with multiple correponding measures in the cube). The "time-to-complete" measure is only populated on some of the fact rows - the rows that represent completion information. Other rows represent other information, and the "time-to-complete" value is set to 0. This works fine for cumulative time-to-complete and average time-to-complete, but it seems like it could mess up data mining. Will those 0-value facts skew the mining results? I'm not seeing a way to filter out those entries and only include the non-zero facts in the mining processing.

Or perhaps I'm totally misunderstanding something, which is quite possible. :)

The zeroes will mess you up in DM and OLAP. An average of a value ignores nulls, but not zeroes.

You can set your measure to retain null values in the fact table - check out the data binding settings.

Hope this helps,

Richard

|||The zeros are properly handled in our average calculated measures in OLAP (we don't use the EE AverageOfChildren aggregation function).

Sounds like it will screw up DM stuff, though. If the values were nulls instead of 0, would DM handle it correctly?|||

Sine you've modeled the variable as continous, a 0 value will be treated as a possible value whereas a null would be treated as missing by DM. They would hence generate different DM model.

If the only zero value in your data correspond to null (missing) value, you can either filter those out from the input data or have another computed measure be null whenever the measure is 0 and use that measure as input to the data mining algorithm.

Hope this helps

|||>you can either filter those out from the input data

How would I do that? There isn't a dimension slice that represents this condition, and that's the only filtering that I've been able to find so far.

A computed measure wouldn't be ideal, but it might work. I'll think about that.
|||Sorry, my earlier statement was not accurate. You cannot filter by measure values in OLAP Mining Model.|||OK, after doing a little more testing, I'm more confused.

As a test, I created a new calculation that takes the original measure and converts 0-valued facts to Nulls. I added that new calculation to the mining structure and models and processed them. Then I ran a singleton prediction query for both the original and new measures. They came up with the exact same results. So, as far as I can tell, either:

a) The 0-value facts don't affect the mining results
b) The null facts are treated the same as the 0 facts (and not ignored)
c) Something about how I'm doing my test is flawed.

Anyone have any suggestions or thoughts here?|||It's possible that the column doesn't have any impact on the result at all? What algorithm are you using and does the column show up as "important" in the viewer?|||Actually, I'm working with a degeneratively simple test case here, where the mining structure only includes one input - a single continuous value, used both as input and for prediction. Perhaps that's too trivial for a reasonable test.

I'm using the Neural Network algorithm, but I can't bring up the viewer - I just get an error (which I reported in another thread in these forums).|||

Aha!

That's the reason for this problem and may be the reason for the viewer problem as well. It seems you found a bug in the system, the bug being that the software didn't return an error when you tried to create this model .

When you mark a column as "input" what you are stating is "use this column as an input to predict other columns." When you mark it as output you are stating "determine values of this column based on all other inputs." We have the concept of "input AND output" because we allow you to predict multiple attributes in a single model.

Therefore, what is happening is that for that column, you have no inputs, and it is an input for no other columns, since there are no more columns for which it could be an input. Simply put, it's an invalid model definition which we should have rejected from the start.

Thanks

-Jamie

|||Doh! Did I mention I was a noob with data mining? :)

I fixed the model, and the results seem to make more sense now. However, it didn't fix the problem with the mining model viewer.|||It's hard to say - there may not be enough differentiation for the viewer to show patterns at that point. I would try things out with a more well known dataset - e.g. the tutorial so you can get a feel for how the tools work in a tested environment and then see if the issue seems to be the software not working correctly or your data not being rich enough to demonstrate descriptive results.

Saturday, February 25, 2012

Data Migration

Hi friends,
In my project I have redesigned my database structure. In the existing
structure there is no Primary key and no relationship b/w data.
In the new structure Primary key and the relationship is added.
Now I want to migrate the existing data into the new structure.
There is a chance for duplicate records and also records that does not
satisfy referential integrity.
How to migrate the data? I want to have a copy of the duplicate records and
also the records which does not satisfy referential integrity.
Its a huge database, so i can't query table by table to find the mismatch
records.
How to proceed?
thanks
vanithaHi,
Let me know can you consider the following:
I assume that you have two database one is normalized and other one is not
normialized.
1. Backup the Both Databases.
2. Add err_text column to all tables in non-normolized DB.
3. Write a store procedure with the following logic:
a. Select a record and try inserting to Normolized DBs table ,
if no error occurs delete the same from Non-Normolized table,
otherwise
updated err_text column of respective table in non-normolized db
with the
error
b. perform step (a) for each record in every table in non-normlized DB.
4. After the step3 what ever record exists in table of non-normolized db
either they
are passed Primary or referential integrity.|||Vanitha
There are many approaches to this. some are very trickey too.
1)One thing that you can easily do is to update new tables and compare with
old one find unmatched rows using pimary key.( u can use dynamic query to
supply table names while comparing)
2) You can use sp_MSforeachtble: You have to write something dynamically to
abtain all duplicate rows
3) write a procedure (using cursor perhaps) to take columns from
information.schema.columns where for each table and supply those columns to
find out duplicat rows using group by and count(*) >1 as you usually do.
4) Use a sproc to insert row by row to another table and insert into
errortable whereever there is an error using @.@.error <>. This approach gives
coplete rows for which inserts failed.
but 2 and 3 only gives duplicate rows and does not check other constriants
Hope This will give you a lead. though does not solve
Regards
R.D
"Vanitha" wrote:

> Hi friends,
> In my project I have redesigned my database structure. In the existing
> structure there is no Primary key and no relationship b/w data.
> In the new structure Primary key and the relationship is added.
> Now I want to migrate the existing data into the new structure.
> There is a chance for duplicate records and also records that does not
> satisfy referential integrity.
> How to migrate the data? I want to have a copy of the duplicate records an
d
> also the records which does not satisfy referential integrity.
> Its a huge database, so i can't query table by table to find the mismatch
> records.
> How to proceed?
> thanks
> vanitha
>|||I can't add a column to the existing table.
Can I add a new table into the existing database called "errorTable" and the
n
insert the table_name, column data and the error description.
pls advice on this.
thanks
"SlowLearner" wrote:

> Hi,
> Let me know can you consider the following:
> I assume that you have two database one is normalized and other one is not
> normialized.
> 1. Backup the Both Databases.
> 2. Add err_text column to all tables in non-normolized DB.
> 3. Write a store procedure with the following logic:
> a. Select a record and try inserting to Normolized DBs table ,
> if no error occurs delete the same from Non-Normolized table,
> otherwise
> updated err_text column of respective table in non-normolized db
> with the
> error
> b. perform step (a) for each record in every table in non-normlized DB
.
> 4. After the step3 what ever record exists in table of non-normolized db
> either they
> are passed Primary or referential integrity.
>|||there are millions of records, so I can't select each and every record and
check the errors.
I want to do this in bulk.
pls help me to solve this.
thanks
vanitha
"SlowLearner" wrote:

> Hi,
> Let me know can you consider the following:
> I assume that you have two database one is normalized and other one is not
> normialized.
> 1. Backup the Both Databases.
> 2. Add err_text column to all tables in non-normolized DB.
> 3. Write a store procedure with the following logic:
> a. Select a record and try inserting to Normolized DBs table ,
> if no error occurs delete the same from Non-Normolized table,
> otherwise
> updated err_text column of respective table in non-normolized db
> with the
> error
> b. perform step (a) for each record in every table in non-normlized DB
.
> 4. After the step3 what ever record exists in table of non-normolized db
> either they
> are passed Primary or referential integrity.
>|||why dont you create a new table or temp table with the same syntax and add
all uninserted columns data to new error table
Regards
R.D
"R.D" wrote:
> Vanitha
> There are many approaches to this. some are very trickey too.
> 1)One thing that you can easily do is to update new tables and compare wi
th
> old one find unmatched rows using pimary key.( u can use dynamic query to
> supply table names while comparing)
> 2) You can use sp_MSforeachtble: You have to write something dynamically t
o
> abtain all duplicate rows
> 3) write a procedure (using cursor perhaps) to take columns from
> information.schema.columns where for each table and supply those columns t
o
> find out duplicat rows using group by and count(*) >1 as you usually do.
> 4) Use a sproc to insert row by row to another table and insert into
> errortable whereever there is an error using @.@.error <>. This approach giv
es
> coplete rows for which inserts failed.
> but 2 and 3 only gives duplicate rows and does not check other constriants
> Hope This will give you a lead. though does not solve
> Regards
> R.D
>
> "Vanitha" wrote:
>|||There are n number of records... so i can't chk record by record.
is there any way to do in bulk. and if any error during the operation, the
migration shd not be cancelled, it shd log in another table or something lik
e
that.
thanks
vanitha
"R.D" wrote:
> Vanitha
> There are many approaches to this. some are very trickey too.
> 1)One thing that you can easily do is to update new tables and compare wi
th
> old one find unmatched rows using pimary key.( u can use dynamic query to
> supply table names while comparing)
> 2) You can use sp_MSforeachtble: You have to write something dynamically t
o
> abtain all duplicate rows
> 3) write a procedure (using cursor perhaps) to take columns from
> information.schema.columns where for each table and supply those columns t
o
> find out duplicat rows using group by and count(*) >1 as you usually do.
> 4) Use a sproc to insert row by row to another table and insert into
> errortable whereever there is an error using @.@.error <>. This approach giv
es
> coplete rows for which inserts failed.
> but 2 and 3 only gives duplicate rows and does not check other constriants
> Hope This will give you a lead. though does not solve
> Regards
> R.D
>
> "Vanitha" wrote:
>|||TRY THIS
CAN YOU JUST INSERT INTO NEW TABLE AND COMPARE WITH OLD TABLE SO THAT YOU
KNOW WHAT RECORDS ARE NOT INSERTED
this is like this
1) add indentity column in the old table and new table
2) insert into new table2( normalised)
3) insert rows in an order
4) find out which rows are not available in new table using somethinglike
SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE.IDENTITY =
TABLE2.IDENTITY WHERE TABLE2.IDENTITY IS NULL
I think this is simple and possible
Regards
R.D
REGARDS
R.D
"Vanitha" wrote:
> There are n number of records... so i can't chk record by record.
> is there any way to do in bulk. and if any error during the operation, the
> migration shd not be cancelled, it shd log in another table or something l
ike
> that.
> thanks
> vanitha
> "R.D" wrote:
>|||OOPS
IN THE SECOND TABLE IDENTIY COLUMN SHOULD ALSO BE INSERTED EXLICITLY OR MAKE
IT A NUMERIC.
sorry there is caps lock pressed on my key board
R.D
"Vanitha" wrote:
> There are n number of records... so i can't chk record by record.
> is there any way to do in bulk. and if any error during the operation, the
> migration shd not be cancelled, it shd log in another table or something l
ike
> that.
> thanks
> vanitha
> "R.D" wrote:
>|||thanks a lot
if any error exits during migration, then it stops the migration process.
how to continue the migration even if error occurs.
thanks
vanitha
"R.D" wrote:
> OOPS
> IN THE SECOND TABLE IDENTIY COLUMN SHOULD ALSO BE INSERTED EXLICITLY OR MA
KE
> IT A NUMERIC.
> sorry there is caps lock pressed on my key board
> R.D
> "Vanitha" wrote:
>

Data Migration

Hi friends,
I have changed the existing database scripts by adding primary key and
foreign key constraints.
Now i have created the new structure, I want to mograte the existing data
into the new structure. There is a chance for duplicate records and also
records that does not satisfy referential integrity.
How to create a populate data from the existing DB and how to avoid the
errors?
thanks a lot
vanithaYou have to eliminate the duplicate records, do you want to delete and
discard them, or do you want to keep them ? You have to provide further
informatione / DDL to help you.
Hth, jens Suessmeyer.
http://www.sqlserver2005.de
--|||hi vanitha,
--You can use this code to scan duplicate keys
use northwind
select orderid from [order details]
group by orderid
having count(orderid)>1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Vanitha" wrote:

> Hi friends,
> I have changed the existing database scripts by adding primary key and
> foreign key constraints.
> Now i have created the new structure, I want to mograte the existing data
> into the new structure. There is a chance for duplicate records and also
> records that does not satisfy referential integrity.
> How to create a populate data from the existing DB and how to avoid the
> errors?
> thanks a lot
> vanitha|||How to generate the populate script?
thanks
vanitha
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> hi vanitha,
> --You can use this code to scan duplicate keys
> use northwind
> select orderid from [order details]
> group by orderid
> having count(orderid)>1
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Vanitha" wrote:
>|||Hi
You may want to check the data first to see if this does apply:
e.g. for duplicates
SELECT A.col1, A.col2, A.col3
FROM MyTableA
GROUP BY A.col1, A.col2, A.col3
HAVING COUNT(*) > 1
For a foreign key try something like:
SELECT A.col1, A.col2, A.col3
FROM MyTableA A
WHERE NOT EXISTS ( SELECT * FROM FKTable F WHERE A.col2 = F.col1 )
If you want to eliminate the duplicates when inserting the data use a
DISTINCT clause
INSERT INTO NewTable ( col1, col2, col3 )
SELECT DISTINCT A.col1, A.col2, A.col3
FROM MyTableA
To eliminate those that do not have FKs
INSERT INTO NewTable ( col1, col2, col3 )
SELECT A.col1, A.col2, A.col3
FROM MyTableA A
WHERE EXISTS ( SELECT * FROM FKTable F WHERE A.col2 = F.col1 )
John
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:DA29DC9C-1109-491C-9970-E69EBAAB16AC@.microsoft.com...
> Hi friends,
> I have changed the existing database scripts by adding primary key and
> foreign key constraints.
> Now i have created the new structure, I want to mograte the existing data
> into the new structure. There is a chance for duplicate records and also
> records that does not satisfy referential integrity.
> How to create a populate data from the existing DB and how to avoid the
> errors?
> thanks a lot
> vanitha|||insert into destination(orderid,x,y,z) --> this are the insert hint
select orderid,x,y,z from orderdetails -->inserted must match
where orderid not in
(
select orderid from [order details]
group by orderid
having count(orderid)>1
)
process those that are duplicate and insert it afterwards
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Vanitha" wrote:
> How to generate the populate script?
> thanks
> vanitha
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>

Data mapping

We have 2 databases - Prod and Dev. They are not syncronized and has a slightly different stored proc-s and table structure (column added/ or removed).

So Prod and Dev are not in sync, they are not mirrors.

Production db has production data.

How I can transition/move/map data from Prod to Dev?

For example, both databases have table Users :(f_name, l_name, field1, field2). And I need to populate dev db with prod data.

What strategy should I follow? What tools showld I use? How I can automate the it?

Should I use Import wizard or DTS?

SQL Server 2000

Thank you

Based on the information you provide both the DTS Import Wizard or a Copy SQL Server Objects Task DTS package will work and both can be automated by a job schedule. Basically they are the same.

If you need to tranform data, which you con't mention you can use a DTS package with tranforms or write you own truncate/insert code and schedule either as a job.

Friday, February 17, 2012

data import

I have two databases identical in structure but with different data (staging database and a production database). There is a bunch of data in the staging database that needs to be moved to the production database. The data is in two tables and a foreign key link exists between them. The ID's in the tables of both databases started with '1' and started incrementing, so if I import all the data into production, there will be duplicate ID's.

Is there an easy way to combine these tables? I'm not really a dba, but familiar enough with sql from an admin standpoint.

Thanks for any adviceThis is why you should use GUIDs instead of Identity values.

One way around this problem is to add a column to your production tables to store the original ID. Then you can recreate the record relationships after new Identity values are assigned.

Thursday, March 29, 2012

data synchronization

data synchronization

Data structure Internals Question

Data Structure for Date Time Selection

Data Structure Advice

Data Structure Advice

Data Structure -- Its been a while

Tuesday, March 27, 2012

Data Source View of Mining Structure cant be edited?

Monday, March 19, 2012

data page structure

Thursday, March 8, 2012

Data Mining Wizard (Not Responding)

Data Mining Wizard (Not Responding)

Data Mining slicing / filtering

Data Mining slicing / filtering

Wednesday, March 7, 2012

Data mining cube with 0-value facts

Saturday, February 25, 2012

Data Migration

Data Migration

Data mapping

Friday, February 17, 2012

data import

Data transformation services

Blog Archive

About Me