When we were on version 6, I used SQL Server to pull key tables over from Vantage each night.
It took a few hours, but having Access front ends and its rich reporting along with SQL Server and the power and blistering (compared with Vantage) speed of the stored procedures, was worth it. I was able to do very complex labor reporting that was much simpler to develop in Access. Things I could never have done with REport Builder, Crystal or any dashboard.
Also, keep in mind the following, I say that the data transfer took a few hours, well, the SQL server 7.5 was a Pentium II, 758MB RAM and the Vantage server was Pentium III with 1.2 GB RAM.
Also, you don't need to pull everything in to SQL Server, leave out the custom fields or fields you know you will never need.
________________________________
From: Thomas Rose <t.rose@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Sent: Wednesday, February 4, 2009 8:51:52 AM
Subject: [Vantage] Data Download Strategy
I have some reports I run based on data from Vista/Vantage. Because I cannot get the required complexity of the data relationships from a BAQ, I export the data to Access and create my report there. I have tried using ODBC to link to the data directly. However, the last two times we had the system lock up, I was running reports based on ODBC links. I'm guessing that is not a coincidence.
So, I do BAQ exports for the data I need, and then read the resulting csv files into my Access database. This works okay for now (we have only been on the system for a few months), but I am worried that as I get a few years worth of data, these csv files are going to become too large. The downloaded data includes the PartTran and the LaborDtl tables, so there will be a lot of data someday. I am considering some alternatives:
1. Keep doing what I have been doing. I am worried about the size of the csv files, though.
2. Only download recent data. Have the Access database maintain its own data tables that are updated with the recent Vista/Vantage data. The downside here is that I am now keeping redundant data. Also, if for any reason older data in Vista/Vantage changes, that will not be reflected in the Access database.
3. Same as option 2, except store the downloaded data in a SQL Server back end, using Access for the front end interface. I'm thinking the SQL Server data would be a bit more robust than the Access data, but the data is still redundant and may not be current.
My questions are, a) which alternative is best, or b) is there another better alternative?
Thom Rose
Controller
Electric Mirror LLC
HOTEL LUXURY
"The World Leader in Back-lit Mirrors & Mirror TV Technology"
T 425 776-4946
F 425 491-8200
A 11831 Beverly Park Rd, Bldg D, Everett, WA 98204 USA
www.electricmirror. com
Note: The information contained in the e-mail, including any attachments, is legally privileged and confidential. If you are not the intended recipient you are hereby notified that any reading, use or dissemination of this message is strictly prohibited. If you have received this message in error, please immediately notify us by telephone at 425-776-4946 and delete this message from your system. Even though this e-mail and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free, and no responsibility is accepted by Electric Mirror LLC for any loss or damage arising in any way from its use
[Non-text portions of this message have been removed]
It took a few hours, but having Access front ends and its rich reporting along with SQL Server and the power and blistering (compared with Vantage) speed of the stored procedures, was worth it. I was able to do very complex labor reporting that was much simpler to develop in Access. Things I could never have done with REport Builder, Crystal or any dashboard.
Also, keep in mind the following, I say that the data transfer took a few hours, well, the SQL server 7.5 was a Pentium II, 758MB RAM and the Vantage server was Pentium III with 1.2 GB RAM.
Also, you don't need to pull everything in to SQL Server, leave out the custom fields or fields you know you will never need.
________________________________
From: Thomas Rose <t.rose@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Sent: Wednesday, February 4, 2009 8:51:52 AM
Subject: [Vantage] Data Download Strategy
I have some reports I run based on data from Vista/Vantage. Because I cannot get the required complexity of the data relationships from a BAQ, I export the data to Access and create my report there. I have tried using ODBC to link to the data directly. However, the last two times we had the system lock up, I was running reports based on ODBC links. I'm guessing that is not a coincidence.
So, I do BAQ exports for the data I need, and then read the resulting csv files into my Access database. This works okay for now (we have only been on the system for a few months), but I am worried that as I get a few years worth of data, these csv files are going to become too large. The downloaded data includes the PartTran and the LaborDtl tables, so there will be a lot of data someday. I am considering some alternatives:
1. Keep doing what I have been doing. I am worried about the size of the csv files, though.
2. Only download recent data. Have the Access database maintain its own data tables that are updated with the recent Vista/Vantage data. The downside here is that I am now keeping redundant data. Also, if for any reason older data in Vista/Vantage changes, that will not be reflected in the Access database.
3. Same as option 2, except store the downloaded data in a SQL Server back end, using Access for the front end interface. I'm thinking the SQL Server data would be a bit more robust than the Access data, but the data is still redundant and may not be current.
My questions are, a) which alternative is best, or b) is there another better alternative?
Thom Rose
Controller
Electric Mirror LLC
HOTEL LUXURY
"The World Leader in Back-lit Mirrors & Mirror TV Technology"
T 425 776-4946
F 425 491-8200
A 11831 Beverly Park Rd, Bldg D, Everett, WA 98204 USA
www.electricmirror. com
Note: The information contained in the e-mail, including any attachments, is legally privileged and confidential. If you are not the intended recipient you are hereby notified that any reading, use or dissemination of this message is strictly prohibited. If you have received this message in error, please immediately notify us by telephone at 425-776-4946 and delete this message from your system. Even though this e-mail and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free, and no responsibility is accepted by Electric Mirror LLC for any loss or damage arising in any way from its use
[Non-text portions of this message have been removed]