"Jeffrey K. Bray" <software@headshoppe.ca> wrote in message
news:#Xwlv5ucDHA.2384@TK2MSFTNGP09.phx.gbl...
> Hello everybody,
> Sorry for the crosspost, but I think I picked the right groups. Also, I
> apologize in advance for the length, but want to maximize the amount of
> information you have before you attempt helping me.
>
> I am new to OLAP & replication, familiar with DTS (although have poor luck
> with recurring packages), and a veteran of SQL Server.
>
> Here is my setup: I have a proprietary POS system that collects FoxPro
> DBF/CDX files for each of our stores. The key thing to remember about
these
> FoxPro tables is that there is 25 *separate* directories of the same set
of
> tables, one for each of our stores. In other words, no single table has a
> column/field/reference to which store it belongs to; the only way I know
is
> by which directory they sit in. We do not have FoxPro and have no
intention
> to get it. We also have SQL Server 2000 as our primary database server
which
> I am very comfortable with and enjoy using. My goal is to use the data
> integration tools of SQL Server to help me access this FoxPro data for
data
> analysis purposes. For further clarification, the FoxPro data changes once
> per day.
>
> What I've tried so far:
>
> I have created 25 "linked servers" under the SQL Enterprise manager, using
> the OLE DB provider for ODBC drivers, pointing to 25 Microsoft Visual
FoxPro
> DSN's I have created pointing to the "open table directories" of all the
DBF
> files. In other words, I have one linked server for every store with
which
> I can query any table.
>
> This works fine when I want to query a single store, but this is rare.
> Usually, we run consolidated reports to analyze sales data across all of
our
> stores. To do this, I write a huge SQL query in my SQL Query Analyser
using
> OPENQUERY to query these 25 linked servers and I either UNION the results
> together or I insert results from each store-query into an SQL Server
> results table, then query that table for final presentation of the data.
> Something like this:
>
> SELECT *
> FROM OPENQUERY([STORE2],'SELECT * FROM receipthistory')
>
> The fact is, this is really really really slow. It's also cumbersome to
> write queries since I have to embed a FoxPro-SQL query inside an T-SQL
> query, and I can't pass parameters the way I want to (for date ranges
etc),
> so I always end up querying more data than I need, blah blah blah. Short
> story: it sucks.
>
> My next step was to have a DTS package run that sewed together all of
these
> tables into destination tables with an extra "store" column for each table
> that would differentiate which store the data came from. My key problems
> with proceeding with this are:
>
> a) I can't seem to get scheduled DTS packages to run, they always fail (I
> don't have an error for you because I haven't tried it in a few months,
but
> I do the same thing that I do through import/export data, but I simply
check
> the box to schedule the job. I want to be sure this is the way to go
before
> potentially wasting time troubleshooting an error message). Manual ones
run
> fine.
>
> b) Since each running of the DTS package would constitute a full copy of
all
> data from the FoxPro DBF files to the SQL server database, I assume I need
> to find a way to empty the destination tables just before every execution
of
> the job, and I'm not sure how to do this. The alternative is to query the
> source data more carefully to basically tell it to only copy over records
> that don't already exist in the destination table set, but I'm not sure
how
> to do this either.
>
> c) I would have to create separate jobs for each of the 25 stores and
> probably schedule them 10-minutes apart (or else it'll kill the server)
from
> one another throughout the night, effecitively keeping my server
incredibly
> busy each night
>
> d) I am making redundant a HUGE amount of data (tens of thousands of
> transactions PER STORE ; the data spans many years). Not only is it in the
> DBF files as well as on the SQL server, but I have exploded my transaction
> log each and every day with a huge mass copy of data. Seems like such a
> waste.
>
> I then turned to replication. Could replication somehow make it easier to
> get the data into SQL Server tables? I am totally unfamiliar with
> replication but it seemed to be geared toward SQL Server-to-SQL Server
> replication only, which made sense, and I doubt I'd have the control to
tag
> the data with store codes; it'd most likely be a literal exact copy.
>
> I then turned to OLAP which seemed most promising. I can easily point it
to
> the sam 25 DSN's for my stores and have done so. However, it keeps asking
me
> for a fact table. I understand that a fact table would basically operate
> like a "join" table, to ensure linkages to all other tables. The problem
> that I'm struggling with now is a simple database design issue; How can I
> take data that is clearly not in 3NF since it is separated into different
> tables and represent it in a fact table? From what I gather, I would make
> "store" a dimension of my cube, but I am very unsure of how my fact table
> should look. So basically, I would have some tables (history, booking,
> clients, staff) multiplied by 25 stores. What would a fact table look
like?
>
> I've kind of reached the end of the road as far as willingness to explore
> forward without more information. The thought occured to me that this must
> be done all the time by data integration experts, and I should just ask
them
> instead of beating my head against the wall. Does anybody have any
> suggestions for me?
>
> MANY thanks in advance,
>
> Jeff Bray
>
>

"Hilary Cotter" <hilaryk@worldnet.att.net> wrote in message
news:3OR5b.129217$3o3.9083372@bgtnsc05-news.ops.worldnet.att.net...
> The problem with using replication to implement a solution is that the
data
> changes on the FoxPro files and SQL Server has no way of knowing what has
> changed.
>
> For instance SQL Server tracks changes to its tables by using triggers
> (Merge Replication) or reading the log (Transactional replication). You
> don't have the luxury of such change tracking with FoxPro - AFAIK. If
> you're able to track these changes you are well on your way to a solution.
>
> What you could do is do some tracking on the FoxPro end and then send the
> data over to a central table in SQL. Ideally you would have an archive
> column set on each table which would be 1 if the row was changed/inserted
> and then 0 copied to SQL Server.
>
>
>
> "Jeffrey K. Bray" <software@headshoppe.ca> wrote in message
> news:#Xwlv5ucDHA.2384@TK2MSFTNGP09.phx.gbl...
> > Hello everybody,
> > Sorry for the crosspost, but I think I picked the right groups. Also, I
> > apologize in advance for the length, but want to maximize the amount of
> > information you have before you attempt helping me.
> >
> > I am new to OLAP & replication, familiar with DTS (although have poor
luck
> > with recurring packages), and a veteran of SQL Server.
> >
> > Here is my setup: I have a proprietary POS system that collects FoxPro
> > DBF/CDX files for each of our stores. The key thing to remember about
> these
> > FoxPro tables is that there is 25 *separate* directories of the same set
> of
> > tables, one for each of our stores. In other words, no single table has
a
> > column/field/reference to which store it belongs to; the only way I know
> is
> > by which directory they sit in. We do not have FoxPro and have no
> intention
> > to get it. We also have SQL Server 2000 as our primary database server
> which
> > I am very comfortable with and enjoy using. My goal is to use the data
> > integration tools of SQL Server to help me access this FoxPro data for
> data
> > analysis purposes. For further clarification, the FoxPro data changes
once
> > per day.
> >
> > What I've tried so far:
> >
> > I have created 25 "linked servers" under the SQL Enterprise manager,
using
> > the OLE DB provider for ODBC drivers, pointing to 25 Microsoft Visual
> FoxPro
> > DSN's I have created pointing to the "open table directories" of all the
> DBF
> > files. In other words, I have one linked server for every store with
> which
> > I can query any table.
> >
> > This works fine when I want to query a single store, but this is rare.
> > Usually, we run consolidated reports to analyze sales data across all of
> our
> > stores. To do this, I write a huge SQL query in my SQL Query Analyser
> using
> > OPENQUERY to query these 25 linked servers and I either UNION the
results
> > together or I insert results from each store-query into an SQL Server
> > results table, then query that table for final presentation of the data.
> > Something like this:
> >
> > SELECT *
> > FROM OPENQUERY([STORE2],'SELECT * FROM receipthistory')
> >
> > The fact is, this is really really really slow. It's also cumbersome to
> > write queries since I have to embed a FoxPro-SQL query inside an T-SQL
> > query, and I can't pass parameters the way I want to (for date ranges
> etc),
> > so I always end up querying more data than I need, blah blah blah. Short
> > story: it sucks.
> >
> > My next step was to have a DTS package run that sewed together all of
> these
> > tables into destination tables with an extra "store" column for each
table
> > that would differentiate which store the data came from. My key problems
> > with proceeding with this are:
> >
> > a) I can't seem to get scheduled DTS packages to run, they always fail
(I
> > don't have an error for you because I haven't tried it in a few months,
> but
> > I do the same thing that I do through import/export data, but I simply
> check
> > the box to schedule the job. I want to be sure this is the way to go
> before
> > potentially wasting time troubleshooting an error message). Manual ones
> run
> > fine.
> >
> > b) Since each running of the DTS package would constitute a full copy of
> all
> > data from the FoxPro DBF files to the SQL server database, I assume I
need
> > to find a way to empty the destination tables just before every
execution
> of
> > the job, and I'm not sure how to do this. The alternative is to query
the
> > source data more carefully to basically tell it to only copy over
records
> > that don't already exist in the destination table set, but I'm not sure
> how
> > to do this either.
> >
> > c) I would have to create separate jobs for each of the 25 stores and
> > probably schedule them 10-minutes apart (or else it'll kill the server)
> from
> > one another throughout the night, effecitively keeping my server
> incredibly
> > busy each night
> >
> > d) I am making redundant a HUGE amount of data (tens of thousands of
> > transactions PER STORE ; the data spans many years). Not only is it in
the
> > DBF files as well as on the SQL server, but I have exploded my
transaction
> > log each and every day with a huge mass copy of data. Seems like such a
> > waste.
> >
> > I then turned to replication. Could replication somehow make it easier
to
> > get the data into SQL Server tables? I am totally unfamiliar with
> > replication but it seemed to be geared toward SQL Server-to-SQL Server
> > replication only, which made sense, and I doubt I'd have the control to
> tag
> > the data with store codes; it'd most likely be a literal exact copy.
> >
> > I then turned to OLAP which seemed most promising. I can easily point it
> to
> > the sam 25 DSN's for my stores and have done so. However, it keeps
asking
> me
> > for a fact table. I understand that a fact table would basically operate
> > like a "join" table, to ensure linkages to all other tables. The problem
> > that I'm struggling with now is a simple database design issue; How can
I
> > take data that is clearly not in 3NF since it is separated into
different
> > tables and represent it in a fact table? From what I gather, I would
make
> > "store" a dimension of my cube, but I am very unsure of how my fact
table
> > should look. So basically, I would have some tables (history, booking,
> > clients, staff) multiplied by 25 stores. What would a fact table look
> like?
> >