1 2 3 53 Previous Next

SAP HANA and In-Memory Computing

783 Posts

Companies today are facing critical challenges.  You need to know how to optimize maintenance while providing support services.  Plus you are always worried about how to save costs. But where do you start?  How do you take your maintenance program to the next level? 

 

To help you face these challenges, SAP will be hosting an Innovation Day focusing on Predictive Maintenance and Service.   You can join us free of charge at the SAP office in Newtown Square, PA on Thursday March 26, 2015.

 

We’ll discuss real business cases were companies have used huge amounts of data gathered from machine to machine connectivity and powered by the real-time processing capabilities embedded in SAP HANA to make informed decisions.

 

You’ll learn how companies are able to realize a multi-million dollar annual return on their investment through:

 

 

  • Increased Revenue due to greater asset uptime supporting increased productivity;
  • ReducedService Parts Inventory allowing for more accurate & timely service parts inventory forecasting;
  • Decreased R & D Costs based on predictive maintenance findings regarding equipment design resulting in higher quality products and fewer warranty claims

 

Helping increase uptime of your critical assets is a win-win for both you and your customers. SAP’s Solution for Predictive Maintenance & Service can
help.  So to register or to learn more just click here or on the link below:

 

 

INNOVATION DAY in NEWTOWN SQUARE, PA

 

Thursday, March 26, 2015  from 9 AM – 12:30PM (local time)

 

 

SAP Office, Executive Briefing Center, Demonstration Room B in Building 1 located at 3999 West Chester Pike, Newtown Square, PA 19073

 

Click here to Register or Learn More.

A recent request came up at a customer who was migrating to a larger independent HANA sidecar scenario and wanted to move large groups of users from one HANA instance to another. Since there is no mass user creation functionality that I am aware of, we had to create one. This would be helpful for applications services teams who are responsible for creating users and assigning roles in large amounts. This can certainly be enhanced to cover a number of other scenarios, but this works for the requirement at hand and was fast to implement.

 

Basic workflow

- Populate a table either through file upload or another UI method, that would have the structure shown below

- Create users that do no already exist, taking into account that they may or may not have SAML enabled and also may or may not have a specific validity date.

- Add roles to users that do not already have that role assigned

- Clean out maintenance table

 

DDL

 

CREATE COLUMN TABLE "HANA_FOUNDATION"."USER_MAINTAIN" ("USER_NAME" NVARCHAR(12),
     "PASSWORD" NVARCHAR(15),
     "VALID_TO" NVARCHAR(8),
     "SAML_ENABLED" NVARCHAR(1),
     "SAML_PROVIDER" NVARCHAR(40),
     "EXTERN_ID" NVARCHAR(20),
     "ROLE" NVARCHAR(60)) UNLOAD PRIORITY 5 AUTO MERGE

SQL Script code

 

/********* Begin Procedure Script ************/
--Justin Molenaur 02/12/2015
--Create users, assign roles and enable SAML based on excel file upload
--Check for existing user and role assignment beforehand
i INTEGER;
row_count INTEGER;
loop_current_SQL NVARCHAR(200);
valid_date NVARCHAR(8);
valid_SAML NVARCHAR(1);
BEGIN
--Select unique users to be created that don't already exist
it_user_list = SELECT DISTINCT A."USER_NAME", A."PASSWORD", A."SAML_ENABLED", A."SAML_PROVIDER", A."EXTERN_ID", A."VALID_TO"
FROM "HANA_FOUNDATION"."USER_MAINTAIN" A
LEFT OUTER JOIN "SYS"."USERS" B
ON (A."USER_NAME" = B."USER_NAME")
WHERE B."USER_NAME" IS NULL;
SELECT COUNT("USER_NAME") into row_count FROM :it_user_list; --Get count of users to create
--Loop for Creation of users that don't exist yet
FOR i IN 0 .. :row_count -1 DO
SELECT "VALID_TO" --Check if a validity date is maintained
into valid_date FROM :it_user_list
LIMIT 1 OFFSET :i;
SELECT "SAML_ENABLED" --Check if a validity date is maintained
into valid_SAML FROM :it_user_list
LIMIT 1 OFFSET :i;
IF :valid_date IS NULL AND :valid_SAML = 'Y' THEN --No Validity, SAML
    SELECT 'CREATE USER ' || A."USER_NAME" || ' PASSWORD ' || A."PASSWORD"
    || ' WITH IDENTITY ''' || A."EXTERN_ID" || ''' FOR SAML PROVIDER ' || A."SAML_PROVIDER"
    INTO loop_current_SQL
    FROM :it_user_list A
    LIMIT 1 OFFSET :i;
  
ELSEIF :valid_date IS NOT NULL and :valid_SAML = 'Y' THEN --Validity, SAML
    SELECT 'CREATE USER ' || A."USER_NAME" || ' PASSWORD ' || A."PASSWORD"
    || ' WITH IDENTITY ''' || A."EXTERN_ID" || ''' FOR SAML PROVIDER ' || A."SAML_PROVIDER"
    || ' VALID UNTIL ''' || A."VALID_TO" || 235900 || ''''
    INTO loop_current_SQL
    FROM :it_user_list A
    LIMIT 1 OFFSET :i;
  
ELSEIF :valid_date IS NOT NULL and :valid_SAML = 'N' THEN --Validity, No SAML
    SELECT 'CREATE USER ' || A."USER_NAME" || ' PASSWORD ' || A."PASSWORD"
    || ' VALID UNTIL ''' || A."VALID_TO" || 235900 || ''''
    INTO loop_current_SQL
    FROM :it_user_list A
    LIMIT 1 OFFSET :i;
ELSE --No Validity, No SAML
    SELECT 'CREATE USER ' || A."USER_NAME" || ' PASSWORD ' || A."PASSWORD"
    INTO loop_current_SQL
    FROM :it_user_list A
    LIMIT 1 OFFSET :i;
  
END IF;
  
    EXEC(:loop_current_SQL);
END FOR;
--Select distinct role assignments needed, checking for already existing role assignments
it_role_list = SELECT DISTINCT A."USER_NAME", A."ROLE"
FROM "HANA_FOUNDATION"."USER_MAINTAIN" A
LEFT OUTER JOIN "SYS"."GRANTED_ROLES" B
ON (A."USER_NAME" = B."GRANTEE" AND A."ROLE" = B."ROLE_NAME")
WHERE B."GRANTEE" IS NULL;
--Get count of roles to assign
SELECT COUNT("USER_NAME") into row_count FROM :it_role_list ;
--Loop for assignment of roles
FOR i IN 0 .. :row_count -1 DO
    SELECT 'GRANT "' || A."ROLE" || '" TO ' || A."USER_NAME"
    INTO loop_current_SQL
    FROM :it_role_list A
    LIMIT 1 OFFSET :i;
  
    EXEC(:loop_current_SQL);
END FOR;
DELETE FROM "HANA_FOUNDATION"."USER_MAINTAIN"; --Clear out maintenance table when complete
END;
/********* End Procedure Script ************/

 

There you go, simple as that. Now get out there and create users in a massive way!

 

Happy HANA,

Justin

In the second of a two-part episode, Raj Erode IT Architect at CenterPoint Energy joins us to discuss three killer SAP HANA use cases for CRM and predictive scenarios and their incredibly innovative Internet of Things (IoT) and Big Data scenario.  Centerpoint won the 2014 HANA Innovation Award for their Big Data scenario.

 

We hope you enjoy hearing Centerpoint’s first-hand experience with mission-critical SAP HANA.  Please let us know your feedback in the comments below.

 

To get more real-world customer HANA stories, subscribe to our iTunes or SoundCloud feed for weekly podcasts that will cover multiple in-production customer use case scenarios for SAP HANA.

 

Also, if you’ve got a killer SAP HANA scenario and would like to share it on the HANA Effect podcast, please contact me.

 

HE6.jpg

 

Transcript: SAP HANA Effect Episode 6

 

Sponsored by

xeon_i_ww_rgb_3000

Intro

Hi everyone, Happy Lunar New Year! In the previous post, we've customized the text analysis configurations and received greetings from SAP HANA. Do you remember the characters of "Wish you prosperity"? Yeah, you got it. That's "恭喜发财", a very popular greeting in Chinese new year. In China, people especially children sometimes jokingly use the phrase "恭喜发财,红包拿来" translated as "Wish you prosperity, now give me a red envelop!" So what's red envelop? You can find its meaning from Wikipedia. Red envelop is a monetary gift, also known as red packet or Hongbao (红包 in Chinese). You can learn how to give red envelops from Chinese Red Packets and Envelopes, Lucky Money during Chinese New Year.

 

With the rapid development of WeChat (China's most popular mobile chat app, someone says it's Chinese Whatsapp, but someone doesn't, see here and here) in China, most people give virtual red envelops via WeChat in Chinese new year 2015 (See Tencent's WeChat Sends 1 Billion Virtual Red Envelopes On New Year's Eve - Forbes for more details). So in this post, let's have a look at the game of WeChat Hongbao and try to simulate it with stored procedure in SAP HANA.


WeChat Hongbao

There are several kinds of red envelops in WeChat. Among them the most exciting one is giving red envelops in chat groups. So what does that mean? Since WeChat has the "chat group" feature, users can create chat groups and invite contacts to join the groups. So now you can give red envelops in the chat group and let the group members get your red envelops. The following is an example of mine. I wanted to give red envelops in one chat group of mine and let the group members to get them. The game is very easy including only two steps, giving and getting.

 

Step 1 (for giver in the chat group): Giving red envelops in the chat group with the following substeps

  1. Input the # of red envelops (e.g., 5 in the screen shot)
  2. Input the total amount (e.g., 100)
  3. Choose the mode (Random or average, default is random since it's more interesting and exciting. We just discuss random in this post)
  4. Choose the method of payment
  5. Confirm to give by clicking the button at the bottom

 

2_1.png

 

Step 2 (for all group members in the chat group): Getting red envelops

Everyone (including the giver) in the chat group can get one red envelop by clicking the red envelop in the chat screen. In addition, all group members can check the result of getting red envelops as follows. You can find a list at the bottom which shows you all current getters and how much they get. After all red envelops are given, you can find the complete list of all getters and who's the champion. Above the list, you can see some figures you set in step 1, like # of red envelops, total amount and game time.

 

3_1.png

 

That's it, very easy. If we choose the average mode, everyone will get 100 / 5 = 20. Let' have a look at the random figures. 35.84 + 5.21 + 34.00 + 17.42 + 7.53 = 100. From the result we can observe that two people are lucky, since 35.84 and 34.00 are much higher than the average 20; one people (17.42) is just soso and two people are not lucky since 5.21 and 7.53 are much lower than the average. That's the magic of random red envelops, since it's more exciting than the average. We all love it.

 

Now let's rethink the game and guess what happened in the backend. For low latency, when the giver gives out the red envelops, the random amounts should be calculated immediately and stored in an array or some other data structure in the backend. When group members get red envelops, the amounts pop up until the last one. So how much you can get only depends on the order and the amount is not calculated when you trigger getting, while the total amount is divided randomly in advance, at creating the game. After hundreds of games, I found some potential rules as follows.

 

  • Rule 1: The sum of all red envelops equals the total amount.
  • Rule 2: There is no empty red envelop which means the minimum is 0.01.
  • Rule 3: Keep random, but avoid huge gap. For example, there's total amount 100 with 10 red envelops. If one get 99, other nine people can get only 1 totally. It's boring for those nine people and they have no interest to get red envelops.
  • Rule 4: The order doesn't matter. For example there are 100 red envelops, the first one may get the most, may also get the least and the possibility is the same. Someone thinks the first one may have higher possibility to get big money than the last one. Nope. According to my experience absolutely NO. As I said the possibility is the same. Just like lucky draw, everyone has chance to get the biggest, and the possibility is same to everyone.

 

Problem

Now we can define our problem and simulate the game in SAP HANA with stored procedure. So, the problem can be defined as given the total amount, # of red envelops and the minimum these three parameters, please return these random red envelops in an order based on the rules above. So, the potential stored procedure should look like the following.

 

CREATE PROCEDURE HONGBAO (
IN AMOUNT DECIMAL(10, 2),
IN NUM INTEGER,
IN MIN DECIMAL(10, 2),
OUT HONGBAO_RESULT TABLE(SEQ INTEGER, AMOUNT DECIMAL(10, 2)))
LANGUAGE SQLSCRIPT
READS SQL DATA AS
BEGIN
  ...
END;











 

After creating the stored procedure, we should be able to call it to get the result. Here is an example. The champion is in red box.

CALL HONGBAO(100, 5, 0.01, ?);











 

4_1.png

 

There're already several solutions to solve this problem, but I don't think they obey the rules well. The ideas of the following solution 1 and 2 came from this article (Sorry it's in Chinese ). I made some modifications and translated them into SAP HANA SQLScript. Finally I improved solution 2 and got my solution 3.

 

Simulating WeChat's Hongbao - Solution 1

Idea: For example, total amount 100, 5 red envelops, minimum 0.01. The first red envelop randoms from 0.01 to 100 - 0.04 = 99.96 (because of rule 2, you need to leave at least 0.04 for the rest four red envelops). Then the second one randoms from 0.01 to 100 - 0.03 - red envelop #1 and so on and so forth.

 

DROP PROCEDURE HONGBAO;
CREATE PROCEDURE HONGBAO (
IN AMOUNT DECIMAL(10, 2),
IN NUM INTEGER,
IN MIN DECIMAL(10, 2),
OUT HONGBAO_RESULT TABLE(SEQ INTEGER, AMOUNT DECIMAL(10, 2)))
LANGUAGE SQLSCRIPT
READS SQL DATA AS
BEGIN
  DECLARE SEQ_ARR INTEGER ARRAY;
  DECLARE AMOUNT_ARR DECIMAL(10, 2) ARRAY;
  DECLARE I INTEGER;
  DECLARE CURR_AMOUNT DECIMAL(10, 2) := :AMOUNT;
  DECLARE SAFE_AMOUNT DECIMAL(10, 2);
  FOR I IN 1 .. :NUM - 1 DO
  SEQ_ARR[:I] := :I;
  SAFE_AMOUNT := :CURR_AMOUNT - (:NUM - :I) * :MIN;
  AMOUNT_ARR[:I] := TO_DECIMAL(RAND() * :SAFE_AMOUNT + :MIN, 10, 2);
  CURR_AMOUNT := :CURR_AMOUNT - :AMOUNT_ARR[:I];
  END FOR;
  SEQ_ARR[:NUM] := :NUM;
  AMOUNT_ARR[:NUM] := :CURR_AMOUNT;
  HONGBAO_RESULT = UNNEST(:SEQ_ARR, :AMOUNT_ARR) AS (SEQ, AMOUNT);
END;





 

After creating the stored procedure successfully, we can now test it.

CALL HONGBAO(100, 5, 0.01, ?);




 

5_1.png

 

CALL HONGBAO(10, 10, 0.01, ?);




 

6_1.png

 

Have you found the problem? Yes, it breaks rule 3 and rule 4. Because of the algorithm, the first one has the highest possibility to random a big red envelop and become the champion. For simplicity, I did not provide mathematical proofs here, but it's easy to understand, isn't it? If the first several ones already occupy the most, the rest has no chance to get a big red envelop. In short, with this algorithm, the huge gap is possible and the order matters.

 

Simulating WeChat's Hongbao - Solution 2

Idea: For example, total amount 100, 5 red envelops, minimum 0.01. The first red envelop randoms from 0.01 to (100 - 0.04) / (5 - 1) (4 means how many red envelops need to random). Then the second one randoms from 0.01 to (100 - 0.03 - red envelop #1) / (5 - 2) (now only 3 red envelops need to random) and so on and so forth. So, the only difference between solution 2 and solution 1 is that in solution 2 the upper limit of random is set to the current average of left random red envelops which can prevent from the huge gap.

 

DROP PROCEDURE HONGBAO;
CREATE PROCEDURE HONGBAO (
IN AMOUNT DECIMAL(10, 2),
IN NUM INTEGER,
IN MIN DECIMAL(10, 2),
OUT HONGBAO_RESULT TABLE(SEQ INTEGER, AMOUNT DECIMAL(10, 2)))
LANGUAGE SQLSCRIPT
READS SQL DATA AS
BEGIN
  DECLARE SEQ_ARR INTEGER ARRAY;
  DECLARE AMOUNT_ARR DECIMAL(10, 2) ARRAY;
  DECLARE I INTEGER;
  DECLARE CURR_AMOUNT DECIMAL(10, 2) := :AMOUNT;
  DECLARE SAFE_AMOUNT DECIMAL(10, 2);
  FOR I IN 1 .. :NUM - 1 DO
  SEQ_ARR[:I] := :I;
  SAFE_AMOUNT := (:CURR_AMOUNT - (:NUM - :I) * :MIN) / (:NUM - :I);
  AMOUNT_ARR[:I] := TO_DECIMAL(RAND() * :SAFE_AMOUNT + :MIN, 10, 2);
  CURR_AMOUNT := :CURR_AMOUNT - :AMOUNT_ARR[:I];
  END FOR;
  SEQ_ARR[:NUM] := :NUM;
  AMOUNT_ARR[:NUM] := :CURR_AMOUNT;
  HONGBAO_RESULT = UNNEST(:SEQ_ARR, :AMOUNT_ARR) AS (SEQ, AMOUNT);
END;

 

The only modification is in line 18, we set the upper limit of random to the current average of left random red envelops. Now we can also have a test.

CALL HONGBAO(100, 5, 0.01, ?);

 

7_1.png

 

CALL HONGBAO(10, 10, 0.01, ?);

 

8_1.png

 

The huge gap seemed to disappear, however there comes a new problem. Because of setting the random upper limit to current average, the first one has much lower possibility to become the champion. Do you know why? So, our rule 4 is broken. The order matters and no one is willing to get the first red envelop. After my observation, there's high possibility the champion appears in the second half. I think we can prove it with mathematics.

 

Simulating WeChat's Hongbao - Solution 3

Idea: Here comes my improved solution 3. We just need to shuffle the result in solution 2 in order to avoid the importance of order. So, we added line 22 - 24.

 

DROP PROCEDURE HONGBAO;
CREATE PROCEDURE HONGBAO (
IN AMOUNT DECIMAL(10, 2),
IN NUM INTEGER,
IN MIN DECIMAL(10, 2),
OUT HONGBAO_RESULT TABLE(SEQ INTEGER, AMOUNT DECIMAL(10, 2)))
LANGUAGE SQLSCRIPT
READS SQL DATA AS
BEGIN
  DECLARE SEQ_ARR INTEGER ARRAY;
  DECLARE AMOUNT_ARR DECIMAL(10, 2) ARRAY;
  DECLARE I INTEGER;
  DECLARE CURR_AMOUNT DECIMAL(10, 2) := :AMOUNT;
  DECLARE SAFE_AMOUNT DECIMAL(10, 2);
  FOR I IN 1 .. :NUM - 1 DO
  SEQ_ARR[:I] := :I;
  SAFE_AMOUNT := (:CURR_AMOUNT - (:NUM - :I) * :MIN) / (:NUM - :I);
  AMOUNT_ARR[:I] := TO_DECIMAL(RAND() * :SAFE_AMOUNT + :MIN, 10, 2);
  CURR_AMOUNT := :CURR_AMOUNT - :AMOUNT_ARR[:I];
  END FOR;
  SEQ_ARR[:NUM] := :NUM;
  AMOUNT_ARR[:NUM] := :CURR_AMOUNT;
  AMOUNT_T = UNNEST(:AMOUNT_ARR) AS (AMOUNT);
  AMOUNT_T = SELECT * FROM :AMOUNT_T ORDER BY RAND();
  AMOUNT_ARR := ARRAY_AGG(:AMOUNT_T.AMOUNT);
  HONGBAO_RESULT = UNNEST(:SEQ_ARR, :AMOUNT_ARR) AS (SEQ, AMOUNT);
END;

 

OK. Let's also have a test.

CALL HONGBAO(100, 5, 0.01, ?);

 

9_1.png

 

CALL HONGBAO(10, 10, 0.01, ?);

 

10_1.png

 

That's it! Everything looks good now.

 

Conclusion

Till now we've simulated WeChat Hongbao with stored procedrue in SAP HANA. We can now get virtual red envelops from SAP HANA. Have fun.

 

Hope you enjoyed reading my blog and get more red envelops in WeChat. If you have better solutions, please share with me. Thank you very much.

 

1.jpg

 

Image source

For last couple of days I was struggling to understand Version control and Change Management features of SAP HANA. I am new to HANA and to SAP.I went through many documents and blogs to understand this so thought of sharing my research with all of you. I hope I will be able to help my some newbies like me

For version control you need to make sure that all development is done through Project window on local repository. (I was reading somewhere that after SPS09 objects can be directly modified in Repository window but we are using SPS08 so we had to use Project window).  Earlier we were directly modifying/Activating objects using SYSTEM window so we were not able to use version control conflict resolution features. To modify objects using Project window you need to :

 

    1. Create local repository for your server using Repository->Create Repository Workspace. By unchecking  “Use Default Workspace” option you can give your own (more readable) name to repository.

Pic1.png

 

2. Once Repository is created then you need to checkout objects on which you want to work. For New objects you can directly create them using Project window.  Right click on object in repository window-> Checkout.

PIC2.png

 

3. Create Project for checked out objects. In Project Explorer go to File->New Project. Uncheck default location option to map Project to folder which has objects which you have checkout for your development.

PIc3.png

Pic4.png

 

4. Right click on Project-> Team-> Share Project.

Pic5.png

 

5. Once Project is Shared you can modify objects inside and also add new objects. All these objects will be tracked under version control. You can saves changes to local repository but when you will activate object(Team->Activate)  then system will compare  checked out copy of object with remote(server) copy and if your local copy will not match with server copy (i.e. somebody else deployed their changes while you were working on your changes) then you will get error related to conflict resolution.

Pic6.png

 

 

6. At this point you have option handle the conflict by merging your code with server code using merge tool or deploying local copy on server or using remote copy for your development.

Pic7.png

 

7. Merge tool will show you exact mismatch between local and remote and provides you option to merge changes.

Pic9.png


8.You can also see history of changes by Right click on object in Repository-->Show in History. This will give you information about different revision of code. You can also see the code deployed in selected revision. You can check out the code associated with any revision and deploy it as current revision. You also have option to compare codes associated with two different revisions.

 

Pic8.png

 

9.One important point to make is that all these version control features works only for content objects so if you have tables/procedures created as catalog objects then you have to use HDB scripts to create table/procedures and other catalog objects and create them as content objects. This will also allow these objects to be added as part of a DU(Delivery Unit) and can be migrated to another system using Transpoter. 

 

 

Hope you liked the post. Please provide your feedback/comments.

 

Thanks,

PB.

In the first of a two-part episode, Raj Erode, IT Architect at CenterPoint Energy joins us to discuss three killer SAP HANA use cases for CRM and predictive scenarios and their incredibly innovative Internet of Things (IoT) and Big Data scenario.  Centerpointe won the 2014 HANA Innovation Award for their Big Data scenario.

We hope you enjoy hearing Centerpointe’s first-hand experience with mission-critical SAP HANA.  Please let us know your feedback in the comments below.

 

HE5.jpg

To get more real-world customer HANA stories, subscribe to our iTunes or SoundCloud feed for weekly podcasts that will cover multiple in-production customer use case scenarios for SAP HANA.

Also, if you’ve got a killer SAP HANA scenario and would like to share it on the HANA Effect podcast, please contact me.

Transcript SAP HANA Effect Episode 5

In the second of a two-part episode, Mike Golz, CIO of the Americas for SAP joins in this two part episode to walk through SAP’s internal journey to run all its mission-critical systems natively on SAP HANA. Listen how SAP “drinks its own champagne” and uses its own products to run better and run simple with SAP HANA.

We hope you enjoy hearing SAP’s first-hand experience with mission-critical SAP HANA.  Please let us know your feedback in the comments below.

 

HE4.jpg

To get more real-world customer HANA stories, subscribe to our iTunes or SoundCloud feed for weekly podcasts that will cover multiple in-production customer use case scenarios for SAP HANA.

Also, if you’ve got a killer SAP HANA scenario and would like to share it on the HANA Effect podcast, please contact me.


Transcript:  SAP HANA Effect Episode 4

Sponsored by

xeon_i_ww_rgb_3000

In the first of a two-part episode, Mike Golz, CIO of the Americas for SAP joins in this two part episode to walk through SAP’s internal journey to run all its mission-critical systems natively on SAP HANA. Listen how SAP “drinks its own champagne” and uses its own products to run better and run simple with SAP HANA.  For more info on how SAP Run’s SAP, visit their SCN community blog.

We hope you enjoy hearing SAP’s first-hand experience with mission-critical SAP HANA.  Please let us know your feedback in the comments below.

 

HE3.jpg

To get more real-world customer HANA stories, subscribe to our iTunes or SoundCloud feed for weekly podcasts that will cover multiple in-production customer use case scenarios for SAP HANA.

Also, if you’ve got a killer SAP HANA scenario and would like to share it on the HANA Effect podcast, please contact me.

 

 

 

SAP HANA Effect Episode 3 Transcript

Link to the Bill McDermott/Jim Cramer video

Naga Nallaiah from Varian Medical Systems joins us to talk about their big bet on SAP ERP on SAP HANA. Listen how Varian went through a huge database migration to get the speed and power of SAP HANA for their mission-critical SAP ERP system– for their very first SAP HANA project!

We hope you enjoy hearing Naga’s first-hand experience with mission-critical SAP HANA.  Please let us know your feedback in the comments below.

 

HE2.jpg

To get more real-world customer HANA stories, subscribe to our iTunes or SoundCloud feed for weekly podcasts that will cover multiple in-production customer use case scenarios for SAP HANA.

Also, if you’ve got a killer SAP HANA scenario and would like to share it on the HANA Effect podcast, please contact me.

 

 

 

SAP HANA Effect Episode 2

Sponsored by

xeon_i_ww_rgb_3000

Paul Jackson, ABAP Manager for Jabil Circuits stops by to explain his company’s first HANA project: ERP Sidecar to accelerate ABAP inventory reports. Listen how Paul’s team was able to reduce reporting times from 12 hours down to 3 minutes using SAP HANA as a sidecar database—- with a part-time team of 3 people!

We hope you enjoy hearing Paul’s first-hand experience with mission-critical SAP HANA.  Please let us know your feedback in the comments below.

 

HE1.jpg

To get more real-world customer HANA stories, subscribe to our iTunes or SoundCloud feed for weekly podcasts that will cover multiple in-production customer use case scenarios for SAP HANA.

Also, if you’ve got a killer SAP HANA scenario and would like to share it on the HANA Effect podcast, please contact me.

 

 

 

SAP HANA Effect Episode 1 Transcript

 

 

Sponsored by

xeon_i_ww_rgb_3000

FREQUENTLY FACED ISSUES IN HANA

 

Issues encountered while creating Analytical views.


Let me explain this in an elaborated form:


Error Message:-


“Central table not unique. Attributes defined for different tables.”


CASE 1:- when we take a counter.


Problem Description:-

When we create an analytical view with tables and attribute views with a counter for any field. As Counter is itself a measure, we don’t need to define any other measure. When we validate and activate it. We will get the error as shown below:

 

 

1.PNG

 

 

 

 

Steps to recreate the error:


1.  Create an analytical view.

3.PNG

 

2. Create a Counter.

7.PNG

1.png

 

 

3.  Activate the view.

2.PNG


Here you will get the error.

 

Now,

4. Delete the counter you created and mark the same field as a measure in semantics.

5.PNG

 

 

There is no error now.

 

If you re designate the measure as an attribute in the semantics and create a counter in the same way. It will be activated successfully.

8.PNG

 

REASON  : For successful activation of an Analytical view, it requires a unique central table. For example when we create an attribute view with multiple tables, then we need to define a “key attribute” without which central table identification is not possible. Thus a “key attribute” is essential for successful activation of an Attribute view with multiple tables. In case of Analytical view as shown in the above example, when we define a measure, the table of the field which has been used as measure will automatically get designated as “central table” by HANA. In this case when we took a counter on Material Number (or any field for that matter can be taken as example) , the system was not able to identify a unique central table and an error message was generated. Now when we removed the counter and took the material number as measure in the semantics and marked it as count, the view was successfully activated.  Now, when we change material number back to attribute from measure and define the same counter on material number again, the before mentioned error will not be encountered again. Strange? The reason is that when we took material number as measure and activated the view, HANA identified the table of the field taken as measure (MARA in this case) as the central table which was not available earlier. Now even after changing Material number to attribute and creating a counter, the error message will not be seen as system is already aware of a central table for the view.

 



CASE 2: When we do not take any measure and mark the “Data Category” as Blank.

 

Steps to recreate the error:

1.  Create an analytical view.

10.PNG

 

2. Mark the Data Category as Blank and not Cube as we are not going to take any measure.

12.PNG11.PNG

 

 

3.  Activate the view.

13.PNG

 

 

Here you will get the error message as “Central Table not Unique”.

14.PNG

 

15.PNG

It could not Identify the Central table. As an evidence we can see here that the "central entity" is blank:

16.PNG

 

4.  Now, Mark any field in the Semantics as Measure and activate it. The view will successfully be activated.

17.PNG

 

Now, Lets see the Central Entity in the Properties section:

18.PNG

Here It automatically took the table as central table from which the field was given

as measure.

 

5.  Now, after reverting the change i.e. again marking the field as an attribute and    activating the view. It will activate successfully.

19.PNG


If the system is not able to pick the central table, we can anytime choose the central table for the central entity to avoid the error.

 

This Content is tested for both version SPS08 and SPS09 of HANA.

 

Please let me know if you have any queries.

Hi everyone, Happy Lunar New Year!

 

It's now Chinese new year or Spring Festival, the most important festival in China. Usually we stay together with our families and friends in the holiday, just like Christmas in many other countries. At the beginning of Chinese new year, people text some greetings to relatives, friends and colleagues traditionally, e.g., 新年快乐(Happy new year), 恭喜发财(Congratulations for prosperity, wishing you prosperity, something like that). I've sent lots of greetings and also received lots of greetings. As we know, SAP HANA has text analysis feature, so an idea came to my mind, can SAP HANA extract and analyze these greetings? I just did some tests and failed. But the good news is that we can do some customization in SAP HANA text analysis and let SAP HANA recognize these greetings. So in this post, I'll share with you how to realize that. We really want some greetings from SAP HANA.

 

Something interesting... Ram or Sheep or Goat?

Before we start the technical stuff, I wanna show you something interesting... Yesterday when I opened Chrome, the following Google doodle appeared. Oh, yeah, it's the year of ram (see Chinese zodiac for more details). Google also celebrated Chinese new year with us, haha. Unfortunately, the doodle is gone. If you want to see it again, you can visit Lunar New Year 2015 and Google Doodle Rings in Chinese Lunar New Year. The interesting thing is that there's a debate what's the real animal ram, sheep or goat? If you're interested, please have a look at Whatever Floats Your Goat: The 2015 Lunar New Year Animal Is Up For Debate : Code Switch : NPR

 

1.PNG

 

Greetings from SAP HANA - Customizing EXTRACTION_CORE

In this section, we expect some greetings from SAP HANA. First of all, let's try something without the customization. NOTICE: All tests are based on SAP HANA SPS 09 Rev. 91.

 

DROP SCHEMA TA CASCADE;
CREATE SCHEMA TA;
SET SCHEMA TA;
CREATE COLUMN TABLE TA_TABLE (
  ID INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  CONTENT NVARCHAR(200),
  LANG NVARCHAR(2)
);
INSERT INTO TA_TABLE (CONTENT, LANG) VALUES ('新年快乐', 'ZH');
INSERT INTO TA_TABLE (CONTENT, LANG) VALUES ('恭喜发财', 'ZH');
CREATE FULLTEXT INDEX TA_INDEX ON TA_TABLE (CONTENT)
CONFIGURATION 'EXTRACTION_CORE'
LANGUAGE COLUMN LANG
TEXT ANALYSIS ON;
SELECT * FROM "$TA_TA_INDEX";











 

As you can see from the above SQLs, we first created the source table with a language column and inserted two greetings, 新年快乐(Happy new year), 恭喜发财(Congratulations for prosperity, wishing you prosperity, something like that). Then we created a full text index on the source table and related column with the configuration "EXTRACTION_CORE" (See all configurations from Text Analysis - SAP HANA Text Analysis Developer Guide - SAP Library) to make the text analysis happen. But the result of text analysis is nothing (see structure of $TA table from Structure of the $TA Table - SAP HANA Text Analysis Developer Guide - SAP Library), since there is no "GREETING" in the predefined entity types.

 

2.PNG

 

So what can we do now? You can find the solution from SAP HANA Text Analysis Extraction Customization Guide - SAP Library which shows you how to customize the text analysis extraction. Want a video? Here you go. SAP HANA Academy - Text Analysis: 10. Custom Dictionaries - YouTube Now let's do it by ourselves.

 

Step 1: Create a XS project and share it with repo

 

3_1.png

 

Step 2: Create the .hdbtextdict file, in this file you define your own TA_TYPE(entity_category) and TA_TOKEN(entity_name). We defined "GREETING" for entity_category, "新年快乐" and "恭喜发财" for entity_name.

 

3_2.png

 

GREETING.hdbtextdict

<?xml version="1.0" encoding="UTF-8"?>
<dictionary xmlns="http://www.sap.com/ta/4.0">
  <entity_category name="GREETING">
  <entity_name standard_form="新年快乐">
  </entity_name>
  <entity_name standard_form="恭喜发财">
  </entity_name>
  </entity_category>
</dictionary>










 

Step 3: Create the .hdbtextconfig file, in this file you need to include your .hdbtextdict file in step 2. Here we first copy the content of SAP HANA's standard EXTRACTION_CORE (sap.hana.ta.config::EXTRACTION_CORE) and include our custom dictionary.

 

3_3.png

 

EXTRACTION_CORE_CUSTOM.hdbtextconfig

...
    <!-- List of repository objects containing Text Analysis extraction dictionaries. -->
    <property name="Dictionaries" type="string-list">
        <string-list-value>TACustom::GREETING.hdbtextdict</string-list-value>
    </property>
...









 

Step 4: Create our full text index using the custom .hdbtextconfig in step 3.

DROP FULLTEXT INDEX TA_INDEX;
CREATE FULLTEXT INDEX TA_INDEX ON TA_TABLE (CONTENT)
CONFIGURATION 'TACustom::EXTRACTION_CORE_CUSTOM'
LANGUAGE COLUMN LANG
TEXT ANALYSIS ON;
SELECT * FROM "$TA_TA_INDEX";









 

Now we can receive greetings from SAP HANA.

 

4_1.png

 

There are a lot of greetings in Chinese new year, you can enrich your .hdbtextdict file as many as you want.

 

Sentiment from SAP HANA - Customizing EXTRACTION_CORE_VOICEOFCUSTOMER

Now we've already got greetings from SAP HANA, what about the sentiment analysis of greetings? From curiosity I tried both English and Chinese for the sentiment analysis. Now let's have a look.

 

INSERT INTO TA_TABLE (CONTENT, LANG) VALUES ('Happy new year', 'EN');
INSERT INTO TA_TABLE (CONTENT, LANG) VALUES ('Congratulations for prosperity', 'EN');
SELECT * FROM TA_TABLE;








 

5.PNG

 

As you can see, first I added two greetings in English. "新年快乐" means "Happy new year", while "恭喜发财" means "Congratulations for prosperity". Then we made the text analysis with the configuration "EXTRACTION_CORE_VOICEOFCUSTOMER" which detects the voice of customer.

 

DROP FULLTEXT INDEX TA_INDEX;
CREATE FULLTEXT INDEX TA_INDEX ON TA_TABLE (CONTENT)
CONFIGURATION 'EXTRACTION_CORE_VOICEOFCUSTOMER'
LANGUAGE COLUMN LANG
TEXT ANALYSIS ON;
SELECT * FROM "$TA_TA_INDEX";







 

From the result of text analysis, we can find SAP HANA succeeded to extract sentiment in both English greetings, but failed to detect sentiment in Chinese. In addition, SAP HANA can extract what the sentiment exactly is (in red box) and the topic of the sentiment (in blue box). There is an improvement in SAP HANA SPS09, that's the TA_PARENT column. With TA_PARENT, you can bind the sentiment and topic easily.

 

  • For "Happy new year", "Happy" is a weak positive sentiment. So happy for what? For "new year".
  • For "Congratulations for prosperity", "Congratulations" is also a weak positive sentiment. So congratulations for what? For "prosperity".

 

6_1.png

 

It seemed the sentiment analysis supports English perfectly, so what about for Chinese? Don't worry, like customizing EXTRACTION_CORE in the previous section, we can also customize the configuration EXTRACTION_CORE_VOICEOFCUSTOMER. Since Chinese is a non-whitespace language, we need to follow Sentiment Analysis Customization in Nonwhitespace Languages. What we want to extract is similar with English as follows.

 

  • For "新年快乐", "新年" means "new year" and "快乐" means "happy".
  • For "恭喜发财", "恭喜" means "congratulations" and "发财" means "prosperity" or something like that.

 

So, we can simply map these characters to sentiments and topics. Let's give it a shot.

 

Step 1: Create the .hdbtextdict file, the XML structure is identical to the previous GREETING.hdbtextdict. However, in order to do the sentiment analysis, we can only use the following five entity_category:

 

  • CustomTopic
  • CustomPositive
  • CustomNegative
  • CustomNeutral
  • CustomProblem

 

7_2.png

 

GREETING_VOC.hdbtextdict

<?xml version="1.0" encoding="UTF-8"?>
<dictionary xmlns="http://www.sap.com/ta/4.0">
  <entity_category name="CustomTopic">
  <entity_name standard_form="新年">
  </entity_name>
  <entity_name standard_form="发财">
  </entity_name>
  </entity_category>
  <entity_category name="CustomPositive">
  <entity_name standard_form="快乐">
  </entity_name>
  <entity_name standard_form="恭喜">
  </entity_name>
  </entity_category>
</dictionary>



 

Step 2: Create the .hdbtextconfig file. Similar with the previous .hdbtextconfig file, in this step we first copy the content of SAP HANA's standard EXTRACTION_CORE_VOICEOFCUSTOMER (sap.hana.ta.config::EXTRACTION_CORE_VOICEOFCUSTOMER) and include our custom dictionary in step 1.

 

7_3.png

 

EXTRACTION_CORE_VOC_CUSTOM.hdbtextconfig

...
<!-- List of Text Analysis extraction dictionaries for Sentiment Analysis. -->
    <property name="Dictionaries" type="string-list">
...
      <string-list-value>TACustom::GREETING_VOC.hdbtextdict</string-list-value>
    </property>
...



 

Step 3: Create our full text index using the custom .hdbtextconfig in step 2.

DROP FULLTEXT INDEX TA_INDEX;
CREATE FULLTEXT INDEX TA_INDEX ON TA_TABLE (CONTENT)
CONFIGURATION 'TACustom::EXTRACTION_CORE_VOC_CUSTOM'
LANGUAGE COLUMN LANG
TEXT ANALYSIS ON;
SELECT * FROM "$TA_TA_INDEX";



 

8_1.png

 

Although we can find "CustomTopic" and "CustomPositive" in TA_TYPE column, the result is partially correct and not satisfactory. What we want are well-defined topics and sentiments just like the result in English. So why is the result different from English? The reason is that we just defined the custom dictionary, but for sentiment analysis, there are other factors like the extraction rules. However, most greetings in Chinese do not obey the rule. For simplicity, we can just add one character in both Chinese greetings to meet the extraction rule without changing its meaning.

 

INSERT INTO TA_TABLE (CONTENT, LANG) VALUES ('新年很快乐', 'ZH');
INSERT INTO TA_TABLE (CONTENT, LANG) VALUES ('恭喜你发财', 'ZH');
SELECT * FROM "$TA_TA_INDEX";



 

9_1.png

 

Now we have the same result with greetings in English. SAP HANA detected sentiment in both Chinese greetings and each greeting has a topic and positive sentiment in TA_TYPE. That's it!

 

Conclusion

In this post, we've customized two text analysis configurations in SAP HANA, i.e., EXTRACTION_CORE and EXTRACTION_CORE_VOICEOFCUSTOMER as well. With customizing text analysis extraction, we received greetings and positive sentiments from SAP HANA in Chinese new year.


Hope you enjoyed reading my blog. Happy Chinese new year and wish you prosperity! 新年快乐,恭喜发财!

 

happynewyear.gif

gongxi.gif

 

Image source

Having shown you how to install the Automated Predictive Library (APL) link below

How to Install the Automated Predictive Library in SAP HANA

 

We can now look to use the samples provided with the APL.

 

I would advise copying the SQL samples locally from the APL source location, probably on the HANA box, but also available within the Predictive Analysis (PA) 2.0 download inside the SAPPAAPL2000_0.TGZ

APL SQL Samples.png

 

First you will need to setup a user or be granted the relevant right to your user.  If you look at the apl_admin.sql it contains all the relevant statements.

 

The user roles needed for this would be

           AFL__SYS_AFL_APL_AREA_EXECUTE

     AFLPM_CREATOR_ERASER_EXECUTE

 

There is one step not shown in the sample SQL, which is creating the APL_SAMPLES schema, this is straightforward such as

     create schema APL_SAMPLES;

 

There is secondary SQL file that your USER needs to which creates the Table Types.  This is all within the apl_create_table_types.sql

You should run this and check that it is successful.  These are currently user specific so any user that requires access to the APL would need to execute this.

 

The table types can be found in HANA Studio under Procedures

 

Table Types.png

 

If you created the schema you will already have SELECT permission on this APL_SAMPLES schema if not you need to request this.

The data now needs to be imported into the schema, this straight forward with HANA Studio.

 

Right click the schema APL_SAMPLES and choose import

Import into Schema v2.png

We can now browse to the server location where the APL was extracted to.

Import into Schema.png

Choose our sample data tables and import them.

Import Samples.png

The samples should now all be configured and available for use directly via SQL or using Predictive Analysis 2.0.

 

I came across an issue with the CENSUS data set and have attached a fix here.  When using the APL it supports INTEGER datatype but it does not support  TINYINT or SMALLINT, so if you are using PA 2.0 and not just the APL SQL scripts you will receive an error.

 

To fix this you can run the following SQL

 

SET SCHEMA APL_SAMPLES;

 

ALTER TABLE CENSUS ALTER ("age" INTEGER, "education-num" INTEGER, "capital-loss" INTEGER, "hours-per-week" INTEGER, "class" INTEGER);

 

 

Let me know how you get on, next I aim to show how these samples can be used with PA 2.0

Having recently installed the the new Automated Predictive Library (APL), the former KXEN Infinite Insight Libraries inside HANA, I wanted to share my experience.

 

The benefit the APL brings is that we can now utilise the KXEN libraries directly in-memory within the HANA Platform so the data never needs to leave HANA.  These libraries are a core part of Predictive Analytics 2.0 and are found in both the Automated and Expert perspectives within this.  As the name suggests when using the APL we have attempted to automate as much of the predictive process as possible.

 

With the Predictive Analytics 2.0 release we have actually released version 1.1 of the APL, (version 1.0 was an internal only release).

 

With the APL 1.1  the following predictive functions have been included.

  • Classification
  • Regression
  • Clustering
  • Time Series
  • Key Influencers

 

For the official release information please see the SAP Note below

http://service.sap.com/sap/support/notes/2111573

 

The APL is actually contained within Predictive Analytics 2.0 download, so first you will need to download that component first.

 

APL Download v2.png

The official APL documentation can be found at http://help.sap.com/pa

APL Help.png

 

 

The pre-requisits for the APL are

1. SAP HANA SP 9 or above - I used HANA revision 92

2. unixODBC - I downloaded and compiled from the source here.  I won't repeat the instructions as they are very clear at http://www.unixodbc.org/

3. AFL SDK  1.00.090 - I don't believe you need to install this as it already comes with HANA

unixODBC.png

Once unixODBC is installed you should find it in /usr/local/lib as shown below

 

usr local lib.png

 

As shown in the APL documentation on page7, you need to create a symbolic link for libodbc.so.1 such as


  ln –s libodbc.so.2 libodbc.so.1


Now you need to add the /usr/local/lib to the LD_LIBRARY_PATH.


LD_LIBRARY_PATH=$LD_LIBRARY_PATH=:/usr/local/lib


For me the easiest way to know this was in place was to add it to the hdbenv.sh, Warning this may not be the correct place to edit, but it worked for me.

hdbenv.png


LD_LIB.png


The APL install itself is very easy, just upload and extract the TGZ file and then launch hdbinst as root.  The file I uploaded was from the full PA 2.0 download and called "SAPPAAPL2000_0.TGZ"

 

mo-dad69e91a:/tmp/apl-1.1.0.20-linux_x64/installer # ls -l

total 28

-rwxr-xr-x 1 30003 shadow 19128 Nov 20 22:01 hdbinst

drwxr-xr-x 5 root  root    4096 Feb 12 13:57 instruntime

drwxr-xr-x 2 root  root    4096 Feb 12 13:57 packages

mo-dad69e91a:/tmp/apl-1.1.0.20-linux_x64/installer # ./hdbinst

 

Enter your SYSTEM ID and credentials and the APL will be installed, HANA will be restarted to allow the install to complete.

That is the software installed you now need to configure it by running the supplied SQL scripts.

 

If you navigate to the extracted TGZ location you should see samples/sql and in here you can open the apl_admin.sql as shownapl_admin.png

 

In here there is some "sample" SQL code to use :-)

 

You need to enable the HANA ScriptServer if it is not already running within your environment - either directly via SQL or in HANA Studio at Admin Console, Configuration, daemon.ini

 

alter system alter configuration ('daemon.ini', 'SYSTEM') set ('scriptserver', 'instances') = '1' with reconfigure;

 

With that running you should now be able to check the APL is installed correctly, all of the APL function statements should return APL related rows such as shown below

 

Screen Shot 2015-02-19 at 15.38.50.png

 

Screen Shot 2015-02-19 at 15.38.58.png

Screen Shot 2015-02-19 at 15.39.04.png

Screen Shot 2015-02-19 at 15.39.27.png

 

As you can see the APL is now installed, now you may want to install and configure the APL and the samples provided, which can be found here -

Using the APL samples provided with Predictive Analysis 2.0

data_path.jpg


SAP has shunned its present data center trends which are directed towards the best-of-breed hardware components and rather embraced its patented in-memory system.


Five years back, SAP launched their super-quick HANA in-memory database. This caused a rebirth in the company, opening a fresh set of business opportunities. From that time, SAP has maintained its pace, releasing new or improved applications designed to run on the in-memory system.


February 3rd saw the company reveal the retooling of its defining business application in order to fit it to the in-memory platform. This SAP HANA suite, which is their 4th Business Suite, has been designed to run on any device, whether stationary or mobile, and it can also be deployed to on-premise, hybrid or cloud options. SAP made this announcement during a dedicated launch event held at the New York Stock Exchange.


This move to redesign and build its new software releases to only run on their specialized in-memory databases is clear proof that SAP eschews the present data center developments which have shown bias towards design of best-of-breed software and hardware components, being the simplest, most pragmatic and most efficacious method to carry out a data center refresh.


Breaking through past limitations


In explaining the move, SAP CEO, Mr. Bill McDermott, revealed that the retooling of its SAP HANA was meant to enable the company to surmount their past limitations and to make full use of the real-time as well as in-memory aptitudes of the SAP HANA suite.


He continued to say that SAP HANA, which was invented by Hasso Plattner, was always destined for re-invention to match the requirements of the digital age, along with the full SAP Business Suite. Given the present climate – the needs for businesses globally to penetrate new markets and connect with their clientele through every channel – McDermott explained that there was now an innovative platform that could assist businesses to register the kind of growth they need.

SAP was excited for the change, citing the launch to be a “historic day” and one that would mark the start of phasing out the complexities of the IT stack from the 20th century.


The retool process took SAP experts over four years, during which time the business suite was changed over from traditional on-premise servers to their new in-memory platform. Nevertheless, the company is well known for its persistent and systematic approach when it comes to releasing top quality applications.


Useful for an Array of Line-of-Business Applications


SAP revealed that its S/4 HANA includes directed configuration schemes for adoption, an instinctual user interface design that is applicable to users serving in various capacities within an enterprise and working on nearly any device. Other features include a highly streamlined data model, revamped real-time enterprise processes and top level granularity with on-the-fly insight.


At the launch in New York, demonstration for the HANA database was carried out using one of the most popular apps in the suite, the SAP Simple Finance. The company hopes to use this new database to expand their current portfolio of cloud-based products, especially those from its subsidiary holdings including SuccessFactors, Ariba and Fieldglass.


This platform will give customers the ability to choose their pace in cloud applications, depending on their needs, and simultaneously maintain the enterprise benefits and business capabilities of their current SAP solutions, according to Mr. McDermott.

 

 

Sources

http://www.eweek.com/database/sap-retools-main-business-suite-to-run-on-in-memory-hana.html

http://www.firstpost.com/business/s4-hana-saps-biggest-software-overhaul-two-decades-2081251.html

http://www.zdnet.com/article/saps-s4hana-master-plan-the-lingering-questions/

Actions

Filter Blog

By author:
By date:
By tag: