Is there any way to find out a subscriber's assigned identity range (at the
publisher)?
It would be helpful to have an idea where a row was added from its identity
value.
No, it only maintains the last range assigned - which will be the highest
one.
Hilary Cotter
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
<tedcorpus@.hotmail.com> wrote in message
news:%23F7cc61eFHA.412@.tk2msftngp13.phx.gbl...
> Is there any way to find out a subscriber's assigned identity range (at
the
> publisher)?
> It would be helpful to have an idea where a row was added from its
identity
> value.
>
|||In the distribution database, MSrepl_identity_range holds the next ranges,
so You'll have to work with the metadata tables on the subscribers for this:
You could use this type of query:
SELECT sysobjects.name AS TableName, *
FROM MSrepl_identity_range INNER JOIN
sysobjects ON MSrepl_identity_range.objid =
sysobjects.id
The data from each subscriber could be amalgamated at the publisher using
linked servers,
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks!
So I could compare max_identity/current_max columns across servers
(accounting for the range value). But linking the servers just for this
purpose might be more administration work than benefit at this point.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ehDGtl7eFHA.2076@.TK2MSFTNGP15.phx.gbl...
> In the distribution database, MSrepl_identity_range holds the next ranges,
> so You'll have to work with the metadata tables on the subscribers for
> this:
> You could use this type of query:
> SELECT sysobjects.name AS TableName, *
> FROM MSrepl_identity_range INNER JOIN
> sysobjects ON MSrepl_identity_range.objid =
> sysobjects.id
> The data from each subscriber could be amalgamated at the publisher using
> linked servers,
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You only need to look at the max value on the publisher's distribution
database. That will be the next assigned range.
Hilary Cotter
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
"TCorp" <tcorpus@.hotmail.com> wrote in message
news:eg1MgYagFHA.2548@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Thanks!
> So I could compare max_identity/current_max columns across servers
> (accounting for the range value). But linking the servers just for this
> purpose might be more administration work than benefit at this point.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ehDGtl7eFHA.2076@.TK2MSFTNGP15.phx.gbl...
ranges,[vbcol=seagreen]
using
>
Showing posts with label row. Show all posts
Showing posts with label row. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Determine month from inside month row grouping
Hi guys,
i have a matrix that has two row groups, the first is month, the next is year. This is for year on year reporting, so the first two cells in each row are like this:
April 2005
April 2006
April 2007
May 2005
May 2006
May 2007
etc
in one of the columns of the matrix, i need to calculate a "per hour" figure, so i need to determine how many hours are in the month that groups this row.
So what i need to know is: how can i tell what month this row is? I don't care if the month is represented as text ("April") or an int (4), because the pseudo code for the expression will be:
<monthly dollar amount> / (24 * DateDiff("d", CDate("2006-<this month>-01"), DateAdd("M", CDate("2006-<this month>-01"), 1)))
Thanks for any suggestions!
sluggy
Duh, that was a stupid question... the answer was just to use the month dataset member as per usual 
24 * DateDiff("d", CDate("2006-" + Fields!Calendar_Month.Value + "01"), DateAdd("M", 1, CDate("2006-" + Fields!Calendar_Month.Value + "01")))
Sunday, March 25, 2012
Determine if more than one row returned
I have an ASP/ADO application querying an SQL Server DB. I want know the
most efficient way to determine if more than one row is returned from a
query. If more than one row is returned, the user will be presented with a
choice of which row to process. If only one row is returned, I want to skip
this stage, and process that single row immediately.
I can think of a number of ways of acheiving this (eg. .recordcount) but I'm
looking for the slickest and most efficient method.
Any thoughts or suggestions
Thanks
CJMCJM wrote:
> I have an ASP/ADO application querying an SQL Server DB. I want know
> the most efficient way to determine if more than one row is returned
> from a query. If more than one row is returned, the user will be
> presented with a choice of which row to process. If only one row is
> returned, I want to skip this stage, and process that single row
> immediately.
> I can think of a number of ways of acheiving this (eg. .recordcount)
> but I'm looking for the slickest and most efficient method.
> Any thoughts or suggestions
>
Best option: Use a stored procedure that only returns the data if more
than one row meeting the criteria exist.
Second best:
Use GetRows to put the recordset data into an array and check its upper
index bound using the ubound function.
This has the added benefit of allowing you to:
1. use the efficient server-side forward-only cursor (which does not
support recordcount)
2. immediately close and destroy the recordset
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hi,
Thanks for your post!
My understanding of your issue is:
You wanted the most efficient way to retrieve the result row count.
If I have misunderstood, please let me know.
I recommend you write a common stored procedure retrieving the row count of
any query result.
Here is a sample just for reference:
create procedure proc_getqueryrowscount
(
@.strquery varchar(1000)
)
as
declare @.strcountquery varchar(1000)
select @.strcountquery = 'select count(*) from ( ' + @.strquery + ' ) v '
exec ( @.strcountquery )
In your application, design and implement a common application interface
function, such as:
int GetDBQueryCount(String strQuery)
In any place when you need to get a query result row count, it's convenient
to use this function.
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w
to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Best option: Use a stored procedure that only returns the data if more
> than one row meeting the criteria exist.
> Second best:
> Use GetRows to put the recordset data into an array and check its upper
> index bound using the ubound function.
> This has the added benefit of allowing you to:
> 1. use the efficient server-side forward-only cursor (which does not
> support recordcount)
> 2. immediately close and destroy the recordset
>
Bob,
Thanks for the response...
Option 2 is the best in this case... there may only be one row returned
(which is fine) but in this case I need to handle it differently...
Thanks
Chris|||Hi, Chris,
I'm glad to see Bob's advice suited you.
If you have any other concerns, please don't hesitate to contact us.
Thanks for using Microsoft Newsgroup.
Have a good day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||CJM wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Bob,
> Thanks for the response...
> Option 2 is the best in this case... there may only be one row
> returned (which is fine) but in this case I need to handle it
> differently...
>
Why not handle it in the stored procedure?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CJM wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Thanks for the response...
> Option 2 is the best in this case... there may only be one row
> returned (which is fine) but in this case I need to handle it
> differently...
>
Just to expand, something like this:
create procedure ...
declare @.rows int
set @.rows=(select count(*) from table where <criteria> )
if @.rows=0
do something
return
if @.rows = 1
do something else
return
if @.rows > 1
select <columns> from table where <criteria>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OhFw993mGHA.2264@.TK2MSFTNGP04.phx.gbl...
> Just to expand, something like this:
> create procedure ...
> declare @.rows int
> set @.rows=(select count(*) from table where <criteria> )
> if @.rows=0
> do something
> return
> if @.rows = 1
> do something else
> return
> if @.rows > 1
> select <columns> from table where <criteria>
>
It's client-side stuff...need user interaction...
If no rows returned, display error message
If 1 row returned, immediately go to editing page for that row...
If several rows returned, display list. User picks row and it redirects to
editing page...
CJM|||Hi CJM,
If there are at most 2 values, then most methods will probably be
equally fast. However, if there are cases where there are much more than
2 values, then the solution below is probably the fastest. This is
because query execution will end when the second value is found.
Note however, that it is based on the proprietary behavior of SQL Server
that does not require just one row/value to be assigned to a local
variable.
Declare @.the_value int
SELECT TOP 2 @.the_value = value
FROM MyTable
WHERE some_column = 'some value'
If @.@.rowcount > 1
Begin
print 'Please select a value'
..
End
Else
SELECT @.the_value AS "This is the value"
HTH,
Gert-Jan
CJM wrote:
> I have an ASP/ADO application querying an SQL Server DB. I want know the
> most efficient way to determine if more than one row is returned from a
> query. If more than one row is returned, the user will be presented with a
> choice of which row to process. If only one row is returned, I want to ski
p
> this stage, and process that single row immediately.
> I can think of a number of ways of acheiving this (eg. .recordcount) but I
'm
> looking for the slickest and most efficient method.
> Any thoughts or suggestions
> Thanks
> CJM
most efficient way to determine if more than one row is returned from a
query. If more than one row is returned, the user will be presented with a
choice of which row to process. If only one row is returned, I want to skip
this stage, and process that single row immediately.
I can think of a number of ways of acheiving this (eg. .recordcount) but I'm
looking for the slickest and most efficient method.
Any thoughts or suggestions
Thanks
CJMCJM wrote:
> I have an ASP/ADO application querying an SQL Server DB. I want know
> the most efficient way to determine if more than one row is returned
> from a query. If more than one row is returned, the user will be
> presented with a choice of which row to process. If only one row is
> returned, I want to skip this stage, and process that single row
> immediately.
> I can think of a number of ways of acheiving this (eg. .recordcount)
> but I'm looking for the slickest and most efficient method.
> Any thoughts or suggestions
>
Best option: Use a stored procedure that only returns the data if more
than one row meeting the criteria exist.
Second best:
Use GetRows to put the recordset data into an array and check its upper
index bound using the ubound function.
This has the added benefit of allowing you to:
1. use the efficient server-side forward-only cursor (which does not
support recordcount)
2. immediately close and destroy the recordset
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hi,
Thanks for your post!
My understanding of your issue is:
You wanted the most efficient way to retrieve the result row count.
If I have misunderstood, please let me know.
I recommend you write a common stored procedure retrieving the row count of
any query result.
Here is a sample just for reference:
create procedure proc_getqueryrowscount
(
@.strquery varchar(1000)
)
as
declare @.strcountquery varchar(1000)
select @.strcountquery = 'select count(*) from ( ' + @.strquery + ' ) v '
exec ( @.strcountquery )
In your application, design and implement a common application interface
function, such as:
int GetDBQueryCount(String strQuery)
In any place when you need to get a query result row count, it's convenient
to use this function.
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w
to allMicrosoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Best option: Use a stored procedure that only returns the data if more
> than one row meeting the criteria exist.
> Second best:
> Use GetRows to put the recordset data into an array and check its upper
> index bound using the ubound function.
> This has the added benefit of allowing you to:
> 1. use the efficient server-side forward-only cursor (which does not
> support recordcount)
> 2. immediately close and destroy the recordset
>
Bob,
Thanks for the response...
Option 2 is the best in this case... there may only be one row returned
(which is fine) but in this case I need to handle it differently...
Thanks
Chris|||Hi, Chris,
I'm glad to see Bob's advice suited you.
If you have any other concerns, please don't hesitate to contact us.
Thanks for using Microsoft Newsgroup.
Have a good day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w
to allMicrosoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||CJM wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Bob,
> Thanks for the response...
> Option 2 is the best in this case... there may only be one row
> returned (which is fine) but in this case I need to handle it
> differently...
>
Why not handle it in the stored procedure?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CJM wrote:
> "Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
> news:O$GUbesmGHA.1596@.TK2MSFTNGP04.phx.gbl...
> Thanks for the response...
> Option 2 is the best in this case... there may only be one row
> returned (which is fine) but in this case I need to handle it
> differently...
>
Just to expand, something like this:
create procedure ...
declare @.rows int
set @.rows=(select count(*) from table where <criteria> )
if @.rows=0
do something
return
if @.rows = 1
do something else
return
if @.rows > 1
select <columns> from table where <criteria>
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OhFw993mGHA.2264@.TK2MSFTNGP04.phx.gbl...
> Just to expand, something like this:
> create procedure ...
> declare @.rows int
> set @.rows=(select count(*) from table where <criteria> )
> if @.rows=0
> do something
> return
> if @.rows = 1
> do something else
> return
> if @.rows > 1
> select <columns> from table where <criteria>
>
It's client-side stuff...need user interaction...
If no rows returned, display error message
If 1 row returned, immediately go to editing page for that row...
If several rows returned, display list. User picks row and it redirects to
editing page...
CJM|||Hi CJM,
If there are at most 2 values, then most methods will probably be
equally fast. However, if there are cases where there are much more than
2 values, then the solution below is probably the fastest. This is
because query execution will end when the second value is found.
Note however, that it is based on the proprietary behavior of SQL Server
that does not require just one row/value to be assigned to a local
variable.
Declare @.the_value int
SELECT TOP 2 @.the_value = value
FROM MyTable
WHERE some_column = 'some value'
If @.@.rowcount > 1
Begin
print 'Please select a value'
..
End
Else
SELECT @.the_value AS "This is the value"
HTH,
Gert-Jan
CJM wrote:
> I have an ASP/ADO application querying an SQL Server DB. I want know the
> most efficient way to determine if more than one row is returned from a
> query. If more than one row is returned, the user will be presented with a
> choice of which row to process. If only one row is returned, I want to ski
p
> this stage, and process that single row immediately.
> I can think of a number of ways of acheiving this (eg. .recordcount) but I
'm
> looking for the slickest and most efficient method.
> Any thoughts or suggestions
> Thanks
> CJM
Wednesday, March 21, 2012
detect existence of datetime fields
I'm looking for an efficient t-sql script to loop through all user
tables in a db and determine/print the value of each row having a
datetime field (including cases where there are multiple datetime
fields per row)
tables in a db and determine/print the value of each row having a
datetime field (including cases where there are multiple datetime
fields per row)
Help greatly appreciated.."JTWhaler" <jonahturnedwhaler@.yahoo.com> wrote in message
news:ed564348.0401022017.34730d8@.posting.google.co m...
> I'm looking for an efficient t-sql script to loop through all user
> tables in a db and determine/print the value of each row having a
> datetime field (including cases where there are multiple datetime
> fields per row)
> Help greatly appreciated..
Here is a script that does a reasonable task of generating
a useful data dictionary out of any database.
To identify the datetime fields simply add
a restriction line in the WHERE bit ... such as
and t.name in ('DateTime', 'SmallDateTime')
select substring(o.name,1,50) as "Table Name",
o.type "Typ",
c.colid,
substring(c.name,1,30) as "Column Name",
substring(t.name,1,30) as "DataType",
c.length
from sysobjects o
left join syscolumns c on (o.id=c.id)
left join systypes t on (c.xusertype=t.xusertype)
where o.type = 'U'
order by 1,3|||jonahturnedwhaler@.yahoo.com (JTWhaler) wrote in message news:<ed564348.0401022017.34730d8@.posting.google.com>...
> I'm looking for an efficient t-sql script to loop through all user
> tables in a db and determine/print the value of each row having a
> datetime field (including cases where there are multiple datetime
> fields per row)
> Help greatly appreciated..
Hi Jonah
The attached T-sql will create a database table that contains an entry
for each datetime column in the database
drop table datetime_column
create table datetime_column
(
table_name varchar(200)
,column_name varchar(200)
,datetime_value datetime null
)
declare @.table_schema as varchar(200)
declare @.table_name as varchar(200)
declare @.column_name as varchar(200)
declare @.query as nvarchar(1000)
DECLARE column_cursor CURSOR FOR
SELECT
table_name, column_name
FROM
INFORMATION_Schema.columns
where
data_type = 'datetime'
and table_name != 'datetime_column'
open column_cursor
FETCH NEXT FROM
column_cursor
INTO
@.table_name, @.column_name
while @.@.fetch_status = 0
begin
select @.query = ' insert into datetime_column select ' + '''' +
@.table_name + '''' + ',' + '''' + @.column_name + '''' + ',' +
@.column_name + ' from ' + @.table_name
--select @.query
exec sp_executesql @.query
FETCH NEXT FROM
column_cursor
INTO
@.table_name, @.column_name
end
close column_cursor
deallocate column_cursor
select * from datetime_column
Monday, March 19, 2012
Detail row doesn't repeat!
How do I set the detail row to show all similar records?
I created another report with same exact query and it shows all 25.
Thanks,
TrintPlease provide more details than this for us to be able to help you.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"trint" <trinity.smith@.gmail.com> wrote in message
news:1105127010.082333.226250@.z14g2000cwz.googlegroups.com...
> How do I set the detail row to show all similar records?
> I created another report with same exact query and it shows all 25.
> Thanks,
> Trint
>|||header --> stufff
details--> one row and should be 25.|||header --> stufff
details--> one row and should be 25.|||Sounds like a developer error to me.
You're obviously not eager to work at explaining your problem. Why should
we work to help you solve it?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"trint" <trinity.smith@.gmail.com> wrote in message
news:1105129238.972872.279370@.c13g2000cwb.googlegroups.com...
> header --> stufff
> details--> one row and should be 25.
>|||Ok,
What is required of a 'detail' row to display multiple instances of
records with, let's say '123' as the first similar column...is that a
setting in the layout?
Thanks,
Trint|||The hide duplicates property.
"trint" wrote:
> Ok,
> What is required of a 'detail' row to display multiple instances of
> records with, let's say '123' as the first similar column...is that a
> setting in the layout?
> Thanks,
> Trint
>
I created another report with same exact query and it shows all 25.
Thanks,
TrintPlease provide more details than this for us to be able to help you.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"trint" <trinity.smith@.gmail.com> wrote in message
news:1105127010.082333.226250@.z14g2000cwz.googlegroups.com...
> How do I set the detail row to show all similar records?
> I created another report with same exact query and it shows all 25.
> Thanks,
> Trint
>|||header --> stufff
details--> one row and should be 25.|||header --> stufff
details--> one row and should be 25.|||Sounds like a developer error to me.
You're obviously not eager to work at explaining your problem. Why should
we work to help you solve it?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"trint" <trinity.smith@.gmail.com> wrote in message
news:1105129238.972872.279370@.c13g2000cwb.googlegroups.com...
> header --> stufff
> details--> one row and should be 25.
>|||Ok,
What is required of a 'detail' row to display multiple instances of
records with, let's say '123' as the first similar column...is that a
setting in the layout?
Thanks,
Trint|||The hide duplicates property.
"trint" wrote:
> Ok,
> What is required of a 'detail' row to display multiple instances of
> records with, let's say '123' as the first similar column...is that a
> setting in the layout?
> Thanks,
> Trint
>
Friday, February 17, 2012
Design of Dimension table ID in a datamart - best practise
Best practise wanted!
In a datamart (DM) a fact row is linked to a dimension by a dimension ID (key). If a DM is build on top of a (enterprise) datawarehouse (EDW), and the dimension table has a corresponding EDW table with a unique ID (dummy key), will there then be any DM design conciderations for not using that EDW table key.
In other words when you design the DM, should the dimensions then have there own (new) ID's regardless of you allready have modelled (unique) ID in the EDW. I have these considerations in favour for using the EDW table ID keys i a dimension:
1. Staging the DM is easy, the EDW table staging is handling the correctness of the dimension ID's.
2. Backtracing concering DM quality checking, errors and so on, is simplyfied by using the same ID in both DM and EDW.
No. Use the same technical key in DM as in EDW.
If you have a product dimension that is used in several data marts, like sales or production planning, you would like to use the same key in order to consolidate information in these data marts. In SSAS 2000/2005 you can then build cubes with the same product dimension which also means less dimension design and maintenance.
HTH
Thomas Ivarsson
|||Thanks
Subscribe to:
Posts (Atom)