Showing posts with label system. Show all posts
Showing posts with label system. Show all posts

Sunday, March 25, 2012

Data security/filtering on field values

My team is developing a new software system that is replacing several
existing applications. The applications being replaced focus on billing and
customer relationship management.
Our company is an international corporation that serves a large number of
customers (hundreds of thousands) spread throughout the US and Canada. For
administrative and accounting reasons, we have divided the customers into
groups based on geographic region (Eastern, Central, and Western US, Canada)
.
Customers are also grouped within their Region into Service Areas.
Our new system requires that authorization be performed based on both the
roles of the user (basic RBAC) and on the set of data that the user is
authorized to view and manipulate.
For example, a customer service rep for the West region of the country
should only be able to see and work with customers that are located in the
West region – to this user, the rest of our customers are irrelevant. The
system should behave as though the only customers that exist are those
located in the region with which they work.
In addition, we need to set up non-geographic data filters, such as the
customer classification (similar to SIC code) and value fields such as
Salesperson.
The filtering of data must allow for both unions (“Or”) and intersection
s
(“And”) between different security settings; for instance, we must be ab
le to
support the following overlapping sets of data filters:
1) All customers in the “East” region and all customers with the
classification “Hospital”
2) All “East Hospital” customers (but no Hospitals outside of the East
region or East customers that are not Hospitals)
3) All customers in either the “East” or “West” regions, regardless
of
classification
We also need to support the concept of “Data Restrictions”, where the
default access to customers with a specific attribute is False unless a gran
t
is assigned.
We need a solution, whether it is a proven architectural approach or a
third-party technology, which will allow us to apply the defined data filter
s
at the lowest level of our application as possible with as little performanc
e
impact as possible. We would prefer to implement something in the database,
but if necessary we could implement it in the data access layer of our
software.
The solution should allow flexible definition of ‘securable attributes’
of a
customer, so that if an additional attributes are defined in the future we
don’t need to modify the database structure or recompile any code.
Questions:
1) Is there a standard or best-practice architectural approach to applying
the sort of security/filtering described above?
2) Are there any third-party tools or technologies that we could purchase
that would provide some or all of this functionality?
3) What sorts of approaches have been used before to address this type of
security requirement?
Thanks in advance,
GuyAs I flew above your text, that thing came in mind for me:
http://vyaskn.tripod.com/ row_level...as
es.htm
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Guy" <Guy@.discussions.microsoft.com> schrieb im Newsbeitrag
news:B4BD9CCC-1B6C-4B49-BEFA-49551E7F6167@.microsoft.com...
> My team is developing a new software system that is replacing several
> existing applications. The applications being replaced focus on billing
> and
> customer relationship management.
> Our company is an international corporation that serves a large number of
> customers (hundreds of thousands) spread throughout the US and Canada.
> For
> administrative and accounting reasons, we have divided the customers into
> groups based on geographic region (Eastern, Central, and Western US,
> Canada).
> Customers are also grouped within their Region into Service Areas.
> Our new system requires that authorization be performed based on both the
> roles of the user (basic RBAC) and on the set of data that the user is
> authorized to view and manipulate.
> For example, a customer service rep for the West region of the country
> should only be able to see and work with customers that are located in the
> West region - to this user, the rest of our customers are irrelevant. The
> system should behave as though the only customers that exist are those
> located in the region with which they work.
> In addition, we need to set up non-geographic data filters, such as the
> customer classification (similar to SIC code) and value fields such as
> Salesperson.
> The filtering of data must allow for both unions ("Or") and intersections
> ("And") between different security settings; for instance, we must be able
> to
> support the following overlapping sets of data filters:
> 1) All customers in the "East" region and all customers with the
> classification "Hospital"
> 2) All "East Hospital" customers (but no Hospitals outside of the East
> region or East customers that are not Hospitals)
> 3) All customers in either the "East" or "West" regions, regardless of
> classification
> We also need to support the concept of "Data Restrictions", where the
> default access to customers with a specific attribute is False unless a
> grant
> is assigned.
> We need a solution, whether it is a proven architectural approach or a
> third-party technology, which will allow us to apply the defined data
> filters
> at the lowest level of our application as possible with as little
> performance
> impact as possible. We would prefer to implement something in the
> database,
> but if necessary we could implement it in the data access layer of our
> software.
> The solution should allow flexible definition of 'securable attributes' of
> a
> customer, so that if an additional attributes are defined in the future we
> don't need to modify the database structure or recompile any code.
> Questions:
> 1) Is there a standard or best-practice architectural approach to applying
> the sort of security/filtering described above?
> 2) Are there any third-party tools or technologies that we could purchase
> that would provide some or all of this functionality?
> 3) What sorts of approaches have been used before to address this type of
> security requirement?
> Thanks in advance,
> Guysql

Thursday, March 22, 2012

Data save failed

When I am saving a record thru Great Plains, the system dont save data and an
error message is appearing on screen which says "Save operation on table
"<Table Name>" failed accessing SQL Data. The dbo is trying to write the data
on back group.
Anybody can suggest a way forward.
Thanks
Huaim
Hi
Contact Greap Plains PSS and see what they say. That is not a SQL Server
error message.
Cheers
Mike
"Huaim" wrote:

> When I am saving a record thru Great Plains, the system dont save data and an
> error message is appearing on screen which says "Save operation on table
> "<Table Name>" failed accessing SQL Data. The dbo is trying to write the data
> on back group.
> Anybody can suggest a way forward.
> Thanks
> Huaim

Data save failed

When I am saving a record thru Great Plains, the system dont save data and an
error message is appearing on screen which says "Save operation on table
"<Table Name>" failed accessing SQL Data. The dbo is trying to write the data
on back group.
Anybody can suggest a way forward.
Thanks
HuaimHi
Contact Greap Plains PSS and see what they say. That is not a SQL Server
error message.
Cheers
Mike
"Huaim" wrote:
> When I am saving a record thru Great Plains, the system dont save data and an
> error message is appearing on screen which says "Save operation on table
> "<Table Name>" failed accessing SQL Data. The dbo is trying to write the data
> on back group.
> Anybody can suggest a way forward.
> Thanks
> Huaim

Data save failed

When I am saving a record thru Great Plains, the system dont save data and a
n
error message is appearing on screen which says "Save operation on table
"<Table Name>" failed accessing SQL Data. The dbo is trying to write the dat
a
on back group.
Anybody can suggest a way forward.
Thanks
HuaimHi
Contact Greap Plains PSS and see what they say. That is not a SQL Server
error message.
Cheers
Mike
"Huaim" wrote:

> When I am saving a record thru Great Plains, the system dont save data and
an
> error message is appearing on screen which says "Save operation on table
> "<Table Name>" failed accessing SQL Data. The dbo is trying to write the d
ata
> on back group.
> Anybody can suggest a way forward.
> Thanks
> Huaim

Sunday, March 11, 2012

Data Modeling: Managing a group of events, both Special & Regular.

Scenario: The system needs to manage events by group. There is no hierachica
l
concept in the events. Special events have different attributes that need to
be treated differently. Note that the DeviceID has a many-to-one relationshi
p
with a LocationID (not in the model). Not seen in the model also include
PersonRole (PersonID, PersonRoleType, RoleTypeStatus, StartDate, EndDate)
Eventually the system will need to report all the related events for a
special event.
Problems: (1) Standard events has no way to tell a related event unless
linking through RelatedEventGroup. (2) An Event can have zero,one
EventManager (ManagerID not yet added) but a special event must have only on
e
Event Manager. What would be a better solution to implement these? (3) Pleas
e
comment (any foreseeable problems?) (4) If I add a nullable RelatedEventID i
n
SpecialEvent, what would be the advantages/divantages?
Before involving all types of person (Participant and other roles) and any
location(s) in the data model, the drafted database design is as the
following:
CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[TypeCode] [char](4) NOT NULL,
[StatusCode] [char](2) NOT NULL,
[Name] [varchar](40) NULL,
[Description] [varchar](255) NULL,
[OwnerName] [varchar](40) NULL,
[ActualStartDateTimestamp] [datetime] NULL,
[ActualEndDateTimestamp] [datetime] NULL,
[PlanStartDateTimestamp] [datetime] NULL,
[PlanEndDateTimestamp] [datetime] NULL,
CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
)
GO
CREATE TABLE [dbo].[RelatedEventGroup](
[EventID] [int] NOT NULL,
[RelatedEventID] [int] NOT NULL,
[Note] [varchar](255) NULL,
CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
(
[EventID] ASC,
[RelatedEventID] ASC
)
)
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_Event_EventID]
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
GO
CREATE TABLE [dbo].[SpecialEvent](
[TypeCode] [char](2) NOT NULL,
[StandardEventID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](255) NOT NULL,
[DeviceID] [int] NOT NULL,
[ManagerID] [int] NOT NULL,
CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
(
[TypeCode] ASC,
[StandardEventID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
[FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
REFERENCES [dbo].[Event] ( [EventID])Hi CTO,
1. I question the basic structure of the SpecialEvents Table. Are there
Multiple "Special Events" for any single Ordinary Event? If not then the
Special Event Table Primary Key should be the same as the Event Table Primar
y
Key, and it should be a one-to-one relationship, not a many-to-one
relationship. So, what's up with "TypeCode" in the SpecialEvents Table? If
there can be multiple Rows in this table witht he same SpecialEventID, but
with different "TypeCode" values, then this table, as structured, is NOT a
table of SpecialEvents, it's something else, (what I don't know) and should
be renamed, and a real specialEvents table should be added.
2. Please clarify what you mean by "Related Events". Do the groups of
Relate dEvents Overlap? i.e., could Event A be related to EventB and Event
B
be related to EventC, but EventC NOT be related to EventA? like for example
cousins? Bob could be Janes Cousin, and Jane could be Sally's Cousin, even
though Bob and SAlly are unrelated... Or,
Are all the "Groups" formed by the relations non-overlapping distinct
groups?, as for eample Citys and States, or Football teams and Leagues, etc.
If it's the latter then the more appropriate way to "relate" the events, is
to just adda groupID in the events table, and make the value the same for
all the events in the group... If there's some data attribute that is
associated with the group, and not with teach individual event, then add an
EventGroup Table that just has GroupID (as PK) and those Group-specific Data
attributes as additional columns...
hth,
Charly
"C TO" wrote:

> Scenario: The system needs to manage events by group. There is no hierachi
cal
> concept in the events. Special events have different attributes that need
to
> be treated differently. Note that the DeviceID has a many-to-one relations
hip
> with a LocationID (not in the model). Not seen in the model also include
> PersonRole (PersonID, PersonRoleType, RoleTypeStatus, StartDate, EndDate)
> Eventually the system will need to report all the related events for a
> special event.
> Problems: (1) Standard events has no way to tell a related event unless
> linking through RelatedEventGroup. (2) An Event can have zero,one
> EventManager (ManagerID not yet added) but a special event must have only
one
> Event Manager. What would be a better solution to implement these? (3) Ple
ase
> comment (any foreseeable problems?) (4) If I add a nullable RelatedEventID
in
> SpecialEvent, what would be the advantages/divantages?
> Before involving all types of person (Participant and other roles) and any
> location(s) in the data model, the drafted database design is as the
> following:
>
> CREATE TABLE [dbo].[Event](
> [EventID] [int] IDENTITY(1,1) NOT NULL,
> [TypeCode] [char](4) NOT NULL,
> [StatusCode] [char](2) NOT NULL,
> [Name] [varchar](40) NULL,
> [Description] [varchar](255) NULL,
> [OwnerName] [varchar](40) NULL,
> [ActualStartDateTimestamp] [datetime] NULL,
> [ActualEndDateTimestamp] [datetime] NULL,
> [PlanStartDateTimestamp] [datetime] NULL,
> [PlanEndDateTimestamp] [datetime] NULL,
> CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC
> )
> )
> GO
> CREATE TABLE [dbo].[RelatedEventGroup](
> [EventID] [int] NOT NULL,
> [RelatedEventID] [int] NOT NULL,
> [Note] [varchar](255) NULL,
> CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC,
> [RelatedEventID] ASC
> )
> )
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID]
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
> GO
> CREATE TABLE [dbo].[SpecialEvent](
> [TypeCode] [char](2) NOT NULL,
> [StandardEventID] [int] NOT NULL,
> [Name] [varchar](50) NOT NULL,
> [Description] [varchar](255) NOT NULL,
> [DeviceID] [int] NOT NULL,
> [ManagerID] [int] NOT NULL,
> CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
> (
> [TypeCode] ASC,
> [StandardEventID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
> [FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
> REFERENCES [dbo].[Event] ( [EventID])
>

Data Modeling: manage events by groups

Scenario: The system needs to manage events by groups. There is no
hierachical concept in the events. Special events have different attributes
that need to be treated differently so I create a separate entity. Note that
the DeviceID has a many-to-one relationship with a LocationID (not in the
model). Not seen in the model also include PersonRole (PersonID,
PersonRoleType, RoleTypeStatus, StartDate, EndDate). Eventually the system
will need to report all the related events for a special event.
Problems: (1) Standard events has no way to tell a related event unless
linking through RelatedEventGroup. (2) An Event can have zero,one
EventManager (ManagerID not yet added) but a special event must have only on
e
Event Manager. What would be a better solution to implement these? (3) Pleas
e
comment. (4) If I add a nullable RelatedEventID in SpecialEvent, what would
be the advantages/divantages?
Before involving all types of person (Participant and other roles) and any
location(s) in the data model, it is drafted as below.
CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[TypeCode] [char](4) NOT NULL,
[StatusCode] [char](2) NOT NULL,
[Name] [varchar](40) NULL,
[Description] [varchar](255) NULL,
[OwnerName] [varchar](40) NULL,
[ActualStartDateTimestamp] [datetime] NULL,
[ActualEndDateTimestamp] [datetime] NULL,
[PlanStartDateTimestamp] [datetime] NULL,
[PlanEndDateTimestamp] [datetime] NULL,
CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)
)
GO
CREATE TABLE [dbo].[RelatedEventGroup](
[EventID] [int] NOT NULL,
[RelatedEventID] [int] NOT NULL,
[Note] [varchar](255) NULL,
CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
(
[EventID] ASC,
[RelatedEventID] ASC
)
)
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_Event_EventID]
GO
ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
REFERENCES [dbo].[Event] ( [EventID])
GO
ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
[FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
GO
CREATE TABLE [dbo].[SpecialEvent](
[TypeCode] [char](2) NOT NULL,
[StandardEventID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Description] [varchar](255) NOT NULL,
[DeviceID] [int] NOT NULL,
[ManagerID] [int] NOT NULL,
CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
(
[TypeCode] ASC,
[StandardEventID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
[FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
REFERENCES [dbo].[Event] ( [EventID])You have already posted this exact same message. Someone has graciously
donated his/her own time to respond; show a little consideration and track
your own threads.
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:E0CEFAD5-B3B2-457E-8E54-346DFADFB8BB@.microsoft.com...
> Scenario: The system needs to manage events by groups. There is no
> hierachical concept in the events. Special events have different
attributes
> that need to be treated differently so I create a separate entity. Note
that
> the DeviceID has a many-to-one relationship with a LocationID (not in the
> model). Not seen in the model also include PersonRole (PersonID,
> PersonRoleType, RoleTypeStatus, StartDate, EndDate). Eventually the system
> will need to report all the related events for a special event.
> Problems: (1) Standard events has no way to tell a related event unless
> linking through RelatedEventGroup. (2) An Event can have zero,one
> EventManager (ManagerID not yet added) but a special event must have only
one
> Event Manager. What would be a better solution to implement these? (3)
Please
> comment. (4) If I add a nullable RelatedEventID in SpecialEvent, what
would
> be the advantages/divantages?
> Before involving all types of person (Participant and other roles) and any
> location(s) in the data model, it is drafted as below.
>
> CREATE TABLE [dbo].[Event](
> [EventID] [int] IDENTITY(1,1) NOT NULL,
> [TypeCode] [char](4) NOT NULL,
> [StatusCode] [char](2) NOT NULL,
> [Name] [varchar](40) NULL,
> [Description] [varchar](255) NULL,
> [OwnerName] [varchar](40) NULL,
> [ActualStartDateTimestamp] [datetime] NULL,
> [ActualEndDateTimestamp] [datetime] NULL,
> [PlanStartDateTimestamp] [datetime] NULL,
> [PlanEndDateTimestamp] [datetime] NULL,
> CONSTRAINT [PK_EVENTS] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC
> )
> )
> GO
> CREATE TABLE [dbo].[RelatedEventGroup](
> [EventID] [int] NOT NULL,
> [RelatedEventID] [int] NOT NULL,
> [Note] [varchar](255) NULL,
> CONSTRAINT [PK_RelatedEventGroup] PRIMARY KEY CLUSTERED
> (
> [EventID] ASC,
> [RelatedEventID] ASC
> )
> )
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID] FOREIGN KEY( [EventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_Event_EventID]
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] WITH NOCHECK ADD CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID] FOREIGN KEY( [RelatedEventID])
> REFERENCES [dbo].[Event] ( [EventID])
> GO
> ALTER TABLE [dbo].[RelatedEventGroup] CHECK CONSTRAINT
> [FK_RelatedEventGroup_EVENT_RelatedEvent
ID]
> GO
> CREATE TABLE [dbo].[SpecialEvent](
> [TypeCode] [char](2) NOT NULL,
> [StandardEventID] [int] NOT NULL,
> [Name] [varchar](50) NOT NULL,
> [Description] [varchar](255) NOT NULL,
> [DeviceID] [int] NOT NULL,
> [ManagerID] [int] NOT NULL,
> CONSTRAINT [PK_SpecialEvent] PRIMARY KEY CLUSTERED
> (
> [TypeCode] ASC,
> [StandardEventID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[SpecialEvent] WITH CHECK ADD CONSTRAINT
> [FK_SpecialEvent_EVENT] FOREIGN KEY( [StandardEventID])
> REFERENCES [dbo].[Event] ( [EventID])
>
>|||I apologize. I use Google Group to search with my Author name, obviously thi
s
thread did not return. I thought that this thread got lost somewhere.
"Scott Morris" wrote:

> You have already posted this exact same message. Someone has graciously
> donated his/her own time to respond; show a little consideration and track
> your own threads.
> "C TO" <CTO@.discussions.microsoft.com> wrote in message
> news:E0CEFAD5-B3B2-457E-8E54-346DFADFB8BB@.microsoft.com...
> attributes
> that
> one
> Please
> would
>
>|||Hello Scott, How to you track your own threads? Can you share it with us?
I posted lots of messages to different newsgroup? But sometimes, I forget to
follow it up...
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23DW6SrXUFHA.3436@.TK2MSFTNGP09.phx.gbl...
> You have already posted this exact same message. Someone has graciously
> donated his/her own time to respond; show a little consideration and track
> your own threads.
>|||Any moderately useful newsgroup reader will do this. I use the one that
comes with outlook.
"js" <js@.someone@.hotmail.com> wrote in message
news:ufq$iBYUFHA.3544@.TK2MSFTNGP10.phx.gbl...
> Hello Scott, How to you track your own threads? Can you share it with us?
>
> I posted lots of messages to different newsgroup? But sometimes, I forget
to
> follow it up...
>
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23DW6SrXUFHA.3436@.TK2MSFTNGP09.phx.gbl...
track
>|||is it outlook express?
"Scott Morris" <bogus@.bogus.com> wrote in message
news:OCMgvbYUFHA.1044@.TK2MSFTNGP10.phx.gbl...
> Any moderately useful newsgroup reader will do this. I use the one that
> comes with outlook.
> "js" <js@.someone@.hotmail.com> wrote in message
> news:ufq$iBYUFHA.3544@.TK2MSFTNGP10.phx.gbl...
> to
> track
>

Saturday, February 25, 2012

Data Migration Oracle 7.16 to SQL Server 2000

Hi,

I have been asked to look at optimising a Data Migration Strategy for one of our clients. I am quite new to Data Migration (I'm actually a system designer!).

The migration is from a legacy Oracle 7.16 Db to a SQL Server 2000 environment.

The legacy Db has some large tables - 30 Million+ rows

Table/column Mapping is 1:1

At present we are using DTS to import into the SQL environment.

My question is: Is there any way to leverage the Oracle 7.16 export to speed up the migration process?

Any advice would be appreciated.

Many thanks

Mike

this forum is for sql server 2005 integration services...not sql server 2000 data transformation services.

i suggest that you post your question to the microsoft dts newsgroup.

data migration

Dear All,
I'm having to manage data migration from a proprietary db system to an in-house designed sql server db.
Is there anyone out there who dealt with this and used software or services of companies specialising in this?
All ideas are welcome, thanks in advance!I would export the data as csv or bunch of insert statements and push them into the new db via bulk insert. thats the most db system independent way, I know and it only needs a dozen lines of code.|||The challenge is mapping data elements and logic between the two systems. And you will need to do custom coding for that. Probably a LOT of custom coding.
My advice would be to load the legacy data into staging table in your new database, and then draft sql procedures or scripts to transform it to your new schema. This way the process is repeatable if logical errors occur.
And my advice would be to get a competent DBA to help you with this and expect it to take a couple weeks to get it right.|||I've done something similar to this before.

Basically, what was needed then was to migrate all data from a proprietory db to MSSQL. Luckily, we were able to connect to that database through ODBC. We added that to MSSQL and from there wrote scripts to do the migration.

I agree with blindman, you may need a lot of coding to do this. We spent few weeks to do it cause almost everything (table structures, fields, etc) is different.

It's not difficult but you may need time to do it.

Friday, February 17, 2012

Data from two tables

I am developing a system that tracks phonecall usage. I have an sql server db that includes two tables which include (amongst others) the fields below them:

phone_emp_mast (contains user info.)
-------
Account_Code
First_Name
Last_Name
Department

detail (contains info on individual calls)
---
Account_Code
Duration

I want to write a query that returns the account_code, first_name, last_name, no. of calls made, avg. length of call, max. length of call and total minutes of calls for each employee in a given department

I tried the following query:

SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins

FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p

WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE;

and it keeps returning the error message:
Column p.last_name is invalid in the select list because it is not contained in either an aggregate function or the group by clause

Can anyone figure out a better way of doing this? please?The GROUP BY clause has to specify every column that was not aggregated in the SELECT clause - irritatingly!

SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall, SUM(d.DURATION) AS TotalMins

FROM DETAIL d CROSS JOIN
PHONE_EMP_MAST p

WHERE (d.ACCOUNT_CODE = d.ACCOUNT_CODE) AND (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME;|||Thanks for that, I have used the following select statement and it is working perfectly. However I now have a new problem...

SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;

I now need to get a count of the number of fields in the detail table where the duration is less than a minute... can anyone suggest a way of doing this. I tried putting in the bits between the stars

SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins,
***(SELECT COUNT(d.DURATION)
WHERE duration < 1) AS NoOfCallsLessThan1***
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'operations admin')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, ***d.DURATION, p.ACCOUNT_CODE***
ORDER BY p.LAST_NAME

but when I do this it returns all the calls individually since instead of only returning 1 row for each account code... anyone have any suggestions or is there anything you don't understand about my explanation?

Thanks in advance!|||You need to add a CASE (or DECODE if using Oracle pre-9i) expression in your first query:

SELECT d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME, COUNT(*) AS NoOfCalls, AVG(d.DURATION) AS AverageLength, MAX(d.DURATION) AS MaxCall,
SUM(d.DURATION) AS TotalMins,
SUM( CASE WHEN d.duration < 1 THEN 1 ELSE 0 END ) shortcalls
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d.ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE (p.DEPARTMENT = 'deptname')
GROUP BY d.ACCOUNT_CODE, p.LAST_NAME, p.FIRST_NAME
ORDER BY p.LAST_NAME;

The DECODE version would be:

SUM( DECODE( SIGN( d.duration-1), -1, 1, 0 ) ) shortcalls|||I'm actually using SQL server... do you know if SQL server supports this function?|||Originally posted by kieranodwyer
I'm actually using SQL server... do you know if SQL server supports this function?
Not DECODE - that's Oracle's own, but CASE is ANSI standard so I imagine that at least the latest version of SQL Server supports it.|||Thanks alot Tony... Thats working perfectly!