I'm working with a Redshift data source that has 15 million rows. These are already pre-aggregated somewhat. It's just a lot of data. I've restricted the query to 8 dimensions and 3 metrics, and I'd like to build a couple of metrics on top of those.
I need to use Power BI to empower users to click around and discover data and trends.
Now, I like DirectQuery for this, except for the problems here:
1. No automatic date hierarchies, so drill-down in charts (Year -> Quarter -> Month -> Date) is missing. The charts are automatically at the lowest (date) level.
2. I couldn't use Power Query to split up a column, so I used SQL, which luckily worked.
3. Various DAX functions are just not available. For example I'd like to compare YTD, MTD, WTD, current week to last week and so on, which is not an option with DirectQuery.
On the other hand, switching to Import takes an interminable amount of time for the entire 15 million rows to be loaded. It is also not an operation Redshift is optimized for.
Ideally I'd like to have as much of the processing offloaded to Redshift. Is Power BI even the right tool for this job? I'm starting to have doubts.
Any help would be appreciated.
