Archive by Author | richheilman

EnterpriseGeeks Annual TechEd Dinner 2013

Once again this year, the EnterpriseGeeks Thursday night dinner will return to “The Buffet” at the Wynn on October 24th.  We’ve always had a great crowd join us at this dinner, and I hope this year is no different.    You can simply meet us there at 7:15pm, or show up at the “Rock of Ages” theatre box office(this used to be Blue Man Group)on the Casino level at 7:00pm to walk over as a group.  The price per person for the buffet is about $35 and of course you will be responsible for your own bill.   Imagine Dragons will perform at 9:00pm so  you will have plenty of time to get back over to the Venetian.  See everyone there!

Table User Defined Functions( Table UDF ) in HANA

With the release of HANA 1.0 SP5, not only do we get a new SQLScript editor and debugger, but there are also a few new SQLScript language features introduced with this release as well. Today, I would like to introduce Table UDFs.  Table UDFs are read-only user-defined functions which accept multiple input parameters and return exactly one results table.  SQLScript is the only language which is supported for table UDFs. Since these functions are ready-only, only read-only statements can be used within the function.  So you may not use statements like INSERT, UPDATE or DELETE. Also, any procedure calls within the function must also be read-only.  Currently, you can only create these functions in the catalog via the SQL Editor.  The syntax for creating a new table UDF is pretty straight forward.  For those of you who have been working with SQLScript since the beginning, you may notice the resurrection of the CREATE FUNCTION statement. This statement was used to create SQLScript procedures in earlier versions of HANA.  Of course, it has since been replaced with CREATE PROCEDURE. The CREATE FUNCTION statement will now be used to create UDFs in HANA.  In the following example, I’m creating the table UDF called GET_BP_ADDRESSES_BY_ROLE which accepts one input parameter called “partnerrole”, and returns a table with the structure defined explicitly.  You can also use global types to define the output structure as well. This function will execute one SELECT statement which contains an INNER JOIN of two tables and returns the result set to the output parameter.

CREATE FUNCTION "SAP_HANA_EPM_DEMO".get_bp_addresses_by_role( partnerrole nvarchar(3))
    RETURNS table ( "PartnerId" NVARCHAR(10), "PartnerRole" NVARCHAR(3), 
                    "EmailAddress" NVARCHAR(255), "CompanyName" NVARCHAR(80), 
                    "AddressId" NVARCHAR(10), "City" NVARCHAR(40),
                    "PostalCode" NVARCHAR(10), "Street" NVARCHAR(60) )
 LANGUAGE SQLSCRIPT 
 SQL SECURITY INVOKER AS
 
BEGIN
 RETURN 
   select a."PartnerId", a."PartnerRole", a."EmailAddress", a."CompanyName",
          a."AddressId", b."City", b."PostalCode", b."Street"
           from "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::businessPartner" as a 
           inner join "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::addresses" as b
                      on a."AddressId" = b."AddressId"
                            where a."PartnerRole" = :partnerrole;
END;

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Once the above CREATE FUNCTION statement is executed via the SQL Editor, the new function will then show up in the catalog under the "Functions" folder.

12-7-2012 12-41-25 PM.png

The function can now be used in the FROM clause of your SELECT statements.   You can pass the input parameters as well.

select * from "SAP_HANA_EPM_DEMO".get_bp_addresses_by_role('02');

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Additional features to UDFs are planned for future support packages, including scalar UDFs, as well as the ability to create UDFs via the HANA Development Workbench directly in your XS projects. Very similar to how we can now create procedures in HANA 1.0 SP5.

Introducing the New SAP HANA SQLScript Debugger

For those who have been working with SQLScript for awhile, you know that it is quite cumbersome to try and figure out what has gone wrong when a procedure does not return the correct results. Of course we have the TRACE statement which can be inserted into the SQLScript procedure which then writes out the intermediate results to a trace table during execution. But this approach is flawed. Firstly, the inspection of the results has to be done after the entire procedure execution is complete.  You can’t see the results in real-time as the code is being executed.  Secondly, and maybe most importantly, what customer will allow you to insert TRACE statements into a procedure directly in a productive environment?  We need the ability to trace variables during execution without having to modify the procedure in any way. Would it not be great for developers, and support personnel to be able to debug SQLScript procedures directly using a real integrated debugger? Well, good news!  With the release of SAP HANA 1.0 SP5, I am pleased to announce the availability of the new SQLScript Debugger!

The new debugger is tightly integrated into SAP HANA Studio.   It uses the standard eclipse “Debug” perspective, so there is basically zero learning curve for those who are familiar with debugging in eclipse.   You can set/remove breakpoints, resume to the next breakpoint, and terminate the debug session. Other step features are still in the planning phases.

12-5-2012 10-43-37 AM.png

The most powerful feature of the debugger is of course the variable evaluation feature.  When the procedure is executed in the debugger, all of the variables are listed in the “Variables” tab including table variables. You can evaluate the values of the variables as you execute each statement of the SQLScript code.   For table variables, you can right-click and choose “Open Preview”.  This will open a tab at the bottom showing the data in table format.

12-5-2012 10-37-43 AM.png

As you probably have already realized, the new SQLScript debugger is a huge deal for HANA developers. We can now debug SQLScript procedures on the fly and see exactly what is going on during execution of each statement.  Currently, the debugger does have some restrictions which we are working to resolve over the next several revisions.  A list of these restrictions is documented in SAP Note 1779803. Finally, look for new videos coming soon to  SAP HANA Academy page showing how to use the new development tools delivered with SAP HANA 1.0 SP5.

Introducing the New SAP HANA SQLScript Editor

Recently, my colleague Thomas Jung introduced the new capabilities in SAP HANA 1.0 SP5, specifically the new SAP HANA Extended Application Services(XS) and the HANA Development perspective within SAP HANA Studio. Not only can you create XS artifacts like XSODATA and XSJS services, but you can also create SQLScript procedures using this new approach as well. Before SP5, procedures were created in the modeler view, very similar to the way you would create attribute, analytical, and calculation views. Using this new approach in SP5, you can now take advantage of the new SQLScript editor and debugger.

Like other XS artifacts, procedures can be created locally using eclipse based XS projects in the project explorer view.  These projects are then stored in the HANA repository as well as all of the associated resources. For more information about the project based development, see the “SAP HANA Studio Becomes a Development Workbench” section of Thomas’ blog. In this post, I would like to introduce the new approach to creating procedures using the new SQLScript editor. In future posts, I will cover some of the more specific features of the new editor as well as the debugger.

To create a new procedure, simply right click in your XS project, and choose “New”, then “File”. Enter the file name as the name of the procedure with a .procedure file extension.

clip_image001

The new editor supports on-the-fly syntax checking, code hints, and some code completion. You may notice that the new editor does not support an input or output pane to define input/output parameters like in previous support packages. Instead, you define the parameters in the CREATE PROCEDURE statement itself. Also, properties can be set directly in the procedure definition. The procedure code is written between the BEGIN and END statements.

image

Once the coding is complete and free of syntax errors, the artifact must be committed to the database. This is done by right-clicking on the procedure and choosing “Team”, then “Commit”. After the commit is complete, the procedure must be activated by choosing “Team”, then “Activate”. The activation process will create the runtime object in the _SYS_BIC schema just as it did in prior support packages. The orange cylinder on the icon means that the activation has been completed successfully.

clip_image004

There are currently a few restrictions in regard to the new SQLScript editor and debugger which we are working to resolve in a future revision. Please see SAP Note 1779803 for more information about these restrictions.  Refer to the new HANA Developer Guide for more information about how to create all of the new XS artifacts.   Watch for more posts covering more features of the new SQLScript editor and debugger on SCN as well as on saphana.com.  Lastly, look for new videos coming soon to  SAP HANA Academy page showing how to use the new development tools delivered with SAP HANA 1.0 SP5.

EnterpriseGeeks Annual TechEd Dinner 2012

Hey all, its that time of year again!  Now that I’ve gotten through most of my TechEd session materials, I can start thinking about the event itself.  After missing the Las Vegas TechEd last year, I’m really excited to be there this year and see all the Egheads!  Once again this year, the EnterpriseGeeks Thursday night dinner will return to “The Buffet” at the Wynn.  I think everyone has enjoyed this place over the last several years since there is such a wide variety of foods and a huge dessert bar(ahem….Tom…).  The Wynn hotel is just across the street from Palazzo and we can all walk over together.  Let’s say we all meet up outside of the “Blue Man Group” theater at 6:30pm(October 18th) to walk over.  You can of course meet us at the “The Buffet” anytime after as well. This should give us plenty of time to eat and relax before heading back for the 3 Doors Down concert which starts at 9:00pm.  The price per person for the buffet is about $35 and of course you will be responsible for your own bill. That is of course, except for the winner of the EnterpriseGeeks Biggest Loser contest which was initiated last year.  We will decide the winner during the week of the event, and that person gets a free pass to the buffet.  Yes, that’s right, for the person who lost the most weight over the last year, we will pay for them to put a little back on, but I will of course encourage him/her to join me in walking some of it off afterwards.   I’m not asking for RSVPs this year as it was pretty pointless in past years when going to this restaurant.  Hope to see you all there!

New Role @ SAP

Hi all. Some of you already know from having talked to you this week at SAPPHIRENOW in Orlando, but I just wanted to put out a note to the rest of the community as well, and tell you that I am transferring from the EPM CSA(Enterprise Performance Management Customer Solution Adoption) group into the HANA Product Management group where I will be working along side my friend, fellow egeek, and colleague, Thomas Jung.  I’ve been working on BPC NW since joining SAP back in 2007, and I have always wanted to work on the technology side of the house rather than on a specific application, so this new role will give me a chance to dig deep into the technology platform, and of course HANA specifically.  My focus will be on HANA development tools, SQLScript, ABAP on HANA, etc., so I plan to get back in touch with the developer community very soon with more content on SCN, as well as here on enterprisegeeks.com.  My disconnect from the developer community in the last 5 years is something that has always bothered me on some level, so I am very excited to re-engage.

It has been a great ride working with the EPM team.  From the BPC architecture team where I started, through BPC solution management, and finally the EPM CSA team, I’ve worked with some of the best people on the planet, and I am grateful for it, and humbled by it.  We’ve done some incredible things in the last 5 years with BPC and it will only get better in the future. So my best wishes to all my colleagues in the EPM solutions area.

So thanks and see ya later to the EPM group, and hello to the HANA Product Management team. I can’t wait to get started, lets push in-memory computing to its limits and beyond!

Later,

Rich

EnterpriseGeeks Annual TechEd Dinner

Ok EgHeads, its that time again.  Since Tom was the only founding member of the EnterpriseGeeks at the SAP TechEd in Las Vegas, we decided to have the annual TechEd dinner in Madrid this year where we will have at least 2 founding members, Tom and I.  Anyway, we asked for some suggestions, and Martin Lang has come through with a really good one I think.  That’s right, another meat feast it will be.  Join us on Tuesday, November 8th at around 10:00pm at El Rodizio.  Once again, it is a Brazilian style steakhouse sort of like Fogo de Chao, where we visited in Phoenix a couple years back.  You can find directions from the conference center to the restaurant here.  Please RSVP using the registration link here.  This will give us a better idea of who is coming, and how many I should make the reservation for.  See you all there!

UPDATE:  Apparently, it would be better to have dinner just before bed so that all that meat can sit in our stomachs throughout the night with no way to burn it off till morning, so lets move the time to 10:00pm  🙂

UPDATE: Yes, another one.  So we are moving our dinner to Tuesday night, November 8th at 10:00pm to accomodate people who want to attend other events on Wednesday night.