My SAP HANA Studio experiences

[Former Member]

Being on an old version of SAP HANA Studio (rev 48) as compared to the server (rev 51), I was not able to access a calculated column from an analytical view in the calculation view SQL script. You also should check the definition of the Analytical view in _SYS_BIC, if it actually has the column (even if it shows it in data preview it might be missing in the definition).

Update to match the server version or higher (I updated to revision 52), reactivate the analytical view and it should show the calculated column in it’s defintion:

Notice I am choosing “dsproten/SHOP_FACTS” and not “dsproten/SHOP_FACTS/olap” (which shows the same Orange Cube icon). If you can find it ending in “/olap”, you are probably on an older SAP HANA Studio version.

(Update) If you have calculated column in an Analytical View, the Analytical View will actually generate an additional Calculation View in the background with the same name, but without /olap and contain your calculated column.

Example of an Analytical View without calculated column:

Now for the Analytical View (without /olap ) with calculated column "QPLUAM":

If the column is not listed, the SQLscript in a calculation view will also not see the Calculated column and throw an error.

After adding a new calculated measure in the analytical view, select “Semantics”. In this example QPLUAM. Aggregation type is set to “NONE” by default.

When activating it does go through, but with a warning on validation:

Change the aggregation type to FORMULA and the warning will disappear.

 

And you can proceed to retrieve the value in a Calculation view:

   var_out = CE_OLAP_VIEW("_SYS_BIC"."dsproten/SHOP_FACTS",["STATE","CATEGORY",SUM("MARGIN"),SUM("QPLUAM")]);

Tip: don’t forget the output is definied at 2 levels, add your Output parameter fields created in the script definition also to the output of the calculation view.

Data Preview et voilà!

Data distribution in a table

Update: 29/11/2013 (HANA rev 63)

A nice little trick ... : How do you know what fields will hold data for an SAP HANA model? This is going the quick and dirty approach, without requesting from the business, "what fields would you require?" in a 3 day workshop.

One way to check is via data distribution for all the columns and not just a single column. A single column, you can check in the "Data preview" - "Distinct Values" tab of SAP HANA Studio this will give you the distribution of records for that column ( I have seen errors appearing with large tables when doing it this way ).

To have an overview of all the fields of a table, simply execute "Open Definition" in the Catalog section and select "Runtime Information" tab. In the Runtime Tab you can select Columns and if you wait a little (it says "Fetching children in the bottom right corner) it will fill a list with all the columns and their amount of distinct records.

This can be helpful if you have to decide which fields to offer in the SAP HANA model. So if there are just NULL values or a single value, there wouldn't much of a point including these fields (there are always exceptions like flags).

Another point is that you can use record distribution for performance analysis. If a table is highly granular and not compressed at all, it will likelt not perform well.

Tip: Overall, the less fields are used in the model, the better for execution time of the model.

And you can also sort on this column and get the fields to include/exclude.