SQL Spring Cleaning: You KNOW You Need it!

Tis the Season of Spring Cleaning! While washing your car and scrubbing the window sills, be sure to include your SQL Server environment in your whirlwind of activity. Yes, much like the base boards, your SQL Server environment needs a little extra ‘maintenance’ attention now again.


Upgrading seems obvious, but we see a significant number of SQL Server installations running on the 2008 and 2012 versions. These SQL Server versions were all reasonably solid, so the “if it isn’t broke, don’t fix it” mentality has created a lag behind. Upgrades do more than solve problems however – they also provide new functionality and address issues that exist, but my not be obvious. Additionally, support for SQL Server 2008 R2 ends July 9, 2019, and SQL Server 2012 is scheduled to reach the end of its lifecycle in only three years. This may seem like a lot of time, but depending upon your other projects and your environment, 36 months can come around quickly. If you are on a SQL Server version below SQL Server 106, we recommend you upgrade to SQL Server 2017. Please review the Supported Version and Edition Upgrades for SQL Server 2017 and the Upgrade to SQL 2017 articles, published by Microsoft, to plan and execute your upgrade.

Since many of our clients leverage SQL Server Reporting Services, we wanted to highlight two items specific to this feature. First, the look and feel of Reporting Services changed significantly in SQL Server 2016, along with adding some long-awaited functionality, such as the ability to control parameter layout, mobile reports, and KPIs in the SSRS Web Portal. Second, the Reporting Services installation is now a separate download and install, rather than a feature component of the SQL Server installation.

Evaluate Licensing

SQL Server 2017 Standard Edition can be licensed on a Server + CAL or a Per Core basis. Your Edition and current license agreement will impact your options. Take a moment to touch base with your Microsoft Software Licensing Provider for a period review of your licensing costs.

Perform an Access Review

Databases move, turnover occurs, leaving orphaned users and old windows accounts straggling as a result. Take a moment to run the sp_validateusers stored procedure to identify an Windows accounts that need attention. SQL orphaned users take a bit more research: the MSSQL Tips article, Different Ways to Find SQL Server Orphaned Users, defines scripts for locating and addressing these.

Review the Basics

The turn of the seasons is a good way to schedule a review of your database health, which should be performed multiple times a year. Review your indexes for fragmentation, your databases for overgrown log files or outdated backups. Schedule and perform a “test drive” of a database restore. It is better to identify problems during a test drive than find out your backups are no good in the middle of a real-life issue. Check your SQL Agent Jobs to make sure they are working as desired and retire any jobs that are no longer needed. Evaluate your Reporting Services Subscriptions and confirm their distribution list is current. Confirm your SQL Server Maintenance Plans are performing tasks against all appropriate databases.

Touch Base with Your Team

Ask the users of the applications that depend upon SQL Server how those applications are performing. Users are the eyes and ears of your back-office system performance, so reach out and solicit their feedback. Team members are often the first to feel the impact of a missing or fragmented index or suffer the delays of a poorly written query. While the performance issue may not be something that can be resolved immediately, often times it can be resolved in time or mitigated. Healthy environments make happy users!

We grieve: the California wildfires

We reach out with our hearts again this year to the victims of the recent wildfires in California. With so many still missing and camping out in makeshift tents, the need for assistance hasn’t ever been greater. Join us in giving to any of the following organizations, who have been confirmed to be providing near to 100% of your contribution towards helping victims.

CA Fire Foundation


Caring Choices


Humane Society of Ventura County

If you donate to the HSVC, 100 percent of your proceeds will go toward serving the animals in their care.


Charity Navigator

The highly-rated organizations listed on this page have received 3- and 4-stars and are confirmed to be working to provide relief to the individuals and communities affected by the devastating fires in Southern and Northern California. Donors may be able to designate their donations to the specific cause they wish by going to the organizations’ websites directly.


Worker Training: Ramp It Up!

Economists love worker training, but companies are often reluctant to provide it. The benefits of training can walk out the door if newly skilled workers are poached by a competitor.

Near-record-low unemployment is one big reason companies are recommiting to training. With a U.S. jobless rate of just 3.7 percent in September and more than 7 million unfilled positions as of August, employers can’t find the people they need in the ranks of the jobless, and luring them away from other employers has gotten prohibitively expensive in some cases. “Your choice is always make or buy. ‘Buy’ is steal somebody else’s worker…

Read more at Bloomberg.com


Changing Accounting Standards Will Impact Property Managers

Leasing guidance before the issuance of ASU 2016-02 required lessees to classify leases as either capital or operating leases. Lessees recognized assets and obligations related to capital leases; expenses associated with capital leases were recognized by amortizing the leased asset and recognizing interest expense on the lease obligation. Many lease arrangements were classified as operating leases, under which lessees would not recognize lease assets or liabilities on their balance sheet, but rather would recognize lease payments as expense on a straight line basis over the lease term.

The IASB decided that lessees should apply a single model to all leases, which is reflected in IFRS 16, Leases, released in January 2016. The FASB decided that lessees should apply a dual model. Under the FASB model, lessees will classify a lease as either a finance lease or an operating lease, while a lessor will classify a lease as either a sales-type, direct financing, or operating lease.

Click here to read the article: How Changing Accounting Standards Will Impact Property Managers

Download the guide from PWC (including the differences between ASC 842 and IFRS 16)



For different types of work, people want different spaces

“Work is increasingly more fluid today,” says Christian Beaudoin, research director for JLL in Chicago. “People work from home, work from third places. Offices today are changing to meet those needs, so workplaces are necessarily becoming more fluid.”

JLL’s “2018 U.S and Canada Fit Out Guide” defines and examines three prominent styles of office space across the U.S. and Canada, compiled from data from more than 2,800 projects it managed. These styles are progressive, moderate, and traditional.

Progressive office space, which JLL calls the leading-edge office space, has an open floor plan, 100 percent of desk space outfitted as bench-style furniture, no enclosed offices, and numerous varieties of collaboration and conference spaces.

Moderate office space has 10 percent of the area allotted to private offices; the remaining space may be open with work stations, along with some conference rooms and several collaboration or multiuse spaces.

Traditional plans have 30 percent of space devoted to enclosed offices; the remainder includes an open floor plan and eight-foot workspaces, several conference rooms, and nominal collaboration spaces.

Get the entire CIRE Magazine at CCIM.com

Read more about this article in the current issue

BIM: What is it and How do I Use it?

What does BIM mean? BIM stands for Building Information Modeling. It is a digital representation of the physical characteristics of a building or home. The BIM file can be used to plan, design and construct a structure in a graphical form or it can simply be a list of the parts and pieces needed for construction. Most people are familiar with two-dimensional CAD drawings that include plans, elevations and sections, such as electrical. With the ongoing advancements in technology, many designers and architects are now using three-dimensional drawings, which eventually will become the industry norm.

One common misconceptions about BIM is that it comes in a box like Microsoft Excel but it doesn’t. Many business professionals use Excel to build cash flow models based on their information reporting needs. Even though it is a common platform, each cash flow is unique and specific to an organization. The same is true with BIM software; it may use a common platform, but each structure is unique and the underlying components of parts and labor vary from project to project.

Today, BIM is used by residential developers to:

  • Generate plans and elevations
  • Develop materials lists (take-offs) and cost estimates
  • Create graphical presentations to support sales and marketing efforts with brochures and online graphics
  • Conduct virtual home tours that offer potential buyers a walk-thru of a plan/elevation in the comfort of their home

During the market downturn, builders eliminated their estimators to reduce overhead and shifted the responsibility and workload to their suppliers. Cornerstone sees a new industry trend (in the past 18 months) of either establishing in-house estimating capabilities or outsourcing to professional firms. Builders focused on bids and take-offs are finding significant savings by taking responsibility for their own plans/elevations, which benefits their bottom line.


Click to watch the April 2018 BIM webinar on our YouTube channel


Next Generation Proforma Reporting

Today’s homebuilders are a mix of visionary and financier. They begin with a vision similar to Walt Disney’s 63 years ago when he built his first theme park in Anaheim, California. To be sure, Walt encountered more than a few skeptics along the way, but in the end his vision far exceeded expectations.

Throughout the past three decades, we at Cornerstone have met and worked with many builders who share this same visionary quality. They have the ability to look at raw land and envision a thriving community abounding with homes, schools, parks and shopping centers.

Though visionaries work diligently to make their dreams come true, they can be constrained by the shackles of financial reporting and meeting the expectations of third parties with a stake in the vision—investors, lenders, public markets, accountants and competitors. We live in an age when information about everything is readily available online any hour of the day; it is no longer an insulated world where information can be controlled.

Builders are asked to quantify their vision with timelines, business assumptions, key performance indicators and well-defined returns on investment. Most builders compile this data into an original proforma. The proforma itself is nothing new to the industry, but today it has blossomed into a living, breathing document used as a measurement tool to determine whether or not the builder’s vision meets expectations and projected returns.

Cornerstone finds that more and more sophisticated builders are using their original proforma to measure their success throughout the life of a project. In response, we have created for our clients a Comparative Margin Report that compares margins at key milestone events, such as release of purchase order, approval of sales contract including options, closing of a home when the new owner receives the keys, and six months after the home has closed.

We believe this last event—six months after the home has closed—is critical because margins reported at close are very often incorrect. It is not that the systems or algorithms go awry; it is a process problem when superintendents sit on change orders and variance purchase orders for months, turning them into accounting only after buyers have moved into their new homes. In a few cases, we have reported to management a swing of 6 to 8% in their final margin calculation due to late invoices and purchase orders.

Cornerstone’s new Comparative Margin Report provides management with a more accurate accounting of profits, KPIs and returns on investment. This report will become increasingly indispensable as the labor market continues to tighten and inflation becomes an industry issue.

Introducing the informXL User Security Analyzer

Let’s face it. Security set up and management in complex ERP environments can be daunting. A lot is resting on iron clad security mechanisms being put in place and monitored over time. Implementers, IT managers and even department line managers are charged with ensuring that proper security mechanisms exist across the operational software supporting homebuilder operations, but with so many internal controls, user roles and personnel, visibility into broad and detailed security set up can be challenging.

Enter informXL and the new User Security Analyzer, now available in the informXL 4.5 for NEWSTAR release. This new Analyzer will benefit anyone responsible for maintaining or auditing user security access in NEWSTAR, especially IT personnel, Controllers or those performing internal audits. It can be used for a variety of tasks, including:

  • General security management by user or user type
  • Analysis of internal controls and segregation of duties
  • Project level security management
  • Review of PO approval rules
  • Review or audit of security overrides

The User Security Analyzer provides detailed information for NEWSTAR users around assigned user types, NEWSTAR Web access, PO approval Rules and logged usage. The Analyzer can also be run by Project Security for quick insight into which users and user types have access to specified projects.  Some of the highlights include:

  • Menu Security – pulls from all menu-based security for active user types showing menu security names and the associated controls. This report provides excellent insight into internal controls and allows validation of current set up to ensure adequate segregation of duties have been properly structured and is helpful during initial user security set up.
  • PO Approval Drill Down – ability to audit or review PO approval rules assigned to a specific user. A drill down feature provides visibility into user information, Rule ID, approval limits and escalation contacts if needed.
  • NSE Web Access – for anyone using NSE Web, the User Security Analyzer will provide insight into whether a user ID has been added into NSE Web and whether or not it has been approved. Conditional formatting allows you to easily see the status, along with the last login date.
  • Usage Drill Down – using a logged usage drill down which pulls all logged usage information by user, easily identify every program code a user has hit over a specified period of time.
  • Project Security – for those who have implemented project level security in NEWSTAR, the User Security Analyzer will highlight all operating units and projects a particular user is assigned to using project level security in NEWSTAR. A pivot table is included allow a quick and easy audit of which users have access to a particular project.

Those responsible for user security oversight will appreciate the ease of insight the informXL User Security Analyzer provides, and the time it saves tending to those important tasks associated with properly securing sensitive data. To see the User Security Analyzer video overview, check it out on the informXL YouTube channel where it can be found in the Analyzer – Constellation NEWSTAR section. The User Security Analyzer is only available beginning with release 4.5. Anyone interested in updating to the latest release should email us to request the update.