Forum Discussion

tljthree's avatar
tljthree
Icon for Advocate I rankAdvocate I
8 years ago

DAX Query against SSAS Tabular to produce Excel sheet datasets for analysis - OPTIONS

We have an existing Analysis Services Tabular database that we need to use as a source for several analysis datasets via DAX query. What is the best way to do this? Am I missing something?

 

What I have

Analysis Services Tabular 2012

Excel 2013

Power BI Desktop (September 2017)

 

Goal

I want a select group of power-users to be able to author DAX queries and dump the results into Excel sheets. The Excel sheets will be datasets for further analysis.

 

Options that I've considered

1. SQL Server Management Studio to do the DAX Query. Issue: The user must have "Full Control" access to the Tabular database, which is more than we want to give.

 

2. DAX Studio - The add-in does what we want, but would have to get this app approved for use internally.

 

3. Power BI Desktop - If the intent is to dump to an Excel sheet, this is kludgy to interactively drag all of the columns from the dataset into the table visual, only to export to Excel.

 

4. The "Change the ODC" trick with Excel, as annotated here: (

https://www.sqlbi.com/articles/import-data-from-tabular-model-in-excel-using-a-dax-query/)

This is a tad kludgy, and requires another tool to allow the DAX query author to see the structure of the Tabular database.

5. Various others - Report Builder, etc. 

 

...

 

What am I missing? Is there a more streamlined way to do this in Power BI Desktop?

 

Or is DAX Studio the best way to achieve this, without giving users too much permissions on the Analysis Services Tabular database?

 

Are more options available as we upgrade Excel, and/or upgrade SSAS or SSMS?

 

Thanks for your help/recommendations.

 

Tom LJ

 

 

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi tljthree,

     

    From Power BI desktop side, it is not supported to export visual data to Excel, we can only export to CSV file. Since you don't want to grant users with "Full Access" to Tabular database, and you don't want to change the ODC file, I think using DAX Studio add-in might be the best choice currently. Reference: Using Microsoft Excel to Retrieve SSAS Tabular Data

     

    Besides, as this question is more related to SSAS, I would suggest you post your question in SSAS forum, there you can get more professional advice, and you might find a better solution.

     

    Best regards,
    Yuliana Gu