Database Consulting Services

Database Consulting Services . . . some plain talk.

SQL Server Database Audits

Do you have persistent data issues that never seem to get and STAY fixed?  Schema . . . the basic rules and building blocks of a database . . . is the single most important factor affecting database performance, reliability, accuracy, and security along with the capability to do reports and analysis to support business decisions and on-going operations.  A schema audit is perhaps the most cost effective way to identify long term problems and get them resolved once and for all.  An independent and unbiased audit, done collaboratively with your staff, brings consensus among IT factions on issues and sets the focus on an achievable solution.

SQL Server Database Conversions and Migrations

Second chances are few . . . but upgrading to a full scale database management system is an opportunity to “get it right”.  If your data needs have out grown small scale spreadsheet and data programs (e.g. Excel and Access), the conversion and migration process is your chance to help set a positive course for your business.  Make it a priority, and your data system will be responsive and flexible to present and future business needs.  Neglecting this opportunity is usually an “ongoing failure” producing problem after problem over time and can negatively impact your business for years.

SQL Server Database Tuning and Optimization

Regular tuning and optimization is a routine and continuous requirement as both the nature and amount of data change.  If you don’t have staff performing this duty, it should be done at the first sign of performance issues and a periodic maintenance schedule established.  Automated maintenance routines will help keep things in order in between.  Deferred maintenance certainly becomes more expensive to remediate.

SQL Server Database Reporting, Business Intelligence, and Analytics

Not getting the information and answers you need in a timely and cost effective manner?  Over the years the data business has become segmented and highly specialized.  As data professionals have narrowed their focus to a limited or even a single subject matter, the data effort has become decentralized and fragmented.  The database world now has administrators, developers, architects, modelers, report builders, and business intelligence analyst . . . not to mention the systems engineers who do the database software installs but know nothing about the data world.  All having their own objectives and priorities.  This is especially important to reporting, BI and analytics because their functions are downstream of everything else.  Yet, few of these specialist have the knowledge and skills to fully comprehend and follow the process upstream when issues arise.  They report the problem and wait for it to work it’s way through the bureaucracy.  Priorities are usually set by the process of escalation to and involvement of higher management.  The key to their effectiveness is to have someone who can develop and bring an integrated view of the data process from beginning to end.


Please let me know how I can be of service to you.


How to configure a Utility Control Point (UPC) on Window 7 without a domain account

The SQL Server 2008 R2 documentation focuses on the SQL Server Agent account as must being a domain account.  However, a “stand alone” Utility Control Point (UCP) can be setup on Window 7 without a domain account.  Of course, the UCP will only function for local SQL Server instances but this is sufficient for a learning/test environment.

The snag is when the SQL Server Agent service account is specified in the UCP setup wizard.  On a local box, the SQL Server Agent service account is stored in the form “.\ServiceAccount” vice “HostName\ServiceAccount”.  This will return an error like this:

The SQL Server Agent service account must be a valid Windows domain account on the specified instance. Validation failed. The operation failed to create a login on the specified instance of SQL Server.  Possible reasons include invalid username and password, no permission to create a login, or a policy on the specified instance of SQL Server that prevents login creation. To continue, verify that the specified username and password are correct, then verify that Windows permissions and SQL Server PBM policies allow the specified account to run the Windows CREATE LOGIN operation. The error reported for this operation was:
Windows NT user or group ‘.\SQLServiceAccount’ not found. Check the name again.
For more information, see the Help topic for this operation.

The workaround is to use the option to manually specify an account.  My preference is to use the SQL Server Agent service account for everything.  By manually specifying the account, it can be input in the correct form, e.g. “HostName\ServiceAccount”.

FRB Data Quirks and what to do about them.

Federal Reserve Bank Data Quirks and what to do about them.

Quirks and “Undocumented Features”

The Federal Reserve Bank has established a pattern of introducing quirks to the data or the XML schema.  These quirks break down their entire technical approach to their data download service.  Fortunately, the sound architecture of FRB_SR greatly aids in compensating.  However, traces of the Federal Reserve Bank’s missives still appear in the data.  For example, the Federal Reserve Bank added about two dozen series code numbers to the “Flow of Funds Accounts of the United States (Z.1)” statistical release without the code names.  Those series code numbers were manually added but the code names were added like “UNKNOWN . . . temp fix for 123456”.  When the Federal Reserve Bank gets around to updating the XML schema or publishing the series names, this issue will be corrected.

Simple and Compact without the Quirks

We designed FRB_SR in a very compact form to facilitate distribution.  Like the Federal Reserve Bank, other data providers offer very segmented and limited access to the data.  FRB_SR contains every statistical release the Federal Reserve Bank publishes.  To make distribution possible, the schema is very simple with only three tables.  Seven data views provide a basic breakout and access to the statistical releases and their sub-levels.  While the FRB_SR design has performance features, the schema is a more compact data warehouse rather than a high performance database.  The initial execution of a view  will likely yield slower performance which improves significantly thereafter.  We have found this adequate for our  development and testing purposes.  If you find  performance is an issue in your data environment, we recommend extracting the data out to fully optimized data tables.  We are currently working on “expansion” scripts to do this.

When can I get clean data without the Quirks?

We publish FRB_SR version updates monthly around the fifteenth for the prior month’s data.  Federal Reserve Bank dictates the schedule.


If Federal Reserve Bank (FRB) data is free, why pay for it?

If Federal Reserve Bank (FRB) data is free, why pay for it?

Actually, FRB data is, in fact, free.

Background: Test data was needed for another database project and the Federal Reserve Bank is a good source. We didn’t need everything they have. However, it turned out all FRB statistical releases are fairly consistent being distributed on top of the SDMX standard.  Since we already built an automated ETL (Extract, Transform, and Load) process to download and handle FRB data according to that standard, we figured why not do all of them.

Here are the main reasons FRB_SR is a good value:

1) The FRB website limits downloads in a fashion that is a bit cumbersome and disorganized.
2) The FRB data downloads are not in a readily usable format.
3) The FRB and SDMX folks, being (quasi) government agencies, have a tendency to be a little careless about making “unannounced changes” . . . four times in the last six months . . . which are show stoppers. Adjusting for this is a relatively easy fix for us since it is a simple code change . . . and we are use to it.
)4 ROI . . . FRB_SR is cost effective.  Over the past many years we have designed, developed, maintained, debugged and reverse engineered countless data applications and systems . . . based on our experience an organization will spend thousands PER MONTH to built and administer a similar process.

So, the fee is not for the data but for doing the ETL process, packaging it in a real database, and distribution.



MacroTrenz Software End-User License Agreement

MacroTrenz Software License

This license pertains to all editions and versions of any MacroTrenz licensed Software Product.  This license is online at MacroTrenz.Com

Copyright (c) 2010 – 2017 Geary M. McIver, Data Consultant.
All rights reserved.

All software products created by and wholly & privately owned by Geary M. McIver, Data Consultant, are licensed exclusively through MACROTRENZ CORP. and are subject to the terms of the following licensing agreement:



MACROTRENZ CORP.’s End-User License Agreement (“EULA”) is a legal agreement between you (either an individual or a single entity) and MACROTRENZ CORP. for the MACROTRENZ CORP. software product(s) which may include associated software components, media, printed materials, and “online” or electronic documentation (“SOFTWARE PRODUCT”). By installing, copying, or otherwise using the SOFTWARE PRODUCT, you agree to be bound by the terms of this EULA. This license agreement represents the entire agreement concerning the program between you and MACROTRENZ CORP., (referred to as “licenser”), and it supersedes any prior proposal, representation, or understanding between the parties. If you do not agree to the terms of this EULA, do not install or use the SOFTWARE PRODUCT.

The SOFTWARE PRODUCT is protected by copyright laws and international copyright treaties, as well as other intellectual property laws and treaties. The SOFTWARE PRODUCT is licensed, not sold.

The SOFTWARE PRODUCT is licensed as follows:
(a) Installation and Use.
MACROTRENZ CORP. grants you the right to install and use a copy of the SOFTWARE PRODUCT on your computer.
(b) Backup Copies.
You may also make copies of the SOFTWARE PRODUCT as may be necessary for backup and archival purposes.

(a) Maintenance of Copyright Notices.
You must not remove or alter any copyright notices on any and all copies of the SOFTWARE PRODUCT.
(b) Distribution.
You may not distribute copies of the SOFTWARE PRODUCT to third parties.
(c) Prohibition on Reverse Engineering, Decompilation, and Disassembly.
You may not reverse engineer, decompile, or disassemble the SOFTWARE PRODUCT, except and only to the extent that such activity is expressly permitted by applicable law notwithstanding this limitation.
(d) Rental.
You may not rent, lease, or lend the SOFTWARE PRODUCT.
(e) Support Services.
MACROTRENZ CORP. may provide you with support services related to the SOFTWARE PRODUCT (“Support Services”). Any supplemental software code provided to you as part of the Support Services shall be considered part of the SOFTWARE PRODUCT and subject to the terms and conditions of this EULA.
(f) Compliance with Applicable Laws.
You must comply with all applicable laws regarding use of the SOFTWARE PRODUCT.

Without prejudice to any other rights, MACROTRENZ CORP. may terminate this EULA if you fail to comply with the terms and conditions of this EULA. In such event, you must destroy all copies of the SOFTWARE PRODUCT in your possession.

All title, including but not limited to copyrights, in and to the SOFTWARE PRODUCT and any copies thereof are owned by Geary M. McIver or his suppliers. All title and intellectual property rights in and to the content which may be accessed through use of the SOFTWARE PRODUCT is the property of the respective content owner and may be protected by applicable copyright or other intellectual property laws and treaties. This EULA grants you no rights to use such content. All rights not expressly granted are reserved by Geary M. McIver.

MACROTRENZ CORP. and Geary M. McIver expressly disclaim any warranty for the SOFTWARE PRODUCT. The SOFTWARE PRODUCT is provided ‘As Is’ without any express or implied warranty of any kind, including but not limited to any warranties of merchantability, noninfringement, or fitness of a particular purpose. MACROTRENZ CORP. and Geary M. McIver do not warrant or assume responsibility for the accuracy or completeness of any information, text, graphics, links or other items contained within the SOFTWARE PRODUCT. MACROTRENZ CORP. and Geary M. McIver make no warranties respecting any harm that may be caused by the transmission of a computer virus, worm, time bomb, logic bomb, or other such computer program. MACROTRENZ CORP. and Geary M. McIver further expressly disclaims any warranty or representation to Authorized Users or to any third party.

In no event shall MACROTRENZ CORP. or Geary M. McIver be liable for any damages (including, without limitation, lost profits, business interruption, or lost information) rising out of ‘Authorized Users’ use of or inability to use the SOFTWARE PRODUCT, even if MACROTRENZ CORP. or Geary M. McIver have been advised of the possibility of such damages. In no event will MACROTRENZ CORP. or Geary M. McIver be liable for loss of data or for indirect, special, incidental, consequential (including lost profit), or other damages based in contract, tort or otherwise. MACROTRENZ CORP. and Geary M. McIver shall have no liability with respect to the content of the SOFTWARE PRODUCT or any part thereof, including but not limited to errors or omissions contained therein, libel, infringements of rights of publicity, privacy, trademark rights, business interruption, personal injury, loss of privacy, moral rights or the disclosure of confidential information.