Another Time Dimension question

I want to track sales vs. both date scheduled to be shipped and date actually shipped. Both are fields in my OLTP. I can set the SSIS program to extract data any why I choose.

Would it be best to create a fact table and 2 different dimension tables or combine the time fields in one dimension table or leave them in the fact table and let SQL Server extract the time dimension?

Thanks.

Hello! In SSAS2005(Analysis services 2005) you have something called 'Role-playing' dimensions that solves this problem. You will only need one time dimension in the starschema that you join to all dates in the fact table.

If you have these relations designed in the source system/star schema or in the data source view SSAS2005 will detect this relation and create separate time dimensions automatically when you build the cube.

HTH

Thomas Ivarsson

|||I don't want to apply one time dimension to many fact tables. I want to apply many time dimensions to one fact table. Right now my date fields are in the Fact Table.|||

I do not think that I have said that but my explanation, perhaps, was not good enough.

You use one time dimension and join the fact tables different date dimensions to the same date key in the time dimension table.

Connect the key for order date in the fact table to the date key in the time dimension. Connect the date key for invoice date in the fact table to the date key in the time dimension.

HTH

Thomas Ivarsson

|||

Ahh...that's better....I think I need to take my dates out of my fact table and create a dimTime table and then I can create the multiple relationships.

Thanks.

|||

That seems to be working but I'm a little fuzzy on the theory.

Either or both date fields may be null in my OLTP table, so I can't use either for the key field. My OLTP table has an integer key that is just a counting number.

I created the dimTime table with the same type key field. When I did my SSIS run I just copied the Fact table key field to the dimTime table key field along with the date fields.

The tables are linked but not throught a date field. SSAS is smart enough to build its aggregates on the date fields and pretty much ignore the key field? It only cares that the tables are linked somehow?

Thanks!!!!

|||

You can add a theoretical time member to the time dimension like '2099-12-31" and point fact records without time members to that time member.

There will be many more SQL Server releases until we reach that date.

HTH

/Thomas Ivarsson

anormal increase of rowmodctr for a table

HI,
We have 5 GIG OLTP database with 25 users.
Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
days
I cannot explain why rowmodcrt increase so rapidly for a specific table.
It look like 500,000 modifications are done on this table evweryday.
After 4 days, rowmodctr = 2,000,000
I f check witth PROFILER, around 200 upgrades are done on this table
everyday.
Since fews days, I decided to scedule every nignt the Maintenace Plan that
Update Statistic
Any idea ?
Thank you
danny
(P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
on the table since the last time the statistics were updated or created.Hi Danny
Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
does?
It is not a supplied procedure, so somebody must have added it to your
system.
Profiler will show you the number of update commands, but rowmodctr counts
every row. How many rows are being updated in each update statement?
How many rows are in the table?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Thank you Kalen

answer: each update statement update only 1 row
answer : 22,000 rows
CREATE PROCEDURE Usp_dba_ShowMe_TableStats @.dbname sysname=NULL, @.option
char (1) = NULL
AS
BEGIN
Declare @.what char(1),
@.qry varchar(2000)
set @.what = @.option
IF @.what = 'I'
Begin
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
ELSE IF @.what = 'A'
Begin
Print space(10)+' Run the Update Statistics on the following Tables'
SET @.qry = 'SET NOCOUNT ON'+char(13)+ 'Use ' + @.dbName + ' SELECT Distinct
''UPDATE STATISTICS''+SPACE(1)+O.NAME+CHAR(13)+''GO'' FROM SYSOBJECTS O
JOIN SYSINDEXES i ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
--ORDER BY O.NAME'
exec (@.qry)
End
ELSE
Begin
Print space(10)+'Please pass in the right parameters : DBName and option
"I" for Information or "A" Action"'
PRINT
'----
----'
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
END
"Kalen Delaney" <replies@.public_newsgroups.com> a crit dans le message de
news:O9cu6uPAEHA.3048@.tk2msftngp13.phx.gbl...
> Hi Danny
> Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
> does?
> It is not a supplied procedure, so somebody must have added it to your
> system.
> Profiler will show you the number of update commands, but rowmodctr counts
> every row. How many rows are being updated in each update statement?
> How many rows are in the table?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
> news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
(clustered)
every
that
changes
>|||HI,
I think I found what happens
A process is modifiing rowmodctr associated with a _WA_Sys
I think I identify the user associated with this process.
danny
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a crit dans le message de
news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Hi Danny
Good catch! It is a interesting procedure you have, but you can add another
condition to ignore column stats. The number may be high as those are
automatically dropped if no one uses the stats for a while and then maybe
rebuilt later. There is little overhead.
To have your proc ignore stats only rows in sysindexes, you can make this
change:
SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
and indexproperty(i.id, indid, 'IsStatistics') = 0 -- <=======
additional condition
ORDER BY i.rowmodctr DESC
Please read about INDEXPROPERTY in the BOL for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:O9AoXHhAEHA.3308@.TK2MSFTNGP10.phx.gbl...
> HI,
> I think I found what happens
> A process is modifiing rowmodctr associated with a _WA_Sys
> I think I identify the user associated with this process.
> danny
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a crit dans le message de
> news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
(clustered)
every
that
changes
>

anormal increase of rowmodctr for a table

HI,
We have 5 GIG OLTP database with 25 users.
Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
days
I cannot explain why rowmodcrt increase so rapidly for a specific table.
It look like 500,000 modifications are done on this table evweryday.
After 4 days, rowmodctr = 2,000,000
I f check witth PROFILER, around 200 upgrades are done on this table
everyday.
Since fews days, I decided to scedule every nignt the Maintenace Plan that
Update Statistic
Any idea ?
Thank you
danny
(P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
on the table since the last time the statistics were updated or created.Hi Danny
Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
does?
It is not a supplied procedure, so somebody must have added it to your
system.
Profiler will show you the number of update commands, but rowmodctr counts
every row. How many rows are being updated in each update statement?
How many rows are in the table?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Thank you Kalen
>>How many rows are being updated in each update statement
answer: each update statement update only 1 row
>>How many rows are in the table
answer : 22,000 rows
CREATE PROCEDURE Usp_dba_ShowMe_TableStats @.dbname sysname=NULL, @.option
char (1) = NULL
AS
BEGIN
Declare @.what char(1),
@.qry varchar(2000)
set @.what = @.option
IF @.what = 'I'
Begin
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
ELSE IF @.what = 'A'
Begin
Print space(10)+' Run the Update Statistics on the following Tables'
SET @.qry = 'SET NOCOUNT ON'+char(13)+ 'Use ' + @.dbName + ' SELECT Distinct
''UPDATE STATISTICS''+SPACE(1)+O.NAME+CHAR(13)+''GO'' FROM SYSOBJECTS O
JOIN SYSINDEXES i ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
--ORDER BY O.NAME'
exec (@.qry)
End
ELSE
Begin
Print space(10)+'Please pass in the right parameters : DBName and option
"I" for Information or "A" Action"'
PRINT
'----
----'
set @.qry = ' Use ' + @.dbName +' SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
ORDER BY i.rowmodctr DESC'
exec (@.qry)
End
END
"Kalen Delaney" <replies@.public_newsgroups.com> a écrit dans le message de
news:O9cu6uPAEHA.3048@.tk2msftngp13.phx.gbl...
> Hi Danny
> Can you tell us a bit about what the procedure sp_dba_ShowMe_TableStats
> does?
> It is not a supplied procedure, so somebody must have added it to your
> system.
> Profiler will show you the number of update commands, but rowmodctr counts
> every row. How many rows are being updated in each update statement?
> How many rows are in the table?
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
> news:#h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> > HI,
> >
> > We have 5 GIG OLTP database with 25 users.
> > Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k
(clustered)
> >
> > Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'"
every
> > days
> > I cannot explain why rowmodcrt increase so rapidly for a specific table.
> > It look like 500,000 modifications are done on this table evweryday.
> > After 4 days, rowmodctr = 2,000,000
> > I f check witth PROFILER, around 200 upgrades are done on this table
> > everyday.
> > Since fews days, I decided to scedule every nignt the Maintenace Plan
that
> > Update Statistic
> >
> > Any idea ?
> >
> > Thank you
> > danny
> > (P.S. The rowmodctr column in SYSINDEXES value shows the number of
changes
> > on the table since the last time the statistics were updated or created.
> >
> >
>|||HI,
I think I found what happens
A process is modifiing rowmodctr associated with a _WA_Sys
I think I identify the user associated with this process.
danny
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a écrit dans le message de
news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> HI,
> We have 5 GIG OLTP database with 25 users.
> Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k (clustered)
> Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'" every
> days
> I cannot explain why rowmodcrt increase so rapidly for a specific table.
> It look like 500,000 modifications are done on this table evweryday.
> After 4 days, rowmodctr = 2,000,000
> I f check witth PROFILER, around 200 upgrades are done on this table
> everyday.
> Since fews days, I decided to scedule every nignt the Maintenace Plan that
> Update Statistic
> Any idea ?
> Thank you
> danny
> (P.S. The rowmodctr column in SYSINDEXES value shows the number of changes
> on the table since the last time the statistics were updated or created.
>|||Hi Danny
Good catch! It is a interesting procedure you have, but you can add another
condition to ignore column stats. The number may be high as those are
automatically dropped if no one uses the stats for a while and then maybe
rebuilt later. There is little overhead.
To have your proc ignore stats only rows in sysindexes, you can make this
change:
SELECT substring(o.name,1,50) AS [table
name], substring(o.name,1,50) AS [Index Name], i.rowmodctr AS [Rows
Modified]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0 and o.xtype = ''U''
and indexproperty(i.id, indid, 'IsStatistics') = 0 -- <=======additional condition
ORDER BY i.rowmodctr DESC
--
Please read about INDEXPROPERTY in the BOL for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> wrote in message
news:O9AoXHhAEHA.3308@.TK2MSFTNGP10.phx.gbl...
> HI,
> I think I found what happens
> A process is modifiing rowmodctr associated with a _WA_Sys
> I think I identify the user associated with this process.
> danny
> "Danny Presse" <DannyP@.congresmtl-NO-SPAM.com> a écrit dans le message de
> news:%23h$$mjKAEHA.3804@.TK2MSFTNGP09.phx.gbl...
> > HI,
> >
> > We have 5 GIG OLTP database with 25 users.
> > Last week, we upgraded from SQL 7.0 to SQL 2000 (sp3a) on W2k
(clustered)
> >
> > Since last week, I began to use "sp_dba_ShowMe_TableStats 'DB','I'"
every
> > days
> > I cannot explain why rowmodcrt increase so rapidly for a specific table.
> > It look like 500,000 modifications are done on this table evweryday.
> > After 4 days, rowmodctr = 2,000,000
> > I f check witth PROFILER, around 200 upgrades are done on this table
> > everyday.
> > Since fews days, I decided to scedule every nignt the Maintenace Plan
that
> > Update Statistic
> >
> > Any idea ?
> >
> > Thank you
> > danny
> > (P.S. The rowmodctr column in SYSINDEXES value shows the number of
changes
> > on the table since the last time the statistics were updated or created.
> >
> >
>