Get Data from Cube to use with SQL queries without loading it first?

Question

My company uses a SQL Server Analysis Services and I only have basic user access.

The data I need is only available in the cube as it's compiled from different sources. The full thing is 30M lines and so preloading in Power Query or Powerpivot isn't feasible.

I just want it to interact with some SQL queries and pull the necessary data, but I don't know how to get it without preloading it all first.

Can anyone please point me in the right direction as to what I should be looking into in order to accomplish what I need?

Please help in layman's terms as I'm a novice and still learning.

Answer 1

You can get data from cube, but not with SQL. DAX or MDX (depending on cube model type) will help you. Unless you can use SQL only to exec OpenQuery() with DAX or MDX inside, but you need configured linked server before.

If you need VBA: Example below is for tabular model (DAX)

To test example code add cube connected pivot table to your Excel sheet and select any cell of this table

Set CurrentPivotTable = ActiveCell.PivotTable

Set ADOCon = CurrentPivotTable.PivotCache.ADOConnection

DAXQuery = "EVALUATE ROW(""ExampleColumn"",""Hello from query!"")"

Set RecSet = ADOCon.Execute(DAXQuery)

While Not RecSet.EOF
    Debug.Print RecSet![[ExampleColumn]]
        
    RecSet.MoveNext
Wend

Also you can use DAX and MDX in PowerQuery to select filtered data from cube.