Are there any limitations on concatenating string columns in Analytic Views?

[Former Member]

Hello Experts,

I have made the following observation and I wonder if I'm missing something or if there is a limitation in the way string columns can be combined as Calculated Columns within Analytic Views.

I created an analytic view solely with the aid of the visual editor. All columns used in the view are defined as string data types of which the row count is used on one of them to define it as a measure column. This works fine as I get the expected results from the view. Now if I create a calculated column by combining two columns (neither is the one I use as a measure) using the + operator, the validation fails with the error that the data type of measure column has to be numeric. This was acceptable previously but now that I have introduced a concatenated column (of two string columns), I get a failure on the validation step. Here is a snippet of the error:

Error Message

Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Create Scenario: failed aCalcEngine.createScenario(): The following errors occured: Inconsistent calculation model (34011)nDetails (Errors):n- CalculationNode (dataSource) -> attributes -> attribute (COUNTER): Keyfigure has to be numeric.n- CalculationNode (finalAggregation) -> attributes -> attribute (COUNTER): Keyfigure has to be numeric

So I removed the column I used as a measure and introduced a measure column from the base table which is defined as a numeric column and bingo the view works with concatenated columns as expected. I then re-introduced the previous column as a second measure and the validation step fails again with the same error.

From this it appears that you must have only numeric measure columns from the base table for you to create any concatenated string column (as a Calculated Column). Since this is fairly straight forward to implement in a raw sql, I think this is perhaps an issue with the studio unless I have missed a step somewhere.

(Searching thro the various posts, I am led to believe that I can indeed use the + operator for concat operation on string columns).

I'm on CloudShare with:

SAP HANA Studio

Version: 1.0.48

Build id: 201301130825 (372847)

HDB version info:

version: 1.00.48.372797

Any thoughts?

Regards,

Ramesh

[former_member184768]

Hi Ramesh,

I don't think there should be an issue. I tried the same (on revision 52), that is, joining two varchar columns in Attrib View using + operator and it works fine for me. The calculated column is defined as ATTRIBUTE and not MEASURE.

Can you please post a screenshot of your Calculated attribute to understand the issue better.

Regards,

Ravi

[Former Member]

Hi Ravi,

Thank you for your response.

Please note that I am working with an Analytic View and not an Attribute View - the behaviour may be different in each case. Also I have the calculated column defined as an ATTRIBUTE and not as a MEASURE since the resulting calculated column is a string.

Here are some screen shots to give a backdrop to my issue (please ignore the semantics of what I'm trying to do, this is just an exercise to convey the error I'm getting!).

This is the base table and the columns I'm using:

Here is the data foundation and the definition of the calculated column I create.

On validation I get the error I mention in my prevoius post.

The system is performing a row count on the COUNTER column. Now if instead of COUNTER, I were to use the column FORCURAM, which is defined as a numeric column in the base table, then the validation succeeds!

Let me know what you discover.

Thanks,

Ramesh

[former_member184768]

Hi Ramesh,

I meant Analytic view only, just typed it wrong.

Couple of points.

Can you please add any Measure (dummy or existing) and add it to the measure for the Analytic view along with your calculated column. It is required to have atleast one measure defined.

Alternatively you can change the Multi-dimension property for the Analytic view to False and use it as Master data reporting object.

Regards,

Ravi

[Former Member]

Hello Ravi,

I had experimented with both of the above before posting this question and I mention one of them in my original post:

"So I removed the column I used as a measure and introduced a measure column from the base table which is defined as a numeric column and bingo the view works with concatenated columns as expected. I then re-introduced the previous column as a second measure and the validation step fails again with the same error."

I therefore concluded:

"From this it appears that you must have only numeric measure columns from the base table for you to create any concatenated string column (as a Calculated Column). Since this is fairly straight forward to implement in a raw sql, I think this is perhaps an issue with the studio unless I have missed a step somewhere."

Now I am getting the same validation errors in other places where I am using row counts. For example, I couldn't graphically design a calculation view based on two existing views both of which have row count (as measure) and work fine on their own. When I combine these views as a union, validation complains about the columns where row counts have been used (the measures) and yet it compiles these views individually without any errors and produces the desired results! My workaround then was to hand crank the calculation view but I think the graphic designer should cope with such a scenario. It looks like the treatment of row count is inconsistent across different views.

Kind regards,

Ramesh