Excel MDX Query: Generally, what happens is, in certain scenario we need that Query which is being runed in the backend by SSAS or SQL Server or whatever source you are using.
MDX Query?
Generally, people need this query for troubleshooting performance or collecting the traces which is being performed in the back end. For more info visit Microsoft website!
For e.g., suppose if you are using excel to fetch data from SQL server or SSAS (SQL Server Analysis Services) at that time if you select some data in your excel sheet then in the back end there is query performed by excel, where it sends all details to SQL or SSAS to get the data because SQL Server and SSAS do not understand the language of the excel. So, this query is being performed in the backend to communicate with the source.
Excel MDX Query?
People need excel mdx queries to query the database model directly to check for nay issue or problem. If you need all you excel backend queries to manupulate with data then you can follow this below steps.
- Open excel file and connect to the Cube/Tabular/SQL DB
- After selecting the required data or fields in to excel, you will get all the data reflected in your excel sheet, now perform the below tasks
Note: suppose if you’re filtering some data in the excel sheet, and it fails to load or it is time consuming then to check whether the problem is only from excel or source you can perform this task to get the MDX query and run this query at the source side directly and check the behavior.
- Go to home option and right click on that!
- You will find customize the ribbon option, just click on that.
- At the right pan you can see developer option, enable that option.
As you can see the “developer option” is added on the top of title bar of the excel. This developer option will be availibe for every user who is using excel online or on machine. Also this option will not be dependent on the office 365 editon you use.
SSAS MDX Query?
- Click on that developer option as you see in the below image highlighted.
- You will find visual basic option in the left side, just click on that.
- so, if you see the below image, you will find that one blank sheet will open to put, he code/script.
- Copy this below mentioned code to get the output in to text file and paste it on the query window as shown in the below screenshot.
Code!
Dim pvtTable As PivotTable
Dim fso As Object
Dim Fileout As Object
Set pvtTable = ActiveSheet.PivotTables(1)
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set Fileout = fso.CreateTextFile(“C:\Temp\MDXOutput.txt“, True, True)
Fileout.Write pvtTable.MDX
Fileout.Close
End Sub
Note: C:\Temp\MDXOutput.txt is my local PC file path, mentioned your file path location here.
- Go to this highlighted location and you can find the MDX script in notepad file as shown in the below image.
- More important is that always select the folder path which is not ristricted or have any GP (Group Policy) applied to it, if yes! then for sure the file will not get saved in the required folder.
As you can see in the above image that we have successfully got out MDX Query and now I can use this query to execute directly into my Tabular or Cube model. Suppose if you have AAS (Azure Analysis Services) or SSAS (SQL Server Analysis Services) both will work with MDX queries.
Related Posts!
- Run as Different User option, then you can follow this article below.
- Create Local Admin Account in any windows, follow the below article!
- Could not load folder content in SSRS/PBIRS
