Can we generate Dynamic fields in HANA

[Former Member]

Hi Frzz,

I have a requirement, in which i need to generate the fieds based on the value set dynamically. Is it possible in HANA with scripted Calculation view??

Please guide.

Best Regards,

Krishna.

[aadityanigam]

Hello Krishna G,

Can you share the use-case?

Could you not use generic fields...

or different table and then output data using union

warm regards

aadi

[Former Member]

Hi Aadi,

I have a table in which i have flat structure of the hierarchy. ( ActualFiled, SuperiorField )

I need to build a view in which for each level i need to create a field dynamically, as the levels are not constant.

Best Regards,

Krishna.

[aadityanigam]

Hello Krishna,

If i understood your problem

Your data looks like this

ActualField	SuperiorField
1
2	1
3	1
4	2
5	2
6	2
7	3
8	3
9	3

your output needs to look like this:

Level 1	Level 2	Level 3
1	2	4
-	3	5
-	-	6
-	-	7
-	-	8
-	-	9

if so, dynamic sql might seem a good option.

My experience suggest there may be a better way...

For Starters ... check out

Window Functions - SQL Reference - SAP Library

Let me know if you see a way forward...or any problems as i am sure there is more to your use case that you cannot reveal on an open forum...So tell me the problem you face if you try it using Window functions.

Warm regards

aadi

[Former Member]

Hii Aadi,

Exactly. I need to build the flat structure as above.

Can you please give some hint how can i achieve this??

With out SQL, can i perform selef join thrice to get above output???

Thank you,

Best Regards,

Krishna.

[former_member182302]

Hi Krishna,

Have a look on this thread:

How to model scenario in HANA | SCN

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna G,

More details are required. Some initial feedback:

1) Are you working with BW on HANA or native HANA?

2) What reporting tools do you have to work with?

3) Self-join is possible in SQL, but is not possible in an Attribute View. It may be possible in a Calculation View, I haven't tried - but this could cause bad performance.

Ultimately it sounds like you have a parent-child structure that you want to display in a leveled structure. I'd recommend building a parent-child hierarchy in your Attribute or Calculation View, building a report in a front-end tool that supports MDX, and give the user the option to drill into the hierarchy.

Another potential solution is to persist the flattened hierarchy via your ETL tool of choice.

You have quite a few options. Please provide more details so that we can help guide the best solution based on the constraints you're facing.

[Former Member]

Hi Jody,

Am working on stand alone HANA. Not BW on HANA.

We are using Webi reports on HANA Views.

We can do self join in Attribute Views by joing the table with same table as per Krishna's post above.

I have a table in which i have field with superior node information. based on this table i need to create a flat hierarchy. As suggested by Krishna, we can do self join and get the expected output. Correct me if am wrong.

Krishna.

[Former Member]

We can do self join in Attribute Views by joing the table with same table as per Krishna's post above.

Have you tried this yet Krishna?

[former_member184768]

Hi Krishna,

One year back I wrote some code for the requirement mentioned by you. Please check if you can modify it and use.

Regards,

Ravi

[aadityanigam]

Hello Krishna,

Check out this blog i wrote outlining your solution

Warm regards

aadi

[former_member182302]

Hi Krishna,

DYNAMIC SQL is not possible with script based calculation view.

We have to use EXEC or EXECUTE IMMEDIATE in a procedure.

Regards,

Krishna Tangudu

[Former Member]

Hi Krishna,

Thanks for your update.

Am not so familiar with SP. You mean we can create procedure (view based ) to generate the fields dynamically?? Correct me if am wrong.

Best Regards,

Krishna.

[former_member182302]

you can consume only a READ Only procedure in a script based view.

But for having dynamic sql.. you need READ/WRITE procedure.

As you have to fix the output parameters in your script based view, hence it is static.

Regards,

Krishna Tangudu

[Former Member]

Krishna,

So for my requirement , instead of creating scrip based view can i create the procedure with

READS SQL DATA WITH RESULT VIEW  ProcView ???

So that i can consume this procedure by calling it as

SELECT * FROM ProcView

Correct me if am wrong.

Thanks for your help.

Best Regards,

Krishna.

[former_member182302]

When you write "READS SQL DATA" you cannot have dynamic SQL.

So if you are able to get the logic without using dynamic sql then you can use as you mentioned.

The issue here is, in your script based calculation view, you are defining your output parameter names and lenghts right? Hence you are making the output static.

Regards,

Krishna Tangudu