How Microsoft hid the whole server and how to find it

- See SSAS-gopher?
- Not…
- And I do not see. And he is!


(Frame from the movie "DMB")

Support for a relatively new tabular (tabular) data model, as opposed to the established multidimensional OLAP, is built into a number of Microsoft products. Starting with SQL Server (SS) and ending with Excel. And if everything is clear with SS, it contains a separate separate SQL Server Analysis Services (SSAS) product. But what about the support of the language of DAX queries and other tabular functionality in Power BI, SharePoint or Excel? Let's look for a " gopher " on the example of Power BI Desktop .

I think many have noticed that when installed on a Power BI Desktop computer, Microsoft SQL Analysis Services (SSAS) folder structures are being deployed in parallel. This is an optional server component that is part of the higher editions of the Microsoft SQL Server (in particular, Enterprise and Business Intelligence editions). He is responsible (as it is easy to guess from the title) for analytical functionality and, in particular, the creation of OLAP-cubes and access to them, and, starting from the 2012 version, and for the full support of the new ideology of multidimensional data stores - tabular (or tabular) data models. This "animal" was invented to support wired data storage (as opposed to the standard string storage for OLTP) and to work with the entire data set in in-memory. A little more detail here .
')

Power BI Desktop folder structure (Stand-alone installation)

The inclusion of server components in the desktop software looks quite logical from the point of view of development and, most importantly, further support of the uniformity of the tabular data model, common for both the flagship server product, and for the desktop development environment and even for Excel. In particular, Power BI Desktop simply integrated the SSAS server core, which is responsible for supporting this tabular model and DAX data requests. When you launch the Power BI Desktop application, the xVelocity engine (nicknamed vertiPaq) starts up in the SSAS server, which performs all the rough work on servicing DAX requests, load distribution, memory management, and transferring results to the renderer, which already displays the results histograms, pivot tables (matrix) and other visual elements available in the Power BI environment.



What gives us this knowledge of domestic cuisine? And the fact that such a modular structure allows using Power BI Desktop as a test analytics server without the need to deploy a full-featured SQL Server environment with Microsoft Analysis Services. And without the corresponding licensing load (i.e. free). Of course, this approach cannot be used in industrial solutions. However, for the rapid deployment of the test infrastructure and verification of the constructed data model, nothing better can be invented.

Home craftsman tips

To determine the current port number on which the local “pocket” Analysis Services was launched, the easiest way is to use the amazing free tool, the “Swiss Knife” for any DAX hacker of DAX Studio . When connecting from DAX Studio to your data model (the necessary PBIX file must be opened in Power BI Desktop), connection settings (as in the figure below) will be displayed in the status line of the application, which can be used to access this data model from other applications, for example from Excel or another Power Bi Desktop instance locally or over the network.

The port number will change each time you start PBI Desktop. In my example, this is 52308. In your case, it will most likely be some other number from the Power BI Desktop ports pool.

To reconnect to this data model, you will need not only a randomly assigned port number, but also a database name randomly generated as a GUID. The port number is obtained according to the method described above, and the identifier of the required database (GUID of the data model) can be obtained by generating the following SQL query in DAX Studio:

select * from $SYSTEM.DBSCHEMA_CATALOGS 

In columns

  CATALOG_NAME and DATABASE_ID 

will be the required GUID name.

Local solution

The link (123kB) is a downloadable PBIX file developed by me, which generates a list of all data models loaded into memory (including the built-in data file). That is, there will be as many lines in the list as there are PBIX files running.
Updated: version updated for compatibility with the August release of PBI Desktop.

Disclaimer : This file can only be used for demonstration purposes and testing, is provided “as is”, with all the bugs, errors and other, 90% generated through the Power Query / PowerPivot interface and cannot serve as a good example for teaching Power BI programming .

Program interface

To configure, after clicking the [Edit requests] button, you must change the parameter

  USER_NAME 

and, perhaps,

  WORK_FOLDER 

.

Home craftsman tips

Power BI Desktop can be deployed on the local computer in two different modes: Stand-alone application Windows Apps The first is downloaded from https://powerbi.com and installed in a couple of clicks as the most common application. The second is installed through the store Microsoft Search Store " Power BI " The binary code of the application in both versions is the same only in the first case, you will have to independently monitor the release of new versions of the application, the releases of which Microsoft cooks like pancakes. The nominal update frequency of Power BI Desktop is once a month, Power BI Reporting Services is on average once a quarter. Installing through the Microsoft Store will relieve you of control over software updates, but there is a small (but non-zero) chance that in the updated version you will unexpectedly encounter any problems that were not observed in the previous release. Personally, just in case, I keep both editions, regularly replenishing my personal collection with monthly releases of PBI Desktop, in order to be able to roll back to the previous one. Another feature of these two modes is that each of them has its own working folder structure. To configure the file above, this is important (see picture).

I think (if there is enough interest from the community) I will continue this topic on the example of specific use cases .

Yuri Kolmakov ( McCow )