Hi,

 

I have submitted an idea on SAP Idea Incubator on custom Stock In Transit Report :

 

The main idea behind the report is to help the purchasing managers get a detailed view of the status of stock in transit in a way which will be useful to them. The current SAP function MB5T does not provide key information to buyers as they need it. The report will help the buyers know exactly in what status their order is currently. There can be more functionality built in to the report.

 

If some one is interested in developing this idea, I will be more than happy to provide him with more inputs.

 

Here is the link with more details of the idea.

 

https://ideas.sap.com/SAPHANAIdeaIncubator/stock-in-transit-report-for-intercompany?current_tab=Recent&row_num=1&getparameters=1

 

Eagerly awaiting your feedback.

 

Regards,

Amit

Hello All,

 

In this blog, I have explained about how to insert records into SAP HANA from a reporting tool like Web Intelligence. This concept can be used in most reporting tools like Dashboards, Lumira & Design Studio to insert data into SAP HANA.

 

Please find below steps to acheive this.

 

 

1. Create table in sap HANA

 

Create column table with name T_B_SALES with below structure in SAP HANA.

pic1.png

 

 

2. Create SAP HANA Repository

 

 

Goto Window -> Show View from Top Menu Bar

 

Select SAP HANA Repositories.

 

pic2.png

 

 

 

Right click on the Repository Area & create new repository.

 

pic3.png

 

 

Give Repository Name and click finish.

 

pic4.png

 

You will see the Repository successfully created in SAP HANA.

pic5.png

 

 

  3. reate XS Application

 

Go to Window -> Show View from Top Menu Bar and select Project Explorer.

pic6.png

 

 

4. In Project Explorer Create New XS Project

 

 

Right click in Project Explorer Area New-> Project ->SAP HANA Development->XS Project.

pic7.png

 

Give Project Name & Click on Finish. Now you will see a XS Project folder created in Project Explorer.

pic8.png

 

5. Create .xsapp & .xsaccess files in XS Project

 

Right Click on XS Project folder and select ‘file’ option.

pic9.png

 

 

  Give file name as .xsapp. Keep this file blank. This file is for XS engine to understand there is an XS application in this package.

pic10.png

 

Similarly create .xsaccess file & write below code in it.

pic11.png

 

 

 

Right Click on our XS Project XS_BOOK_STORE and create XS JavaScript Source File.

 

pic12.png

 

 

 

Name it as “insert_purchase_record.xsjs” and write below code in it.

  var cid = $.request.parameters.get('cid');

  var bname = $.request.parameters.get('bname');

var no_of_copies = $.request.parameters.get('no_of_copies');   
$.response.headers.set(
'access-control-allow-origin','*');

  var conn = $.db.getConnection();

   var pstmt = conn.prepareStatement("INSERT INTO \"UNAME\".\"T_B_SALES\"
values ('"
+cid+"','"+bname+"',current_date,"+no_of_copies+")");

var rs = pstmt.execute();

   $.response.setBody( "Entry has been created Successfully.");

  }

 

  conn.commit(); 

  conn.close();

 

Note: UNAME is username in SAP HANA

 

 

Share Project

Right Click On Project and goto Team-> Share Project

Once your Project is shared small Yellow cylinder will appear at Project Logo.

pic12_1.png

 

 

Now Select all the 3 files (.xsapp,.xsaccess and insert_purchase_record.xsjs) and Activate the files.

pic15.png

 

 

 

Then all files will show the small Yellow cylinder at file logo after successful activation of files.

pic14.png

 

 

6. Create a WEBI report which will write data back to SAP HANA.

Create WEBI report with No data Source. As we do not want to see any data, and need to insert data into HANA DB.

 

Create 3 Variables in BO as below

pic15.png

 

 

 

Define 3 input control Text Boxes for each of variables.

pic16.png

pic17.png

 

 

 

Create Report with above variables as below. Try to articulate it to appeal to users.

pic18.png

 

 

 

Do not forget to select cell property of insert cell to Hyperlink, as below.

pic19.png

 

 

Now, right click On the “Insert” Text, Goto Linking->Hyperlink and put below code in window & parse it.

 

http://<HANA_SERVER>:<PORT>/XS_BOOK_STORE/insert_purchase_record.xsjs?cid=B0000001&bname=INDIA&no_of_copies=1 

 

  PORT: Mostly = 8000 (for HANA XS ENGINE)

pic20.png

 

 

Click on parse. Below window will now appear,

 

pic21.png

 

 

Now assign the BOBJ variables to URL parameters, as below.

 

pic22.png

 

 

7. Execute the BO Report

 

Now let’s test our XS application..!

 

Click on insert, this will take you to the browser and will prompt you to enter HANA password.

pic23.png

 

  After entering the password and clicking OK it you show you following window.

pic24.png

 

 

 

Now Check HANA table T_B_SALES. You will see one entry created in table..!!

 

pic25.png

 

 

You can now also try to update and delete the records in HANA DB. You can use the same table, on which we are performing DML operations, as the BOBJ report source and can see the records gets change at WEBI Report level at runtime.

 

Hope you find this worth of reading..!! Thanks

HANA is all-sufficient as a platform. It has everything out of the box to develop applications - DB, XS Engine, SAPUI5.

Even if HANA mostly considered for enterprise application's development it is also can be used as a box for public web applications/sites as well.

In this text below  I'm going to talk about some points to have in mind when you start doing non-enterprise systems on HANA.

 

So what is usual web application in terms of user access?

  1. everybody can access it
  2. soon or later you will be forced to login into application somehow to get the service
  3. login via social networks is widely used and can be considered as de-facto standard

 

Lets look at what is HANA proposes in user access area. Currently HANA supports following authentication methods:

For non-interactive logon:

    1. X509 authentication
    2. SPNego
    3. SAP logon ticket
    4. Basic authentication

For interactive logon:

    1. SAML
    2. Form-based authentication

And 'None' for public applications.

This is one more proof for the statement 'HANA is still mostly enterprise-oriented'

The only thing we can take from the above list for out use case is ability to make public applications. All the rest must be done manually.

 

Custom session management (login step) can look like:

01.login.png

 

I think it is clear that during any next call from UI to backend sessionId must be provided as a cookie and validated on HANA server.

 

This approach is nice but there is one issue here - sessionId generation.

Lets think how we can do that. Actually there are two ways: do it on our own or try to reuse token/cookies from social network.

Expanding first way we can:

  1. on client side:
    1. Math.random()
    2. jQuery.sap.uid()
    3. other jQuery/JS plugins, libs
  2. on XS Engine side:
    1. same as in #1 except jQuery(unless you ported jQuery on XS )
  3. on HANA side:
    1. SELECT SYSUUID FROM DUMMY;
    2. SELECT RAND() FROM DUMMY;
    3. some magic like: populate table with 'random' values via #1 or 2 and 'randomly' select from this table

With all of these points there is one huge issue - they are not designed to be used as session Id generators. They are producing values with low entropy.

This contradicts to basic requirements for session id generation (some details can be found here).


Moving to second way. Reusing token/cookie (provided by social network) directly is not really good idea as well. Because for example it can change over the time (Facebook can validate current FB cookie and issue new one still pointing to the same user's session). All we can do here is add some salt and apply hash function. We could if were provided with api by HANA team


So there is no direct, obvious way to generate reliable, strong sessionId from HANA applications code. But there is tricky workaround as usual

Approach is the following:

  1. create destination pointing to localhost
  2. create empty password-protected xsjs service
  3. create technical user with access to package with password-protected service
  4. call protected service via destination passing userName/password
  5. extract sessionId (and csrf protection token if needed) from response cookies/headers

As simple as that


Simplest implementation can look like:

folders.png

utils.xsjs listing:


function generateSessionIdAndToken() {
    var destination = $.net.http.readDestination("training.dlapanik.SessionId", "localhost");
    var client = new $.net.http.Client();
    var request = new $.net.http.Request($.net.http.GET, "/training/dlapanik/SessionId/private/getSessionId.xsjs");
    request.headers.set("Authorization", "Basic U2Vzc0lkVGVjaFVzZXI6SW5pdGlhbDIzNA===");
    request.headers.set("X-CSRF-Token", "Fetch");
    client.request(request, destination);
    var response = client.getResponse();
    if (response.status === $.net.http.OK) {
        $.response.status = $.net.http.OK;
        return {
            sessionId : response.cookies.get("xsSessionId"),
            csrfToken : response.headers.get("x-csrf-token")
        };    
    } else {
        return {
            status : "not OK:" + response.status
        };
    }
}



Here we see pure XSJS Outbound API usage.

There only question can occur is about header with name 'Authorization'. Actually this is the way to pass user name/password into the service call. The value you see after 'Base' is base64 function from user/pass combination. For example for SessIdTechUser:Initial234 you will get value U2Vzc0lkVGVjaFVzZXI6SW5pdGlhbDIzNA==. I used Base64 Decode and Encode - Online to create authorization token providing <user_name>:<password> as input.

 

Full project code can be found on github. Please note that example will work in case you activate it into 'training.dlapanik' package. In case package is diferent please adjust the code.

 

All in one slide:

sessionIdGeneration.png

 

 

To test launch http://<host>:<port>/training/dlapanik/SessionId/utils.xsjs, will get json object like

 

sessionIdGenerationTesting.png

 

So summarize: in this article we saw how to 'ask Hana' to generate sessinoId value for public non-enterprise HANA applications.

You can find the series and project from Real-time sentiment rating of movies on SAP HANA (part 6) - wrap-up and looking ahead

 

 

 

Intro

Hi everyone, welcome to the movie world again. In the previous blog post, we've used SAP HANA Info Access to build our smart app on desktop. With the simplicity of Info Access Dev Toolkit and its underneath text analysis feature in SAP HANA, it's possible for us to build such a powerful and fancy app in a short time. However, the UI framework of Info Access Dev Toolkit is more focused on the desktop instead of tablet or phone. When I used my iPhone to visit the app, the UI looked like the following.

 

27.PNG28.PNG

 

Obviously the UI is not responsive as you can see, since it's for big screens. Usually we don't analyze so many things at the same time on our mobiles, as mobiles are for simple operations. It's crazy for you to show six charts and one list in only one screen on your mobile with drag and drop features. Do you like that? I think nobody likes. Since the scenario of our movie sentiment rating app is very simple, an idea came to my mind, how about build our app on mobile devices? And you can use it to pick up the best movie when you go to the cinema. It sounds cool. Let's do it together!

 

OData services

When we use SAP HANA Info Access, we just need an attribute view for the date layer. Since now we want to use SAPUI5 to build our movie app on mobile devices, first we need to expose few OData services for models in SAPUI5. Here I won't explain what OData is and how to use OData in SAP HANA XS, but you can find details from Home | OData - The Protocol for REST APIs and Data Access with OData in SAP HANA XS - SAP HANA Developer Guide - SAP Library

 

In our case, we want to create the following two OData services. (It seems the syntax highlighting still doesn't work. Sorry for the ugly formatting again. )

 

1. movies.xsodata

service namespace "movieRating.services" {
"movieRating.models::CV_MOVIES" as "CV_MOVIES"
key generate local "GenID"
parameters via entity "InputParams"
;}

















 

The above OData service exposes the calculation view with input parameters described in Real-time sentiment rating of movies on SAP HANA (part 3) - text analysis and modeling. Since there are huge amounts of movies, we need to give two input parameters in order to get the limited results, one for start date and the other for end date. It will be used for displaying brief movie info including the movie title, poster, release date, rating and # of mentions. We can get the brief movie info in mention descending order with release date from 2014-11-11 to 2014-11-17 as follows.

 

30.PNG

 

2. tweets.xsodata

service namespace "movieRating.services" {
"movieRating.models::AT_TWEETS" as "AT_TWEETS"
with("movie_id", "user_screen_name", "user_profile_image_url", "sent", "created_at_str", "text", "id_counter")
key generate local "GenID"
;}
















 

For this OData service, it's based on the attribute view in Real-time sentiment rating of movies on SAP HANA (part 3) - text analysis and modeling with limited attributes. We'll use it to show related mentions when we click a specific movie. For each mention, we want to show the user name, his/her profile image, the creation time of the tweet, the text and detected sentiment. We can also test the OData service to get the latest 20 mentions given a specific movie ID.

 

31.PNG

 

SAPUI5 - sap.m

Now let's use SAPUI5 to build the responsive UI for our movie app. So what is SAPUI5? SAPUI5 stands for SAP UI Development Toolkit for HTML5 and you can find details from SAPUI5 Developer Guide for SAP HANA - SAP Library. Since we want to build our movie app on mobile devices, we need to build views with sap.m library instead of sap.ui.commons library. So what is the difference between these two libraries? sap.ui.commons is for desktop/big screen and it's not responsive, meanwhile sap.m is for desktop/tablet/phone and it's responsive which means sap.m can adapt to the mobile OS, browser and screen size. You can also find more from this unit of "Next Steps in Software Development on SAP HANA" on openSAP. I just learned this unit today.

 

Structure

We decided to use sap.m to build our mobile app, but how can we start? First I highly recommend you to have a look at Model View Controller (MVC) - SAPUI5 Developer Guide for SAP HANA - SAP Library, since MVC is a very important concept in SAPUI5.

 

32.PNG

 

And you can also find a very detailed demo from An MVC Demo Application - SAPUI5 Developer Guide for SAP HANA - SAP Library. It's a perfect demo for MVC concept in SAPUI5 and the structure is similar with ours. The following image shows the structure of our mobile app.

 

29.PNG

 

  • The "appView" only holds sap.m.App control to handle the page navigation between two pages "homeView" and "detailView".
  • The "homeView" is displayed initially with two controls. sap.m.DateRangeSelection is used for date range selection, while sap.m.Carousel is used to show the brief movie info.
  • The "detailView" will be showed when we click a movie in the carousel. It consists of several controls including sap.m.PullToRefresh and sap.m.List. With sap.m.PullToRefresh, we can pull to refresh sap.m.List to show the latest mentions. In addition, we also add the option "Load more mentions" which let the user to load more old mentions.

 

Implementation

index.html

<!DOCTYPE HTML>
<html>
  <head>
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta http-equiv='Content-Type' content='text/html;charset=UTF-8'/>
  <link rel="stylesheet" type="text/css" href="styles/style.css" />
  <script src="/sap/ui5/1/resources/sap-ui-core.js"
  id="sap-ui-bootstrap"
  data-sap-ui-libs="sap.m"
  data-sap-ui-theme="sap_bluecrystal">
  </script>
  <!-- only load the mobile lib "sap.m" and the "sap_bluecrystal" theme -->
  <script>
  sap.ui.localResources("movieratingmobile");
  var app = sap.ui.view({id:"appView", viewName:"movieratingmobile.app", type:sap.ui.core.mvc.ViewType.JS});
  app.placeAt("content");
  </script>
  </head>
  <body class="sapUiBody" role="application">
  <div id="content"></div>
  </body>
</html>


app.view.js

sap.ui.jsview("movieratingmobile.app", {
  /** Specifies the Controller belonging to this View.
  * In the case that it is not implemented, or that "null" is returned, this View does not have a Controller.
  * @memberOf movieratingmobile.app
  */
  getControllerName : function() {
  return "movieratingmobile.app";
  },
  /** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.
  * Since the Controller is given to this method, its event handlers can be attached right away.
  * @memberOf movieratingmobile.app
  */
  createContent : function(oController) {
  var home = sap.ui.view({id:"homeView", viewName:"movieratingmobile.home", type:sap.ui.core.mvc.ViewType.JS});
  var detail = sap.ui.view({id:"detailView", viewName:"movieratingmobile.detail", type:sap.ui.core.mvc.ViewType.JS});
  return new sap.m.App("app").addPage(home).addPage(detail);
  }
});








 

app.controller.js

sap.ui.controller("movieratingmobile.app", {
/**
* Called when a controller is instantiated and its View controls (if available) are already created.
* Can be used to modify the View before it is displayed, to bind event handlers and do other one-time initialization.
* @memberOf movieratingmobile.app
*/
  onInit: function() {
  var date_to = new Date();
  var date_from = new Date(date_to.getTime() - 6 * 24 * 60 * 60 * 1000);
  var oView = this.getView();
  var oModel = new sap.ui.model.json.JSONModel("/movieRating/services/movies.xsodata/InputParams(date_from=datetime'" + this.dateString(date_from) + "',date_to=datetime'" + this.dateString(date_to) + "')/Results/?$format=json&$orderby=mention desc");
  oView.setModel(oModel);
  var dateRange = sap.ui.getCore().byId("dateRange");
  dateRange.setDateValue(date_from);
  dateRange.setSecondDateValue(date_to);
  this.app = sap.ui.getCore().byId("app");
  var oBus = sap.ui.getCore().getEventBus();
  oBus.subscribe("nav", "to", this.navToHandler, this);
  oBus.subscribe("nav", "back", this.navBackHandler, this);
  },
/**
* Similar to onAfterRendering, but this hook is invoked before the controller's View is re-rendered
* (NOT before the first rendering! onInit() is used for that one!).
* @memberOf movieratingmobile.app
*/
// onBeforeRendering: function() {
//
// },
/**
* Called when the View has been rendered (so its HTML is part of the document). Post-rendering manipulations of the HTML could be done here.
* This hook is the same one that SAPUI5 controls get after being rendered.
* @memberOf movieratingmobile.app
*/
// onAfterRendering: function() {
//
// },
/**
* Called when the Controller is destroyed. Use this one to free resources and finalize activities.
* @memberOf movieratingmobile.app
*/
// onExit: function() {
//
// }
  dateString: function(date) {
  var year = date.getFullYear();
  var month = date.getMonth() + 1;
  month = month > 9 ? month : "0" + month;
  var day = date.getDate();
  day = day > 9 ? day : "0" + day;
  return year + '-' + month + '-' + day;
  },
  navToHandler: function(channelId, eventId, data) {
  if (data && data.id) {
  // lazy load view
  if (this.app.getPage(data.id) === null) {
  jQuery.sap.log.info("now loading page '" + data.id + "'");
  this.app.addPage(sap.ui.jsview(data.id, "movieratingmobile." + data.id));
  }
  // Navigate to given page (include bindingContext)
  this.app.to(data.id, data.data.context);
  } else {
  jQuery.sap.log.error("nav-to event cannot be processed. Invalid data: " + data);
  }
  },
  navBackHandler: function() {
  this.app.back();
  }
});







 

home.view.js

sap.ui.jsview("movieratingmobile.home", {
  /** Specifies the Controller belonging to this View.
  * In the case that it is not implemented, or that "null" is returned, this View does not have a Controller.
  * @memberOf movieratingmobile.home
  */
  getControllerName : function() {
  return "movieratingmobile.home";
  },
  /** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.
  * Since the Controller is given to this method, its event handlers can be attached right away.
  * @memberOf movieratingmobile.home
  */
  createContent : function(oController) {
  var poster = new sap.m.Image({
  densityAware: false,
  src: {
  path: "poster",
  formatter: function(fValue) {
  return fValue.replace(/tmb/, "det");
  }
  },
  press: [oController.showDetail, oController]
  });
  var title = new sap.m.Text({
  text: "{title}"
  }).addStyleClass("title");
  var release_date = new sap.m.Text({
  text: "{release_date}"
  });
  var rating = new sap.m.RatingIndicator({
  iconSize: "12px",
  value: {
  path: "rating",
  formatter: function(fValue) {
  return parseFloat(fValue) / 2;
  }
  }
  });
  var score = new sap.m.Text({
  text: {
  path: "rating",
  formatter: function(fValue) {
  return " " + fValue;
  }
  }
  }).addStyleClass("score");
  var rating_score = new sap.m.HBox({
  items: [rating, score]
  });
  var mention = new sap.m.Text({
  text: {
  path: "mention",
  formatter: function(fValue) {
  return "(" + fValue + " Mentions)";
  }
  }
  });
  var vbox = new sap.m.VBox({
  alignItems: sap.m.FlexAlignItems.Center,
         items: [poster, title, release_date, rating_score, mention]
  });
  var carousel = new sap.m.Carousel({
  loop: true,
  showPageIndicator: false,
  pages: {
  path: "/d/results",
  template: vbox
  }
  });
  var dateRange = new sap.m.DateRangeSelection("dateRange", {
  change: [oController.changeDate, oController]
  });
  var page = new sap.m.Page({
  title: "Movie Sentiment Rating",
  content: [dateRange, carousel]
  });
  return page;
  }
});







 

home.controller.js

sap.ui.controller("movieratingmobile.home", {
/**
* Called when a controller is instantiated and its View controls (if available) are already created.
* Can be used to modify the View before it is displayed, to bind event handlers and do other one-time initialization.
* @memberOf movieratingmobile.home
*/
// onInit: function() {
//
// },
/**
* Similar to onAfterRendering, but this hook is invoked before the controller's View is re-rendered
* (NOT before the first rendering! onInit() is used for that one!).
* @memberOf movieratingmobile.home
*/
// onBeforeRendering: function() {
//
// },
/**
* Called when the View has been rendered (so its HTML is part of the document). Post-rendering manipulations of the HTML could be done here.
* This hook is the same one that SAPUI5 controls get after being rendered.
* @memberOf movieratingmobile.home
*/
// onAfterRendering: function() {
//
// },
/**
* Called when the Controller is destroyed. Use this one to free resources and finalize activities.
* @memberOf movieratingmobile.home
*/
// onExit: function() {
//
// }
  changeDate: function(oEvent) {
  this.getView().getModel().loadData("/movieRating/services/movies.xsodata/InputParams(date_from=datetime'" + sap.ui.controller("movieratingmobile.app").dateString(oEvent.getParameters().from) + "',date_to=datetime'" + sap.ui.controller("movieratingmobile.app").dateString(oEvent.getParameters().to) + "')/Results/?$format=json&$orderby=mention desc");
  },
  showDetail: function(oEvent) {
  var oBindingContext = oEvent.getSource().getParent().getBindingContext();
  var oBus = sap.ui.getCore().getEventBus();
  oBus.publish("nav", "to", {
  id: "detail",
  data: {
  context : oBindingContext
  }
  });
  }
});







 

detail.view.js

sap.ui.jsview("movieratingmobile.detail", {
  /** Specifies the Controller belonging to this View.
  * In the case that it is not implemented, or that "null" is returned, this View does not have a Controller.
  * @memberOf movieratingmobile.detail
  */
  getControllerName : function() {
  return "movieratingmobile.detail";
  },
  /** Is initially called once after the Controller has been instantiated. It is the place where the UI is constructed.
  * Since the Controller is given to this method, its event handlers can be attached right away.
  * @memberOf movieratingmobile.detail
  */
  createContent : function(oController) {
  var pullToRefresh = new sap.m.PullToRefresh({
  refresh: [oController.refreshList, oController]
  });
  var poster = new sap.m.Image({
  densityAware: false,
  src: "{poster}"
  });
  var title = new sap.m.Text({
  text: "{title}"
  }).addStyleClass("title");
  var release_date = new sap.m.Text({
  text: "{release_date}"
  });
  var rating = new sap.m.RatingIndicator({
  iconSize: "12px",
  value: {
  path: "rating",
  formatter: function(fValue) {
  return parseFloat(fValue) / 2;
  }
  }
  });
  var score = new sap.m.Text({
  text: {
  path: "rating",
  formatter: function(fValue) {
  return " " + fValue;
  }
  }
  });
  var rating_score = new sap.m.HBox({
  items: [rating, score]
  });
  var mention = new sap.m.Text({
  text: {
  path: "mention",
  formatter: function(fValue) {
  return "(" + fValue + " Mentions)";
  }
  }
  });
  var vbox = new sap.m.VBox({
         items: [title, release_date, rating_score, mention]
  }).addStyleClass("info");
  var hbox = new sap.m.HBox({
  items: [poster, vbox]
  }).addStyleClass("movieInfo");
  var oBar = new sap.m.Toolbar({
  height: "90px",
  content: [hbox]
  });
  var listItem = new sap.m.FeedListItem({
  iconDensityAware: false,
  sender: "{user_screen_name}",
  icon: "{user_profile_image_url}",
  info: "{sent}",
  timestamp: "{created_at_str}",
  text: "{text}"
  });
  var oList = new sap.m.List({
  items: {
  path: "/d/results",
  template: listItem
  }
  });
  oList.setModel(model);
  var loader = new sap.m.StandardListItem({
  type: sap.m.ListType.Active,
  title: "Load 20 more mentions...",
  press: [oController.loadMore, oController]
  });
  var page = new sap.m.Page({
  title: "Movie Detail",
  showNavButton: true,
  subHeader: oBar,
  content: [pullToRefresh, oList, loader],
  navButtonPress: [oController.showHome, oController]
  });
  this.addEventDelegate({
  onBeforeShow: function(evt) {
  this.setBindingContext(evt.data);
  oController.initializeList(evt);
  }
  }, this);
  return page;
  }
});







 

detail.controller.js

var movie_id, max_id_counter;
var model = new sap.ui.model.json.JSONModel();
sap.ui.controller("movieratingmobile.detail", {
/**
* Called when a controller is instantiated and its View controls (if available) are already created.
* Can be used to modify the View before it is displayed, to bind event handlers and do other one-time initialization.
* @memberOf movieratingmobile.detail
*/
// onInit: function() {
//
// },
/**
* Similar to onAfterRendering, but this hook is invoked before the controller's View is re-rendered
* (NOT before the first rendering! onInit() is used for that one!).
* @memberOf movieratingmobile.detail
*/
// onBeforeRendering: function() {
//
// },
/**
* Called when the View has been rendered (so its HTML is part of the document). Post-rendering manipulations of the HTML could be done here.
* This hook is the same one that SAPUI5 controls get after being rendered.
* @memberOf movieratingmobile.detail
*/
// onAfterRendering: function() {
//
// },
/**
* Called when the Controller is destroyed. Use this one to free resources and finalize activities.
* @memberOf movieratingmobile.detail
*/
// onExit: function() {
//
// }
  showHome: function() {
  var oBus = sap.ui.getCore().getEventBus();
  oBus.publish("nav", "back");
  },
  initializeList: function(oEvent) {
  movie_id = new sap.ui.model.json.JSONModel(oEvent.data).getProperty("/oModel/oData" + oEvent.data.sPath + "/id");
  model.loadData("/movieRating/services/tweets.xsodata/AT_TWEETS/?$format=json&$filter=movie_id eq " + movie_id + "&$top=20&$orderby=id_counter desc", null, false);
  this.updateMax(model);
  this.getView().getContent()[0].getContent()[0].setDescription("Last updated time: " + new Date().toUTCString());
  },
  refreshList: function(oEvent) {
  model.loadData("/movieRating/services/tweets.xsodata/AT_TWEETS/?$format=json&$filter=movie_id eq " + movie_id + "&$top=20&$orderby=id_counter desc", null, false);
  this.updateMax(model);
  this.getView().getContent()[0].getContent()[0].setDescription("Last updated time: " + new Date().toUTCString()).hide();
  },
  loadMore: function() {
  var mentionModel = new sap.ui.model.json.JSONModel();
  mentionModel.loadData("/movieRating/services/tweets.xsodata/AT_TWEETS/?$format=json&$filter=movie_id eq " + movie_id + " and id_counter lt '" + max_id_counter + "'&$top=20&$orderby=id_counter desc", null, false);
  var mentions = this.updateMax(mentionModel);
  for (var i in mentions) {
  this.getView().getContent()[0].getContent()[1].addItem(new sap.m.FeedListItem({
  iconDensityAware: false,
  sender: mentions[i].user_screen_name,
  icon: mentions[i].user_profile_image_url,
  info: mentions[i].sent,
  timestamp: mentions[i].created_at_str,
  text: mentions[i].text
  }));
  }
  },
  updateMax: function(model) {
  var mentions = model.getProperty("/d/results");
  max_id_counter = mentions[mentions.length - 1].id_counter;
  return mentions;
  }
});







 

Look & feel

1. Homepage displaying new release movies info (e.g., rating and # of mentions) within carousel in the last seven days

33.PNG

 

2. Swipe the carousel to see various movies

34.PNG

 

3. Click a movie to see the latest 20 tweet mentions/sentiments of this movie

35.PNG

 

4. Scroll down/up to see mentions

36.PNG

 

5. Pull to refresh the latest 20 mentions

37.PNG

 

6. You can always load 20 more old mentions

38.PNG

 

7. Select the release date range to get movies you want to see

39.PNG

 

Movie Sentiment Rating using sap.m

Similar with Real-time sentiment rating of movies on SAP HANA (part 4) - SAP HANA info access, I've also recorded a video for you. Have fun.

 

 

Next steps

In this blog post, we've learned how to use SAPUI5, especially the sap.m library and we've built our movie sentiment rating app with sap.m. So till now, we've completely developed two smart apps, one based on SINA in previous blog and the other using sap.m which is described in this blog. In the next blog, let's have an open discussion. I'll show you the source code on GitHub and some future work.

 

Hope you enjoyed the development journey and picking up your favorite movie on your phone.

Hello everyone.

As you are? you are not hungry? You don't sleep? Well, have you ever thought of reversing requests? Probably it's a stupid idea, like other million, but often we focus too much on a question without thinking that the answers are around it.

When a manager asks for something, he's really thinking about that thing? Or he doesn't know that the real answer is around that thing?

SAP Hana could be used, for its performance, to create automated inverted query when analyzing data. Someone asks for billing datas? why not give him the costs of the department that deals with billing? Someone asks how many defective products have been generated? why not give immediately even those generated perfectly and in how much time?

A scan engine could be generated which processes the words related to the request made, and search queries pre-existing, compacting them and creating a report easy to read .

Someone could say "But if someone wants something he can asks for it, why all that mess?". I think that this is a behavioral problem before all that should be resolved. Managers must read always everything, even if repeated, even if they think to be smart man and remember everything.

Often it happens that you solve something and you destroy something else. So that's important that a global vision of data shouldn't be never missed by analysts.

We had a successful SAP HANA & Big Data meetup at Shanghai on November 3rd.

We would like to thank our sponsors: Kai-Yin and her manager. And thanks our speaker: Ray.

 

The following is a brief summary of the meetup:

This meetup is a 3 hours session to get developers some ideas around SAP HANA and Big data.

 

The followings are the statistics:

  1. There are 21 people registering on the website http://www.headin.cn/Themes/Activity/Details?activityId=53fd82abc3378fa0c8f7e045 which include 12 external participants.
    Actually there are 22 people joining this meetup.
    1.jpg 2.png
  2. The schedule of this meetup was from 18:00 to 21:30. It was until 22:00 that we finished this session because a lot of people were very interested about this topic and discussed the study roadmap with Ray and they wanted to study more about SAP HANA and big data.
    3.png 4.jpg

  3. The feedbacks from our participants:

“Big data is hot topic on recently and yesterday Mr.Wu give us amazing session on it.

He give us a landscape on big data, within different stories and projects from his experience.  Big data has impacted on retails, oil industry, E-commerce, and also share his opinion on how big data will impact our work and life.  One story give me a deep impression that Machine learning will change the translation and the role of the translator will to rethink about his career path in future.

On his experience , Mr.Wu introduced the 6 characters of the big data and the science behind the big data.

Another interesting part to compare the prediction between big data and Chinese tradition methodology, "I Ching".

Thanks a lot for Mr.Wu sharing us how to learn the big data from beginner.

Good session on me to learn the big data and expect next amazing session.

Also, thanks the delicious Pisa from the organizer offered.” – Zhang, Yaguang / SAP / Business One

 

首先,PPT内容非常的丰富,没有过多的文字赘述而是用大量的有视觉效果的案例图片来表达主题。其次,演讲者(wu xuening)用言简意赅的略带幽默的表达方式起到了很好的效果。所讲案例涵盖SAP软件所涉及的各个行业,对了解SAP HANA和大数据的适用范围和状况很有帮助。最后,对我个人目前所从事的SAP软件翻译和本地化这一块,受益匪浅,很受启发,特别是对机器学习(machine learning)产生了非常浓厚的兴趣。” - Abulizi, Anniwa / SAP / SLS

 

此次SAP的大数据的课程,课程设计浅显易懂,让我一个完全不知道IT编程的人大概了解了SAP HANA平台为客户提供的依托大数据解决商业问题的整体解决方案。邬老师讲课也深受大家喜爱,中间穿插易经,更是增加了课程的趣味性。多多举办这些基础类普及类的课程,受益匪浅!谢谢SAP – Ni, Yangyang / Housheng Investment / Investment Manager

 

“During the workshop, I knew how hana system work,what big data have been and would be used in different industries;

I got a real understand about hana&big data now, thanks to Wu xuening&honggang.” – Dong, Liping / Alcatel Lucent / Engineer

 

The overall feedbacks were very positive. Lots of attendees ask for the following activities.

Trying to get my teeth into MVC coding and javascript, I started looking at ready available packages like JQuery, NodeJS, Cloud9 (and briefly spring.io).

But what would all this work be getting me, other than knowledge that I cannot apply and some fun, which is short-lived. I am an SAP HANA consultant, MVC is good, SAP UI5 and MVC is better.

 

First stop after searching was Amazon AWS, where you can cheaply rent SAP HANA One. All very easy to setup, instructions for SAP HANA Studio Windows are provided here, but a bit more difficult for my Mac. After a long search on SCN, people wrote that a SAP HANA Studio Mac version was made available, but shortly after removed from the SAP download portal, I found some help on an external link.

So I installed Eclipse (Luna) which requires the Java SDK on Mac OS X and downloaded all the required plugins to do my SAP HANA cloud development.

 

Screen Shot 2014-11-14 at 12.59.33.pngScreen Shot 2014-11-14 at 13.00.02.png

 

Further down the process I wanted to run my instance on AWS, which looked really cheap at $3 an hour (11/11/2014) for a c3.8xlarge EC2 instance type ...

Screen Shot 2014-11-12 at 17.47.24.png

 

But the overall price estimate scared my wallet off a little - so many hidden costs?

Screen Shot 2014-11-14 at 13.08.07.png

 

Then I stumbled upon: SAP HANA River

Free at last! Or at least have a good trial run, before spending the money.

 

After successfully registering using my existing S-User and accepting the terms and conditions I found that I can also connect my local SAP HANA Studio installation to the instance in the cloud.

 

Screen Shot 2014-11-14 at 13.16.37.png

 

Trying to follow the documentation in this link failed: SAP HANA Cloud Platform

 

Screen Shot 2014-11-14 at 12.42.56.png

It just wouldn't let me in.

 

First tip, do not use the default "Landscape host" entry of "hana.ondemand.com", use as above, correctly pointed out in the instructions "https://hanatrial.ondemand.com"

Screen Shot 2014-11-14 at 13.18.02.png

 

Still it wasn't working, there was not instance running apparently ...

So I checked in my cloud deployment and indeed, I just created a database schema and not a proper instance. My fault, so no real tip here.

 

Trying again, it at least recognised that something was running, but complained: Invalid username and password.

 

Second tip, "trial" is missing, so I tried "s1234567890trial" (of course with my correct S-User number) in the user name field, but still no luck.

 

After some head scratching, I asked myself - do I really know the password of my "S-user-trial" account?? No, as it was just mapped to my "S-User".

 

Third tip, the "Account Name" should contain the "S-user-trial" entry and the "User name" should be the "S-User" - et voila

Screen Shot 2014-11-14 at 13.39.03.png

Clicking the Next button then allowed me to chose the SAP HANA instance:

 

Screen Shot 2014-11-14 at 13.42.45.png

And finally click "Finish".

 

Not surprisingly it contained some Enterprise Performance Management sample data:

Screen Shot 2014-11-14 at 13.46.48.png

 

Next stage, create some code and run it! Lucky find, I have a link in the instances section to the in browser development river:

Screen Shot 2014-11-14 at 13.54.29.png

And creating and saving a file is very easy by using right-click or the menu bar:

Screen Shot 2014-11-14 at 13.50.53.png

but there seem to be some differences in files which can be seen in the browser i.e. templates (above) and the local installation (below), the samples are missing:

Screen Shot 2014-11-14 at 13.59.04.png

 

Anyhow, I wanted to run the samples through the browser anyway.

Click on the run drop-down shows some different run modes iOS and Android as well (neat feature), although I couldn't detect the difference for the very simple HelloWorld sample:

Screen Shot 2014-11-14 at 14.14.06.png

selecting "Run" automatically shows:

Screen Shot 2014-11-14 at 14.18.14.png

in SAP UI5 style.

 

and now I have to write some code "To be continued ...", also next time I will add some information on data import using my local SAP HANA studio. Where is my schema again?

 

Part II

 

I may actually split this off into a seperate page, as this blog may get to long. But until then I will continue here, with the data import of my Excel sheets.

Right-click on the schema or catalogue only lets you import table dumps which have been created with SAP HANA Studio.

So instead we need to go via the quick-launch page and select "Import". If by accident you closed the quick launch page, it's accessible via "Help":

Screen Shot 2014-11-15 at 17.33.01.png

(Note: if you are using local HANA deployments or your first time, make sure you select a system using the button towards the top of the page)

And on the right-hand side we have "Import"

Screen Shot 2014-11-15 at 17.00.53.png

And under SAP HANA Content you can select Content. After selecting the xls file, and specifying my options ...

Screen Shot 2014-11-15 at 17.02.30.png

I had to choose my schema (note: it's not a "New Schema" it's a "New target table in an existing schema") and the list presented was very very long indeed. All the other cloud schemas were listed. I haven't tried publishing in someone else's schema, but I doubt it would work. In the end I found my schema, it helps to look at the name you connected your quick launch window to (see above screenshot).

 

Clicking the Next button, took a good 2-3minutes to bring up the next dialog box window. I doubt that large xls files in MB can be uploaded this way. If I would be using the paid version I am sure I could SSH/Putty a file to the server first and then use the sql IMPORT FROM command for a CSV file.

 

With regards to other cloud data, I have only found the option of an HCI oData connection which can draw from the SAP Business Suite:

Screen Shot 2014-11-15 at 18.04.11.png

Alternatively it should be possible to draw data in from another cloud web service via code using oData. But there doesn't seem to be a simple point and click scenario, where we can read the webservice/WSDL and create a table from it, including delta update requests.

 

Other connectors are not working like "Data provisioning", but I wouldn't expect them to work in SAP HANA Studio, as I don't host any SAP ECC / SLT servers in the same trial cloud to draw data from.

 

 

Coming back to my Excel spreadsheet, it showed a few flaws at first in the mapping screen. The source file (left side) had lines going from the title into the title on the right side. This may just be in the Mac version like this:

Screen Shot 2014-11-15 at 17.10.33.png

After a quick resize of the window this seems to have corrected itself.

Screen Shot 2014-11-15 at 17.11.08.png

Clicking Finish imported the data correctly, as mapped above.

 

Screen Shot 2014-11-15 at 17.12.08.png

 

Next I will make a start with coding - but in a different page <link goes here> :-)

Can anyone explain step by step, to make a HANA report similar to FBL3N?

(Special thanks to Ashish Sahu -blog content and review, Eric Du and Travis Price for technical papers)

 

The NoSQL market has grown rapidly to support the vast amount of data generated by today’s world of intense, always-on environments.

Datastax, built on Cassandra, a high performance, Apache open-source database technology, spans its database technology in a fully distributed way, across private data centers and the cloud to power this landscape.

It is one of the leading NoSQL vendors providing fault-tolerant distributed databases to 200+ customers including Netflix, Adobe, HP, eBay and Twitter. Additionally, DataStax supplies OpsCenter for visual management and monitoring, along with expert support, training, and consulting services for Hadoop, Cassandra, and Solr. SAP and DataStax are working together to bring their joint capabilities to the enterprise.

 

The integration of the technologies is delivered for 2 scenarios:

  1. SAP HANA SPS09 supports DataStax Enterprise 4.5

 

SAP’s in-memory SAP HANA platform is integrated with DataStax to enable applications and interactive analysis across corporate applications data & operational data with content from Internet of Things, messaging or stream data stored on DataStax.

This integration is delivered via SAP HANA smart data access (SDA) that makes it possible to access data from DataStax remotely, without moving data into SAP HANA. SAP HANA handles the data like a local virtual table performing automatic data type conversions and push-down processing.

The combined capability means that customers can use SAP HANA’s “speed of thought” analytics on millions of transactions per second data coming into DataStax. It also simplifies the landscape by processing data where it resides instead of performing costly and time-consuming data movement.

SDA already supports many different data sources such as: SAP IQ, SAP ASE, Spark, Teradata, Apache Hadoop, Oracle, SQLServer providing a comprehensive selection for its customers’ needs.

DSE 4.5 HANA.jpg

 

 

SAP HANA is a high-performance in-memory columnar database which performs OLTP and OLAP on the same platform.

It provides in-database native support for advanced analytics like predictive, text analysis, spatial and graph processing. The platform is the leading choice of more than 4000+ customers and 1700+ startups.

DataStax Enterprise (DSE), built on Apache Cassandra™, delivers what Internet Enterprises need to compete in today's high-speed, always-on data economy. DataStax Enterprise is the leading distributed database choice for online applications that require fast performance with no downtime.

DataStax Enterprise 4.5 encapsulates a distributed peer-to-peer architecture in which all nodes are equal, ensuring cluster resilience to an arbitrary number of node or data center failures.

 

Architecturally, SAP HANA’s smart data Access Component connects to DataStax Analytics with Spark/Shark through the ODBC driver. The resultant data set is treated as a remote table in HANA and it can be used as the input for all advanced analytics functionalities in HANA, which includes modeling views, SQL Script procedures, predictive, text analytics and so on.

 

The joint capabilities of SAP HANA and DataStax are as follows:

 

Always On

  • Zero downtime. Ever.
  • 100% availability for mission-critical applications

 

Performance

  • High throughput
  • Low latency

 

Advanced Analytics

  • Intuitive interfaces make it easy to explore transactional data in real time

 

No Complex Data Migration or ETL

  • Transactional data is immediately available to SAP Applications

 

Integrate with SAP Ecosystem

  • Use existing SAP workflows and BI tools

 

 

 

2. SAP’s Agile BI Platform,SAP Lumira, supports DataStax

 

We have also completed the integration with SAP Lumira in order for customers to view Datastax data in Lumira Data Visualizations and Storyboards.

DSE 4.5 Lumira.jpg

 

The DataStax Enterprise Sample Connector Template for SAP Lumira makes it easy to integrate DataStax data with numerous other data sources to allow business users to analyze data and get insights in real-time You can download the DataStax Connector Template for SAP Lumira from the SAP HANA Marketplace under this link:

http://marketplace.saphana.com/p/3389

INTRODUCTION:

 

This document is regarding a solution for a specific requirement (mentioned below) that I had when working on a SAP HANA project for a client.

 

REQUIREMENT:

 

I had a requirement to export a complete schema (SCHEMA X) from one system (Development  - BD2) and import to another schema (SCHEMA Y) (Quality system – BQ2) so as to make all tables available in target schema as available in source.

 

SCHEMA X in target system has all the catalog objects as in source system.

 

Therefore when SCHEMA X is exported from source and imported into target system, HANA system raises an error that the catalog objects (in my case - tables) already exist in target system (as the exported file contains references to SCHEMA X and not to SCHEMA Y).

 

PROCESS:

 

To achieve the above requirement, just exporting and importing catalog object will not work as the source schema name is different from the target schema.

 

Also there is SCHEMA MAPPING option from HANA Modeler perspective which is suitable for modeling views but the method I used is a common approach for moving the catalog objects from one schema to another.

 

Source schema: ARJUN (Development)

Target schema: SRIVATSAN (Quality)

 

All the tables available in ARJUN schema of DEV system has to be exported and imported to SRIVATSAN schema of Quality system.

 

Both the schemas are available in Quality system.

 

ARJUN schema in quality system already has all the tables as development system so there is a requirement to rename all the occurrences of ARJUN to SRIVATSAN in the table creation code after the EXPORT.

 

 

 

Step 1: EXPORT the source schema (ARJUN)


  • Right click the schema and choose EXPORT

1.jpg

 

2.jpg

  • Click Next.

 

3.jpg

 


  • Choose Type as BINARY.
  • Provide an empty folder path for the export.
  • Click Finish.


4.jpg

 

Step 2 : Renaming the exported schema (from ARJUN to SRIVATSAN).


  • Open NOTEPAD++ (Advanced text editor that I used for renaming)
  • Press Ctrl + F
  • Go to Find in Files
  • Enter the text to be found and text to be replaced with.
  • Choose the Directory in which the exported file is present.

 

5.jpg


  • Click Find All.
  • All occurrences of the search text are displayed.


6.jpg

 


  • Again press Ctrl + F , open  “Find in Files” tab.


7.jpg

 

  • Click “Replace in Files”.


8.jpg

 

  • Click OK.
  • Now, all the table creation statements of the exported schema will have SRIVATSAN instead of ARJUN.


 

9.jpg

 

 


  • Also it is necessary to RENAME the folder present inside the index folder to the name of the SCHEMA to which the file has to be imported.


10.jpg

Now, the exported binary file is ready to be imported to the target schema (SRIVATSAN) of target system (Quality).

 

Step 3 : Import the catalog object.


  • Go to HANA studio.
  • Click FILE -> IMPORT.


11.jpg

 

  • Choose “Catalog objects” under SAP HANA.


12.jpg

 


  • Choose the target system (Quality).


13.jpg

 


  • Click NEXT.
  • Choose the Location of the exported file.


14.jpg

 

  • Click Next.
  • Choose the required tables that are to be imported to target system.


15.jpg

 


  • Click Next.
  • Choose the required options for DATA and THREADS.


16.jpg

 

  • Click Finish.

 

Now, the tables that are required are imported to SRIVATSAN schema.

 

Conclusion:

 

This approach is a general method to  copy tables from one system to other (different schemas). This might be achieved even through SCHEMA MAPPING which I haven’t tried practically.


Please share your views and comments.

You can find the series and project from Real-time sentiment rating of movies on SAP HANA (part 6) - wrap-up and looking ahead

 

 

 

Intro

Hi everyone, welcome to the series again. We're now in the second half of the series. Hope you've enjoyed the previous three blogs including

 

Real-time sentiment rating of movies on SAP HANA (part 1) - create schema and tables

Real-time sentiment rating of movies on SAP HANA (part 2) - data preparation

Real-time sentiment rating of movies on SAP HANA (part 3) - text analysis and modeling

 

In the above three blogs, we've already finished data preparation and sentiment analysis, in addition we've also created two information views which will be consumed in the following two blogs. Now, it's time for UI. In this blog let's first take a look at SAP HANA info access and build our first smart application based on this super cool feature!

 

What is SAP HANA info access?

Maybe it's the first time you heard SAP HANA info access, never mind. Let's first have a look at it. You can find the detailed introduction and its usage from  Building Search UIs with SAP HANA Info Access - SAP HANA Developer Guide - SAP Library, so I won't show the details here. I just highlight something important for you.

 

"The SAP HANA info access HTTP service wraps search SQL queries and exposes them through an HTTP interface. The service operates on one SAP HANA attribute view at a time and makes use of the indexed and analysed texts joined in that view."

 

22.PNG

 

First of all you can find the above definition and architecture diagram of SAP HANA info access from SAP HANA Info Access Architecture - SAP HANA Developer Guide - SAP Library. As you can see, there are four layers including attribute views and Info Access HTTP Service on the SAP HANA side, while SINA and Info Access Dev Toolkit for HTML5 on the UI side. With SAP HANA info access, you do not need to care the server-side search logic and you can just focus on the UI stuff. So, why not use this awesome feature to build our sentiment app? In our smart app, we'll just use the attribute view which is already defined in Real-time sentiment rating of movies on SAP HANA (part 3) - text analysis and modeling and the Info Access Dev Toolkit for HTML5 at the top layer for simplicity. I'll show you how simple but powerful it is in this blog.

 

There is still something you need to notice. Make sure you've finished the following before you start your journey with SAP HANA info access.

  1. Import the delivery unit "HCO_INA_SERVICE.tgz" from your SAP HANA server, since this DU is not included in the default installed DU list, i.e., when you install SAP HANA server, it is not automatically installed like the SAPUI5 DU.
  2. Download the delivery unit "HCO_INA_UITOOLKIT.tgz" from SAP Software Download Center to your desktop/laptop and import the DU from the client side.
  3. Assign this role "sap.bc.ina.service.v2.userRole::INA_USER" to the user whom you want to use SAP HANA info access.

 

In addition, I highly recommend you to play with the SAP HANA info access demo in SHINE (section 9.10) first, since you can have a basic idea & look and feel about SAP HANA info access from this demo. Visit http://<myServer>:<XS_Port>/sap/hana/democontent/epm/fulltextsearch/ui/search.html after you import SHINE and generate data. Besides, you can also have a look at its source code, as our smart app is based on it.

 

23.png

 

Info Access Dev Toolkit for HTML5

Since we've already preapred our attribute view "AT_TWEETS.attributeview" in Real-time sentiment rating of movies on SAP HANA (part 3) - text analysis and modeling, what we need to do in this blog is more related with the front-end stuff such as HTML, CSS and JavaScript. Now let me show you the most important search.html file which we will visit directly in our browser. If you've read the source code of the SINA demo in SHINE, you'll be familiar with it. Here I just want to mention some key points from top to bottom. (I just found the syntax highlighting does not work on SCN today, so the below code may look ugly. Sorry for that. )

 

1. You need to include the following script when you use the info access development toolkit.

<script data-main="/sap/bc/ina/uitoolkit/js/main.js" src="/sap/bc/ina/uitoolkit/js/lib/require.js"></script>














 

2. createFulltextIndex.js is used to create full text index of the searched fields for the first time. We'll discuss it later.

 

3. The following code defines the name/package/key of the attribute view.

<div data-sap-widget="workarea" data-entityset="AT_TWEETS" data-packagename="movieRating.models" data-aggregationdimension="id_counter">

4. We have 6 work area items (position from 0 to 5) in our facet pane, while there are only 3 in the SINA demo of SHINE. Besides, we have one work area item (position 6) for the result list in our content pane.

 

5. We have 17 work area items with the chart widget in our facet repository and the following 6 are displayed by default which seem the most important.

  • SENTIMENT BY MOVIE
  • SENTIMENT
  • TWEET SOURCE
  • TITLE
  • TOKEN
  • STUDIO

 

6. Except SENTIMENT BY MOVIE, for each work area item with the chart widget, you can switch between bar chart, pie chart and tag cloud for your convenience.

 

7. We have one work item area with the result list widget which shows the details of each MENTION and the result list is sorted by "created_at" in a descending order, i.e., you will see the most recent mention at first. Similar with the SINA demo of SHINE, we also define 3 separate result templates as follows.

  • tweet.html: Defines the template of the result in our content pane.
  • tweet_small.html: Defines the template of the result in our facet pane.
  • tweet_detail.html: Defines the template of the result in the overlay container.


search.html

<!DOCTYPE HTML>
<html lang="en">
<head>
<meta charset="utf-8">
<!-- Always force latest IE rendering engine (even in intranet) & Chrome Frame -->
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<title>Movie Sentiment Rating based on SINA</title>
<!-- CSS: implied media="all" -->
<!-- CSS concatenated and minified via ant build script-->
<link rel="stylesheet" type="text/css" href="search.css">
<!-- end CSS-->
<link rel="shortcut icon" href="img/favicon.png" />
<script data-main="/sap/bc/ina/uitoolkit/js/main.js" src="/sap/bc/ina/uitoolkit/js/lib/require.js"></script>
<script>
requirejs(['server/createFulltextIndex.js'], function   () {
});
</script>
<!-- create fulltext index -->
<!-- <script src="server/createFulltextIndex.js"></script> -->
</head>
<body>
    <header>
        <div data-sap-widget="header" data-title="Movie Sentiment Rating based on SINA" data-helphref="http://help.sap.com/hana/SAP_HANA_INA_Search_JavaScript_Reference_en/index.html"></div>
    </header>
    <div class="background">
    <div data-sap-widget="alert"></div>
   <div data-sap-widget="workarea" data-entityset="AT_TWEETS" data-packagename="movieRating.models" data-aggregationdimension="id_counter">
    <div class="searchnfilterframe">
    <div data-useresponseattributes="false" data-usedimensions="true" data-sap-widget="searchnfilter"></div>
    </div>
        <div class="facetPaneShadow">
          <div class="facetPane">
            <div class="facetColumn" data-children-drag-handle="header">
                <div data-sap-widget="workareaitem" data-title="Drop here" data-target-position="0"></div>
                <div data-sap-widget="workareaitem" data-title="Drop here" data-target-position="1"></div>
                <div data-sap-widget="workareaitem" data-title="Drop here" data-target-position="2"></div>
            </div><!-- end of the facetColumn" -->
          </div> <!--  end of facetPane -->
        </div> <!--  end of facetPaneShadow -->
       
        <div class="facetPaneShadow">
          <div class="facetPane">
            <div class="facetColumn" data-children-drag-handle="header">
                <div data-sap-widget="workareaitem" data-title="Drop here" data-target-position="3"></div>
                <div data-sap-widget="workareaitem" data-title="Drop here" data-target-position="4"></div>
                <div data-sap-widget="workareaitem" data-title="Drop here" data-target-position="5"></div>
            </div><!-- end of the facetColumn" -->
          </div> <!--  end of facetPane -->
        </div> <!--  end of facetPaneShadow -->
        <div class="contentPaneShadow">
            <div class="contentPane" data-children-drag-handle="header">
                <!-- The following div is a placeholder that will be replaced on the first time a workarea-centerable will be centered. -->
                <div data-sap-widget="workareaitem" data-iscenter="true" data-title="Drop here" data-target-position="6"></div>
            </div>
        </div>
        <div data-sap-widget="facetrepository" data-children-drag-handle="body">
       
        <div data-sap-widget="workareaitem" data-title="SENTIMENT BY MOVIE" data-source-position="0">
                        <div data-sap-widget="switchbox" data-activeindex="0">
                            <a href="javascript: void(0);" class="iconTimeline">Timeline</a>
                            <div data-sap-widget="chart" data-dimension-line="sentiment" data-dimension-x="title" data-dimension-y="$$Count$$" data-charttype="line" data-color="colored" data-toplarge=10 data-maxlabelnum="all"></div>
                            <a href="javascript:void(0)" class="iconGroupBarChartVertical">Goup Bar Chart</a>
                            <div data-sap-widget="chart" data-toplarge="25" data-topsmall="5" data-title="SENT-MOVIE-GROUPBAR" data-dimension-line="title" data-dimension-x="sentiment" data-dimension-y="$$Count$$" data-charttype="groupbar"></div>
                        </div>
                </div>
       
        <div data-sap-widget="workareaitem" data-title="SENTIMENT" data-source-position="1">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="SENTIMENT-BAR" data-dimension="sentiment" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="SENTIMENT-PIE" data-dimension="sentiment" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="SENTIMENT-TAG" data-dimension="sentiment" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="TWEET SOURCE" data-source-position="2">
                    <div data-sap-widget="switchbox" data-activeindex="0">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="SOURCE-BAR" data-dimension="tweet_source" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="SOURCE-PIE" data-dimension="tweet_source" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="SOURCE-TAG" data-dimension="tweet_source" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="TITLE" data-source-position="3">
                    <div data-sap-widget="switchbox" data-activeindex="2">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="TITLE-BAR" data-dimension="title" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="TITLE-PIE" data-dimension="title" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="TITLE-TAG" data-dimension="title" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="TOKEN" data-source-position="4">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="TOKEN-BAR" data-dimension="token" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="TOKEN-PIE" data-dimension="token" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="TOKEN-TAG" data-dimension="token" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="STUDIO" data-source-position="5">
                    <div data-sap-widget="switchbox" data-activeindex="0">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="STUDIO-BAR" data-dimension="studio" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="STUDIO-PIE" data-dimension="studio" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="STUDIO-TAG" data-dimension="studio" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="TWEET BY YEAR">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="TWEET-YEAR-BAR" data-dimension="tweet_time_year" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="TWEET-YEAR-PIE" data-dimension="tweet_time_year" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="TWEET-YEAR-TAG" data-dimension="tweet_time_year" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="TWEET BY MONTH">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="TWEET-MONTH-BAR" data-dimension="tweet_time_month" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="TWEET-MONTH-PIE" data-dimension="tweet_time_month" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="TWEET-MONTH-TAG" data-dimension="tweet_time_month" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="TWEET BY DAY">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="TWEET-DAY-BAR" data-dimension="tweet_time_day" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="TWEET-DAY-PIE" data-dimension="tweet_time_day" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="TWEET-DAY-TAG" data-dimension="tweet_time_day" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="TWEET BY HOUR">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="TWEET-HOUR-BAR" data-dimension="tweet_time_hour" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="TWEET-HOUR-PIE" data-dimension="tweet_time_hour" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="TWEET-HOUR-TAG" data-dimension="tweet_time_hour" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="TWEET BY WEEK">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="TWEET-WEEK-BAR" data-dimension="tweet_time_week" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="TWEET-WEEK-PIE" data-dimension="tweet_time_week" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="TWEET-WEEK-TAG" data-dimension="tweet_time_week" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="MOVIE BY YEAR">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="MOVIE-YEAR-BAR" data-dimension="movie_year" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="MOVIE-YEAR-PIE" data-dimension="movie_year" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="MOVIE-YEAR-TAG" data-dimension="movie_year" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="MPAA RATING">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="MOVIE-MPAA-BAR" data-dimension="mpaa_rating" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="MOVIE-MPAA-PIE" data-dimension="mpaa_rating" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="MOVIE-MPAA-TAG" data-dimension="mpaa_rating" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="RELEASE DATE BY YEAR">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="RELEASE-YEAR-BAR" data-dimension="release_date_year" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="RELEASE-YEAR-PIE" data-dimension="release_date_year" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="RELEASE-YEAR-TAG" data-dimension="release_date_year" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="RELEASE DATE BY MONTH">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="RELEASE-MONTH-BAR" data-dimension="release_date_month" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="RELEASE-MONTH-PIE" data-dimension="release_date_month" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="RELEASE-MONTH-TAG" data-dimension="release_date_month" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="RELEASE DATE BY DAY">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="RELEASE-DAY-BAR" data-dimension="release_date_day" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="RELEASE-DAY-PIE" data-dimension="release_date_day" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="RELEASE-DAY-TAG" data-dimension="release_date_day" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="RELEASE DATE BY WEEK">
                    <div data-sap-widget="switchbox" data-activeindex="1">
                        <a href="javascript:void(0)" class="iconBarChartHorizontal">Bar Chart</a>
                        <div data-sap-widget="chart" data-toplarge="25" data-title="RELEASE-WEEK-BAR" data-dimension="release_date_week" data-charttype="bar" data-color="green"></div>
                        <a href="javascript:void(0)" class="iconPieChart">Pie Chart</a>
                        <div data-sap-widget="chart" data-title="RELEASE-WEEK-PIE" data-dimension="release_date_week" data-charttype="pie" data-color="#F0AB00"></div>
                        <a href="javascript:void(0)" class="iconCorrelation">Tagcloud</a>
                        <div data-sap-widget="chart" data-topsmall="8" data-toplarge="25" data-title="RELEASE-WEEK-TAG" data-dimension="release_date_week" data-charttype="tagcloud"></div>
                    </div>
                </div>
               
                <div data-sap-widget="workareaitem" data-title="MENTION" data-source-position="6">
                <div data-sap-widget="switchbox" data-activeindex="0">
                     <!-- Optional data-sap-widget="resultlist" attributes:
                     - data-cutoverflowresults
                         ="false": resultlist shall show all results regardless the size if the resultlist container
                         ="true":  (default) show only so many results that they fit into the resultlist container
                     - data-maxresults
                         ="10": (default) number of results to be shown
                     - data-responseattributes
                         ="": (default) use default attributes of view/collection (HANA database) for ODATA response
                     - data-highlightterms
                         ="true": (default) search term is highlighted
                     -->
                     <!-- First switchbox item -->
                     <a href="javascript:void(0)" class="iconListView">Result List</a>
                     <div data-sap-widget="resultlist"
                      data-showheader="false"
                          data-maxresultslarge="8" data-maxresultssmall="3" data-responseattributes="user_screen_name,user_profile_image_url,title,movie_year,mpaa_rating,runtime,release_date,poster,studio,sent,created_at_str,tweet_source,source_url,token,sentiment,text_head,text_tail,created_at_time_str,poster_pro,synopsis"
                          data-detailwidth="650" data-detailheight="500"
                          data-highlightterms="true" data-highlightedattributes="title" data-highlightmaxlength="100" data-highlightstartposition="1"
                          data-snippetterms="false" data-snippetedattributes="title"
                          data-opendetailevent="click" data-showdetail="true"
                          data-orderby="created_at_str"
                          data-sortorder="DESC"
                          data-resulttemplate="templates/tweet.html"
                          data-resulttemplate-css="templates/tweet.css"
                          data-resulttemplate-small="templates/tweet_small.html"
                          data-resulttemplate-small-css="templates/tweet_small.css"
                          data-resulttemplate-detail="templates/tweet_detail.html"
                          data-resulttemplate-detail-css="templates/tweet_detail.css">
                     </div>
                </div>
                </div>
            </div>  <!-- end of facetrepository div -->
    </div> <!-- end of workarea div -->
    <div class="clear"></div>
    </div> <!-- end of background div -->
</body>
</html>


Create full text index

Regarding the second key point in the last section, we'll now discuss creating full text index. First you may find the following code snippet at the beginning of the HTML body which I have not mentioned yet.

 

<div class="searchnfilterframe">
    <div data-useresponseattributes="false" data-usedimensions="true" data-sap-widget="searchnfilter"></div>
    </div>

 

So what's that? It will display a search filter at the top of the web application. Moreover, it should be the heart & key feature of SAP HANA info access. So, I'm gonna show you in this separate section. Because we want to enable searching the used dimensions in our chart widgets instead of the used attributes in our result list, we set "data-useresponseattributes" to false and "data-usedimensions" to true. As we all know, we can use full text index to search in SAP HANA. However, SAP HANA isn't so smart that it can create the full text index for you automatically. Currently we need to create the full text index manually. That's why we need createFulltextIndex.js and createFulltextIndex.xsjs in our project. Since we just use createFulltextIndex.js to call createFulltextIndex.xsjs, we just need to change the URL of createFulltextIndex.js in SHINE. Here let's just have a look at the server-side logic.

 

createFulltextIndex.xsjs

(function (){
    'use strict';
    //initialize variables
    var conn = null,
        body = '',
        prepStat = null;
    //initial database setup, create fulltext indizes
    try {
        //get connection
        conn = $.db.getConnection();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX TWEET_TA_TOKEN_I ON "MOVIE_RATING"."$TA_TWEETS_I" ("TA_TOKEN") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX TWEET_TA_TYPE_I ON "MOVIE_RATING"."$TA_TWEETS_I" ("TA_TYPE") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX TWEET_SOURCE_STR_I ON "MOVIE_RATING"."movieRating.data::movieRating.Tweets" ("source_str") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX TWEET_CREATED_AT_YEAR_I ON "MOVIE_RATING"."movieRating.data::movieRating.Tweets" ("created_at_year") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX TWEET_CREATED_AT_MONTH_I ON "MOVIE_RATING"."movieRating.data::movieRating.Tweets" ("created_at_month") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX TWEET_CREATED_AT_DAY_I ON "MOVIE_RATING"."movieRating.data::movieRating.Tweets" ("created_at_day") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX TWEET_CREATED_AT_HOUR_I ON "MOVIE_RATING"."movieRating.data::movieRating.Tweets" ("created_at_hour") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX TWEET_CREATED_AT_WEEK_I ON "MOVIE_RATING"."movieRating.data::movieRating.Tweets" ("created_at_week") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX MOVIE_TITLE_I ON "MOVIE_RATING"."movieRating.data::movieRating.Movies" ("title") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX MOVIE_MPAA_RATING_I ON "MOVIE_RATING"."movieRating.data::movieRating.Movies" ("mpaa_rating") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX MOVIE_YEAR_STR_I ON "MOVIE_RATING"."movieRating.data::movieRating.Movies" ("year_str") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX MOVIE_STUDIO_I ON "MOVIE_RATING"."movieRating.data::movieRating.Movies" ("studio") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX MOVIE_RELEASE_DATE_YEAR_I ON "MOVIE_RATING"."movieRating.data::movieRating.Movies" ("release_date_year") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX MOVIE_RELEASE_DATE_MONTH_I ON "MOVIE_RATING"."movieRating.data::movieRating.Movies" ("release_date_month") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX MOVIE_RELEASE_DATE_DAY_I ON "MOVIE_RATING"."movieRating.data::movieRating.Movies" ("release_date_day") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        prepStat = conn.prepareStatement('CREATE FULLTEXT INDEX MOVIE_RELEASE_DATE_WEEK_I ON "MOVIE_RATING"."movieRating.data::movieRating.Movies" ("release_date_week") SYNC SEARCH ONLY OFF');
        prepStat.execute();
        prepStat.close();
        // --- commit changes and close connection
        conn.commit();
        conn.close();
        body = 'fulltext index created';
        $.response.status = $.net.http.OK;
    }
    catch (e){
        // 289: index already exists, thats ok
        if(e.code && e.code !== 289){
            body = e.message;
            $.response.status = $.net.http.BAD_REQUEST;
        } else {
            body = 'fulltext index already exists';
            $.response.status = $.net.http.OK;
        }
    }
    $.response.contentType = 'text/plain';
    $.response.setBody(body);
}());









 

You can see in the above XSJS file, we create 16 full text indexes for the dimensions in our chart widgets except SENTIMENT BY MOVIE if these full text indexes do not exist. Otherwise, we need to handle the exception. Now you may ask, "Can I run these SQL statements in SQL console manually?" Absolutely you can. But considering the DU transportation, it's better to use this dynamic way.

 

Besides we can also set the "Search Properties" in our AT_TWEETS.attributeview. For more details, please refer Modeling Your Search Content - SAP HANA Developer Guide - SAP Library

 

24.PNG

 

Look & feel

Now it's time to show the look & feel! You can find four parts from the following window dump.

  • Search filter in red box
  • Facet pane in green box with six default chart widgets
  • Content pane in blue box with result list
  • Facet repository in yellow box with 17 chart widgets, you can drag and drop them to facet pane.

As you can see, since we filter the movie title to "The Judge", everything including the chart widgets and the result list will only display the stuff with this movie.

 

25_1.png

 

Now let's click one mention and the following overlay container will prompt to us. It'll show you the very detailed tweet info and mentioned movie info. From strong positive sentiment to strong negative sentiment, we use green to red, similar with the traffic light. In addition, the sentiment token is also highlighted in the tweet text. Remember in Real-time sentiment rating of movies on SAP HANA (part 2) - data preparation, we insert tweets into SAP HANA in real-time and the movie sentiment is continuously analyzed by SAP HANA. So with this smart app, we can analyze the movie sentiment in real-time! For instance, you can analyze the sentiment and # of mentions of a specific movie or you can even analyze all new release movies in current week. Exciting?

 

26.PNG

 

Movie Sentiment Rating based on SINA

Still not enough? Don't worry. I've recorded a video for you. In the following video I'll show you how to use this smart app to analyze the movie sentiment based on SINA.

 

 

Next steps

In this blog, we've jumped into SAP HANA info access and built our first movie smart app based on it. With the simplicity of SAP HANA info access, we are able to finish our first app in a very short time. What we need is just an attribute view and some front-end stuff like HTML, CSS and JavaScript. We did less but got more. In the next blog, we will have a look at sap.m (a package in SAPUI5) and build our movie app on mobile devices. See you soon.

 

Hope you enjoyed reading my blog and playing with the smart app.

With HANA all around (speaking about the HANA database, not HANA XS or Hana Cloud) I as ABAP developer expect myself needing to leverage more database features. This consequently means more to code more SQL – and this implies more headache to me. My brain more used to more imperative commands instead of declarative expressions.

But technology does not care about my brain, so I have to get it used to this type of programming. Luckily, I don’t need a HANA-DB in order to train myself, so I started to write a short series about how to use SQL – oh wait, it’s now called “code pushdown”. This makes it easier to sell and might trick my brain that this is not veeeery old stuff which I should have been knowing for a long time but funky new stuff which is worth investing time into.

This first post is about the very basic of joins – particularly LEFT OUTER JOINs in combination with restrictions.

 

Business background

In our application, the database scheme is quite well normalized (don’t ask me which normal form it is, but it should be the third one or higher, I was never eager to differentiate further). However; to the user, the data is presented in a denormalized form. While transactionally, the dependent data is retrieved via an association, we also need to enable finding instances where dependent data matches some criteria. However, the non-existence of this dependent data must not prevent finding instances when not searching for attributes of the dependent data.

Implementation alternatives

Reading the requirement, the first thing that comes to every ABAPer’s mind is a database view. Go to se11, model your relations, use OpenSQL – that’s it. Unluckily not.

An DDIC DB-view always joins tables with an INNER JOIN. This means that the right hand side table (RHST) needs to provide matching entries for each instance of the Left HSTas prerequisite for the joined data to exist. This makes DDIC DB views unusable for all those selections, where data shall be searchable which has a minimum cardinality of 0. Thus, specifying the join as LEFT OUTER at runtime seems to be the only alternative. However, as we are being told by the OPenSQL-exception, placing a restriction on the RHST via OpenSQL is also not possible. Both limitations – as far as I know – shall be obsolete when using Core data services (CDS). But as this is only available from 7.40 upwards, it can only be subject of another post.

Until then, in our project we were forced to used native SQL based on LEFT OUTER JOINs in order to fulfill those requirements.

 

The INITIAL issue

After we had implemented a generic query class executing the native SQL, we felt quite comfortable having found a nice solution. Until the first tickets appeared in out bug tracking tool. As we’re using a floorplan manager based UI, our users were enjoying the “new” search options – particularly the “is initial” and the “is not initial” option were attracting a lot of affection: “oh, this makes it so easy for me to find a … where no purchasing requisition has been created yet”. Of course! “But it does not work!”. The reason for that was the custom table containing the purchasing requisition number was a depend one to the table which contained the instances for which shall be searched. With dependent_table.purch_req = ‘’, the desired instances for which the dependent data does not exist were not returned. When explaining the issue and solution in my team, I felt that not every ABAPer is aware of that behavior.

Thus, I wrote a short report executing multiple selects in order to explain the different result sets.

Have look at the following screenshot picturing the content of the joined tables and the different SELECT-statements issued. Do you know all the results?

Sample_Selects_wo_results.PNG

 

You can find a screenshot of the report including result and explanations separately at below. I did not want to spoil your curiosity presenting the results directly

 

Sample_Selects_results_1.PNG

sample_Selects_results_2.PNG

 

 

Did you expect all the results? There was one, which surprised me. And to my even bigger surprise, it behaves differently on NW 7.31 and NW 7.40 – maybe I should open a message for that one…

You can find the report attached including the report source and alternatively SAPLINK-nugget containing the source tables if you’re too lazy to create the two tables yourself.

 

Outlook

There is one massive limitation with respect to performance on this approach of using LEFT OUTER JOINs. Therefore, in a subsequent post, I may introduce the advantage of subqueries (once I understood it myself to such an extent that I feel comfortable writing about it… )

 

Got feedback?

I’d love to read your comments on this topic and how you prepare yourself for the obviously upcoming re-incarnation of SQL – sorry: of the code pushdown.

openSAP: Next Steps in Software Development on SAP HANA – my thoughts after week 4

 

This week we looked at OData and ways to consume the data. The OData protocol allows you to create, read or update  and delete data via an URL.

The OData protocol is a powerful part as it is an endpoint that allows access to the SAP HANA data database. This access can be from a xsjs or html file on the HANA server, but it can also be from somewhere else.

 

 

odata.jpg

Source: OData.org

 

In SAP HANA you have functions to do aggregations, can define associations, associations can be picked up later by SAPUI5 components for added functionality. (look for the purchase order header and item sample)

And typical for sap was also the OData4SAP Annotations. Typical as when you enable this extension it will deliver you the label, the unit and the text (but not the currency)

 

What I don’t remember from the initial HANA course, but apparently available since SP06 are SQLSCRIPT extensions. These extensions are procedures that are triggered by the OData service. The input is basically the data that is passed from the OData service i.e. the new to be created record or the update of a record.

What you can do in the trigger is validation, but if you want you can get creative and think of other things such as an online ticket sales procedure that checks the timestamp of the order to look which place in the virtual line the prospect buyer has for the final ticket of that important concert of <insert your favorite music here>

This is an improvement over the earlier version as I can remember that previously you had either a straight OData service with nothing else or a xsjs service.

Another part of this week was Batch requests and deep insertions. The latter was interesting as this provided a way to maintain relationships between two tables. For example if you have purchase order headers and items, the item table needs the foreign key ordernumber to keep a relation to the header file.

The deep insert functionality creates an ID place holder that will be replaced by the actual ordernumber ID in the end. It’s a shame that SAPUI5 doesn’t support it yet. The example was a concatenation of string variables to manually create an OData Request.

 

Exercises:

In this week we had to type A LOT for the exercises. (I know you can cut&paste but I like to type it in at least for a part, to better digest the lessons).  Especially interesting was using the SAPUI5 for creating a table using the OData source. It takes a little getting used to as the entire setup of SAPUI5 is that you declare variables based on objects.

 

You set properties in a Json variable :

Var myButtonproperties = ({text:"Hello World!",tooltip:"Hello Tooltip!"}

 

Then you create a new instance using those properties :

var myButton = new sap.ui.commons.Button (myButtonproperties);

 

you can set properties via methods :

myButton.setText("Hello World!");

 

and finally you can attach functions to events :

 

myButton.attachPress(function(){$("#btn").fadeOut()});

 

and if you want to have more buttons you can try something like this :           

var namearr = ["btn1", "btn2", "btn3"];
                var textarr = ["text1", "text2", "text3"];
                var buttons = new array
               
                for (i = 0; i < namearr.length; i++) { 
                                buttons.push(new sap.ui.commons.Button(namearr[i]));
                                buttons[i].setText(textarr[i]);
                                               buttons[i].attachPress(function(){$(namearr[i]).fadeOut();})}

This will give you an array of buttons that you can place all over your document.

But admittedly the last was a bit of playtime after the exercises J

Your Chance to Provide Feedback to SAP

 

SAP developers and employees from the SAP User Experience team are giving you the opportunity to try out and provide feedback on the new SAP® HANA Modeler based on the SAP Web Integrated Development Environment by participating in a usability test.

 

The test examines the new cloud-based development environment that allows you to create complex analytic models right inside your  browser.  Experience new ways of user interaction to work with the HANA Modeler in the WebIDE and create Analytic Models (Calculation Views & Analytic Privileges) with SAP’s cutting-edge HTML5 technologies.

 

During the test you will evaluate the ease-of-use, usability and functional completeness of the HANA Modeler in WebIDE.

 

Targeted roles are Consultants or dedicated data modelers who create, manage and deploy analytic models on HANA as well as application/content developers responsible for building analytical models on HANA for further consumption in HANA Native Applications or in reporting tools.

 

If you are interested in discovering the latest developments in this area, please sign up for this topic:

 

SAP HANA – SAP HANA Modeler in SAP Web Integrated Development Environment

 

 

As a tester, you can actively work on the prototypes or products during the test. At the end of the test, you will have an opportunity to share your feedback on the prototype. Please note that it is the tool that is being tested, not you, to further enhance the user experience of SAP's products.

 

Testing will start at Tuesday, November 11th 2014 after the keynote.

 

Access to the full topic catalog and registration instruction for the usability testing can be found here https://usability.sap.com/

 

See you in Berlin!

For the SAP HANA Modeling- and Product Management Team Christoph Morgen

Recently, I have experienced something while modeling HANA information view that I want to share with everyone.


Case:

Dynamic Filter for projection with current Fiscal Year and Fiscal Period.


Method:

In order to filter it dynamically I decided to use input parameter. There are different parameter types that we can use to do this. I decided to use following "Derived from table" type as shown below:



Challenge:

When filtering column then it displays list of values from the same HANA information view and we can select the date. Now, as the requirement is current Fiscal Year and Period so how do we filter the column to dynamically select current date in "Filter Value" section?


Solution:

Like everyone, I expected it to understand the expression. So I tried following and it worked. I used the expression


currentdate()




I was aware that I could use expression in the "Default Value" section (as explained in SAP HANA Developer Guide) but didn't expect it to

work in "Filter Value". I was able to filter the projection using this input parameter. To test the filtering, I removed the input parameter and the projection displayed all the years rather than just current year. Hopefully, when I get an explanation for this then I will share it here. For now, you can try different expressions.

Actions

Filter Blog

By author:
By date:
By tag: