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

I have recently been using SAP IP distribution based on reference data planning function to perform the allocation of budget and plan expense data based on certain key values.  In my scenario all the costs generated by shared cross company functions such as HR, IT or Transportation are allocated to lower level departments based on 'allocation keys'.  These allocation keys indicate what percentage share of the total cost each department should pay.

Using the three categories I mentioned earlier as an example the types of allocation methods you could you for each shared function are:

• HR - Total number of staff in each department
• IT - Total number of PC's used by each department
• Transportation - Total number of company cars used by the department

In some cases different measures can be used together in a formula to create more complex keys, an example would be:

• Transportation - (Total number of company cars used by the departments * 0.90) + (Total number of staff in the department * 0.10)

In the example above the total number of staff could be used as an indicator as to how to share out the company wide taxi and train fare costs.  The company cars would have by far the higher cost so that value makes up 90% of the formula and the number of staff just 10%.  This is just a simple and unrealistic example but you get the idea, I have seen accountants create so crazy formulas so it can be a powerful option if used correctly.  One of the hardest parts of this exercise is compiling all the data, accurately and in a format you can work with, once that is done you can start work in the SAP Planning Modeller.

The basic high level steps are as follows: Firstly step is to create your aggregation level on top of your (likely) Multi-Provider that will join; your financial expenses data, the reference data that contains the allocation keys used to distribute the costs fairly and the key figure that you want to populate once the distribution logic has run.   Next you can create the distribution planning functions, select Distribution by Reference Data as the type. 

Step two is to select the key figure that will contain the expense amounts you want distributed: 
(In the example I used previously this would be the shared cross company function costs)

Then select the key figure that contains the reference data distribution keys and reference values:
(In the example I used previously this would be the total number of staff, PCs or cars etc.)

	You also identify the key of reference data to read, that is specify how the reference data differs from the plan data; if you choose year form example the the logic will select all reference data that has the same values, except year.
Once the planning function is executed the total expense is distributed by all departments using the reference data keys.

There are a couple of things worth pointing out about using this method;

1. If you distribute £100 from HR costs to four departments at £25 each then behind the scenes -£100 is posted to the HR amount key figure, which is understandable and correct as otherwise your data would be doubled up.  If however you are required to report on that original figure at a later date then you need to ensure you have taken a copy of it first, this can easily be achieved in another planning function and the two can be linked together in a planning sequence - you just need to be careful in your reporting that you don't report these and the distributed values together in the same report for the aforementioned reasons. 
2. It's worth noting that if you allocate by percentages and the total sums to over 100% then that total is taken as 100 and all other values weighted accordingly, for example: 10,90,20 = 120, so the new weighted values are: 8.3, 75, 16.7 = 100.

And I'll finish with a few troubleshooting tips I picked up:

1. Ensure that key of reference data is correct whenever the structure of the underlying InfoProviders changes.
2. Don't go for the big bang approach! Once the planning function is built create a test query on a small sub-set of data and validate it works first.
3. Any changes to InfoObjects used by the underlying InfoProvider that your Aggregation Level is built on will cause it to de-activate.

I have recently completed some functional testing running SAP on a remote Citrix Web Server and was pleasantly surprised by the results.  Citrix software enables software to be hosted on central servers and run remotely by the users without having to install anything except the Citrix client application.

After starting the remote copy of the SAP GUI I was able to login to my usual SAP development system and run all of my usual BI related transactions without any issues.  The one thing I was most impressed with was the ability to run transaction RRMX with some custom parameters, open Citrix versions Excel and BEx Analyzer, automatically open a workbook and connect to my SAP BI server - potentially all without Microsoft Office or any SAP software installed on my local machine.

I imagine that System Administrators types will be drooling at the prospect of having thousands of users working on thin client laptops without a plethora of software to install and maintain.  Company wide standardisation of software and versions would be as easy as installing or patching a single software instance. 

There are some downsides; if the Citrix server is down so is the entire workforce and the central server would have to have some serious power to cope with so many users at once, plus you would be paying for both SAP and Citrix licenses (I have no idea what they cost).  If you want to transfer files then you have to use a shared drive rather than your own desktop.

In summary I think that for certain groups of light use users it could be a good and viable option but a one size fits all company wide approach is unlikely to be suitable for everyone.

In the past week I created a input enabled query in BEx Analyzer to save back long term plan data over a 10 year period.  To accomplish this I used an offset variable range from the current year to current year +10, simple enough.  I then inserted the query into an Analysis Grid in an Excel workbook and used the convert to formula function as I wanted to adjust the layout of the query, moving each years associated rows into a different locations within the workbook. 

Then something odd happened, after converting to formulas all the BExGetData cells were returning #NV (no value) with the exception of the current year!?  Also when attempting to save data to all future years using BExSetData formulas nothing would save successfully.  I re-inserted an Analysis Grid, entered some values into all years and used the Save Values function from the right-click context menu, as I had originally expected the data saved successfully, so the problem was isolated to retrieving and saving data using formulas.

After some unsuccessful troubleshooting attempts I escalated the issue to SAP, a few days later I got a reply - it was a bug and it is fixed in Front End Patch (FEP) 18, OK so where can I get hold of that... well actually it's not released yet.  For reference I am using Excel 2003 and BEx Analyzer 7.0 with Front End Patch 15.02.  As you can imagine this was not an ideal response, on the one hand I was amused that I had found a new bug so far in to the products release, hopefully a rare occasion by now, I must be using the product in a way that others don't - bleeding edge stuff!  On the other hand it is worrying that at even at this high patch level they haven't got it right, I have higher hopes for new new Business Objects product line.

In case its of interest I found a solution to my problem using FEP 15.02, in the end I had to create 10 separate queries one for each year, calendar year offset +1, +2, +3 etc.  and that seemed to do the trick functionally, the obvious downside is having to create and maintain 10 queries rather than 1.  As it happens even if the patch was available I doubt I could of used it, my client uses a company wide standard patch version that has been tried and tested and you can't just rollout a new patch to hundreds of users without a serious amount of admin and time lag.

A neat trick to easily access a custom ABAP program built in SE38 is to add a custom transaction in SE80.

To do this select Repository Browser, then Program from the drop down list and enter the ABAP program name.  Next right-click on the Transaction folder and select Create, finally enter in the custom transaction code in the resulting dialog box.

Then anytime you want to access the program just enter the transaction code, simple and effective.