How Much Data Are We Talking About?
When planning the deployment of a power modeling and forecasting tool in a corporate environment, one of the most important considerations prior to implementation is the size of the data that will be used. IT personnel want to know how much data they are going to be storing, maintaining, backing up, and archiving so they can plan for the hardware and software resources to handle it. The answer varies widely depending on the types of analysis to be performed. Input databases may be relatively small (e.g. 100 megabytes), or they can be several gigabytes if many assumptions require information to be defined on the hourly or even sub-hourly level. Output databases can be anywhere from a few megabytes to several hundred gigabytes or even terabytes depending on what information needs to be reported and the required granularity of the reports. The data managed and stored by the IT department can quickly add up and become a challenge to maintain.
Here are a couple example scenarios:
A single planning analyst does a one-year hourly run (8760 hours) with modest reporting, which produces an output database of 40 MB. On average, the analyst runs about six studies per day over 50 weeks and the total space generated by this analyst is a modest 75GB. This is totally manageable for an IT department using inexpensive disk space.
Now, let’s say there are five analysts, they need more detailed reporting, they are looking at multiple years, and a regulatory agency states that they have to retain all of their data for 10 years. In this scenario, the total data size jumps to 500 MB for a single study. Given the same six studies per day those analysts would accumulate 3.75 TB of output data in a year, all needing to be backed up and archived for the auditors, which will take a considerable amount of hardware and IT resources.
What Are My Database Options?
There are dozens of database management systems available. Many power modeling tools support just one database system natively, so it’s important to know the data limitations of the different modeling tools when selecting one.
Some database systems are file-based. For example, one popular file-based database system is called SQLite. SQLite is fast, free, and flexible. This file-based database system is very efficient and is fairly easy to work with, but is best suited for individual users, as are many other file-based systems. These systems are great options for a single analyst working on a single machine.
As mentioned earlier, groups of analysts might decide to all share a common input database and write simultaneously to many output databases. Typically, this requires a dedicated server to handle all of the interaction between the forecasting systems and the source or destination databases. Microsoft SQL Server is one of the most popular database systems available in corporate environments, and the technical resources for it are usually available in most companies. Once you have your modeling database saved in SQL Server, assuming your modeling tool supports it, you can read from input databases and write to databases simultaneously and share the data with other departments with tools that they are already familiar with.
Here is a quick comparison of some of the more popular database systems used in power modeling:
|Database System||DB Size Limit (GB)||Supported Hardware||Client/Server||Cost|
|MySQL||Unlimited||64-bit or 32-bit||Yes||Free|
|Oracle||Unlimited||64-bit or 32-bit||Yes||High|
|MS SQL Server||536,854,528||64-bit Only (as of 2016)||Yes||High|
|SQLite||131,072||64-bit or 32-bit||No||Free|
|XML / Text File||OS File Size Limit||64-bit or 32-bit||No||Free|
|MS SQL Server Express||10||64-bit or 32-bit||Yes||Free|
|MS Access (JET)*||2||32-bit Only||No||Low|
A Word About MS Access (JET)*
In the past, many Windows desktop applications requiring an inexpensive desktop database system used MS Access database (more formally known as the Microsoft JET Database Engine). As hardware and operating systems have transitioned to 64-bit architectures, the use of MS Access database has become less popular due to some of its limitations (2GB max database size, 32,768 objects, etc.), as well as to increasing alternatives. Microsoft has not produced a 64-bit version of JET and does not have plans to do so. There are several other free desktop database engines available that serve the same needs as JET but run natively on 64-bit systems, including Microsoft SQL Server Express, SQLite, or MySQL which offer many more features.
Which Databases Does AURORAxmp Support?
There are several input and output database options when using AURORAxmp for power modeling. Those options, coupled with some department workflow policies, will go a long way in making sure your data is manageable and organized.
EPIS delivers its native AURORAxmp databases in a SQLite format which we call xmpSQL. No external management tools are required to work with these database files – everything you need is built into AURORAxmp. You can read, write, view, change, query, etc., all within the application. Other users with AURORAxmp can also utilize these database files, but xmpSQL doesn’t really lend itself to a team of users all writing to it at the same time. Additionally, some of our customers have connected departments that would like to use the forecast data outside of the model, and that usually leads them to Microsoft SQL Server.
For groups of analysts collaborating on larger studies, AURORAxmp supports SQL Server database, although its use isn’t required. Rather than use SQL Server as the database standard for AURORAxmp (which might be expensive for some customers), the input databases are delivered in a low cost format (xmpSQL), but AURORAxmp offers the tools to easily change the format. Once the database is saved in SQL Server, you are using one of the most powerful, scalable, accessible database formats on the planet with AURORAxmp. Some of our customers also use the free version of SQL Server – called SQL Server Express Edition – which works the same way as the full version, but has a database size limit of 10GB.
Some additional options for output databases within AURORAxmp are:
• MySQL: Open source, free, server-based, simultaneous database platform that is only slightly less popular than SQL Server.
• XML/Zipped XML: A simple file-based system that makes it easy to import and export data. Many customers like using this database type because the data is easily accessed and is human readable without additional expensive software.
• MS Access (JET) : The 32-bit version of AURORAxmp will read from and write to MS Access databases. EPIS, however, does not recommend using it given the other database options available, and due to its 2 GB size limitation. MS Access was largely designed to be an inexpensive desktop database system and given its limitations as previously discussed, we recommend choosing another option such as xmpSQL, SQL Server Express or MySQL which offer far more features.
Where Do We Go From Here?
AURORAxmp is a fantastic tool for power system modeling and forecasting wholesale power market prices. It has been in the marketplace for over twenty years, and is relied upon by many customers to provide accurate and timely information about the markets they model. However, it really can’t do anything without an input database.
EPIS has a team of market analysts that are dedicated to researching, building, testing, and delivering databases for many national and international power markets. We provide these databases as part of the license for AURORAxmp. We have many customers that use our delivered databases and others who choose to model their own data. Either way, AURORAxmp has the power and the flexibility to utilize input data from many different database types.
If you are just finding AURORAxmp and want to see how all of this works, we have a team here that would love to show you the interface, speed and flexibility of our product. If you are already using our model but would like guidance on which database system is best for your situation, contact our EPIS Support Team and we’ll be glad to discuss it with you.