Home Business Intelligence Connect with Energy BI Desktop Mannequin from Excel and SSMS

Connect with Energy BI Desktop Mannequin from Excel and SSMS

0
Connect with Energy BI Desktop Mannequin from Excel and SSMS

[ad_1]

Power BI Desktop Excel SSMS

Energy BI Desktop is a implausible report authoring device. I’ve numerous 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 attainable to connect with a Energy BI Desktop mannequin from Excel, SQL Server Administration Studio (SSMS) or SQL Server Profiler? The reply is sure, you possibly can. 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 eventualities:

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

  • Once more, if in case you have some efficiency points you may want to connect with the mannequin from SSMS

  • You’ve a posh mannequin and it’s onerous for you perceive it, however, you’re a nice Excel developer, so you possibly can hook up with Energy BI Desktop mannequin from Excel so you should use attain options obtainable in Excel like named units

  • Only for curiosity! You might be interested in writing MDX codes over an current mannequin, you need to see how your mannequin seem like in Excel and so forth

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

Energy BI Desktop makes use of xVelocity expertise and masses information into reminiscence. It makes use of an area occasion of SQL Server Evaluation Providers (SSAS). It does the job by operating msmdsrv.exe file which could be present in “bin” folder underneath your Energy BI Desktop set up folder which is generally underneath you Program Recordsdata. The msmdsrv.exe is certainly the SSAS service file. So even for those who 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 will be legitimate till Energy BI Desktop is just not closed or the msmdsrv.exe is just not killed from Job 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 must always be capable of hook up with the occasion from Excel, SSMS or SQL Server Profiler provided that we all know the port quantity.

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

%ProgramFilespercentMicrosoft SQL ServermsasXX.INSTANCE_NAMEOLAPbin

which XX is your model of SQL Server. So XX could 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 situated in your Energy BI Desktopbin folder with the opposite one yow will discover underneath 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.

Methods to discover Energy BI Desktop native port?

There are numerous strategies you possibly can get hold of 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)

  • It’s best to see one thing like this

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

  • What we’d like 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 needs to be one thing just like the screenshot under which reveals energetic connections, their native tackle which is native IP tackle 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 outcome desk respectively from left to proper is:

Lively Connection, Native Tackle, Overseas Tackle, State, PID

As I discussed, Lively Connection is the one we’re inquisitive 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 device to run DAX queries towards SSAS Tabular Fashions and Energy BI Desktop Fashions. The fascinating level is that it’s free and you may 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 once you work in your prospects’ websites. You doubtlessly don’t have the rights to put in a brand new software program like DAX Studio. However, you’ll at all times have entry to your native information even once you work on website for a buyer.

Everytime you run Energy BI Desktop it creates a bunch of information and folders in a temp listing in your native disc. You’ll discover numerous fascinating details about a operating occasion of Energy BI Desktop like SQL Server Profiler hint information information, 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 information 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 for those who haven’t linked to any information sources or for those who haven’t open any saved Energy BI Desktop (*.PBIX) information. That port quantity is saved in a textual content file named “msmdsrv.port.txt”. So the one factor we’d like 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 once you shut Energy BI Desktop.

You could find Energy BI Desktop temp folder right here:

  • Navigate to%LocalAppDatapercentMicrosoftPower BI DesktopAnalysisServicesWorkspaces

Power BI Desktop Temp Folder

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

  • Open “Knowledge” folder

  • Discover “msmdsrv.port.txt” within the folder and open it. It accommodates 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 straightforward to connect with the mannequin.

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

Connecting to Power BI Desktop Model from Excel

  • Enter the server identify as “localhost:60575” the place “60575” could 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 way in which you need to view information then click on OK

Connecting to Power BI Desktop Model from Excel

  • Now you possibly can slice and cube your Energy BI Desktop mannequin information 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 identify 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 parts 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 numerous further LocalDate tables within the mannequin. I reckon they’re there for supporting robotically generated date hierarchy. I observed that there’s certainly a LocalDate desk for every date column you’ve gotten in your mannequin. So if in case you have 10 columns of “Date” information kind in your mannequin, you then’ll have 10 LocalDate tables. Not too positive if it’s the simplest strategy to implement a date hierarchy, however, it’s how it’s!

Notice: You may simply see how robotically generated date hierarchy works. You simply must have a date column in a desk in your mannequin. If you drag and drop the date column on to a visible, let’s say a desk, you possibly can see that date column will robotically 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 may 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 kind the code under 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 identify within the “all()” perform.

Querying Power BI Desktop Model with DAX

Working MDX expressions towards Energy BI Desktop Mannequin

As you may know you possibly can run MDX expressions towards an SSAS Tabular Mannequin database. Energy BI Desktop Mannequin is just not an exception.

To this point 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 operating MDX expressions.

The next MDX expression reveals Whole Web Gross sales by Product for Calendar 12 months 2014 (I imported information 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 can even 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 looking the mannequin from SSMS, you may get nothing once you drag and drop a measure from a truth desk into the grid. It’s because you dragged an implicit measure reasonably than an specific one. Usually talking, an implicit measure is a sort 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 features. 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 best technique in my expertise. Not solely are you able to browse the mannequin from DAX Studio, however, additionally it’s the greatest device you should use for querying your Energy BI Desktop or SSAS Tabular fashions. There are a bunch of services obtainable within the device 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 to write your DAX
  • Write some DAX codes and run it

Browsing Power BI Desktop Model from DAX Studio

There’s a whole lot of 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 will need to have device.

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 kind
  • Enter “localhost:60575” as server identify 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 record 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 clarify efficiency tuning and the explanations we chosen these occasions in SQL Server Profiler it’s out of scope of this put up. If you happen to’re inquisitive about studying extra about DAX efficiency tuning I encourage you to take a look at this wonderful whitepaper from SQLBI.

[ad_2]

LEAVE A REPLY

Please enter your comment!
Please enter your name here