By Craig Colangelo, Sr Consultant for PerformanceG2
 
Many of us don’t have the budget to implement elaborate logging schemes into our smaller SSIS projects. That being said, even the smallest amount of logging will pay for itself the first time something blows up. In my experience, some logging, no matter how simple, is better than none.

Logging allows you to better understand load errors, diagnose longer batch run times and flush our load inefficiencies – just to name a few key benefits. In SSIS, logging can be as intricate or simple as you would like. Using just system variables and event handlers, you can create a functional logging system. Writing key system variable output during select events like OnError, OnPreExecute and OnPostExecute at the package container level, gives you a good idea of what is going on in every piece of your package. A few useful system variables are PackageName, SourceName, ErrorCode, ErrorDescription, StartTime, ContainerStartTime and MachineName. Writing to a log table with datediff’s between getdate() and desired system time variable, gets you very useful run time semantics. Through in row count tasks, user variables and send mail at various steps, and you have got a solid logging scheme. Spending the extra time upfront to implement the right level of logging in even your smallest projects will, undoubtedly save you diagnosis time in the future.

 
 
Contact the Blogger

 
By Craig Colangelo, Sr Consultant for PerformanceG2
 
Requirement was to display crosstab intersection as a ratio in HTML output, but retain conventional decimal format for the data item itself (for further downstream computation and at a different scale). Consequent for the ratio was to always be 1. Could not add additional data items.

There are several possible solutions to this problem. Couldn’t use any form of casting to string, as I needed decimal downstream…and at finer scale than display. Also couldn’t add a one off version just for display. Decided to use the solution described below as it met my needs well for this particular problem. Decided to share it because the Pattern property is not very well documented and took trial and error to get right syntax.

In the Crosstab Intersection’s properties -> Data -> Data Format -> Number (2 decimal places) -> Pattern property….set to ###.##’:1′. This will take 3.544432112 and display it as 3.54:1, while retaining the integrity of the decimal itself…so that it can be used downstream at more precise scale for other calculations.

 
 
Contact the Blogger

 
By Craig Colangelo, Sr Consultant for PerformanceG2
 
As the downstream beneficiary of the company’s operational data, those of us who design and build data warehouses see all sorts of bum data. Operational systems that don’t exactly behave as everyone expects, business results that differ from commonly held assumptions, dirty data that appears to be in some unknown language, and so forth and so on. We, of course, have the means to code around most any obstruction we encounter in the data, but we need to make sure that the owners of the source systems know about potential issues in their applications and processes.

A data warehouse is in a unique position because its design is influenced by design assumptions in the source systems and business processes feeding into it. When these assumptions are proven incorrect, we are able to add huge value to the application development process by calling out the incorrect assumptions so that they might be addressed upstream or consciously chosen to be ignored. Either way, it’s our responsibility to make sure that we delicately communicate the potential issues to those who need to know. This leads to tighter business processes, stronger source systems, and ultimately better data for decision makers.

 
 
Contact the Blogger

 
By Craig Colangelo, Sr Consultant for PerformanceG2
 
At several client sites, I’ve had the need to rollback specific production reports or report sets to previous versions. Often times, this was in a single Cognos environment (no dev or test, just prod). Additionally, my clients didn’t have the resources to provide version control systems to manage report source code, therefore, I had to create a pseudo source code management process specifically for C8 reports where archived report specs lived outside of the C8 content store (to allow for effective development collaboration).

There are many ways to supplement or vary this process to suit your own needs. It’s a simple idea and worked well considering the very low cost to utilize. I’m sure there’s plenty of similar freeware for this, however, we don’t always have the ability to add additional software to client servers.

I needed to be able to rollback previous versions of reports from an off C8 server location. I also needed to be able to refer back to ‘release notes’ to understand what I implemented in each iteration of these reports. To satisfy these needs I would simply save off the report spec (.xml) to a local/C8 server shared drive, with an iterative version number appended, and a same named text document that tracked the version number, and what was distinctly moved/added/changed for each iteration. The only thing that I had to do in C8 to enable local saves of the report spec was:

• Get the C8 LFA.dll from C8 server
• Open a command prompt window and register the LFA.dll file by typing regsvr32 LFA.dll
• In Report Studio, from the Tools menu, click Options
• Select the Allow local file access check box and click OK
• The menu items (Local) Open and (Local) Save As appear in the File menu

Note: This does require allowing ActiveX control via your browser and adding the Cognos site and your local computer to the Trusted Zone

Whenever I would work on a new version, I would first ‘check out’ the latest xml spec by adding the word ‘lock_’ to the front of the .xml and .txt files for that report. This indicated to anyone else looking to change this report, that the report was currently being modified. This process, coupled with good concise descriptions of updates in the .txt file for each version was more than enough to ensure effective group collaboration, solid enough version control, and the ability to rollback reports to specific points in development time.

 
 
Contact the Blogger

Craig Colangelo, Sr Consultant at PerformanceG2

July 7, 2009

We’ll wrap up this blog series with today’s post, building on last week’s best practices theme.  We’ve touched on the fact that these scorecarding systems are more than just a means for data presentation…they’re truly performance influencing applications.  Treat development of these tools more like an application development effort.  Use things like joint application design sessions, lunch and learns, and one on ones w/ scorecard owners to help build trust and to encourage true business ownership of the tool.

Sell it!  Many companies have lofty goals and aggressive growth plans but don’t have the means to track and influence their progress towards their stated destination.  Scorecarding applications can be the solution.  Do what you can to sell your solution as a means of practically living the strategy that your company already likely has in place. 

Don’t leave metric owners hanging.  How would you feel if you were responsible for a high visibility metric that was a perpetual poor performer and couldn’t show why the metric was in the dumps?  Make sure to give as much supporting information for apparent cause analysis in the tool as possible.  This can be accomplished with default reports, impact/impacted diagrams, and even corrective action pathways.

When helping the business choose what to measure to help them achieve their strategic goals, remember the SMART acronym:

• Specific – unambiguous, concise
• Measurable – tangible, real
• Actionable – something the owner can influence
• Relevant – aligned with and encouraging the behavior you want to drive
• Timely – if you’re trying to impact weekly cycles, monthly measurements aren’t enough!

Also make sure to have a mixture of both lagging and leading indicators where possible.  Lagging indicators (like financials) are usually very easily obtained but are not enough to influence an outcome…leading indicators give you a means for early warning.

Every strategic objective needs some form of measurement.  If a SMART metric cannot be obtained, use a proxy.  Even if it’s not necessarily a 1:1 relationship between the objective and the measurement, at least you’ll have a shot at influencing results. 

An effective scorecarding solution is always in a state of flux…continually evolving.  This is a good thing.  It should be used and modified to meet the changing needs of the organization.  As a developer, make sure you build this app not to last, but to change.

Successful scorecarding implementations are an effective way to show the value of BI and how it directly impacts the bottom line.  Your scorecarding app should be a gateway into other BI components.  Those who are benefiting most from your scorecard environment are usually the ones funding your overall budget/projects … what better way to show your group’s value?

Craig Colangelo, Sr Consultant at PerformanceG2

June 30, 2009

In this week’s post, I’d like to cover just a few general best practices I’ve discovered along the way.  These are simply my conclusions as a project manager and technical leader in a data warehousing group.  They are based on my scorecarding implementation experiences at several companies. 

I think it’s important to call your first implementation a pilot or proof of concept and to treat it like one.  This helps to set the tone for the effort and lets everyone know that this is really the first touch with the technology and methods involved.  The implicitly lowered expectations (by calling it a pilot), greater dedication of time (by both you and the business), and greater visibility (allowing for a more focused effort) should help you to achieve a successful first implementation.  Be sure to set the expectation that this is a dual benefit effort.  Good for the business area you’re scorecarding for as it gives them greater visibility into their own performance and allows them to actively influence outcomes.  Good for the IT group as it gives you practical experience with this new tool and the ability to better support it in the future. 

Pick a strong subject area.  New, important strategic imperatives or aggressive growth plans are usually good candidates as they get the focus that they should.  Well defined metrics (both leading and lagging), focused business leaders and analysts, and committed joint effort by IT and the business (make sure it’s stated) are also important success characteristics. 

Allow for proper due diligence.  There’s always a struggle between faster time to market and a more deliberate, well thought out implementation.  A decision should be made upfront as to what’s more important.  Make no mistake, scorecarding tools are actual applications.  This is not just another means of displaying information.  The purpose of the tool is to drive performance.  It is not enough to simply build a scorecarding app and release it.  Employing the tenants of accountability using the tool, giving the application high visibility, and use of the app during staff meetings all help to ensure that performance is being driven towards the desired outcomes and ultimately aids in the acceptance of the technology. 

There are many, many factors involved in determining the success of a scorecarding solution.  This is certainly not meant to be an all inclusive list, but rather some of the more important factors I’ve encountered.  We’ll continue on with more best practices next week.

Craig Colangelo, Sr Consultant at PerformanceG2

June 23, 2009

We’ve spent time in previous posts summarizing where scorecarding fits in the larger CPM space, benefits/risks associated with implementation, and how to best realize scorecarding opportunities.  Now, we’re going to better define the solution itself and touch on some of the basics.

First, it is important to understand that scorecarding is not an IT solution.  IT is necessary, but not nearly enough to fully achieve an effective solution in this space.  A typical implementation does involve software, hardware, and other systems…but only to the extent that they deliver information to business users and drive performance in the right manner. 

Many a scorecard project has failed because it was not designed or implemented to meet the strategic needs of executives, managers, and other decision makers.  Choosing the right business sponsor can go a long way in mitigating that risk.  Getting lots of business input and implementing the tool specifically to drive performance are also paramount to success.

There’s plenty of bleed over in the data presentation world and one solution may encompass several presentation methods.  Many confuse dashboards for scorecards.  Let’s cover a few differences:

• Dashboards are generally more dimensionally biased and usually have the ability to add more dimensional contest.
• Dashboards are more operational in nature and data is usually updated more frequently (hourly, daily).
• Dashboards usually answer one or just a few performance type questions and performance is quickly gleaned with just a glance.
• Scorecards are very performance management centric…think accountability, goals, performance improvement, etc.
• Scorecards are more strategic in nature and data is updated less frequently (weekly, monthly, quarterly, annually).
• Scorecards are generally less graphic.

Scorecard basics:

• A scorecard is a collection of performance metrics designed to reflect the strategic goals of a unit in an organization
• A metric is a measurement which has a target value; an actual value and an owner who is responsible for the performance of that metric.  A metric is related to a strategic goal.  They allow you to understand how you are performing.   Metrics and your scorecarding application answer key questions like …
  • How am I doing?
  • When has this happened before?
  • What drives this metric?
  • What could be causing the problems?
  • What other metrics and objectives does this affect?
  • What do I have to do about it?
  • Who else is included in the decisions?
  • Where does the data come from?
• A metric type defines the behavior of a collection of metrics.  Metric types  typically identify one aspect of performance (such as revenue or expenses) defined by a performance pattern, for example:
  • Revenue has a performance pattern of “above target is positive”…you want revenue to be higher than target.
  • Expense has a performance pattern of “below target is positive”…you want expenses to be lower than target.
  • Inventory has a performance pattern of “on target is positive”…you want just the right amount of inventory, not too high and not too low.

Requirements gathering:

• Make sure you have the right audience at the right time…
  • Senior leaders craft vision then formulate, articulate, and communicate strategy to their groups.  They are the ones ultimately responsible for performance in this overall scorecard area.  They hold their direct reports accountable for performance of their own metrics (metric owners).  A combination of senior leader + direct reports should define which metrics to use for each strategic objective.
  • Analysts help define where the data for the predefined metrics live, how their group would like the data to display, and a metric’s impact on other metrics (just their theory…you should definitely involve metric owners and senior leaders in this theory formulation).
• Be ready to define the following during requirements gathering sessions…
  • Any graphics to support the strategic direction available (strategy map, balanced scorecard, road map, common mission statements, etc.)
  • Name users of the scorecarding system and their role (information consumer, metric owner, analyst, scorecard owner, SME, etc.)
  • For metrics, you’ll need some of the following information:
    • Business and technical descriptions
    • Owner’s name and contact info
    • Frequency of data
    • Data source for actual, target, and tolerance
    • Unit of measure, scale, and how it rolls up
    • Metrics that impact this metric
    • Metrics that are impacted by this metric
    • General groupings and location for metric on graphic
    • Supporting reports/analysis/dashboards for potential drill out
  • For scorecards, you’ll need some of the following information:
    • Scorecard name
    • Business and technical descriptions
    • Owner’s name and contact info
    • Hierarchy – where does this scorecard fit in the tree
    • Supporting documentation for the strategic initiative

That’s all for now.  We’ll touch on best practices and other general advice in next week’s post.

Craig Colangelo, Sr Consultant at PerformanceG2

June 15, 2009

Peter Drucker said, “There is nothing so useless as doing efficiently that which should not be done at all”. I can certainly understand this quote’s applicability in today’s corporate climate of mass measurements and broad focus. T.P.S. report, anyone? But what, exactly, should be done? Where, exactly, should we focus?

The answers vary by company, industry, and even change with the economic times. The secret to your company’s success is complicated and specific and is ever evolving. Scorecarding applications allow you to clearly map out and perform along your own path to corporate success. Benefits of implementing thoughtful, actionable scorecarding include:

• Strategy focused organization
• Agility – well equipped to handle internal and external change
• Greater results focus
• Better accuracy in forecasting business results
• More holistic view of performance
• Clear cause and effect leading to better decision making

Understanding potential corporate culture risks are key to a successful implementation. Contemplating the following risks will help you more thoughtfully implement components at the right levels and in the right way at your own organization:

• Accountability and visibility – Think about your own organization’s tolerance for broadcasting results. This is truly putting a metric to a name…in some form.
• Need to trust the tool – If your ‘check engine’ light is glowing and you ignore it, is it doing any good?
• Adversity to change – If your implementation is meant to guide change in your company, give thought to how willing you are to accept change.
• Time constraints – Many decision makers are completely bogged down living only in the operational management space (day to day)…how can you best introduce strategy based decisions? Who should learn the new system? How?

Recognizing the right kind of scorecarding opportunity is another key to a successful implementation. Often times, meaningful corporate change projects are started with little thought around how to influence, measure, and communicate results. Do your best to piggy-back on just that sort of project. Opportunities include:

• Whenever performance becomes an issue (under performing, aggressive growth plans, acquisitions of new businesses, etc.)
• Corporate or departmental strategic initiatives
• ‘Franchise’ type opportunities (i.e. – bank branch performance)
• Senior leaders looking for new ways to manage their portion of the business
• Process improvement or change management initiatives
• Incentive programs

Now that we’ve covered some of the key benefits, potential risks, and common opportunities…. we’ll touch on scorecarding 101 and requirements gathering during next week’s post.

Craig Colangelo, Senior Consultant for PerformanceG2

This is part one of a five part series on successful scorecarding. Over the course of the next several weeks, we’ll touch on the following scorecard related topics…

• Strategy and corporate performance management
• Identifying benefits
• Understanding potential risks
• Realizing opportunities
• Scorecarding 101
• Requirements gathering
• Best practices & other practical advice

Tune in weekly for the latest and greatest!

Many successful companies realize the importance of strategy and the benefits of driving performance in the desired strategic direction. Unfortunately, relatively few have properly articulated, communicated, or implemented the practical means to wind up at their chosen strategic destination. Scorecarding applications allow you to do just that…guide day to day decisions in the direction of your company’s chosen strategy. Living your company’s mission statement OR strategy/road map OR balanced scorecard doesn’t have to be a pipe dream or something that only happens in an annual review. Implementing a pragmatic and actionable scorecarding system gives employees the ability to think and act strategically every day.

The uses of conventional BI systems are really only one part of a larger corporate performance management opportunity. Combining planning components (drive the business), scorecarding components (monitor/influence the business), and reporting components (understand the business) allows for a fully synergistic decision making continuum. This is true closed-loop analytics, where clearly stated strategy influences outcomes, and those outcomes influence future planning, and so on. In this continuum, corporate strategy can now be implemented and thoughtfully executed at all desired levels of the organization.

Now that we’ve set the table on where scorecarding fits in terms of living strategy and corporate performance management, we’ll spend next week’s post detailing the benefits, identifying risks, and better understanding scorecarding opportunities.

(more…)