SAP HANA Table Functions

It has been very quite at the moment.Because nothing spectecular is happening in the Analysis Office world. SAP released Analysis Office 2.7 SP5. I still hope they get Analysis Office 2.8 back on track and without so much problems as they have today. But back to
topic.

Recently I had the opporunity to work with SAP HANA Table Functions. First I had a complex SAP HANA Calculation View with Joins, Aggregations and so on. But I read a blog post on the SCN
by Konrad Załęski and thought maybe I can use Table Functions to do the same as I do with my Calculation Views.

I use Eclipse 2018-09 (4.9.0) and the BW Modeling Tools 1.19.35 so maybe the options are not at the same position in another version. As far as I know SAP, the buttons are definitly not at
the same position anymore. 🙈

To reduce the complexity I used three calculation views to narrow down my problem step by step.

After I tested my SQL commands to get the same results, I could remove Calculation View 1 with this simple SQL Statement.

FUNCTION "SCHEMA"."test::test_function" ( ) 

RETURNS TABLE ("POSITION" NVARCHAR(4),
               "CHANNEL" NVARCHAR(3),
               "PRODUCT" NVARCHAR(20),
               "MATERIAL" NVARCHAR(18),
               "CURRENCY" NVARCHAR(5),
               "AMOUNT" DECIMAL(17,2),
               "YEAR" NVARCHAR(4))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
RETURN

/*
* Get all Position, Sales Channels for all Products and Currency 
* where Process = Allocation and Market Number is empty.
*/

SELECT DISTINCT "POSITION",
                "CHANNEL",
                "PRODUCT",
                "MATERIAL",
                "CURRENCY",
                sum( "AMOUNT" ) as "AMOUNT",
                "YEAR"
FROM "SAPABAP1"."/BIC/ATEST2" where "PROCESS" = 'A' and "MANUM" = ''
group by
                "POSITION",
                "PROCESS",
                "CHANNEL",
                "MANUM",
                "PRODUCT",
                "MATERIAL",
                "CURRENCY",
                "YEAR";
                
END;

After that I was thrilled with SQL commands. It’s been a while since I used SQL. Maybe the last time before I started as a BW consultant. So I looked into it. You find a lot of help on help.sap.com or on udemy.com. Since then I just think about my existing Calculation Views and how I can make them better with
Table Functions.

So here is my question for this post. Do you use Table Functions and how complex do you build them?

These posts might also be interesting:

SAP HANA Calculation View to build virtual keyfigure

author.

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

SAP Analysis for Office – The Comprehensive Guide

The book SAP Analysis for Office – The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.

45,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.7 and contains
299 Pages.

37,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.6 and contains
272 Pages.

27,00 €

8. April 2019

SAP HANA Calculation View to build virtual keyfigure

In my project I have now the opportunity to build SAP HANA Calculation Views. We use Calculation Views to combine two different Advanced DataStore Objects. First you have to set the parameter
External HANA View in the setting of your ADSO. When you activate this setting a SAP HANA Table is created which you can use in a Calculation View.

Create External SAP HANA View for an ADSO

The following functions are available for a SAP HANA Calculation View:

Join
Union
Projections
Aggregation
Rank

In our project we build a view which creates a Month-to-Date (MTD), Quarter-to-Date (QTD) and Year-to-date (YTD) view. For this we have a control ADSO which has the following structure.

The control ADSO has also an External SAP HANA View. After the preparation is done and the control data is loaded into the BW, we go back to Eclipse and open the perspective SAP HANA
Administration Console. First we add an Inner-JOIN to connect our first ADSO with the control ADSO.

Now we have created the connection between the two ADSOs. After the JOIN, we created an UNION to have the freedom to add more JOINs in the future. We match now all
fields from the source to the target.

After we matched all fields, we now can select them in the AGGREGATION layer.

Now we assign the key figure fields the right Semantic Type.

After we activate the Calculation View, we can now see the data in the preview.

But keep in mind, we only have Month-to-Date values in our BW. The Year-to-Date and Quarter-to-Date values are just virtual. I hope this can help someone to build something really cool. Here is
the overview of my HANA View.

These posts might also be interesting:

author.

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

SAP Analysis for Office – The Comprehensive Guide

The book SAP Analysis for Office – The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.

45,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.7 and contains
299 Pages.

37,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.6 and contains
272 Pages.

27,00 €

SAP BW Assign navigation attribute to InfoObject

In my current project we run into an issue that we have to match navigation attributes from one ADSO to an infoobject of another ADSO. The old feature of a MultiProvider was to Identify (assign).

SAP BW Assign infoobject to MultiProvider

So I searched for the correlating setting in BW Modeling Tools for Composite Provider and I found nothing in my SAP BW 7.4 SP17. After a little research I found that a SAP BW 7.5 has this
information. It is hide under „Show unassigned Navigation Attributes„.

Eclipse: Show Unassigned Navigation Attributes

Eclipse: Assign Navigation Attributes to InfoObject

If you show the unassigned navigation attributes, you can assign them to a normal InfoObject like the old MultiProvider. But a SAP BW 7.4 has no such setting.

Eclipse: No Show Unassigned Navigation Attributes

Eclipse: No navigation attributes to assign

I found this Wiki entry, which
descibes exactly what I need. At the end there is an information which is important if you use Advanced Data Store Objects. You have to read the s-note 2215947. The solution is to build one CompositeProvider (HCPR)
with your ADSO and its navigation attributes.

Eclipse: Activiate Navigation Attribute on Output tab

Then activate the setting „This CompositeProvider can be added to another CompositeProvider“and add the first CompositeProvider to a new CompositeProvider. Now you are able to assign the
navigation attributes to a characteristic in the output of the new CompositeProvider.

I hope this can help someone to solve a similar problem.

author.

Hi,

I am Tobias, I write this blog since 2014, you can find me on twitter, facebook and youtube. I work as a Senior Business
Warehouse Consultant. In 2016 I wrote the first edition of Analysis Office – The
Comprehensive Guide. If you want you can leave me a paypal coffee
donation. You can also contact me directly if you want.

In my newsletter you get informed about new topics
You learn how to use Analysis Office
You get tips and tricks about SAP BI topics
You get the first 3 chapters of my ebook Analysis Office – The Comprehensive Guide for free

/**/

* indicates required

Email Address *

First Name

Last Name

You want to know SAP Analysis Office in a perfect detail?
You want to know how to build an Excel Dashboard with your Query in Analysis Office?
You want to know how functions in SAP Analysis Office works?

Then you have to take a look into Analysis Office – The Comprehensive Guide. Either as a video course or as a ebook.

Get the Analysis Office ebook

Join the Analysis Office Video Course

If you want to create a Composite Provider in the BW Modeling Tools, you have to choose New >> Composite Provider in the context
menu of your InfoArea or File >> New >> Composite Provider.

Create new Compositeprovider — Create new CompositeProvider

After you choose the CompositeProvider, a new dialog appears which allows you to define the InfoArea, Name, Description and if you want to copy it from another CompositeProvider (HCPR). You can
add it straight to your favorites if you want.

After you defined a name and the description, you can click Next to get the dialog to select part providers.

Select PartProvider for CompositeProvider

You can choose between Join Providers and Union Providers. Either all Part Providers are treated like InfoCubes in MultiProviders or you choose Join Providers to join your Part Providers
like an Infoset.

Use the search to find your part provider. After you found it, click OK and Finish. You now see the General tab of your Composite Provider.

On the General tab you can configure settings of your CompositeProvider. You can decide if you want to create an External SAP HANA View, to use your CompositeProvider with a
Calculation View for example. You can also define if your CompositeProvider can be added to another CompositeProvider. This will be decribed in this blog post. You can define the common runtime properties and the runtime profile properties.

The Scenario tab is the main thing when you create your CompositeProvider. You have to assign your source fields to your target fields. Like the above an 1:1 Mapping or you can assign a constant
value via the context menu of your target.

Or you can use the context menu to edit a field and its properties.

Or you can just remove the assignment and a false field. It is your choice 😉 If you select your part provider in the source panel, you receive the following context menu.

If you choose Show Unassigned Navigation Attributes, you can see the navigation attributes of an InfoObject and assign it to a characteristic directly. This topic is different for BW
7.4, so I cover this in a separate post. The following screenshot shows how it looks like when you have BW 7.5.

You can also rename and delete or create the groups of your target so you can find your fields easily in your query.

The next part is the Output tab. On the output tab you can define several things for an InfoObject. Select an InfoObject and the following screen appears.

You can for example associate a field based ADSO with InfoObjects to receive master data and authorizations. Or if you have an InfoObject with navigation attributes, you can select them bei
clicking on an InfoObject and select the context menu.

This is how you create a CompositeProvider with Eclipse. This may change in future releases. If you have some questions, post them into the comment section below.

author.

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

Query Link Components

Link your Query Components in a SAP BW/4HANA system again, after you have decouple them by mistake

67,00 €

Transport Dependency Check

Check the dependency of your transport requests before you want to transport your development to your productive system.

57,00 €

SAP BW Use Pattern in Variable with Customer Exit

In my current project I created with a collegue a really cool function to analyze a string with 1333 characters. We are using a BW 7.4 SP 17 on HANA. First we have to build an Advanced DataStoreObject with a Field which has a length of 1333. For further understanding, we call the
field Field_1333. As data type I used SSTRING.

Create a field with a length of 1333 in Eclipse

The Advanced DataStoreObject will be included into a CompositeProvider. The Problem is the CompositeProvider cannot work with a field which has the length of 1333.

Advanced DSO cannot work with a field which is longer than 255

So my colleague decided to build an Open ODS View on top of the Advanced DSO. Therefor you have to activate the checkbox External SAP HANA View. My Open ODS View contains only
the field „Field_1333“, Number of Records and one InfoObject I need for my query. As you can see, the Open ODS View shows a length of 250 for the field „Field_1333“. This is only a
display problem.

When you look into the data, the Information is complete.

Now I use the Open ODS View in my CompositeProvider and here we go, the field is now available for my query.

Now we build a query on the CompositeProvider and add the necessary InfoObject into the rows and a restricted key figure into the columns with a variable for the field „Field_1333“. This variable
is used to fill the customer exit variable of the field „Field_1333“.

Now you have the opportunity to fill a lot of values separated by comma. The customer exit sorts all values by ascending and fill the variable with ‚*‘ & value & ‚*‘

ls_e_range-low = ls_e_range-low && ‚*‘ && ls_code-ccode && ‚*‘.

Now we can combine different combinations and get a result. This result is used as a PreQuery in my main development. So I am able to build a complex analytics application and give the user the
opportunity to analyze his data deeply.

author.

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

SAP Analysis for Office – The Comprehensive Guide

The book SAP Analysis for Office – The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.

45,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.7 and contains
299 Pages.

37,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.6 and contains
272 Pages.

27,00 €

Analysis Office 2.5 & AMDP

At the moment it is very quiet about new blog post. This is maybe the summer break everybody talks about. 😉

I am at the final phase of my diploma thesis and have no time to do cool stuff and also SAP doesn’t release Analysis
Office 2.5. It is still in ramp up and nothing new at the moment.

So I could only say, my current project is a BW on HANA and I am testing how HANA SQL Script (AMDP) works and how fast we could build some stuff. First tests show a transformation with routines
for 800.000 records about 1:55 minutes. The same transformation with AMDP (ABAP Managed Database Procedures) only need between 15 – 23 seconds. This is really impressive.

So stay tuned, if SAP will release Analysis Office 2.5 and some examples with AMDP.

These posts might also be interesting:

author.

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

SAP Analysis for Office – The Comprehensive Guide

The book SAP Analysis for Office – The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.

45,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.7 and contains
299 Pages.

37,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.6 and contains
272 Pages.

27,00 €

BW Modeling Tools create advanced DataStore-Object

If you use a BW/4HANA instance, you have to use the BW Modeling Tools (BWMT) to build your data model. You could only build Advanced DataStore-Objects (ADSO). If you create a new ADSO, you have
to select a BW-Project and an InfoArea. After that you have to define a technical name and a description. You could also use a template to build your ADSO.

On the general view of the ADSO you can choose a model template and define the technical settings of it.

Under Details you can add your InfoObjects, own fields, define keys and group your objects to logical groups. Really cool is the feature of own fields. You do not have to create a lot of
InfoObjects anymore.

After you have add your InfoObjects you can define under settings the partitions and indizes of your object.

As you can see it is very easy to build an Advanced DataStore-Object with the BW Modeling Tools. After you have done all your settings, you have to activate it.

These posts might also be interesting:

author.

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

SAP Analysis for Office – The Comprehensive Guide

The book SAP Analysis for Office – The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.

45,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.7 and contains
299 Pages.

37,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.6 and contains
272 Pages.

27,00 €

BW Modeling Tools add a new project

After I started my BW/4HANA instance on cal.sap.com and everything was working, I logged on and typed RSA1. But there was no modeling tab anymore. Then I rememberd, I need the BW Modeling Tools. So I installed the latest Version of Eclipse and the BW Modeling Tools. After that
I need a Project. The project is the binder for all activities in our BW system. Make a right-click in the Eclipse Project Explorer and choose BW Project.

A new dialog appears where you have to choose a system connection. You can choose between a new system connection from scratch or an existing one.

In my case I choose my BW4HANA instance and click on Next.

After you choose your system, you can edit the connection parameters if you want. Click on Next to log on to the system.

After your logon was successful, you have to enter a project name for the new Project.

Click Finish to close this dialog. Your new project has been created. The next blog posts will be give you an overview about InfoObjects, Advanced DSOs, Queries and so on.

Note: I don’t know why some Information on this screenshots are in German. Has any one an idea?

These posts might also be interesting:

author.

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

SAP Analysis for Office – The Comprehensive Guide

The book SAP Analysis for Office – The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.

45,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.7 and contains
299 Pages.

37,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.6 and contains
272 Pages.

27,00 €

SAP BW Modeling Tools in Eclipse

This week the new open SAP course BW/4HANA in a Nutshell started. It is a free course.
One big thing is that BW/4HANA no longer supports the good old BEx Suite. You now have to use the BW Modeling Tools based on Eclipse. In this and further posts I will go deeper into the BW Modeling Tools and what is possible and what is not possible at the
moment.

If you have any questions feel free to ask or correct me 😉 So let’s get started.

First we need Eclipse, which we can download here. But be aware when you download the Mars version,
you need the Mars version of the BW Modeling Tools. And if you download the Neon version, you need the Neon version of the BW Modeling Tools.
After you installed Eclipse you can download the BW Modeling Tools directly in Eclipse.

In Eclipse, choose in the menu bar Help >> Install new software and enter for Neon version (https://tools.hana.ondemand.com/neon) and for
Mars (https://tools.hana.ondemand.com/mars). Press Enter to display the available features.

Select the Modeling Tools for SAP BW powered by SAP HANA and choose Next. On the next page you get an overview of features to be installed. Choose
Next. Confirm the license agreements and choose Finish to start the installation.

After the installation is done, you need to set up da new BW project to access the
BW-Repository. More about the Modeling Tools will be covered in future articles. So stay tuned.

These posts might also be interesting:

author.

I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

SAP Analysis for Office – The Comprehensive Guide

The book SAP Analysis for Office – The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.

45,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.7 and contains
299 Pages.

37,00 €

SAP Analysis for Office – The Comprehensive Guide

SAP Analysis for Office – The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.6 and contains
272 Pages.

27,00 €

author.

SAP BW Assign navigation attribute to InfoObject

author.

Subscribe

BW Modeling Tools: Create a CompositeProvider

author.

SAP BW Use Pattern in Variable with Customer Exit

author.

Analysis Office 2.5 & AMDP

author.

BW Modeling Tools create advanced DataStore-Object

author.

BW Modeling Tools add a new project

author.

SAP BW Modeling Tools in Eclipse

author.

Schlagwort: BWMT

author.

author.

author.

Subscribe

author.

author.

author.

author.

author.

author.