Restrict query to different data sources

Hi Support Team. I am trying to restrict the query to fire different data sources for increase the performance. given below is the bussiness requirement details There is 3 different sources(ex. SOURCE1, SOURCE2 and SOURCE3), we created 3 different view(View1, View2 and View3) on top of all 3 data sources and added new field called "Source_Name" in all three view respectively that holds the view source name(SOURCE1, SOURCE2 and SOURCE3). Then finally we created the business view(Final_View) by UNION all three views(View1 union View2 union View3). Requirement is If user is querying the data by "select * from Final_View where Source_Name = 'SOURCE1';" then Denodo query should not try to hit the other two data sources(SOURCE2 and SOURCE3). it sould hit only the SOURCE1 data source. Same as if user is querying the data by "select * from Final_View where Source_Name in ( 'SOURCE1','SOURCE2');" then Denodo query should not try to hit the SOURCE3 data source. it sould hit only the SOURCE1 and SOURCE2 data source. Your help would be greatly appreciated. Regards, Rohit
user
10-05-2017 03:15:40 -0400

1 Answer

You have to basically partition it - it adds extra views, but works well. I have this requirement a lot. Basically you'll add 3 extra "intermediate" view that has a where clause on the source. Something like: select * from <otherview> where source_name = 'SOURCE1' You then union these 3 intermediate views together - that way when you query the final view like: select * from finalview where source_name = 'SOURCE1' It knows to only execute a single branch of that tree. Post back if that doesn't make sense.
user
10-05-2017 11:00:42 -0400
You must sign in to add an answer. If you do not have an account, you can register here