cancel
Showing results for 
Search instead for 
Did you mean: 

Limitations in IDT when using BW as data source

former_member183330
Active Participant
0 Kudos

Hi Experts,

Could anyone please let me know what are the limitations in IDT when using BW as data source.

Because my query is taking much time when I build logic on date field, when I fetch the data by giving particular range on date like "[date] Between '01-06-2015' to '30-06-2015' ", I am getting the data with in seconds.

But when I use predefined condition on date like Month([Date])=Month(CurDate()) And Year([Date])=Year(Curdate()) it is taking huge time to get the data.

Could any one help on this.

I can provide more details if any.

Thanks,

G Sampath Kumar

Accepted Solutions (0)

Answers (1)

Answers (1)

amitrathi239
Active Contributor
0 Kudos

Hi,

Even i have also noticed this.whenever i have used the curdate function object in the webi prompt,reports takes time to refresh.

Instead of creating direct Month/Year object in Business layer based on the Current date function,create calculated columns in the  data foundation layer.

Right click on table->Insert calculated columns.

Amit

former_member183330
Active Participant
0 Kudos

Hi Amit,

Tried your suggestion, but same thing happening.

Any other way

Thanks,

G Sampath Kumar

amitrathi239
Active Contributor
0 Kudos

Hi,

do you have calendar table? or is it possible you can get the current date from database instead of using curdate function in IDT.

in my case i have two sources,BW & Oracle.I have create one view in the oracle with all required prompts objects.Then dragged the table in the universe and created objects.no joins were set with this table.beacuse these objects used in the prompt only to set the condition.

Amit

former_member183330
Active Participant
0 Kudos

I don't have Calender table, I have @Prompt(Key Date) shall I use this?

I don't have other data bases, shall I create derived table to achieve as you said(will this work)?

Thanks,

G Sampath Kumar

amitrathi239
Active Contributor
0 Kudos

Hi,

I haven't try this but you can try with derived table.

Create one derived table and SQL look like.

SELECT Month(curDate()),

Year(curDate())

FROM

click T or D table

In the from clause select any T or D table.Then create two objects in the universe and try.Do not join this table with other table.

Amit

former_member183330
Active Participant
0 Kudos

I Noticed one thing

When I am comparing Month([Date]) with Month(Curdate())

Query fetching all the data from BW for [Date] and after fetching all data it is only showing where

Month([Date]) with Month(Curdate())

same happening for year also.

I observed this in data federation administration tool.

to confirm the same I have taken some other field which is having 2 digits number and compared with Month(curdate()). At this time condition applied when fetching data from BW as EQ 6.

I assume this is a loop hole in BO which needs to be corrected by SAP.

Thanks,

G Sampath Kumar

amitrathi239
Active Contributor
0 Kudos

Hi,

share the screenshot of DF admin tool.Want to see the comparsion.

Instead of putting the Month equal to month(curdate) put the static month and year and see in DF tool.

or do one thing copy the sql of report and run in the DF admin tool with static date and year.other for month(curdate) and year.

Amit

former_member183330
Active Participant
0 Kudos

Hi Amit,

I Tried static inputs also, same thing is happening.

Please find below screen shots.

SQL generated by query

MDX query which triggered on BW


I



Isn't there any solution for this. this is causing drastic load on server even to get one month data also it is reading total data. if I use incremental days function it is working, by using this function I am getting the data but it could be resolved.

Thanks,

G Sampath Kumar

amitrathi239
Active Contributor
0 Kudos

Hi, 

I don't have much idea on this. But the time difference is in mille seconds from extracting to display. Expand any query and you will see the issue.

Better to raise a SAP support ticked on this.

Amit