Tag Archives: sql

Data Backup For Disaster Recovery And Support

There are two main reasons you would want to backup your data: 1) to make a copy of your data in case the original data is lost and 2) to send in your data to CyberMatrix support when you encounter a difficult or hard to reproduce problem.

Disaster Recovery

Many companies forget that their company relies on software for day to day operations. This software usually stores important data. If this data is not safely backed up and a disastrous event like a fire, theft or even a hard drive crash occurs business operations can be seriously impacted.

Many companies will use specialized software to backup all important shared data. While these backup solutions tend to be very robust it is important not to become complacent. At least every month the backup should be inspected to verify that it is actually backing up all important data. There’s little worse than restoring a backup only to find that data from one important application was not included in the backup.

Also important is to attempt to store the backup files offsite. See: Disaster Prevention: Backup to the Cloud

Technical Support

Another important use for data backup is obtaining speedy application support. Complex software that uses a database to store large amounts of data is often virtually impossible to troubleshoot over the phone or via email when the software starts behaving improperly. To provide a solution to a technical problem, support technicians either have to have witnessed the problem before or they need to try to reproduce the problem themselves. If a problem can’t be reproduced chances are that either the customer left out an important detail or that the problem is data-related. By sending in the problem data, support resolution time can often be reduced dramatically.

All CyberMatrix non-client/server desktop database software has a backup solution included with it. To back up data in this software simply choose Records | Backup Data from the Data Manager menu. This will create a time-stamped file in the application’s Data\Backup folder. The created file is a zip archive file that can be opened in Windows. Customers with support issues can easily send in the file as an e-mail attachment.

For customers of CyberMatrix client/server software and web software, the data files will have to be manually zipped up and send to support. For newer software all files with the extension *.EDB* should be sent in. For older software all files ending in *.Dat, *.Idx and *.Blb should be sent in. (* is a wildcard that stands for any number of characters.)

With CyberMatrix enterprise software all data is stored in large database servers like Microsoft SQL Server, MySQL or PostgreSQL. Because the data files are not directly accessible they cannot be backed up as with local database software. One way to backup your data within the software is to export key table data in CSV or SQL format. The CSV or SQL files can then be stored somewhere safe. This approach can be problematic since not only is it more time consuming but CSV files cannot handle carriage returns and other odd characters in memo fields. Binary data such as images also cannot be saved in a CSV file which is really just a delimited text file. SQL files are better at handling such data but this depends on the database server used. So for enterprise software, the best mechanism for data backup is to use the replication features of the particular database server used.

How to Create a New Project Clock Report Using a Different Rate

custom reporting tool

custom reporting tool

One of the most common questions we get about our time tracking software is “why don’t the reports pick up the rate we’re using?” The problem is that each report uses specific instructions to pull data from the database. There is no simple way to tell a report which fields to use while still providing a report editing feature. What you can do instead though is use this report editing feature to create your own version of a report that uses the rate you want.

The technique is very similar in all our time tracking software but suppose you are a Project Clock. You like the Project Chart report but you want to use the Task rate instead of the Project rate. To edit that report, just click it and press the Edit button. This brings up the report designer. Now what you have to do is change the report’s database instructions or SQL. SQL is a language use to communicate with database.

To change the SQL choose Report | Variables from the menu. Then click on the SQL variable. You should see the reort’s SQL which looks like this:

Select P.Description AS ProjectName,Rate,SUM(CAST((EndTime-StartTime) SECOND AS INTEGER)/3600) As Hours From Projects P JOIN TimeHist T ON(P.ProjectID=T.ProjectID) GROUP By P.Description,rate Order By P.Description

Most basic SQL statements have four main parts. The SELECT statements states which fields you want to get. The FROM statement says from which tables to get the fields. If there are more than one table, each table must be joined to one other table by a common field (in this case the ProjectID field). The GROUP BY part states which fields are not being operated on (in this case summed). The ORDER BY statement indicates by which fields do you want the results sorted by.

So to use the task rate we need to link to the Tasks table using the field TaskID which is in both the Tasks table and the TimeHist table. We also must explicitly say which table to get the rate from. So we get SQL like this:

Select P.Description AS ProjectName, Ta.Rate,SUM(CAST((EndTime-StartTime) SECOND AS INTEGER)/3600) As Hours From Projects P JOIN TimeHist T ON(P.ProjectID=T.ProjectID) JOIN Tasks Ta ON(Ta.TaskID=T.TaskID) GROUP By P.Description,rate Order By P.Description

Notice that the Rate field is prefaced by Ta which is short form for the Tasks table. Press the green check mark icon to save changes to the SQL variable and return to the designer. Now choose File | Save as to save you new report with a descriptive name like Project Chart with Task Rate. Close the designer and press F5 to see your new report in the report list.

You can find out more about our time and billing products including Project Clock and Timesheets at cybermatrix.com.