Analyzing fresh ADX data from Excel pivot table
How can you access ADX (aka Kusto) data in from Excel?
Using Power Query (Known in Excel as Get & Transform), you can connect to Kusto and import data into Excel.
Data can be imported into the data model or into a table or a pivot cache.
In any case the keyword is import, which means that to see new data, you need to refresh and wait for all the data to be imported again. Typically, data in Kusto is counted in Billions of rows and cannot be brought fully into Excel memory.
In many cases you also want to see fresh data as soon as it arrives to Kusto.
In a previous article Direct Query from Excel to Azure Data Explorer (microsoft.com) I described a way to mimic Direct Query access ala Power BI in Excel.
The method used in this article that allows the user to filter the imported data using values entered into cells in the grid.
In this article I would like to describe a way to really query Kusto data in real time without importing any data and without any volume limitations.
Using OLAP cubes from Excel pivot tables
Starting in Excel 2000, Excel had the option to connect to OLAP cubes in AS and use them as a source for Pivot tables and later also to Cube functions.
Trying to search for the earliest mention of Excel and OLAP, I saw a reference for a book
Originally these cubes were really cubes on an on-premise machine, but since these origins many other creatures have been presented to Excel as an OLAP cube.
Among these non-OLAP things who present themselves as OLAP are AS tabular models, Power Pivot models within Excel and (Spoiler alert) Power BI datasets.
They all have in common the ability to process MDX queries, which is the language Excel uses, although the native language of these sources is DAX.
Here is the proof that Excel is (almost) oblivious to the fact that these are not really cubes. While running a query , you’ll see this message in the status line.
So, what is the secret for using true Direct Query from Excel?
The answer is Power BI datasets.
We can build a PBI model using DQ to Kusto, publish to the PBI service and connect Excel to the published dataset.
MDX queries generated by the Pivot code, will find their way to the Kusto backend as KQL statements that will aggregate the data as needed by the pivot and brought back to Excel.
Step by step example
- Create a Power BI report (Attached) based on the Contoso Sales database in the Kusto Help cluster.
- Create measures for anything that you want to use in values.
- Published the report to any workspace you have access to.
- Open a new Excel file and used insert pivot table
- Select the last option From Power BI (As an alternative, you can start from the report on the service and use Export/Analyze in Excel
- From the list of available Power BI datasets select the one just published
- Get a pivot connected to the dataset
- Drag fields and measures to the different areas of the pivot table
- Optionally add a pivot chart
Caveats and things to remember
- You can’t drag a field to the values and expect a sum of field to be created automatically. You must create measures
- The use of slicers can increase the number of queries significantly. It’s better to just use filters
- Don’t rely on M dynamic parameters in your PBI model, because you won’t be able to provide values when connecting from Excel.
- The attached Excel file will not work for you as is because it is connected to a specific workspace which you don’t have access to. The pbix file should work and you should be able to use it as is.
