Does anyone know the best way to import data with tens of millions of rows from SSAS cubes? Using one of the cubes in a SSAS server, I am looking to create a view with 8 columns and likely between 30-40 million rows. I can't make it smaller as I want a high degree of granularity in this report.
In addition to importing this data, I also need to perform transformations on it. The data has a Year and Month Column - I need to use Power Query to create a date object based on those two columns. The view will also have an "Attribute" column, which describes what type of value I'm looking at. I need to Pivot that column, sum up all the types to create a Total Column, and then Unpivot to get them back in the "Attribute" column.
Is there a way I can do this in Power BI? I tried using the technique of Parameters and Reducing Rows (https://www.youtube.com/watch?v=_zYvybVMk7k), but it does not work for me, as the "Reduce Rows" option is greyed out. If I try in the advanced editor to reduce the number of rows to 1,000, I get this error "This query does not have any columns with the supported data types. It will be disabled from being loaded to the model."
I assume part of the problem is that I can't load the entire SSAS view in the first place. In the linked Guy in a Cube video, he already has his full dataset fully uploaded, and just uses parameters to make it smaller. I can't import all the data in the first place using my machine. What can I do? Thanks!
