Forum Discussion
Agg Table Efficiency Min Date
In my specific case...the view is the fastest method to refreshing data and getting the groupings necessary. Unfortunately the underlying data has several columns that need grouping and Power Query wasn't providing the needed efficiency. I've improved the sql code for the view and can run sql directly in the model for 2 fact tables ~20mil rows with about 25 columns in 52mins. Hoping this will improve even more once the view is in place and I can push changes to Oracle. Thanks lbendlin for trying to assist!!
Oracle doesn't allow query folding at this time...so I can't get the benefit of query folding using Power Query to make adjustments after sql. When I aggregate data using the sql statement...it takes almost an hour to run around 8m rows. My sql is doing a count distinct. That's literally the only aggregation in this table. My 'group by' is 7 Keys from the relational db..all intergers. I have 3 sub queries where I have to group things a certain way because the source table is at a more granular level. Sub queries are pretty basic but I'm open to ideas if that's causing my issue.
lbendlin quick sql Example...
select count( distinct q.id),
Q.status
from(
select a.id,
case when a.id in (
select b.id
from b
where b.id = a.id and
b.status = active) then active else other end as status
from a) q
group by q.status