Polestar in the cloud Web Services API - Sample Codes Ted Ueda

If you're interested in Polestar in the cloud and the recently announced Polestar in the cloud API $20K IDEAtion Challenge, then this blog entry may be of interest to you. I'm going to present here a few sample codes for that API, so you'll quickly get up to speed and have a head start on the competition!

Each of the sample codes follow the same workflow: retrieve data, upload the dataset to OnDemand, then view it in Polestar in the cloud. What differs is the programming language used and the data source. The four samples are:

1. Python script with inline dataSet.
2. C# with data retrieved from Query as a Web Service
3. Xcelsius SDK - Adobe Flex component.
4. Java with RSS Feed data.

Polestar in the cloud API

Ty Miller, a Techical Director here, sent me email about a month ago asking whether I was interested in checking out a cool new Web Services API for the BusinessObjects Labs project called Polestar in the cloud. I figured whatever Ty's involved in is high on the hot-and-interesting scale - he's sent a few pretty interesting Customer Support cases my way in the past - but at the time I was busy helping customers and working with Crystal Reports for Eclipse 2.0 and Universe Data Access Driver Development Kit SDK, so I had to flag his email for later action.

Rob Horne, who's also into pretty interesting stuff, recently blogged about Polestar in the cloud. Reading his blog certainly encouraged me to check it out (although I have to say I'm far from a Steve Nash fan), and what I found was pretty impressive.

Polestar makes the relationship between you and your data highly personal - I've gotten tremendous insight, and seen relationships I've not thought of, merely by uploading data and working with it in Polestar. Having Polestar as part of the "Cloud" computing on Whohar - the http://create.ondemand.com/ portal - makes it that much more compelling. Now you can upload and store your data on Whohar, and analyze it using different dashboards as well as Polestar.

Programmatic dataset management is accomplished via the Whohar RESTful Web Services API - you can upload, download, update and delete datasets via simple HTTP calls. You can then invoke a URL to view a dataset in Polestar.

The beauty of the API is in its simplicity - you're able to code against it using most programming languages out there, and have a very elegant means to integrate your app with Polestar in the cloud.

Rob kindly sent me a preview of the Whohar API docs - it's now available publicly here - and I've been playing with the API since. I've cleaned up some of my sample codes and present it here for your enjoyment. I do hope it helps you in quickly working out the API workflow required to integrate your stuff with Polestar in the cloud.

Python Script with Inline DataSet

Here's a short Python script that I used to get a 'feel' for the API -

Python script uploading data to Polestar in the cloud
#!/usr/bin/env python # # whohar_polestar.py # # Upload data to Whohar and view in Polestar in the cloud #=============================================================================== import base64 import os import urllib2, cookielib import webbrowser # # URL to Whohar Dataset API and Logon Credentials # url = 'https://create.ondemand.com/v1/datasets' username='<ondemand>' password='<ondemand>' # # Dataset to upload to Whohar # data = '''<?xml version="1.0" encoding="UTF-8"?> <dataset name="Test Data" visibility="private"> <source>test data</source> <description> Test data for Polestar OnDemand </description> <tags> <tag name="Test Data"> </tags> <table> <fields> <field name="Text Field" type="string"> <qualification type="dimension"> </field> <field name="Boolean Field" type="boolean"> <qualification type="dimension"> </field> <field name="Number Field" type="number"> <qualification type="measure" aggregation="average"> </field> <field name="Currency Field" type="currency"> <qualification type="measure" aggregation="sum"> </field> <field name="Date Field" type="date"> <qualification type="measure" aggregation="max"> </field> <field name="Time Field" type="time"> <qualification type="measure" aggregation="min"> </field> <field name="DateTime Field" type="dateTime"> </fields> <rows> <r> <v>Adam Ant</v> <v>false</v> <v>1</v> <v>123.45</v> <v>2007-04-25</v> <v>23:15:30</v> <v>2007-04-25T23:15:30</v> </r> <r> <v>Betty Boop</v> <v>true</v> <v>2</v> <v>246.8</v> <v>2008-10-16</v> <v>10:30:00</v> <v>2008-10-16T10:30:00</v> </r> <r> <v>Cowboy Carl</v> <v>false</v> <v>3</v> <v>369.12</v> <v>2001-11-02</v> <v>12:24:00</v> <v>2009-10-10T10:10:00</v> </r> <r> <v>Danger Dan</v> <v>false</v> <v>4</v> <v>4812.16</v> <v>1999-01-03</v> <v>12:21:00</v> <v>2010-10-10T10:10:00</v> </r> </rows> </table> </dataset>''' # # Log on and send data to Whohar # datasetURL = None # Define HTTP Basic Authentication header string base64string = base64.encodestring('%s:%s' % (username, password))[:-1] authheader = "Basic %s" % base64string # Define HTTP Cookie container - used to retrieve Session ID cookieJar = cookielib.CookieJar() opener = urllib2.build_opener(urllib2.HTTPCookieProcessor(cookieJar)) # Construct HTTP request with authentication header req = urllib2.Request(url) req.add_header("Authorization", authheader) # Send dataset to Whohar. HTTP Response to successful upload is HTTP Code 201. # Python throws an exception on 201, so we trap and retrieve content Location. try: handle = opener.open(req, data) except IOError, e: if e.code != 201: raise datasetURL = e.read() datasetURL = datasetURL[datasetURL.index("http"):-1] # Parse URL for the dataset ID id = datasetURL[datasetURL.rfind('/') + 1 :] # Retrieve Session ID from HTTP Cookie session=None for cookie in cookieJar: if cookie.name == '_whohar_session' : session = cookie.value # # Open Polestar OnDemand with dataset ID and session ID # polestar_url='https://create.ondemand.com/polestar/show_exploration/' + id + '?whohar_session_id=' + session # Open a web browser with the Polestar OnDemand URL #webbrowser.open_new(polestar_url) # I use Cygwin, for which webbrowser isn't defined, so I # open the browser manually: os.system('"/cygdrive/c/Program Files/Internet Explorer/iexplore.exe" ' + '"' + polestar_url + '"')

that shows the basics of uploading a dataSet and viewing it in Polestar: the required XML dataset format, setting up the HTTP Basic Authentication, retrieving the dataSet ID and session ID, and redirecting to Polestar in the cloud.

Edit the top of the file to insert your create.ondemand.com logon credentials, and modify the bottom to use either the webbrowser lib or explicit launching of a web browser, and run it using the Python interpreter.

You'll note how simple the API is - the advantage of using a RESTful Web Services design.

C# With Data Retrieved from Query as a Web Service

A bit more useful example, using C# - I wrote it on Visual Studio 2008 - that consumes data from Query as a Web Service (QaaWS), upload them to Whohar and view in Polestar in the cloud. Here's a screenshot of what you get, showing both the QaaWS client with the query, and the Polestar view of the data:

The query is based on the eFashion sample Universe, and Polestar is displaying the breakdown of sales revenue, quanitity of merchandise sold, and margin for stores by State. Texas is looking pretty good (more on Texas later)...

Here's the code:

C# 2008 to upload QaaWS-generated data to Polestar OnDemand
using System; using System.CodeDom; using System.CodeDom.Compiler; using System.Collections.Generic; using System.Collections.ObjectModel; using System.IO; using System.Linq; using System.Net; using System.Reflection; using System.ServiceModel.Description; using System.Text; using System.Web; namespace QaaWS2Polestar { /* Call and retrieve data from QaaWS and send to Polestar OnDemand via Whohar (https://create.ondemand.com) * * Sample Usage: * * QaaWS2Polestar.exe http://tueda-bexi3.crystald.net:8080/dswsbobje/qaawsservices/?WSDL&cuid=Af6QUpRFAexMhAuYi2onVtQ Administrator "" MyWhoharUsername MyWhoharPassword * */ class Program { static void Main(string[] args) { if (args.Length <> <cms> " + "<cms> <polestar> <polestar>"); } string qaaws_url = args[0]; string qaaws_username = args[1]; string qaaws_password = args[2]; string polestar_username = args[3]; string polestar_password = args[4]; /* * QaaWS invocation variables. */ object rows; int fetchedrows; Type rowType; PropertyInfo[] rowProperties; /* * Polestar OnDemand Web Services variables. */ string whohar_url = "https://create.ondemand.com/v1/datasets"; HttpWebResponse httpWebResponse; string polestar_data_param; string document_id; string session_id; string polestar_display_url = "https://create.ondemand.com/polestar/show_exploration/"; /* * Call QaaWS and retrieve data as Array and determine the properties of the element type. */ rows = retrieveQaaWSData(qaaws_url, qaaws_username, qaaws_password); fetchedrows = ((Array)rows).GetLength(0); rowType = rows.GetType().GetElementType(); rowProperties = rowType.GetProperties(); /* * Construct data XML from data retrieved from QaaWS */ polestar_data_param = "<?xml version=\"1.0\" encoding=\"UTF-8\"?> " + " <dataset name="\" visibility="\"> " + " <source>test data</source> " + " <description> " + " Test data for Polestar OnDemand " + " </description> " + " <tags> " + " <tag name="\"> " + " </tags> "; polestar_data_param += "<table> "; /* * For each column, specify name, type, aggregation and qualification * from type. */ polestar_data_param += "<fields> "; for (int j = 0; j < rowproperty =" rowProperties[j];" name =" rowProperty.Name;" type =" rowProperty.PropertyType.Equals(typeof(double?))" aggregation =" rowProperty.PropertyType.Equals(typeof(double?))" qualification =" rowProperty.PropertyType.Equals(typeof(double?))" name="\" type="\"> "; polestar_data_param += " <qualification type="\" aggregation="\"> "; polestar_data_param += "</field> "; } polestar_data_param += "</fields> "; /* * Copy data from QaaWS into data XML */ polestar_data_param += "<rows> "; for (int i = 0; i < row =" ((Array)rows).GetValue(i);">"; for (int j = 0; j < value =" rowProperties[j].GetValue(row," value =" ((System.DateTime?)value).Value.ToString(">" + (value == null ? "" : value.ToString()) + "</v>"; } polestar_data_param += "</r>"; } polestar_data_param += "</rows></table></dataset> "; /* * Send data to Polestar, then retrieve document ID and Session ID */ httpWebResponse = getHttpPOSTResponse(whohar_url, polestar_data_param, polestar_username, polestar_password); session_id = httpWebResponse.Cookies["_whohar_session"].Value; document_id = httpWebResponse.Headers["Location"]; document_id = document_id.Substring(document_id.LastIndexOf('/') + 1); System.Diagnostics.Process.Start("iexplore.exe", polestar_display_url + document_id + "?whohar_session_id=" + session_id); } /* * Send HTTP POST Request to URL and return the response. */ static HttpWebResponse getHttpPOSTResponse(string url, string data, string username, string password) { HttpWebRequest webRequest; CookieContainer cookieContainer; byte[] dataBytes; //Disable Expect: 100-continue header //System.Net.ServicePointManager.Expect100Continue = false; /* * Define cookie container to persist HTTP Cookies across request redirection. */ cookieContainer = new CookieContainer(); /* * Encode HTTP POST body content and POST to the url. */ dataBytes = Encoding.UTF8.GetBytes(data); webRequest = (HttpWebRequest)HttpWebRequest.Create(url); webRequest.CookieContainer = cookieContainer; webRequest.Method = "POST"; webRequest.ContentType = "text/xml"; webRequest.ContentLength = dataBytes.Length; if (username != null) { /* * HTTP Basic Authentication */ webRequest.Credentials = new NetworkCredential(username, password); } webRequest.GetRequestStream().Write(dataBytes, 0, dataBytes.Length); webRequest.GetRequestStream().Close(); try { return (HttpWebResponse) webRequest.GetResponse(); } catch (WebException webException) { WebResponse webResponse; webResponse = webException.Response; StreamReader streamReader = new StreamReader(webResponse.GetResponseStream()); String body = streamReader.ReadToEnd(); streamReader.Close(); throw webException; } } /* * Call the Query As A Web Service defined by the WSDL found at the specified URL with the * specified logon credentials, and return an array of dotNetBeans (instances of class with * properties). */ static object retrieveQaaWSData(string qaaws_url, string qaaws_username, string qaaws_password) { /* * Variables used to specify QaaWS as a Service Reference */ MetadataExchangeClient metadataExchangeClient; MetadataSet metadataSet; WsdlImporter wsdlImporter; ServiceEndpoint serviceEndpoint; System.ServiceModel.Channels.Binding binding; /* * Variables used to compile the Service Reference generated code from QaaWS */ ServiceContractGenerator generator; CodeCompileUnit codeCompileUnit; CompilerParameters compilerParameters; CodeDomProvider codeDomProvider; CompilerResults compilerResults; /* * Variables representing the generated QaaWS objects */ Assembly qaawsAssembly; Type qaawsType; Type qaawsHeaderType; Type qaawsParamType; object qaawsHeader; object qaawsParam; object qaaws; /* * Retrieve the QaaWS WSDL and generate code as a Service Reference. Note that we could * have used the simpler Web Reference, but Service Reference is programmatically nicer, * since we can use Reflection introspect the QaaWS parameters and infer meaning from the * property names. */ metadataExchangeClient = new MetadataExchangeClient(new Uri(qaaws_url), MetadataExchangeClientMode.HttpGet); metadataExchangeClient.ResolveMetadataReferences = true; metadataSet = metadataExchangeClient.GetMetadata(); wsdlImporter = new WsdlImporter(metadataSet); // Import the endpoint and binding info that's needed when instantiating the QaaWS client object. serviceEndpoint = wsdlImporter.ImportAllEndpoints()[0]; binding = wsdlImporter.ImportAllBindings()[0]; generator = new ServiceContractGenerator(); foreach (ContractDescription contract in wsdlImporter.ImportAllContracts()) { generator.GenerateServiceContractType(contract); } /* * Compile the QaaWS code. */ codeCompileUnit = generator.TargetCompileUnit; // System.ServiceModel.dll isn't found by default, so manually find and update reference. for (int i = codeCompileUnit.ReferencedAssemblies.Count - 1; i >= 0; i--) { string assemblyName = codeCompileUnit.ReferencedAssemblies[i]; switch (assemblyName) { case "System.ServiceModel.dll": codeCompileUnit.ReferencedAssemblies[i] = typeof(ServiceContractGenerator).Assembly.Location; break; default: break; } } compilerParameters = new CompilerParameters(); compilerParameters.GenerateExecutable = false; compilerParameters.GenerateInMemory = true; codeDomProvider = CodeDomProvider.CreateProvider("CSharp"); compilerResults = codeDomProvider.CompileAssemblyFromDom(compilerParameters, new CodeCompileUnit[] { codeCompileUnit }); if (compilerResults.Errors.Count > 0) { string errorMessage = "Error Compiling .NET Proxy Code Generated from the WSDL"; foreach (CompilerError error in compilerResults.Errors) { errorMessage += error.ErrorText + "\n"; } throw new Exception(errorMessage); } /* * Load the generated assembly and create the QaaWS client, QaaWS header object, and QaaWS * parameter objects. */ qaawsAssembly = compilerResults.CompiledAssembly; qaawsType = qaawsAssembly.GetType("QueryAsAServiceSoapClient", false); qaawsHeaderType = qaawsAssembly.GetType("QaaWSHeader", false); qaawsParamType = qaawsAssembly.GetType("runQueryAsAService", false); if (qaawsType == null || qaawsHeaderType == null || qaawsParamType == null) { throw new Exception("QaaWS Web Service not found."); } // Note: since we don't have a app.config file with the generated QaaWS binding and endpoint // info, we pass it into the constructor. qaaws = qaawsAssembly.CreateInstance(qaawsType.Name, false, BindingFlags.Default, null, new object[] { binding, serviceEndpoint.Address }, null, null); qaawsHeader = qaawsAssembly.CreateInstance(qaawsHeaderType.Name); qaawsParam = qaawsAssembly.CreateInstance(qaawsParamType.Name); // Set the QaaWS logon credentials. If the QaaWS has prompts, you would Reflect for // the prompt property and set them here. qaawsParamType.GetProperty("login").SetValue(qaawsParam, qaaws_username, null); qaawsParamType.GetProperty("password").SetValue(qaawsParam, qaaws_password, null); return qaawsType.GetMethod("runQueryAsAService").Invoke(qaaws, new object[] { qaawsHeader, qaawsParam, null, null, null, null, null, null, null, null, }); } } }

You'll note that much of the code is taken up by the QaaWS SOAP Web Service call - dynamically generating stub code from the WSDL, auto-compiling and loading the assembly, and using Reflection to invoke the service - and not by the OnDemand API.

It may be an unfair comparison, but this does suggest the simplicitly of a RESTful Web Service in comparison to SOAP.

Compile the code using Visual Studio 2008, and run it in the command-line:

QaaWS2Polestar.exe

and it'll launch Internet Explorer with Polestar OnDemand view of your QaaWS data.

A couple of comments about the sample code:

1. It generates the QaaWS stubs from the WSDL using .NET Service Reference rather than Web Reference. A Service-Reference-generated-code makes for easier Reflection into the stub classes, since you can infer use from the generated property names.
2. It currently doesn't set any QaaWS prompts - you can add to the Reflection routine near the end of the code to introspect any prompts.

Xcelsius SDK - Adobe Flex Component

I created this sample using Adobe Flex Builder 3 and the Xcelsius SDK. Implementation uses MXML that subclasses a mx:Button component that, when clicked, reads a bound 2D Array, constructs the Whohar dataSet XML, uploads the data and displays it in Polestar in the cloud.

You can use it within an Adobe Flash application or embedded in Xcelsius. Here's a screenshot of it embedded within Xcelsius, showing both the Polestar and Xcelsius views of the data:

When you click on the "To Polestar OnDemand" button you see on the Xcelsius view, the Polestar OnDemand view is displayed.The data is just some test data I entered into Excel in the Xcelsius designer - in actual practise, you'd be using 'real' Excel data, or a Web Service data source.

Here's the MXML code:

PolestarOnDemand.xml - uploads bound Array to Polestar in the cloud
<?xml version="1.0" encoding="utf-8"?> <mx:button mx="http://www.adobe.com/2006/mxml" label="To Polestar in the cloud" enabled="true" click="sendToPolestarOnDemand(event)" alpha="1.0" fillalphas="[1.0, 1.0]" fillcolors="[#FFFFFF, #FFFFFF]" color="#000000"> <mx:script> <![CDATA[ import flash.net.navigateToURL; import mx.controls.Alert;; import mx.managers.PopUpManager; import mx.rpc.events.FaultEvent; import mx.rpc.events.ResultEvent; import mx.utils.StringUtil; /* * Polestar OnDemand logon variables. Pass the suppress_response_codes parameter to * prohibit return of non-HTTP 200 codes (other than 5xx errors), since Flex on IE * throws exceptions on HTTP 201 Created. */ private var _psURL:String = "https://create.ondemand.com/v1/datasets?suppress_response_codes=true"; private var _psUser:String = "<user>"; private var _psPassword:String = ""; /* * Property dataArray holds the 2D Array to be sent to Polestar. */ private var _dataArray:Array = null; [Inspectable(type="Array")] public function get dataArray():Array { return _dataArray; } public function set dataArray(value:Array):void { _dataArray = value; invalidateProperties(); } /* * Begin process of sending data to Polestar OnDemand. * First query user for OnDemand Logon credentials. */ private function sendToPolestarOnDemand(event:Event):void { var logonDialog:PolestarOnDemandLogon = PopUpManager.createPopUp( this.parentApplication as DisplayObject, PolestarOnDemandLogon, true) as PolestarOnDemandLogon; logonDialog.username = _psUser; logonDialog.password = _psPassword; logonDialog.addEventListener("logonDialogOK", retrieveLogonCredentials); PopUpManager.centerPopUp(logonDialog); } /* * Retrieve the OnDemand logon credentials entered by user, and go to next step - * sending data to OnDemand. */ private function retrieveLogonCredentials(event:Event) : void { var logonDialog:PolestarOnDemandLogon = event.target as PolestarOnDemandLogon; _psUser = logonDialog.username; _psPassword = logonDialog.password; try { sendDataToPolestarOnDemand(_psURL, _psUser, _psPassword); } catch(err:Error) { Alert.show(err.message + "\n" + err.getStackTrace()); } } /* * Create the dataset XML then stream to OnDemand. */ private function sendDataToPolestarOnDemand(url:String, username:String, password:String):void { var data:String; var row:Array; var i:int, j:int; /* * Set HTTP Basic Authentication header. * Flex3 has Base64Encoder, but Flex2 does not. Since Xcelsius currently supports Flex2 only, * we use an open source Base64 library by Jason Nussbaum (http://blog.jasonnussbaum.com/?p=108). */ dataService.headers = {Authorization: "Base " + Base64.Encode(username + ":" + password)}; /* * Construct the dataSet preface info. */ data = "<?xml version=\"1.0\" encoding=\"UTF-8\"?> " + " <dataset name="\" visibility="\"> " + " <source>Xcelsius</source> " + " <description> " + " Test data for Polestar OnDemand from Xcelsius" + " </description> " + " <tags> " + " <tag name="\"> " + " </tags> "; /* * Define the dataSet. */ data += "<table>\n"; if(_dataArray.length <= 0) { throw new Error("Error: No data"); } /* * Specify column type depending on type of data. We assume column is a measure if * it's a numeric type, and dimension otherwise. Aggregation is set to 'sum' if * numeric, and 'none' otherwise. */ data += "<fields>\n"; row = _dataArray[0]; for(j = 0 ; j < string =" (row[j]" string =" (row[j]" string =" (row[j]" name="\" type="\"> "; data += " <qualification type="\" aggregation="\"> "; data += "</field> "; } data += "</fields> \n"; /* * Add the data in <r><v>ColValue1</v><v>ColValue2</v></r> format. */ data += "<rows>\n "; for(i = 0 ; i < row =" _dataArray[i]">"; for(j = 0 ; j <>" + (row[j] == null ? "" : entify(row[j].toString())) + "</v>"; } data += "</r>\n"; } data += "</rows> \n" + "</table> \n" + "</dataset>"; /* * Send data to OnDemand */ dataService.url = url; dataService.send(new XML(data)); } /* * Retrieve the dataset ID from the Polestar OnDemand data request * response, then hand off to method that opens a web browser to Polestar OnDemand. */ private function retrieveDocumentInfo(event:ResultEvent):void { try { var datasetId:String = null; var sessionId:String = null; var body:String = event.result.toString(); datasetId = body.substring(body.lastIndexOf("/")+ 1, body.length - 1); if(datasetId == null || StringUtil.trim(datasetId).length == 0) { throw new Error("Data Error: missing dataset ID."); } // Open web browser with Polestar OnDemand showing document. openBrowserWithPolestarOnDemand(datasetId); } catch(err:Error) { Alert.show("retrieveDocumentInfo: " + err.message + "\n" + err.getStackTrace()); } } /* * Open web browser to view Polestar OnDemand with specified dataset Id. * Expectation here is that previous requests would have set the HTTP Cookie * with the OnDemand logon session ID, so that no re-logon will be required for * the redirection. */ private function openBrowserWithPolestarOnDemand(datasetId:String):void { var url:String = "https://create.ondemand.com/polestar/show_exploration/" + datasetId; var urlRequest:URLRequest = new URLRequest(url); navigateToURL(urlRequest); } /* * Make the world safe for tags to live in peace and harmony. */ private function entify(s:String):String { return s == null ? null : s.replace(/&/, "&") .replace(/</, "<") .replace(/>/, ">"); } /* * Error in sending data. */ private function dataError(event:FaultEvent):void { Alert.show("Data Error: " + event.message); } ]]> </mx:Script> <!-- HTTP Service to transmit Data to Polestar OnDemand --> <mx:httpservice id="dataService" contenttype="application/xml" resultformat="text" result="retrieveDocumentInfo(event)" fault="dataError(event)"> </mx:Button>

and the MXML for the Logon dialog box it uses to collect the OnDemand logon credentials:

PolestarOnDemandLogon.xml - Logon dialog box for PolestarOnDemand.xml
<?xml version="1.0" encoding="utf-8"?> <mx:titlewindow mx="http://www.adobe.com/2006/mxml" layout="absolute" width="332" height="134" title="Logon to Polestar OnDemand" showclosebutton="true" close="onClose(event)" backgroundcolor="#FFFFFF" alpha="1.0"> <mx:metadata> [Event("logonDialogOK")] </mx:Metadata> <mx:script> <![CDATA[ import mx.events.CloseEvent; import mx.managers.PopUpManager; public function get username():String { return usernameTextInput.text; } public function set username(value:String):void { usernameTextInput.text = value; } public function get password():String { return passwordTextInput.text; } public function set password(value:String):void { passwordTextInput.text = value; } private function okButtonClicked(event:Event):void { var okEvent:Event = new Event("logonDialogOK"); dispatchEvent(okEvent); closer(); } private function onClose(event:CloseEvent):void { closer(); } private function closer():void { usernameTextInput.text = ""; passwordTextInput.text = ""; PopUpManager.removePopUp(this); } ]]> </mx:Script> <mx:label y="12" text="User:" textalign="right" left="10" width="82"> <mx:label y="38" text="Password:" textalign="right" left="10" width="82"> <mx:textinput y="10" left="100" right="20" id="usernameTextInput" displayaspassword="false" editable="true" enabled="true"> <mx:textinput y="34" left="100" right="20" id="passwordTextInput" displayaspassword="true" editable="true" enabled="true"> <mx:button y="64" label="OK" id="okButton" enabled="true" click="okButtonClicked(event)" horizontalcenter="12"> </mx:TitleWindow>

Note that the data the component uploads is bound to an 2D array. Here's a very simple Xcelsius Property Sheet MXML that will allow you to do the binding to Excel cell ranges from within Xcelsius:

PolestarOnDemandPropertySheet.mxml - Xcelsius Property Sheet for PolestarOnDemand.xml
<?xml version="1.0" encoding="utf-8"?> <mx:application mx="http://www.adobe.com/2006/mxml" layout="absolute" borderthickness="3" bordercolor="#ff0000" applicationcomplete="init()"> <mx:style source="style\PSStyle.css"> <mx:script> <![CDATA[ import xcelsius.binding.BindingDirection; import xcelsius.binding.tableMaps.input.InputBindings; import xcelsius.binding.tableMaps.output.OutputBindings; import xcelsius.propertySheets.interfaces.PropertySheetFunctionNamesSDK; import xcelsius.propertySheets.impl.PropertySheetExternalProxy; // Proxy for this Property Sheet and its Xcelsius custom component. protected var proxy:PropertySheetExternalProxy = new PropertySheetExternalProxy(); // Holds name of property currently being (re)binded. protected var propertyToBind:String; // Holds ID of property currently being (re)binded. protected var currentBindingID:String; protected function init():void { // Register callback for bind completion. proxy.addCallback(PropertySheetFunctionNamesSDK.RESPONSE_BINDING_ID, continueBind); // Notify Xcelsius that proxy initialization is complete. proxy.callContainer(PropertySheetFunctionNamesSDK.INIT_COMPLETE_FUNCTION); // Fill the controls with the current values of the component. initValues(); } /* * Initialize property values being displayed in Property Sheet */ protected function initValues():void { // Retrieve properties to initialized var propertyValues:Array = proxy.getProperties(["dataArray"]); /* * For each property, set the relevant UI component field values. */ for (var i:int=0, m:int = (propertyValues == null ? 0: propertyValues.length) ; i < object =" propertyValues[i];" string =" propertyObject.name;" bindingtext =" getPropertyBindDisplayName(propertyName);" enabled =" false;" text =" bindingText;//" text =" propertyValue;" array =" proxy.getBindings([propertyName]);" propertybindings ="="" length ="="" length ="="" string =" propertyBindings[0][0];" currentbindingid =" null;" array =" proxy.getBindings([propertyName]);"> 0)) { currentBindingID = propertyBindings[0]; // Use the 1st binding address for the property. } // Store the name of the property that we are binding, we need this when we "continueBinding". propertyToBind = propertyName; // Let the user choose where to bind to in the Excel spreadsheet. proxy.requestUserSelection(currentBindingID); } /* * Complete User request to bind property to Excel cells. */ protected function continueBind(bindingID:String):void { // Define common variables here. var propertyName:String = propertyToBind; var propertyValues:Array; var propertyObject:Object; var bindingAddresses:Array; // Clear any existing bindings - so we can re-bind. if (currentBindingID != null) { proxy.unbind(currentBindingID); currentBindingID = null; } // Process the property binding. switch (propertyName) { case "dataArray": // User explicitly cleared binding, do not create another. if ((bindingID == null) || (bindingID == "")) { valueEditor.enabled = true; // Re-enable manual entry. // Fill the control back with current value instead of range address. propertyValues = proxy.getProperties([propertyName]); propertyObject = propertyValues[0]; valueEditor.text = String(propertyObject.value); // Make sure we set the property on the component as well. proxy.setProperty(propertyName, propertyObject.value); return; } // Disable the option to manually enter a value once the component is bound. valueEditor.enabled = false; // Display the range address. valueEditor.text = proxy.getBindingDisplayName(bindingID); // Excel spreadsheet binding:- // // BindingDirection.BOTH - Update the custom component "value" property when the spreadsheet changes // and also update the spreadsheet when the custom component "value" changes. // InputBindings.ARRAY - Write to multiple cells in the spreadsheet. // OutputBindings.ARRAY - Read a multiple cells in the spreadsheet. // proxy.bind("dataArray", null, bindingID, BindingDirection.BOTH, InputBindings.ARRAY2D, OutputBindings.ARRAY2D); break; default: break; } } ]]> </mx:Script> <mx:viewstack y="45" id="viewstack1" height="100%" left="0" right="0"> <mx:canvas label="General" height="100%" width="100%" id="general"> <mx:label x="23" y="24" text="Data" width="119"> <mx:hrule y="28" height="10" left="59" right="28"> <mx:textinput id="valueEditor" y="50" right="69" left="37" enabled="false" change="proxy.setProperty('dataArray', (valueEditor.text))"> <mx:button y="50" right="37" width="24" click="initiateBind('dataArray');" icon="@Embed('resources/bind to cell.png')"> </mx:Canvas> </mx:ViewStack> <mx:tabbar y="0" width="635" x="0" dataprovider="viewstack1"> </mx:TabBar> </mx:Application>

You can also use the component outside of Xcelsius and within a Flex application. Here's a short sample of how you'd invoke PolestarOnDemand.mxml from a mx:Application:

PolestarOnDemandApp.mxml - embedding PolestarOnDemand.mxml component
<?xml version="1.0" encoding="utf-8"?> <mx:application mx="http://www.adobe.com/2006/mxml" xc="com.sap.xcelsius.sdk.sample.*" layout="absolute" creationcomplete="postInit()"> <mx:script> <![CDATA[ private function postInit():void { var atable:Array; atable = new Array(5); atable[0] = new Array(3); atable[0][0] = "Canada"; atable[0][1] = 22.222; atable[0][2] = 2242.33; atable[1] = new Array(3); atable[1][0] = "USA"; atable[1][1] = 122.222; atable[1][2] = 1242.33; atable[2] = new Array(3); atable[2][0] = "France"; atable[2][1] = 222; atable[2][2] = 222.33; atable[3] = new Array(3); atable[3][0] = "Germany"; atable[3][1] = 222.222; atable[3][2] = 22.33; atable[4] = new Array(3); atable[4][0] = "Australia"; atable[4][1] = 221.222; atable[4][2] = 2.33; polestarOnDemand.dataArray = atable; } ]]> </mx:Script> <xc:polestarondemand id="polestarOnDemand"> </xc:PolestarOnDemand> <mx:style source="com/sap/xcelsius/sdk/sample/style/MyStyle.css"> </mx:Application>

Some comments:

1. The code passes the parameter suppress_response_codes in the Whohar URL to have responses always return HTTP 200 (other than HTTP 5xx errors). This feature was added specifically for Flex running in Internet Explorer. The normal RESTful HTTP 201 (Created) return on new dataset creation throws an exception in Internet Explorer, preventing Flex from reading the returned new resource location.
2. Xcelsius SDK currently supports Flex 2 only - so in Flex Builder 3, ensure you set the compiler for your project to use version 2 rather than 3.
3. Flex 2 does not contain a library class for Base64 encoding - I used one of the many open source Base64 implementations for ActionScript available on the web.
4. Xcelsius supports MXML components via the Xcelsius SDK only starting with FixPack 1.1 (previously, you had to code using ActionScript). I tested this component using Service Pack 1 + FixPack 1.2.
5. I didn't Style the component for Xcelsius - if you want to specify fonts and colors for the component in Xcelsius, you'd need to specify styling in the code. I recommend looking at the ComboBox sample for an example on how to property style a Xcelsius SDK component.

Java With RSS Feed Data

Last example - retrieve data from Yahoo! HotJobs RSS Feed, upload and view in PoleStar OnDemand. I always wanted to be a spaceman, so I enter 'astronaut' for the HotJobs keyword, and this is the Polestar in the cloud result:

All the astronaut-related jobs are located in Texas! Most in Houston (as in "Houston, we've got a problem") and a couple in Galveston. So that means I have to be willing to relocate if I want to fulfill my childhood career aspiration. Oh, well, I think I better stick to more realistic keywords like 'programmer', 'developer', or 'cowboy'. Maybe I should learn to ride a horse first...

Anyways, here's the Java code:

JobLocationPolestar.java - Polestar in the cloud with data from Yahoo! HotJobs RSS Feed
package com.sap.ondemand.whohar.sample; import com.sun.syndication.feed.synd.SyndEntry; import com.sun.syndication.feed.synd.SyndFeed; import com.sun.syndication.io.FeedException; import com.sun.syndication.io.SyndFeedInput; import com.sun.syndication.io.XmlReader; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.net.URL; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.Map; import javax.net.ssl.HttpsURLConnection; /* * Retrieves Yahoo Job Listing RSS given keyword and uploads data to Business Objects OnDemand, * then opens view of data in Polestar in the cloud. * * Usage: * java com.sap.ondemand.whohar.sample.JobsLocationPolestar <keyword> <ondemand> <ondemand> * * For example, java com.sap.ondemand.whohar.sample.JobsLocationPolestar cowboy ondemanduser ondemandpass */ public class JobsLocationPolestar { public static void main(String[] args) throws Exception { final String YAHOO_RSS_URI = "http://hotjobs.yahoo.com/job-rss-k-"; String keyword; URL yahoo_rss; List<entry> entries; SimpleDateFormat dateFormat; StringBuilder data; final String ONDEMAND_URI = "https://create.ondemand.com/v1/datasets"; final String POLESTAR_URI = "https://create.ondemand.com/polestar/show_exploration/"; String username = args[1]; String password = args[2]; String authHeader; sun.misc.BASE64Encoder base64Encoder; HttpsURLConnection urlConnection; byte[] dataBytes; byte[] temp; ByteArrayOutputStream baos; int count; String dataSetID; Map<string,>> headers; List<string> cookies; String sessionID; String polestar_url; // DateTime string format for uploading data to OnDemand dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss"); /* * Call Yahoo Job Listing RSS and retrieve list of entries found. */ keyword = URLEncoder.encode(args[0], "UTF-8"); yahoo_rss = new URL(YAHOO_RSS_URI + keyword); entries = getYahooRSS(yahoo_rss); /* * Create dataSet string for uploading to OnDemand */ data = new StringBuilder(); data.append(data_beginning); data.append("<rows>\n"); for(Entry entry : entries) { data.append("<r>\n"); data.append("<v>" + entry.uri + "</v>\n"); data.append("<v>" + entry.title + "</v>\n"); data.append("<v>" + entry.link + "</v>\n"); data.append("<v>" + entry.region + "</v>\n"); data.append("<v>" + entry.city + "</v>\n"); data.append("<v>" + (entry.publishedDate == null ? "" : dateFormat.format(entry.publishedDate)) + "</v>\n"); data.append("</r>\n"); } data.append("</rows>\n"); data.append(data_ending); /* * Specify the HTTP Basic Authentication header. */ base64Encoder = new sun.misc.BASE64Encoder(); authHeader = "Basic " + base64Encoder.encode((username + ":" + password).getBytes("UTF-8")); dataBytes = data.toString().getBytes("UTF-8"); /* * Send data to OnDemand */ urlConnection = (HttpsURLConnection) (new URL(ONDEMAND_URI)).openConnection(); urlConnection.setDoInput(true); urlConnection.setDoOutput(true); urlConnection.setRequestMethod("POST"); urlConnection.setRequestProperty("Authorization", authHeader); urlConnection.setRequestProperty("Content-length", "" + dataBytes.length); urlConnection.setRequestProperty("Content-Type", "text/xml"); urlConnection.getOutputStream().write(dataBytes); /* * Ensure succesful data upload by ensuring HTTP Status Code 200. */ if(urlConnection.getResponseCode() != 201) { throw new Exception("Failed dataSet upload.\n" + "HTTP " + urlConnection.getResponseCode() + ": " + urlConnection.getResponseMessage()); } /* * Parse the response for the dataSet ID */ temp = new byte[1024]; baos = new ByteArrayOutputStream(); while((count = urlConnection.getInputStream().read(temp)) > 0) { baos.write(temp, 0, count); } dataSetID = new String(baos.toByteArray(), "UTF-8"); dataSetID = dataSetID.substring(dataSetID.lastIndexOf('/') + 1, dataSetID.length() - 1); /* * Parse the returned HTTP Cookies for the session ID. */ headers = urlConnection.getHeaderFields(); cookies = headers.get("Set-Cookie"); sessionID = null; for(String cookie : cookies) { if(cookie.startsWith("_whohar_session")) { sessionID = cookie.split(";", 2)[0].split("=", 2)[1]; break; } } /* * Redirect to Polestar OnDemand with dataSet ID and session ID */ polestar_url = POLESTAR_URI + dataSetID + "?whohar_session_id=" + sessionID; Runtime.getRuntime().exec("\"c:\\Program Files\\Internet Explorer\\iexplore.exe\" " + "\"" + polestar_url+ "\""); } /* * OnDemand dataSet upload data format - the beginning part defines the column type, qualification * and aggregation. */ private static String data_beginning = "<?xml version=\"1.0\" encoding=\"UTF-8\"?> " + " <dataset name="\" visibility="\"> " + " <source>test data</source> " + " <description> " + " Test data for Polestar OnDemand " + " </description> " + " <tags> " + " <tag name="\"> " + " </tags> " + " <table> " + " <fields> " + " <field name="\" type="\"> " + " <qualification type="\"> " + " </field> " + " <field name="\" type="\"> " + " <qualification type="\"> " + " </field> " + " <field name="\" type="\"> " + " <qualification type="\"> " + " </field> " + " <field name="\" type="\"> " + " <qualification type="\"> " + " </field> " + " <field name="\" type="\"> " + " <qualification type="\"> " + " </field> " + " <field name="\" type="\"> " + " <qualification type="\"> " + " </field> " + " </fields> "; private static String data_ending = " </table>\n" + "</dataset> "; /* * Call the Yahoo Job Listing RSS, parse the results and return list of RSS entries returned. */ private static List<entry> getYahooRSS(URL yahoo_rss_url) throws FeedException, IOException { List<entry> entries; SyndFeedInput syndFeedInput; entries = new ArrayList<entry>(); syndFeedInput = new SyndFeedInput(); SyndFeed syndFeed = syndFeedInput.build(new XmlReader(yahoo_rss_url)); for(Iterator<syndentry> ientry = syndFeed.getEntries().iterator(); ientry.hasNext(); ) { SyndEntry syndEntry; String title; syndEntry = ientry.next(); title = syndEntry.getTitle(); // Parse title for location. Pattern is "— city1, region1 • city2, region2" // Note that the separator is dash (—)and not hyphen (-) for(String location : title.substring(title.lastIndexOf('—')+1).split("•")) { Entry entry; String[] cityRegion; cityRegion = location.split(","); entry = new Entry(); entry.uri = syndEntry.getUri(); entry.title = title; entry.link = syndEntry.getLink(); entry.publishedDate = syndEntry.getPublishedDate(); entry.city = cityRegion.length <= 1 ? "" : cityRegion[0].trim().toUpperCase(); entry.region = cityRegion.length <= 1 ? cityRegion[0].trim().toUpperCase() : cityRegion[1].trim().toUpperCase(); entries.add(entry); } } return entries; } static class Entry { public String uri; public String title; public String link; public String city; public String region; public Date publishedDate; } }

It's a command-line app where the first argument is the HotJobs keyword and second and third arguments are the OnDeman logon credentials.

It uses the Project ROME RSS Java library to retrieve the feed.

Whohar itself has a RSS feed reader, so you don't necessarily have to use the API to upload RSS entry data into Whohar. For this task, however, I wanted to post-process the data after retrieving it from Yahoo! and before uploading. Specifically, I wanted to parse the Job Title fields to derive the job location city and State.

Summary

The Polestar in the cloud and Whohar API has such ease-of-use that you can call it from almost any application. In this blog entry, I illustrated how you'd upload data and display it in Polestar using four different programming languages.

I'm sure you'll be able to think of much more compelling uses for it than the examples I've given above. If so, I highly recommend immediately going to the Polestar in the cloud API $20K IDEAtion Challenge site and signing yourself up for a chance to win some recognition for your idea! The cash would be a nice bonus, too...

Hope you find this entry informative, and the sample codes allow you to quickly get up-to-speed with the Polestar in the cloud API.

Ted Ueda is a Senior Engineer with Technical Customer Assurance, SAP Business Objects.