Both dashboards and scorecards are mechanisms for visually representing complex enterprise data which provides insight into business performance. The data types include summaries of key performance indicators (e.g. sales data) and other measures of business performance (e.g. number of customer service calls).

The distinction between these two reporting mechanisms has become increasingly blurry. The terms are often used interchangeably, reflecting a widespread belief by industry professionals that they are the same thing. Although they have a great deal in common, there are good reasons for keeping a clear distinction between the two. In particular, they embody two distinct approaches to the communication of enterprise data, each with its own virtues.

Dashboards: Providing Current Insight on Business Operations

A dashboard is an interactive user interface designed to deliver user-specific information relating to the health of the business. They use various types of visual representation techniques (e.g. graphs, dials, speedometers, stoplights) and process controls to focus user attention on important trends, changes, and exceptions.

The purpose of a dashboard is to allow for an immediate understanding of the state of the business. The data is typically displayed in real time allowing immediate response to changes in business activities. In this way, dashboards allow for tactical (as opposed to strategic) decision making. Dashboard data tracks key performance indicators (KPIs) to monitor the vital signs of the business. They have drill down capability from summary data to underlying reports, enabling users to investigate the root cause of problems or changes.

Scorecards: Measuring Strategic Business Performance

Scorecards (or a Balanced Scorecard) also provide insight on business operations but involve a much more structured approach. They are designed with the aim of measuring performance relative to a specific set of business goals and strategies. Rather than provide a snapshot of the business, they track business activities as trends over time to inform the business strategy. Scorecards need to be supported by a management process that defines actionable results-oriented tasks (see Table 1 for the main differences between dashboards and scorecards). They can be used to monitor and assess performance at the individual, departmental, and enterprise level. Industry performance benchmarks and risk factors can also be incorporated into scorecards.

Visual representations of information are organized in such a way (e.g. by individual, department, and business unit) to ensure a focus on business goals. Information is typically not presented in real time. Summary data usually funnels vast quantities of enterprise data over larger intervals and measures against baselines (e.g. last year) and benchmarks (e.g. industry performance). They can be tied into communication systems (e.g. email) to send out alerts when results are not in the expected range or when actions/initiatives are overdue.

Table 1. Comparing Dashboards and Scorecards

Examples of Dashboard and Scorecard Metrics

The differences between dashboards and scorecards are best demonstrated by looking at real examples of the kind of metrics that would occur in each. Consider a manager responsible for calls at a large customer support call center. Whether the manager needs a dashboard or scorecard depends on what they want to do with the data they collect – the management decisions (operational or strategic) they want to inform.

A dashboard can be used to monitor and manage the current activities of the call center. For example, by looking at the total number of calls and the total number of staff, management can make an operational decision to reduce or increase staff to accommodate call volumes. See Table 2 for comparative examples of dashboard and scorecard metrics for a call center.

A scorecard for a call center might have the strategic target of reducing resolution time to four minutes to improve customer satisfaction while reducing cost. Call data can then be recorded and assessed relative to this goal. When performance falls short, management can execute a pre-defined management strategy for dealing with missed targets (e.g. identifying individuals with low call resolution rates and re-training or disciplining them).

Table 2. Examples of Dashboard and Scorecard Metrics for a Call Center

Key Takeaways

The decision whether to deploy a dashboard or a scorecard should depend on the specific business needs of the organization. Keep the following in mind when considering the choice between a dashboard and a scorecard:

1. Dashboards support tactical decision making and scorecards are strategic.
   Dashboards monitor real-time business performance to inform operational decision making. Scorecards always monitor business performance relative to a specific objective and over larger periods of time to inform strategic management.
2. Dashboards provide business snapshots and scorecards analyze performance trends. 
   Because dashboards and scorecards support different types of decision making, they measure and summarize different kinds of data. Dashboards aim to provide real-time operational data, while scorecards record data over periods of time and plot performance against defined business objectives.
3. Dashboards require operational expertise and scorecards require a robust management strategy. 
   Even if the enterprise knows what it wants to measure relative to strategic objectives, a robust management is required to change organizational behavior in the direct pursuit of those goals. The effective use of dashboards requires managers who understand the ebb and flow of business data and know when to act on changes in business activities.

Bottom Line

Dashboards and scorecards involve different approaches to the communication of enterprise performance data for management decision making. Knowing the difference is essential for those charged with developing the right reporting tool for the business.

There seems to be a great deal of excitement about the next release of SharePoint and with good reason, in almost everyway 2010 is better than 2007.  With so many SharePoint implementations that promised so much and improved the previous situation greatly but didn’t quite live up to the hype, SharePoint 2010 provides the perfect opportunity for existing customers to ‘sweat their assets’ and really make SharePoint deliver what it was supposed to.  The Business Intelligence and Performance Management aspects of SharePoint 2010 are something to be excited about and if you throw in the ability to migrate existing business processes into portal hosted business applications you have a compelling argument to upgrade.

If you implemented the SharePoint BI features via PerformancePoint Server (PPS) 2007 and you are concerned about upgrading from 2007 to PPS 2010 then you will be pleased to hear that Microsoft have released this official blog outlining the steps to a successful, smooth and hitch-fee migration:
http://blogs.msdn.com/performancepoint/archive/2010/02/25/upgrading-performancepoint-server-2007-to-pps-2010.aspx

Blog synopsis: Most of the customers who have been using PerformancePoint Server 2007 have accumulated several months, if not years, worth of dashboards and data. Their KPIs, grids, charts, scorecards, and custom objects have gone to good use, providing a great deal of corporate discussion about how to handle business decisions and to help plan for the future. And while the 2007 version of PerformancePoint helped to do this very well, the SharePoint BI 2010 version does it even better. Understandably, most companies want to build on top of their old dashboards in 2010. And the idea of starting from scratch is unthinkable. Fortunately, Microsoft has a nice migration path so that you can migrate all of your existing objects to the new version. The migration process is straightforward, but to help ensure that things go smoothly, we’ve created a set of steps to follow.

Microsoft supports two upgrade paths. The first is a version to version, in-place upgrade that involves launching an install of SharePoint 2010 right over the top of SharePoint 2007. The install detects the prior version instance and proceeds through the upgrade process. The second involves a version to version database attach scenario that allows you to attach a 2007 database to a new install of SharePoint 2010. Both methods work equally well. The latter method is ideal if you have new hardware for SharePoint 2010 and don’t need to re-use existing servers hosting 2007 versions of PerformancePoint/SharePoint.

It looks like Microsoft have listened to their customers and really put some work into ensuring that the upgrade paths for all aspects of SharePoint prove to be as straight forward and pain-free as possible.

Unified BI and CPM, Self-Service Reporting and Analysis, Interactive Visualization and Advanced Office Integration come together in a revolutionary product.

Tough times can bring many challenges - but can also present many opportunities. Havin g a clear view of your business can enable better decision making and optimize your use of valuable resources.

Thanks to its innovative toolkit approach, BOARD enables organizations to create customized Business Intelligence and Corporate Performance Management solutions quickly and effectively, without the need for any programming or extensive IT involvement.

BOARD provides access to a comprehensive "self-service" information environment, which enables users to obtain immediate answers to key business issues from a verified, consistent, shared information source.

Join us for a 30 minute presentation of BOARD along with a user case study review.

Want to find our more? Please join us for our next webinar:

Are you getting the most out of your data? BOARD in 30 minutes

Date:  March 17th, 2010
Time: 2:00PM US Central Time

Join here:
https://www1.gotomeeting.com/register/163195968

For more information on BOARD visit: www.board.com

Why not start the New Year in style by attending the Microsoft Global Energy Forum 2010?

The event takes place on Thursday, January 21^st 2010 at the Westin Galleria, Houston, TX and is free to attend. Simply register your attendance in advance by visiting http://microsoft.crgevents.com/gef2010/microsoft_gef and clicking on ‘register’.

Business Intelligence will be a top priority for corporations in 2010 and this event is the perfect opportunity to better understand how to equip your company. By working with Altius you can learn how to implement impactful, quick to deliver Business Intelligence solutions that yield a high return on investment (ROI). We have over 16 years experience of serving many of the world’s top energy companies and would like to do the same for you. Speak see us to learn more about:

• Doing ‘more for less’ in 2010
• Business intelligence (BI) and Performance Management with SharePoint
• Taking control of corporate spreadsheets
• Reviewing your current BI landscape
• Maximizing analysis and decision making

What's more, by visiting our stand and dropping off your business card, you will be entered into our prize draw and have the chance to win one of two special prizes: either a Zune HD Video MP3 Player or XBox 360!

We welcome the opportunity to discuss your specific needs and questions and hope to see you on the 21^st.

A while ago I posted a blog on Reporting on SAP data with Microsoft tools and have an update regarding SQL Server 2008.

Integration Services for SQL Server 2008 supports the extract of data from or the export of data to SAP BI InfoProviders such as InfoCubes, Data Store Objects (DSO), and InfoObjects.  Unfortunately you can only use this method if the SAP system has Open Hub Services activated, otherwise you will need to use a solution such as ERP Link.

Microsoft have released a technical white paper entitled: Using SQL Server 2008 Integration Services with SAP BI 7.0 detailing common scenarios for likely usage and how to's. 
You can find out more by reading the white paper which is available here:
http://msdn.microsoft.com/en-us/library/dd299430.aspx

Spreadsheets are ubiquitous, perhaps the most used application in the workplace for a large majority of people and definitely not going away.  However sometimes they can also be, well, dangerous and secretly costing you plenty of cash so it is worth looking at them in these challenging economic times. 

Some of the common questions that you should ask about your spreadsheets:

• Can you trust the data held within them?
  • Especially if you have been sent them via email?
  • Do you know where the data in the spreadsheet came from?
  • Can you check a server side data source to give you more confidence in trusting the data?
• How are the spreadsheets managed?
  • How do you know you have the right version of the spreadsheet?
  • Do people keep their own copies of data and protect it?
  • What would happen if these people left the organisation?
  • Are the workbooks full of complex logic only understood by its creator or does it use common business rules common throughout the organisation?

For more information I recommend you download the white paper "Getting More from Your Spreadsheets with Business Intelligence" written by Business Objects.  It covers the hidden costs of spreadsheets and other issues such as security, data integrity, reliability and how to extract the most value out of them. 

As nearly all users are familiar with Excel you will face serious opposition by trying to remove it entirely, so what options are available with regards to reining in these spreadsheets so they are used more effectively and better controlled? (from an SAP-Business Objects perspective as that is my area of interest)

For simply accessing and sharing data you can use the Business Objects Live Office suite that enables server side data to be embedded into Excel, Word, PowerPoint or Outlook.  This guarantees the data held within the documents is trusted as it is sourced directly from the data warehouse and eliminates the  issue of two groups of people having differing figures for the same set of information, you have 'one version of the truth'.  Once the data is embedded into Excel you can format it and perform calculations in the usual way and all changes are retained.

For planning, budgeting and forecasting exercises you can use SAP Integrated Planning (IP) or SAP Business Planning & Consolidation (ex-OutlookSoft).  Whilst BPC has superceeded SAP IP it is in fact guaranteed to be supported by SAP until 2017 so it is still a viable option.  SAP BPC 7.0 which being built on the NetWeaver stack (as opposed to version 5.0 build on Microsoft) is in 'ramp-up' mode and is unlikely to be generally released until Q2 2009. 

Some of the advantages of using either of these budgeting, planning or forecasting solutions are:

• Complex business logic can be moved from individual workbooks and handled central and in a uniform way
• Data security can be managed to ensure that only the correct users can submit/review data
• Better auditing, tracking and monitoring of the planning processes

to name but a a few.

If you use Excel to model 'what if' scenarios then you should consider Xcelsius.  The Business Objects visualisation tool has powerful features that can help you make sense of complex data.  It can also be used for dashboards and other models and has been refered to a one of the new BI 2.0 type applications.  A simple Profitability Analysis model demo built using Xcelsius is available to view here (requires Adobe Flash) and a whole host of others here This profitability model allows you to create "what-if" scenarios by modifying sales growth rate and all other relevant accounts measured as a percentage of total sales. This example, built with fictitious data, depicts the most relevant accounts of a profit and loss statement, and shows the impact of changes on net income. The results change immediately, allowing you to create endless what-if scenarios.

In summary spreadsheets are here to stay and with Office Excel 2007 increasing the maximum limit on the number of rows in a spreadsheet we will probably see million row data silos being created all over the organisation by business users rather than pointing them to a server side data source in a BI system.  Despite the disadvantages of spreadsheets, including their unfortunate tendency to make important data difficult to find and to derail compliance efforts, companies should forget about eradicating them, its far too late for that with 400 million global users of Excel, instead focus on using them better.

Following on from my previous post on using a structured project management methodology I want to now cover some of the important tips I have picked up managing SAP BI projects.

• Users must come first; any new solution must be used or it is both useless and a waste of time. They must be engaged and represented at every stage, if they don’t buy into the vision then the project legacy is doomed.
• Any project that is driven by the technical team may well end up being flashy and fully featured but it may not live up to the expectations of its users. It is essential to engage business contacts who know the project area inside out and really do represent their interests. This can avoid a lot of painful rework at a later date.
• The project vision and scope must be clear so all team members are moving in the same direction and only working on what is critical to its delivery.
• The project must have support from a sponsor high enough in the organisation to make things happen to help drive the project forward when times get tough.
• A crack team of experienced technical specialists are indispensable; they will use best practice, know how to fix common but undocumented issues and invariably reduce the implementation time. Even though they may seem more expensive they generally provide more value and reduce overall cost of project delivery.
• Be prepared for software bugs!  As we all know no SAP implementation will be delivered without having to apply notes, service packs and patches, so allow contingency time from the outset.
• The stakeholders of many enterprise projects can span multiple offices and countries so good communication channels are vital. This can include having access to collaboration software such as Live Meeting, resource repositories such as SharePoint, international conference call facilities and even instant messaging applications.
• A well managed change management strategy is required to get the users on board and ready to use a new system and its associated processes. At the point of project go-live the users must want to use the new system and not simply continue using their old habits (if it is possible for them to do so), it must also be easy for them to make the switch and good training material and support mechanisms will help immensely.
• If the project team is different from the support team then organise your knowledge transfer sessions early and do not under-estimate how long this process will take. It is essential the solution can be fully supported at the point the implementation team leaves the project.
• Promote the project in the organisation and involve other teams and departments who have even the vaguest interest. If you can build support and momentum behind the project then it will be easier to it get through any issues that arise that involve those other interested parties.

This list is by no means definitive but I hope that some of these guidelines can help you deliver successful projects.  Good luck!

I know, I had you at structured project management! Firstly apologies for the dull sounding title but I have recently delivered a successful SAP BI project using a structured project management methodology and thought that over a series of two blogs I would firstly cover the reasons why you should take this approach and then next cover some lessons I learnt and also some tips that I picked up along the way.

One of the hardest things to do with a Business Intelligence (or in fact any!) project is to keep it running on time and on budget, without proper control projects can easily go way off course and blow the original business case and associated budget out of the water.  Structured project management means managing the project in a logical, organised way, following defined steps.  By definition a structured project management method is the written description of this logical, organised approach.

The project should be divided in manageable stages enabling efficient control of resources and regular progress monitoring throughout the project.  The various roles and responsibilities for managing a project should be fully described and are adaptable to suit the size and complexity of the project, and the skills of the organisation.  It is also worth considering product-based planning that means the project plans are focused on delivering results and are not simply about planning when the various activities on the project will be done. 

The project should be driven by its business case, and to some extend its ultimate goal, that describes the organisation's justification, commitment and rationale for the project's deliverables or outcome.  The business case should also be regularly reviewed in conjunction with the project's progress to ensure the business objectives, which may well change during the life of the project, are still being met.  In the majority of today's projects there are invariably different groups of people involved and a structured project methodology provides a common language across all the interested parties to bring them together with the necessary controls and stages to work successfully throughout the life of the project.

Without a clearly defined project management method, those who commission a project, those who manage it and those who work on it will have different ideas about how things should be organised and when the different aspects of the project will be completed.  Those involved will not be clear about how much responsibility, authority and accountability they have and, as a result, there will often be confusion surrounding the project. Without a project management method, projects are rarely completed on time and within acceptable cost - this is especially true of large projects.

BI is always changing so I would recommend reviewing and adapting the methodology used using lessons learned from previous projects.  In summary some of the benefits of an approach like this are:

• You never lose sight of the original business goal of the project.  It is very easy to get lost in the technology and more sexy parts of a solution and forget the customers reasons for doing the project in the first place, this is especially true for long running projects.
• Planning, monitoring and control helps ensure the project is delivered on time, within budget and to the required level of quality.
• Project progress is more visible to senior management so enabling better control.
• Risks and problems are pro-actively managed so enabling better chances of project success.
• The controlled use of resources from the start to the end of projects helps to utilise these resources more efficiently.
• Changes are managed correctly without getting out of control.
• The process is repeatable; once learned and understood it is reusable on many projects increasing project success rates.
• You learn from the experience from previous projects and apply those lessons in the future.
• Actively involving stakeholders throughout the project help ensure the desired outcomes.  In the past I have seen the business engaged only at the start of the project and then not involved in the build stage then it is no wonder that what is delivered and what the customer is expecting is often very different.

A lot of this is common sense but it is always worth reminding yourself of best practice once in a while as you are only as good as your last project!

If you belong to a mid-market company you may feel a little hard done by when it comes to selecting an effective Business Intelligence platform, after all they have the same problems as larger customers face but the BI packages available are often either; lacking in features, too costly, require a long implementation time or specialist support skills. 

Common BI requirements for mid-market OR enterprise customers include being able to:

• View a single version of the truth bring data from multiple sources into one common view.
• Query data in an an ad-hoc fashion quickly and easily.
• Visualise important metrics in dash boards or score cards.
• Host data in a central repository that is accessible to all users.
• Integrate data with common tools like Microsoft Office

So why can't you get all the features of an enterprise BI solution in a smaller, more affordable package?

You can guess what's next.. you can with Business Objects Edge!

All these requirements are addressed in Edge Series 3.0.  Essentially it is their Enterprise product (XI) installed on a single CPU server with fewer user licences making it an attractive and affordable, “all in one” package.  It comes complete with licenses, support, and maintenance and has an easy upgrade path should the business expand and need either additional user licences or more features.

It's really an impressive suite of products and Business Objects have created a really neat micro-site that is worth checking out at: www.intelligenceatyourfingertips.com

The site contains more information, evaluation copies, videos, white papers, webinars and more, Altius partner with  Business Objects and are available should you require any further information, assistance with BI strategy or help with implementation.  business.objects@altiusconsulting.com

I recently completed training and supporting a large amount of users for my current project and found a neat, free little tool that can really improve the training and support materials mix.  I normally use a combination of presentations, user guides, quick one page crib sheets and still users ask for more help!  The fact is many of them don't read the materials in the first place or they find it hard to follow for certain activities, so if a picture is worth a thousand words, how many is a video worth!?

The Jing Project (www.jingproject.com) is a lightweight, quick n' easy screen capture facility for taking short 'video postcards' that you can either host for free on the web or in my case output to a file to host on my projects intranet site.  The standard FAQ pages can then be supplemented by short videos showing the user what to do rather than telling them how, the result is a much better understanding in a short time period.

When loaded it sits at the top of your screen looking like a sun, it's operation is easy, you simply select capture video and then select the size of the screen you want to capture.
	It supports voice as well and in seconds you can complete your short demo. Finally you watch a preview of what you have recorded and select the output method and your done.

It's certainly worth investigating as I have found it a real time saver.

As Microsoft is so ubiquitous in the IT world it stands to reason that most organisations that use SAP also use Microsoft products as part of their technology mix but few are aware of just how much they can use both technologies together.

I have read an excellent blog on the Microsoft site (http://blogs.msdn.com/jbasilico/archive/2008/07/08/using-sap-data-with-microsoft-s-bi-tools.aspx) that explains what options are available and how they can do it.  Of course knowing that you can do it and being able to do it are two different things entirely but luckily here at Altius we are a Microsoft Gold Partner and have worked on many large, global SAP BI projects and are perfectly positioned to implement the various options available.

So what options do you have?

• Extracting data directly from SAP R/3 or SAP BI for later reporting and analysis
• This is useful if you want to combine SAP data with other sources in Microsoft Analysis Services (SSAS) to later surface in a reporting environment such as Reporting Services.
• Extracting data from SAP BI 7.0 is going to get a whole lot easier if you use SQL Server 2008 as the latest version of Integration Services will include direct support for SAP Open Hub Services.
  
• Reporting directly off SAP BI data with Microsoft Reporting Services
• If you already use Microsoft Reporting Services you can build reports directly on SAP BI 7.0 data without staging it in SSAS first.
  
• Ability to leverage Excel directly against SAP BI 7.0 without BEx Analyzer
• I have successfully used BEx on many project and it is a powerful tool however it does have some drawbacks:
• As a separate Excel add-in it must be installed on every machine in addition to Excel causing a maintenance headache, especially when;
• The latest patch number is 18 so at times I have seen it crash quite frequently although it has been much improved recently.
• You cannot hide the design tools from pure reporting users so they sometimes get inquisitive and subsequently confuse themselves.
• Using a pure Excel environment will be much more familiar and more than capable for large sets of lightweight reporting issues.

In addition other integration opportunities include displaying SAP data on SharePoint and also embedding SAP into Microsoft Office using Duet.  It is fast becoming the case that nearly any type of integration is possible as long as you have the right skills, Microsoft are really improving the collaboration options with each new release and that can only be positive for their customers. 

Whilst attempting to save back 10 years Long Term Plan data in a planning enabled workbook I encountered the unusual situation where only the data for the years 2008 - 2011 would save back successfully.  I investigated all the usual potential causes and found nothing obvious wrong, the query was correct the InfoProvider was set to planning mode etc. so I was a little stumped.

The issue turned out to  be a setting in SPRO (shown in the screen shot below) that dictates the available time intervals.

You can also get to this transaction directly by using: RSRHIERARCHYVIRT

Upon loading this screen the time interval was set to 22/04/2011, hence why data would not save back beyond this period.  To solve the issue I increased the year from 2011 to 2020, in my particular case for Calendar Year rather than Fiscal Year.   

All data then proceeded to save as expected, remember to change this setting in each of the servers in your stack (Development, QA and Production etc.) or you will experience the same issue each time.  Its worth adding a task to your system Cutover plan.

I have been working on an unusual and complex planning workbook that has 32 separate queries embedded in it and was recently shown a quick and easy way to list all of them.  We have a naming convention to identify all the queries that should be in the workbook but this technique can be used to validate it because we all know that assumptions can be wrong!

The required steps are listed below:

1. Execute transaction SE37 to enter the Function Builder
2. Enter the function module: RRMX_WORKBOOK_QUERIES_GET
3. Click the Test/Execute button as highlighted in red below:
   
4. Enter in the workbook unique id and click the Execute button
    
   You can source the workbook id by clicking open workbook within BEx Analyzer, selecting the workbook and then viewing its properties.
   
   
5. Click the View All Entries button as highlighted in red below:
    
6. All the queries are listed!
   

An Business Intelligence solution can be perceived as expensive but does business really spend enough time calculating the value of the benefits it can bring?

I recently encountered a business that is replacing its manual Excel based financial reporting solution with a SAP BI 7.0 solution and quizzed an employee about whether he liked the proposed new system at the end of a User Acceptance Testing (UAT) session.  His answered that he would normally spend around a day manually preparing month end reports and more at quarter or year end.  It is a large department and there are many others in the same situation as him so lets do some math:

• Month end: 1 day saved x 12 = 12
• Quarter end: 2 days saved x 4 = 8
• Year end: 2 days saved
• Total potential days saved = 22 per person

As an example if there are 10 other analysts in his position then you have 220 days saved per annum - basically the cost of a full time employee.  In addition they can spend more time actually analysing data rather than just compiling and preparing it!

The solution in question will actually provide nearly all employees with access to reports that affect them and rather than just viewing huge canned reports they can filter to only the areas that they are interested in and drill down interactively.  How many days will that save and how cost savings will it help identify?

The point is that cost is easy to calculate but value is much harder, over the expected life of a BI solution it should repay itself many times over and be worth any initial outlay.  In the current economic downturn there are many situations when it would actually be more expensive to not implement a solid BI strategy than actually to do it.

If you have ever used BExSetData formulas in an Excel workbook for a planning scenario then you probably have a setup like so:

A formatted user facing worksheet containing; an area where the user enters their plan data with a BEx save data button, an area containing BExGetData formulas to either retrieve the same saved data to validate the save, bring in some other reference data or both. Plus a further hidden worksheet containing the BExSetData formulas that reference the values entered on the user facing worksheet (not shown above).

Gray cells can be protected using standard Excel functionality and white cells are open for input. A hidden column to the left of the product list would contain the technical names or keys for each product. The functional buttons are added using the BEx Analyzer design toolbar.

Whilst this solution works well it can be quite complicated to setup and maintain in large complex workbooks.  The other downside is you are required to have two separate areas for saving and retrieving data - you can't have BExGetData formulas in the area you want to save, as soon as you entered any values the formulas would be overwritten and lost.  Another issue is that unless you have activated offline mode for your data providers the BEx formulas are shown as #NV (no value) when you open an offline workbook and are only converted back to numeric data after a refresh.

Another method that I have used in this scenario, providing you have the VBA skills, is to dynamically generate the BExGet/SetData formulas and use the same area for retrieving AND saving data.  When the refresh button is clicked the BExGetData formula are generated, executed and the cell values updated, on save BExSetData formulas save the updated values in those cells.

In this way there doesn't have to be any formulas in the workbook; this is advantageous for reporting workbooks that need to be shared with users who do not have BEx Analyzer installed. Once built the VBA coding is remarkably generic and can be applied to many situations, all that is required is that the relevant cell ranges containing the BEx formula parameters be updated whenever new rows or columns are added or removed.  These settings can be stored on a hidden worksheet and the time spent maintaining workbooks can be dramatically decreased.

Having a strong Microsoft programming background I took this concept one step further and developed a toolbar that provided a GUI to editing all of the settings whilst also moving all custom VBA from the workbooks to the Excel add-in.  This means that only a single code base be maintained and upgrades are accomplished simply by rolling out a new add-in to the users.  The only requirement for the workbooks is that you click the Attach Macros button in the Workbook Settings section of the BEx design toolbar, this enables the VBA to call the BExGet/Set Data formulas.

I hope this inspires some you Excel VBA gurus out there to have a play around and see what can be accomplished with the BEx Analyzer API - my colleague wrote a useful post on the subject a while back, you can read it here: Using BEx Analyzer's API via VBA