Connect with Energy BI Desktop Mannequin from Excel and SSMS


Power BI Desktop Excel SSMSEnergy BI Desktop is a incredible report authoring instrument. I’ve a number of expertise working with Tableau as properly and I can say, man, Energy BI is rising in a short time. Numerous superior concepts have been added to Energy BI and much more is coming. However, It could be a query for a few of you that’s that doable to connect with a Energy BI Desktop mannequin from Excel, SQL Server Administration Studio (SSMS) or SQL Server Profiler? The reply is sure, you’ll be able to. However, how on earth somebody ought to hook up with a Energy BI Desktop mannequin from Excel, SSMS or SQL Server Profiler? Properly, it could possibly be helpful for the next situations:

  • Connecting to the mannequin utilizing SQL Server Profiler for efficiency tuning, monitoring and so forth

  • Once more, when you’ve got some efficiency points you may want to connect with the mannequin from SSMS

  • You have got a fancy mannequin and it’s exhausting for you perceive it, however, you’re a nice Excel developer, so you’ll be able to hook up with Energy BI Desktop mannequin from Excel so you should utilize attain options out there in Excel like named units

  • Only for curiosity! You might be inquisitive about writing MDX codes over an current mannequin, you wish to see how your mannequin appear to be in Excel and so forth

On this article I present you ways to connect with Energy BI Desktop mannequin no matter any use case situations. So for no matter purpose you’d like to connect with a Energy BI Desktop mannequin this put up will show you how to obtain your aim.

Energy BI Desktop makes use of xVelocity know-how and hundreds knowledge into reminiscence. It makes use of an area occasion of SQL Server Evaluation Providers (SSAS). It does the job by working msmdsrv.exe file which could be present in “bin” folder beneath your Energy BI Desktop set up folder which is generally beneath you Program Information. The msmdsrv.exe is certainly the SSAS service file. So even if you happen to haven’t put in SSAS in your machine Energy BI Desktop runs msmdsrv.exe. When Energy BI Desktop runs msmdsrv.exe it creates an area occasion of SSAS. This native SSAS occasion makes use of a random port quantity so it could be legitimate till Energy BI Desktop is just not closed or the msmdsrv.exe is just not killed from Process Supervisor.

Find msmdsrv.exe in Power BI Desktop Folder

So, we now have an area occasion of SSAS utilizing a random port quantity. Due to this fact, we should always have the ability to hook up with the occasion from Excel, SSMS or SQL Server Profiler provided that we all know the port quantity.

Notice: When you have put in an occasion of SSAS in your machine yow will discover msmdsrv.exe beneath “OLAPbin” folder from SQL Server set up path:

%ProgramFilespercentMicrosoft SQL ServermsasXX.INSTANCE_NAMEOLAPbin

which XX is your model of SQL Server. So XX can be 10, for SQL Server 2008R2, could possibly be 11 for SQL Server 2012 and so forth. The distinction between the native msmdsrv.exe file positioned in your Energy BI Desktopbin folder with the opposite one yow will discover beneath your SQL Server set up folder is that the one which Energy BI Desktop runs is a console programme whereas the opposite one is a Home windows service programme.

Tips on how to discover Energy BI Desktop native port?

There are numerous strategies you’ll be able to receive the port quantity. On this put up I clarify three of them.

  • Discovering Energy BI Desktop native port utilizing Home windows Command Immediate (CMD)
  • Utilizing DAX Studio
  • Discovering native port quantity from Energy BI Desktop temp listing
Discovering Energy BI Desktop native port utilizing Home windows Command Immediate (CMD)
  • Run Home windows Command Immediate as Administrator
  • Copy, paste and run the next command:

TASKLIST /FI “imagename eq msmdsrv.exe” /FI “sessionname eq
console”

Finding Power BI Desktop local port using Windows Command Prompt (CMD)

  • You must see one thing like this

Finding Power BI Desktop local port using Windows Command Prompt (CMD)

  • What we want is the PID
  • Now run the next command and put the PID quantity you bought from earlier command between citation marks

netstat /ano | findstr “13944”

Finding Power BI Desktop local port using Windows Command Prompt (CMD)

  • The outcomes ought to be one thing just like the screenshot beneath which reveals energetic connections, their native handle which is native IP handle adopted by the port quantity. And that is what we’re on the lookout for.

Finding Power BI Desktop local port using Windows Command Prompt (CMD)

Notice: The column names for the consequence desk respectively from left to proper is:

Lively Connection, Native Handle, Overseas Handle, State, PID

As I discussed, Lively Connection is the one we’re serious about in order that the quantity coming after colon is the port quantity.

Discovering Energy BI Desktop native port utilizing DAX Studio

DAX Studio is an superior instrument to run DAX queries towards SSAS Tabular Fashions and Energy BI Desktop Fashions. The fascinating level is that it’s free and you’ll obtain it from right here.

Finding Power BI Desktop local port using DAX Studio

Finding Power BI Desktop local port using DAX Studio

Discovering Energy BI Desktop native port from Energy BI Desktop temp listing

In actual world you may NOT have entry rights to open CMD command immediate if you work in your prospects’ websites. You probably don’t have the rights to put in a brand new software program like DAX Studio. However, you’ll all the time have entry to your native recordsdata even if you work on web site for a buyer.

Everytime you run Energy BI Desktop it creates a bunch of recordsdata and folders in a temp listing in your native disc. You’ll discover a number of fascinating details about a working occasion of Energy BI Desktop like SQL Server Profiler hint knowledge recordsdata, native occasion of Evaluation Providers log file and port quantity. For the aim of this put up we simply want the port quantity, however, I encourage you to take a look on the different recordsdata within the Energy BI Desktop temp listing. I wager you’ll discover it informative. Everytime you run Energy BI Desktop in your machine it opens a random port quantity. The port quantity is impartial of the mannequin so it doesn’t actually matter if you happen to haven’t linked to any knowledge sources or if you happen to haven’t open any saved Energy BI Desktop (*.PBIX) recordsdata. That port quantity is saved in a textual content file named “msmdsrv.port.txt”. So the one factor we want is to browse the temp listing of Energy BI Desktop and open the above textual content file.

Notice: This can be a temp listing  that’s created everytime you run Energy BI Desktop and is deleted if you shut Energy BI Desktop.

You’ll find Energy BI Desktop temp folder right here:

  • Navigate to%LocalAppDatapercentMicrosoftPower BI DesktopAnalysisServicesWorkspaces

Power BI Desktop Temp Folder

  • There ought to be an “AnalysisServicesWorkspaceXXX” folder which XXX is a random quantity. Open that folder

  • Open “Information” folder

  • Discover “msmdsrv.port.txt” within the folder and open it. It incorporates the random native SSAS occasion port quantity utilized by Energy BI Desktop

Finding Power BI Desktop Local Port Number in Temp Dir

Power BI Desktop Local Port Number

Now that we now have the port quantity it’s simple to connect with the mannequin.

  • Open Excel
  • Click on “From Different Sources” from “Information” tab from the ribbon
  • Click on “From Evaluation Providers”

Connecting to Power BI Desktop Model from Excel

  • Enter the server title as “localhost:60575” the place “60575” can be the port quantity you retrieved earlier then click on Subsequent

Connecting to Power BI Desktop Model from Excel

Connecting to Power BI Desktop Model from Excel

Connecting to Power BI Desktop Model from Excel

  • Choose the best way you wish to view knowledge then click on OK

Connecting to Power BI Desktop Model from Excel

  • Now you’ll be able to slice and cube your Energy BI Desktop mannequin knowledge in Excel

Connecting to Power BI Desktop Model from Excel

It’s very easy to connect with a Energy BI Desktop mannequin from SSMS after having the port quantity.

  • Open SSMS
  • Choose “Evaluation Providers” for Server Sort
  • Enter the server title as “localhost:60575” which 60575 is the port quantity and click on Join

Connecting to Power BI Desktop Model from SSMS

Now we’re efficiently linked to the Energy BI Desktop Mannequin. Broaden the mannequin components and also you’ll discover bunch of fascinating issues totally different connections used within the mannequin, tables and so forth.

Connecting to Power BI Desktop Model from SSMS

It’s fascinating isn’t it?

The very first thing that caught my eyes after I linked to a Energy BI Desktop from SSMS was a number of further LocalDate tables within the mannequin. I reckon they’re there for supporting mechanically generated date hierarchy. I observed that there’s certainly a LocalDate desk for every date column you will have in your mannequin. So when you’ve got 10 columns of “Date” knowledge sort in your mannequin, then you definately’ll have 10 LocalDate tables. Not too positive if it’s the simplest technique to implement a date hierarchy, however, it’s how it’s!

Notice: You’ll be able to simply see how mechanically generated date hierarchy works. You simply must have a date column in a desk in your mannequin. Once you drag and drop the date column on to a visible, let’s say a desk, you’ll be able to see that date column will mechanically generate a date hierarchy with out having an actual date dimension in your mannequin.

Power BI Desktop Generate Date Hierarchy

Querying Energy BI Desktop Mannequin with DAX

You’ll be able to run DAX queries towards the mannequin. In our pattern I ran a easy DAX question to see the contents of the LocalDate tables.

  • In SSMS, proper click on the database from Object Explorer
  • Click on “New Question” then click on “MDX” (You’re proper, there isn’t any DAX right here, however, we’ll write and execute DAX queries in an MDX question editor. Superior!)

Querying Power BI Desktop Model with DAX

    • Now merely sort the code beneath and press F5 to execute it and see the outcomes:

       consider

              all (LocalDateTable_4535931b-fab6-4ea2-b2d7-0979ef27c9e5′)

Notice: You clearly must put your LocalDate desk title within the “all()” perform.

Querying Power BI Desktop Model with DAX

Operating MDX expressions towards Energy BI Desktop Mannequin

As you may know you’ll be able to run MDX expressions towards an SSAS Tabular Mannequin database. Energy BI Desktop Mannequin is just not an exception.

Thus far you bought the thought how to connect with a Energy BI Desktop Mannequin from SSMS and question the tables with DAX. The identical course of applies for working MDX expressions.

The next MDX expression reveals Whole Web Gross sales by Product for Calendar 12 months 2014 (I imported knowledge from AdventureWorksDW2016CTP3 database into Energy BI Desktop):

SELECT

  NON EMPTY {

    [Measures].[Total Sales]

  } ON COLUMNS,

  NON EMPTY {

    (

      [DimProduct].[EnglishProductName].[EnglishProductName].allmembers

    )

  } ON ROWS

FROM ( SELECT

       (

         {

           [DimDate].[CalendarYear].&[2014]

         }

       ) ON COLUMNS

     FROM [Model])

WHERE (

        [DimDate].[CalendarYear].&[2014]

      )

MDX and Power BI Desktop

Looking Energy BI Desktop Mannequin from SSMS

You may as well browse the mannequin from SSMS. To take action, excellent click on on the database from Object Explorer and choose “Browse”.

Browsing Power BI Desktop Model from SSMS

Browsing Power BI Desktop Model from SSMS

Notice: When shopping the mannequin from SSMS, you may get nothing if you drag and drop a measure from a reality desk into the grid. It’s because you dragged an implicit measure relatively than an specific one. Usually talking, an implicit measure is a kind of measure you haven’t created. They’re certainly numeric columns in your tables. However, an specific measure is the measure you create utilizing DAX capabilities. A greatest observe to create specific measures and conceal all implicit ones within the mannequin. Verify this out to be taught extra about measure varieties in DAX.

Looking Energy BI Desktop Mannequin from DAX Studio

Looking a Energy BI Desktop mannequin from DAX Studio might be the simplest methodology in my expertise. Not solely are you able to browse the mannequin from DAX Studio, however, additionally it’s the greatest instrument you should utilize for querying your Energy BI Desktop or SSAS Tabular fashions. There are a bunch of services out there within the instrument for efficiency tuning and so forth.

  • Open DAX Studio and hook up with your Energy BI Desktop mannequin
  • It instantly opens a question editor so that you could write your DAX
  • Write some DAX codes and run it

Browsing Power BI Desktop Model from DAX Studio

There’s numerous fascinating issues about DAX Studio like the power to see Question Plans, Server Timing and so forth, so I encourage you to obtain and play with it. If you wish to tune your DAX efficiency it is a should have instrument.

Profiling Energy BI Desktop Mannequin utilizing SQL Server Profiler

You need to use SQL Server Question Profiler to seize question plans and use it for efficiency tuning.

  • Open SQL Server Question Profiler
  • Choose Evaluation Providers as server sort
  • Enter “localhost:60575” as server title then click on “Join”

Profiling Power BI Desktop Model using SQL Server Profiler

  • Click on “Occasions Choice” tab
  • Tick “Present all occasions”

Profiling Power BI Desktop Model using SQL Server Profiler

  • Discover and tick all the following occasions from the checklist and deselect all different occasions, then untick “Present all occasions”

Profiling Power BI Desktop Model using SQL Server Profiler

  • Click on Run
  • Now return to your Energy BI Desktop and drag and drop a column to the canvas

Power BI Desktop

  • You’ll instantly see the traces in SQL Server Profiler

Profiling Power BI Desktop Model using SQL Server Profiler

I’m not going to elucidate efficiency tuning and the explanations we chosen these occasions in SQL Server Profiler it’s out of scope of this put up. Should you’re serious about studying extra about DAX efficiency tuning I encourage you to take a look at this wonderful whitepaper from SQLBI.



Supply hyperlink

Leave a Reply

Your email address will not be published. Required fields are marked *