Best Way to Import Big Data from SSAS Cube?

[Anonymous]

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!

[Kbandewar]

The youtube link that you shared is working for me, even if your full data doesn't loads just go to "edit query" and you can see your imported table. and you can perform this parameter trick

[lbendlin]

The best way to import data from a cube is not to do it.  Cubes should be consumed via live connection.

If you must, write your own MDX queries to optimize the amount of data you are pulling. If you have a suitable datetime column you can consider using incremental refresh.  

Be aware that what you are doing is pulling data out of one multidimensional cube and stuffing it back into another cube lite (SSAS Tabular).

[CracktheCode85]

Hey Ibendlin ,
Let's say you want to only bring in This year and Last years data from SSAS Cube with a MDX query to filter those dates.
Is there a "SELECT *" function with MDX that can accomplish this?
Also I'm finding that using MDX to pull in tables from Cubes into Power BI is 'breaking' the hierarchy connection for things like 'Sales per product level'. 
Do you have any recommendations on how to keep the hierarchy intact when using an MDX query to import?

[lbendlin]

The best way to import data from a cube is not to do it.  Cubes should be consumed via live connection.

I am not an expert in MDX but I am certain they cover hierarchies. Hierarchy (MDX) - SQL Server | Microsoft Learn

[CracktheCode85]

Thanks Ilbendin,

Live connection is definitely the easiest and fastest way to build visuals. The downside is you cannot create new measures, built custom titles for user selections, nor adjust the decimal places for calculated functions with the live connection. 

Side question: Is there a faster way to refresh Cube data in Import Mode???

I'm trying to learn MDX (and gotten pretty far) but I'm losing the hierarchy of product levels for values when importing them this way. 

Maybe there is a DAX query that would be better suited for this? (I'm not sure at this juncture but hopefully someone out here has experienced this and can assist 🙂 ) 

[lbendlin]

Accessing cubes via DAX means the system has to translate the DAX into MDX.  Same as when accessing SSAS Tabular via MDX (like you do in "Analyze in Excel") - it's an additional translation step that may not be 100% accurate and is definitely much slower.  Try to avoid that.

Cube = MDX

SSAS Tabular = DAX

[CracktheCode85]

That makes sense. It's just a shame that Cubes works perfectly fine in Excel as a Pivot table, but not in Power BI 😞

[lbendlin]

Keep using Excel ?

What's the business problem you are trying to solve?

[CracktheCode85]

Recreate the report in Power BI which has a gateway established for automatic refreshing. Essentially automate the report in Power BI. I don't suppose I could automate a Pivot from Excel to refresh a BI Dashboard could I? (with cube data?)

[lbendlin]

Recreate the report in Power BI 

That's called "Fighting the API". Power BI is not Excel. Trying to make Power BI behave like Excel is an exercise in futility.   Use whatever tool is right for the job, but use it as it was meant to be used.

[CracktheCode85]

I completely agree with you 🙂 

I'd just like to be able to have some nice visuals that auto refresh from the source and tell a much better visual story 🙂 
Unfortunately (as I'm learning with Cube data sources) this is not possible 😕 
But I will keep our conversation in my back pocket for when I get asked why it's not possible 🙂