Passing a value to a database view from webi/idt prompt

[Former Member]

Hi,

Environment:
BO 4.0 SP5
Teradata 13.10


I am working on performance tuning of 12 webi reports which are built on IDT universe. The universe is developed using teradata database views. The views have logic in them which includes joins with multiple base tables and other calculations. The bottleneck I have found is that the views are processing more data than actually required by the user running the report.

There is a period table which contains 1 column with time periods starting from January to current month for one year. This table is used in almost all the views without any restriction on the period. So, if a user executes a report for August period, the view will process the data from January to August and return it to webi engine where it will be filtered for the period entered by the user. All such views are then again joined at the universe level.

The code of the view looks something like below. Please note this is a simplified version of the code for understanding purposes.

SELECT
Tab1.c1,
Tab1.c2,
Tab2.c1

FROM PeriodTable PT
JOIN Tab1 ON (PT.Period=Tab1.Period)

LEFT JOIN Tab2
ON Tab2.ID = Tab1.ID
AND PT.Period=Tab2.Period

LEFT JOIN Tab3
ON Tab3.ID = Tab1.ID
AND PT.Period=Tab3.Period

LEFT JOIN Tab4
ON Tab4.ID = Tab1.ID
AND PT.Period=Tab4.Period

LEFT JOIN Tab5
ON Tab5.ID = Tab1.ID
AND PT.Period=Tab5.Period


Now, the quickest solution would be to restrict the view by adding a where clause in a view such as "where PT.Period = User Input Period from webi". However, I have not seen any implementation where the webi input or prompt is used to restrict the view. Please let me know if there is any way of doing so.


Thanks.
Asad

[Former Member]

If you ALWAYS need to restrict the view then I would recommend looking into a derived table, where you can add @prompt functionality as part of selecting your columns from your view.

[Former Member]

Hi Asad,

you can create a universe or class level filter/conditional  depending on your need which needs to be prompted for LOV/s selection during runtime.

PT.Period= @ Prompt(.............)

Hope this helps.

Thanks,
Raja

[Former Member]

Hi Raja,

Thanks for the response. We already have this prompt but the problem is that we do not have any restriction at the DB view level. I am looking for a way to pass a value from @prompt to DB view definition. Hope my explanation is clear.

Asad

[Former Member]

Hi Asad,

There is no way the the prompt value that user selected appear as a filter on where clause of the view query. However the prompt values will appear in the where clause of your generated query. Per my understanding (you can test it in your database) there is no significant difference if the prompt value appears in where clause of your View query and prompt value in the where clause of generated query.

There might be some performance overhead if your database tables not indexed for the column you are trying to filter on in that case it goes for Full table scan adding to table Run time of query..

[Former Member]

Hi Asad,

Can u try Placeholder.

select * from <DB_VIEW> ('PLACEHOLDER' = (<DB_INPUT_VARIABLE>,@prompt(<var>)));

Regards,

Rashmi

[Former Member]

Hi Asad,

Got it , I agree with Mishra, I am not sure on how DB level definations can be handled through WEBI  or any reporting tool for that matter.

Good luck!!

Thanks,

Raja

[Former Member]

Hi Rashmi,

Can you eloborate little more, please? Note that I am trying to pass a value to a DB view definition from webi prompt.

Thanks for your response.

Asad

[Former Member]

Hi Asad,

I have tried passing the user input to HANA DB view. I am not sure whether it goes the same with Teradata.

Step 1: In data foundation select the required view from connection.

Step 2: Right click and insert a dervied table.

Step 3: Edit the Dervied table query by appending

          ('PLACEHOLDER' = (<DB_INPUT_VARIABLE>,@prompt(<var>)));

Regards,

Rashmi

[Former Member]

Hi Rashmi,

Just curious, aren't you creating a new derived table in universe with restricted data ? you are not restricinng that data in DB directly , is that correct ?

You are  creating local view with needed data , correct ?

Thanks,
Raja

[Former Member]

Hi Raja,

I agree with you on derived table.

But consider, I have an input parameter based on which certain calculations are made in DB view. If the user input doesnt reach DB, then the required calculations cannot be made right?

I am not able to understand how calculations are made in DB view if we cannot pass input to DB view through webi?

However i tried the following:

1. making  input parameter mandatory in the DB view

2. removng the default value.

Based on the above settings, i was not able to create a derived table.

But with a default value set in the DB view, the result i get in webi is based on the user input from webi prompt.

Note: calculations of certain columns are done in DB.

Thanks and Regards,

Rashmi

[Former Member]

Hi,

A similar thread was posted earlier. This is specific to HANA DB.

http://scn.sap.com/message/13797032

Hope it helps..

Thanks and Regards,

Rashmi

[Former Member]

Thank you !! All that is correct but goal is to restrict that data that's been considered for data retrival , correct ? your where condition will scan the table completely right before retriving data to view/derived table.