Energy BI Desktop is a incredible report authoring instrument. I’ve numerous expertise working with Tableau as nicely and I can say, man, Energy BI is rising in a short time. A lot of superior concepts have been added to Energy BI and much more is coming. However, It is likely to be a query for a few of you that’s that potential to connect with a Energy BI Desktop mannequin from Excel, SQL Server Administration Studio (SSMS) or SQL Server Profiler? The reply is sure, you may. However, how on earth somebody ought to hook up with a Energy BI Desktop mannequin from Excel, SSMS or SQL Server Profiler? Properly, it might 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 have some efficiency points you would possibly want to connect with the mannequin from SSMS
You may have a posh mannequin and it’s arduous for you perceive it, however, you’re a nice Excel developer, so you may hook up with Energy BI Desktop mannequin from Excel so you should utilize attain options accessible in Excel like named units
Only for curiosity! You’re interested in writing MDX codes over an present mannequin, you need to see how your mannequin appear 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 situations. So for no matter motive you’d like to connect with a Energy BI Desktop mannequin this submit will show you how to obtain your aim.
Energy BI Desktop makes use of xVelocity know-how and hundreds information into reminiscence. It makes use of an area occasion of SQL Server Evaluation Companies (SSAS). It does the job by working msmdsrv.exe file which may be present in “bin” folder beneath your Energy BI Desktop set up folder which is often beneath you Program Recordsdata. The msmdsrv.exe is certainly the SSAS service file. So even when you 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 just isn’t closed or the msmdsrv.exe just isn’t killed from Activity Supervisor.
So, now we have an area occasion of SSAS utilizing a random port quantity. Due to this fact, we should 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: If in case you have put in an occasion of SSAS in your machine you 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 could be 10, for SQL Server 2008R2, might 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 you 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.
Methods to discover Energy BI Desktop native port?
There are numerous strategies you may get hold of the port quantity. On this submit 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”
- It is best to see one thing like this
- 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”
- The outcomes must be one thing just like the screenshot beneath which exhibits lively connections, their native handle which is native IP handle adopted by the port quantity. And that is what we’re on the lookout for.
Notice: The column names for the consequence desk respectively from left to proper is:
Energetic Connection, Native Deal with, International Deal with, State, PID
As I discussed, Energetic Connection is the one we’re interested by 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 in opposition to SSAS Tabular Fashions and Energy BI Desktop Fashions. The fascinating level is that it’s free and you’ll obtain it from right here.
Discovering Energy BI Desktop native port from Energy BI Desktop temp listing
In actual world you would possibly NOT have entry rights to open CMD command immediate while you work in your clients’ websites. You probably 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 recordsdata even while you work on website 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 numerous fascinating details about a working occasion of Energy BI Desktop like SQL Server Profiler hint information recordsdata, native occasion of Evaluation Companies log file and port quantity. For the aim of this submit 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 guess 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 when you haven’t linked to any information sources or when you 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’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 while you shut Energy BI Desktop.
You could find Energy BI Desktop temp folder right here:
- Navigate to%LocalAppDatapercentMicrosoftPower BI DesktopAnalysisServicesWorkspaces
There must 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 comprises the random native SSAS occasion port quantity utilized by Energy BI Desktop
Now that now we have the port quantity it’s simple to connect with the mannequin.
- Open Excel
- Click on “From Different Sources” from “Knowledge” tab from the ribbon
- Click on “From Evaluation Companies”
- Enter the server identify as “localhost:60575” the place “60575” could be the port quantity you retrieved earlier then click on Subsequent
- Choose the way in which you need to view information then click on OK
- Now you may slice and cube your Energy BI Desktop mannequin information in Excel
It’s very easy to connect with a Energy BI Desktop mannequin from SSMS after having the port quantity.
- Open SSMS
- Choose “Evaluation Companies” for Server Kind
- Enter the server identify as “localhost:60575” which 60575 is the port quantity and click on Join
Now we’re efficiently linked to the Energy BI Desktop Mannequin. Develop the mannequin parts and also you’ll discover bunch of fascinating issues totally different connections used within the mannequin, tables and so forth.
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 routinely generated date hierarchy. I seen that there’s certainly a LocalDate desk for every date column you’ve gotten in your mannequin. So when you have 10 columns of “Date” information sort in your mannequin, then you definitely’ll have 10 LocalDate tables. Not too positive if it’s the best solution to implement a date hierarchy, however, it’s how it’s!
Notice: You may simply see how routinely generated date hierarchy works. You simply have to have a date column in a desk in your mannequin. While you drag and drop the date column on to a visible, let’s say a desk, you may see that date column will routinely generate a date hierarchy with out having an actual date dimension in your mannequin.
Querying Energy BI Desktop Mannequin with DAX
You may run DAX queries in opposition to 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 is no such thing as a DAX right here, however, we’ll write and execute DAX queries in an MDX question editor. Superior!)
- 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 have to put your LocalDate desk identify within the “all()” operate.
Operating MDX expressions in opposition to Energy BI Desktop Mannequin
As you would possibly know you may run MDX expressions in opposition to an SSAS Tabular Mannequin database. Energy BI Desktop Mannequin just isn’t an exception.
To date you bought the concept 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 exhibits Whole Web Gross sales by Product for Calendar Yr 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]
)
Shopping Energy BI Desktop Mannequin from SSMS
It’s also possible to browse the mannequin from SSMS. To take action, good click on on the database from Object Explorer and choose “Browse”.
Notice: When shopping the mannequin from SSMS, you would possibly get nothing while you drag and drop a measure from a truth desk into the grid. It is because you dragged an implicit measure moderately than an express one. Typically talking, an implicit measure is a kind of measure you haven’t created. They’re certainly numeric columns in your tables. However, an express measure is the measure you create utilizing DAX features. A finest apply to create express measures and conceal all implicit ones within the mannequin. Test this out to be taught extra about measure sorts in DAX.
Shopping Energy BI Desktop Mannequin from DAX Studio
Shopping a Energy BI Desktop mannequin from DAX Studio might be the best methodology in my expertise. Not solely are you able to browse the mannequin from DAX Studio, however, additionally it’s the finest instrument you should utilize for querying your Energy BI Desktop or SSAS Tabular fashions. There are a bunch of amenities accessible 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
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 will need to have instrument.
Profiling Energy BI Desktop Mannequin utilizing SQL Server Profiler
You should utilize SQL Server Question Profiler to seize question plans and use it for efficiency tuning.
- Open SQL Server Question Profiler
- Choose Evaluation Companies as server sort
- Enter “localhost:60575” as server identify then click on “Join”
- Click on “Occasions Choice” tab
- Tick “Present all occasions”
- Discover and tick the entire following occasions from the listing and deselect all different occasions, then untick “Present all occasions”
- Click on Run
- Now return to your Energy BI Desktop and drag and drop a column to the canvas
- You’ll instantly see the traces in 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 submit. In case you’re interested by studying extra about DAX efficiency tuning I encourage you to take a look at this superb whitepaper from SQLBI.
