Data Mart rollbacks

How have folks been managing rollbacks on failures inside SSIS when populating data marts?
For example - we have a seperate package for each dimension table, then a master Fact table update. If one of the dimension table updates fails - how have you rolled back the previous changes in the tables updated prior to the failure - or if the Fact tabel package fails - how do you manage rollback in all the dimension tables?
My first thought was using the Audit table information to determine which tables needed rolled back.
Hello Joe,
What about putting the Tasks (Execute Package tasks) in a transaction?
http://msdn2.microsoft.com/en-us/library/ms137690(SQL.90).aspx

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> How have folks been managing rollbacks on failures inside SSIS when
> populating data marts?
> For example - we have a seperate package for each dimension table,
> then a master Fact table update. If one of the dimension table
> updates fails - how have you rolled back the previous changes in the
> tables updated prior to the failure - or if the Fact tabel package
> fails - how do you manage rollback in all the dimension tables?
> My first thought was using the Audit table information to determine
> which tables needed rolled back.
>
|||Is this what your team would implement?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:885683c261f8c97ff6e74166f0@.news.microsoft.com ...
> Hello Joe,
> What about putting the Tasks (Execute Package tasks) in a transaction?
> http://msdn2.microsoft.com/en-us/library/ms137690(SQL.90).aspx
>
> --
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
>
|||Hello Joe,
Yes. I would be looking to put things inside of transactions. I may logically
split things up but yes transactions would be the way for me
Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com
[vbcol=seagreen]
> Is this what your team would implement?
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:885683c261f8c97ff6e74166f0@.news.microsoft.com ...
|||the easy way:
backup the DB, execute the ETLs, restore the DB in case of a problem...
in fact, its a big recommendation to always backup first, so there is no overhead here.
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:D6985AF3-799E-4B96-8A87-7A823C9C1FC2@.microsoft.com...
How have folks been managing rollbacks on failures inside SSIS when populating data marts?
For example - we have a seperate package for each dimension table, then a master Fact table update. If one of the dimension table updates fails - how have you rolled back the previous changes in the tables updated prior to the failure - or if the Fact tabel package fails - how do you manage rollback in all the dimension tables?
My first thought was using the Audit table information to determine which tables needed rolled back.

Data Mart rollbacks

How have folks been managing rollbacks on failures inside SSIS when populati
ng data marts?
For example - we have a seperate package for each dimension table, then a ma
ster Fact table update. If one of the dimension table updates fails - how h
ave you rolled back the previous changes in the tables updated prior to the
failure - or if the Fact tabel package fails - how do you manage rollback in
all the dimension tables?
My first thought was using the Audit table information to determine which ta
bles needed rolled back.Hello Joe,
What about putting the Tasks (Execute Package tasks) in a transaction?
http://msdn2.microsoft.com/en-us/library/ms137690(SQL.90).aspx
Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

> How have folks been managing rollbacks on failures inside SSIS when
> populating data marts?
> For example - we have a seperate package for each dimension table,
> then a master Fact table update. If one of the dimension table
> updates fails - how have you rolled back the previous changes in the
> tables updated prior to the failure - or if the Fact tabel package
> fails - how do you manage rollback in all the dimension tables?
> My first thought was using the Audit table information to determine
> which tables needed rolled back.
>|||Is this what your team would implement?
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:885683c261f8c97ff6e74166f0@.news.microsoft.com...
> Hello Joe,
> What about putting the Tasks (Execute Package tasks) in a transaction?
> http://msdn2.microsoft.com/en-us/library/ms137690(SQL.90).aspx
>
> --
> Allan Mitchell
> http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
> http://www.konesans.com
>
>|||Hello Joe,
Yes. I would be looking to put things inside of transactions. I may logica
lly
split things up but yes transactions would be the way for me
--
Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com
[vbcol=seagreen]
> Is this what your team would implement?
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:885683c261f8c97ff6e74166f0@.news.microsoft.com...
>|||the easy way:
backup the DB, execute the ETLs, restore the DB in case of a problem...
in fact, its a big recommendation to always backup first, so there is no ove
rhead here.
"Joe" <hortoristic@.gmail.dot.com> wrote in message news:D6985AF3-799E-4B96-8
A87-7A823C9C1FC2@.microsoft.com...
How have folks been managing rollbacks on failures inside SSIS when populati
ng data marts?
For example - we have a seperate package for each dimension table, then a ma
ster Fact table update. If one of the dimension table updates fails - how h
ave you rolled back the previous changes in the tables updated prior to the
failure - or if the Fact tabel package fails - how do you manage rollback in
all the dimension tables?
My first thought was using the Audit table information to determine which ta
bles needed rolled back.

Data Mart Maintenance

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

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

-- cursor to loop through File Groups

DECLARE FG CURSOR FOR

SELECT DISTINCT groupid

FROM sysfilegroups

OPEN FG

FETCH NEXT FROM FG INTO @.fgroupid

WHILE @.@.Fetch_status=0

BEGIN

-- Cursor to loop through filenames

DECLARE FILENAME CURSOR FOR

SELECT DISTINCT f.name

FROM sysfiles f

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

OPEN FILENAME

FETCH NEXT FROM FILENAME INTO @.fname

WHILE @.@.Fetch_status=0

BEGIN

PRINT 'Current Filename = ' + @.fname

--Table Name Cursor

DECLARE TBL CURSOR FOR

--This query selects tables with clustered indexes only

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

FROM INFORMATION_SCHEMA.TABLES i

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

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

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

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

AND sf.filename=@.fname

OPEN TBL

FETCH NEXT FROM TBL INTO @.tname

WHILE @.@.Fetch_status=0

BEGIN

PRINT @.SQLA

EXEC (@.SQLA)

FETCH NEXT FROM TBL INTO @.SQLA

END

CLOSE TBL

DEALLOCATE TBL

FETCH NEXT FROM FILENAME INTO @.fname

END

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

EXEC (@.SQLB)

CLOSE FILENAME

DEALLOCATE FILENAME

FETCH NEXT FROM FG INTO @.fgroupid

Data Mart Maintenance

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

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

-- cursor to loop through File Groups

DECLARE FG CURSOR FOR

SELECT DISTINCT groupid

FROM sysfilegroups

OPEN FG

FETCH NEXT FROM FG INTO @.fgroupid

WHILE @.@.Fetch_status=0

BEGIN

-- Cursor to loop through filenames

DECLARE FILENAME CURSOR FOR

SELECT DISTINCT f.name

FROM sysfiles f

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

OPEN FILENAME

FETCH NEXT FROM FILENAME INTO @.fname

WHILE @.@.Fetch_status=0

BEGIN

PRINT 'Current Filename = ' + @.fname

--Table Name Cursor

DECLARE TBL CURSOR FOR

--This query selects tables with clustered indexes only

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

FROM INFORMATION_SCHEMA.TABLES i

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

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

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

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

AND sf.filename=@.fname

OPEN TBL

FETCH NEXT FROM TBL INTO @.tname

WHILE @.@.Fetch_status=0

BEGIN

PRINT @.SQLA

EXEC (@.SQLA)

FETCH NEXT FROM TBL INTO @.SQLA

END

CLOSE TBL

DEALLOCATE TBL

FETCH NEXT FROM FILENAME INTO @.fname

END

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

EXEC (@.SQLB)

CLOSE FILENAME

DEALLOCATE FILENAME

FETCH NEXT FROM FG INTO @.fgroupid

Data Mart

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

Quote:

Originally Posted by yogeshbhandare

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

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

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