Analytics – out of the box: SAP Business Analytics

SAP finally announced on September 14, 2010 that it was getting onto the pre-packaged analytics bandwagon.  SAP announced ten applications in this first release for six industries (Consumer Products, Healthcare, Financial Services, Public Sector, Retail and Telecommunications) in its BusinessObjects  offering.

Building on the rapid-marts offering that the then BOBJ used to have and leveraging SAP’s industry and line of business expertise, these new applications are based on the SAP Business Objects XI platform – WebIntelligence, Crystal Reports and Dashboards (formerly Xcelsius). Bill McDermott, the joint CEO of SAP, described it as “complete and ready-to-go” and claimed the applications can be deployed in as less as eight weeks.

You may remember the brouhaha created by SAS last year , when it kicked off the controversy on Business Analytics being the future, rather than Business Intelligence. Going back even further, Oracle already had this in its Siebel Analytics pre-built analytic applications for various industries. Therefore, it would seem that SAP is already late in the game, but considering that neither Microsoft nor IBM have similar offerings, it may not be too bad for SAP. Better late than never…

Under the hood:

The pre-packaged analytic applications are based on the BusinessObjects XI platform – with the universe as the semantic layer or metadata model. It can be based on both SAP and non-SAP data, OLTP and data warehouse, relational and unstructured.  SAP would work with its partners HP and Teradata to optimize the analytic solutions on their hosting and data warehousing solutions.

Business Analytics dashboards are Xcelsius flash files which can be used with web services/QAWS to deliver real-time analytics. It may also be possible to use these with SAP Business Objects Explorer (formerly Polestar) and/or SAP BW Accelerator or the SAP high-performance analytic appliance (HANA).

Business Analytics vs. Business Intelligence – Revisiting the controversy:

When SAS created this controversy last year, an important point noted by many was the SAS home page titled:

SAS | Business Intelligence Software and Predictive Analytics

It’s important to see how the rebranding has reflected in a change to the SAS home page a year hence. It now reads:

SAS | Business Analytics and Business Intelligence Software

SAS Institute was always viewed as a niche vendor, operating in the pure-play statistical and predictive analytics space and this marketing was to re-brand SAS’ offerings to move it mainstream.  In effect, it signaled the market assessment by these major vendors, that in tough times, customers were seeking shorter lead times and demanding better tools which are quick and easy to introduce and provide quicker return on investment.  As we come around the downturn, with SAP still focusing on this segment, it is clear that traditional BI is clearly seen as complex, costly and difficult to implement.

Open questions:

There are several questions open at the moment, given that this is an initial launch. SAP plans to offer more applications over the next year-18 months in collaboration with customers and its partners.  The partners include Aster Group, Blueprint, Capgemini, Column5, CSC, Fusion Consulting, The Glenture Group, LSI Consulting and syskoplan and surely it would take quite a while for the ecosystem to develop.  It remains to be seen whether the prepackaged analytics catches on like Xcelsius dashboards did for BOBJ.

It is not clear whether the prepackaged analytics would be positioned at the bigger enterprises or the SME segment only, as its success could cannibalize revenues from the flagship Enterprise XI suite.

There are also questions around the scalability of the framework the analytic applications are built on. The extensibility APIs and reference architectures for partners to build their own add-ons and plugins / applications  of their own is not yet out (planned in 2011), so it’s not quite like the iPhone/iPad app store yet. It is also not clear how customizations to the applications would be supported or to what extent these could be customized.  The long awaited universe rewrite including data federation might be a part of plans if the analytic applications turn out to be truly backend-agnostic and do support future in-memory data structures (SAP’s acquisition of Sybase would indicate likely support for the Sybase ASE in-memory database). If this happens, it would be in line with earlier plans to roll-out in-memory EPM and OLTP solutions.

Export data out of Xcelsius dashboards

Accessing data in Xcelsius is pretty easy. You can have static data loaded from Excel, or dynamic data loaded through Web Services, Excel XML Maps, QaaWS (Query as a Web Service), Crystal Reports, Web Intelligence Reports or queries using Live Office.

There are however only a few ways to export data from Xcelsius. Either we could use Flash variables or use the XML Data Button to export data out of the compiled .swf file.

Here I present a simple method to export data from an Xcelsius .swf to a csv/Excel file. This method utilizes the XML Data Button’s capability to send and receive XML data.

The source code is provided at the end of this post as a download. Copy the code from the PDF and save as export2csv.aspx on the web server.

Note that this is an ASP.Net (.aspx extension) file, which works with an IIS Web Server. If you wish to use this code, please remember to change the links to point to your webserver and the appropriate directories.
For being able to write the exported data to a directory, you may need to grant Write access to the IUSR_<MachineName> account to allow anonymous access.

After you’ve placed the export2csv.aspx file on your webserver, perform the following steps in your XLF file.
1. Add an XML data button [ Xcelsius 4.5 ]/Connection Refresh button [ Xcelsius 2008 ] and check the “Enable Send” box. Make sure that in the MIME Type dropdown, “application/x-www-form-urlencoded” is selected.

2. Type out the URL of the export2csv.aspx file in the XML Data URL and add the ranges of data that you need to export. The screenshot above shows 2 ranges being exported.
3. Check the “Enable Load” box and add a single cell range (Sheet1!$J$1 in the example) to receive the URL of the CSV file that will be returned by the export2csv.aspx file.
Note that the XML variable name returned by the export2csv.aspx is exportdataurl, so you need to name the range the same. If you wish to change the name, you’ll need to update the same in the export2csv.aspx file.

4. Add a URL link button and set its URL to the cell defined in the previous step (Sheet1!$J$1)
5. Export the .swf file and try out exporting the data by clicking on the Connection Refresh Button and then opening the file by clicking on the URL link Button.

Now you can easily export data from any .swf that you’ve created in Xcelsius.

How does this work?
1. The XML Data Button exports the ranges you’ve selected in an XML file to our export2csv.aspx file.
2. Our export2csv.aspx file parses and saves this XML in a .csv format on the web server.
3. The path of the .csv file is returned to the .SWF file through the “Enable Load” range.
4. The URL Link component retrieves the URL from this cell and opens the .CSV file when clicked.

Source code:

<%
'-----------------------------------
'Name: export2csv.aspx
'Author: Maloy Manna
'Date: 04.26.2008
'Purpose: To export data out of Xcelsius .swf in csv format
'Input: Xcelsius data manager Send ranges in XML format
'Output: CSV output file URI
'Last Modified:
'Modification:
'-----------------------------------
'Variable declarations
Dim sXMLString as String
Dim sFormitem as String
Dim sFileName as String
Dim sRPath as String
Dim sPath as String
Dim sXML as String
Dim iRangeLength As Integer
Dim iRowLength As Integer
Dim iRangeCounter As Integer
Dim iCounter As Integer
Dim oFile as File
Dim oWrite as StreamWriter
Dim oXML as Object
Dim oRNodeList as Object
Dim oRNode as Object
Dim oNodeList as Object
Dim oNode as Object
'Get XML that was sent from SWF file
For Each sFormItem In Request.Form
sXMLString = sXMLString & sFormItem & " = " & Request.Form(sFormItem)
Next
'Load XML into object
oXML = Server.CreateObject("MSXML2.DOMDocument")
oXML.async = False
oXML.validateOnParse = False
oXML.loadXML(sXMLString)
'Create output file with timestamp in the name
sFileName = "Exported_Data_" & DatePart("m",Now()) & DatePart("d",Now()) & DatePart("yyyy",Now()) & DatePart("h",Now()) & DatePart("n",Now()) & DatePart("s",Now()) & ".csv"
oWrite= oFile.CreateText("C:\Inetpub\wwwroot\" & sFileName)
'Use XPath to get the data
'Find the number of ranges or variables to be exported
sRPath ="/data/variable"
oRNodeList = oXML.documentElement.selectNodes(sRPath)
iRangeLength = oRNodeList.Length
For iRangeCounter = 0 to iRangeLength - 1
oRNode = oXML.documentElement.selectSingleNode("/data/variable["+CType(iRangeCounter,String)+"]/row")
'Write out the data set for the range
sPath = "/data/variable["+CType(iRangeCounter,String)+"]/row"
'Find the number of rows for the range
oNodeList = oXML.documentElement.selectNodes(sPath)
iRowLength = oNodeList.Length
For iCounter =0 to iRowLength - 1
oNode = oXML.documentElement.selectSingleNode("/data/variable["+CType(iRangeCounter,String)+"]/row["+CType(iCounter,String)+"]/column")
oWrite.Write(oNode.text)
While Not(oNode.nextSibling Is Nothing)
oWrite.Write("," +oNode.nextSibling.text)
oNode = oNode.nextSibling
End While
oWrite.WriteLine
Next
'Write a blank line before the next range starts
oWrite.WriteLine
Next
oWrite.Close()
'Build XML data to return
sXML = ""
'exportdataurl
sXML = sXML & ""
sXML = sXML & ""
sXML = sXML & "" & "http://localhost/" & sFileName & ""
sXML = sXML & ""
sXML = sXML & ""
'close out the XML data string
sXML = sXML & ""
'Set the Response type
Response.Buffer = True
Response.ContentType = "application/vnd.csv"
Response.Write(sXML)
%>


Download code as PDF: Code for export2csv.aspx

Update on 06 September 2009:

Several of you have asked me for the JSP version of the export code. Unfortunately, I don’t have the expertise to do this in JSP, but fortunately Luca Spinelli and Ivan Aguilar from ClearPeaks have created the JSP version. The .war file is available for free if you  send an email to info@clearpeaks.com (I’m unable to include the .war file it here due to lack of compressed file extension support in WordPress). Please get in touch with ClearPeaks, if you want the source code.

- Maloy

What’s new in Xcelsius 2008 – Top 10 features

Business Objects has just released the updated Xcelsius 2008 after a long beta program. What are the top new features of this exciting data visualization and dashboard tool? Let’s find out the top 10:

1. Copy-Paste - Productivity is bound to increase through collaborative development now as it is possible to copy-paste components between XLF files.

2. Filtered Rows – Selector components like List box and combo-box now include a new option in the insert-options list. This is the “Filtered rows” option, automatically removing duplicates. In the previous version, this worked only with the Filter component.

3. Color-scheme – The new-look Xcelsius 2008 has some amazing pre-built color schemes, including “auto-matching” of colors and allowing color configuration of backgrounds, text, buttons, charts, selectors, single value components, maps and scroll bars. A notch above the existing Xcelsius with its garish and glossy color schemes. 3 new themes – Admiral, iTheme and Nova have also been added to the existing set.

4. History component – This is a very useful new feature which captures a single-cell value and inserts it into a contiguous row/column of data, thus allowing the designer to track changes based on data refreshes, user selections etc. Used with XML Data connection, this can be used for exporting data snapshots out of Xcelsius.

5. Trend Analyzer component – This component automatically inserts linear, logarithmic calculations into charts, tables, and other components at runtime. You can choose the order of the polynomial. Alternatively, you can let Xcelsius decide the best fit for the trend.

6. Dual Y-Axis option – A long standing wish of the Xcelsius developer community, the secondary axis charting has now been made available on line, column, stacked column, bar, stacked bar, combination, chart, area, and stacked area charts. Earlier, the workarounds included using multiple copies of the chart and overlaying them with clever formatting and dynamic visibility.

7. Alerts in selector components – This feature allows configuring selectors e.g. combo-box, list-box etc to display alerts icon for each label. It also has a new “Middle values are good” option for percent alerts in the color-order section.

8. Data Manager – A redesigned development interface now includes a new Data Manager, which consolidates the various possible external data sources including QaaWS, XML data, portal, Live Office, XML maps, flash variables, crystal reports, Adobe Life cycle data services and web services. There is also a general option available to import data from a spreadsheet in the Business Objects Enterprise.

9. Slick new IDE – The new IDE is created using Adobe Flex and includes an embedded Excel sheet which improves productivity by allowing the designer to make changes to the model within the IDE, rather than Alt-Tab-bing between Excel and Xcelsius. There are several one-touch Quick-views available – the integrated workspace, canvas only, Excel only and both canvas-and-Excel. And all those floating bars can be pinned or auto-hidden. While dragging and dropping, scroll arrows appear over the toolbars, and it takes a little while to adjust to the way you can dock these bars.

10. Component management – Has been made easier in that you can select individual components within a group and modify its properties, including sending it back or forward within the group. Hiding and locking of components and groups have also been made very easy.

What’s more:

* It is no longer a pain to go through the uninstall-reinstall process to add a license. You can update your trial by entering the license from Help – License Manager.

* New containers like the Panel component with scroll bars and the Tab component make it easy to build component-heavy visualizations without having to worry too much about precious screen space or rely too much on dynamic visibility.

* You can build your own custom components using Adobe Flex with the Xcelsius SDK.

Get the Xcelsius 2008 with a 60 day developer keycode at Diamond where you can also download the Xcelsius 2008 Component SDK.

What’s less:

* Even with the redesign, Xcelsius retains its memory-hogging issues. A blank XLF with an embedded Excel easily takes up around 300 MB RAM, out of which 80MB is of the Excel alone.

* A Switch-To/Retry dialog is common during launching Xcelsius, so much so that hiding it has been made a part of File – Preferences – Excel Options – “Always Hide” checkbox.

* The Export icons are difficult to make sense of and have inconsistent tool-tips (Powerpoint, PDF and Word do not have any, while Outlook and Business Objects platform do.)

* Installation is to “C:\Program Files\” by default without letting you select any location. If you wish to change the location, you’ll need to modify a registry entry as David Taylor points out in his blog.

I hope to tinker around a bit more with the new look Xcelsius 2008 and find out how it does in the performance and scalability departments – aspects which become increasingly important gaging by the enthusiasm of enterprises embracing it as a tool for dashboards and scorecards.

~biguru

SaaS BI – Software as a service model in Business Intelligence

SaaS has taken off in a big way in the past few years. And BI has not been lagging behind. For leading vendors like BOBJ (an SAP company) or Cognos (an IBM company), it’s going to be close to 2 years now since they started their SaaS BI.

What exactly are the advantages of SaaS over the traditional approach?

Benefits are aplenty, from zero costs of purchasing hardware, hiring of key IT personnel like system administrators and DBAs, to minimal implementation and maintenance costs. With CRM being made affordable to SME (small and medium-sized enterprises) by salesforce.com (note the huge success of its AppExchange), and the fact that large enterprise adoption of BI was complete, meant that the only scope for future growth in BI would come from upgrades of platforms (analogous to Microsoft’s Windows operating system – think of BOBJ upgrading from 6.5 to XI) or from expansion in the SME space with better offerings.
In fact, with Google distributing its Apps, Serena jumping on to the bandwagon with its project and portfolio management (PPM) software, and even Tata Consultancy Services launching its own ITaaS (IT as a service), SaaS is almost confirmed to be the software delivery model of the future.

The marginal cost of acquiring SME customers is reduced due to the nature of the SaaS service, and this has encouraged more and more BI companies to venture into exploring this delivery model for BI. There are niche players well entrenched in this SaaS BI space: LucidEra, Oco, Information Builders and PivotLink – and a few of them provide the entire range of BI – including ETL and not limited only to reporting. But now the bigger fish have also ventured into this space. Going by the success of BOBJ’s crystalreports.com or its Information OnDemand (a project which biguru was involved in developing) and IBM Cognos Now!, there seems no doubt over the future direction that BI delivery is headed. Did I mention almost all of these can work together with Salesforce?

As much as SaaS BI is redefining delivery mechanisms from the perspective of the vendors, it also involves a paradigm shift for the customers. Finally, it is less a question of technology, than it is about the benefits to the business, the ROI and focusing on the core competency of the enterprise. In a sense, SaaS ‘outsources’ all of the IT to the vendor, leaving business to deal with issues like implementation and managing requirements. It is also a pointer to the maturing of the industry – from discovering requirements to providing relevant information and insight.

The technology implications for the vendors are daunting. For once, they need to expand in domains outside their core areas, from maintaining data centers to ensuring bug-free roll-outs, even though they’ll also have the luxury of not having to maintain multiple version stacks. On the web, users become far more demanding, and are far less willing to break their heads while figuring out why something won’t work due to a missing dll or patch etc.

Improvements (both aesthetics, ease of use and navigation) in the user interface, as well as performance requirements have already seen a sort of standardization on Adobe Flash as the medium of delivery for dashboards. Even though AJAX/Javascript is also in the picture, the complexity of coding and longer cycle times seems to be tilting the scales in favor of Flash. As user experience gets paramount, BOBJ has launched its latest visualization offering Xcelsius 2008 (version 5) which also includes an SDK for interoperability with Adobe Flex, to build sites like Information OnDemand.
With BI getting more business-process centric, SAP is now focused on embedding BI capabilities in its range of offerings. Business Objects is currently tinkering with TAWS (Take Action as a Web Service) in its Labs, which would allow its dashboards to use web services to plug into the service-oriented-application-architecture. With respect to hot-plugging into the SOA architecture of BI, however, Oracle has made great strides and even IBM Cognos is better placed.

The future of SaaS BI and enterprise BI SOA seems interesting indeed.