Hi,
I have some questions that I would like clarified please. I'll based these questions on one of our scenarios of loading data from multiple Hadoop tables into Power resulting in producing a single, very large (250 million rows) flat table.
Q1 - Sometimes a load this large is successful and sometimes it fails. What is the successful load dependant on, is it depedant on the RAM available to machine running Power BI, or is dependant on the total data imported being under the 10Gb limit?
Q2 - When creating the connection to Hadoop, I can use the 'Advanced options >> SQL statement' to write a query to return this very large flat table. Is this written SQL statement Query Folded (i.e. processed on the Hadoop server)? If yes, then why is the 'View Native Query', on the Source PQ step, context menu option greyed out? The 'View Native Query' being greyed indicates to me that the SQL statement wasn't Query Folded, but I thought Query Folding was the whole purpose of the SQL statement and therefpre the 'View Native Query' should be clickable (not greyed out).
Q3 - If I extract a single Hadoop table into Power Query without using a SQL statement then I can see the 'View Native Query'. If I then place a Parameter on the table to extract records with that meet the parameter criteria then is the parameter Query Folded? So does it:
a) filter the records, based on the Parameter, at the Hadoop source and only bring into Power Query records that met the parameter criteria?
OR
b) it isn't Query Folded and so all records are brought into Power Query and then filtered, based on the parameter, inside of Power Query?
Q4 - In the Power BI Service, I have the option to change the Parameter value. When I do and then click refresh, the report updates with the new parameter data. Is this new parameter update/refresh being Query Folded too?
These questions are asked to be able to restrict the number of rows loaded into Power Query. Waiting overnight for the 250 million rows to load into Power Query isn't ideal and especially when you amend anything in Power Query then it seems the whole 250 million records are loaded again. Quite often resulting in my computer crashing.
Bearing in mind the requirements state full granularity so no aggregation can be performed on the data, how would you guys/gals handle this?
Some thoughts.
1. I could limit the recordset to TOP 1000 and build and finish the report, then remove the TOP 1000 and load the full dataset. The full refresh will happen at a scheduled time (overnight) and then the data loading issue wouldn't be a problem. Any filtering done by the user in the Power BI Service will still result in slow updating. Again, not ideal.
2. I know Power BI doesn't allow Direct Query to Hadoop, so would an intermediately layer in the form of a SASS Tabular cube and using Direct Query against that SSAS Tabular cube be Best Practice (Hadoop > SSAS Tabular > Power BI) or will the data loading/rendering issues still remain?
I have researched this online but some of the answers found are contradictory. To be certain I ask for the above clarification.
Thanks in advance.
