Data style?

Hi, I would like to change the data output style
generated from the SQL query. So, how can I go about
doing it? Example : display 59766 as 59,766 and also
datetime as date.
Please help...thanks a lot!
On Tue, 1 Feb 2005 20:39:35 -0800, Wendy W wrote:

>Hi, I would like to change the data output style
>generated from the SQL query. So, how can I go about
>doing it? Example : display 59766 as 59,766 and also
>datetime as date.
>Please help...thanks a lot!
>
Hi Wendy,
The formatting of the output of a query is entirely up to the client. If
you access the same data using Query Analyser, Enterprise Manager, Access
and some other tools, you'll already see a variety in formats used, as
each of these tools has it's own defaults.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||
>Hi Wendy,
>The formatting of the output of a query is entirely up
to the client. If
>you access the same data using Query Analyser,
Enterprise Manager, Access
>and some other tools, you'll already see a variety in
formats used, as
>each of these tools has it's own defaults.
>Best, Hugo
>--
Thanks, Hugo but my situation is slightly different here.
I'm supposed to display records in a data grip (ASP.NET)
using the SQL statement. I can't format the data grid
column to my desired format, the grid is dynamic.
Therefore I need to format the fields in SQL statement.
How can this be solved?
|||Wendy,
You will have to provide the data as character-based data,
preformatted as needed. You could try various solutions such
as these, shown with an integer variable @.int and the getdate() function
instead of an entire table::
declare @.int integer
set @.int = 32476
select parsename(convert(varchar(20),cast(@.int as money), 1),2)
select convert(char(10),getdate(),101)
select convert(char(10),getdate(),121)
Steve Kass
Drew University
Wendy W wrote:

>to the client. If
>
>Enterprise Manager, Access
>
>formats used, as
>
>
>Thanks, Hugo but my situation is slightly different here.
>I'm supposed to display records in a data grip (ASP.NET)
>using the SQL statement. I can't format the data grid
>column to my desired format, the grid is dynamic.
>Therefore I need to format the fields in SQL statement.
>How can this be solved?
>
sql

Data Sparsity across dimensions

With the AutoExists functionality, in one dimension I would never have a combination of two mutually exclusive members. For example day of week in a date dimension. If I wanted to have Day of Week as a seperate dimension (this is the simplest example I can think of trying to explain my current situation) is there any way to enforce the same relationship so that when I select a specific date it knows what day of the week it is and doesn't try and cross the two dimensions every time?

Thanks in advance.
Mike

There are others that have had the same thought. Is this what you are looking fore(http://prologika.com/CS/blogs/blog/archive/2006/10/21/Linked-Attribute-Hierarchies.aspx) ?

Perhaps in a future release.

HTH

Thomas Ivarsson

|||Hi Thomas

Thanks for this, if this is in a future release I really think it would be great.

Mike

Data Shaping Question (Syntax)

Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
can see only one example of data shaping in MSDN). How do I calculate
fields ? Starting with:
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
SomeNumber FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, ShipVia, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"RELATE CustomerID TO CustomerID)"
(from Northwind), I'd like to find out the syntax for sum-ming on, say,
ShipVia, (although it does not make any logical sense to sum on that
column). So, in other words, I'd like to add ***to the parent record***
(the one coming from Customers), a calculated column containing a sum
on the child (chapter), so for customer "ALFKI" I should add up 1, 2,
1, 3, 1, and 1, obtaining 9.
I can, of course, change it to
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, EmployeeID, OrderDate,
SUM(ShipVia) as SumOfShipVia FROM Orders " & _
"GROUP BY CustomerID, OrderID, EmployeeID, OrderDate} AS Orders
" & _
"RELATE CustomerID TO CustomerID)"
but then the SUM is not a sum, since it's still in the child chapter.
How do I show the sum in the parent record ?
Similar - This contains three levels: Customers, Orders, and
OrderDetails.
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ((SHAPE {SELECT CustomerID, OrderID, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, Quantity FROM
[Order Details]} AS Orderdetails " & _
"RELATE OrderID TO OrderID) As OrderDetails) " & _
"RELATE CustomerID TO CustomerID)"
What if I wanted to add to the child (Orders) a calculated column on a
column in the grandchild, OrderDetails, say, OrdersDetails.UnitPrice ?
What would be the syntax then ?
Thank you very much for your help.
Alex.
"Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166718883.058620.244010@.80g2000cwy.googlegro ups.com...
> Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
> can see only one example of data shaping in MSDN). How do I calculate
> fields ? Starting with:
> . . ..
This data shaping syntax is an old, dead feature of ADO. Don't use it. In
..NET use multi-table DataSets, which are a much better solution for
client-side shaped data.
David
|||Well, David, the thing is that I don't use dotnet - I have to write
some VBA code in Excel 2000 to show data in a hierarchical data grid,
so that the user can look at a list of workgroups with their details
(aggregate data at workgroup level), then click on a workgroup and
explode it so that the teams contained in that workgroup are seen, with
their details, and then clicks on one of the teams in the workgroup and
sees the contained team-members, with their details, kind of a treeview
(actually, a fancy datagrid). Since I don't have any 3rd party tools
available, I have to make it work with the default (MS) controls.
MSHFlexGrid is one of them, and it does exactly what the user needed
(show data in a tree-like structure).
So I should show:
root
-- list of workgroups, with agregate data (#hours worked, for
instance, by workgroup)
-- list of teams in current workgroup, with agregate data (#hours
worked by team)
-- list of workers in current team, with #hours worked by
worker
PS. All this data comes from some MSAccess tables.
Thank you very much.
David Browne wrote:
> "Radu" <cuca_macaii2000@.yahoo.com> wrote in message
> news:1166718883.058620.244010@.80g2000cwy.googlegro ups.com...
> This data shaping syntax is an old, dead feature of ADO. Don't use it. In
> .NET use multi-table DataSets, which are a much better solution for
> client-side shaped data.
> David
|||"Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166727210.486584.307260@.i12g2000cwa.googlegr oups.com...
> Well, David, the thing is that I don't use dotnet - I have to write
> some VBA code in Excel 2000 to show data in a hierarchical data grid,
> so that the user can look at a list of workgroups with their details
> (aggregate data at workgroup level), then click on a workgroup and
> explode it so that the teams contained in that workgroup are seen, with
> their details, and then clicks on one of the teams in the workgroup and
> sees the contained team-members, with their details, kind of a treeview
> (actually, a fancy datagrid). Since I don't have any 3rd party tools
> available, I have to make it work with the default (MS) controls.
> MSHFlexGrid is one of them, and it does exactly what the user needed
> (show data in a tree-like structure).
> So I should show:
> root
> -- list of workgroups, with agregate data (#hours worked, for
> instance, by workgroup)
> -- list of teams in current workgroup, with agregate data (#hours
> worked by team)
> -- list of workers in current team, with #hours worked by
> worker
> PS. All this data comes from some MSAccess tables.
>
SO first off, this is posted in a SQL Server group. Your solution doesn't
involve SQL Server. If it did, I'd advise you to create views in SQL Server
that add the appropriate columns to the resultset. Perhaps you can do the
same in access with saved queries, or subqueries.
David
>

Data Shaping Question (Syntax)

Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
can see only one example of data shaping in MSDN). How do I calculate
fields ? Starting with:
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
SomeNumber FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, ShipVia, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"RELATE CustomerID TO CustomerID)"
(from Northwind), I'd like to find out the syntax for sum-ming on, say,
ShipVia, (although it does not make any logical sense to sum on that
column). So, in other words, I'd like to add ***to the parent record***
(the one coming from Customers), a calculated column containing a sum
on the child (chapter), so for customer "ALFKI" I should add up 1, 2,
1, 3, 1, and 1, obtaining 9.
I can, of course, change it to
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, EmployeeID, OrderDate,
SUM(ShipVia) as SumOfShipVia FROM Orders " & _
"GROUP BY CustomerID, OrderID, EmployeeID, OrderDate} AS Orders
" & _
"RELATE CustomerID TO CustomerID)"
but then the SUM is not a sum, since it's still in the child chapter.
How do I show the sum in the parent record ?
Similar - This contains three levels: Customers, Orders, and
OrderDetails.
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ((SHAPE {SELECT CustomerID, OrderID, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, Quantity FROM
[Order Details]} AS Orderdetails " & _
"RELATE OrderID TO OrderID) As OrderDetails) " & _
"RELATE CustomerID TO CustomerID)"
What if I wanted to add to the child (Orders) a calculated column on a
column in the grandchild, OrderDetails, say, OrdersDetails.UnitPrice ?
What would be the syntax then ?
Thank you very much for your help.
Alex."Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166718883.058620.244010@.80g2000cwy.googlegroups.com...
> Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
> can see only one example of data shaping in MSDN). How do I calculate
> fields ? Starting with:
> . . ..
This data shaping syntax is an old, dead feature of ADO. Don't use it. In
.NET use multi-table DataSets, which are a much better solution for
client-side shaped data.
David|||Well, David, the thing is that I don't use dotnet - I have to write
some VBA code in Excel 2000 to show data in a hierarchical data grid,
so that the user can look at a list of workgroups with their details
(aggregate data at workgroup level), then click on a workgroup and
explode it so that the teams contained in that workgroup are seen, with
their details, and then clicks on one of the teams in the workgroup and
sees the contained team-members, with their details, kind of a treeview
(actually, a fancy datagrid). Since I don't have any 3rd party tools
available, I have to make it work with the default (MS) controls.
MSHFlexGrid is one of them, and it does exactly what the user needed
(show data in a tree-like structure).
So I should show:
root
-- list of workgroups, with agregate data (#hours worked, for
instance, by workgroup)
-- list of teams in current workgroup, with agregate data (#hours
worked by team)
-- list of workers in current team, with #hours worked by
worker
PS. All this data comes from some MSAccess tables.
Thank you very much.
David Browne wrote:
> "Radu" <cuca_macaii2000@.yahoo.com> wrote in message
> news:1166718883.058620.244010@.80g2000cwy.googlegroups.com...
> > Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
> > can see only one example of data shaping in MSDN). How do I calculate
> > fields ? Starting with:
> > . . ..
> This data shaping syntax is an old, dead feature of ADO. Don't use it. In
> .NET use multi-table DataSets, which are a much better solution for
> client-side shaped data.
> David|||"Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166727210.486584.307260@.i12g2000cwa.googlegroups.com...
> Well, David, the thing is that I don't use dotnet - I have to write
> some VBA code in Excel 2000 to show data in a hierarchical data grid,
> so that the user can look at a list of workgroups with their details
> (aggregate data at workgroup level), then click on a workgroup and
> explode it so that the teams contained in that workgroup are seen, with
> their details, and then clicks on one of the teams in the workgroup and
> sees the contained team-members, with their details, kind of a treeview
> (actually, a fancy datagrid). Since I don't have any 3rd party tools
> available, I have to make it work with the default (MS) controls.
> MSHFlexGrid is one of them, and it does exactly what the user needed
> (show data in a tree-like structure).
> So I should show:
> root
> -- list of workgroups, with agregate data (#hours worked, for
> instance, by workgroup)
> -- list of teams in current workgroup, with agregate data (#hours
> worked by team)
> -- list of workers in current team, with #hours worked by
> worker
> PS. All this data comes from some MSAccess tables.
>
SO first off, this is posted in a SQL Server group. Your solution doesn't
involve SQL Server. If it did, I'd advise you to create views in SQL Server
that add the appropriate columns to the resultset. Perhaps you can do the
same in access with saved queries, or subqueries.
David
>sql

Data Shaping Question (Syntax)

Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
can see only one example of data shaping in MSDN). How do I calculate
fields ? Starting with:
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
SomeNumber FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, ShipVia, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"RELATE CustomerID TO CustomerID)"
(from Northwind), I'd like to find out the syntax for sum-ming on, say,
ShipVia, (although it does not make any logical sense to sum on that
column). So, in other words, I'd like to add ***to the parent record***
(the one coming from Customers), a calculated column containing a sum
on the child (chapter), so for customer "ALFKI" I should add up 1, 2,
1, 3, 1, and 1, obtaining 9.
I can, of course, change it to
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ({SELECT CustomerID, OrderID, EmployeeID, OrderDate,
SUM(ShipVia) as SumOfShipVia FROM Orders " & _
"GROUP BY CustomerID, OrderID, EmployeeID, OrderDate} AS Orders
" & _
"RELATE CustomerID TO CustomerID)"
but then the SUM is not a sum, since it's still in the child chapter.
How do I show the sum in the parent record ?
Similar - This contains three levels: Customers, Orders, and
OrderDetails.
strSQL = "SHAPE {SELECT CustomerID, CompanyName, ContactName,
ContactTitle FROM Customers} AS Customers " & _
"APPEND ((SHAPE {SELECT CustomerID, OrderID, EmployeeID,
OrderDate FROM Orders} AS Orders " & _
"APPEND ({SELECT OrderID, ProductID, UnitPrice, Quantity FROM
[Order Details]} AS Orderdetails " & _
"RELATE OrderID TO OrderID) As OrderDetails) " & _
"RELATE CustomerID TO CustomerID)"
What if I wanted to add to the child (Orders) a calculated column on a
column in the grandchild, OrderDetails, say, OrdersDetails.UnitPrice ?
What would be the syntax then ?
Thank you very much for your help.
Alex."Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166718883.058620.244010@.80g2000cwy.googlegroups.com...
> Hi. I guess it's simple, but I think I'm really dumm... (and, on top, I
> can see only one example of data shaping in MSDN). How do I calculate
> fields ? Starting with:
> . . ..
This data shaping syntax is an old, dead feature of ADO. Don't use it. In
.NET use multi-table DataSets, which are a much better solution for
client-side shaped data.
David|||Well, David, the thing is that I don't use dotnet - I have to write
some VBA code in Excel 2000 to show data in a hierarchical data grid,
so that the user can look at a list of workgroups with their details
(aggregate data at workgroup level), then click on a workgroup and
explode it so that the teams contained in that workgroup are seen, with
their details, and then clicks on one of the teams in the workgroup and
sees the contained team-members, with their details, kind of a treeview
(actually, a fancy datagrid). Since I don't have any 3rd party tools
available, I have to make it work with the default (MS) controls.
MSHFlexGrid is one of them, and it does exactly what the user needed
(show data in a tree-like structure).
So I should show:
root
-- list of workgroups, with agregate data (#hours worked, for
instance, by workgroup)
-- list of teams in current workgroup, with agregate data (#hours
worked by team)
-- list of workers in current team, with #hours worked by
worker
PS. All this data comes from some MSAccess tables.
Thank you very much.
David Browne wrote:
> "Radu" <cuca_macaii2000@.yahoo.com> wrote in message
> news:1166718883.058620.244010@.80g2000cwy.googlegroups.com...
> This data shaping syntax is an old, dead feature of ADO. Don't use it. I
n
> .NET use multi-table DataSets, which are a much better solution for
> client-side shaped data.
> David|||"Radu" <cuca_macaii2000@.yahoo.com> wrote in message
news:1166727210.486584.307260@.i12g2000cwa.googlegroups.com...
> Well, David, the thing is that I don't use dotnet - I have to write
> some VBA code in Excel 2000 to show data in a hierarchical data grid,
> so that the user can look at a list of workgroups with their details
> (aggregate data at workgroup level), then click on a workgroup and
> explode it so that the teams contained in that workgroup are seen, with
> their details, and then clicks on one of the teams in the workgroup and
> sees the contained team-members, with their details, kind of a treeview
> (actually, a fancy datagrid). Since I don't have any 3rd party tools
> available, I have to make it work with the default (MS) controls.
> MSHFlexGrid is one of them, and it does exactly what the user needed
> (show data in a tree-like structure).
> So I should show:
> root
> -- list of workgroups, with agregate data (#hours worked, for
> instance, by workgroup)
> -- list of teams in current workgroup, with agregate data (#hours
> worked by team)
> -- list of workers in current team, with #hours worked by
> worker
> PS. All this data comes from some MSAccess tables.
>
SO first off, this is posted in a SQL Server group. Your solution doesn't
involve SQL Server. If it did, I'd advise you to create views in SQL Server
that add the appropriate columns to the resultset. Perhaps you can do the
same in access with saved queries, or subqueries.
David
>

data set reuse in the same report

Is there a way to reuse a data set in a report?
Example:
report has 2 data sets, ds1 and ds2.
ds1 calls a sproc that returns the data.
ds2 use the same data but filters it and displays it in a different
way.
the queries that generate the data are long (over 1 hour) and I don't
want to call the sproc again to generate the same data for ds2.
is there a way to reuse the data from ds1?
thanksOn Apr 10, 11:03 am, "jlemm" <beasyou...@.gmail.com> wrote:
> Is there a way to reuse a data set in a report?
> Example:
> report has 2 data sets, ds1 and ds2.
> ds1 calls a sproc that returns the data.
> ds2 use the same data but filters it and displays it in a different
> way.
> the queries that generate the data are long (over 1 hour) and I don't
> want to call the sproc again to generate the same data for ds2.
> is there a way to reuse the data from ds1?
> thanks
You should be able to reference the data from ds1 via something (an
expression) like this (if using a textbox control):
=Sum(Fields!SomeFieldName.Value, "ds1")
You should be able to reuse the dataset as well in another table
control (via selecting ds1 as part of the general properties), etc;
however, as far as, not re-executing it, I can't say. I would suggest
trying to improve the performance of your query as well (via the Data
Engine Tuning Advisor, SQL Profiler, etc). Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Data region in RS allows only 1 data source - virtual cube solutions?

I have a report requiring two different data sources be compared in one
data region. For example, I'm comparing a measure for a client against
a national average. The client measure comes from one cube and the
national average is stored in a sql database. RS won't allow two data
sources to be used in a data region unless you can use an aggregate
function on the second data source. I attempted to solve this by
creating a cube for the second data source (national averages) and then
a virtual cube to make one data source (i.e. one MDX statement from the
virtual cube per data region) for RS. The orginal MDX used for the
first data source required slicing by dimensions not included in the
national average cube and visa versa. I have been able to get the
correct answer for each cube before combining into the virtual cube,
but I can not get the measures correctly in the virtual if, for each
measure, I need to slice by a dimension that is not shared. I haven't
been able to find the information anywhere (and I've tried internet and
2 books), but because my measures are not correct I assume that a
virtual cube in only useful if the correct results are obtained by
slicing into only the shared dimensions? Are there any other solutions
to the rediculous RS limitation of only being able to provide one data
source to a data region? What we they thinking'oops, should have used spell check before submitting. it is a
ridiculous RS limitation.|||Hi Carrie,
I believe you can refer to more than one datasource from a data region such
as a table. Try putting in two data sources on a report and drag data from
them both into a table (note, you have to set the tables data source to one
data set).
see how that goes
<cwilliams@.choosehmc.com> wrote in message
news:1104767162.584780.225260@.z14g2000cwz.googlegroups.com...
> I have a report requiring two different data sources be compared in one
> data region. For example, I'm comparing a measure for a client against
> a national average. The client measure comes from one cube and the
> national average is stored in a sql database. RS won't allow two data
> sources to be used in a data region unless you can use an aggregate
> function on the second data source. I attempted to solve this by
> creating a cube for the second data source (national averages) and then
> a virtual cube to make one data source (i.e. one MDX statement from the
> virtual cube per data region) for RS. The orginal MDX used for the
> first data source required slicing by dimensions not included in the
> national average cube and visa versa. I have been able to get the
> correct answer for each cube before combining into the virtual cube,
> but I can not get the measures correctly in the virtual if, for each
> measure, I need to slice by a dimension that is not shared. I haven't
> been able to find the information anywhere (and I've tried internet and
> 2 books), but because my measures are not correct I assume that a
> virtual cube in only useful if the correct results are obtained by
> slicing into only the shared dimensions? Are there any other solutions
> to the rediculous RS limitation of only being able to provide one data
> source to a data region? What we they thinking'
>|||The answer is to create a calculated member using the ValidMeasure
function for each measure in the virtual cube. Just in case anyone is
interested...sql

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 lost after INSERT command

I have a website that is used to enter dates from a calendar. For example, user A might go to the website and select 20 different days in January. When user A clicks on the Save button the dates are written to the database with an INSERT command. This works fine. However, when user B then goes through the same procedure, say selecting 5 dates that are in the set selected by user A, those dates are properly saved in the database but the rows containing user A's same 5 dates disappear. I suspect this is something simple but I'm new and ignorant. Please help.Are you sure that when User B selects dates that are in the set selected by User A you are not using an Update statement instead of an Insert statement?|||It would be very helpful if you would post the queries involved, as well as the table DDL.|||Thank you for taking the time to respond. I have a bit of code where I do a DELETE prior to doing the INSERT. Your question led me to examine my DELETE statement again, only to discover that I had made it too broad so that it was deleting all records having a particular date, not just the one of the user currently logged in. I will adopt the suggestion to include code in future postings. Thanks again.

data from tables not likned

Hi ;
Can some please tell me how to retrieve data from a table which is not linked For example i want total sale for aperiod to get the sale percentage of respective departments with respect to total sale or is there a way to call a sectionHow did the tables relate to each other? If you are not able to join, make use of sqlquery property and write that as sub query