Forum Discussion
Replicate an Excel connection to an SSAS cube in Power BI Desktop
I have an Excel report that uses an SSAS cube as its data source, and it is painfully slow. I'd like to move it to PBI desktop to get better performance, but I'm not sure exactly how to replicate the connection query to ensure that I'm getting all of the necessary data.
Here's the Excel connection string:
Provider=MSOLAP.7;Integrated Security=SSPI;Persist Security Info=True;User ID="";Initial Catalog=<SSASDatabaseName>;Data Source=<SSASServerName>;Location=<SSASServerName>;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2
The connection type is Cube, and the Command Text is <CubeName>.
I'm able to connect to the server and the cube without any problem, but I'm not sure what to select to get the same data that's being pulled into Excel. When I get to the selection window, however, I'm unclear on what I should be selecting - the dimensions? The measures? Both?
If I use Import, I get this:
Again, I'm not sure which components I should be selecting to emulate the Excel connection query.
If I use Live Query, I get this, which seems like it's connecting to the entire cube - all dimensions, all measures, etc., etc., etc.
Does anyone have any insight regarding which option is best for recreating the Excel query?
Thanks.
Figured it out. Turns out there was an obvious way to do it. I just inserted a new pivot table in the Excel workbook, and used the Data Connection as the source for the table. Then I was able to look at all of the available tables, fields, etc., and determine that the list of tables and fields was identical to the one that is present in PBI when you use the Live Query connection.
1 Reply
- jetboy2k
Helper I
Figured it out. Turns out there was an obvious way to do it. I just inserted a new pivot table in the Excel workbook, and used the Data Connection as the source for the table. Then I was able to look at all of the available tables, fields, etc., and determine that the list of tables and fields was identical to the one that is present in PBI when you use the Live Query connection.